DAO Classes

Overview

You can work directly with the database or you can use a simple database-access-class (aka dao) which would cover all the fields in a row.

Postmodern allows you to have a relatively simple but straight forward matching of clos classes to a database table. In its simplest form, a dao object would contain the values of the rows of a database table. This is not intended as a full-fledged object-relational magic system ― while serious ORM systems have their place, they are notoriously hard to get right, and are outside of the scope of a humble SQL library like this.

At the heart of Postmodern's DAO system is the dao-class metaclass. It allows you to define classes for your database-access objects as regular CLOS classes. Some of the slots in these classes will refer to columns in the database.

Metaclass dao-class

Basic Dao Definition Examples

A simple dao definition could look like this:

(defclass users ()
  ((name :col-type string :initarg :name :accessor name)
   (creditcard :col-type (or db-null integer) :initarg :card :col-default :null)
   (score :col-type bigint :col-default 0 :accessor score)
   (payment-history :col-type (or (array integer) db-null)
                    :initarg :payment-history :accessor payment-history))
  (:metaclass dao-class)
  (:keys name))

In this case the name of the users will be treated as the primary key (the :keys parameter at the end) and the database table is assumed to be named users because that is the name of the class and there was no :table-name parameter provided. (It might be worth noting that "user" is a reserved word for Postgresql and using reserved words, while possible using quotes, is generally not worth the additional trouble they cause.)

In our example, the name and score slots cannot be null because :col-type does not provide for db-null as an optiona. The creditcard slot can be null and actually defaults to null. The :col-default :null specification ensures that the default in the database for this field is null, but it does not bound the slot to a default form. Thus, making an instance of the class without initializing this slot will leave it in an unbound state.

The payment-history slot is matched to a Postgresql column named payment_history (remember that Postgresql uses underscores rather than hyphens) and that Postgresql column is an array of integers. If we wanted a two dimensional array of integers, the col-type would look like:

:col-type (or (array (array integer)) db-null)

If the value contained in the Postgresql slot payment-history is a common lisp array, Postmodern will seamless handle the conversion to and from the common lisp array and the Postgresql array.

An example of a class where the keys are set as multiple column keys is here:

(defclass points ()
  ((x :col-type integer :initarg :x
      :reader point-x)
   (y :col-type integer :initarg :y
      :reader point-y)
   (value :col-type integer :initarg :value
          :accessor value))
  (:metaclass dao-class)
  (:keys x y))

In this case, retrieving a points record would look like the following where 12 and 34 would be the values you are looking to find in the x column and y column respectively.:

