1 Introduction

Postmodern

A Common Lisp PostgreSQL programming interface

Postmodern is a Common Lisp library for interacting with PostgreSQL databases. It is under active development. Features are:

Efficient communication with the database server without need for foreign libraries.

Support for UTF-8 on Unicode-aware Lisp implementations

A syntax for mixing SQL and Lisp code

Convenient support for prepared statements and stored procedures

A metaclass for simple database-access objects

The biggest differences between this library and CLSQL/CommonSQL or cl-dbi are that Postmodern has no intention of being portable across different SQL implementations (it embraces non-standard PostgreSQL features), and approaches extensions like lispy SQL and database access objects in a quite different way. This library was written because the CLSQL approach did not really work for me, your mileage may vary.

Contents

Dependencies

The library depends on usocket (except on SBCL and ACL, where the built-in socket library is used), md5, closer-mop, bordeaux-threads if you want thread-safe connection pools, and CL+SSL when SSL connections are needed. As of version 1.3 it also depends on ironclad, base64 and uax-15 because of the requirement to support scram-sha-256 authentication.

Postmodern itself is split into four different packages, some of which can be used independently.

Simple-date is a very basic implementation of date and time objects, used to support storing and retrieving time-related SQL types. It is not loaded by default and you can use local-time instead.

CL-postgres is the low-level library used for interfacing with a PostgreSQL server over a socket.

S-SQL is used to compile s-expressions to strings of SQL code, escaping any Lisp values inside, and doing as much as possible of the work at compile time.

Finally, Postmodern itself is the library that tries to put all these things together into a convenient programming interface.

License

Postmodern is released under a zlib-style license. Which approximately means you can use the code in whatever way you like, except for passing it off as your own or releasing a modified version without indication that it is not the original.

Download and installation

A git repository with the most recent changes can be viewed or checked out at https://github.com/marijnh/Postmodern

Quickstart

This quickstart is intended to give you a feel of the way coding with Postmodern works. Further details about the workings of the library can be found in the reference manual.

Assuming you have already installed it, first load and use the system:

(ql:quickload :postmodern)
(use-package :postmodern)

If you have a PostgreSQL server running on localhost, with a database called 'testdb' on it, which is accessible for user 'foucault' with password 'surveiller', you can connect like this:

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

Which will establish a connection to be used by all code, except for that wrapped in a with-connection form, which takes the same arguments but only establishes the connection locally. If the Postgresql server is running on a port other than 5432, you would also pass the appropriate keyword port parameter. E.g.:

In many contexts, query strings and lists starting with keywords can be used interchangeably. The lists will be compiled to SQL. The S-SQL manual describes the syntax used by these expressions. Lisp values occurring in them are automatically escaped. In the above query, only constant values are used, but it is possible to transparently use run-time values as well:

That last argument, :single, indicates that we want the result not as a list of lists (for the result rows), but as a single value, since we know that we are only selecting one value. Some other options are :rows, :row, :column, :alists, and :none. Their precise effect is documented in the reference manual.

You do not have to pull in the whole result of a query at once, you can also iterate over it with the doquery macro:

The above defines a class that can be used to handle records in a table with three columns: name, inhabitants, and sovereign. In simple cases, the information above is enough to define the table as well:

This defines our table in the database. execute works like query, but does not expect any results back.

You can create tables directly without the need to define a class, and in more complicated cases, you will need to use the create-table operator. One example using s-sql rather than plain vanilla sql would be the following:

In the above case, the new table's name will be so-items (actually in the database it will be so_items because sql does not allow hyphens. The column item-id is an integer and cannot be null. The column so-id is also an integer, but is allowed to be null and is a foreign key to the id field in the so-headers table so-headers. The primary key is actually a composite of item-id and so-id. (If we wanted the primary key to be just item-id, we could have specified that in the form defining item-id.)

Let us go back to our approach using a dao class and add a few countries:

sql is a macro that will simply compile a query, it can be useful for seeing how your queries are expanded or if you want to do something unexpected with them.

As you can see, lists starting with keywords are used to express SQL commands and operators (lists starting with something else will be evaluated and then inserted into the query). Quoted symbols name columns or tables (keywords can also be used but might introduce ambiguities). The syntax supports subqueries, multiple joins, stored procedures, etc. See the S-SQL reference manual for a complete treatment.

The defprepared macro creates a function that takes the same amount of arguments as there are $X placeholders in the given query. The query will only be parsed and planned once (per database connection), which can be faster, especially for complex queries.

(disconnect-toplevel)

Authentication

