Postmodern

http://marijnhaverbeke.nl/postmodern
Admin
Postmodern is a Common Lisp library for interacting with PostgreSQL databases, offering efficient...
Tokens:16,064
Snippets:121
Trust Score:9.3
Update:8 months ago
Show doc for...
Tokens:
Raw
### Simple-Date with cl-postgres Setup

Instructions to load the simple-date/postgres-glue library and set the SQL readtable for seamless integration with cl-postgres.

```APIDOC
## Simple-Date with cl-postgres Setup

### Description
This section details how to integrate the Simple-Date library with cl-postgres for database operations. It involves loading the necessary glue code and configuring the SQL readtable to recognize simple-date types.

### Setup Steps

1.  **Load the glue library:**
    ```lisp
    (ql:quickload :simple-date/postgres-glue)
    ```

2.  **Configure cl-postgres SQL readtable:**
    This step registers the appropriate SQL readers and writers for simple-date types with cl-postgres.
    ```lisp
    (setf cl-postgres:*sql-readtable*
          (cl-postgres:copy-sql-readtable
           simple-date-cl-postgres-glue:*simple-date-sql-readtable*))
    ```

### Reverting to Default cl-postgres Reader

To revert to the default cl-postgres SQL reader:

```lisp
(setf cl-postgres:*sql-readtable*
      (cl-postgres:copy-sql-readtable
       cl-postgres::*default-sql-readtable*))
```

### Using Local-Time with cl-postgres

To enable the local-time reader when cl-postgres is using the default reader:

```lisp
(local-time:set-local-time-cl-postgres-readers)
```

### Note on Time Zones

The Simple-Date library currently treats all times as UTC and does not handle time zones. Use with caution if time zone awareness is critical.
```

--------------------------------

### Run All Postmodern Tests from Command Line (SBCL)

Provides an example of how to execute all Postmodern component tests from the command line using SBCL. This method allows for automated testing and integrates with environment variables for database connection parameters.

```bash
env DB_USER=$USER sbcl –noinform \
 –eval '(ql:quickload "postmodern/tests")'
 –eval '(ql:quickload "cl-postgres/tests")'
 –eval '(ql:quickload "s-sql/tests")'
 –eval '(ql:quickload "simple-date/tests")'
 –eval '(progn (setq 5am:*print-names* nil) (5am:run-all-tests))'
 –eval '(sb-ext:exit)'
```

--------------------------------

### Create Table with Composite Primary Key using s-sql and Plain SQL

Illustrates creating a 'so-items' table with a composite primary key using Postmodern's s-sql operator and its plain SQL equivalent. This example shows handling hyphens in table and column names and defining foreign key relationships.

```lisp
(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)))
```

```sql
(query "CREATE TABLE so_items (
           item_id INTEGER NOT NULL,
           so_id INTEGER REFERENCES so_headers(id)
                 MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT,
           product_id INTEGER,
           qty INTEGER,
           net_price NUMERIC,
           PRIMARY KEY (item_id, so_id)
     );")
```

--------------------------------

### Export Local-Time Timestamps as JSON Strings

Demonstrates exporting timestamp data retrieved from PostgreSQL using Local-Time as JSON strings. It includes examples using encode-json-to-string and the :json-strs query parameter.

```common-lisp
(encode-json-to-string (query (:select 'timestamp-with-time-zone
                               :from 'test-data
                               :where (:= 'id 1))
                               :single))
```

```common-lisp
(query (:select 'timestamp-with-time-zone
        :from 'test-data
        :where (:< 'id 3))
  :json-strs)
```

--------------------------------

### Format PostgreSQL Timestamps with TO_CHAR (s-sql)

This s-sql snippet shows how to format PostgreSQL timestamp, time, and date columns using the TO_CHAR function. It mirrors the functionality of the standard SQL example, demonstrating timezone and UTC offset formatting.

```lisp
(query (:select  (:to-char 'col_timestamp_with_time_zone "YYYY-MM-DD HH24:MI:SS TZ")
                 (:to-char 'col_timestamp_with_time_zone "YYYY-MM-DD HH24:MI:SS OF")
                 (:to-char 'col_timestamptz "YYYY-MM-DD HH24:MI:SS")
                 (:to-char 'col_timestamp "YYYY-MM-DD HH24:MI:SS")
                 (:to-char 'col_time "HH24:MI:SS")
                 (:to-char 'col_date "YYYY-MM-DD")
        :from 'short-data-type-tests
        :where (:= 'id 1)))

(("2020-10-30 19:30:54 EDT" "2020-10-30 19:30:54 -04" "2020-10-30 19:30:54"
  "2020-10-30 19:30:54" "19:30:54" "2020-10-30"))
```

