Postmodern Examples Table of Contents Delete

Table management

Create Table with Dao

(defclass country () ((id :accessor id :col-type integer :initarg :id) (name :reader name :col-type string :initarg :name) (inhabitants :accessor inhabitants :col-type (or db-null numeric) :initarg :inhabitants) (gdp :accessor gdp :col-type double-float :initarg :gdp) (real-gdp :accessor real-gdp :col-type float :initarg :real-gdp) (sovereign :col-type (or db-null string) :initarg :sovereign :accessor country-sovereign)) (:metaclass dao-class) (:keys name)) (dao-table-definition 'country) ;; => "CREATE TABLE country ( ;; id INTEGER NOT NULL, ;; name TEXT NOT NULL, ;; inhabitants NUMERIC DEFAULT NULL, ;; gdp DOUBLE PRECISION NOT NULL, ;; real_gdp REAL NOT NULL, ;; sovereign TEXT DEFAULT NULL, ;; PRIMARY KEY (name))" (execute (dao-table-definition 'country))

Note that you should get a warning that CREATE TABLE / PRIMARY KEY will create implicate index "country_pkey" for table "country". 
This means that postgresql has automatically created one index for you, in this cse based on column "name".
If you include, for example, an id column and give it col-type serial, then you will get an additional warning that an implicit 
sequence has been created for you, saving you time.

With respect to types, in the above, we created slots with col-type of integer, string, numeric,float and double-float. That translated into integer, text, numeric, real and double-precision.

You may also notice that the col-type for sovereign was declared (or db-null string) and inhabitants was declared (or db-null numeric), which translated to having a DEFAULT NULL whereas the other slots were provided with a definition of NOT NULL.

If you use a type that postgresql does not understand, for example, defining a slot with a :col-type list, executing the definition will throw a syntax error that the type "list" does not exist. You may leave out the :col-type your-special-type-here from the slot definition and the table will be created without that slot. Using insert-dao on an instance of your class would insert all the data except the data that you did not provide a valid :col-type. Thus, if your class did have slots containing lists of items, you would need to handle those slots separately.

Create a table with Deftable

Assume you have defined a dao class named quantico. You can then create a table with various constraints, foreign keys etc. As an example:

(defclass quantico () ((id :col-type integer :initarg :id :accessor :id) (user-id :col-type integer :initarg :user-id :accessor :user-id)) (:metaclass dao-class) (:keys id)) (deftable quantico (!dao-def) ;; Import the existing info from the dao-class definition. (!foreign 'users 'user-id 'id :on-delete :cascade :on-update :cascade)) (create-table 'quantico) WARNING: Postgres warning: CREATE TABLE / PRIMARY KEY will create implicit index "quantico_pkey" for table "quantico"

The !foreign function takes the parameters: [target table][single or list of columns in the current class] optionally [single or list of columns in the target table] and then the key parameters :on-update and :on-delete.

The possible values for :on-update and :on-delete are: :restrict :set-null :set-default :cascade or :no-action

Create a table without a dao class

You can always create tables without a dao class as well. The example I used at the beginning of the page is:

(query (:create-table regions ((id :type int4 :primary-key t) (name :type varchar :default "" :unique t)))) (query (:create-table countries ((id :type int4 :primary-key t) (name :type varchar :default "" :unique t) (region-id :type int4 :default 0) (latitude :type numeric :default 0) (longitude :type numeric :default 0) (iso :type bpchar :default "") (currency :type varchar :default "")) (:foreign-key (region-id) (regions id))))

Note the additional items passed here: id is declared as the primary key, name is declared as unique. Region-id is called out as a foreign key to the id field in the regions table. Foreign key constraints can be added to the actual column definition using the keyword :references.

One inconsistency here is that the :foreign-key looks first for the current table column, then the target table and target table column. The deftable version looks for the target table, then the current column then the target table column.

One interesting bit to notice. The name of the table works whether it is in double quotes or not. It cannot, however, be a symbol.

Alter Table

(query (:alter-table "countries" :add-column "stuff4" :type integer :default 0))
(query (:alter-table "countries" :add-column "stuff4" :type integer))
(query (:alter-table "countries" :add-column "stuff4" :type (or db-null integer)))
The difference between these versions is how they handle nulls. The first will generate a column that cannot be null and will set a default of 0. The second will not set a default but will still generate a column that cannot be null. The third will not set a default and the column is allowed to be null. Note that because the table name is in double quotes, if you have a multiple word table name, the words must be separated by the normal sql underscores, not the normal "lispy" hyphens.

You can use sql-compile to build the alter-table statements dynamically like so:

(let ((table "countries") (column "stuff4"))
  (query (sql-compile `(:alter-table ,table :add-column ,column
                                     :type (or db-null integer)))))


(query (:insert-into 'test2 (:select '* :from 'test1)))

You could insert a plist into a record like so:

(let ((table 'regions) (lst '('id 225 'name "Dwoffle")))
  (query (sql-compile (append `(:insert-into ,table :set)