Getting Started with cl-perec

So, you want to write a database-backed Lisp thang. You've certainly got plenty of options: elephant, postmodern, and cl-perec, to name a few.

Elephant you rule out because it doesn't play very well with non-elephant applications (last I checked, anyway). Postmodern you rule out because, while pretty close to what you want, cl-perec's associations are dreamy and your data involves a lot of relationships.

First, you'll need to get postgresql and cl-perec. For postgres, use the closest available package management system if at all possible. For cl-perec, because its list of dependencies is long and troublesome, I recommend acquisition using clbuild¹. That's how I did it, and it's worked out pretty well so far.

Second, we need to create a database, and a user who can access that database. See postgres' Manual for details.² It's long and complicated, so I'll see you next week.

Back already? Excellent. Now we need to tell cl-perec about our database. That starts with creating a class for the database connection. I don't know why, it just does.

(defclass database-connection
    (cl-perec:database-mixin
     cl-rdbms:postgresql-postmodern)
  ())

The required cl-perec:database-mixin provides some of the necessary machinations for our database-connection class to be recognized properly by cl-perec. cl-rdbms:postgresql-postmodern represents the underlying data store—you might also use cl-rdbms:oracle or cl-rdbms:sqlite, depending on which database you want.

Then we can create a variable to hold an instance of our new class. Or, in this case, we'll use the variable thoughtfully provided by cl-perec.⁸

(defparameter
  cl-perec:*database*
  (make-instance 'database-connection
    :connection-specification
    '(:database  "test_db"
      :user-name "test_user"
      :password  "test_password")))

Exciting, no? Finally, we can venture into the land of ORM and create our tables.⁷

(cl-perec:defpclass* generic-guy ()
  ((name      "Ted" :type string)
   (tie-color :blue :type (member :blue :green))))

Over in pgsql, we do a \dt and see … nothing. That's because cl-perec doesn't create the tables until you try to create an object using those tables. Since a database with no data is pretty useless, this isn't really a big deal. Still, if you really want to force those tables into existence even without data, cl-perec::ensure-exported is what you're looking for.³

cl-perec demands everything happen within a transaction, which means we're going to have to wrap everything we type at the REPL in a with-transaction so we can see its results over in pgsql right away. So, if you aren't already using it, look into slime-fuzzy-completion: it dramatically reduces the amount of typing necessary to produce a long symbol.

That out of the way, it's time to create our first generic-guy.

