Postmodern Examples Table of Contents Doquery


Postmodern allows you to have a relatively simple but straight forward matching of clos classes to a database table. A sample is as follows:

(defclass country () ((id :accessor id :col-type int4 :initarg :id :initform nil) (name :accessor name :col-type varchar :initarg :name :initform "") (updated-at :accessor updated-at :col-type timestamp :initarg :updated-at :initform (local-time:now)) (region-id :accessor region-id :col-type int4 :initarg :region-id :initform 0) (latitude :accessor latitude :col-type numeric :initarg :latitude :initform 0) (longitude :accessor longitude :col-type numeric :initarg :longitude :initform 0) (something-with-null :accessor something-with-null :col-type boolean :initarg :something-with-null :col-default :null) (iso :accessor iso :col-type bpchar :initarg :iso :initform "") (permission-id :accessor permission-id :col-type int4 :initarg :permission-id :initform 1)) (:documentation "Dao class for a countries record.") (:metaclass postmodern:dao-class) (:table-name countries)(:keys id))

Note that the class needs to be modified slightly. Three specific points here. First, to specify that a slot refers to a column, it needs a :col-type option. Second, the class definition includes a metaclass reference to postmodern:dao-class. Third, the table-name and primary keys are specified.

Notice the slot named something-with-null. Instead of having an :initform, it has a :col-default :null specification. This 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.

You can have multiple primary keys (a composite key) in postgresql/postmodern.

(:keys mycol1 mycol2)

Selecting Schemas

If you need to specify the schema for the table name in the dao definition, notice that the table name is really a string. The proper specification would be like this:

(:table-name "schema_name.table_name")

Nested Classes

Assuming you have a class whose instances will contain other classes which themselves have separate tables, you don't include the :col-type in the definition.

Returning Data Objects (DAOs)

Postmodern allows you to return daos that match classes and tables. Given a class as described above, the standard call just uses the name of the table and the primary key to get the class instance out of the table. More on daos later.

Here are two simple examples of the calls to the database. First, get-dao returns a single instance of a countries class from the database:

(get-dao 'country 21) #<COUNTRIES 〈1006BC31B1〉>

Second, select-dao return instances of the countries class out of the database query of 'id greater than 200, sorted by name.

(select-dao 'country (:> 'id 200) 'name) (#<COUNTRIES {10043370f1}> #<COUNTRIES {10043377E1}>)

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.

Selecting by DAO

Assume that you want to pull out all the data as classes, the following will pull that as a list of daos, sorted by name, with id greater than 0.

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

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

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

Finally, the following adds a second condition, that the name be greater than "Turkmenistan" and shows it in context, printing out a list of the names of the activty-types daos returned:

(dolist (x (select-dao 'country (:and (:> 'name "Turkmenistan") (:> 'id 0)) (:desc 'name))) (format t "~a~%" (name x))) Zimbabwe Zambia Wallis Vietnam Venezuela Vanuatu Uzbekistan US Virgin Islands US Uruguay Ukraine UK Uganda UAE Tuvalu

Using a DAO, replacing linked ids with actual names

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 countries table for Australia:

(describe (get-dao 'country 38)) #<COUNTRIES {1004B116A1}> [standard-object] Slots with :INSTANCE allocation: ID = 38 NAME = "Australia" UPDATED = 1126397740 REGION-ID = 9 LATITUDE = -27 LONGITUDE = 133 ISO = "AU" PERMISSION-ID = 1

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) (first (with-column-writers ('region 'region-id) (query-dao 'countries (:select 'countries.* (:as ' 'region) :from 'countries :left-join 'regions :on (:= 'countries.region-id ' :where (:= ' country)))))) (describe (get-country2 "Australia")) #<COUNTRIES {1003AD23D1}> [standard-object] Slots with :INSTANCE allocation: ID = 38 NAME = "Australia" UPDATED = 1126397740 REGION-ID = "Pacific" LATITUDE = -27 LONGITUDE = 133 ISO = "AU" PERMISSION-ID = 1

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.

Getting keys out of DAO

Per the postmodern documentation the function (dao-keys class) Returns list of slot names that are the primary key of DAO class CLASS. This is likely interesting if you have primary keys which are composed of more than one slot.