Postmodern can use either md5 or scram-sha-256 authentication. Scram-sha-256 authentication is obviously more secure, but slower than md5, so take that into account if you are planning on opening and closing many connections without using a connection pooling setup..

Other authentication methods have not been tested. Please let us know if there is a authentication method that you believe should be considered.

Running tests

Postmodern uses FiveAM for
testing. The different component systems of Postmodern have tests
defined in corresponding test systems, each defining a test suite.
The test systems and corresponding top-level test suites are:

:postmodern in postmodern/tests,

:cl-postgres in cl-postgres/tests,

:s-sql in s-sql/tests, and

:simple-date in simple-date/tests.

Before running the tests make sure PostgreSQL is running and a test
database is created. By default tests use the following connection
parameters to run the tests:

Database name: test

User: test

Password:

Hostname: localhost

Port: 5432

Use-SSL :NO

If connection with these parameters fails then you will be asked to
provide the connection parameters interactively. The parameters will
be stored in cl-postgres-tests:*test-connection* variable and
automatically used on successive test runs. This variable can also be
set manually before running the tests.

To test a particular component one would first load the corresponding
test system, and then run the test suite. For example, to test the
postmodern system in the REPL one would do the following:

As you can see from above, database connection parameters can be
provided using environment variables:

DB_NAME: database name,

DB_USER: user,

DB_PASS: password,

DB_HOST: hostname.

Reference

The reference manuals for the different components of Postmodern are kept in separate files. For using the library in the most straightforward way, you only really need to read the Postmodern reference and glance over the S-SQL reference. The simple-date reference explains the time-related data types included in Postmodern, and the CL-postgres reference might be useful if you just want a low-level library for talking to a PostgreSQL server.

Caveats and to-dos

Timezones

It is important to understand how postgresql (not postmodern) handles timestamps and timestamps with time zones. Postgresql keeps everything in UTC, it does not store a timezone even in a timezone aware column. If you use a timestamp with timezone column, postgresql will calculate the UTC time and will normalize the timestamp data to UTC. When you later select the record, postgresql will look at the timezone for the postgresql session, retrieve the data and then provide the data recalculated from UTC to the timezone for that postgresql session. There is a good writeup of timezones at http://blog.untrod.com/2016/08/actually-understanding-timezones-in-postgresql.html and http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/.

Keeping that in mind, Simple-date has no concept of time zones. If you really need your time-keeping to be reliable and/or universal you might consider using local-time, which solves the same problem as simple-date, but does understand time zones. We are considering the best ways to make life easier for users of the two libraries.

Portability

The Lisp code in Postmodern is theoretically portable across implementations, and testing is normally done on sbcl, ccl and ecl. ABCL currently has issues with utf-8 and :null. Clisp has its normal issues with an outdated asdf. Please let us know if it does not work on the implementation that you normally use. Implementations that do not have meta-object protocol support will not have DAOs, but all other parts of the library should work (all widely used implementations do support this).

The library is not likely to work for PostgreSQL versions older than 8.4. Other features only work in newer Postgresql versions as the features were only introduced in those newer versions.

Things that should be implemented

