postmodern-table-management
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 (from the docs)
S-SQL Create-Table
At this minimum level, the :create-table and :create-extended-table calls are exactly the same. Let's walk through a simple example:
(query (:create-table 'films-at-eleven ((code :type (or (string 5) db-null) :constraint 'firstkey :primary-key t) (title :type (varchar 40)) (did :type integer) (date-prod :type (or date db-null)) (kind :type (or (varchar 10) db-null)) (len :type (or interval db-null) :interval :hour-to-minute)))) The parameter specification is :create-table (name (&rest columns) &rest options)
The first parameter is the name of the new table. You could make the name a quoted string, e.g. "films-at-eleven", but postmodern will handle either a string or the unquoted parameter provided above. Postmodern will also automatically handle the conversion of the hyphens so beloved by common lisp programmers to the underscores demanded by the sql specification.
The next form contains subforms for each column in the table.
The first parameter being the name of the column.
As you know, postgresql will enforce the data type for the column. So, the next keyword specifies that type to follow. You can see several different types specified in this table. Look at the type for 'code'. It specifies that the data will be either NULL or a string with a maximum length of 5 characters.
Compare that against the type specifiers for title or did. In both of those cases, the column entry is not allowed to be NULL, it must have a value. In this example the title must be a string with a length no greater than 40 characters. (For most purposes, you can treat a string specification the same as a varchar specification.
The did type specification mandates an integer - trying to put a float in that column will trigger a database type error.
The date-prod column obviously has a date data type and the len column has a interval data type, intervals being a period of time rather than a specific time.
The code column also has a constraint - It is the primary key for indexing the table and that contraint has a name - in this case 'firstkey. If you do not name the constraint, the system will name it for you.
The len column also has additional information. It is an interval of time; That could encompass years as well as seconds. The specification given here, :hour-to-minute means that the database will only keep the hours to minutes part of the potential time interval.
Table Names Parameter
The following discussion on table names is the same for both :create-table or :create-extended table.
Create-table works with either the simple name for the table parameter or the name can be wrapped in a form. There are a few acceptable ways to pass the table-name parameters. We recommend a single quote as in the first example below, but for backwards compatibility, you can also provide the table-name with no quotes at all. The last example below shows the table name preceded by the desired schema name. e.g. 'public.tablename1.
(query (:create-table 'distributors-in-hell ((did :type (or integer db-null)))))(query (:create-table (distributors-in-hell) ((did :type (or integer db-null)))))(query (:create-table ("distributors-in-hell") ((did :type (or integer db-null)))))(query (:create-table 'public.distributors-in-hell ((did :type (or integer db-null))))) Using the name wrapped in a form provides the opportunity to add additional qualifiers - if-not-exists, temp, and unlogged.
(query (:create-table (:if-not-exists 'distributors-in-hell) ((did :type (or integer db-null))))) Temporary and Unlogged Tables
Temporary tables exists only for the duration of the session. To create them using s-sql in postmodern, the first parameter to :create-table should be a form where the first atom in the form is the keyword :temp. You can optionally use the :if-not-exists keyword, and then the name of the temporary table. E.g.
(query (:create-table (:temp 'distributors-in-hell) ((did :type (or integer db-null)))))(query (:create-table (:temp :if-not-exists 'distributors-in-hell) ((did :type (or integer db-null))))) Unlogged tables do not have their data written to the write-ahead log. As a result they are faster, but not crash safe. Any indexes created on an unlogged table are unlogged as well. The parameter signature is similar to a temporary table. E.g.
(query (:create-table (:unlogged 'distributors-in-hell) ((did :type (or integer db-null)))))
Using Identity Columns
Postgresql version 10 added identity columns. These are auto-incrementing columns that use the standard SQL syntax instead of the serial and bigserial datatypes which are still available, but are not SQL standard syntax. The serial and bigserial datatypes also generate sequences that may have different usage permissions which can be problematic if you need to make changes to the sequence.
There are two keyword alternatives that need to be considered:
- :generated-as-identity-by-default (or more simply :identity-by-default)
- :generated-as-identity-always (or more simply :identity-always)
The difference between the two alternatives is that if you try to insert or update a value in the column and the generated-always option is chosen, postgresql will raise an error. If you use the by-default option, postgresql will use your value rather than the automatically generated value.
Note: The data type used for identity columns must be one of smallint, int, integer or bigint.
(query (:create-table 'color
((color-id :type int :generated-as-identity-always t) (color-name :type varchar))))(query (:create-table 'color ((color-id :type int :generated-as-identity-by-default t) (color-name :type varchar))))(query (:create-table 'color ((color-id :type int :identity-always t) (color-name :type varchar))))(query (:create-table 'color ((color-id :type int :identity-by-default t) (color-name :type varchar))))Array Columns
The following shows the creation of a table with a two dimensional array
(query (:create-table 'array-int ((vector :type (or int[][] db-null))))) Check Constraints
You can put a contraint on a table row that specifies values must meet certain requirements. In the following examples, the first puts a check constraint on a row, the second places at check constraint at the table level.
(query (:create-table 'distributors ((did :type (or integer db-null) :check (:> 'did 100)) (name :type (or (varchar 40) db-null)))))(query (:create-table 'distributors ((did :type (or integer db-null)) (name :type (or (varchar 40) db-null))) (:constraint con1 :check (:and (:> 'did 100) (:<> 'name ""))))) Unique Constraints
You can ensure that a column or a combination of columns is unique without making that column or columns the primary key for the table.
The first example sets a unique constraint at the column level of email, the second example sets a unique constraint at the table level.
Please note the need to set :unique to t in the column constraint version.
(query (:create-table 'person ((id :type serial :primary-key t) (first-name :type (varchar 50)) (last-name :type (varchar 50)) (email :type (varchar 50) :unique t))))(query (:create-table 'films ((code :type (or (string 5) db-null)) (title :type (or (varchar 40) db-null)) (did :type (or integer db-null)) (date-prod :type (or date db-null)) (kind :type (or (varchar 10) db-null)) (len :type (or interval db-null) :interval :hour-to-minute)) (:constraint production :unique 'date-prod)))This can get more complex if so desired. See the following example from the postgresql documentation, translated into s-sql. This level of complexity, however, requires the :create-extended-table method.
(query (:create-extended-table 'distributors ((did :type (or integer db-null)) (name :type (or (varchar 40) db-null))) ((:unique did :with (:= 'fillfactor 70)))))In case you are wondering, fillfactor is a storage parameter described in the postgresql documentation as:
"The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables."
Timestamps with and without timezones
Ah, the bane of many programmers' existence - dates and times. At least the table creation is fairly straightforward.
The following examples are fairly self explanatory.
(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamp-without-time-zone db-null)))))(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamp-with-time-zone db-null)))))(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamptz db-null)))))(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamp db-null)))))(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or time db-null))))) Adding Foreign Keys
There are a few different ways to add foreign keys in postgresql when defining a table.
Defining Foreign Keys at the Column Level
First with foreign key on the column. Use the keyword :references and specify the table name and the relevant column within the subform. You will see why the subform in a minute.
(query (:create-table 'so-items ((item-id :type integer) (so-id :type (or integer db-null) :references ((so-headers id))) (product-id :type (or integer db-null)) (qty :type (or integer db-null)) (net-price :type (or numeric db-null))) (:primary-key item-id so-id)))If the foreign key references a group of columns in the foreign table, those fit into that same subform.
(query (:create-table 'so-items ((item-id :type integer) (so-id :type (or integer db-null) :references ((so-headers id p1 p2))) (product-id :type (or integer db-null)) (qty :type (or integer db-null)) (net-price :type (or numeric db-null))) (:primary-key item-id so-id)))You can specify the actions to be taken if a row in the foreign table is deleted or updated. Per the postgresql documentation:
"… when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually changed, no action is done. Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable. There are the following possible actions for each clause:
- NO ACTION
Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action in postgresql but the default in postmodern is restrict.
- RESTRICT
Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.
- CASCADE
Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively.
- SET NULL
Set the referencing column(s) to null.
- SET DEFAULT
Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.)
If the referenced column(s) are changed frequently, it might be wise to add an index to the referencing column(s) so that referential actions associated with the foreign key constraint can be performed more efficiently."
So now examples with specifying the on-delete and on-update actions.
(query (:create-table 'so-items ((item-id :type integer) (so-id :type (or integer db-null) :references ((so-headers id) :no-action :no-action)) (product-id :type (or integer db-null)) (qty :type (or integer db-null)) (net-price :type (or numeric db-null))) (:primary-key item-id so-id)))Defining Foreign Keys at the Table Level
Instead of specifying the foreign keys at the column level, you can specify them at the table level, but of course that means you have to additionally specify which column in the current table is a foreign key.
The following example creates a named constraint and a foreign key at column role-id with a single column in the foreign table. The first example uses the default actions for on-delete and on-default. The second example shows non-default actions specified.
(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamp-without-time-zone db-null))) (:primary-key user-id role-id) (:constraint account-role-role-id-fkey :foreign-key (role-id) (role role-id))))(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamp-without-time-zone db-null))) (:primary-key user-id role-id) (:constraint account-role-role-id-fkey :foreign-key (role-id) (role role-id) :no-action :no-action))) Additional foreign keys can easily be added:
(query (:create-table 'account-role ((user-id :type integer) (role-id :type integer) (grant-date :type (or timestamp-without-time-zone db-null))) (:primary-key user-id role-id) (:constraint account-role-role-id-fkey :foreign-key (role-id) (role role-id)) (:constraint account-role-user-id-fkey :foreign-key (user-id) (users user-id))))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)))))Altering Columns
At the moment, altering columns requires that you still include a blank :type keyword.
As an example, assume you created a table named "test2" with a column named "description" with a not null constraint
and you want to drop the not null constraint on the table. Either of the following will work
(the only differences being whether you quote the names or use strings for the names:
(query (:alter-table "test2" :alter-column "description" :type (:drop not-null)))(query (:alter-table 'test2 :alter-column 'description :type (:drop not-null)))Insert-into
(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) lst))))