(cl-perec:with-transaction
  (make-instance 'generic-guy :tie-color :green)) =>
#<GENERIC-GUY :persistent t>

(inspect *) => ERROR

Whoops! You can't access the slots of a persistent object outside of a transaction. We're just playing around though, so let's opt out of that requirement.

(setf (cl-perec:persistent-p *) nil)

Now we can (inspect **) without worrying about transactions—just don't expect to see any changes reflected in the database!

Back in pgsql, we notice that all the table and column names created for us by cl-perec are prefixed by incredibly ugly underscores.

test_db=> \dt+

List of relations

Schema	Name	Type	Owner	Description
public	_generic_guy	table	test_user

test_db=> select * from _generic_guy;

_oid	_name	_tie_color
996143	Ted	1

This isn't really what we want because, well, why would we want it? We're using an underlying SQL database so other applications in other programming languages can also get at the data, and forcing an underscore prefix on them isn't really very nice. Fortunately, our heretofore seemingly pointless database-connection class can help us make changes without affecting any other cl-perec-using code.

;; I'm not a fan of having an underscore prefix
;; everything in postgres.
(defmethod cl-rdbms::calculate-rdbms-name
  ((db database-connection) thing name)
  "Cuts off the end of names that are too long and appends the
   hash of the original name.

   WARNING: This name mapping is not injective, different lisp
   names /may/ be mapped to the same rdbms name."
  (cl-rdbms::calculate-rdbms-name-with-utf-8-length-limit
    name
    cl-rdbms.postgresql::+maximum-rdbms-name-length+
    :prefix ""))

The usage of cl-rdbms internals is unfortunate, but I didn't see an easier way to eliminate the underscore prefix. (If you know of one, feel free to let me know.)

Unfortunately, now you're going to run into a bit of trouble: cl-perec (quite reasonably) stores the results of calculate-rdbms-name, which means old tables are going to continue using the underscore-prefixed names, while any new tables will use the prefix-less variants. Fortunately, we're just playing around so nuking the database and restarting the lisp image⁴ is a-okay, if suboptimal. (Redefining your class might work, too.)

Retracing our prior steps after we've redefined our name mapping, we can see the results in pgsql.

test_db=> \dt+

List of relations

Schema	Name	Type	Owner	Description
public	generic_guy	table	test_user

test_db=> select * from generic_guy;

_oid	name	tie_color
1127215	Ted	1

Much prettier!

Anyway, now that we have a generic_guy table, and Ted, the green-tied generic guy, it's time to start thinking about relationships. But before we can worry about relationships, we need somebody or something to which we can relate. We could model Ted's love life, but instead we'll model his employment. First, we'll need a heartless corporation.

(cl-perec:defpclass* corporation ()
  ((name         :type string)
   (evilness 5   :type (integer 0 10))))

(cl-perec:with-transaction
  (make-instance 'corporation
    :name "Initech"
    :evilness 8)) =>
#<CORPORATION :persistent t>

Then we can begin defining employee-employer relationships⁵ and make Ted work for Initech.

(cl-perec:defassociation*
  ((:class corporation
    :slot employees
    ;; NOT cl:set!
    :type (cl-perec:set generic-guy))
   (:class generic-guy
    :slot employer
    :type corporation)))

;; assuming *ted* and *initech* contain our
;; previously-created objects
(cl-perec:with-transaction
  ;; revive-instance imports an instance into the
  ;; current transaction
  (cl-perec:revive-instances *ted* *initech*)
  (setf (employer-of *ted*) *initech*))

Congratulations Ted, you've been hired! Note how cl-perec handles the other direction of the relationship for us.

(cl-perec:with-transaction
  (cl-perec:revive-instances *initech*)
  (employees-of *initech*)) =>
(#<GENERIC-GUY :persistent t>) ; '(Ted)

Also notice how cl-perec altered the tables to handle our new relationship all by itself.

test_db=> select * from generic_guy;

_oid	name	tie_color	employer_oid
1127215	Ted	1	1232276

test_db=> select * from corporation;

_oid	name	evilness
1232276	Initech	8

As you can see, in spite of some rather spartan documentation, cl-perec isn't too bad,⁶ so get out there and start defining your data. I'll be back with more when I get to that point in my application—but don't hold your breath 'cause I'm pretty slow. :)

Footnotes

1. clbuild is essentially a technical solution to the social problem of Lispers being terrible at proper releases. Like most open-source projects geared towards programmers, it assumes a POSIX system—if you're on Windows, you've got two options: install a posix system (in a VM is probably fine; on an old, unused box works well too), or shell out for AllegroCache (which has the advantage of actual documentation, though it's closer in spirit to Elephant).
2. I ended up with something like

   sudo -u postgres createdb …
   sudo -u postgres createuser …

   But your environment may differ. When I figure out how to do it a second time, I'll hopefully remember to update this with specifics.
3. e.g.,

   (cl-perec:with-transaction
     (cl-perec::ensure-exported
       (find-class 'generic-guy)))

4. Around this time I managed to bork SBCL's class definer and had to restart lisp if I wanted to define or alter any new classes, so I never bothered to look into what needed to change to start using the new, prettier names. You probably shouldn't change the name mapping in a production system anyway.
5. While you could achieve similar results by amending the generic-guy class to add an

   (employer nil :type (or corporation null))

   slot, you wouldn't get the otherwise-free employees-of method. Regardless, you'll need to know defassociation for M-N relationships.
6. Some of the coding conventions are a bit annoying (e.g., the #t/#f readmacros in place of t and nil), but that's a rant for another day.
7. cl-perec:defpclass* is a thin wrapper around defclass-star which sets the metaclass to cl-perec:persistent-class. defclass-star tries to provide sensible accessor name defaults (slot-name-of), among other things.
8. If you have multiple databases, or perhaps just multiple database connections, have a look at with-transaction*:

   (cl-perec:with-transaction* (:database *test-db*)
     &body))

   Of course, you could also just bind cl-perec:*database* yourself.

Continue to Persisting Simple Types with cl-perec