Migrating to Postmodern 1.10

A number of backwards-incompatible changes are introduced in this version of the library ― there are a few small cleanups, and the database-access object system has been completely overhauled.

with-connection

The old with-connection form has been replaced by what used to be called with-connection*. This means that if you had code like this...

(with-connection ("my-db" "harry" "****" "localhost") ...)

... you should change it to ...

(with-connection (list "my-db" "harry" "****" "localhost") ...)

... since the whole list is now evaluated. Similarly, if you were using with-connection*, you should remove the asterisk.

Integrating custom data types

CL-postgres now exports ways to manipulate the way it reads values from query results. The old s-sql:sql-ize generic has been moved to cl-posgres:to-sql-string, and can be used to control the way values are written out when passed as arguments to prepared statements or inserted in S-SQL query forms.

simple-date is no longer depended on by CL-postgres and S-SQL, but uses the above interface to integrate itself. Load it after loading CL-postgres, and suitable readers and writers for its types will be registered. Integrating other date/time libraries is trivial.

Errors and reconnecting

In previous versions, only the database-connection-lost conditions offered a :reconnect restart. There are now various conditions offering this restart, all subtypes of database-connection-error, and the library tries its very best to wrap all hard-to-prevent errors with such a restart (socket errors, database shutdowns). The goal is that you can use this feature to cleanly and simply add functionality for recovering from connectivity problems and server restarts. If you still have issues here, please discuss them on the mailing list (universal error recovery is rather hard to test).

There is now also a large set of condition types exported from the cl-postgres-error package, which can make writing handler-case forms around database code a lot more pleasant. See cl-postgres/error.lisp for the list (or just cause the error you are interested in to be raised, and look at its type).

The DAO system

This is where upgrading might be somewhat painful. The old deftable macro has been dropped completely, in favour of the dao-class metaclass. The focus of this part of the library has shifted from defining tables to defining access objects. You can still generate simple CREATE TABLE statements using the dao-table-definition function, but this is intended to just be a shortcut. Table definition is now the responsibility of the library user, not the library.

So why this regression in functionality? It turned out that coupling access objects and table definitions like this was not such a good idea. You might want to create access objects for views, or for tables with all kinds of complicated constraints. Adding support for this to deftable would have turned it into an even bigger behemoth than it already was, and not fundamentally solve the problem.

So now we have a nice, clean DAO interface, and no schema-definition interface at all (create-template and friends were also dropped). The most notable change is probably that the :auto-id option is gone. This was very convenient but horribly 'magical'. If you had something like this:

(deftable product ()
  ((name :type string :initarg :name :accessor product-name)
   (weight :type float :initarg :weight :accessor product-weight))
  (:class-name product)
  (:auto-id t)
  (:indices (:unique name)))

(defun create-tables ()
  ; ...
  (create-table 'product))

The equivalent could look like this:

(defclass product ()
  ((id :col-type serial :initarg :id :accessor product-id)
   (name :col-type string :initarg :name :accessor product-name)
   (weight :col-type float :initarg :weight :accessor product-weight))
  (:keys id)
  (:metaclass dao-class))

(defun create-tables ()
  ; ...
  (execute (dao-table-definition 'product))
  (execute (:create-unique-index 'product-name :on 'product :fields 'name)))

Or you could explicitly create the id sequence and give the id field a :col-default of (:nextval "product_ids"), to have more control over the id generation.

The above example should give you a basic idea of the new interface: DAO classes are now created by regular class definitions. Instead of :type options, column slots should get :column or :col-type options. The semantics of creating and inserting DAOs have been slightly adjusted: There is no magic happening when you create a DAO instance (it used to fetch id values), except when you give make-instance a :fetch-defaults keyword argument, in which case it will query the database for the rows' default values, and put them in the instance. Usually, it is cleaner to not use this, since it generates extra queries and does not work for stuff like serial fields anyway, where no proper :col-default can be given. When an object is inserted into the database with insert-dao, some slots may be unbound. These will then, both in the database and in the object, be assigned values based on the column defaults. For example, if you have the above product class:

(defvar *p* (make-instance 'product :name "brick" :weight 2))
;; The id slot is unbound
(insert-dao *p*)
(print (product-id *p*))
;; Here it will have received a new id value

Note that this works even for serial types, since the defaults are fetched by giving the INSERT statement a RETURNING clause, so the association between default values and columns is handled by the database, not the DAO class.