(get-dao 'points 12 34)

Now look at a slightly more complex example.

(defclass country ()
  ((id :col-type integer :col-identity t :accessor id)
   (name :col-type string :col-unique t :check (:<> 'name "")
         :initarg :name :reader country-name)
   (inhabitants :col-type integer :initarg :inhabitants
                :accessor country-inhabitants)
   (sovereign :col-type (or db-null string) :initarg :sovereign
              :accessor country-sovereign)
   (region-id :col-type integer :col-references ((regions id))
              :initarg :region-id :accessor region-id))
  (:documentation "Dao class for a countries record.")
  (:metaclass dao-class)
  (:table-name countries))

In this example we have an id column which is specified to be an identity column. Postgresql will automatically generate a sequence of of integers and this will be the primary key.

We have a name column which is specified as unique and is not null.

We have a region-id column which references the id column in the regions table. This is a foreign key constraint and Postgresql will not accept inserting a country into the database unless there is an existing region table with an id that matches this number. Postgresql will also not allow deleting a region if there are countries that reference that region's id. If we wanted Postgresql to delete countries when regions are deleted, that column would be specified as:

(region-id :col-type integer :col-references ((regions id) :cascade)
           :initarg :region-id :accessor region-id)

Now you can see why the double parens.

We also specified that the table name is not "country" but "countries". (Some style guides recommend that table names be plural and references to rows be singular.) NOTE: You can provide a fully qualified table name. In other words, if you have

(:table-name a.countries)

Postmodern will look for the countries table in the "A" schema.

When inheriting from DAO classes, a subclass' set of columns also contains all the columns of its superclasses. The primary key for such a class is the union of its own keys and all the keys from its superclasses. Classes inheriting from DAO classes should probably always use the dao-class metaclass themselves.

When a DAO is created with make-instance, the :fetch-defaults keyword argument can be passed, which, when T, will cause a query to fetch the default values for all slots that refers to columns with defaults and were not bound through initargs. In some cases, such as serial and identity columns, which have an implicit default, this will not work. You can work around this by creating your own sequence, e.g. "my_sequence", and defining a (:nextval "my_sequence") default.

Finally, DAO class slots can have an option :ghost t to specify them as ghost slots. These are selected when retrieving instances, but not written when updating or inserting, or even included in the table definition. The only known use for this to date is for creating the table with (oids=true), and specify a slot like this:

(oid :col-type integer :ghost t :accessor get-oid)

Slot Options

The slot definitions in a table have several additional optional keyword parameters:

  • :col-type To specify that a slot refers to a column, give it a :col-type option containing an S-SQL type expression (useful if you want to be able to derive a table definition from the class definition). The (or db-null integer) form is used to indicate a column can have NULL values otherwise the column will be treated as NOT NULL.
  • :col-default When using dao-table-definition, having :col-default in a slot definition will tell Postgresql to use this default value if no value is provided when inserting a new row. In the following example, Postgresql would insert the default value of 12 if no value was provided when inserting a new row.

    (defclass col-default ()
      ((id :col-type integer :col-identity t :accessor id)
       (name :col-type text :col-unique t :col-check (:<> 'name "")
             :initarg :name :accessor name :col-collate "de_DE.utf8")
       (data :col-type integer :col-default 12 :accessor data
             :initarg :data))
      (:metaclass dao-class)
      (:table-name col-default))
    
  • :col-identity Often used for an id slot when you are going to define a Postgresql table by referring to this dao and you want the primary key to be an identity column automatically generated by Postgresql. This column must have a :col-type integer.
  • :col-primary-key When using dao-table-definition, specifying :col-primary-key in a slot definition will tell Postgresql that this column is the primary key for the table. You will also need to set :unique to t for this column. In the following example, the username is the primary key, not the id column.

    (defclass test-data-col-primary-key ()
      ((id :col-type integer  :accessor id)
       (username :col-type text :col-primary-key t :col-unique t  :initarg :username :accessor username)
       (department-id :col-type integer :initarg :department-id :accessor department-id))
      (:metaclass dao-class)
      (:table-name users1))
    
  • :col-unique When you want to specify that the values in the table column associated with this slot must be unique. See an example in the username slot definition in the discussion of :col-primary-key immediately above.
  • :col-export Specifies a function name to be called when you have to convert a slot value from a CL datatype to a Postgresql datatype that Postmodern does not automatically handle. More about this below.
  • :col-import Specifies a function name to be called when you have to convert a slot value from a Postgresql datatype to a CL datatype that Postmodern does not automatically handle. More about this below.
  • :col-name You can use the :col-name initarg (whose unevaluated value will be passed to to-sql-name) to specify the slot's column's name. In other words, you want a slot name that is different from the database table's column name. This tells Postmodern what that database table column's name when getting data from a table. This is NOT used in dao-table-definition in creating tables.

    (defclass test-col-name ()
      ((a :col-type string :col-name aa :initarg :a :accessor test-a)
       (b :col-type string :col-name bb :initarg :b :accessor test-b)
       (c :col-type string              :initarg :c :accessor test-c)
       (from :col-type string :col-name from :initarg :d :accessor test-d)
       (to-destination :col-type string :col-name to :initarg :e :accessor test-e))
      (:metaclass dao-class)
      (:keys a))
    
  • :col-collate When using dao-table-definition, having :col-collate in a slot definition will tell Postgresql to use this collation when sorting this column. You can have different collations in different columns of the same table. In the following example, Postgresql would use a German UTF8 collation in the name column.
(defclass col-collate ()
  ((id :col-type integer :col-identity t :accessor id)
   (name :col-type text :col-unique t :col-check (:<> 'name "")
         :initarg :name :accessor name :col-collate "de_DE.utf8")
  (:metaclass dao-class)
  (:table-name col-collate))
  • :col-check When using dao-table-definition, having :col-check in a slot definition will tell Postgresql to ensure that any data entered into that column must meet certain requirements. In the following example, Postgresql would ensure that values in the name column can never be an empty string.

    (defclass col-check ()
      ((id :col-type integer :col-identity t :accessor id)
       (name :col-type text :col-unique t :col-check (:<> 'name "")
             :initarg :name :accessor name :col-collate "de_DE.utf8")
      (:metaclass dao-class)
      (:table-name col-check))
    
  • :col-references This specifies that the column references a column in another table. In the following example, the department-id slot references the id column in the departments table:

    (defclass test-data-col-identity-with-references ()
      ((id :col-type integer :col-identity t :accessor id :col-primary-key t)
       (username :col-type text :unique t :initarg :username :accessor username)
       (department-id :col-type integer :col-references ((departments id))
                      :initarg :department-id :accessor department-id))
      (:metaclass dao-class)
      (:table-name usersr))
    

Table options

DAO class definitions support two extra class options: :table-name to give the name of the table that the class refers to (defaults to the class name), and :keys to provide a set of primary keys for the table if they have not been specified in a single column. If more than one key is provided, this creates a multi-column primary key and all keys must be specified when using operations such as update-dao and get-dao. When no primary keys are defined, operations such as update-dao and get-dao will not work.

IMPORTANT: Class finalization for a dao class instance are wrapped with a thread lock. However, any time you are using threads and a class that inherits from other classes, you should ensure that classes are finalized before you start generating threads that create new instances of that class.

Dao Class Inheritance

The following dao class is related to ruby active record with the slots updated-at and created-at. Both slots are initialized automatically. Updated-at is automatically updated every time you use insert-dao whereas created-at will remain the same even if you change entry data.

This code is written with the local-time library, but simple-time should also work:

(defun timestamp-to-postgre-time (timestamp)
  (local-time:format-timestring nil timestamp))

(defun update-time (old-time)
  (declare (ignore old-time))
  (local-time:format-timestring nil (now)))

(defclass active-record ()
  ((created-at :col-type timestamp
               :accessor created-at
               :description "The timestamp when the entry was created."
               :initform (local-time:now)
               :col-export timestamp-to-postgre-time)
   (updated-at :col-type timestamp
               :accessor updated-at
               :description "The timestamp when the entry was last updated."
               :initform (local-time:now)
               :col-export update-time))
  (:metaclass dao-class))

By using active-record as superclass, subclasses inherit the same convenient features:

(defclass user (active-record)
  ((user-id :col-type bigint
            :primary-key t
            :reader user-id
            :col-unique t
            :col-identity t)
   (name :col-type (or db-null string)
         :initarg :last-name
         :col-default :null
         :accessor last-name)
   (email :col-type string
          :col-unique t
          :initarg :email
          :accessor email))
  (:metaclass pomo:dao-class))

Next is an example for using inheritance with foreign-keys. For that we define a dao-class (which will not be created as table) whereas the only column is the primary key user-id from the user table:

(defclass user-id-foreign-key ()
  ((user-id :col-type integer
            :initarg :user-id
            :reader user-id
            :col-references ((user user-id))
            :col-primary-key t))
  (:metaclass pomo:dao-class))

Now we can add user-id-foreign-key as superclass in tables where we want to use user-id as foreign key:

(defclass user-credential (user-id-foreign-key active-record)
  ((password-hash :col-type string
                  :initarg :password-hash
                  :accessor password-hash)
   (salt :col-type integer
         :initarg :salt
         :accessor salt))
  (:metaclass pomo:dao-class))

Define-Dao-Class Macro (New to version 1.33.12)

New to Postmodern version 1.33.12 (thank you Killianmh) is a macro that makes defining a dao class slightly easier. It is like defclass except two postmodern specific changes:

  1. The dao-class metaclass options is automatically added.
  2. If second value in a slot is not a keyword, it is assumed to be col-type.

Example:

(define-dao-class id-class ()
((id integer :initarg :id :accessor test-id)
 (email :col-type text :initarg :email :accessor email))
(:keys id))

Dao Query and Selection

The base query and selection methods and macros are:

  • get-dao A method that create an instance of a dao object class based on a query of the rows of a database table based on its primary keys.
  • select-dao: A macro to select dao objects for the rows in the associated table for which the given test (either an S-SQL expression or a string) holds. When sorting arguments are given, which can also be S-SQL forms or strings, these are used to sort the result.
  • do-select-dao: A macro like select-dao but iterates over the results rather than returning them.
  • query-dao: A macro that executes a given query and returns the results as daos of a given type.
  • do-query-dao: A function which operates like query-dao but iterates over the results rather than returning them.

Dao Reference

Dao Export and Import Functions (Postmodern v. 1.33.1 and newer)

There may be times when the types of values in a dao slot do not have comparable types in Postgresql. For purposes of the following example, assume you have slots that you want to contain lists. Postgresql does not have a "list" data type. Postgresql arrays must be homogeneous but CL lists do not have that limitation. What to do?

One method would be to use text columns or jsonb columns in Postgresql and have functions that convert as necessary going back and forth. In the following example we will use text columns in Postgresql and write CL list data to string when we "export" the data to Postgresql and then convert from string when we "import" the data from Postgresql into a dao-class instance.

One use of the col-import and col-export capabilities is with respect to enums: storing a CL enum as text in the database and then "re-symbolizing them" them again when the DAO classes are read.

Consider the following dao-class definition. We have added additional column keyword parameters :col-export and :col-import. These parameters refer to functions which will convert the values from that slot to a valid Postgresql type (in our example, a string) on export to the database and from that Postgresql type to the type we want in this slot on import from the database (in this example a symbol). NOTE that you use an unquoted symbol for the functions being specified in :col-export and :col-import.

(defclass orchard ()
((id :col-type integer  :col-identity t :accessor id :col-primary-key t)
 (name :col-type text :col-unique t :col-check (:<> 'name "")
       :initarg :name :accessor name)
 (orchard-type :col-type orchard_type_enum
             :initarg :orchard-type :accessor orchard-type
             :col-import intern
             :col-export string))
(:metaclass dao-class)
(:table-name orchards))

Here we have the benefit that our export and import functions are already predefined by CL.

To make things slightly more interesting, we create a different dao which has two slots which are going to contain lists, but one will export to a Postgresql column that contains strings and the other will export to a Postgresql column that contains arrays of integers.

(defclass listy ()
  ((id :col-type integer :col-identity t :accessor id :col-primary-key t)
   (name :col-type text :col-unique t :col-check (:<> 'name "")
         :initarg :name :accessor name)
   (r-list :col-type (or text db-null) :initarg :r-list :accessor r-list
           :col-export list->string :col-import string->list)
   (l-array :col-type (or (array integer) db-null)
            :initarg :l-array :accessor l-array
            :col-export list->arr :col-import array->list))
  (:metaclass dao-class)
  (:table-name listy))

Now we are going to define the import functions. When writing your import functions, pay attention to how you want to handle nil or :NULL values as well as how you might want to error check the conversion from a Postgresql datatype to a CL datatype. Just to show some of the differences, we are going to translate :NULL strings in Postgresql to :NULL in common lisp and we are going to translate :NULL arrays in Postgresql to nil in common lisp.

(defun string->list (str)
  "Take a string representation of a list and return a lisp list.
  Note that you need to handle :NULLs."
  (cond ((eq str :NULL)
         :NULL)
        (str
         (with-input-from-string (s str) (read s)))
        (t nil)))

(defun array->list (arry)
  "Here we have decided that we want the list be be nil rather than :NULL if the array is empty."
  (cond ((eq arry :NULL)
         nil)
        ((vectorp arry)
         (coerce arry 'list))
        (t nil)))

And now the export functions. In our example we are just going to be using format to write the CL value to a string unless it is not a list. You are responsible for writing an export function that does what you need. This example just tells Postgresql to insert :NULL if the slot value is not a list. In real life you would need more error checking and condition handling.

The list to array export function inserts :NULL if not a list and otherwise coerces the list to a vector so that Postgresql will accept it as an array.

(defun list->string (lst)
  "Here we have decided to insert :null if the input list is nil."
  (if (listp lst)
      (format nil "~a" lst)
      :null))

(defun list->arr (lst)
  (if (null lst)
      :null
      (coerce lst 'vector)))

method dao-keys (class)

→ list

Returns list of slot names that are the primary key of DAO class. This is likely interesting if you have primary keys which are composed of more than one slot. Pay careful attention to situations where the primary key not only has more than one column, but they are actually in a different order than they are in the database table itself. You can check this with the internal find-primary-key-info function. Obviously the table needs to have been defined. The class must be quoted.

(pomo:find-primary-key-info 'country1)

(("name" "text") ("id" "integer"))

method dao-keys (dao)

→ list

Returns list of values that are the primary key of dao. Explicit keys takes priority over col-identity which takes priority over col-primary-key.

This is likely interesting if you have primary keys which are composed of more than one slot. Pay careful attention to situations where the primary key not only has more than one column, but they are actually in a different order than they are in the database table itself. Obviously the table needs to have been defined. You can provide a quoted class-name or an instance of a dao.

method find-primary-key-column

→ symbol

Loops through a class's column definitions and returns the first column name that has bound either col-identity or col-primary-key.

method dao-exists-p (dao)

→ boolean

Test whether a row with the same primary key as the given dao exists in the database. Will also return NIL when any of the key slots in the object are unbound.

method make-dao (type &rest args &key &allow-other-keys)

→ dao

Combines make-instance with insert-dao. Make the instance of the given class and insert it into the database, returning the created dao.

method fetch-defaults (dao)

→ dao if there were unbound slots with default values, otherwise nil

Used to fetch the default values of an object on creation. An example would be creating a dao object with unbounded slots. Fetch-defaults could then be used to fetch the default values from the database and bind the unbound slots which have default values. E.g.

(let ((dao (make-instance 'test-data :a 23)))
  (pomo:fetch-defaults dao))

method find-primary-key-column (class)

→ symbol

Loops through a class's column definitions and returns the first column name that has bound either col-identity or col-primary-key. Returns a symbol.

macro define-dao-finalization (((dao-name class) &rest keyword-args) &body body)

Create an :around-method for make-dao. The body is executed in a lexical environment where dao-name is bound to a freshly created and inserted DAO. The representation of the DAO in the database is then updated to reflect changes that body might have introduced. Useful for processing values of slots with the type serial, which are unknown before insert-dao.

method get-dao (type &rest keys)

→ dao

Get the single DAO object from the row that has the given primary key values, or NIL if no such row exists. Objects created by this function will have initialize-instance called on them (after loading in the values from the database) without any arguments ― even :default-initargs are skipped. The same goes for select-dao and query-dao.

(get-dao 'country "The Netherlands")
#<COUNTRY {1010F0DCF3}>

From an sql perspective, the standard call to get-dao translates as:

select * from table

NOTE: if you have added fields to the database table without updating the class definition, get-dao and select-dao will throw errors. This may cause your application to appear to hang unless you have the necessary condition handling in your code. Usually this will only happen during development, so throwing an error is not a bad idea. If you want to ignore the errors, set *ignore-unknown-columns* to t.

macro select-dao (type &optional (test t) &rest sort)

→ list

Select DAO objects for the rows in the associated table for which the given test (either an S-SQL expression or a string) holds. When sorting arguments are given, which can also be S-SQL forms or strings, these are used to sort the result.

(Note that, if you want to sort, you have to pass the test argument.)

(select-dao 'country)
(#<COUNTRY {101088F6F3}> #<COUNTRY {101088FAA3}>)
2

(select-dao 'country (:> 'inhabitants 50000000))
NIL
0

(select-dao 'country (:> 'inhabitants 5000000))
(#<COUNTRY {10108AD293}>)
1

(select-dao 'country (:> 'inhabitants 5000))
(#<COUNTRY {10108CA773}> #<COUNTRY {10108CAB23}>)
2

(select-dao 'country (:> 'inhabitants 5000) 'name) ;sorted by name
(#<COUNTRY {10108EF423}> #<COUNTRY {10108EF643}>)

(mapcar 'country-name (select-dao 'country (:> 'inhabitants 5000) 'name))
("Croatia" "The Netherlands")

(mapcar 'country-name (select-dao 'country (:> 'inhabitants 5000)))
("The Netherlands" "Croatia")

If for some reason, you wanted the list in reverse alphabetical order, then:

(select-dao 'country (:> 'id  0) (:desc 'name))

macro do-select-dao (((type type-var) &optional (test t) &rest sort) &body body)

Like select-dao, but iterates over the results rather than returning them. For each matching DAO, body is evaluated with type-var bound to the DAO instance.

Example:

(do-select-dao (('user user) (:> 'score 10000) 'name)
  (pushnew user high-scorers))

macro query-dao (type query &rest args)

→ list of daos

Execute the given query (which can be either a string or an S-SQL expression) and return the result as DAOs of the given type. If the query contains placeholders ($1, $2, etc) their values can be given as extra arguments. The names of the fields returned by the query must either match slots in the DAO class, or be bound through with-column-writers.

function do-query-dao (((type type-var) query &rest args) &body body)

→ list of daos

Like query-dao, but iterates over the results rather than returning them. For each matching DAO, body is evaluated with type-var bound to the instance.

Example:

(do-query-dao (('user user) (:order-by (:select '* :from 'user :where (:> 'score 10000)) 'name))
  (pushnew user high-scorers))

variable *ignore-unknown-columns*

Normally, when get-dao, select-dao, or query-dao finds a column in the database that's not in the DAO class, it will raise an error. Setting this variable to a non-NIL will cause it to simply ignore the unknown column. This allows you to create daos which are subsets of a table.

method insert-dao (dao)

→ dao

Insert the given dao into the database. Column slots of the object which are unbound implies the database defaults. Hence, if these columns has no defaults defined in the database, the the insertion of the dao will be failed. (This feature only works on PostgreSQL 8.2 and up.)

method update-dao (dao)

→ dao

Update the representation of the given dao in the database to the values in the object. This is not defined for tables that do not have any non-primary-key columns. Raises an error when no row matching the dao exists.

function save-dao (dao)

→ boolean

Tries to insert the given dao using insert-dao. If the dao has unbound slots, those slots will be updated and bound by default data triggered by the database. If this raises a unique key violation error, it tries to update it by using update-dao instead. In this case, if the dao has unbound slots, updating will fail with an unbound slots error.

Be aware that there is a possible race condition here ― if some other process deletes the row at just the right moment, the update fails as well. Returns a boolean telling you whether a new row was inserted.

This function is unsafe to use inside of a transaction ― when a row with the given keys already exists, the transaction will be aborted. Use save-dao/transaction instead in such a situation.

See also: upsert-dao.

function save-dao/transaction (dao)

→ boolean

The transaction safe version of save-dao. Tries to insert the given dao using insert-dao. If this raises a unique key violation error, it tries to update it by using update-dao instead. If the dao has unbound slots, updating will fail with an unbound slots error. If the dao has unbound slots, those slots will be updated and bound by default data triggered by the database.

Be aware that there is a possible race condition here ― if some other process deletes the row at just the right moment, the update fails as well. Returns a boolean telling you whether a new row was inserted.

Acts exactly like save-dao, except that it protects its attempt to insert the object with a rollback point, so that a failure will not abort the transaction.

See also: upsert-dao.

method upsert-dao (dao)

→ dao

Like save-dao or save-dao/transaction but using a different method that doesn't involve a database exception. This is safe to use both in and outside a transaction, though it's advisable to always do it in a transaction to prevent a race condition. The way it works is:

If the object contains unbound slots, we call insert-dao directly, thus the behavior is like save-dao.

Otherwise we try to update a record with the same primary key. If the PostgreSQL returns a non-zero number of rows updated it treated as the record is already exists in the database, and we stop here.

If the PostgreSQL returns a zero number of rows updated, it treated as the record does not exist and we call insert-dao.

The race condition might occur at step 3 if there's no transaction: if UPDATE returns zero number of rows updated and another thread inserts the record at that moment, the insertion implied by step 3 will fail.

Note, that triggers and rules may affect the number of inserted or updated rows returned by PostgreSQL, so zero or non-zero number of affected rows may not actually indicate the existence of record in the database.

This method returns two values: the DAO object and a boolean (T if the object was inserted, NIL if it was updated).

IMPORTANT: This is not the same as insert on conflict (sometimes called an upsert) in Postgresq. An upsert in Postgresql terms is an insert with a fallback of updating the row if the insert key conflicts with an already existing row. An upsert-dao in Postmodern terms is the reverse. First you try updating an existing object. If there is no existing object to oupdate, then you insert a new object.

method delete-dao (dao)

Delete the given dao from the database.

function dao-table-name (class)

→ string

Get the name of the table associated with the given DAO class (or symbol naming such a class).

function dao-table-definition (class)

→ string

Given a DAO class, or the name of one, this will produce an SQL query string with a definition of the table. This is just the bare simple definition, so if you need any extra indices or or constraints, you'll have to write your own queries to add them, in which case look to s-sql's create-table function.

macro with-column-writers ((&rest writers) &body body)

Provides control over the way get-dao, select-dao, and query-dao read values from the database. This is not commonly needed, but can be used to reduce the amount of queries a system makes. writers should be a list of alternating column names (strings or symbols) and writers, where writers are either symbols referring to a slot in the objects, or functions taking two arguments ― an instance and a value ― which can be used to somehow store the value in the new instance. When any DAO-fetching function is called in the body, and columns matching the given names are encountered in the result, the writers are used instead of the default behaviour (try and store the value in the slot that matches the column name).

An example of using this is to add some non-column slots to a DAO class, and use query-dao within a with-column-writers form to pull in extra information about the objects, and immediately store it in the new instances.

Another example would be to convert something that is in one format in Postgresql e.g. a string to something else in your dao. Suppose your dao has slots that contain a regular list, an alist and a plist but for whatever reason the data is stored in Postgresql as text:

(defclass listy ()
  ((id :col-type integer :col-identity t :accessor id)
   (name :col-type text :col-unique t :col-check (:<> 'name "")
         :initarg :name :accessor name)
   (rlist :col-type text :initarg :rlist :accessor rlist)
   (alist :col-type text :initarg :alist :accessor alist)
   (plist :col-type text :initarg :plist :accessor plist))
  (:metaclass dao-class)
  (:table-name listy))

Table definition and creation using a dao

It can be useful to have the SQL statements needed to build an application's tables available from the source code, to do things like automatically deploying a database. The following macro and functions allow you to group sets of SQL statements under symbols, with some shortcuts for common elements in table definitions.

macro deftable (name &body definition)

Define a table. name can be either a symbol or a (symbol string) list. In the first case, the table name is derived from the symbol's name by S-SQL's rules. In the second case, the name is given explicitly. The body of definitions can contain anything that evaluates to a string, as well as S-SQL expressions. The variables *table-name* and *table-symbol* are bound to the relevant values in the body. Note that the evaluation of the definition is ordered, so you'll generally want to create your table first and then define indices on it.

variable *table-name*

Used inside deftable to find the name of the table being defined.

variable *table-symbol*

Used inside deftable to find the symbol naming the table being defined.

function !dao-def ()

Should only be used inside a deftable form. Define this table using the corresponding DAO class' slots. Adds the result of calling dao-table-definition on table-symbol to the definition.

function !index (&rest columns), !unique-index (&rest columns)

Used inside a deftable form. Define an index on the table being defined. The columns can be given as symbols or strings.

function !foreign (target fields &rest target-fields/on-delete/on-update/deferrable/initially-deferred)

Used inside a deftable form. Add a foreign key to the table being defined. target-table is the referenced table. columns is a list of column names or single name in this table, and, if the columns have different names in the referenced table, target-columns must be another list of column names or single column name of the target-table, or :primary-key to denote the column(s) of the target-table's primary key as referenced column(s).

The on-delete and on-update arguments can be used to specify ON DELETE and ON UPDATE actions, as per the keywords allowed in create-table. In addition, the deferrable and initially-deferred arguments can be used to indicate whether constraint checking can be deferred until the current transaction completed, and whether this should be done by default. Note that none of these are really &key arguments, but rather are picked out of a &rest arg at runtime, so that they can be specified even when target-columns is not given.

function !unique (target-fields &key deferrable initially-deferred)

Constrains one or more columns to only contain unique (combinations of) values, with deferrable and initially-deferred defined as in !foreign

function create-table (symbol)

Takes the name of a dao-class and creates the table identified by symbol by executing all forms in its definition as found in the *tables* list.

function create-all-tables ()

Creates all defined tables.

function create-package-tables (package)

Creates all tables identified by symbols interned in the given package.

variables *table-name*, *table-symbol*

Used inside deftable to find the name of the table being defined.

Used inside deftable to find the symbol naming the table being defined.

function drop-table (table-name &key if-exists cascade)

If a table exists, drop a table. Available additional key parameters are :if-exists and :cascade.

Out of Sync Dao Objects

What Happens when dao classes are out of sync with the database table? Let's establish our baseline

(defclass test-data ()
  ((id :col-type serial :initarg :id :accessor test-id)
   (a :col-type (or (varchar 100) db-null) :initarg :a :accessor test-a)
   (b :col-type boolean :col-default nil :initarg :b :accessor test-b)
   (c :col-type integer :col-default 0 :initarg :c :accessor test-c)
   (d :col-type numeric :col-default 0.0 :initarg :d :accessor test-d))
  (:metaclass dao-class)
  (:table-name dao-test)
  (:keys id))

#<DAO-CLASS S-SQL-TESTS::TEST-DATA>

(execute (dao-table-definition 'test-data))

Now we define a class that uses the same table, but does not have all the columns.

(defclass test-data-short ()
  ((id :col-type serial :initarg :id :accessor test-id)
   (a :col-type (or (varchar 100) db-null) :initarg :a :accessor test-a))
  (:metaclass dao-class)
  (:table-name dao-test)
  (:keys id))

We create an instance of the shortened class and try to save it, then check the results.

(let ((dao (make-instance 'test-data-short :a "first short")))
  (save-dao dao))

(query (:select '* :from 'dao-test) :alists)
(((:ID . 1) (:A . "first short") (:B) (:C . 0) (:D . 0)))

It was a successful save, and we see that the missing columns took their default values.

Now we define a shortened class, but the a slot is now numeric or null instead of a string and try to save it and check it.

(defclass test-data-short-wrong-1 ()
  ((id :col-type serial :initarg :id :accessor test-id)
   (a :col-type (or numeric db-null) :initarg :a :accessor test-a))
  (:metaclass dao-class)
  (:table-name dao-test)
  (:keys id))

(let ((dao (make-instance 'test-data-short-wrong-1 :a 12.75)))
  (save-dao dao))

(query (:select '* :from 'dao-test) :alists)

(((:ID . 1) (:A . "first short") (:B) (:C . 0) (:D . 0))
 ((:ID . 2) (:A . "12.75") (:B) (:C . 0) (:D . 0))

Notice that the 12.75 has been converted into a string when it was saved. Postgresql did this automatically. Anything going into a text or varchar column will be converted to a string.

Now we will go the other way and define a dao with the right number of columns, but col d is a string when the database expects a numeric and check that.

(defclass test-data-d-string ()
  ((id :col-type serial :initarg :id :accessor test-id)
   (a :col-type (or (varchar 100) db-null) :initarg :a :accessor test-a)
   (b :col-type boolean :col-default nil :initarg :b :accessor test-b)
   (c :col-type integer :col-default 0 :initarg :c :accessor test-c)
   (d :col-type text :col-default "" :initarg :d :accessor test-d))
  (:metaclass dao-class)
  (:table-name dao-test)
  (:keys id))

(let ((dao (make-instance 'test-data-d-string :a "D string" :b nil :c 14
                                              :d "Trying string")))
  (save-dao dao))

Database error 22P02: invalid input syntax for type numeric: "Trying string"
QUERY: INSERT INTO dao_test (d, c, b, a) VALUES (E'Trying string', 14, false, E'D string') RETURNING id
[Condition of type DATA-EXCEPTION]

Ok. That threw a data exception. What happens if we try to force a numeric into an integer column?

(let ((dao (make-instance 'test-data-d-string :a "D string" :b nil :c 14.37
                                              :d 18.78)))
  (save-dao dao))

Database error 22P02: invalid input syntax for type integer: "14.37"
[Condition of type CL-POSTGRES-ERROR:DATA-EXCEPTION]

Ok. Postgresql is enforcing the types.

(let ((dao (make-instance 'test-data-d-string :a "D string" :b nil :c 14
                                              :d 18.78)))
  (save-dao dao))

(query (:select '* :from 'dao-test) :alists)
(((:ID . 1) (:A . "first short") (:B) (:C . 0) (:D . 0))
 ((:ID . 2) (:A . "12.75") (:B) (:C . 0) (:D . 0))
 ((:ID . 3) (:A . "D string") (:B) (:C . 14) (:D . 939/50)))

Notice that postmodern returned a ratio 939/50 for the numeric 18.78.

We have looked at saving daos. Now look at returning a dao from the database where the dao definition is different than the table definition. First checking to see if we can get a correct dao back.

(get-dao 'test-data 3)
#<TEST-DATA {100C82AA33}>

Ok. That worked as expected.

Second using a shortened dao that is correct in type of columns, but incorrect n the number of columns compared to the database table.

(get-dao 'test-data-short 3)
No slot named b in class TEST-DATA-SHORT. DAO out of sync with table, or
incorrect query used.
[Condition of type SIMPLE-ERROR]

Restarts:
0: [RETRY] Retry SLIME REPL evaluation request.
1: [*ABORT] Return to SLIME's top level.
2: [ABORT] abort thread (#<THREAD "new-repl-thread" RUNNING {100C205083}>)

Not only did it throw an exception, but I needed to actually use an interrupt from the repl to get back in operation. And then use (reconnect *database*). Very Bad result.

THIS ERROR IS CONTROLLABLE BY THE VARIABLE *IGNORE-UNKNOWN-COLUMNS*

Now if we setf the default global variable *ignore-unknown-columns* to t

(setf *ignore-unknown-columns* t)

(get-dao 'test-data-short 3)
#<TEST-DATA-SHORT {10054DFED3}>

(describe (get-dao 'test-data-short 3))
#<TEST-DATA-SHORT {100B249783}>
[standard-object]

Slots with :INSTANCE allocation:
ID                             = 3
A                              = "D string"

We now have a dao that is a subset of the database table it pulled from. Just to validate that:

(query (:select '* :from 'dao-test :where (:= 'id 3)))

((3 "D string" NIL 14 939/50))

Just to be thorough, let's use a dao that has more slots than the database table.

(defclass test-data-long ()
  ((id :col-type serial :initarg :id :accessor test-id)
   (a :col-type (or (varchar 100) db-null) :initarg :a :accessor test-a)
   (b :col-type boolean :col-default nil :initarg :b :accessor test-b)
   (c :col-type integer :col-default 0 :initarg :c :accessor test-c)
   (d :col-type numeric :col-default 0.0 :initarg :d :accessor test-d)
   (e :col-type text :col-default "sell by date" :initarg :e :accessor test-e))
  (:metaclass dao-class)
  (:table-name dao-test)
  (:keys id))

Now if we make an instance of this dao and try to save it in the dao-class table:

(let ((dao (make-instance 'test-data-long :a "first short" :d 37.3)))
  (save-dao dao))

Database error 42703: column "e" does not exist
QUERY: INSERT INTO dao_test (d, a)  VALUES ($1, $2) RETURNING e, c, b, id
[Condition of type CL-POSTGRES-ERROR:UNDEFINED-COLUMN]

Postgresql rejected the attempted insert with an undefined column error.

Introduction to Multi-table dao class objects

Postmodern's dao-class objects are not required to be tied down to a specific table. They can be used simply as classes to hold data for whatever purpose your application may use.

For this introduction, we will use two sets of tables: (1) country-d and region-d and (2) country-n and region-n. In each case the country table will have a foreign key tied to a region.

A foreign key is a "constraint" referencing a primary key in another table. The table containing the foreign key is the referencing or child table and the table referenced by the foreign key is the referenced or parent table. The foreign key enforces a requirement that the child table column refering to another table must refer to a row that exists in the other table. In other words, you cannot create a row in table country-d that references a region-d name "Transylvania" if the region-d name "Transylvania" does not yet exist in the region-d table. At the same time, you could not later delete the region-d row with "Transylvania" if the country-d row referencing it still exists.

Do you remember the slightly more complicated version of country from earlier on the page?

(defclass country ()
  ((id :col-type integer :col-identity t :accessor id)
   (name :col-type string :col-unique t :check (:<> 'name "")
         :initarg :name :reader country-name)
   (inhabitants :col-type integer :initarg :inhabitants
                :accessor country-inhabitants)
   (sovereign :col-type (or db-null string) :initarg :sovereign
              :accessor country-sovereign)
   (region-id :col-type integer :col-references ((regions id))
              :initarg :region-id :accessor region-id))
  (:documentation "Dao class for a countries record.")
  (:metaclass dao-class)
  (:table-name countries))

That one specified a foreign key reference in the region-id column, so we cannot insert the data from a country dao unless there is already a region table with an id column equal to the region-id in the country dao.

Lets look at two slightly different ways of handling countries and regions.

In our first set of tables, country-d will have a region column that references the name column in a region-d table (so the name column in region-d must be the primary key for region-d).

This looks relatively straight forward and it is in this simple case. Things start getting more complicated if you start having to reference a table where there are many items with the same name. An example would be tracking library books. There may be multiple copies of a book title, but you need to know which book was checked out to which library patron. In these types of situations, the primary key cannot be the name of the region, it needs to reference some particular id.

In our second set of tables, country-n will have a region-id column that references an id column in a region-d table (so the id column in region-d must be the primary key for region-d).

Simple Version

Lets start by declaring our classes and we will use the deftable make to create a definition for our tables that gets stored in the *tables* special variable. We can then use the (create-table 'class-name) function to create the table in the database.

Just to be slightly different, we are going to declare the classes without the :col-reference and :col-unique modifiers and put those into the (deftable) macro call. We will set the id as a serial in the -d version because we want to use name as the primary key and seting id as an identity would cause it to be the primary key.

(defclass region-d ()
  ((id :col-type serial :initarg :id :reader region-id)
   (name :col-type string :initarg :name :accessor region-name))
  (:metaclass pomo:dao-class)
  (:keys name))

(deftable region-d
  (!dao-def)
  (!unique 'name))

(create-table 'region-d)

(defclass country-d ()
  ((id :col-type serial :initarg :id :reader country-id)
   (name :col-type string :initarg :name
         :reader country-name)
   (region-name :col-type string :initarg :region-name :accessor region-name))
   (:metaclass pomo:dao-class)
   (:keys name))

(deftable country-d
  (!dao-def)
  (!unique 'name)
  (!foreign 'region-d 'region-name 'name))

(create-table 'country-d)

The new function !foreign insde the deftable adds a foreign key which requires that a region with that id already exist before you can insert a country. By the way, because of the foreign key constraint, postgresql will require that the region-d table be created before the country-d table.

Look at *tables* for a moment:

*tables*
((REGION-D . #<FUNCTION (LAMBDA ()) {534D126B}>)
 (COUNTRY-D . #<FUNCTION (LAMBDA ()) {52A1484B}>))

The region-d lambda looks like this:

(LAMBDA ()
    (LET ((*TABLE-NAME* "region_d") (*TABLE-SYMBOL* 'REGION-D))
      (DOLIST (STAT (LIST (!DAO-DEF) (!UNIQUE 'NAME))) (EXECUTE STAT))))

The country-d lambda looks like this:

(LAMBDA ()
    (LET ((*TABLE-NAME* "country_d") (*TABLE-SYMBOL* 'COUNTRY-D))
      (DOLIST
          (STAT
           (LIST (!DAO-DEF) (!UNIQUE 'NAME)
                 (!FOREIGN 'REGION-D 'REGION-NAME 'NAME)))
        (EXECUTE STAT))))

Less Simple Version

In the -n version, we are going to use the id columns as the primary key. We will not need to tell deftable t

(defclass region-n ()
  ((id :col-type integer :col-identity t :initarg :id :reader region-id)
   (name :col-type string :initarg :name :accessor region-name))
  (:metaclass pomo:dao-class))

(deftable region-n
  (!dao-def)
  (!unique 'name))

(create-table 'region-n)

(defclass country-n ()
  ((id :col-type integer :col-identity t :initarg :id :reader country-id)
   (name :col-type string :initarg :name
         :reader country-name)
   (region-id :col-type integer :initarg :region-id :accessor region-id))
   (:metaclass dao-class))

(deftable country-n
  (!dao-def)
  (!unique 'name)
  (!foreign 'region-n 'region-id 'id))

(create-table 'country-n)

How do you find the region-id? While we set the primary key as name for both country and region in the simple version, it will be a little more work in the less simple version. Lets start by inserting a couple of regions and we will stick with the dao method for the moment:

(insert-dao (make-instance 'region-d :name "Western Europe"))
(insert-dao (make-instance 'region-n :name "Western Europe"))
(insert-dao (make-instance 'region-d :name "Southern Europe"))
(insert-dao (make-instance 'region-n :name "Southern Europe"))

Now we can add a few countries to country-d:

(insert-dao (make-instance 'country-d :name "The Netherlands"
                                      :region-name "Western Europe"))

(insert-dao (make-instance 'country-d :name "Croatia"
                                      :region-name "Southern Europe"))

Now we can add a few countries to country-n, remembering that for this version, name is not the primary key so how to get the region dao with the name "Western Europe"? For region-d it is easy because the name is the primary key. So

(get-dao 'region-d "Western Europe")
#<REGION-D {100A322D43}>

For region-n it is a little more complicated because the primary key is the id column, not the name column. So there are a couple of ways to do it. First is select-dao which will return a list of daos meeting a test criteria, in a sorted order if that third parameter is also provided. Eg.

(select-dao 'region-n (:= 'id 1))
(#<REGION-N {100AAC6E13}>)

(select-dao 'region-n (:= 'name "Western Europe"))
(#<REGION-N {100A813CF3}>)

(select-dao 'region-n t 'name)
(#<REGION-N {100AC90FA3}> #<REGION-N {100AC911B3}>)

Another method with is query-dao, which takes a row and inserts it into a dao. That gets us a list of daos meeting the select criteria.

(query-dao 'region-n "select * from region_n where name = 'Western Europe'")
(#<REGION {1009E75E63}>)

or, using s-sql expression

(query-dao 'region-n (:select '*
                      :from 'region-n
                      :where (:= 'name "Western Europe")))
(#<REGION-D {100A50DA13}>)

Here are two different ways of generating the region-id so we can insert a new dao into country-n:

(insert-dao
  (make-instance 'country-n
                 :name "The Netherlands"
                 :region-id (region-id
                              (first (select-dao 'region-n
                                                 (:= 'name "Western Europe"))))))
#<COUNTRY-N {1002AD79B3}>

(insert-dao
  (make-instance 'country-n
                 :name "Croatia"
                 :region-id (query (:select 'id
                                    :from 'region-n
                                    :where (:= 'name "Southern Europe"))
                             :single)))
#<COUNTRY-N {1002ADE2B3}>

But the returned row need not be the result from a single table. Suppose we create a third table that has population by year and inserted a couple of rows. This time we will do it with s-sql.

(query (:create-table 'country-population ((id :type bigserial)
                                          (country-id :type integer)
                                          (year :type integer)
                                          (population :type integer))))

(let ((country-id (query (:select 'id
                          :from 'country-d
                          :where (:= 'name "The Netherlands"))
                         :single)))
  (query (:insert-rows-into 'country-population
          :columns 'country-id 'year 'population
          :values `((,country-id 2014 16830000)
                    (,country-id 2015 16900000)
                    (,country-id 2016 16980000)
                    (,country-id 2017 17080000)))))

(let ((country-id (query (:select 'id
                          :from 'country-d
                          :where (:= 'name "Croatia"))
                         :single)))
  (query (:insert-rows-into 'country-population
          :columns 'country-id 'year 'population
          :values `((,country-id 2014 4255518)
                    (,country-id 2015 4232873)
                    (,country-id 2016 4208611)
                    (,country-id 2017 4182846)))))

Now we create a class that pulls from all three tables (country, region and country-population).

(defclass country-with-population ()
  ((country-name :col-type string :initarg :country-name
           :reader country-name)
     (region-name :col-type string :initarg :region-name :accessor region-name)
     (year :col-type integer :initarg :year :reader year)
     (population :col-type integer :initarg :population :reader population))
     (:metaclass dao-class)
     (:keys country-name))

Can we use query-dao to get a list of country-with-population daos with the most recent population data? The answer is yes. That would give us a class that maybe our application can use without having to worry about constantly going back to the database to look for the region's name or whatever.

Of course you still need to get the data into the class instances. You could write the following one time as a function to generate your list of countries with the most recent population data. Note that you need to rename the columns to the appropriate initarg name (e.g. 'country-n.name is selected as 'country-name). You do not need to worry about the order of the selected rows. So long as the selections are renamed properly, the slots will be populated properly.

In the data that we have in the system, we happen to know that the years available are the same for every country. In that case, we just want the information for the maximum year. One way to do that would be:

(query-dao 'country-with-population
                 (:select (:as 'country-n.name 'country-name)
                          'year
                          (:as 'region-n.name 'region-name)
                          'population
                      :from 'country-n
                      :inner-join 'region-n
                      :on (:= 'country-n.region-id 'region-n.id)
                      :inner-join 'country-population
                      :on (:= 'country-n.id 'country-population.country-id)
                      :where (:= 'year (:select (:max 'year)
                                        :from 'country-population))))

But what happens if the data is not the same for both countries? Lets drop the 2017 population data row for Croatia and make sure it still returns the most current year that we have for both countries.

(query (:delete-from 'country-population
        :where (:and (:= 'country-id 2)
                     (:= 'year 2017))))

If we run the same query from above, we only get an instance for The Netherlands because that was the only data available for the maximum year (2017). We need to approach the data slightly differently.Because this is postmodern and we only care about the Postgresql database, we can use its DISTINCT ON extension to the SQL standard.

See https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT for more documentation.

The following query will pull the most recent year for both countries. How did that happen? We limited the select clause to distinct country names so we would only pull one of each country, then ordered the result by country-name, but most importantly by year descending.

(query-dao 'country-with-population
                 (:order-by (:select  (:as 'country-n.name 'country-name)
                                      'year
                                      (:as 'region-n.name 'region-name)
                                      'population
                             :distinct-on 'country-n.name
                             :from 'country-n
                             :inner-join 'region-n
                             :on (:= 'country-n.region-id 'region-n.id)
                             :inner-join 'country-population
                             :on (:= 'country-n.id
                                     'country-population.country-id))
                     'country-name
                     (:desc 'year)))
(#<COUNTRY-WITH-POPULATION {1009AFAEC3}>
 #<COUNTRY-WITH-POPULATION {1009AFC963}>)

At this point you could write a function that gets a country-with-population dao pulling the most recent population year from the database:

(defun get-country-with-most-recent-population (country)
  (car (query-dao 'country-with-population
                  (:order-by (:select  (:as 'country-n.name 'country-name)
                                       'year
                                       (:as 'region-n.name 'region-name)
                                       'population
                                       :distinct-on 'country-n.name
                                       :from 'country-n
                                       :inner-join 'region-n
                                       :on (:= 'country-n.region-id
                                               'region-n.id)
                                       :inner-join 'country-population
                                       :on (:= 'country-n.id
                                               'country-population.country-id)
                                       :where (:= 'country-n.name '$1))
                             'country-name
                             (:desc 'year))
                  country)))

Obviously it is not get-dao, which is simpler but just pulls everything in a single row from a table and this pulls just the data you want from three different tables and it is bespoken for that class. Because get-dao is a generic function, with the normal method being applied when passing a symbol, you could write a new method for get-dao that would apply if you passed it an actual country-with-population class instance.

If you want to display fields in a record which matches a dao class that you have set up, you can call get-dao with the name of table and the primary key. In this example, the table is "countries and the primary key happens to be the field "id" with a value of 1.

For example, assume we pull a dao object out of our country-n table for Croatia:

(describe (get-dao 'country-n 2))
#<COUNTRY-N {1005BF7273}>
  [standard-object]

Slots with :INSTANCE allocation:
  ID                             = 2
  NAME                           = "Croatia"
  REGION-ID                      = 2

Notice that the region-id field has an integer value. This works. But assume it has a slot of region-id, which refers to an id in the table "regions" and you want the name of the region displayed rather than the region-id. There is a hack using with-column-writers which essentially writes the name into the link slot. Now, we write a function that uses the with-column-writers macro and pull in the actual region name from the regions table.

(defun get-country2 (country-name )
  (first (with-column-writers
         ('region-n 'region-id)
         (query-dao 'country-n
                    (:select 'country-n.* (:as 'region-n.name 'region-n)
                             :from 'country-n
                             :left-join 'region-n
                             :on (:= 'country-n.region-id 'region-n.id)
                             :where (:= 'country-n.name country-name))))))

(describe (get-country2 "Croatia"))
#<COUNTRIES {1003AD23D1}>
  [standard-object]
(describe (get-country2 "Croatia"))
#<COUNTRY-N {100593DF03}>
  [standard-object]

Slots with :INSTANCE allocation:
  ID                             = 2
  NAME                           = "Croatia"
  REGION-ID                      = "Southern Europe"


(region-id (get-country2 "Croatia"))
"Southern Europe"

Normally calling the accessor region-id would return an integer, but now it is returning the name of the region. if you are using the dao as a simple way to get the relevant data out of the database and you are just going to display this value, this saves you from having to make additional database calls. Otherwise, you would have to make an additional call to get the information from all the foreign tables.