Postmodern is under active development so issues and feature requests should
be flagged on [[https://github.com/marijnh/Postmodern][Postmodern's site on github]].

It would be a nice feature if Postmodern could help you with defining your
database schemas and, more importantly, updating your databases when your code
changes. It would theoretically not be hard to build a function that compares
a schema on the Lisp side with the state of the database, and helps you to
interactively update your database. PostgreSQL has a quite complete
introspection system. Unfortunately it would be a lot of work to implement
this, since databases can contain so many different types of entities
(tables, views, indices, procedures, constraints, sequences, etc.) which are
all created, changed, and dropped in different ways.

Some areas that are currently under consideration can be found in the ROADMAP.md
file.

6.1.2 Special variables

Special Variable: *allow-overwriting-prepared-statements*

When set to t, ensured-prepared will overwrite prepared statements having
the same name if the query statement itself in the postmodern meta connection
is different than the query statement provided to ensure-prepared.

Setting this to T will make S-SQL add double quotes around
identifiers in queries. Setting it :auto will turn on this behaviour
only for reserved words. Setting it to :literal will cause to-sql-name to
escape reserved words,but will not make other changes such as changing
forward slash to underscore.

Controls whether row values (as in select row(1, ’foo’) ) should be
received from the database in text or binary form. The default value
is nil, specifying that the results be sent back as text. Set this
to t to cause the results to be read as binary.

The exported special var holding the current read table, a hash
mapping OIDs to instances of the type-interpreter class that contain
functions for retreiving values from the database in text, and
possible binary, form.

Indicate whether S-SQL will use standard SQL strings (just use ”
for #’), or backslash-style escaping. Setting this to NIL is always
safe, but when the server is configured to allow standard
strings (parameter ’standard_conforming_strings’ is ’on’), the noise
in queries can be reduced by setting this to T.

Define a table. name can be either a symbol or a (symbol string)
list. In the first case, the table name is derived from the symbol by
S-SQL’s rules, in the second case, the name is given explicitly. The
body of definitions can contain anything that evaluates to a string,
as well as S-SQL expressions. In this body, the variables *table-name*
and *table-symbol* are bound to the relevant values.

Iterate over the rows in the result of a query, binding the given
names to the results and executing body for every row. Query can be a
string, an s-sql query, or a list starting with one of those, followed
by the arguments to parameterize the query with.

Writes an encoder and decoder function for floating point
numbers with the given amount of exponent and significand
bits (plus an extra sign bit). If support-nan-and-infinity-p is
true, the decoders will also understand these special cases. NaN
is represented as :not-a-number, and the infinities as
:positive-infinity and :negative-infinity. Note that this means
that the in- or output of these functions is not just floating
point numbers anymore, but also keywords.

Execute a query, optionally with arguments to put in the place of
$X elements. If one of the arguments is a known result style or a class name,
it specifies the format in which the results should be returned.

Define simple operators. Arity is one of :unary (like
’not’), :unary-postfix (the operator comes after the operand),
:n-ary (like ’+’: the operator falls away when there is only one
operand), :2+-ary (like ’=’, which is meaningless for one operand),
or :n-or-unary (like ’-’, where the operator is kept in the unary
case). After the arity follow any number of operators, either just a
keyword, in which case the downcased symbol name is used as the
operator, or a two-element list containing a keyword and a name
string.

Create a row-reader, using the given name for the fields argument
and the given body for reading the rows. A row reader is a function
that is used to do something with the results of a query. It has two
local functions: next-row and next-field, the first should be called
once per row and will return a boolean indicating whether there are
any more rows, the second should be called once for every element in
the fields vector, with that field as argument, to read a single value
in a row. See list-row-reader in public.lisp for an example.

Execute the body within a savepoint, releasing savepoint when the
body exits normally, and rolling back otherwise. NAME is both the
variable that can be used to release or rolled back before the body
unwinds, and the SQL name of the savepoint.

Execute the body within a database transaction, committing when the
body exits normally, and aborting otherwise. An optional name and/or
isolation-level can be given to the transaction. The name can be used to
force a commit or abort before the body unwinds. The isolation-level
will set the isolation-level used by the transaction.

Used inside a deftable form. Define a foreign key on this table.
Pass a table the index refers to, a list of fields or single field in
*this* table, and, if the fields have different names in the table
referred to, another field or list of fields for the target table, or
:primary-key to indicate that the other table’s primary key should be
referenced.

The cache hit ratio shows data on serving the data from memory compared to how often you have to go to disk.
This function returns a list of heapblocks read from disk, heapblocks hit from memory and the ratio of
heapblocks hit from memory / total heapblocks hit.
Borrowed from: https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/

Binds *database* to a new connection, as specified by the spec
argument, which should be a list of arguments that can be passed to
connect, and runs the function given as a second argument with that
database.

This function requires that postgresql extension pg_stat_statements must be loaded via shared_preload_libraries.
It is borrowed from https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/.
Optional parameters OB allow order-by to be ’calls’, ’total-time’, ’rows-per’ or ’time-per’, defaulting to time-per.
num-calls to require that the number of calls exceeds a certain threshold, and limit to limit the number of rows returned.
It returns a list of lists, each row containing the query, number of calls, total_time, total_time/calls, stddev_time, rows,
rows/calls and the cache hit percentage.

Given the name of a database, will return the name, a pretty-print string of
the size of the database and the size in bytes. If a database name is not provided,
it will return the result for the currently connected database.

Prepared statements are stored both in the meta slot in the postmodern
connection and in postgresql session information. In the case of prepared
statements generated with defprepared, there is also a lisp function with
the same name.

If you know the prepared statement name, you can delete the prepared statement
from both locations (the default behavior), just from postmodern by passing
:postmodern to the location key parameter or just from postgresql by passing
:postgresql to the location key parameter.

If you pass the name ’All’ as the statement name, it will
delete all prepared statements.

The default behavior is to also remove any lisp function of the same name.
This behavior is controlled by the remove-function key parameter.

Returns a list of sublists where the sublist contains two strings.
If a table primary key consists of only one column, such as ’id’ there
will be a single sublist where the first string is the name of the column
and the second string is the string name for the datatype for that column.
If the primary key for the table consists of more than one column, there
will be a sublist for each column subpart of the key. The sublists will
be in the order they are used in the key, not in the order they appear
in the table. If just-key is set to t, the list being returned will
contain just the column names in the primary key as string names
with no sublists. If the table is not in the public schema, provide
the fully qualified table name e.g. schema-name.table-name.

Returns a list of lists where each sub-list contains the name of the
database, a pretty-print string of the size of that database and the size in bytes.
The default order is by database name. Pass t as a parameter to :order-by-size for order by size.
Setting size to nil will return just the database names in a single list
ordered by name. This function excludes the template databases.

Syntactic sugar. A query that lists the prepared statements
in the session in which the function is run. If the optional
names-only parameter is set to t, it will only return a list
of the names of the prepared statements.

Returns a list of alists of rolenames, role attributes and membership in roles.
See https://www.postgresql.org/docs/current/role-membership.html for an explanation.
The optional parameter can be used to set the return list types to :alists or :plists.

Returns a list of lists (table-name, size in 8k pages) of tables in the current database.
Providing a name to the schema parameter will return just the information for tables in that schema.
It defaults to just the tables in the public schema. Setting schema to nil will return all tables, indexes etc
in the database in descending order of size. This would include system tables, so there
are a lot more than you would expect. If :size is set to nil, it returns only a flat list of table names.
Setting order-by-size to t will return the result in order of size instead of by table name.

Returns more table info than table-description. Table can be either a string or quoted.
Specifically returns ordinal-position, column-name, data-type, character-maximum-length,
modifier, whether it is not-null and the default value.

Create and connect a database object. use-ssl may be :no, :try, :yes, or
:full (NOTE: :yes only verifies that the server cert is issued by a trusted CA,
but does not verify the server hostname; use :full to also verify the hostname).

Takes a postgresql version number which should be a string with the major and minor versions separated by a period e.g. ’12.2’ or ’9.6.17’. Checks against the connection understanding of the running postgresql version and returns t if the running version is the requested version or newer.

Read utf-8 encoded data from a byte stream and construct a
string with the characters found. When null-terminated is given
it will stop reading at a null character, stop-at-eof tells it to
stop at the end of file without raising an error, and the
char-length and byte-length parameters can be used to specify the
max amount of characters or bytes to read.

If you have received an invalid-prepared-statement error or a prepared-statement
already exists error but the prepared statement is still in the meta slot in
the postmodern connection, try to regenerate the prepared statement at the
database connection level and restart the connection.

This changes the postgresql runtime parameter controlling what order
schemas are searched. You can always use fully qualified names [schema.table].
By default, this function only changes the search path for the current session.

Check whether a table exists in a particular schema. Defaults to the search path.
Takes either a string or a symbol for the table name. The table-name can be fully
qualified in the form of schema.table-name or database.schema.table-name. If
the schema is specified either in a qualified table-name or in the optional
schema-name parameter, we look directly to the information schema tables. Otherwise
we use the search path which can be controlled by being within a with-schema form.

Convert a symbol or string into a name that can be a sql table,
column, or operation name. Add quotes when escape-p is true, or
escape-p is :auto and the name contains reserved words.
Quoted or delimited identifiers can be used by passing :literal as
the value of escape-p. If escape-p is :literal, and the name is a string then
the string is still escaped but the symbol or string is not downcased,
regardless of the setting for *downcase-symbols* and the hyphen
and forward slash characters are not replaced with underscores.

Ignore-reserved-words is only used internally for column names which are allowed
to be reserved words, but it is not recommended.

Returns list of slot names that are the primary key of DAO
class. This is likely interesting if you have primary keys which are composed
of more than one slot. Pay careful attention to situations where the primary
key not only has more than one column, but they are actually in a different
order than they are in the database table itself. You can check this with the
find-primary-key-info function.

Turn a lisp value into a string containing its SQL
representation. Returns an optional second value that indicates
whether the string should be escaped before being put into a query.
Generally any string is going to be designated to be escaped

Where: an indication of the context in which the error occurred. Presently this includes a call stack traceback of active procedural language functions and internally-generated queries. The trace is one entry per line, most recent first.

Position: the field value is a decimal ASCII integer, indicating an error cursor position as an index into the original query string. The first character has index 1, and positions are measured in characters not bytes.

A hook for locally overriding/adding behaviour to DAO row readers.
Should be an alist mapping strings (column names) to symbols or
functions. Symbols are interpreted as slot names that values should be
written to, functions are called with the new object and the value as
arguments.

A slightly convoluted macro for defining interpreter functions. It
allows two forms. The first is to pass a single type identifier, in
which case a value of this type will be read and returned directly.
The second is to pass a list of lists containing names and types, and
then a body. In this case the names will be bound to values read from
the socket and interpreted as the given types, and then the body will
be run in the resulting environment. If the last field is of type
bytes, string, or uint2s, all remaining data will be read and
interpreted as an array of the given type.

Macro to make defining syntax a bit more straightforward. Name
should be the keyword identifying the operator, arglist a lambda list
to apply to the arguments, and body something that produces a list of
strings and forms that evaluate to strings.

This macro synthesizes a function to send messages of a specific
type. It takes care of the plumbing – calling writer functions on a
stream, keeping track of the length of the message – so that the
message definitions themselves stay readable.

Helper macro for reading messages from the server. A list of cases
(characters that identify the message) can be given, each with a body
that handles the message, or the keyword :skip to skip the message.
Cases for error and warning messages are always added.

The body may contain an initial parameter of the form :LENGTH-SYM SYMBOL
where SYMBOL is a symbol to which the remaining length of the packet is
bound. This value indicates the number of bytes that have to be read
from the socket.

Handles arguments to some complex SQL operations. Arguments
are divided by keywords, which are interned with the name of the
non-keyword symbols in words, and bound to these symbols. After the
naming symbols, a ? can be used to indicate this argument group is
optional, an * to indicate it can consist of more than one element,
and a - to indicate it does not take any elements. When used, keywords
must appear in the order defined.

This is used to prevent a row-reader from recursively calling some
query function. Because the connection is still returning results from
the previous query when a row-reading is being executed, starting
another query will not work as expected (or at all, in general). This
might also raise an error when you are using a single database
connection from multiple threads, but you should not do that at all.
Also binds *timestamp-format* and *connection-params*, which might be
needed by the code interpreting the query results.

Takes a user-name, a client-nonce, a server response and a password. If the server response is not in the form of an array of bytes which are encoded in base64, the response type must be specified as either :base64-string or :utf8-string. The client-nonce should be a normal utf8 string.
It returns the server-response as a normal string, the server-provided-salt as a normal string, and the server-iterations as an integer.

The allowed response-types are :base64-string, :base64-usb8-array and :utf8-string.

Bind a prepared statement, ask for the given formats, and pass the
given parameters, that can be either string or byte vector.
(vector (unsigned-byte 8)) parameters will be sent as binary data, useful
for binding data for binary long object columns.

Synthesise a number of methods for a newly defined DAO class.
(Done this way because some of them are not defined in every
situation, and each of them needs to close over some pre-computed
values. Notes for future maintenance: Fields are the slot names
in a dao class. Field-sql-name returns the col-name for the
postgresql table, which may or may not be the same as the slot
names in the class and also may have no relation to the initarg
or accessor or reader.)

Retrieves a list consisting of the pid and the secret-key from the connection, not from the database itself.
These are needed for cancelling connections and error processing with respect to prepared statements.

Available parameters - in order after name - are :concurrently, :on, :using, :fields
and :where.The advantage to using the keyword :concurrently is that writes to the table
from other sessions are not locked out while the index is is built. The disadvantage is
that the table will need to be scanned twice. Everything is a trade-off.

Process table constraints that precede the closing parentheses in the table definition for the base level create table.
The difference between this and the expand-table-constraint-sok function is the parameter list
signature. This expects to receive no sublists. The expand-table-constraint-sok function expects to list of sublists.
This is done to maintain backwards compatibility and most general users do not need the extended version.

Foreign keys have defaults on-delete restrict, on-update restrict, and match simple. If you want
to change those defaults, you need to specify them in that order.

Per the postgresql documentation at https://www.postgresql.org/docs/10/static/sql-createtable.html

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)

Expand-table-constraint for the create-extended-table sql-op. The difference between the two is the parameter list
signature. This expects a list of sublists. The regular expand-table-constraint expects to receive no sublists.
DOES NOT IMPLEMENT POSTGRESQL FUNCTION EXCLUDE.

Takes an password (must be an ascii string) and server salt (by default presumed byte-array but can be set for :string or :hex) and an integer iterations. Digest is presumed to be :sha256 but can be set to other valid ironclad digests. returns a byte-array

Helper function for the following two macros. Note that it will attempt to
automatically reconnect if database-connection-error, or admin-shutdown. It
will reset any prepared statements triggering an invalid-sql-statement-name
error. The generated function will overwrite old prepared statements triggering
a duplicate-prepared-statement error and will pre-emptively overwrite an existing
prepared statement of the same name the first time generate-prepared is called
for this function name. Subsequent calls to the generated function will not
overwrite unless postgresql throws a duplicate-prepared-statement error.

Initiate SSL handshake with the PostgreSQL server, and wrap the socket in
an SSL stream. When require is true, an error will be raised when the server
does not support SSL. When hostname is supplied, the server’s certificate will
be matched against it.

Read server messages until either a new row can be read, or there
are no more results. Return a boolean indicating whether any more
results are available, and, if available, stores the amount of
effected rows in *effected-rows*. Also handle getting out of
copy-in/copy-out states (which are not supported).

Takes a server response and a client-nonce. If the server response is not in the form of an array of bytes which are encoded in base64, the response type must be specified as either :base64-string or :utf8-string. The client-nonce should be a normal utf8 string.
It returns the server-response as a normal string, the server-provided-salt as a normal string, and the server-iterations as an integer

Scans string. If any character should be mapped to nothing, it eliminates that character. If any character is not printable ascii, it returns nil. If every character remaining after eliminations is printable ascii, it returns the printable-ascii string.

Takes an array of bytes which are encoded in base64, It should return a list of three alists of the form:
(("r" . "odaUyoz0GpB5GxXLfe2Y8SVjZEosREsxzxhtXY1jiNebxJlohG8IRD1v")
("s" . "HV25Sl/1VAUF7k+Ddv42dQ==") ("i" . "4096") where "r" is the server nonce,
"s" is a base64 encoded salt and "i" is the number of iterations for the hash digest.

We do not use split-sequence building the cons cell because the equal sign can appear in the nonce or salt itself.

Given a ratio, a stream and a digital-length-limit, if *silently-truncate-ratios* is true,
will return a potentially truncated ratio. If false and the digital-length-limit is reached,
it will throw an error noting the loss of precision and offering to continue or reset
*silently-truncate-ratios* to true. Code contributed by Attila Lendvai.

DEPRECATED. The same as write-ratio-as-floating point. Note the difference between rational and ratio.
Kept for backwards compatibility.
Given a ratio, a stream and a digital-length-limit, if *silently-truncate-rationals* is true,
will return a potentially truncated ratio. If false and the digital-length-limit is reached,
it will throw an error noting the loss of precision and offering to continue or reset
*silently-truncate-rationals* to true. Code contributed by Attila Lendvai.

This query sql-op takes one or more table names and will truncate
those tables (deleting all the rows. The following keyword parameters
are optionally allowed and must be in this order.
:only will truncate only this table and not descendent tables.
:restart-identity will restart any sequences owned by the table.
:continue-identity will continue sequences owned by the table.
:cascade will cascade the truncation through tables using foreign keys.

Method: expand-sql-op(OP1 (eql drop-rule)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql drop-type)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql drop-view)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql drop-sequence)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql drop-index)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql drop-table)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql cascade)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql create-unique-index)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql create-index)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql alter-sequence)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql alter-table)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql create-extended-table)) ARGS-NAME0

