cl-pg 1:20061216-5 / usr / share / common-lisp / source / pg / pg.lisp

;;; pg.lisp -- socket level interface to the PostgreSQL RDBMS for Common Lisp
;;
;; Author: Eric Marsden <eric.marsden@free.fr>
;; Time-stamp: <2006-11-19 emarsden>
;; Version: 0.22
;;
;;     Copyright (C) 1999,2000,2001,2002,2003,2004,2005  Eric Marsden
;;
;;     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.
;;
;; Please send suggestions and bug reports to <eric.marsden@free.fr>


;;; 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.
;;
;; See the README for API documentation.

;; 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
;; Johannes Grødem <johs@copyleft.no> for a fix to parsing of DATE
;; 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
         :reader connection-failure-transport-error))
  (:report
   (lambda (exc stream)
     (declare (type stream stream))
     (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)
             (connection-failure-transport-error exc)))))

(define-condition authentication-failure (postgresql-error)
  ((reason :initarg :reason
           :reader authentication-failure-reason))
  (:report
   (lambda (exc stream)
     (declare (type stream stream))
     (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)
     (declare (type stream stream))
     (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)
     (declare (type stream stream))
     (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"
  "The encoding that will be used for communication with the PostgreSQL backend,
for example \"LATIN1\", \"UTF8\", \"EUC_JP\".
See <http://www.postgresql.org/docs/7.3/static/multibyte.html>.")

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


(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
              :initform nil)
   (encoding  :accessor pgcon-encoding
              :initarg :encoding)))

(defmethod print-object ((self pgcon) stream)
    (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)))))

(defstruct pgresult connection status attributes tuples)


(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 &key abort)
  (: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"))

(defgeneric pg-execute (connection portal &optional maximum-number-of-rows)
  (:documentation
   "Executes the portal defined previously and return (optionally) up to MAXIMUM-NUMBER-OF-ROWS.
For an unlimited number of rows use 0."))

(defgeneric pg-close-statement (connection statement-name)
  (:documentation
   "Closes prepared statement specified by STATEMENT-NAME and closes
all portals associated with that statement (see PG-PREPARE and PG-BIND)."))

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

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


;; 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.
(defun pg-connect (dbname user &key (host "localhost") (port 5432) (password "") (encoding *pg-client-encoding*))
  "Initiate a connection with the PostgreSQL backend.
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."
  (handler-case (pg-connect/v3 dbname user
                               :host host
                               :port port
                               :password password
                               :encoding encoding)
    (protocol-error (c)
      (declare (ignore c))
      (warn "reconnecting using protocol version 2")
      (pg-connect/v2 dbname user
                     :host host
                     :port port
                     :password password
                     :encoding encoding))))


(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"
  (declare (type pgresult result))
  (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))))


;; EOF