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)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")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.
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 four 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) #<COUNTRY 〈1006BC31B1〉>Note, if you have composite keys, you will need to provide as many parameters as there are columns in your composite key. E.g. you might have a points class with x and y points as the composite key. In that case the get-dao call would look like:
(get-dao 'points 12 173)where 12 and 173 are the x, y points need to get the dao out of the database.
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)
(#<COUNTRY {10043370f1}> #<COUNTRY {10043377E1}>)Third, using the (:dao class-type) modifier for query will return a list of DAO objects.
(query (:select '* :from 'countries :where (:> 'id 200) (:dao country))(#<COUNTRY {10043370f1}> #<COUNTRY {10043377E1}>)Fourth, using the (:dao class-type :single) modifier for query will return a single DAO object.
(query (:select '* :from 'countries :where (:= 'id 201) (:dao country))#<COUNTRY {10043370f1}>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.
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 TuvaluIf 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 = 38NAME = "Australia"UPDATED = 1126397740REGION-ID = 9LATITUDE = -27LONGITUDE = 133ISO = "AU"PERMISSION-ID = 1Notice 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 'regions.name 'region) :from 'countries :left-join 'regions :on (:= 'countries.region-id 'regions.id) :where (:= 'countries.name country))))))(describe (get-country2 "Australia"))#<COUNTRIES {1003AD23D1}> [standard-object]Slots with :INSTANCE allocation:ID = 38NAME = "Australia"UPDATED = 1126397740REGION-ID = "Pacific"LATITUDE = -27LONGITUDE = 133ISO = "AU"PERMISSION-ID = 1Normally 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.
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.
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>> (with-test-connection (if (pomo:table-exists-p 'dao-test) (query (:drop-table 'dao-test)) (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))(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)))Postgresql truncated the numeric 14.37 to force it into an integer column.
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. 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}>It does work.
Third using a dao that is correct in columns but incorrect in type.
(type-of (test-d (get-dao 'test-data-d-string 3)))RATIOIf you recall, the 'test-data-d-string dao wanted text in the d column, postgresql refused and the dao has accepted a ratio in that slot. The database has enforced data consistency (which is part of its purpose), but we do not have enforced data consistency in the dao.