pg.lisp 10.8 KB
Newer Older
Eric Marsden's avatar
Eric Marsden committed
1 2
;;; pg.lisp -- socket level interface to the PostgreSQL RDBMS for Common Lisp
;;
3
;; Author: Eric Marsden <eric.marsden@free.fr>
4
;; Time-stamp: <2006-11-19 emarsden>
5
;; Version: 0.22
Eric Marsden's avatar
Eric Marsden committed
6
;;
7
;;     Copyright (C) 1999,2000,2001,2002,2003,2004,2005  Eric Marsden
Eric Marsden's avatar
Eric Marsden committed
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
;;
;;     This library is free software; you can redistribute it and/or
;;     modify it under the terms of the GNU Library General Public
;;     License as published by the Free Software Foundation; either
;;     version 2 of the License, or (at your option) any later version.
;;
;;     This library is distributed in the hope that it will be useful,
;;     but WITHOUT ANY WARRANTY; without even the implied warranty of
;;     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
;;     Library General Public License for more details.
;;
;;     You should have received a copy of the GNU Library General Public
;;     License along with this library; if not, write to the Free
;;     Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
;;
23
;; Please send suggestions and bug reports to <eric.marsden@free.fr>
Eric Marsden's avatar
Eric Marsden committed
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39


;;; Overview =========================================================
;;
;; This module lets you access the PostgreSQL object-relational DBMS
;; from Common Lisp. The code implements the client part of the
;; socket-level frontend/backend protocol, rather than providing a
;; wrapper around the libpq library. The module is capable of type
;; coercions from a range of SQL types to the equivalent Lisp type.
;; The only non portable code is the use of 'socket-connect' and
;; (optional) some way of accessing the Unix crypt() function.
;;
;; Works with pretty much all the ANSI Common Lisp implementations.
;; Exceptions are Corman Common Lisp whose socket streams do not
;; support binary I/O.
;;
Peter Van Eynde's avatar
Peter Van Eynde committed
40
;; See the README for API documentation.
Eric Marsden's avatar
Eric Marsden committed
41 42 43 44 45 46 47

