Next: Introduction, Previous: (dir), Up: (dir) [Contents][Index]
This is the postmodern Reference Manual, generated automatically by Declt version 3.0 "Montgomery Scott" on Wed Feb 19 21:21:21 2020 GMT+0.
| • Introduction | What postmodern is all about | |
| • Systems | The systems documentation | |
| • Modules | The modules documentation | |
| • Files | The files documentation | |
| • Packages | The packages documentation | |
| • Definitions | The symbols documentation | |
| • Indexes | Concepts, functions, variables and data types |
Postmodern is a Common Lisp library for interacting with PostgreSQL databases. It is under active development. Features are:
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.
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.
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. 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.
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.
We suggest using quicklisp for installation.
A git repository with the most recent changes can be viewed or checked out at https://github.com/marijnh/Postmodern
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.
Now for a basic sanity test:
(query "select 22, 'Folie et déraison', 4.5")
;; => ((22 "Folie et déraison" 9/2))
That should work. query is the basic way to send queries to the database. The same query can be expressed like this:
(query (:select 22 "Folie et déraison" 4.5))
;; => ((22 "Folie et déraison" 9/2))
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:
(defun database-powered-addition (a b)
(query (:select (:+ a b)) :single))
(database-powered-addition 1030 204)
;; => 1234
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:
(doquery (:select 'x 'y :from 'some-imaginary-table) (x y)
(format t "On this row, x = ~A and y = ~A.~%" x y))
You can work directly with the database or you can use a database-access-class (aka dao). This is what a database-access class looks like:
(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))
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:
(dao-table-definition 'country)
;; => "CREATE TABLE country (
;; name TEXT NOT NULL,
;; inhabitants INTEGER NOT NULL,
;; sovereign TEXT,
;; PRIMARY KEY (name))"
(execute (dao-table-definition 'country))
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 would be the following:
(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)))
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:
(insert-dao (make-instance 'country :name "The Netherlands"
:inhabitants 16800000
:sovereign "Willem-Alexander"))
(insert-dao (make-instance 'country :name "Croatia"
:inhabitants 4400000))
Then, to update Croatia's population, we could do this:
(let ((croatia (get-dao 'country "Croatia")))
(setf (country-inhabitants croatia) 4500000)
(update-dao croatia))
(query (:select '* :from 'country))
;; => (("The Netherlands" 16800000 "Willem-Alexander")
;; ("Croatia" 4500000 :NULL))
Next, to demonstrate a bit more of the S-SQL syntax, here is the query the utility function list-tables uses to get a list of the tables in a database:
(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))))
;; => "(SELECT relname FROM pg_catalog.pg_class
;; INNER JOIN pg_catalog.pg_namespace ON (relnamespace = pg_namespace.oid)
;; WHERE ((relkind = 'r') and (nspname NOT IN ('pg_catalog', 'pg_toast'))
;; and pg_catalog.pg_table_is_visible(pg_class.oid)))"
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.
Finally, here is an example of the use of prepared statements:
(defprepared sovereign-of
(:select 'sovereign :from 'country :where (:= 'name '$1))
:single!)
(sovereign-of "The Netherlands")
;; => "Willem-Alexander"
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)
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:
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:
(ql:quickload "postmodern/tests")
(5am:run! :postmodern)
;; ... test output ...
It is also possible to test multiple components at once by first loading test systems and then running all tests:
(ql:quickload '("cl-postgres/tests" "s-sql/tests"))
(5am:run-all-tests)
;; ... test output ...
To run the tests from command-line specify the same forms using your implementation's command-line syntax. For instance, to test all Postmodern components on SBCL, use the following command:
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)'
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.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.
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.
The Lisp code in Postmodern is theoretically portable across implementations, and seems to work on all major ones and even less major ones such as Genera. 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.
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.
Next: Modules, Previous: Introduction, Up: Top [Contents][Index]
The main system appears first, followed by any subsystem dependency.
| • The postmodern system | ||
| • The s-sql system | ||
| • The cl-postgres system |
Next: The s-sql system, Previous: Systems, Up: Systems [Contents][Index]
Sabra Crolleton <sabra.crolleton@gmail.com>
Marijn Haverbeke <marijnh@gmail.com>
zlib
PostgreSQL programming API
postmodern.asd (file)
postmodern (module)
Next: The cl-postgres system, Previous: The postmodern system, Up: Systems [Contents][Index]
Sabra Crolleton <sabra.crolleton@gmail.com>
Marijn Haverbeke <marijnh@gmail.com>
zlib
Lispy DSL for SQL
s-sql.asd (file)
s-sql (module)
Previous: The s-sql system, Up: Systems [Contents][Index]
Sabra Crolleton <sabra.crolleton@gmail.com>
Marijn Haverbeke <marijnh@gmail.com>
zlib
Low-level client library for PostgreSQL
cl-postgres.asd (file)
cl-postgres (module)
Modules are listed depth-first from the system components tree.
| • The postmodern/postmodern module | ||
| • The s-sql/s-sql module | ||
| • The cl-postgres/cl-postgres module |
Next: The s-sql/s-sql module, Previous: Modules, Up: Modules [Contents][Index]
postmodern (system)
postmodern/
Next: The cl-postgres/cl-postgres module, Previous: The postmodern/postmodern module, Up: Modules [Contents][Index]
s-sql (system)
s-sql/
Previous: The s-sql/s-sql module, Up: Modules [Contents][Index]
cl-postgres (system)
cl-postgres/
Files are sorted by type and then listed depth-first from the systems components trees.
| • Lisp files |
Next: The s-sql․asd file, Previous: Lisp files, Up: Lisp files [Contents][Index]
postmodern.asd
postmodern (system)
Next: The cl-postgres․asd file, Previous: The postmodern․asd file, Up: Lisp files [Contents][Index]
s-sql.asd
s-sql (system)
Next: The postmodern/postmodern/package․lisp file, Previous: The s-sql․asd file, Up: Lisp files [Contents][Index]
cl-postgres.asd
cl-postgres (system)
Next: The postmodern/postmodern/connect․lisp file, Previous: The cl-postgres․asd file, Up: Lisp files [Contents][Index]
postmodern (module)
postmodern/package.lisp
Next: The postmodern/postmodern/query․lisp file, Previous: The postmodern/postmodern/package․lisp file, Up: Lisp files [Contents][Index]
package.lisp (file)
postmodern (module)
postmodern/connect.lisp
Next: The postmodern/postmodern/prepare․lisp file, Previous: The postmodern/postmodern/connect․lisp file, Up: Lisp files [Contents][Index]
connect.lisp (file)
postmodern (module)
postmodern/query.lisp
Next: The postmodern/postmodern/util․lisp file, Previous: The postmodern/postmodern/query․lisp file, Up: Lisp files [Contents][Index]
query.lisp (file)
postmodern (module)
postmodern/prepare.lisp
Next: The postmodern/postmodern/transaction․lisp file, Previous: The postmodern/postmodern/prepare․lisp file, Up: Lisp files [Contents][Index]
query.lisp (file)
postmodern (module)
postmodern/util.lisp
Next: The postmodern/postmodern/namespace․lisp file, Previous: The postmodern/postmodern/util․lisp file, Up: Lisp files [Contents][Index]
query.lisp (file)
postmodern (module)
postmodern/transaction.lisp
Next: The postmodern/postmodern/execute-file․lisp file, Previous: The postmodern/postmodern/transaction․lisp file, Up: Lisp files [Contents][Index]
query.lisp (file)
postmodern (module)
postmodern/namespace.lisp
do-with-schema (function)
Next: The postmodern/postmodern/table․lisp file, Previous: The postmodern/postmodern/namespace․lisp file, Up: Lisp files [Contents][Index]
query.lisp (file)
postmodern (module)
postmodern/execute-file.lisp
Next: The postmodern/postmodern/deftable․lisp file, Previous: The postmodern/postmodern/execute-file․lisp file, Up: Lisp files [Contents][Index]
postmodern-use-mop
postmodern (module)
postmodern/table.lisp
Next: The s-sql/s-sql/package․lisp file, Previous: The postmodern/postmodern/table․lisp file, Up: Lisp files [Contents][Index]
postmodern (module)
postmodern/deftable.lisp
Next: The s-sql/s-sql/s-sql․lisp file, Previous: The postmodern/postmodern/deftable․lisp file, Up: Lisp files [Contents][Index]
Next: The cl-postgres/cl-postgres/package․lisp file, Previous: The s-sql/s-sql/package․lisp file, Up: Lisp files [Contents][Index]
s-sql (module)
s-sql/s-sql.lisp
Next: The cl-postgres/cl-postgres/features․lisp file, Previous: The s-sql/s-sql/s-sql․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/package.lisp
*optimize* (special variable)
Next: The cl-postgres/cl-postgres/errors․lisp file, Previous: The cl-postgres/cl-postgres/package․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/features.lisp
Next: The cl-postgres/cl-postgres/sql-string․lisp file, Previous: The cl-postgres/cl-postgres/features․lisp file, Up: Lisp files [Contents][Index]
package.lisp (file)
cl-postgres (module)
cl-postgres/errors.lisp
Next: The cl-postgres/cl-postgres/trivial-utf-8․lisp file, Previous: The cl-postgres/cl-postgres/errors․lisp file, Up: Lisp files [Contents][Index]
package.lisp (file)
cl-postgres (module)
cl-postgres/sql-string.lisp
Next: The cl-postgres/cl-postgres/strings-utf-8․lisp file, Previous: The cl-postgres/cl-postgres/sql-string․lisp file, Up: Lisp files [Contents][Index]
package.lisp (file)
cl-postgres (module)
cl-postgres/trivial-utf-8.lisp
Next: The cl-postgres/cl-postgres/communicate․lisp file, Previous: The cl-postgres/cl-postgres/trivial-utf-8․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/strings-utf-8.lisp
Next: The cl-postgres/cl-postgres/messages․lisp file, Previous: The cl-postgres/cl-postgres/strings-utf-8․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/communicate.lisp
Next: The cl-postgres/cl-postgres/oid․lisp file, Previous: The cl-postgres/cl-postgres/communicate․lisp file, Up: Lisp files [Contents][Index]
communicate.lisp (file)
cl-postgres (module)
cl-postgres/messages.lisp
Next: The cl-postgres/cl-postgres/ieee-floats․lisp file, Previous: The cl-postgres/cl-postgres/messages․lisp file, Up: Lisp files [Contents][Index]
package.lisp (file)
cl-postgres (module)
cl-postgres/oid.lisp
Next: The cl-postgres/cl-postgres/interpret․lisp file, Previous: The cl-postgres/cl-postgres/oid․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/ieee-floats.lisp
Next: The cl-postgres/cl-postgres/protocol․lisp file, Previous: The cl-postgres/cl-postgres/ieee-floats․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/interpret.lisp
Next: The cl-postgres/cl-postgres/public․lisp file, Previous: The cl-postgres/cl-postgres/interpret․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/protocol.lisp
Next: The cl-postgres/cl-postgres/bulk-copy․lisp file, Previous: The cl-postgres/cl-postgres/protocol․lisp file, Up: Lisp files [Contents][Index]
cl-postgres (module)
cl-postgres/public.lisp
Previous: The cl-postgres/cl-postgres/public․lisp file, Up: Lisp files [Contents][Index]
public.lisp (file)
cl-postgres (module)
cl-postgres/bulk-copy.lisp
Next: Definitions, Previous: Files, Up: Top [Contents][Index]
Packages are listed by definition order.
Next: The postmodern package, Previous: Packages, Up: Packages [Contents][Index]
postmodern.asd
Next: The s-sql-system package, Previous: The postmodern-system package, Up: Packages [Contents][Index]
package.lisp (file)
pomo
Next: The s-sql package, Previous: The postmodern package, Up: Packages [Contents][Index]
s-sql.asd
Next: The cl-postgres-system package, Previous: The s-sql-system package, Up: Packages [Contents][Index]
package.lisp (file)
common-lisp
Next: The cl-postgres-error package, Previous: The s-sql package, Up: Packages [Contents][Index]
cl-postgres.asd
Next: The cl-postgres-oid package, Previous: The cl-postgres-system package, Up: Packages [Contents][Index]
package.lisp (file)
Next: The cl-postgres package, Previous: The cl-postgres-error package, Up: Packages [Contents][Index]
package.lisp (file)
oid
common-lisp
Next: The cl-postgres․features package, Previous: The cl-postgres-oid package, Up: Packages [Contents][Index]
package.lisp (file)
common-lisp
Next: The cl-postgres-ieee-floats package, Previous: The cl-postgres package, Up: Packages [Contents][Index]
package.lisp (file)
common-lisp
Next: The cl-postgres-trivial-utf-8 package, Previous: The cl-postgres․features package, Up: Packages [Contents][Index]
package.lisp (file)
common-lisp
Previous: The cl-postgres-ieee-floats package, Up: Packages [Contents][Index]
package.lisp (file)
common-lisp
Definitions are sorted by export status, category, package, and then by lexicographic order.
| • Exported definitions | ||
| • Internal definitions |
Next: Internal definitions, Previous: Definitions, Up: Definitions [Contents][Index]
| • Exported constants | ||
| • Exported special variables | ||
| • Exported macros | ||
| • Exported functions | ||
| • Exported generic functions | ||
| • Exported conditions | ||
| • Exported classes | ||
| • Exported types |
Next: Exported special variables, Previous: Exported definitions, Up: Exported definitions [Contents][Index]
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
oid.lisp (file)
Next: Exported macros, Previous: Exported constants, Up: Exported definitions [Contents][Index]
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.
prepare.lisp (file)
transaction.lisp (file)
Special holding the current database. Most functions and macros operating on a database assume this contains a connected database.
connect.lisp (file)
connect.lisp (file)
s-sql.lisp (file)
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.
s-sql.lisp (file)
table.lisp (file)
transaction.lisp (file)
The maximum amount of connection that will be kept in a single pool, or NIL for no maximum.
connect.lisp (file)
errors.lisp (file)
errors.lisp (file)
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.
interpret.lisp (file)
How many seconds to wait before trying to connect again. Borrowed from pgloader
public.lisp (file)
How many times to we try to connect again. Borrowed from pgloader
public.lisp (file)
sql-string.lisp (file)
sql-string.lisp (file)
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.
interpret.lisp (file)
When set to a filename, this file will be used as client certificate for SSL connections.
protocol.lisp (file)
When set to a filename, this file will be used as client key for SSL connections.
protocol.lisp (file)
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.
s-sql.lisp (file)
Used inside deftable to find the name of the table being defined.
deftable.lisp (file)
Used inside deftable to find the symbol naming the table being defined.
deftable.lisp (file)
Directory where the Unix domain socket for PostgreSQL be found.
public.lisp (file)
Next: Exported functions, Previous: Exported special variables, Up: Exported definitions [Contents][Index]
Create a row reader, as in the row-reader macro, and assign a name to it.
protocol.lisp (file)
table.lisp (file)
Like prepare, but gives the function a name instead of returning it. The name should not be a string but may be quoted.
prepare.lisp (file)
Like defprepared, but with lambda list for statement arguments.
prepare.lisp (file)
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.
deftable.lisp (file)
Like query-dao, but rather than returning a list of results, executes BODY once for each result, with TYPE-VAR bound to the DAO representing that result.
table.lisp (file)
Like select-dao, but rather than returning a list of results, executes BODY once for each result, with TYPE-VAR bound to the DAO representing that result.
table.lisp (file)
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.
query.lisp (file)
Executes body within a with-transaction form if and only if no transaction is already in progress.
transaction.lisp (file)
Executes body within a with-transaction form if and only if no
transaction is already in progress. This adds the ability to specify an isolatin
level other than the current default
transaction.lisp (file)
Execute a query, ignore the results.
query.lisp (file)
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.
ieee-floats.lisp (file)
Wraps a query into a function that will prepare it once for a connection, and then execute it with the given parameters. The query should contain a placeholder ($1, $2, etc) for every parameter.
prepare.lisp (file)
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.
query.lisp (file)
Execute a query and return the result as daos of the given type. The fields returned by the query must match the slots of the dao, both by type and by name.
table.lisp (file)
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.
s-sql.lisp (file)
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.
protocol.lisp (file)
Select daos for the rows in its table for which the given test holds, order them by the given criteria.
table.lisp (file)
Compile form to a sql expression as far as possible.
s-sql.lisp (file)
Helper macro to locally set *read-row-values-as-binary* to t while executing body so that row values will be returned as binary.
interpret.lisp (file)
table.lisp (file)
Locally establish a database connection, and bind *database* to it.
connect.lisp (file)
Executes the body within a with-transaction (if no transaction is already in progress) or a with-savepoint (if one is), binding the transaction or savepoint to NAME (if supplied)
transaction.lisp (file)
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.
transaction.lisp (file)
A macro to set the schema search path of the postgresql
database to include as first entry a specified schema.
calling with strict ’t only the specified schema is set as current
search path. All other schema are then not searched any more.
calling with if-not-exist set to :create the schema is created if
this schema did not exist.
calling with drop-after set to ’t the schema is removed after the
execution of the body form.
example :
(with-schema (:schema-name :strict nil :drop-after nil :if-not-exist :error)
(foo 1)
(foo 2))
namespace.lisp (file)
Helper macro to locally set *read-row-values-as-binary* to nil while executing body so that row values will be returned as t.
interpret.lisp (file)
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.
transaction.lisp (file)
Next: Exported generic functions, Previous: Exported macros, Up: Exported definitions [Contents][Index]
Used inside a deftable form. Define this table using the corresponding DAO class’ slots.
deftable.lisp (file)
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.
deftable.lisp (file)
Used inside a deftable form. Define an index on the defined table.
deftable.lisp (file)
deftable.lisp (file)
Used inside a deftable form. Define a unique index on the defined table.
deftable.lisp (file)
Immediately abort an open transaction.
transaction.lisp (file)
public.lisp (file)
Bloat measurement of unvacuumed dead tuples. Borrowed from: https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/
util.lisp (file)
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/
util.lisp (file)
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.
connect.lisp (file)
Polite way of terminating a query at the database (as opposed to calling close-database). Slower than (terminate-backend pid) and does not always work.
prepare.lisp (file)
Just changes the database assuming you are using a toplevel connection. Recommended only for development work.
util.lisp (file)
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.
util.lisp (file)
Disconnect and remove all connections in the connection pool.
connect.lisp (file)
Gracefully disconnect a database connection.
public.lisp (file)
bulk-copy.lisp (file)
Returns t if any argument is not nil or :null.
util.lisp (file)
Determine if a particular column exists. Table name and column-name can be either strings or symbols.
util.lisp (file)
Immediately commit an open transaction.
transaction.lisp (file)
Create and return a database connection.
connect.lisp (file)
Set *database* to a new connection. Use this if you only need one connection, or if you want a connection for debugging from the REPL.
connect.lisp (file)
Test whether a database connection is still connected.
connect.lisp (file)
Retrieves the meta field of a connection, the primary purpose of which is to store information about the prepared statements that exists for it.
public.lisp (file)
interpret.lisp (file)
Create all defined tables.
deftable.lisp (file)
Create an index. Slightly less sophisticated than the query version because it does not have a where clause capability.
util.lisp (file)
Create all tables whose identifying symbol is interned in the given package.
deftable.lisp (file)
Creating a non existing schema.
If the schema exists an error is raised.
namespace.lisp (file)
Create a sequence. Available additional key parameters are
:temp :if-not-exists :increment :min-value :max-value :start and :cache. See
https://www.postgresql.org/docs/current/static/sql-createsequence.html for details on usage.
util.lisp (file)
Create a defined table.
deftable.lisp (file)
Returns the string name of the current database.
util.lisp (file)
Generate the appropriate CREATE TABLE query for this class.
table.lisp (file)
table.lisp (file)
Given a database-error for an integrity violation, will attempt to extract the constraint name.
errors.lisp (file)
Given a database-error, will extract the critical name from the error message.
errors.lisp (file)
Determine if a particular database exists.
util.lisp (file)
Returns a boolean indicating whether the given connection is currently connected.
public.lisp (file)
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.
util.lisp (file)
Returns the version of the current postgresql database.
util.lisp (file)
bulk-copy.lisp (file)
ieee-floats.lisp (file)
ieee-floats.lisp (file)
interpret.lisp (file)
Return a list of alists of the descriptions a particular constraint given the table-name and the constraint name using the information_schema table.
util.lisp (file)
Generates a list of lists of information on the foreign key constraints
util.lisp (file)
Describe the current views in the specified schema. Takes an optional schema name but defaults to public schema.
util.lisp (file)
Disconnect *database*.
connect.lisp (file)
Drop an index. Available keys are :concurrently, :if-exists, and :cascade.
util.lisp (file)
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.
prepare.lisp (file)
Drops an existing database schema ’schema’
A notice instead of an error is raised with the is-exists parameter.
namespace.lisp (file)
Drop a sequence. Name should be quoted. Available key parameters are :if-exists and :cascade
util.lisp (file)
Enable a syntactic shortcut #Q(...) for (sql (...)). Optionally takes a character to use instead of #\Q.
s-sql.lisp (file)
ieee-floats.lisp (file)
ieee-floats.lisp (file)
Execute a previously prepared query with the given parameters, apply a row-reader to the result.
public.lisp (file)
Execute a query string and apply the given row-reader to the result.
public.lisp (file)
Executes all queries in the provided SQL file. If print is set to t, format will print the count of query and the query.
execute-file.lisp (file)
Returns the specified named prepared statement (if any) that postgresql has for this session.
prepare.lisp (file)
Returns the specified named prepared statement (if any) that postmodern has put in the meta slot in the connection.
prepare.lisp (file)
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.
util.lisp (file)
Convert a string to something that might have been its original lisp name. Does not work if this name contains non-alphanumeric characters other than #-
s-sql.lisp (file)
Get the process id used by postgresql for this connection.
prepare.lisp (file)
Get the process id used by postgresql for this connection, but get it from the postmodern connection parameters.
prepare.lisp (file)
Returns the version of the connected postgresql instance.
public.lisp (file)
Returns the default schema search path for the current session.
namespace.lisp (file)
public.lisp (file)
Check whether a index exists. Takes either a string or a symbol for the index name.
util.lisp (file)
Uses information_schema to list all the constraints in a table. Table-name can be either a string or quoted.
util.lisp (file)
Returns available postgresql extensions per pg_available_extensions
util.lisp (file)
List the available types in this postgresql version.
util.lisp (file)
Returns a list of strings of just the column names in a table. Pulls info from the postmodern table-description function rather than directly.
util.lisp (file)
Return a list of (name type) lists for the fields of a table. Goes directly to the pg-catalog tables.
util.lisp (file)
Returns info from pg_stat_activity on open connections
util.lisp (file)
Returns a list of the functions in the database from the information_schema.
util.lisp (file)
List database users.
util.lisp (file)
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.
util.lisp (file)
List detailed information on the triggers from the information_schema table.
util.lisp (file)
Returns a list of sublists of foreign key info in the form of ’((constraint-name local-table local-table-column foreign-table-name foreign-column-name))
util.lisp (file)
Returns a list of the definitions used to create the current indexes for the table.
util.lisp (file)
List the indexed columns and their attributes in a table. Includes primary key.
util.lisp (file)
Return a list of the indexs in a database. Turn them into keywords if strings-p is not true.
util.lisp (file)
Returns postgresql extensions actually installed in the database per pg_available_extensions
util.lisp (file)
List the prepared statements that postmodern has put in the meta slot in
the connection. It will return a list of alists of form:
((:NAME . "SNY24")
(:STATEMENT . "(SELECT name, salary FROM employee WHERE (city = $1))")
(:PREPARE-TIME . #<TIMESTAMP 25-11-2018T15:36:43,385>)
(:PARAMETER-TYPES . "{text}") (:FROM-SQL).
If the names-only parameter is set to t, it will only return a list of the names of the prepared statements.
prepare.lisp (file)
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.
prepare.lisp (file)
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.
util.lisp (file)
public.lisp (file)
List schemas in the current database, excluding the pg_* system schemas.
namespace.lisp (file)
List all existing user defined schemata.
Note: The query uses the portable information_schema relations instead of pg_tables relations
SELECT schema_name FROM information_schema.schemata where schema_name !~ ’(pg_*)|information_schema’ ORDER BY schema_name ;
namespace.lisp (file)
Return a list of the sequences in a database. Turn them into keywords if strings-p is not true.
util.lisp (file)
List the index names and the related columns in a single table.
util.lisp (file)
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.
util.lisp (file)
Return a list of the tables in a database. Turn them into keywords if strings-p is not true.
util.lisp (file)
Returns a list of tables in a particular schema, defaulting to public.
util.lisp (file)
Lists the tablespaces in the currently connected database.
util.lisp (file)
List distinct trigger names from the information_schema table. Table-name can be either quoted or string.
util.lisp (file)
List constraints on a table.
util.lisp (file)
Return a list of the views in a database. Turn them into keywords if strings-p is not true.
util.lisp (file)
errors.lisp (file)
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.
util.lisp (file)
Returns a list of lists with schema, table name and approximate number of records in the currently connected database.
util.lisp (file)
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).
public.lisp (file)
bulk-copy.lisp (file)
read SQL queries in given string and split them, returns a list
execute-file.lisp (file)
Prepare a query string and store it under the given name.
public.lisp (file)
Returns t if the prepared statement exists in the current postgresql session, otherwise nil.
prepare.lisp (file)
read SQL queries in given file and split them, returns a list
execute-file.lisp (file)
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.
trivial-utf-8.lisp (file)
Immediately release a savepoint, commiting its results.
transaction.lisp (file)
Reconnect a disconnected database connection.
public.lisp (file)
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.
prepare.lisp (file)
Immediately roll back a savepoint, aborting it results.
transaction.lisp (file)
Try to insert the content of a DAO. If this leads to a unique key violation, update it instead.
table.lisp (file)
table.lisp (file)
Predicate for schema existence. More consistent with naming scheme for other functions.
namespace.lisp (file)
Check whether a sequence exists. Takes either a string or a symbol for the sequence name.
util.lisp (file)
Shortcut for getting the next value from a sequence.
util.lisp (file)
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.
namespace.lisp (file)
interpret.lisp (file)
Add an sql reader to a readtable. When the reader is not binary, it is wrapped by a function that will read the string from the socket.
interpret.lisp (file)
Take a tablename of the form database.schema.table or schema.table and return the tablename and the schema name. The name can be a symbol or a string. Returns a list of form ’(table schema database
util.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
Escape string data so it can be used in a query.
s-sql.lisp (file)
s-sql.lisp (file)
Convert a string into an array of unsigned bytes containing its utf-8 representation.
trivial-utf-8.lisp (file)
Return a list of (name type null-allowed) lists for the fields of a table.
util.lisp (file)
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.
util.lisp (file)
Return the size of a postgresql table in k or m. Table-name can be either a string or quoted.
util.lisp (file)
Less polite way of terminating at the database (as opposed to calling close-database). Faster than (cancel-backend pid) and more reliable.
prepare.lisp (file)
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.
s-sql.lisp (file)
Close the prepared query given by name by closing the session connection. Does not remove the query from the meta slot in connection
public.lisp (file)
Returns a list of lists showing schema.table, indexname, index_size and number of scans. The code was borrowed from: https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/
util.lisp (file)
Calculate the amount of bytes needed to encode a string.
trivial-utf-8.lisp (file)
Convert a byte array containing utf-8 encoded characters into the string it encodes.
trivial-utf-8.lisp (file)
Determine the amount of bytes that are part of the character starting with a given byte.
trivial-utf-8.lisp (file)
public.lisp (file)
Check whether a view exists. Takes either a string or a symbol for the view name.
util.lisp (file)
Perform a blocking wait for asynchronous notification. Return the channel string, the payload and notifying pid as multiple values.
public.lisp (file)
Write a string to a byte-stream, encoding it as utf-8.
trivial-utf-8.lisp (file)
Next: Exported conditions, Previous: Exported functions, Up: Exported definitions [Contents][Index]
automatically generated reader method
transaction.lisp (file)
automatically generated writer method
transaction.lisp (file)
automatically generated reader method
transaction.lisp (file)
automatically generated writer method
transaction.lisp (file)
automatically generated reader method
public.lisp (file)
automatically generated writer method
public.lisp (file)
Return a boolean indicating whether the given dao exists in the database.
table.lisp (file)
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.
table.lisp (file)
automatically generated reader method
errors.lisp (file)
errors.lisp (file)
errors.lisp (file)
errors.lisp (file)
errors.lisp (file)
Delete the given dao from the database.
table.lisp (file)
Close a database connection. Returns it to a pool if it is a pooled connection.
connect.lisp (file)
Add the connection to the corresponding pool, or drop it when the pool is full.
automatically generated reader method
protocol.lisp (file)
automatically generated writer method
protocol.lisp (file)
automatically generated reader method
protocol.lisp (file)
automatically generated writer method
protocol.lisp (file)
Get the object corresponding to the given primary key, or return nil if it does not exist.
table.lisp (file)
Insert the given object into the database.
table.lisp (file)
Make the instance of the given class and insert it into the database
table.lisp (file)
protocol.lisp (file)
protocol.lisp (file)
protocol.lisp (file)
Reconnect a database connection.
connect.lisp (file)
Conversion function used to turn a lisp value into a value that PostgreSQL understands when sent through its socket connection. May return a string or a (vector (unsigned-byte 8)).
sql-string.lisp (file)
Get the representation of a Lisp value so that it can be used in a query.
s-sql.lisp (file)
Transform a lisp type into a string containing
something SQL understands. Default is to just use the type symbol’s
name.
s-sql.lisp (file)
s-sql.lisp (file)
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
sql-string.lisp (file)
Update the object’s representation in the database with the values in the given instance.
table.lisp (file)
Update or insert the given dao. If its primary key
is already in the database and all slots are bound, an update will
occur. Otherwise it tries to insert it.
table.lisp (file)
Next: Exported classes, Previous: Exported generic functions, Up: Exported definitions [Contents][Index]
errors.lisp (file)
server-shutdown (condition)
errors.lisp (file)
operator-intervention (condition)
errors.lisp (file)
integrity-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
server-shutdown (condition)
errors.lisp (file)
database-error (condition)
Conditions of this type are signalled when an error
occurs that breaks the connection socket. They offer a :reconnect
restart.
errors.lisp (file)
database-error (condition)
Raised when a query is initiated on a disconnected connection object.
errors.lisp (file)
database-connection-error (condition)
This is the condition type that will be used to
signal virtually all database-related errors (though in some cases
socket errors may be raised when a connection fails on the IP
level).
errors.lisp (file)
error (condition)
Code: the SQLSTATE code for the error (see Appendix A). Not localizable. Always present.
:code
(quote nil)
database-error-code (generic function)
Message: the primary human-readable error message. This should be accurate but terse (typically one line). Always present.
:message
database-error-message (generic function)
(setf database-error-message) (generic function)
Detail: an optional secondary error message carrying more detail about the problem. Might run to multiple lines.
:detail
(quote nil)
database-error-detail (generic function)
Hint: an optional suggestion what to do about the problem.
:hint
(quote nil)
database-error-hint (generic function)
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.
:context
(quote nil)
database-error-context (generic function)
Query that led to the error, if any.
(quote cl-postgres::*current-query*)
database-error-query (generic function)
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.
:position
(quote nil)
database-error-position (generic function)
:cause
(quote nil)
database-error-cause (generic function)
Used to wrap stream-errors and socket-errors, giving them a database-connection-error superclass.
errors.lisp (file)
database-connection-error (condition)
errors.lisp (file)
data-exception (condition)
errors.lisp (file)
transaction-rollback (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
data-exception (condition)
errors.lisp (file)
integrity-violation (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
data-exception (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
object-state-error (condition)
errors.lisp (file)
integrity-violation (condition)
errors.lisp (file)
data-exception (condition)
errors.lisp (file)
object-state-error (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
database-error (condition)
protocol.lisp (file)
simple-warning (condition)
:pid
postgresql-notification-pid (generic function)
(setf postgresql-notification-pid) (generic function)
:channel
postgresql-notification-channel (generic function)
(setf postgresql-notification-channel) (generic function)
:payload
postgresql-notification-payload (generic function)
(setf postgresql-notification-payload) (generic function)
protocol.lisp (file)
simple-warning (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
operator-intervention (condition)
errors.lisp (file)
integrity-violation (condition)
errors.lisp (file)
transaction-rollback (condition)
errors.lisp (file)
s-sql.lisp (file)
simple-error (condition)
errors.lisp (file)
transaction-rollback (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
transaction-rollback (condition)
errors.lisp (file)
database-error (condition)
errors.lisp (file)
syntax-error-or-access-violation (condition)
errors.lisp (file)
integrity-violation (condition)
trivial-utf-8.lisp (file)
simple-error (condition)
:message
:byte
(quote nil)
Next: Exported types, Previous: Exported conditions, Up: Exported definitions [Contents][Index]
Metaclass for database-access-object classes.
table.lisp (file)
standard-class (class)
:keys
direct-keys (generic function)
dao-keys (generic function)
dao-column-map (generic function)
Representation of a database connection. Contains
login information in order to be able to automatically re-establish a
connection when it is somehow closed.
public.lisp (file)
standard-object (class)
pooled-database-connection (class)
:host
connection-host (generic function)
:port
connection-port (generic function)
:db
connection-db (generic function)
:user
connection-user (generic function)
:password
connection-password (generic function)
:ssl
connection-use-ssl (generic function)
:service
connection-service (generic function)
(setf connection-service) (generic function)
:socket
connection-socket (generic function)
(setf connection-socket) (generic function)
t
connection-available (generic function)
(setf connection-available) (generic function)
connection-parameters (generic function)
(setf connection-parameters) (generic function)
connection-timestamp-format (generic function)
(setf connection-timestamp-format) (generic function)
Previous: Exported classes, Up: Exported definitions [Contents][Index]
s-sql.lisp (file)
s-sql.lisp (file)
Type for representing NULL values. Use like (or integer db-null) for declaring a type to be an integer that may be null.
s-sql.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
Previous: Exported definitions, Up: Definitions [Contents][Index]
| • Internal constants | ||
| • Internal special variables | ||
| • Internal macros | ||
| • Internal functions | ||
| • Internal generic functions | ||
| • Internal conditions | ||
| • Internal structures | ||
| • Internal classes | ||
| • Internal types |
Next: Internal special variables, Previous: Internal definitions, Up: Internal definitions [Contents][Index]
interpret.lisp (file)
interpret.lisp (file)
Next: Internal macros, Previous: Internal constants, Up: Internal definitions [Contents][Index]
query.lisp (file)
strings-utf-8.lisp (file)
Bound to the current connection’s parameter table when executing a query.
protocol.lisp (file)
Maps pool specifiers to lists of pooled connections.
connect.lisp (file)
errors.lisp (file)
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.
table.lisp (file)
A copy of the default readtable that client code can fall back on.
interpret.lisp (file)
This is used to communicate the fact that a slot is a column to effective-slot-definition-class.
table.lisp (file)
protocol.lisp (file)
errors.lisp (file)
s-sql.lisp (file)
package.lisp (file)
trivial-utf-8.lisp (file)
A lock to prevent multiple threads from messing with the connection pool at the same time.
connect.lisp (file)
A set of all PostgreSQL’s reserved words, for automatic escaping.
s-sql.lisp (file)
Mapping from keywords identifying result styles to the row-reader that should be used and whether all values or only one value should be returned.
query.lisp (file)
cl-postgres.asd
Unexported ordered list containing the known table definitions.
deftable.lisp (file)
This is used to communicate the format (integer or float) used for timestamps and intervals in the current connection, so that the interpreters for those types know how to parse them.
interpret.lisp (file)
transaction.lisp (file)
cl-postgres.asd
Next: Internal functions, Previous: Internal special variables, Up: Internal definitions [Contents][Index]
query.lisp (file)
Given a character, calls the writer function for every byte in the encoded form of that character.
trivial-utf-8.lisp (file)
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.
interpret.lisp (file)
table.lisp (file)
s-sql.lisp (file)
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.
s-sql.lisp (file)
errors.lisp (file)
Shorthand for defining binary readers.
interpret.lisp (file)
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.
messages.lisp (file)
Create a function to read integers from a binary stream.
communicate.lisp (file)
Create a function to write integers to a binary stream.
communicate.lisp (file)
Helper macro for the functions that check whether an object exists.
util.lisp (file)
Helper macro for the functions that list tables, sequences, and views.
util.lisp (file)
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.
protocol.lisp (file)
Computes a value, then runs a body, then returns, as multiple values, that value and the amount of effected rows, if any (see *effected rows*).
protocol.lisp (file)
query.lisp (file)
query.lisp (file)
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.
s-sql.lisp (file)
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.
public.lisp (file)
Aquire a lock for the pool when evaluating body (if thread support is present).
connect.lisp (file)
errors.lisp (file)
When, inside the body, an error occurs that breaks the connection socket, a condition of type database-connection-error is raised, offering a :reconnect restart.
public.lisp (file)
Macro to wrap a block in a handler that will try to re-sync the connection if something in the block raises a condition. Not hygienic at all, only used right below here.
protocol.lisp (file)
Next: Internal generic functions, Previous: Internal macros, Up: Internal definitions [Contents][Index]
s-sql.lisp (file)
table.lisp (file)
deftable.lisp (file)
Generates the sql string for the portion of altering a column.
s-sql.lisp (file)
query.lisp (file)
Try to initiate a connection. Caller should close the socket if this raises a condition.
protocol.lisp (file)
transaction.lisp (file)
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.
messages.lisp (file)
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.)
table.lisp (file)
Helper for the following two macros.
protocol.lisp (file)
Convert an array of 0-255 numbers into the corresponding string of (lowercase) hex codes.
messages.lisp (file)
transaction.lisp (file)
transaction.lisp (file)
transaction.lisp (file)
transaction.lisp (file)
messages.lisp (file)
query.lisp (file)
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.
public.lisp (file)
Takes a list of two items and returns a single string separated by a space. The items will be converted to sql compatible namestrings.
s-sql.lisp (file)
bulk-copy.lisp (file)
bulk-copy.lisp (file)
messages.lisp (file)
messages.lisp (file)
messages.lisp (file)
execute-file.lisp (file)
bulk-copy.lisp (file)
table.lisp (file)
Enumerate the slots in a class that refer to table rows.
table.lisp (file)
table.lisp (file)
Defines a row-reader for objects of a given class.
table.lisp (file)
query.lisp (file)
Build a list of superclasses of a given class that are DAO classes.
table.lisp (file)
Helper function for macros which look for ’something but that has been converted to (quote something).
s-sql.lisp (file)
messages.lisp (file)
Return the type and whether it may be NULL. TYPE may be a list starting with ’or’ containing two, and only two, potential types to test.
s-sql.lisp (file)
namespace.lisp (file)
Drop a table. Available additional key parameters are :if-exists and :cascade.
util.lisp (file)
strings-utf-8.lisp (file)
strings-utf-8.lisp (file)
strings-utf-8.lisp (file)
strings-utf-8.lisp (file)
Used to make sure a connection object is connected before doing anything with it.
public.lisp (file)
Make sure a statement has been prepared for this connection. If overwrite is set to t (not the default), it will overwrite the existing query of the same name.
prepare.lisp (file)
communicate.lisp (file)
Escape an array of octets in PostgreSQL’s horribly inefficient textual format for binary data.
sql-string.lisp (file)
Try to escape an expression at compile-time, if not possible, delay to runtime. Used to create stored procedures.
s-sql.lisp (file)
Helper function for building a composite table name
s-sql.lisp (file)
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.
s-sql.lisp (file)
Process table constraints that follow the closing parentheses in the table definition.
s-sql.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
Provide interval limit options
s-sql.lisp (file)
Helper for the select operator. Turns the part following :from into the proper SQL syntax for joining tables.
s-sql.lisp (file)
s-sql.lisp (file)
s-sql.lisp (file)
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.)
s-sql.lisp (file)
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.
s-sql.lisp (file)
s-sql.lisp (file)
deftable.lisp (file)
messages.lisp (file)
s-sql.lisp (file)
Formats have to be passed as arrays of 2-byte integers, with 1 indicating binary and 0 indicating plain text.
messages.lisp (file)
table.lisp (file)
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.
prepare.lisp (file)
Read an error message from the socket and raise the corresponding database-error condition.
protocol.lisp (file)
errors.lisp (file)
Get a database connection from the specified pool, returns nil if no connection was available.
connect.lisp (file)
Returns valid IPv4 or IPv6 address for the host.
public.lisp (file)
Read an asynchronous notification message from the socket and signal a condition for it.
protocol.lisp (file)
Returns a type-interpreter containing interpretation rules for this type.
interpret.lisp (file)
Given an array of bytes and the amount of bytes to use, extract the character starting at the given start position.
trivial-utf-8.lisp (file)
Read a warning from the socket and emit it.
protocol.lisp (file)
messages.lisp (file)
Reduce a list of strings to a single string, inserting a separator between them.
s-sql.lisp (file)
public.lisp (file)
bulk-copy.lisp (file)
Check whether a connection object is connected, try to connect it if it isn’t.
public.lisp (file)
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.
protocol.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
This interpreter is used for types that we have no specific interpreter for – it just reads the value as a string. (Values of unknown types are passed in text form.)
interpret.lisp (file)
Decode a 64 bit time-related value based on the timestamp format used. Correct for sign bit when using integer format.
interpret.lisp (file)
If the interpreter’s use-binary field is a function, call it and return the value, otherwise, return T or nil as appropriate.
interpret.lisp (file)
Determine if we went the text or binary reader for this type interpreter and return the appropriate reader.
interpret.lisp (file)
Checks whether a variable is a valid isolation-level keyword.
transaction.lisp (file)
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).
protocol.lisp (file)
Generates an appropriate expander function for a given operator with a given arity.
s-sql.lisp (file)
execute-file.lisp (file)
Apply the hashing that PostgreSQL expects to a password.
messages.lisp (file)
messages.lisp (file)
Provide unique statement names.
prepare.lisp (file)
messages.lisp (file)
Read a SQL query from STREAM, starting at whatever the current position is.
Returns another SQL query each time it’s called, or NIL when EOF is
reached expectedly. Signal end-of-file condition when reaching EOF in the
middle of a query.
See the following docs for some of the parser complexity background:
http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
Parser states are:
- EAT reading the query
- TAG reading a tag that could be an embedded $x$ tag or a closing tag
- EOT End Of Tag
- EQT Eat Quoted Text
- EDQ Eat Double-Quoted Text (identifiers)
- EOQ done reading the query
- ESC read espaced text (with backslash)
execute-file.lisp (file)
execute-file.lisp (file)
execute-file.lisp (file)
execute-file.lisp (file)
execute-file.lisp (file)
execute-file.lisp (file)
messages.lisp (file)
table.lisp (file)
messages.lisp (file)
Helper function which may be useful for certain macros.
Takes what might be a string, a symbol or a quoted-name in the form
’(quote name) and returns the string version of the name.
s-sql.lisp (file)
interpret.lisp (file)
interpret.lisp (file)
interpret.lisp (file)
Read the fields of a null-terminated list of byte + string values and put them in an alist.
protocol.lisp (file)
Read a byte array of the given length from a stream.
communicate.lisp (file)
Read the field descriptions for a query result and put them into an array of field-description objects.
protocol.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
Read lines from given filename and return them in a stream. Recursively apply i include instructions.
execute-file.lisp (file)
interpret.lisp (file)
Read a null-terminated string from a stream. Interprets it as ASCII.
communicate.lisp (file)
Read a null-terminated string from a stream. Takes care of encoding when UTF-8 support is enabled.
communicate.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
communicate.lisp (file)
query.lisp (file)
Used for supporting both plain string queries and S-SQL constructs. Looks at the argument at compile-time and wraps it in (sql ...) if it looks like an S-SQL query.
query.lisp (file)
Join adjacent strings in a list; leave other values intact.
s-sql.lisp (file)
Takes a string and a replacement char and replaces any character which is not alphanumeric or an asterisk with a specified character - by default an underscore and returns the modified string.
util.lisp (file)
s-sql.lisp (file)
Send a close command to the server, giving it a name.
protocol.lisp (file)
bulk-copy.lisp (file)
bulk-copy.lisp (file)
Execute a previously parsed query, and apply the given row-reader to the result.
protocol.lisp (file)
Send a parse command to the server, giving it a name.
protocol.lisp (file)
Send a query to the server, and apply the given row-reader to the results.
protocol.lisp (file)
interpret.lisp (file)
interpret.lisp (file)
interpret.lisp (file)
messages.lisp (file)
messages.lisp (file)
messages.lisp (file)
messages.lisp (file)
Skip a given number of bytes in a binary stream.
communicate.lisp (file)
Skip a null-terminated string.
communicate.lisp (file)
Helper function for split-on-keywords. Extracts the values associated with the keywords from an argument list, and checks for errors.
s-sql.lisp (file)
Compile-time expansion of forms into lists of stuff that evaluate to strings (which will form a SQL query when concatenated).
s-sql.lisp (file)
Expand a list of elements, adding a separator between them.
s-sql.lisp (file)
Takes a list of elements (symbols or strings) and returns a separated list of strings. If the element is a cons, then
s-sql.lisp (file)
messages.lisp (file)
messages.lisp (file)
Concatenate a list of strings into a single one.
s-sql.lisp (file)
query.lisp (file)