--------------------------------

### Run Postmodern Tests in REPL (Common Lisp)

Demonstrates how to load and run the 'postmodern' test suite within a Common Lisp REPL using Quicklisp and FiveAM. This is useful for developers testing individual components.

```common-lisp
(ql:quickload "postmodern/tests")
(5am:run! :postmodern)

    ;; ... test output ...

```

--------------------------------

### Create PostgreSQL Database and Connect

This code demonstrates how to create a new PostgreSQL database and then establish a development connection to it. It first uses `with-connection` to connect to an existing database (like 'postgres') to perform the creation, then uses `connect-toplevel` to switch to the newly created database for ease of development. The `create-database` function supports options like `:limit-public-access` and `:comment`.

```common-lisp
(with-connection '("postgres" "foucault" "surveiller" "localhost")
  (create-database 'testdb :limit-public-access t
                     :comment "This database is for testing silly theories"))

(connect-toplevel "testdb" "foucault" "surveiller" "localhost")
```

--------------------------------

### Load Postmodern Library with Quicklisp

This snippet demonstrates how to load the Postmodern library using Quicklisp, a popular Lisp implementation manager. It also shows how to bring the Postmodern package into the current scope for easier use.

```common-lisp
(ql:quickload :postmodern)
(use-package :postmodern)
```

--------------------------------

### Enabling Binary Parameter Passing with `with-connection`

Illustrates how to use the `with-connection` macro to establish a database connection with binary parameter passing enabled. This is an alternative to the `connect` function.

```lisp
(with-connection '("database-name" "user-name" "user-password" "localhost or IP address"
                   :use-binary t [any other keyword parameters you want to apply])
  ...)
```

--------------------------------

### Executing Basic SQL Queries with Postmodern

Shows how to execute basic SQL queries using the `query` function in Postmodern. This includes direct SQL strings and S-SQL (Lisp-based SQL) syntax. It also covers handling constant values, parameterized queries, and specifying result formats like `:single`.

```common-lisp
(with-connection '("testdb" "foucault" "surveiller" "localhost")
    (query "select 22, 'Folie et déraison', 4.5"))
;; => ((22 "Folie et déraison" 9/2))
```

```common-lisp
(query (:select 22 "Folie et déraison" 4.5))
;; => ((22 "Folie et déraison" 9/2))
```

```common-lisp
(defun database-powered-addition (a b)
  (query (:select (:+ a b)) :single))

(database-powered-addition 1030 204)
;; => 1234
```

--------------------------------

### Create Table with s-sql and Plain SQL

Demonstrates creating a 'countries' table using Postmodern's s-sql operator and its equivalent in plain SQL. This includes defining columns with types, constraints like primary keys and uniqueness, and foreign key references.

```lisp
(query (:create-table 'countries
      ((id :type integer  :primary-key t :identity-always t)
       (name :type string :unique t :check (:<> 'name ""))
       (inhabitants :type integer)
       (sovereign :type (or db-null string))
       (region-id :type integer :references ((regions id))))))
```

```sql
(query "CREATE TABLE countries (
           id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
           name TEXT NOT NULL UNIQUE CHECK (NAME <> E''),
           inhabitants INTEGER NOT NULL,
           sovereign TEXT,
           region_id INTEGER NOT NULL REFERENCES regions(id)
             MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT)")
```

--------------------------------

### Run Multiple Postmodern Component Tests in REPL (Common Lisp)

Shows how to load and run tests for multiple Postmodern components simultaneously in a Common Lisp REPL. This is useful for comprehensive testing of integrated systems.

```common-lisp
(ql:quickload '("cl-postgres/tests" "s-sql/tests"))
(5am:run-all-tests)

    ;; ... test output ...

```

--------------------------------

### Enabling Binary Parameter Passing on Connection

Demonstrates how to configure a Postmodern database connection to send parameters to PostgreSQL in binary format for supported data types. This can be done during connection creation.