;; Please note that your postmaster has to be started with the `-i'
;; option in order for it to accept TCP/IP connections (typically this
;; is not the default setting). See the PostgreSQL documentation at
;; <URL:http://www.PostgreSQL.org/> for more information.
;;
;; Thanks to Marc Battyani for the LW port and for bugfixes, to
48
;; Johannes Grødem <johs@copyleft.no> for a fix to parsing of DATE
Eric Marsden's avatar
Eric Marsden committed
49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
;; types, to Doug McNaught and Howard Ding for bugfixes, to Ernst
;; Jeschek for pointing out a bug in float parsing, to Brian Lui for
;; providing fixes for ACL6, to James Anderson for providing a fix for
;; a change in PostgreSQL timestamp format.


(declaim (optimize (speed 3) (safety 1)))

(in-package :postgresql)


(define-condition postgresql-error (simple-error) ())
(define-condition connection-failure (postgresql-error)
  ((host :initarg :host
         :reader connection-failure-host)
   (port :initarg :port
         :reader connection-failure-port)
   (transport-error :initarg :transport-error
Peter Van Eynde's avatar
Peter Van Eynde committed
67
         :reader connection-failure-transport-error))
Eric Marsden's avatar
Eric Marsden committed
68 69
  (:report
   (lambda (exc stream)
70
     (declare (type stream stream))
Eric Marsden's avatar
Eric Marsden committed
71 72 73 74 75
     (format stream "Couldn't connect to PostgreSQL database at ~a:~a.
Connection attempt reported ~A.
Is the postmaster running and accepting TCP connections?~%"
             (connection-failure-host exc)
             (connection-failure-port exc)
Peter Van Eynde's avatar
Peter Van Eynde committed
76
             (connection-failure-transport-error exc)))))
Eric Marsden's avatar
Eric Marsden committed
77 78 79 80 81 82

(define-condition authentication-failure (postgresql-error)
  ((reason :initarg :reason
           :reader authentication-failure-reason))
  (:report
   (lambda (exc stream)
83
     (declare (type stream stream))
Eric Marsden's avatar
Eric Marsden committed
84 85 86 87 88 89 90 91
     (format stream "PostgreSQL authentication failure: ~a~%"
             (authentication-failure-reason exc)))))

(define-condition protocol-error (postgresql-error)
  ((reason :initarg :reason
           :reader protocol-error-reason))
  (:report
   (lambda (exc stream)
92
     (declare (type stream stream))
Eric Marsden's avatar
Eric Marsden committed
93 94 95 96 97 98 99 100
     (format stream "PostgreSQL protocol error: ~a~%"
             (protocol-error-reason exc)))))

(define-condition backend-error (postgresql-error)
  ((reason :initarg :reason
           :reader backend-error-reason))
  (:report
   (lambda (exc stream)
101
     (declare (type stream stream))
Eric Marsden's avatar
Eric Marsden committed
102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
     (format stream "PostgreSQL backend error: ~a~%"
             (backend-error-reason exc)))))


(defconstant +NAMEDATALEN+ 32)          ; postgres_ext.h
(defconstant +SM_DATABASE+ 64)
(defconstant +SM_USER+     32)
(defconstant +SM_OPTIONS+  64)
(defconstant +SM_UNUSED+   64)
(defconstant +SM_TTY+      64)

(defconstant +STARTUP_MSG+            7)
(defconstant +STARTUP_KRB4_MSG+      10)
(defconstant +STARTUP_KRB5_MSG+      11)
(defconstant +STARTUP_PASSWORD_MSG+  14)

(defconstant +STARTUP_PACKET_SIZE+
  (+ 4 4 +SM_DATABASE+ +SM_USER+ +SM_OPTIONS+ +SM_UNUSED+ +SM_TTY+))

(defconstant +MAX_MESSAGE_LEN+    8192)     ; libpq-fe.h

(defvar *pg-client-encoding* "LATIN1"
124 125
  "The encoding that will be used for communication with the PostgreSQL backend,
for example \"LATIN1\", \"UTF8\", \"EUC_JP\".
Eric Marsden's avatar
Eric Marsden committed
126 127 128 129 130
See <http://www.postgresql.org/docs/7.3/static/multibyte.html>.")

(defvar *pg-date-style* "ISO")


131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
(defclass pgcon ()
  ((stream    :accessor pgcon-stream
              :initarg :stream
              :initform nil)
   (host      :accessor pgcon-host
              :initarg :host
              :initform nil)
   (port      :accessor pgcon-port
              :initarg :port
              :initform 0)
   (pid       :accessor pgcon-pid)
   (secret    :accessor pgcon-secret)
   (notices   :accessor pgcon-notices
              :initform (list))
   (binary-p  :accessor pgcon-binary-p
146 147 148
              :initform nil)
   (encoding  :accessor pgcon-encoding
              :initarg :encoding)))
149 150

(defmethod print-object ((self pgcon) stream)
Peter Van Eynde's avatar
Peter Van Eynde committed
151 152 153 154 155 156 157 158 159
    (print-unreadable-object (self stream :type nil)
      (with-slots (pid host port) self
        (format stream "PostgreSQL connection to backend pid ~d at ~a:~d"
                (when (slot-boundp self 'pid)
                  pid)
                (when (slot-boundp self 'host)
                  host)
                (when (slot-boundp self 'port)
                  port)))))
160 161

(defstruct pgresult connection status attributes tuples)
Eric Marsden's avatar
Eric Marsden committed
162 163


Peter Van Eynde's avatar
Peter Van Eynde committed
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198
(defgeneric pg-exec (connection &rest args)
  (:documentation
   "Execute the SQL command given by the concatenation of ARGS
on the database to which we are connected via CONNECTION. Return
a result structure which can be decoded using `pg-result'."))