Create a table with more complete syntax where table-constraints and extended-table-constraints are lists.
Note that with extended tables you can have tables without columns that are inherited or partitioned.

Method: expand-sql-op(OP1 (eql create-table)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql create-composite-type)) ARGS-NAME0

Creates a composite type with a type-name and two or more
columns. Sample call would be:
(sql (:create-composite-type ’fullname (first-name text) (last-name text)))

Method: expand-sql-op(OP1 (eql window)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql with-recursive)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql with)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql parens)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql partition-by)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql over)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql delete-from)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql update)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql insert-rows-into)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql notify)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql unlisten)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql listen)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql insert-into)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql function)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql for-share)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql for-update)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql set-constraints)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql order-by)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql limit)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql var-samp)) ARGS-NAME0

The var-samp function returns the sample variance of the input values (square of the sample standard deviation).

Example:

(query (:select (:var-samp ’salary) :from ’people))

Method: expand-sql-op(OP1 (eql var-pop)) ARGS-NAME0

The var-pop function returns the population variance of the input values (square of the population standard deviation).

Example:

(query (:select (:var-pop ’salary) :from ’people))

Method: expand-sql-op(OP1 (eql variance)) ARGS-NAME0

Variance is a historical alias for var_samp. The variance function returns the sample
variance of the input values (square of the sample standard deviation).

