doc/index.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
   2 <html xmlns="http://www.w3.org/1999/xhtml">
   3
   4   <head>
   5     <title>Postmodern</title>
   6     <link rel="stylesheet" type="text/css" href="style.css"/>
   7     <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
   8   </head>
   9
  10   <body>
  11
  12     <h1>Postmodern</h1>
  13
  14     <p>Postmodern is a Common Lisp library for interacting with <a
  15     href="http://www.postgresql.org">PostgreSQL</a> databases.
  16     Features are:</p>
  17
  18     <ul>
  19       <li>Efficient communication with the database server without
  20       need for foreign libraries.</li>
  21       <li>Support for UTF-8 on Unicode-aware Lisp implementations</li>
  22       <li>A syntax for mixing SQL and Lisp code</li>
  23       <li>Convenient support for prepared statements and stored
  24       procedures</li>
  25       <li>A metaclass for simple database-access objects</li>
  26     </ul>
  27
  28     <p>The biggest differences between this library and <a
  29     href="http://clsql.b9.com/">CLSQL</a>/CommonSQL are that
  30     Postmodern has no intention of being portable across different SQL
  31     implementations (it embraces non-standard PostgreSQL features), and
  32     approaches extensions like lispy SQL and database access objects
  33     in a quite different way. This library was written because the
  34     CLSQL approach did not really work for me, your mileage may
  35     vary.</p>
  36
  37     <h2>Contents</h2>
  38
  39     <ol>
  40       <li><a href="#news">News</a></li>
  41       <li><a href="#dependencies">Dependencies</a></li>
  42       <li><a href="#license">License</a></li>
  43       <li><a href="#download">Download and installation</a></li>
  44       <li><a href="#support">Support and mailing lists</a></li>
  45       <li><a href="#quickstart">Quickstart</a></li>
  46       <li><a href="#reference">Reference</a></li>
  47       <li><a href="#caveats">Caveats and to-dos</a></li>
  48       <li><a href="#resources">Resources</a></li>
  49     </ol>
  50
  51     <h2><a name="news"></a>News</h2>
  52
  53     <p class="news"><em>28-11-2012:</em> <a href="http://marijnhaverbeke.nl/postmodern/postmodern-1.19.tgz">Version
  54     1.19</a>: Fix ECL incompatibilities,
  55     add <a href="postmodern.html#upsert-dao"><code>upsert-dao</code></a>
  56     function, add support
  57     for <a href="cl-postgres.html#wait-for-notification">notifications</a>
  58     and <a href="cl-postgres.html#bulk-copying">bulk copying</a>, and
  59     make unix sockets work on CCL.</p>
  60
  61     <p class="news"><em>19-10-2011:</em> <a href="http://marijnhaverbeke.nl/postmodern/postmodern-1.18.tgz">Version
  62     1.18</a>: Improve support for array literals, add array slicing.
  63     Switch test suite over
  64     to <a href="https://github.com/adlai/Eos">Eos</a>.
  65     Added <a href="postmodern.html#make-dao"><code>make-dao</code></a>, <a href="postmodern.html#query"><code>:dao</code></a>
  66     query selectors,
  67     and <a href="postmodern.html#define-dao-finalization"><code>define-dao-finalization</code></a>.
  68     Support PostGIS
  69     operators, <a href="s-sql.html#for-update">locking</a>,
  70     and <a href="s-sql.html#set-constraints">constraint deferring</a>
  71     syntax in S-SQL. Add
  72     a <a href="postmodern.html#!unique"><code>!unique</code></a>
  73     operator for defining table constraints. Add
  74     a <a href="cl-postgres.html#database-error-constraint-name"><code>database-error-constraint-name</code></a>
  75     condition object accessor.</p>
  76
  77     <p class="news"><em>02-02-2011:</em> <a href="http://marijnhaverbeke.nl/postmodern/postmodern-1.17.tgz">Version
  78     1.17</a>: Another modest maintenance release.
  79     Fixes <a href="simple-date.html#day-of-week"><code>day-of-week</code></a>
  80     in simple-date. Makes
  81     the <code>:plist</code> <a href="postmodern.html#query">query
  82     format</a> actually work.
  83     Adds <a href="s-sql.html#sql-escape"><code>sql-escape</code></a>
  84     as an exported symbol for client code that needs to escape stuff.
  85     Adds support for multi-dimensional arrays. Adds
  86     the <a href="postmodern.html#*ignore-unknown-columns*"><code>*ignore-unknown-columns*</code></a>
  87     configuration variable.</p>
  88
  89     <p class="news"><em>02-04-2010:</em> <a
  90     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.16.tgz">Version
  91     1.16</a>: Introduces a <code><a
  92     href="postmodern.html#save-dao/transaction">save-dao/transaction</a></code>,
  93     because the old semantics were broken (originally inside of
  94     transactions, after fixing that outside of them). Add support for
  95     passing vectors as argument to prepared queries, and reading them
  96     from query results. Add <code>:on-update</code> and
  97     <code>:on-delete</code> arguments to <code><a
  98     href="postmodern.html#!foreign">!foreign</a></code>. Add
  99     <code>:plist</code> and <code>:plists</code> query result formats.
 100     Guarantee that <code><a
 101     href="postmodern.html#deftable">deftable</a></code> definitions
 102     are executed in the order they were defined in. Moves the <a
 103     href="http://common-lisp.net/project/ieee-floats">ieee-floats</a>
 104     and <a
 105     href="http://common-lisp.net/project/trivial-utf-8">trivial-utf-8</a>
 106     dependencies into the repository, so they don't have to separately
 107     fetched.</p>
 108
 109     <p class="news"><em>02-04-2010:</em> We are moving from common-lisp.net to
 110     marijnhaverbeke.nl, and from darcs to git. New project home: <a
 111     href="http://marijnhaverbeke.nl/postmodern">http://marijnhaverbeke.nl/postmodern</a>.</p>
 112
 113     <p class="news"><em>01-03-2009:</em> <a
 114     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.15.tgz">Version
 115     1.15</a>: Almost a year since the last release. Postmodern is
 116     still alive—it is just <em>so</em> perfect it hardly needs updates
 117     anymore. In this release: Stop depending on the usocket library in
 118     Allegro CL and SBCL, just use the built-in socket bindings
 119     instead. Allow connecting over a Unix socket in SBCL. Support
 120     natural joins in <code>:select</code>. Add <code>:if-exists</code>
 121     argument to <code>:drop-...</code> operators, add support for
 122     <code>:add-column</code> form to <code>:alter-table</code>. Add
 123     <code>:between-symmetric</code> operator. Introduce "ghost slot"
 124     hack to DAO system to support fetching OIDs. Extend
 125     <code>doquery</code> to also handle parameterised queries.</p>
 126
 127     <p class="news"><em>07-03-2009:</em> <a
 128     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.14.tgz">Version
 129     1.14</a>: Some more syntax supported in <a
 130     href="s-sql.html">S-SQL</a>, add <a
 131     href="cl-postgres.html#*silently-truncate-rationals*"><code>*silently-truncate-rationals*</code></a>
 132     and <a
 133     href="cl-postgres.html#*query-callback*"><code>*query-callback*</code></a>,
 134     export <a
 135     href="postmodern.html#dao-keys"><code>dao-keys</code></a>, and fix
 136     some small bugs.</p>
 137
 138     <p class="news"><em>28-08-2008:</em> <a
 139     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.13.tgz">Version
 140     1.13</a>: A few small fixes and optimisations, and there is once
 141     again a <a
 142     href="postmodern.html#deftable"><code>deftable</code></a> &#x2015;
 143     though it has a different role than it used to have.</p>
 144
 145     <p class="news"><em>30-04-2008:</em> <a
 146     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.12.tgz">Version
 147     1.12</a>: Restores the <a
 148     href="postmodern.html#save-dao"><code>save-dao</code></a> function
 149     (though you should still be careful with it), adds <a
 150     href="cl-postgres.html#open-database">support</a> <a
 151     href="postmodern.html#*default-use-ssl*">for</a> <a
 152     href="http://www.postgresql.org/docs/current/static/ssl-tcp.html">SSL</a>
 153     connections, makes some error messages clearer, adds some S-SQL <a
 154     href="s-sql.html#between">operators</a>, and fixes a few bugs.</p>
 155
 156     <p class="news"><em>09-04-2008:</em> <a
 157     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.11.tgz">Version
 158     1.11</a>: Fixes bugs, most importantly one that caused <a
 159     href="cl-postgres.html">CL-postgres</a> to blow up whenever a <a
 160     href="http://www.postgresql.org/docs/current/static/config-setting.html">connection
 161     parameter</a> was changed for an open connection. Also adds <a
 162     href="s-sql.html#*escape-sql-names-p*">automatic escaping</a> of
 163     reserved words to S-SQL, a <a
 164     href="postmodern.html#*max-pool-size*"><code>*max-pool-size*</code></a>
 165     parameter and a <a
 166     href="postmodern.html#coalesce"><code>coalesce</code></a> function
 167     to Postmodern</p>
 168
 169     <p class="news"><em>19-03-2008:</em> <a
 170     href="http://marijnhaverbeke.nl/postmodern/postmodern-1.10.tgz">Version
 171     1.10</a>: Note that this release is <strong>not entirely
 172     backwards-compatible</strong>. It introduces a new, more flexible
 173     approach to <a href="postmodern.html#daos">DAO objects</a>, a more
 174     pluggable <a href="cl-postgres.html#reading">handling</a> of
 175     custom data types, and a bunch of small clean-ups, optimizations,
 176     and enhancements. See the <a
 177     href="migrating-to-1.10.html">migration guide</a> for details.</p>
 178
 179     <h2><a name="dependencies"></a>Dependencies</h2>
 180
 181     <p>The library depends on <a
 182     href="http://cliki.net/usocket">usocket</a> (except on SBCL and
 183     ACL, where the built-in socket library is used), <a
 184     href="http://www.cliki.net/CL-MD5">md5</a>, <a
 185     href="http://common-lisp.net/project/closer/closer-mop.html">closer-mop</a>,
 186     <a
 187     href="http://common-lisp.net/project/bordeaux-threads">bordeaux-threads</a>
 188     if you want thread-safe connection pools, and <a
 189     href="http://common-lisp.net/project/cl-plus-ssl/">CL+SSL</a> when
 190     SSL connections are needed.</p>
 191
 192     <p>Postmodern itself is split into four different packages, some
 193     of which can be used independently. <a
 194     href="simple-date.html">Simple-date</a> is a very basic
 195     implementation of date and time objects, used to support storing
 196     and retrieving time-related SQL types. <a
 197     href="cl-postgres.html">CL-postgres</a> is the low-level library
 198     used for interfacing with a PostgreSQL server over a socket. <a
 199     href="s-sql.html">S-SQL</a> is used to compile s-expressions to
 200     strings of SQL code, escaping any Lisp values inside, and doing as
 201     much as possible of the work at compile time. Finally, <a
 202     href="postmodern.html">Postmodern</a> itself is the library that
 203     tries to put all these things together into a convenient
 204     programming interface.</p>
 205
 206     <h2><a name="license"></a>License</h2>
 207
 208     <p>Postmodern is released under a zlib-style license. Which
 209     approximately means you can use the code in whatever way you like,
 210     except for passing it off as your own or releasing a modified
 211     version without indication that it is not the original.</p>
 212
 213     <h2><a name="download"></a>Download and installation</h2>
 214
 215     <p>The latest release of Postmodern can be downloaded from <a
 216     href="http://marijnhaverbeke.nl/postmodern/postmodern.tgz">http://marijnhaverbeke.nl/postmodern/postmodern.tgz</a>,
 217     or installed with <a
 218     href="http://www.cliki.net/ASDF-Install">asdf-install</a>.</p>
 219
 220     <p>A <a href="http://git-scm.com/">git</a> repository with the
 221     most recent changes can be checked out with:</p>
 222
 223     <pre>&gt; git clone http://marijnhaverbeke.nl/git/postmodern</pre>
 224
 225     <p>You can also view the repository on <a
 226     href="http://github.com/marijnh/Postmodern">github</a>.</p>
 227
 228     <p>The file <a
 229     href="http://marijnhaverbeke.nl/postmodern/postmodern-latest.tgz">http://marijnhaverbeke.nl/postmodern/postmodern-latest.tgz</a>
 230     always contains a snapshot of the current repository head.</p>
 231
 232     <h2><a name="quickstart"></a>Quickstart</h2>
 233
 234     <p>This quickstart is intended to give you a feel of the way
 235     coding with Postmodern works. Further details about the workings
 236     of the library can be found in the <a href="#reference">reference
 237     manual</a>.</p>
 238
 239     <p>Assuming you have already installed it, first load and use the
 240     system:</p>
 241
 242     <pre class="code">
 243 (asdf:oos 'asdf:load-op :postmodern)
 244 (use-package :postmodern)</pre>
 245
 246     <p>If you have a PostgreSQL server running on localhost, with a
 247     database called 'testdb' on it, which is accessible for user
 248     'foucault' with password 'surveiller', you can connect like
 249     this:</p>
 250
 251     <pre class="code">
 252 (connect-toplevel "testdb" "foucault" "surveiller" "localhost")</pre>
 253
 254     <p>Which will establish a connection to be used by all code,
 255     except for that wrapped in a <a
 256     href="postmodern.html#with-connection"><code>with-connection</code></a>
 257     form, which takes the same arguments but only establishes the
 258     connection locally.</p>
 259
 260     <p>Now for a basic sanity test:</p>
 261
 262     <pre class="code">
 263 (query "select 22, 'Folie et déraison', 4.5")
 264 ;; => ((22 "Folie et déraison" 9/2))</pre>
 265
 266     <p>That should work. <a href="postmodern.html#query">query</a> is
 267     the basic way to send queries to the database. The same query can
 268     be expressed like this:</p>
 269
 270     <pre class="code">
 271 (query (:select 22 "Folie et déraison" 4.5))
 272 ;; => ((22 "Folie et déraison" 9/2))</pre>
 273
 274     <p>In many contexts, query strings and lists starting with
 275     keywords can be used interchangeably. The lists will be compiled
 276     to SQL. The <a href="s-sql.html">S-SQL</a> manual describes the
 277     syntax used by these expressions. Lisp values occurring in them are
 278     automatically escaped. In the above query, only constant values
 279     are used, but it is possible to transparently use run-time values
 280     as well:</p>
 281
 282     <pre class="code">
 283 (defun database-powered-addition (a b)
 284   (query (:select (:+ a b)) :single))
 285 (database-powered-addition 1030 204)
 286 ;; => 1234</pre>
 287
 288     <p>That last argument, <code>:single</code>, indicates that we
 289     want the result not as a list of lists (for the result rows), but
 290     as a single value, since we know that we are only selecting one
 291     value. Some other options are <code>:rows</code>,
 292     <code>:row</code>, <code>:column</code>, <code>:alists</code>, and
 293     <code>:none</code>. Their precise effect is documented in the <a
 294     href="postmodern.html#query">reference manual</a>.</p>
 295
 296     <p>You do not have to pull in the whole result of a query at once,
 297     you can also iterate over it with the <a
 298     href="postmodern.html#doquery"><code>doquery</code></a> macro:</p>
 299
 300     <pre class="code">
 301 (doquery (:select 'x 'y :from 'some-imaginary-table) (x y)
 302   (format t "On this row, x = ~A and y = ~A.~%" x y))</pre>
 303
 304     <p>This is what a database-access class looks like:</p>
 305
 306     <pre class="code">
 307 (defclass country ()
 308   ((name :col-type string :initarg :name
 309          :reader country-name)
 310    (inhabitants :col-type integer :initarg :inhabitants
 311                 :accessor country-inhabitants)
 312    (sovereign :col-type (or db-null string) :initarg :sovereign
 313               :accessor country-sovereign))
 314   (:metaclass dao-class)
 315   (:keys name))</pre>
 316
 317     <p>The above defines a class that can be used to handle records in
 318     a table with three columns: name, inhabitants, and sovereign. In
 319     simple cases, the information above is enough to define the table
 320     as well:</p>
 321
 322     <pre class="code">
 323 (dao-table-definition 'country)
 324 ;; => "CREATE TABLE country (
 325 ;;      name TEXT NOT NULL,
 326 ;;      inhabitants INTEGER NOT NULL,
 327 ;;      sovereign TEXT,
 328 ;;      PRIMARY KEY (name))"
 329 (execute (dao-table-definition 'country))</pre>
 330
 331    <p>This defines our table in the database. <a
 332    href="postmodern.html#execute">execute</a> works like query, but
 333    does not expect any results back.</p>
 334
 335    <p>Let us add a few countries:</p>
 336
 337    <pre class="code">
 338 (insert-dao (make-instance 'country :name "The Netherlands"
 339                                     :inhabitants 16800000
 340                                     :sovereign "Willem-Alexander"))
 341 (insert-dao (make-instance 'country :name "Croatia"
 342                                     :inhabitants 4400000))</pre>
 343
 344     <p>Then, to update Croatia's population, we could do this:</p>
 345
 346     <pre class="code">
 347 (let ((croatia (get-dao 'country "Croatia")))
 348   (setf (country-inhabitants croatia) 4500000)
 349   (update-dao croatia))
 350 (query (:select '* :from 'country))
 351 ;; => (("The Netherlands" 16800000 "Willem-Alexander")
 352 ;;     ("Croatia" 4500000 :NULL))</pre>
 353
 354     <p>Next, to demonstrate a bit more of the S-SQL syntax, here is
 355     the query the utility function <a
 356     href="postmodern.html#list-tables"><code>list-tables</code></a>
 357     uses to get a list of the tables in a database:</p>
 358
 359     <pre class="code">
 360 (sql (:select 'relname :from 'pg-catalog.pg-class
 361       :inner-join 'pg-catalog.pg-namespace :on (:= 'relnamespace 'pg-namespace.oid)
 362       :where (:and (:= 'relkind "r")
 363                    (:not-in 'nspname (:set "pg_catalog" "pg_toast"))
 364                    (:pg-catalog.pg-table-is-visible 'pg-class.oid))))
 365 ;; => "(SELECT relname FROM pg_catalog.pg_class
 366 ;;      INNER JOIN pg_catalog.pg_namespace ON (relnamespace = pg_namespace.oid)
 367 ;;      WHERE ((relkind = 'r') and (nspname NOT IN ('pg_catalog', 'pg_toast'))
 368 ;;             and pg_catalog.pg_table_is_visible(pg_class.oid)))"</pre>
 369
 370     <p><a href="s-sql.html#sql"><code>sql</code></a> is a macro that
 371     will simply compile a query, it can be useful for seeing how your
 372     queries are expanded or if you want to do something unexpected
 373     with them.</p>
 374
 375     <p>As you can see, lists starting with keywords are used to
 376     express SQL commands and operators (lists starting with something
 377     else will be evaluated and then inserted into the query). Quoted
 378     symbols name columns or tables (keywords can also be used but
 379     might introduce ambiguities). The syntax supports subqueries,
 380     multiple joins, stored procedures, etc. See the S-SQL <a
 381     href="s-sql.html">reference manual</a> for a complete
 382     treatment.</p>
 383
 384     <p>Finally, here is an example of the use of prepared
 385     statements:</p>
 386
 387     <pre class="code">
 388 (defprepared sovereign-of
 389   (:select 'sovereign :from 'country :where (:= 'name '$1))
 390   :single!)
 391 (sovereign-of "The Netherlands")
 392 ;; => "Willem-Alexander"</pre>
 393
 394     <p>The <a
 395     href="postmodern.html#defprepared"><code>defprepared</code></a>
 396     macro creates a function that takes the same amount of arguments
 397     as there are <code>$X</code> placeholders in the given query. The
 398     query will only be parsed and planned once (per database
 399     connection), which can be faster, especially for complex
 400     queries.</p>
 401
 402     <pre class="code">
 403 (disconnect-toplevel)</pre>
 404
 405     <h2><a name="reference"></a>Reference</h2>
 406
 407     <p>The reference manuals for the different components of
 408     Postmodern are kept in separate files. For using the library in
 409     the most straightforward way, you only really need to read the <a
 410     href="postmodern.html">Postmodern reference</a> and glance over
 411     the <a href="s-sql.html">S-SQL reference</a>. The <a
 412     href="simple-date.html">simple-date</a> reference explains the
 413     time-related data types included in Postmodern, and the <a
 414     href="cl-postgres.html">CL-postgres reference</a> might be useful
 415     if you just want a low-level library for talking to a PostgreSQL
 416     server.</p>
 417
 418     <ul>
 419       <li><a href="postmodern.html">Postmodern</a></li>
 420       <li><a href="s-sql.html">S-SQL</a></li>
 421       <li><a href="simple-date.html">Simple-date</a></li>
 422       <li><a href="cl-postgres.html">CL-postgres</a></li>
 423     </ul>
 424
 425     <h2><a name="caveats"></a>Caveats and to-dos</h2>
 426
 427     <h3>Timezones</h3>
 428
 429     <p><a href="simple-date.html">Simple-date</a> has no concept of
 430     time zones. This means that using it is rather error-prone, and if
 431     you really need your time-keeping to be reliable and/or universal
 432     you should either not use the types it provides or think really
 433     hard about the way you handle time zones.</p>
 434
 435     <p>Recently, a lot of work has been done on <a
 436     href="http://www.common-lisp.net/project/local-time">local-time</a>,
 437     which solves the same problem as simple-date, but does understand
 438     time zones. The <a
 439     href="http://common-lisp.net/cgi-bin/darcsweb/darcsweb.cgi?r=local-time-local-time-1.0">1.0
 440     repository</a> currently has code for integration with
 441     CL-postgres, though this might not be stable yet.</p>
 442
 443     <h3>Portability</h3>
 444
 445     <p>The Lisp code in Postmodern is theoretically portable across
 446     implementations, and seems to work on all major ones. I am not
 447     actively testing against new releases or obscure implementations,
 448     but if you run into problems you are welcome to contact me through
 449     the <a
 450     href="mailto:postmodern-devel+subscribe@common-lisp.net">mailing
 451     list</a>, and we can try to solve them. Implementations that do
 452     not have meta-object protocol support will not have DAOs, but all
 453     other parts of the library should work (all widely used
 454     implementations do support this).</p>
 455
 456     <p>The library will definitely not work for PostgreSQL versions
 457     older than 7.4 (it uses a client/server protocol that was
 458     introduced in that version). On versions prior to 8.1, retrieving
 459     date and time objects is broken, because their binary
 460     representation was changed. Part of the functionality of <a
 461     href="postmodern.html#insert-dao"><code>insert-dao</code></a>
 462     (automatic defaulting of unbound slots) only works in PostgreSQL
 463     8.2 and up.</p>
 464
 465     <h3>Things that should be implemented</h3>
 466
 467     <p>It would be a nice feature if Postmodern could help you with
 468     defining your database schemas and, more importantly, updating
 469     your databases when your code changes. It would theoretically not
 470     be hard to build a function that compares a schema on the Lisp
 471     side with the state of the database, and helps you to
 472     interactively update your database. PostgreSQL has a quite
 473     complete introspection system. Unfortunately it would be a lot of
 474     work to implement this, since databases can contain so many
 475     different types of entities (tables, views, indices, procedures,
 476     constraints, sequences, etc.) which are all created, changed, and
 477     dropped in different ways.</p>
 478
 479     <h2 id="resources">Resources</h2>
 480
 481     <ul>
 482       <li><a href="https://sites.google.com/site/sabraonthehill/postmodern-examples">A collection of Postmodern examples</a></li>
 483       <li><a href="http://www.postgresql.org/docs/current/static/index.html">The PostgreSQL manuals</a></li>
 484       <li><a href="http://www.postgresql.org/docs/current/static/protocol.html">The wire protocol Postmodern uses</a></li>
 485       <li><a href="http://clsql.b9.com/">CLSQL</a></li>
 486       <li><a href="https://github.com/filonenko-mikhail/cl-ewkb">Common Lisp Postgis library</a></li>
 487       <li><a href="http://common-lisp.net/project/local-time/">Local-time</a></li>
 488     </ul>
 489
 490   </body>
 491
 492 </html>