```lisp
(connect "test-db" "test-user" "test-password" "192.168.5.223"
         :port 5434 :pooled-p t :use-ssl try :application-name "test-app" :use-binary t)
```

--------------------------------

### Generate Table Definition from DAO using Postmodern

Shows how to generate plain SQL for creating a table directly from a previously defined DAO class using `dao-table-definition`. The generated SQL can then be executed to create the table.

```lisp
(dao-table-definition 'country)

"CREATE TABLE country
  (name TEXT NOT NULL,
   inhabitants INTEGER NOT NULL,
   sovereign TEXT DEFAULT NULL,
  PRIMARY KEY (name))"

(execute (dao-table-definition 'country))
```

```lisp
(dao-table-definition 'country-c)

;; => "CREATE TABLE countries (
;;       id INTEGER NOT NULL PRIMARY KEY generated always as identity,
;;       name TEXT NOT NULL UNIQUE,
;;       inhabitants INTEGER NOT NULL,
;;       sovereign TEXT DEFAULT NULL,
;;       region_id INTEGER NOT NULL REFERENCES regions(id)
;;         MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT)

(execute (dao-table-definition 'country-c))
```

--------------------------------

### Dynamically Changing Binary Parameter Setting

Shows how to enable or disable binary parameter passing for an existing database connection using the `use-binary-parameters` function. Pass `t` for binary and `nil` for text.

```lisp
(use-binary-parameters *database* t)

(use-binary-parameters some-database-connection t)
```

--------------------------------

### Pooling PostgreSQL Connections with Postmodern

Demonstrates how to use Postmodern's connection pooling feature. Connections are not lightweight and consume significant memory. Pooling is recommended for applications making frequent connections to reduce overhead. The `with-connection` macro is used, and the maximum pool size can be configured via `*max-pool-size*`.

```common-lisp
(with-connection '("testdb" "foucault" "surveiller" "localhost" :pooled-p t)
      ...)
```

--------------------------------

### Load Simple-Date PostgreSQL Glue and Set Readtable

Loads the simple-date/postgres-glue library and configures cl-postgres to use the simple-date SQL readers and writers. This is necessary for seamless integration when storing and retrieving simple-date types from a PostgreSQL database.

```common-lisp
(ql:quickload :simple-date/postgres-glue)

(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         simple-date-cl-postgres-glue:*simple-date-sql-readtable*))
```

--------------------------------

### Parameterized Queries in SQL and S-SQL

Demonstrates how to use parameterized queries in both regular SQL and S-SQL syntax to prevent SQL injection and handle quotes automatically. These are essential for prepared queries.

```lisp
(query "select name, address from customers where id = $1" 237)

(query (:select 'name 'address :from 'customers :where (:= 'id '$1)) 237)
```

--------------------------------

### Load and Configure Simple-Date with PostgreSQL

Loads the simple-date-cl-postgres-glue library and configures cl-postgres to use Simple-Date's SQL readers for handling date, time, and timestamp types.

```common-lisp
(ql:quickload :simple-date/postgres-glue)

(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
          simple-date-cl-postgres-glue:*simple-date-sql-readtable*))
```

--------------------------------

### Interval Type

Documentation for the `interval` type, including functions for creation and decomposition.

```APIDOC
## Interval Type

### Class `interval`

An interval represents a period of time. It contains both an absolute part (in milliseconds) and a relative part for months and years.

### Function `encode-interval`

#### Description
Creates an interval object.

#### Parameters
- `&key` (year 0) (month 0) (week 0) (day 0) (hour 0) (minute 0) (second 0) (millisecond 0)
  - Arguments can be negative and of any size.

#### Returns
- `interval` - An interval object.

### Function `decode-interval`

#### Description
Decomposes an interval into its constituent parts.

#### Parameters
- `interval` (interval) - The interval object to decode.

#### Returns
- `values` (year, month, day, hour, minute, second, millisecond) - The components of the interval. Note that these may differ from the input parameters due to equivalences (e.g., 3600 seconds = 1 hour).
```

--------------------------------

### Establish Lexically Scoped Connection to PostgreSQL

This code snippet shows how to establish a database connection with a lexical scope using the `with-connection` form. This method is appropriate for multi-threaded applications, executables, or when managing multiple distinct database connections. It accepts connection parameters as a list.