Example:

(query (:select (:variance ’salary) :from ’people))

Method: expand-sql-op(OP1 (eql stddev-samp)) ARGS-NAME0

The stddev-samp function returns the sample standard deviation of the input values.

Example:

(query (:select (:stddev-samp ’salary) :from ’people))

Method: expand-sql-op(OP1 (eql stddev-pop)) ARGS-NAME0

The stddev-pop function returns the population standard deviation of the input values.

Example:

(query (:select (:stddev-pop ’salary) :from ’people))

Method: expand-sql-op(OP1 (eql stddev)) ARGS-NAME0

The stddev function returns the the sample standard deviation of the input values. It is a historical alias
for stddev-samp.

Example:

(query (:select (:stddev ’salary) :from ’people))

Method: expand-sql-op(OP1 (eql regr-syy)) ARGS-NAME0

The regr-syy function returns the sum(Y^2) - sum(Y)^2/N (“sum of squares” of the dependent variable).

The regr-sxx function returns the sum(X^2) - sum(X)^2/N (“sum of squares” of the independent variable).

Example:

(query (:select (:regr-sxx ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql regr-slope)) ARGS-NAME0

The regr-slope function returns the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Example:

(query (:select (:regr-slope ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql regr-r2)) ARGS-NAME0

The regr-r2 function returns the square of the correlation coefficient.

Example:

(query (:select (:regr-r2 ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql regr-intercept)) ARGS-NAME0

The regr-intercept function returns the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Example:

(query (:select (:regr-intercept ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql regr-count)) ARGS-NAME0

The regr-count function returns the number of input rows in which both expressions are nonnull.

Example:

(query (:select (:regr-count ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql regr-avgy)) ARGS-NAME0

The regr-avgy function returns the average of the dependent variable (sum(Y)/N).

Example:

(query (:select (:regr-avgy ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql regr-avgx)) ARGS-NAME0

The regr-avgx function returns the average of the independent variable (sum(X)/N)

Example:

(query (:select (:regr-avgx ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql mode)) ARGS-NAME0

Mode is used to find the most frequent input value in a group. See e.g. https://www.postgresql.org/docs/10/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE
and article at https://tapoueh.org/blog/2017/11/the-mode-ordered-set-aggregate-function/.

Method: expand-sql-op(OP1 (eql array-agg)) ARGS-NAME0

Array-agg returns a list of values concatenated into an array.
Allowable optional keyword parameters are :distinct, :order-by and :filter.

Returns a list containing a string of two parentheses as an empty set.

Method: expand-sql-op(OP1 (eql set)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql case)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql between-symmetric)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql between)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql covar-samp)) ARGS-NAME0

The covar-samp function returns the sample covariance between a set of dependent and independent variables.

Example:

(query (:select (:covar-samp ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql covar-pop)) ARGS-NAME0

The covar-pop function returns the population covariance between a set of dependent and independent variables.

Example:

(query (:select (:covar-pop ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql corr)) ARGS-NAME0

The corr function returns the correlation coefficient between a set of dependent and independent variables.

Example:

(query (:select (:corr ’height ’weight) :from ’people))

Method: expand-sql-op(OP1 (eql percentile-dist)) ARGS-NAME0

Requires Postgresql 9.4 or higher. There are two required keyword parameters :fraction and :order-by.
Percentile-dist returns the first input value whose position in the ordering equals or exceeds
the specified fraction. If the fraction parameter is an array eturns an array of results matching
the shape of the fractions parameter, with each non-null element replaced by the input value
corresponding to that percentile.

Requires Postgresql 9.4 or higher. Percentile-cont returns a value corresponding to the specified fraction in the ordering,
interpolating between adjacent input items if needed.
There are two required keyword parameters :fraction and :order-by. If the fraction value is an array,
then it returns an array of results matching the shape of the fractions parameter, with each non-null
element replaced by the value corresponding to that percentile.

Returns true if all input values are true, otherwise false. Allowed keyword parameters are distinct and filter.
Note that if the filter keyword used, the filter must be last in the every args.
If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter clause (s-sql will properly expand it).
E.g. (query (:select ’* (:every (:like ’studname "%h"))
:from ’tbl-students
:group-by ’studname ’studid ’studgrades))
See tests.lisp for examples.

Method: expand-sql-op(OP1 (eql min)) ARGS-NAME0

Returns the minimum value of a set of values. Allowed keyword parameters are distinct and filter.
Note that if the filter keyword is used, the filter must be last in the min args.
If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter clause (s-sql will properly expand it).
E.g. (query (:select (:min ’*) (:min ’* :filter (:= 1 ’bid)) ’id :from ’pbbench-history))
See tests.lisp for more examples.

Method: expand-sql-op(OP1 (eql max)) ARGS-NAME0

Max returns the maximum value of a set of values. Allowed keyword parameters are distinct and filter.
Note that if the filter keyword is used,
the filter must be last in the max args. If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter clause (s-sql will properly expand it).
E.g. (query (:select (:max ’*) (:max ’* :filter (:= 1 ’bid)) ’id :from ’pbbench-history))
See tests.lisp for more examples.

Method: expand-sql-op(OP1 (eql sum)) ARGS-NAME0

Sum calculates the total of a list of values. Allowed keyword parameters are distinct and filter.
Note that if the keyword filter is used,
the filter must be last in the sum args. If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter clause (s-sql will properly expand it).
E.g. (query (:select (:sum ’*) (:sum ’* :filter (:= 1 ’bid)) ’id :from ’pbbench-history))
See tests.lisp for examples.

Method: expand-sql-op(OP1 (eql avg)) ARGS-NAME0

Avg calculates the average value of a list of values. Allowed keyword parameters are distinct and filter.
Note that if the filter keyword is used,
the filter must be last in the avg args. If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter clause (s-sql will properly expand it).
E.g. (query (:select (:avg ’*) (:avg ’* :filter (:= 1 ’bid)) ’id :from ’pbbench-history))
See tests.lisp for examples.

Method: expand-sql-op(OP1 (eql count)) ARGS-NAME0

Count returns the number of rows. It can be the number of rows collected by the select statement as in

(query (:select (:count ’*) :from ’table1 :where (:= ’price 100)))

or it can be a smaller number of rows based on the allowed keyword parameters :distinct and :filter as in

Note that if used, the filter must be last in the count args. If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter clause.
E.g. (sql (:select (:count ’*) (:count ’* :filter (:= 1 ’bid)) ’id :from ’pbbench-history))
See tests.lisp for examples.

Method: expand-sql-op(OP1 (eql values)) ARGS-NAME0

values statement

Method: expand-sql-op(OP1 (eql extract)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql not-in)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql in)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql not-null)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql is-null)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql exists)) ARGS-NAME0

