DAO Classes

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)

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))
  

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.

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))

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)))

→ 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"))

→ 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.

→ symbol

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

→ 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.

→ dao

Combines make-instance with insert-dao. Make the instance of the given class and insert it into the database, returning the created 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))

→ 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.

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.

→ 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.

→ 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))

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))

→ 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.

→ 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))

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.

→ 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.)

→ 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.

→ 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.

→ 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.

→ 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.

Delete the given dao from the database.

→ string

Get the name of the table associated with the given DAO class (or symbol naming such a 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.

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))

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.

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

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

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.

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

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.

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

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.

Creates all defined tables.

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

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

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

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

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))))

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.