```common-lisp
(with-connection '("testdb" "foucault" "surveiller" "localhost")
  ...)
```

--------------------------------

### Date Type

Documentation for the `date` type, including functions for creation, decoding, and determining the day of the week.

```APIDOC
## Date Type

### Class `date`

Represents a date, with no time-of-day information.

### Function `encode-date`

#### Description
Creates a date object.

#### Parameters
- `year` (integer) - The year.
- `month` (integer) - The month (1-12).
- `day` (integer) - The day of the month.

#### Returns
- `date` - A date object.

### Function `decode-date`

#### Description
Extracts the year, month, and day from a date object.

#### Parameters
- `date` (date) - The date object to decode.

#### Returns
- `values` (year, month, day) - The components of the date.

### Function `day-of-week`

#### Description
Determines the day of the week for a given date.

#### Parameters
- `date` (date) - The date object.

#### Returns
- `integer` - The day of the week, where 0 is Sunday and 6 is Saturday.
```

--------------------------------

### Iterating Over Query Results with Postmodern

Illustrates the use of the `doquery` macro for iterating over query results row by row. This is an efficient way to process large result sets without loading everything into memory at once.

```common-lisp
(doquery (:select 'x 'y :from 'some-imaginary-table) (x y)
  (format t "On this row, x = ~A and y = ~A.~%" x y))
```

--------------------------------

### Enable Simple-Date Reader with Default cl-postgres

Configures cl-postgres to use the simple-date SQL readers and writers when the default cl-postgres reader is active. This allows using simple-date types with PostgreSQL even if other custom readers are not in use.

```common-lisp
(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         simple-date-cl-postgres-glue:*simple-date-sql-readtable*))
```

--------------------------------

### Time Operations

Documentation for generic functions used for operations on time-related objects like dates, timestamps, and intervals.

```APIDOC
## Operations

Generic functions are used for operations on time values, with semantics varying based on operand types.

### Method `time-add`

#### Description
Adds two time-related objects. Adding an interval to a date or timestamp returns a new date or timestamp. Adding two intervals returns their sum. Integers are interpreted as milliseconds.

#### Parameters
- `a` (date, timestamp, interval, or integer) - The first time-related object.
- `b` (date, timestamp, interval, or integer) - The second time-related object.

#### Returns
- `value` - The result of the addition (date, timestamp, or interval).

### Method `time-subtract`

#### Description
Subtracts time-related objects. Subtracting two dates or timestamps results in an interval representing their difference. Subtracting two intervals returns their difference.

#### Parameters
- `a` (date, timestamp, or interval) - The minuend.
- `b` (date, timestamp, or interval) - The subtrahend.

#### Returns
- `value` - The result of the subtraction (interval or date/timestamp if applicable).

### Method `time=`

#### Description
Compares two time-related values for equality.

#### Parameters
- `a` (date, timestamp, or interval) - The first value.
- `b` (date, timestamp, or interval) - The second value.

#### Returns
- `boolean` - True if the values are equal, False otherwise.

### Method `time<`

#### Description
Compares two time-related values to check if the first is less than the second.

#### Parameters
- `a` (date, timestamp, or interval) - The first value.
- `b` (date, timestamp, or interval) - The second value.

#### Returns
- `boolean` - True if `a` is less than `b`, False otherwise.

### Method `time>`

#### Description
Compares two time-related values to check if the first is greater than the second.

#### Parameters
- `a` (date, timestamp, or interval) - The first value.
- `b` (date, timestamp, or interval) - The second value.

#### Returns
- `boolean` - True if `a` is greater than `b`, False otherwise.

### Function `time<=`

#### Description
Checks if the first time-related value is less than or equal to the second.

#### Parameters
- `a` (date, timestamp, or interval) - The first value.
- `b` (date, timestamp, or interval) - The second value.

#### Returns
- `boolean` - True if `a` is less than or equal to `b`, False otherwise.

### Function `time>=`

#### Description
Checks if the first time-related value is greater than or equal to the second.

#### Parameters
- `a` (date, timestamp, or interval) - The first value.
- `b` (date, timestamp, or interval) - The second value.

#### Returns
- `boolean` - True if `a` is greater than or equal to `b`, False otherwise.
```

--------------------------------

### Load and Configure Local-Time with PostgreSQL