Method: expand-sql-op(OP1 (eql cast)) ARGS-NAME0

Cast is one of two functions that help convert one type of data to another.
The other function is type. An example use of cast is:

LOCALTIME and LOCALTIMESTAMP deliver values without time zone. They optionally
take a precision parameter, which causes the result to be rounded to that many
fractional digits in the seconds field. Without a precision parameter, the
result is given to the full available precision.

Method: expand-sql-op(OP1 (eql local-timestamp)) ARGS-NAME0

LOCALTIME and LOCALTIMESTAMP deliver values without time zone. They optionally
take a precision parameter, which causes the result to be rounded to that many
fractional digits in the seconds field. Without a precision parameter, the
result is given to the full available precision. Precision
only applies to seconds.

Method: expand-sql-op(OP1 (eql current-time)) ARGS-NAME0

Current-time and Current-timestamp deliver values with time zones. They
optionally take a precision parameter, which causes the result to be rounded
to that many fractional digits in the seconds field. Without a precision
parameter, the result is given to the full available precision. Precision
only applies to seconds.

Method: expand-sql-op(OP1 (eql current-timestamp)) ARGS-NAME0

Current-time and Current-timestamp deliver values with time zones. They
optionally take a precision parameter, which causes the result to be rounded
to that many fractional digits in the seconds field. Without a precision
parameter, the result is given to the full available precision. Precision
only applies to seconds.

Method: expand-sql-op(OP1 (eql current-date)) ARGS-NAME0

Provides the current time. The default is universal time. If you want
a more human readable approach, you can use :to-char. As an example:
(query (:select (:current-date) (:to-char (:current-date) "YYYY-MM-DD")))
((3751488000 "2018-11-18"))

Method: expand-sql-op(OP1 (eql interval)) ARGS-NAME0

Interval takes a string.
See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT-EXAMPLES.
It optionally take a precision parameter, which causes the result to be rounded
to that many fractional digits in the seconds field. Without a precision
+parameter, the result is given to the full available precision. Precision
only applies to seconds.

This handles statements that include functions in the query such as (:+ 1 2), (:pi) in the array whereas just
passing an array as #(1.0 2.4) does not and you are not selecting into an array, so do not use :array.

Method: expand-sql-op(OP1 (eql array)) ARGS-NAME0

This is used when calling a select query into an array. See sample usage.