LIMIT and OFFSET in Postmodern

posted on 2010-04-25 17:58:09

I've been using the Postmodern library in Common Lisp to access PostgresQL databases of late and it's been a pretty good experience.

That said, I had a little trouble using LIMIT and OFFSET to get DAO objects and wanted to put an example online in case anyone else has trouble with this. Future Postmoderners, May Google protect you.

You'll want to use query-dao instead of select-dao and wrap the limit around a select statement. It winds up looking something like this:

(query-dao 'card
  (:limit (:select (:distinct 'name) :from 'card) 10 0))

In this example, we're selecting the first 10 rows (that are distinct by name) from the card table. Obviously, you could use '* instead of (:distinct 'name) to select from all rows.

That's not much of a post but here it is. Once I'm through finals I'll try to start posting a bit more regularly. Then again, I'll be moving once in May and once in June so we'll see what happens.

A Brief, "Postmodern" Shout-out

posted on 2010-04-12 01:11:48

Things have been crazy lately but I'm not here to give a full update. I will say that there's been good with the bad, family, friends and supporters all the while and that the bad is mostly the usual bureaucratic and financial troubles that are just a part of life. I'm trying to post more regularly. Today is a brief programming post.

This semester I've been taking a database course and we're building a small, silly webapp as the final project. The course uses SQL+PHP and I asked my professor if he wouldn't mind if I used SQL+Common Lisp. He accepted and so I've been using the Postmodern library for Common Lisp to talk to my Postgresql database. Postmodern has been really nice to use so far but there's one thing that I had a little trouble with that I'd like to document here.

Generally, if you're writing classes in Lisp you're using CLOS and an example might be something like this:

(defclass user ()
  ((username
    :initarg :username :reader :username)
   (password
    :initarg :password :reader :password)
   (salt
    :initarg :salt :reader :salt)
   (email
    :initarg :email :reader :email)
   (first-name
    :initarg :first-name :reader :first-name)
   (last-name
    :initarg :last-name :reader :last-name)
   (zip
    :initarg :zip :reader :zip)))

Postmodern has a nice method for interacting with the database via class definitions that it coins "Database Access Objects". Note that DAOs neither are nor attempt to be a full ORM solution, a very sane decision in my humble and inexperienced opinion. Anyway, to make a normal class into a DAO class is easy, just do this:

(defclass user ()
  ((username
    :col-type string
    :initarg :username :reader :username)
   (password
    :col-type string
    :initarg :password :reader :password)
   (salt
    :col-type string
    :initarg :salt :reader :salt)
   (email
    :col-type string
    :initarg :email :reader :email)
   (first-name
    :col-type string
    :initarg :first-name :reader :first-name)
   (last-name
    :col-type string
    :initarg :last-name :reader :last-name)
   (zip
    :col-type integer
    :initarg :zip :reader :zip))
  (:metaclass dao-class)
  (:keys username))

All you have to do is add col-types to each slot so the system knows what type is stored in the database rows, list the components of the primary key and declare it a member of the dao-class metaclass. With that done, you can easily work with CLOS objects and fairly seamlessly select, update, delete or instantiate+insert them into the database. Creating the table itself can be done as follows: (execute (dao-table-definition 'user)). However, this is really intended as a shortcut for cases where you have a simple table definition. Say you wanted to allowed users to own collections of things, maybe collectible cards, and track those in the database as well. You ought to have foreign key constraints on the database so that collections couldn't be owned by users that didn't exist or consist of cards that didn't exist or were made up.

In the case where foreign key constraints are desired or other more complex checks should be made, the preferred method is to write a deftable definition in addition to the class and then create the table with (create-table 'class) or (create-all-tables) if you have several tables. This would make for nasty code duplication since you'd still need a dao-object class to interact with the tables as nicely as possible. Thankfully, there's a macro to clear the situation up and import the simple parts from your dao-class specification. A possible deftable for the collection class might look like this:

(deftable collection
  (!dao-def) ;; Import the existing info from the dao-class definition.
  (!foreign 'user 'username) ;; Ensure that the username that owns the collection exists in our user table.
  ;; Ensure that each card in a collection has a name and edition corresponding to a card in our database.
  (!foreign 'card '(card-name card-edition)
	          '(name edition)))

Of course, if your tables are already created and you just want to access them or you want to create them at the psql prompt, you don't care about any of this. Hmm...I guess that's supposed to go at the top. Anyway, a more careful and thorough reading of the documentation would've shown me this but examples are nice and here one is in case anyone googles around for it like I did. As far as I can tell, this is the preferred current approach for table creation. Corrections welcome and thanks to Marijn Haverbeke for writing postmodern. It's been wonderful so far.

This blog covers 2015, Books, Butler, C, Dad, Discrete Math, Displays, Education, Erlang, Essay, Gaming, Gapingvoid, HTDP, Hardware, IP Law, LISP, Lecture, Lessig, Linkpost, Linux, Lists, MPAA, Milosz, Music, Neruda, Open Source, Operating Systems, Personal, Pics, Poetry, Programming, Programming Languages, Project Euler, Quotes, Reddit, SICP, Self-Learning, Uncategorized, Webcomic, XKCD, Xmas, \"Real World\", adulthood, apple, career, careers, choices, clones, coleslaw, consumption, creation, emulation, fqa, games, goals, haltandcatchfire, heroes, injustice, ironyard, linux, lisp, lists, math, melee, metapost, milosz, music, pandemic, personal, poetry, productivity, professional, programming, ragequit, recreation, reflection, research, rip, strangeloop, vacation, work, year-in-review

View content from 2024-09, 2024-06, 2024-03, 2024-01, 2023-12, 2023-07, 2023-02, 2022-12, 2022-06, 2022-04, 2022-03, 2022-01, 2021-12, 2021-08, 2021-03, 2020-04, 2020-02, 2020-01, 2018-08, 2018-07, 2017-09, 2017-07, 2015-09, 2015-05, 2015-03, 2015-02, 2015-01, 2014-11, 2014-09, 2014-07, 2014-05, 2014-01, 2013-10, 2013-09, 2013-07, 2013-06, 2013-05, 2013-04, 2013-03, 2013-01, 2012-12, 2012-10, 2012-09, 2012-08, 2012-06, 2012-05, 2012-04, 2012-03, 2012-01, 2011-10, 2011-09, 2011-08, 2011-07, 2011-06, 2011-05, 2011-04, 2011-02, 2011-01, 2010-11, 2010-10, 2010-09, 2010-08, 2010-07, 2010-05, 2010-04, 2010-03, 2010-02, 2010-01, 2009-12, 2009-11, 2009-10, 2009-09, 2009-08, 2009-07, 2009-06, 2009-05, 2009-04, 2009-03, 2009-02, 2009-01, 2008-12, 2008-11, 2008-10, 2008-09, 2008-08, 2008-07, 2008-06, 2008-05, 2008-04, 2008-03, 2008-02, 2008-01, 2007-12, 2007-11, 2007-10, 2007-09, 2007-08, 2007-07, 2007-06, 2007-05