Loads the cl-postgres+local-time library and registers Local-Time's readers for PostgreSQL time and timestamp types.

```common-lisp
(ql:quickload :cl-postgres+local-time)
(local-time:set-local-time-cl-postgres-readers)
```

--------------------------------

### Timestamp Type

Documentation for the `timestamp` type, covering its creation, decoding, and conversion to/from universal time.

```APIDOC
## Timestamp Type

### Class `timestamp`

Represents an absolute timestamp with millisecond precision.

### Function `encode-timestamp`

#### Description
Creates a timestamp object.

#### Parameters
- `year` (integer) - The year.
- `month` (integer) - The month (1-12).
- `day` (integer) - The day of the month.
- `hour` (integer, optional) - The hour (default: 0).
- `minute` (integer, optional) - The minute (default: 0).
- `second` (integer, optional) - The second (default: 0).
- `millisecond` (integer, optional) - The millisecond (default: 0).

*Note: Avoid negative values or values outside their normal ranges (e.g., 60 for minutes, 1000 for milliseconds).* 

#### Returns
- `timestamp` - A timestamp object.

### Function `decode-timestamp`

#### Description
Decodes a timestamp object into its components.

#### Parameters
- `timestamp` (timestamp) - The timestamp object to decode.

#### Returns
- `values` (year, month, day, hour, minute, second, millisecond) - The components of the timestamp.

### Function `timestamp-to-universal-time`

#### Description
Converts a timestamp to the corresponding universal time, rounded to seconds. This function treats the timestamp as if it were in UTC.

#### Parameters
- `timestamp` (timestamp) - The timestamp to convert.

#### Returns
- `universal-time` - The universal time representation.

### Function `universal-time-to-timestamp`

#### Description
Creates a timestamp object from a universal time. The resulting timestamp is treated as if it were in UTC.

#### Parameters
- `universal-time` (universal-time) - The universal time to convert.

#### Returns
- `timestamp` - The corresponding timestamp object.
```

--------------------------------

### Enable Local-Time PostgreSQL Readers

Activates the PostgreSQL readers for the local-time library. This function is provided by the local-time library itself for integrating its time types with cl-postgres.

```common-lisp
(local-time:set-local-time-cl-postgres-readers)
```

--------------------------------

### Query Results with Binary Parameters

Presents the results of queries when parameters are passed in binary format. Note that the representation of some types (like numbers and booleans) may differ from text-based parameters.

```lisp
(query "select $1" 1 :single)
1
(query "select $1" 1.5 :single)
1.5
(query "select $1" T :single)
T
(query "select $1" nil :single)
NIL
(query "select $1" :NULL :single)
:NULL
```

--------------------------------

### Prepared Statement for Sovereign Query

Defines and uses a prepared statement to efficiently query the sovereign of a country by name. Prepared statements are parsed and planned once, improving performance for repeated queries.

```common-lisp
(defprepared sovereign-of
  (:select 'sovereign :from 'country :where (:= 'name '$1))
  :single!)
(sovereign-of "The Netherlands")
```

--------------------------------

### Retrieve DAO Instance by Primary Key

Retrieves an instance of the 'country' DAO class from the database, using the primary key 'Croatia' to identify the specific record.

```common-lisp
(get-dao 'country "Croatia")
```

--------------------------------

### List Tables Query using s-sql

Retrieves a list of table names from the 'pg_catalog.pg_class' system catalog using s-sql. It filters for regular tables and excludes system-related schemas.

```common-lisp
(sql (:select 'relname :from 'pg-catalog.pg-class
      :inner-join 'pg-catalog.pg-namespace :on (:= 'relnamespace
                                                   'pg-namespace.oid)
      :where (:and (:= 'relkind "r")
                   (:not-in 'nspname (:set "pg_catalog" "pg_toast"))
                   (:pg-catalog.pg-table-is-visible 'pg-class.oid))))
```

--------------------------------

### PostgreSQL Parameter Type Handling (Text Default)

Illustrates how PostgreSQL interprets parameters when their data type is not explicitly specified or cannot be inferred from the table schema. By default, Postmodern sends parameters as text.

```lisp
(query "select $1" 1 :single)
"1"
(query "select $1" 1.5 :single)
"1.5"
(query "select $1" T :single)
"true"
(query "select $1" nil :single)
"false"
(query "select $1" :NULL :single)
:NULL
```

