1#+TITLE: Postmodern 2#+OPTIONS: num:nil 3#+HTML_HEAD: <link rel="stylesheet" type="text/css" href="style.css" /> 4#+HTML_HEAD: <style>pre.src{background:#343131;color:white;} </style> 5#+OPTIONS: ^:nil 6 7Version 1.32 8 9Postmodern is a Common Lisp library for interacting with [[https://postgresql.org][PostgreSQL databases]]. 10Features are: 11 12- Efficient communication with the database server without need for foreign libraries. 13- Support for UTF-8 on Unicode-aware Lisp implementations 14- A syntax for mixing SQL and Lisp code 15- Convenient support for prepared statements and stored procedures 16- A metaclass for simple database-access objects 17 18The biggest differences between this library and [[http://quickdocs.org/clsql/][clsql]] or [[https://github.com/fukamachi/cl-dbi][cl-dbi]] 19are that Postmodern has no intention of being portable across different SQL 20implementations (it embraces non-standard PostgreSQL features), and approaches 21extensions like lispy SQL and database access objects in a quite different way. 22This library was written because the CLSQL approach did not really work for 23me. Your mileage may vary. 24 25 26* Dependencies 27 :PROPERTIES: 28 :ID: 216c43d0-57ff-4ae3-a302-6d04a3d79665 29 :CUSTOM_ID: 6887e7c3-c818-469a-b5f1-10a4b578b90b 30 :END: 31The library depends on [[http://quickdocs.org/usocket/][usocket]] (except on [[http://sbcl.org/][SBCL]] and [[https://franz.com/products/allegrocl/][ACL]], where the built-in socket library is used), [[https://github.com/pmai/md5.git][md5]], [[https://github.com/pcostanza/closer-mop.git][closer-mop]], [[https://github.com/sionescu/bordeaux-threads.git][bordeaux-threads]] if you want 32thread-safe connection pools, and [[https://github.com/cl-plus-ssl/cl-plus-ssl.git][CL+SSL]] when SSL connections are needed. 33 34As of version 1.3 it also depends on [[https://github.com/sharplispers/ironclad][ironclad]], [[https://github.com/massung/base64][base64]] and [[https://github.com/sabracrolleton/uax-15][uax-15]] because of the need to support scram-sha-256 authentication. 35 36Postmodern itself is split into four different packages, some of which can be used independently: 37 38- [[file:simple-date.html][Simple-date]] is a very basic implementation of date and time objects, used to support storing and retrieving time-related 39SQL types. It is not loaded by default and you can use [[https://github.com/dlowe-net/local-time][local-time]] instead. 40 41- [[file:cl-postgres.html][CL-postgres]] is the low-level library used for interfacing with a PostgreSQL server over a socket. 42 43- [[file:s-sql.html][S-SQL]] is used to compile s-expressions to strings of SQL code, escaping any Lisp values inside, and doing as much as 44possible of the work at compile time. Finally, 45 46- [[file:postmodern.html][Postmodern]] itself is a wrapper around these packages and provides higher level functions, a very simple data access object that can be mapped directly to database tables and some convient utilities. It then tries to put all these things together into a convenient programming interface.itself is the library that tries to put all these things together into a convenient programming interface. 47 48* License 49 :PROPERTIES: 50 :ID: 8ba6488f-4b3c-47f7-8a50-844363c5f484 51 :CUSTOM_ID: 554e0dee-93b3-47b1-b808-3bd6c366b784 52 :END: 53Postmodern is released under a zlib-style license. Which approximately 54means you can use the code in whatever way you like, except for passing 55it off as your own or releasing a modified version without indication 56that it is not the original. 57 58The functions execute-file.lisp were ported from [[https://github.com/dimitri/pgloader][pgloader]] with grateful thanks to 59Dimitri Fontaine and are released under a BSD-3 license. 60 61* Download and installation 62 :PROPERTIES: 63 :ID: d4cca0ee-ff7f-4530-9be7-e9b3de62bdb4 64 :CUSTOM_ID: 6f05b344-12c2-42b0-b231-3aaced30afb8 65 :END: 66We suggest using [[https://quicklisp.org][quicklisp.org]] for installation. 67 68A git repository with the most recent changes can be viewed or checked out at: 69 70> git clone [[https://github.com/marijnh/Postmodern]] 71 72* Quickstart 73 :PROPERTIES: 74 :ID: f55510fb-3715-4cdd-9e37-4ab0e968e72d 75 :CUSTOM_ID: b5bb7222-8134-4dcb-83b7-f764b7f2bb33 76 :END: 77This quickstart is intended to give you a feel of the way coding with 78Postmodern works. Further details about the workings of the library 79can be found in the reference manuals linked below. 80 81Assuming you have already installed quicklisp, load postmodern. 82#+BEGIN_SRC lisp 83(ql:quickload :postmodern) 84(use-package :postmodern) 85#+END_SRC 86 87If you have a PostgreSQL server running on localhost, with a database called 'testdb' 88on it, which is accessible for user 'foucault' with password 'surveiller', there are 89two basic ways to connect to a database. If your role/application/database(s) looks 90like a 1:1 relationship, you can connect like this: 91#+BEGIN_SRC lisp 92(connect-toplevel "testdb" "foucault" "surveiller" "localhost") 93#+END_SRC 94 95This will establish a connection to be used by all code, except for that wrapped 96in a with-connection form, which takes the same arguments but only establishes 97the connection within that lexical scope. 98 99Connect-toplevel will maintain a single connection for the life of the session. 100 101If the Postgresql server is running on a port other than 5432, 102you would also pass the appropriate keyword port parameter. E.g.: 103 104#+BEGIN_SRC lisp 105(connect-toplevel "testdb" "foucault" "surveiller" "localhost" :port 5434) 106#+END_SRC 107 108Ssl connections would similarly use the keyword parameter :use-ssl and 109pass :no, :try, :require, :yes, or :full 110- :try means if the server supports it 111- :require means use provided ssl certificate with no verification 112- :yes means verify that the server cert is issued by a trusted CA, but does not verify the server hostname 113- :full means expect a CA-signed cert for the supplied hostname and verify the server hostname 114 115If you have multiple roles connecting to one or more databases, i.e. 1:many or 116many:1, (in other words, changing connections) then with-connection form which establishes a connection with a lexical scope is more appropriate. 117#+BEGIN_SRC lisp 118(with-connection '("testdb" "foucault" "surveiller" "localhost") 119 ...) 120#+END_SRC 121For example, if you are creating a database, you need to have established a connection 122to a currently existing database (typically "postgres"). Assuming the foucault role 123is a superuser and you want to stay in a development connection with your new database 124afterwards, you would first use with-connection to connect to postgres, create the 125database and then switch to connect-toplevel for development ease. 126#+BEGIN_SRC lisp 127(with-connection '("postgres" "foucault" "surveiller" "localhost") 128 (create-database 'testdb :limit-public-access t 129 :comment "This database is for testing silly theories")) 130 131(connect-toplevel "testdb" "foucault" "surveiller" "localhost") 132#+END_SRC 133Note: (create-database) functionality is new to postmodern v. 1.32. Setting the 134:limit-public-access parameter to t will block connections to that database from 135anyone who you have not explicitly given permission (except other superusers). 136 137A word about Postgresql connections. Postgresql connections are not lightweight 138threads. They actually consume about 10 MB of memory per connection and Postgresql can be tuned 139to limit the number of connections allowed at any one time. In addition, any connections 140which require security (ssl or scram authentication) will take additiona time and create 141more overhead. 142 143If you have an application like a web app which will make many connections, you also 144generally do not want to create and drop connections for every query. The usual solution 145is to use connection pools so that the application is grabbing an already existing connection 146and returning it to the pool when finished, saving connection time and memory. 147 148To use postmodern's simple connection pooler, the with-connection call would look like: 149#+BEGIN_SRC lisp 150(with-connection '("testdb" "foucault" "surveiller" "localhost" :pooled-p t) 151 ...) 152#+END_SRC 153 154The maximum number of connections in the pool is set in the special variable 155*max-pool-size*, which defaults to nil (no maximum). 156 157Now for a basic sanity test which does not need a database connection at all: 158 159#+BEGIN_SRC lisp 160(query "select 22, 'Folie et déraison', 4.5") 161;; => ((22 "Folie et déraison" 9/2)) 162#+END_SRC 163That should work. query is the basic way to send queries to the database. 164The same query can be expressed like this: 165#+BEGIN_SRC lisp 166(query (:select 22 "Folie et déraison" 4.5)) 167;; => ((22 "Folie et déraison" 9/2)) 168#+END_SRC 169 170In many contexts, query strings and lists starting with keywords can be used 171interchangeably. The lists will be compiled to SQL. The S-SQL manual describes 172the syntax used by these expressions. Lisp values occurring in them are 173automatically escaped. In the above query, only constant values are used, but 174it is possible to transparently use run-time values as well: 175#+BEGIN_SRC lisp 176(defun database-powered-addition (a b) 177 (query (:select (:+ a b)) :single)) 178 179(database-powered-addition 1030 204) 180;; => 1234 181#+END_SRC 182 183That last argument, :single, indicates that we want the result not as a list 184of lists (for the result rows), but as a single value, since we know that we 185are only selecting one value. Some other options are :rows, :row, :column, :alists, 186:plists, :array-hash, :json-strs, :json-str, :json-array-str, :dao and :none. 187Their precise effect is documented in the [[file:postmodern.html][Postmodern reference manual under [[file:postmodern.html#querying][Queries 188 189You do not have to pull in the whole result of a query at once, you can 190also iterate over it with the doquery macro: 191#+BEGIN_SRC lisp 192(doquery (:select 'x 'y :from 'some-imaginary-table) (x y) 193 (format t "On this row, x = ~A and y = ~A.~%" x y)) 194#+END_SRC 195 196You can work directly with the database or you can use a simple 197database-access-class (aka dao) which would cover all the fields in a row. 198This is what a database-access class looks like: 199#+BEGIN_SRC lisp 200(defclass country () 201 ((name :col-type string :initarg :name 202 :reader country-name) 203 (inhabitants :col-type integer :initarg :inhabitants 204 :accessor country-inhabitants) 205 (sovereign :col-type (or db-null string) :initarg :sovereign 206 :accessor country-sovereign)) 207 (:metaclass dao-class) 208 (:keys name)) 209#+END_SRC 210The above defines a class that can be used to handle records in a table named 211'country' with three columns: name, inhabitants, and sovereign. The :keys 212parameter specifies which column(s) are used for the primary key. Once you have 213created the class, you can return an instance of the country class by calling 214 215#+BEGIN_SRC lisp 216(get-dao 'country "Croatia") 217#+END_SRC 218 219You can also define classes that use multiple columns in the primary key: 220 221#+BEGIN_SRC lisp 222(defclass points () 223 ((x :col-type integer :initarg :x 224 :reader point-x) 225 (y :col-type integer :initarg :y 226 :reader point-y) 227 (value :col-type integer :initarg :value 228 :accessor value)) 229 (:metaclass dao-class) 230 (:keys x y)) 231#+END_SRC 232 233In this case, retrieving a points record would look like the following where 23412 and 34 would be the values you are looking to find in the x column and y 235column respectively.: 236 237#+BEGIN_SRC lisp 238(get-dao 'points 12 34) 239#+END_SRC 240 241Consider a slightly more complicated version of country: 242#+BEGIN_SRC lisp 243(defclass country-c () 244 ((id :col-type integer :col-identity t :accessor id) 245 (name :col-type string :col-unique t :check (:<> 'name "") 246 :initarg :name :reader country-name) 247 (inhabitants :col-type integer :initarg :inhabitants 248 :accessor country-inhabitants) 249 (sovereign :col-type (or db-null string) :initarg :sovereign 250 :accessor country-sovereign) 251 (region-id :col-type integer :col-references ((regions id)) 252 :initarg :region-id :accessor region-id)) 253 (:metaclass dao-class) 254 (:table-name countries)) 255#+END_SRC 256 257In this example we have an id column which is specified to be an identity column. 258Postgresql will automatically generate a sequence of of integers and this will 259be the primary key. 260 261We have a name column which is specified as unique and is not null and the 262check will ensure that the database refuses to accept an empty string as the name. 263 264We have a region-id column which references the id column in the regions table. 265This is a foreign key constraint and Postgresql will not accept inserting a country 266into the database unless there is an existing region with an id that matches this 267number. Postgresql will also not allow deleting a region if there are countries 268that reference that region's id. If we wanted Postgresql to delete countries when 269regions are deleted, that column would be specified as: 270#+BEGIN_SRC lisp 271(region-id :col-type integer :col-references ((regions id) :cascade) 272 :initarg :region-id :accessor region-id) 273#+END_SRC 274 275Now you can see why the double parens. 276 277We also specify that the table name is not "country" but "countries". (Some style guides 278recommend that table names be plural and references to rows be singular.) 279 280** Table Creation 281 :PROPERTIES: 282 :ID: 6ac2dcab-bd3b-48de-9ea0-fec010d76879 283 :CUSTOM_ID: eccad49e-8df8-4451-89ff-4987b103c9dd 284 :END: 285*** With SQL or S-SQL 286 :PROPERTIES: 287 :CUSTOM_ID: 7629810d-8ccb-4236-b540-6aff596a042f 288 :END: 289You can create tables directly without the need to define a class, and in more 290complicated cases, you may need to use the s-sql :create-table operator or plain 291vanilla sql. Staying with examples that will match our slightly more complicated 292dao-class above (but ignoring the fact that the references parameter would 293actually require us to create the regions table first) and using s-sql rather 294than plain vanilla sql would be the following: 295#+BEGIN_SRC lisp 296(query (:create-table 'countries 297 ((id :type integer :primary-key t :identity-always t) 298 (name :type string :unique t :check (:<> 'name "")) 299 (inhabitants :type integer) 300 (sovereign :type (or db-null string)) 301 (region-id :type integer :references ((regions id)))))) 302#+END_SRC 303 304Restated using vanilla sql: 305#+BEGIN_SRC sql 306(query "CREATE TABLE countries ( 307 id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 308 name TEXT NOT NULL UNIQUE CHECK (NAME <> E''), 309 inhabitants INTEGER NOT NULL, 310 sovereign TEXT, 311 region_id INTEGER NOT NULL REFERENCES regions(id) 312 MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT)") 313#+END_SRC 314Let's look at a slightly different example: 315#+BEGIN_SRC lisp 316(query (:create-table so-items 317 ((item-id :type integer) 318 (so-id :type (or integer db-null) :references ((so-headers id))) 319 (product-id :type (or integer db-null)) 320 (qty :type (or integer db-null)) 321 (net-price :type (or numeric db-null))) 322 (:primary-key item-id so-id))) 323#+END_SRC 324 325Restated using plain sql: 326#+BEGIN_SRC sql 327(query "CREATE TABLE so_items ( 328 item_id INTEGER NOT NULL, 329 so_id INTEGER REFERENCES so_headers(id) 330 MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT, 331 product_id INTEGER, 332 qty INTEGER, 333 net_price NUMERIC, 334 PRIMARY KEY (item_id, so_id) 335 );") 336#+END_SRC 337In the above case, the new table's name will be so_items because sql does not 338allow hyphens and plain vanilla sql will require that. Postmodern will 339generally allow you to use the quoted symbol 'so-items. This is also true for 340all the column names. The column item-id is an integer and cannot be null. The 341column so-id is also an integer, but is allowed to be null and is a foreign key 342to the id field in the so-headers table so-headers. The primary key is actually 343a composite of item-id and so-id. (If we wanted the primary key to be just 344item-id, we could have specified that in the form defining item-id.) 345 346For more detail and examples on building tables using the s-sql approach, 347see [[file:create-tables.html][create-tables.html]] 348 349*** With a Previously Created DAO 350 :PROPERTIES: 351 :CUSTOM_ID: 5129ed89-2ff3-45b4-ae70-41c2c286eacc 352 :END: 353You can also use a previously defined dao to create a table as well. The 354dao-table-definition function examines a dao class and generates the plain vanilla 355sql for creating a table. That can be passed on to the execute function to create 356a table. For example the simple country dao would generate: 357#+BEGIN_SRC lisp 358(dao-table-definition 'country) 359 360"CREATE TABLE country 361 (name TEXT NOT NULL, 362 inhabitants INTEGER NOT NULL, 363 sovereign TEXT DEFAULT NULL, 364 PRIMARY KEY (name))" 365 366(execute (dao-table-definition 'country)) 367#+END_SRC 368(Execute works like query, but does not expect any results back.) 369 370The slightly more complicated country-c version would generate: 371#+BEGIN_SRC lisp 372(dao-table-definition 'country-c) 373 374;; => "CREATE TABLE countries ( 375;; id INTEGER NOT NULL PRIMARY KEY generated always as identity, 376;; name TEXT NOT NULL UNIQUE, 377;; inhabitants INTEGER NOT NULL, 378;; sovereign TEXT DEFAULT NULL, 379;; region_id INTEGER NOT NULL REFERENCES regions(id) 380;; MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT) 381 382(execute (dao-table-definition 'country-c)) 383#+END_SRC 384For the rest of the discussion on this page, we will use the simpler version 385and save the more complicated version for the [[file:postmodern.html][postmodern]] manuals. 386 387** Inserting Data 388 :PROPERTIES: 389 :ID: 980103dd-9593-4047-9954-92e80f3785a9 390 :CUSTOM_ID: 0b22f6d2-20e4-49cd-a311-083aade58cbf 391 :END: 392Similarly to table creation, you can insert data using the s-sql wrapper, plain 393vanilla sql or daos. 394 395The s-sql approach would be: 396 397#+BEGIN_SRC lisp 398(query (:insert-into 'country :set 'name "The Netherlands" 399 'inhabitants 16800000 400 'sovereign "Willem-Alexander")) 401 402(query (:insert-into 'country :set 'name "Croatia" 403 'inhabitants 4400000)) 404#+END_SRC 405 406You could also insert multiple rows at a time but that requires the same columns for each row: 407 408#+BEGIN_SRC lisp 409(query (:insert-rows-into 'country :columns 'name 'inhabitants 'sovereign 410 :values '(("The Netherlands" 16800000 "Willem-Alexander") 411 ("Croatia" 4400000 :null)))) 412#+END_SRC 413 414The sql approach would be: 415#+BEGIN_SRC lisp 416(query "insert into country (name, inhabitants, sovereign) 417 values ('The Netherlands', 16800000, 'Willem-Alexander')") 418 419(query "insert into country (name, inhabitants) 420 values ('Croatia', 4400000)") 421#+END_SRC 422 423The multiple row sql approach would be: 424#+BEGIN_SRC lisp 425(query "insert into country (name, inhabitants, sovereign) 426 values 427 ('The Netherlands', 16800000, 'Willem-Alexander'), 428 ('Croatia', 4400000, NULL)") 429#+END_SRC 430 431Using dao classes would look like: 432Let us go back to our approach using a dao class and add a few countries: 433#+BEGIN_SRC lisp 434(insert-dao (make-instance 'country :name "The Netherlands" 435 :inhabitants 16800000 436 :sovereign "Willem-Alexander")) 437(insert-dao (make-instance 'country :name "Croatia" 438 :inhabitants 4400000)) 439#+END_SRC 440Postmodern does not yet have an insert-daos (plural) function. 441 442Staying with the dao class approach, to update Croatia's population, we could do this: 443#+BEGIN_SRC lisp 444(let ((croatia (get-dao 'country "Croatia"))) 445 (setf (country-inhabitants croatia) 4500000) 446 (update-dao croatia)) 447(query (:select '* :from 'country)) 448 449;; => (("The Netherlands" 16800000 "Willem-Alexander") 450;; ("Croatia" 4500000 :NULL)) 451#+END_SRC 452Please see the documentation for s-sql for more examples of using s-sql rather than daos. 453 454Next, to demonstrate a bit more of the S-SQL syntax, here is the query the 455utility function list-tables uses to get a list of the tables in a database: 456#+BEGIN_SRC lisp 457(sql (:select 'relname :from 'pg-catalog.pg-class 458 :inner-join 'pg-catalog.pg-namespace :on (:= 'relnamespace 459 'pg-namespace.oid) 460 :where (:and (:= 'relkind "r") 461 (:not-in 'nspname (:set "pg_catalog" "pg_toast")) 462 (:pg-catalog.pg-table-is-visible 'pg-class.oid)))) 463 464;; => "(SELECT relname FROM pg_catalog.pg_class 465;; INNER JOIN pg_catalog.pg_namespace ON (relnamespace = pg_namespace.oid) 466;; WHERE ((relkind = 'r') and (nspname NOT IN ('pg_catalog', 'pg_toast')) 467;; and pg_catalog.pg_table_is_visible(pg_class.oid)))" 468#+END_SRC 469 470sql is a macro that will simply compile a query, it can be useful for seeing 471how your queries are expanded or if you want to do something unexpected with 472them. 473 474As you can see, lists starting with keywords are used to express SQL commands 475and operators (lists starting with something else will be evaluated and then 476inserted into the query). Quoted symbols name columns or tables (keywords can 477also be used but might introduce ambiguities). The syntax supports subqueries, 478multiple joins, stored procedures, etc. See the [[file:s-sql.html][S-SQL reference manual]] for a 479complete treatment. 480 481Finally, here is an example of the use of prepared statements: 482#+BEGIN_SRC lisp 483(defprepared sovereign-of 484 (:select 'sovereign :from 'country :where (:= 'name '$1)) 485 :single!) 486(sovereign-of "The Netherlands") 487;; => "Willem-Alexander" 488#+END_SRC 489 490The defprepared macro creates a function that takes the same amount of 491arguments as there are $X placeholders in the given query. The query will 492only be parsed and planned once (per database connection), which can be 493faster, especially for complex queries. 494#+BEGIN_SRC lisp 495(disconnect-toplevel) 496#+END_SRC 497 498* Authentication 499 :PROPERTIES: 500 :ID: 5ad46584-6887-4866-9c40-633768c0d39a 501 :CUSTOM_ID: b15533d8-efa3-43a9-b632-a3256cea261f 502 :END: 503Postmodern can use either md5 or scram-sha-256 authentication. Scram-sha-256 504authentication is obviously more secure, but slower than md5, so take that into 505account if you are planning on opening and closing many connections without 506using a connection pooling setup.. 507 508Other authentication methods have not been tested. Please let us know if there 509is a authentication method that you believe should be considered. 510 511* Reference 512 :PROPERTIES: 513 :ID: 27b39236-15ee-42c3-958a-3c9c903c4567 514 :CUSTOM_ID: 8993c7bd-4ba2-4080-8c5a-ff90de45eca5 515 :END: 516The reference manuals for the different components of Postmodern are kept 517in separate files. For using the library in the most straightforward way, 518you only really need to read the Postmodern reference and glance over the 519S-SQL reference. The simple-date reference explains the time-related data 520types included in Postmodern, and the CL-postgres reference might be useful 521if you just want a low-level library for talking to a PostgreSQL server. 522 523- [[file:postmodern.html][Postmodern]] 524 525- [[file:s-sql.html][S-SQL]] 526 527- [[file:array-notes.html][Array-Notes]] 528 529- [[file:simple-date.html][Simple-date]] 530 531- [[file:cl-postgres.html][CL-postgres]] 532 533* Data Types 534 :PROPERTIES: 535 :ID: d089d05b-4485-4fb5-9097-5a66492bc470 536 :CUSTOM_ID: e2475974-6131-40ef-9ca3-54bf111a5dd0 537 :END: 538** Data Types 539 :PROPERTIES: 540 :ID: deae4656-1b87-4518-9718-3b3e8c35f6c5 541 :CUSTOM_ID: b850ea6c-b61e-4601-8423-65a8a626cb58 542 :END: 543For a short comparison of lisp and Postgresql data types (date and time datatypes are described in the next section) 544 545| SQL type | Description | 546|------------------+-------------------------------------------------------------------| 547| smallint | -32,768 to +32,768 2-byte storage | 548| integer | -2147483648 to +2147483647 integer, 4-byte storage | 549| bigint | -9223372036854775808 to 9223372036854775807 8-byte storage | 550| numeric(X, Y) | User specified, see notes below | 551| real | float, 6 decimal digit precision 4-byte storage | 552| double-precision | floating, 15 decimal digit precision 8-byte storage | 553| text | variable length string, no limit specified | 554| char(X) | char(length), blank-padded string, fixed storage length | 555| varchar(X) | varchar(length), non-blank-padded string, variable storage length | 556| boolean | boolean, 'true'/'false', 1 byte | 557| bytea | binary string allowing non-printable octets | 558| date | date range: 4713 BC to 5874897 AD | 559| interval | See [[file:interval-notes.html][interval]] | 560| array | See discussion at [[file:array-notes.html][array-notes.html]] | 561 562Numeric and decimal are variable storage size numbers with user specified precision. 563Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point. 564The syntax is numeric(precision, scale). Numeric columns with a specified scale will coerce input 565values to that scale. For more detail, see https://www.postgresql.org/docs/current/datatype-numeric.html 566 567| PG Type | Sample Postmodern Return Value | Lisp Type (per sbcl) | 568|------------------+-----------------------------------------------------------------------------+--------------------------------------| 569| boolean | T | BOOLEAN | 570| boolean | NIL *Note: within Postgresql this will show 'f' | BOOLEAN | 571| int2 | 273 | (INTEGER 0 4611686018427387903) | 572| int4 | 2 | (INTEGER 0 4611686018427387903) | 573| char | A | (VECTOR CHARACTER 64) | 574| varchar | id&wl;19 | (VECTOR CHARACTER 64) | 575| numeric | 78239/100 | RATIO | 576| json | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}} | (VECTOR CHARACTER 64) | 577| jsonb | {"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"]} | (VECTOR CHARACTER 128) | 578| float | 782.31 | SINGLE-FLOAT | 579| point | (0.0d0 0.0d0) | CONS | 580| lseg | ((-1.0d0 0.0d0) (2.0d0 4.0d0)) | CONS | 581| path | ((1,0),(2,4)) | (VECTOR CHARACTER 64) | 582| box | ((1.0d0 1.0d0) (0.0d0 0.0d0)) | CONS | 583| polygon | ((21,0),(2,4)) | (VECTOR CHARACTER 64) | 584| line | {2,-1,0} | (VECTOR CHARACTER 64) | 585| double_precision | 2.38921379231d8 | DOUBLE-FLOAT | 586| double_float | 2.3892137923231d8 | DOUBLE-FLOAT | 587| circle | <(0,0),2> | (VECTOR CHARACTER 64) | 588| cidr | 100.24.10.0/24 | (VECTOR CHARACTER 64) | 589| inet | 100.24.10.0/24 | (VECTOR CHARACTER 64) | 590| interval | #<INTERVAL P1Y3H20m> | INTERVAL | 591| bit | #*1 | (SIMPLE-BIT-VECTOR 1) | 592| int4range | [11,24) | (VECTOR CHARACTER 64) | 593| uuid | 40e6215d-b5c6-4896-987c-f30f3678f608 | (VECTOR CHARACTER 64) | 594| text_array | #(text one text two text three) | (SIMPLE-VECTOR 3) | 595| integer_array | #(3 5 7 8) | (SIMPLE-VECTOR 4) | 596| bytea | #(222 173 190 239) | (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (4)) | 597| text | Lorem ipsum dolor sit amet, consectetur adipiscing elit | (VECTOR CHARACTER 64) | 598| enum_mood | happy *Note: enum_mood was defined as 'sad','ok' or 'happy' | (VECTOR CHARACTER 64) | 599 600* Caveats and to-dos 601 :PROPERTIES: 602 :ID: 157ea05f-4c49-4e49-8cf9-a3df4bf16b09 603 :CUSTOM_ID: fc095960-ba9d-4a98-ac89-0db7a56777f1 604 :END: 605** Timezones and Simple-Date and Local-Time 606 :PROPERTIES: 607 :ID: 8ff631c8-994f-4658-bbc4-779afc80bdf2 608 :CUSTOM_ID: e5c68251-0ca6-4f96-8d36-175cec626eeb 609 :END: 610It is important to understand how postgresql (not postmodern) handles 611timestamps and timestamps with time zones. Postgresql keeps everything 612in UTC, it does not store a timezone even in a timezone aware column. 613If you use a timestamp with timezone column, postgresql will calculate 614the UTC time and will normalize the timestamp data to UTC. When you 615later select the record, postgresql will look at the timezone for the 616postgresql session, retrieve the data and then provide the data 617recalculated from UTC to the timezone for that postgresql session. 618There is a good writeup of timezones at 619[[http://blog.untrod.com/2016/08/actually-understanding-timezones-in-postgresql.html]] 620and [[http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/][http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/]]. 621 622Without simple-date or local-time properly loaded and without use of the 623Postgresql to_char function, sample date and time data from postgresql will 624look like: 625 626| PG Type | Sample Return Value | Lisp Type | 627|-----------------+------------------------------------------------------------+---------------------------------| 628| date | 3798576000 | (INTEGER 0 4611686018427387903) | 629| time_wo_tz | ((HOURS 9) (MINUTES 47) (SECONDS 9) (MICROSECONDS 926531)) | CONS | 630| time_w_tz | 09:47:16.510459-04 | (VECTOR CHARACTER 64) | 631| timestamp_wo_tz | 3798611253 | (INTEGER 0 4611686018427387903) | 632| timestamp_w_tz | 3798625647 | (INTEGER 0 4611686018427387903) | 633 634YOU DO NOT NEED TO ADD ANY OTHER LIBRARIES IF ALL YOU WANT TO DO IS GET ISO 8601 or 635[[https://tools.ietf.org/html/rfc3339][RFC 3339]] PROPERLY FORMATTED TIME AND DATE STRINGS WITH THE [[https://www.postgresql.org/docs/current/functions-formatting.html][TO_CHAR]] POSTGRESQL 636FUNCTION. 637 638Assume a data table with columns "col_timestamp_without_time_zone", "col_timestamp_with_time_zone", "col_timestamptz", "col_timestamp", "col_time" 639and "col_date". 640 641First, just basic sql. In this example we ask for the timestamp_with_time_zone field 642three times to show differences in the result dealing with time zones. First we do not 643add a timezone parameter to the pattern, the second time we ask for the time zone 644using TZ, the third time we ask for the offset from UTC and get back -04. We would get 645the same result using those additional parameters with the col_timestamptz field. 646 647#+BEGIN_SRC sql 648(query "(SELECT to_char(col_timestamp_with_time_zone, E'YYYY-MM-DD HH24:MI:SS'), 649 to_char(col_timestamp_with_time_zone, E'YYYY-MM-DD HH24:MI:SS TZ'), 650 to_char(col_timestamp_with_time_zone, E'YYYY-MM-DD HH24:MI:SS OF'), 651 to_char(col_timestamptz, E'YYYY-MM-DD HH24:MI:SS'), 652 to_char(col_timestamp, E'YYYY-MM-DD HH24:MI:SS'), 653 to_char(col_time, E'HH24:MI:SS'), 654 to_char(col_date, E'YYYY-MM-DD') 655 FROM short_data_type_tests 656 WHERE (id = 1))") 657(("2020-10-30 19:30:54" "2020-10-30 19:30:54 EDT" "2020-10-30 19:30:54 -04" 658"2020-10-30 19:30:54" "2020-10-30 19:30:54" "19:30:54" "2020-10-30")) 659#+END_SRC 660 661Now the s-sql version: 662#+BEGIN_SRC lisp 663(query (:select (:to-char 'col_timestamp_with_time_zone "YYYY-MM-DD HH24:MI:SS TZ") 664 (:to-char 'col_timestamp_with_time_zone "YYYY-MM-DD HH24:MI:SS OF") 665 (:to-char 'col_timestamptz "YYYY-MM-DD HH24:MI:SS") 666 (:to-char 'col_timestamp "YYYY-MM-DD HH24:MI:SS") 667 (:to-char 'col_time "HH24:MI:SS") 668 (:to-char 'col_date "YYYY-MM-DD") 669 :from 'short-data-type-tests 670 :where (:= 'id 1))) 671 672(("2020-10-30 19:30:54 EDT" "2020-10-30 19:30:54 -04" "2020-10-30 19:30:54" 673 "2020-10-30 19:30:54" "19:30:54" "2020-10-30")) 674#+END_SRC 675*** Simple-Date Library Use 676The Simple-date add-on library (not enabled by default) 677provides types (CLOS classes) for dates, timestamps, and intervals 678similar to the ones SQL databases use, in order to be able to store and read 679these to and from a database in a straighforward way. A few obvious operations 680are defined on these types. 681 682To use simple-date with cl-postgres or postmodern, 683load simple-date-cl-postgres-glue and register suitable SQL 684readers and writers for the associated database types. 685 686#+BEGIN_SRC lisp 687(ql:quickload :simple-date/postgres-glue) 688 689(setf cl-postgres:*sql-readtable* 690 (cl-postgres:copy-sql-readtable 691 simple-date-cl-postgres-glue:*simple-date-sql-readtable*)) 692#+END_SRC 693 694With simple date loaded, the same data will look like this: 695 696| PG Type | Sample Return Value | Lisp Type | 697|----------------------------+--------------------------------------+-----------------------| 698| date | #<DATE 16-05-2020> | DATE | 699| time_without_timezone | #<TIME-OF-DAY 09:47:09.926531> | TIME-OF-DAY | 700| time_with_timezone | 09:47:16.510459-04 | (VECTOR CHARACTER 64) | 701| timestamp_without_timezone | #<TIMESTAMP 16-05-2020T09:47:33,315> | TIMESTAMP | 702| timestamp_with_timezone | #<TIMESTAMP 16-05-2020T13:47:27,855> | TIMESTAMP | 703 704You can export these as json-strings with the encode-json-to-string function. E.g. 705#+BEGIN_SRC lisp 706(encode-json-to-string (query (:select 'timestamp-without-time-zone 707 :from 'test-data 708 :where (:= 'id 1)) 709 :single)) 710"\"2020-12-30 13:30:54:0\"" 711#+END_SRC 712Or more simply with passing one of the json result type parameters to the query 713function. E.g. 714#+BEGIN_SRC lisp 715(query (:select 'timestamp-with-time-zone 716 :from 'test-data 717 :where (:< 'id 3)) 718 :json-strs) 719 720'("{\"timestampWithTimeZone\":\"2019-12-30 18:30:54:0\"}" 721 "{\"timestampWithTimeZone\":\"1919-12-30 18:30:54:0\"}") 722#+END_SRC 723To get back to the default cl-postgres reader: 724#+BEGIN_SRC lisp 725(setf cl-postgres:*sql-readtable* 726 (cl-postgres:copy-sql-readtable 727 cl-postgres::*default-sql-readtable*)) 728#+END_SRC 729However [[http://marijnhaverbeke.nl/postmodern/simple-date.html][Simple-date]] has no concept of time zones. Many users use another library, 730[[https://github.com/dlowe-net/local-time][local-time]], which solves the same problem as simple-date, but does understand 731time zones. One thing to remember is that PostgreSQL doesn't store timezone 732information when using `timestamp with time zone`. Time zone information only 733used to convert it to proper UTC timestamp. 734 735*** Local-Time Library Use 736For those who want to use local-time, to enable the local-time reader: 737#+BEGIN_SRC lisp 738(ql:quickload :cl-postgres+local-time) 739(local-time:set-local-time-cl-postgres-readers) 740#+END_SRC 741 742With that set postgresql time datatype returns look like: 743With local-time loaded and local-time:set-local-time-cl-postgres-readers run, 744the same sample data looks like: 745 746| PG Type | Sample Return Value | Lisp Type | 747|----------------------------+----------------------------------+-----------------------| 748| date | 2020-05-15T20:00:00.000000-04:00 | TIMESTAMP | 749| time_without_timezone | 2000-03-01T04:47:09.926531-05:00 | TIMESTAMP | 750| time_with_timezone | 09:47:16.510459-04 | (VECTOR CHARACTER 64) | 751| timestamp_without_timezone | 2020-05-16T05:47:33.315622-04:00 | TIMESTAMP | 752| timestamp_with_timezone | 2020-05-16T09:47:27.855146-04:00 | TIMESTAMP | 753 754Similarly to simple-date timestamps, these can be exported as json-strings with the encode-json-to-string function. E.g. 755#+BEGIN_SRC lisp 756(encode-json-to-string (query (:select 'timestamp-with-time-zone 757 :from 'test-data 758 :where (:= 'id 1)) 759 :single)) 760"\"{2020-12-30T08:30:54.000000-05:00}\"" 761#+END_SRC 762Or more simply with passing one of the json result type parameters to the query 763function. E.g. 764#+BEGIN_SRC lisp 765(query (:select 'timestamp-with-time-zone 766 :from 'test-data 767 :where (:< 'id 3)) 768 :json-strs) 769 770'("{\"timestampWithTimeZone\":\"{2019-12-30T13:30:54.000000-05:00}\"}" 771 "{\"timestampWithTimeZone\":\"{1919-12-30T13:30:54.000000-05:00}\"}") 772#+END_SRC 773** Portability 774 :PROPERTIES: 775 :ID: 769a0e88-de54-4356-a474-72708accbafb 776 :CUSTOM_ID: bdf9ddb0-5f95-4807-8862-8970b35bd142 777 :END: 778The Lisp code in Postmodern is theoretically portable across implementations, 779and seems to work on all major ones as well as some minor ones such as Genera. 780It is regularly tested on ccl, sbcl, ecl and cmucl. 781 782ABCL currently has issues with utf-8 and :null.. 783 784Implementations that do not have meta-object protocol support will not have 785DAOs, but all other parts of the library should work (all widely used 786implementations do support this). 787 788The library is not likely to work for PostgreSQL versions older than 8.4. 789Other features only work in newer Postgresql versions as the features 790were only introduced in those newer versions. 791 792** Reserved Words 793 :PROPERTIES: 794 :ID: 671c5e6a-f548-4791-86a5-575b3fcc0aa5 795 :CUSTOM_ID: 565fad0b-aef4-497c-92d8-096a0a78c804 796 :END: 797It is highly suggested that you do not use words that are reserved by Postgresql 798and the sql standard as identifiers (e.g. table names, columns). The reserved 799words are: 800 801"all" "analyse" "analyze" "and" "any" "array" "as" "asc" "asymmetric" 802"authorization" "between" "binary" "both" "case" "cast" "check" "collate" 803"column" "concurrently" "constraint" "create" "cross" "current-catalog" 804"current-date" "current-role" "current-schema" "current-time" 805"current-timestamp" "current-user" "default" "deferrable" "desc" "distinct" "do" 806"else" "end" "except" "false" "fetch" "filter" "for" "foreign" "freeze" "from" 807"full" "grant" "group" "having" "ilike" "in" "initially" "inner" "intersect" 808"into" "is" "isnull" "join" "lateral" "leading" "left" "like" "limit" 809"localtime" "localtimestamp" "natural" "new" "not" "notnull" "nowait" "null" 810"off" "offset" "old" "on" "only" "or" "order" "outer" "overlaps" "placing" 811"primary" "references" "returning" "right" "select" "session-user" "share" 812"similar" "some" "symmetric" "table" "then" "to" "trailing" "true" "union" 813"unique" "user" "using" "variadic" "verbose" "when" "where" "window" "with" 814 815** Things that should be implemented 816 :PROPERTIES: 817 :ID: 50d91126-93f1-4f50-96ad-bd63a7278866 818 :CUSTOM_ID: 4ac8f5f4-d3b0-41c5-a222-fe3086049279 819 :END: 820Postmodern is under active development so Issues and feature requests should 821be flagged on [[https://github.com/marijnh/Postmodern][Postmodern's site on github]]. 822 823Some areas that are currently under consideration can be found in the ROADMAP.md 824file. 825 826* Resources 827 :PROPERTIES: 828 :ID: eb969965-5221-48f8-bb79-5a93fe451454 829 :CUSTOM_ID: 773ee4ef-a685-484e-bc6a-6daa849a7d04 830 :END: 831- [[https://mailman.common-lisp.net/listinfo/postmodern-devel][Mailing List]] 832- [[https://sites.google.com/site/sabraonthehill/postmodern-examples][A collection of Postmodern examples]] 833- [[http://www.postgresql.org/docs/current/static/index.html][The PostgreSQL manuals]] 834- [[http://www.postgresql.org/docs/current/static/protocol.html][The wire protocol Postmodern uses]] 835- [[http://clsql.b9.com/][CLSQL]] 836- [[https://github.com/filonenko-mikhail/cl-ewkb][Common Lisp Postgis library]] 837- [[http://common-lisp.net/project/local-time/][Local-time]] 838 839* Running tests 840 :PROPERTIES: 841 :ID: bd354217-9828-444b-afbf-41e9f0d522ca 842 :CUSTOM_ID: 844add79-070b-4e89-8797-3bc21ea3173b 843 :END: 844 845Postmodern uses [[https://github.com/sionescu/fiveam][FiveAM]] for 846testing. The different component systems of Postmodern have tests 847defined in corresponding test systems, each defining a test suite. 848The test systems and corresponding top-level test suites are: 849 850- `:postmodern` in `postmodern/tests`, 851- `:cl-postgres` in `cl-postgres/tests`, 852- `:s-sql` in `s-sql/tests`, and 853- `:simple-date` in `simple-date/tests`. 854 855Before running the tests make sure PostgreSQL is running and a test 856database is created. By default tests use the following connection 857parameters to run the tests: 858 859- Database name: test 860- User: test 861- Password: <empty> 862- Hostname: localhost 863- Port: 5432 864- Use-SSL :NO 865 866If connection with these parameters fails then you will be asked to 867provide the connection parameters interactively. The parameters will 868be stored in `cl-postgres-tests:*test-connection*` variable and 869automatically used on successive test runs. This variable can also be 870set manually before running the tests. 871 872To test a particular component one would first load the corresponding 873test system, and then run the test suite. For example, to test the 874`postmodern` system in the REPL one would do the following: 875#+BEGIN_SRC lisp 876(ql:quickload "postmodern/tests") 877(5am:run! :postmodern) 878 879 ;; ... test output ... 880#+END_SRC 881 882 883It is also possible to test multiple components at once by first 884loading test systems and then running all tests: 885#+BEGIN_SRC lisp 886(ql:quickload '("cl-postgres/tests" "s-sql/tests")) 887(5am:run-all-tests) 888 889 ;; ... test output ... 890#+END_SRC 891 892To run the tests from command-line specify the same forms using your 893implementation's command-line syntax. For instance, to test all 894Postmodern components on SBCL, use the following command: 895 896 env DB_USER=$USER sbcl --noinform \ 897 --eval '(ql:quickload "postmodern/tests")' \ 898 --eval '(ql:quickload "cl-postgres/tests")' \ 899 --eval '(ql:quickload "s-sql/tests")' \ 900 --eval '(ql:quickload "simple-date/tests")' \ 901 --eval '(progn (setq 5am:*print-names* nil) (5am:run-all-tests))' \ 902 --eval '(sb-ext:exit)' 903 904As you can see from above, database connection parameters can be 905provided using environment variables: 906 907- `DB_NAME`: database name, 908- `DB_USER`: user, 909- `DB_PASS`: password, 910- `DB_HOST`: hostname. 911