(defgeneric fn (connection fn integer-result &rest args)
  (:documentation
   "Execute one of the large-object functions (lo_open, lo_close etc).
 Argument FN is either an integer, in which case it is the OID of an
 element in the pg_proc table, and otherwise it is a string which we
look up in the alist *lo-functions* to find the corresponding OID."))

(defgeneric pg-disconnect (connection)
  (:documentation
   "Disconnects from the DB"))

(defgeneric pg-supports-pbe (connection)
  (:documentation
   "Returns true if the connection supports pg-prepare/-bind and -execute")
  (:method (connection)
    (declare (ignore connection))
    nil))

(defgeneric pg-prepare (connection statement-name sql-statement &optional type-of-parameters)
  (:documentation
   "Prepares a sql-statement give a given statement-name (can be empty)
and optionally declares the types of the parameters as a list of strings.
You can define parameters to be filled in later by using $1 and so on."))

(defgeneric pg-bind (connection portal statement-name list-of-types-and-values)
  (:documentation
   "Gives the values for the parameters defined in the statement-name. The types
can be one of :char :byte :int16 :int32 or :cstring"))

199
(defgeneric pg-execute (connection portal &optional maximum-number-of-rows)
Peter Van Eynde's avatar
Peter Van Eynde committed
200
  (:documentation
201 202
   "Executes the portal defined previously and return (optionally) up to MAXIMUM-NUMBER-OF-ROWS.
For an unlimited number of rows use 0."))
Peter Van Eynde's avatar
Peter Van Eynde committed
203 204 205

(defgeneric pg-close-statement (connection statement-name)
  (:documentation
206 207
   "Closes prepared statement specified by STATEMENT-NAME and closes
all portals associated with that statement (see PG-PREPARE and PG-BIND)."))
Peter Van Eynde's avatar
Peter Van Eynde committed
208 209 210

(defgeneric pg-close-portal (connection portal)
  (:documentation
211
   "Closes a prepared statement portal"))
212

213 214 215 216 217
(defgeneric pglo-read (connection fd bytes)
  (:documentation
   "Read from a large object on file descriptor FD."))


218 219 220 221 222
;; first attempt to connect to connect using the v3 protocol; if this
;; results in an ErrorResponse we close the connection and retry using
;; the v2 protocol. This allows us to connect to PostgreSQL 7.4
;; servers using the benefits of the new protocol, but still interact
;; with older servers.
223
(defun pg-connect (dbname user &key (host "localhost") (port 5432) (password "") (encoding *pg-client-encoding*))
Eric Marsden's avatar
Eric Marsden committed
224
  "Initiate a connection with the PostgreSQL backend.
225 226 227 228 229 230 231
Connect to the database DBNAME with the username USER, on PORT of
HOST, providing PASSWORD if necessary. Return a connection to the
database (as an opaque type). If HOST is a pathname or a string
starting with #\/, it designates the directory containing the Unix
socket on which PostgreSQL's backend is waiting for local connections.
We first attempt to speak the PostgreSQL 7.4 protocol, and fall back
to the older network protocol if necessary."
232 233 234
  (handler-case (pg-connect/v3 dbname user
                               :host host
                               :port port
235 236
                               :password password
                               :encoding encoding)
237
    (protocol-error (c)
Peter Van Eynde's avatar
Peter Van Eynde committed
238
      (declare (ignore c))
239 240 241 242
      (warn "reconnecting using protocol version 2")
      (pg-connect/v2 dbname user
                     :host host
                     :port port
243 244
                     :password password
                     :encoding encoding))))
Eric Marsden's avatar
Eric Marsden committed
245 246 247 248 249 250 251 252 253 254 255 256 257


(defun pg-result (result what &rest args)
  "Extract WHAT component of RESULT.
RESULT should be a structure obtained from a call to `pg-exec',
and WHAT should be one of
   :connection -> return the connection object
   :status -> return the status string provided by the database
   :attributes -> return the metadata, as a list of lists
   :tuples -> return the data, as a list of lists
   :tuple n -> return the nth component of the data
   :oid -> return the OID (a unique identifier generated by PostgreSQL
           for each row resulting from an insertion"
Peter Van Eynde's avatar
Peter Van Eynde committed
258
  (declare (type pgresult result))
Eric Marsden's avatar
Eric Marsden committed
259 260 261 262 263 264 265 266 267 268 269 270 271 272 273
  (cond ((eq :connection what) (pgresult-connection result))
        ((eq :status what)     (pgresult-status result))
        ((eq :attributes what) (pgresult-attributes result))
        ((eq :tuples what)     (pgresult-tuples result))
        ((eq :tuple what)
         (let ((which (if args (first args) (error "which tuple?")))
               (tuples (pgresult-tuples result)))
           (nth which tuples)))
        ((eq :oid what)
         (let ((status (pgresult-status result)))
           (if (string= "INSERT" (subseq status 0 6))
               (parse-integer (subseq status 7 (position #\space status :start 7)))
               (error "Only INSERT commands generate an oid: ~s" status))))
        (t (error "Unknown result request: ~s" what))))

274

Eric Marsden's avatar
Eric Marsden committed
275
;; EOF