--------------------------------

### Revert to Default cl-postgres SQL Readtable

Resets the cl-postgres SQL readtable to its default configuration. This is useful if you need to switch back from the simple-date readers to the standard cl-postgres readers.

```common-lisp
(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         cl-postgres::*default-sql-readtable*))
```

--------------------------------

### Export Simple-Date Timestamps as JSON Strings

Demonstrates exporting timestamp data retrieved from PostgreSQL using Simple-Date as JSON strings. It shows two methods: using encode-json-to-string directly and using the :json-strs query parameter.

```common-lisp
(encode-json-to-string (query (:select 'timestamp-without-time-zone
                               :from 'test-data
                               :where (:= 'id 1))
                               :single))
```

```common-lisp
(query (:select 'timestamp-with-time-zone
        :from 'test-data
        :where (:< 'id 3))
  :json-strs)
```

--------------------------------

### Define Advanced DAO Class with Constraints and Foreign Keys

Defines a 'country-c' DAO class for a 'countries' table. It includes an auto-incrementing 'id', a unique and non-empty 'name', and a foreign key 'region-id' referencing the 'regions' table. This demonstrates complex column definitions and constraints.

```common-lisp
(defclass country-c ()
  ((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))
  (:metaclass dao-class)
  (:table-name countries))
```

--------------------------------

### Retrieve DAO Instance with Composite Primary Key

Retrieves an instance of the 'points' DAO class from the database using composite primary key values 12 and 34.

```common-lisp
(get-dao 'points 12 34)
```

--------------------------------

### Encode a Timestamp

Creates a timestamp object with millisecond precision from year, month, day, hour, minute, second, and millisecond components. It's recommended to use valid ranges for time components.

```common-lisp
(simple-date:encode-timestamp 2023 10 26 14 30 0 500)
```

--------------------------------

### Compare Time Values for Less Than

Determines if the first time-related value is strictly less than the second. Supports comparison between dates, timestamps, and intervals, returning a boolean.

```common-lisp
(simple-date:time< #s(simple-date:date :year 2023 :month 10 :day 25) #s(simple-date:date :year 2023 :month 10 :day 26))
```

```common-lisp
(simple-date:time< #s(simple-date:interval :day 1) #s(simple-date:interval :hour 25))
```

--------------------------------

### Insert Single Row using s-sql

Inserts a single row into the 'country' table using the s-sql wrapper. It specifies the table, the columns to set, and their corresponding values. Supports null values for optional columns.

```common-lisp
(query (:insert-into 'country :set 'name "The Netherlands"
                                   'inhabitants 16800000
                                   'sovereign "Willem-Alexander"))

(query (:insert-into 'country :set 'name "Croatia"
                                   'inhabitants 4400000))
```

--------------------------------

### Define DAO Class with Single Primary Key

Defines a DAO class named 'country' for a 'country' table. It includes columns for name, inhabitants, and sovereign, with 'name' specified as the primary key. This allows for direct database record manipulation.

```common-lisp
(defclass country ()
  ((name :col-type string :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))
  (:metaclass dao-class)
  (:keys name))
```

--------------------------------

### Define DAO Class using define-dao-class Macro

Demonstrates the `define-dao-class` macro, a simplified way to define DAO classes introduced in Postmodern version 1.33.12. It automatically adds the `dao-class` metaclass and simplifies type declarations.

```common-lisp
(define-dao-class id-class ()
 ((id integer :initarg :id :accessor test-id)
  (email :col-type text :initarg :email :accessor email))
 (:keys id))
```

--------------------------------

### Insert Multiple Rows using Plain SQL

Inserts multiple rows into the 'country' table using standard SQL syntax. This approach allows for inserting multiple records in a single query by separating value sets with commas.

```sql
(query "insert into country (name, inhabitants, sovereign)
                            values
                              ('The Netherlands', 16800000, 'Willem-Alexander'),
                              ('Croatia', 4400000, NULL)")
```

--------------------------------

### Insert Multiple Rows using s-sql

Inserts multiple rows into the 'country' table simultaneously using s-sql. This method requires all rows to have the same columns specified. Null values are represented by :null.

```common-lisp
(query (:insert-rows-into 'country :columns 'name 'inhabitants 'sovereign
                                   :values '(("The Netherlands" 16800000 "Willem-Alexander")
                                             ("Croatia" 4400000 :null))))
```

--------------------------------

### Update Row using DAO Classes

Updates an existing row in the 'country' table using DAO classes. It retrieves a DAO instance, modifies its properties, and then updates the database.

```common-lisp
(let ((croatia (get-dao 'country "Croatia")))
  (setf (country-inhabitants croatia) 4500000)
  (update-dao croatia))
(query (:select '* :from 'country))
```

--------------------------------

### Encode a Date

Creates a date object from year, month, and day components. The resulting date object represents a calendar date without any time-of-day information.

```common-lisp
(simple-date:encode-date 2023 10 26)
```

--------------------------------

### Establish Single Long-Life Connection to PostgreSQL

This code snippet demonstrates how to establish a single, long-lived connection to a PostgreSQL database. It's suitable for applications that do not use multi-threading or create executables. The function takes database name, username, password, and host as arguments. It maintains a single connection for the entire Lisp instance's lifetime.

```common-lisp
(connect-toplevel "testdb" "foucault" "surveiller" "localhost")
```

```common-lisp
(connect-toplevel "testdb" "foucault" "surveiller" "216.27.61.135")
```

```common-lisp
(connect-toplevel "testdb" "foucault" "surveiller" "localhost" :port 5434)
```

```common-lisp
(connect-toplevel "testdb" "foucault" "surveiller" "localhost" :use-ssl :require)
```

--------------------------------

### Explicitly Specifying Parameter Type in PostgreSQL

Shows how to explicitly cast a parameter to a specific data type (e.g., integer) within the SQL query string, allowing PostgreSQL to interpret it correctly.

```lisp
(query "select $1::integer" 1 :single)
1
```

--------------------------------

### Insert Single Row using Plain SQL

Inserts a single row into the 'country' table using standard SQL syntax. Values are provided as string literals or numbers directly within the SQL query.

```sql
(query "insert into country (name, inhabitants, sovereign)
                            values ('The Netherlands', 16800000, 'Willem-Alexander')")

(query "insert into country (name, inhabitants)
                            values ('Croatia', 4400000)")
```

--------------------------------

### Insert Single Row using DAO Classes

Inserts a single row into the 'country' table by creating and inserting a DAO instance. This method uses object-oriented constructs to represent and persist data.

```common-lisp
(insert-dao (make-instance 'country :name "The Netherlands"
                                    :inhabitants 16800000
                                    :sovereign "Willem-Alexander"))
(insert-dao (make-instance 'country :name "Croatia"
                                    :inhabitants 4400000))
```

--------------------------------

### Convert Universal Time to Timestamp (UTC)

Creates a timestamp object from a universal time value. The resulting timestamp is treated as if it represents a time in Coordinated Universal Time (UTC).

```common-lisp
(simple-date:universal-time-to-timestamp 1698314400)
```

--------------------------------

### Format PostgreSQL Timestamps with TO_CHAR (SQL)

This SQL snippet demonstrates how to use the TO_CHAR function in PostgreSQL to format timestamp, time, and date columns. It shows different formatting options including timezone abbreviations (TZ) and UTC offsets (OF).

```sql
(query "(SELECT to_char(col_timestamp_with_time_zone, E'YYYY-MM-DD HH24:MI:SS'),
                to_char(col_timestamp_with_time_zone, E'YYYY-MM-DD HH24:MI:SS TZ'),
                to_char(col_timestamp_with_time_zone, E'YYYY-MM-DD HH24:MI:SS OF'),
                to_char(col_timestamptz, E'YYYY-MM-DD HH24:MI:SS'),
                to_char(col_timestamp, E'YYYY-MM-DD HH24:MI:SS'),
                to_char(col_time, E'HH24:MI:SS'),
                to_char(col_date, E'YYYY-MM-DD')
         FROM short_data_type_tests
         WHERE (id = 1))")
(("2020-10-30 19:30:54" "2020-10-30 19:30:54 EDT" "2020-10-30 19:30:54 -04"
"2020-10-30 19:30:54" "2020-10-30 19:30:54" "19:30:54" "2020-10-30"))
```

--------------------------------

### Define DAO Class with Composite Primary Key

Defines a DAO class named 'points' with a composite primary key consisting of 'x' and 'y' columns. This is useful for tables where a unique record is identified by multiple fields.

```common-lisp
(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))
```

--------------------------------

### Encode an Interval

Constructs an interval object representing a duration. It accepts various time units (years, months, weeks, days, hours, minutes, seconds, milliseconds) and can handle negative values.

```common-lisp
(simple-date:encode-interval :hour 2 :minute 30 :second 15)
```

--------------------------------

### Compare Time Values for Less Than or Equal To

Performs a less than or equal to comparison between two time-related values. This is the inverse of the time> operation and returns a boolean.

```common-lisp
(simple-date:time<= #s(simple-date:date :year 2023 :month 10 :day 26) #s(simple-date:date :year 2023 :month 10 :day 26))
```

```common-lisp
(simple-date:time<= #s(simple-date:interval :day 1) #s(simple-date:interval :hour 24))
```

--------------------------------

### Compare Time Values for Greater Than

Checks if the first time-related value is strictly greater than the second. This comparison applies to dates, timestamps, and intervals, returning a boolean result.

```common-lisp
(simple-date:time> #s(simple-date:date :year 2023 :month 10 :day 27) #s(simple-date:date :year 2023 :month 10 :day 26))
```

```common-lisp
(simple-date:time> #s(simple-date:interval :hour 25) #s(simple-date:interval :day 1))
```

--------------------------------

### Disconnect from Database

Closes all active top-level database connections managed by Postmodern.

```common-lisp
(disconnect-toplevel)
```

--------------------------------

### Add Time Values

Adds two time-related objects. Adding an interval to a date or timestamp yields a new date or timestamp. Adding two intervals results in a combined interval. Integers are treated as milliseconds.

```common-lisp
(simple-date:time-add #s(simple-date:date :year 2023 :month 10 :day 26) #s(simple-date:interval :day 5))
```

```common-lisp
(simple-date:time-add #s(simple-date:timestamp :year 2023 :month 10 :day 26 :hour 10) #s(simple-date:interval :hour 3))
```

```common-lisp
(simple-date:time-add #s(simple-date:interval :day 2) #s(simple-date:interval :hour 5))
```

--------------------------------

### Compare Time Values for Equality

Checks if two time-related values (dates, timestamps, or intervals) are equal. Returns a boolean: true if they represent the same time or period, false otherwise.

```common-lisp
(simple-date:time= #s(simple-date:date :year 2023 :month 10 :day 26) #s(simple-date:date :year 2023 :month 10 :day 26))
```

```common-lisp
(simple-date:time= #s(simple-date:interval :hour 1) #s(simple-date:interval :minute 60))
```

--------------------------------

### Convert Timestamp to Universal Time (UTC)

Converts a timestamp object to a universal time representation, rounding to the nearest second. This conversion treats the timestamp as if it were in Coordinated Universal Time (UTC).

```common-lisp
(simple-date:timestamp-to-universal-time #s(simple-date:timestamp :year 2023 :month 10 :day 26 :hour 14 :minute 30 :second 0 :millisecond 500))
```

--------------------------------

### Determine Day of the Week

Calculates the day of the week for a given date. The function returns an integer from 0 (Sunday) to 6 (Saturday).

```common-lisp
(simple-date:day-of-week #s(simple-date:date :year 2023 :month 10 :day 26))
```

--------------------------------

### Decode a Date

Extracts the year, month, and day components from a date object. This function allows you to access the individual elements of a previously created date.

```common-lisp
(multiple-value-bind (year month day) (simple-date:decode-date my-date)
  (format t "Year: ~a, Month: ~a, Day: ~a~%" year month day))
```

--------------------------------

### Reset to Default PostgreSQL Reader

Resets the cl-postgres SQL readtable back to the default configuration, effectively disabling the Simple-Date SQL readers.

```common-lisp
(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
          cl-postgres::*default-sql-readtable*))
```

--------------------------------

### Decode a Timestamp

Decomposes a timestamp object into its constituent parts: year, month, day, hour, minute, second, and millisecond. This allows for detailed inspection of a timestamp's value.

```common-lisp
(multiple-value-bind (year month day hour minute second millisecond) (simple-date:decode-timestamp my-timestamp)
  (format t "~a:~a:~a.~a on ~a/~a/~a~%" hour minute second millisecond month day year))
```
Tokens:
Raw