;;; pg.lisp -- socket level interface to the PostgreSQL RDBMS for Common Lisp
;;
;; Author: Eric Marsden <emarsden@laas.fr>
;; Time-stamp: <2003-03-09 emarsden>
;; Version: 0.18
;;
;;     Copyright (C) 1999,2000,2001,2002,2003  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 <emarsden@laas.fr>
;; The latest version of this package should be available from
;;
;;     <URL:http://purl.org/net/emarsden/home/downloads/>


;;; 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 CMUCL, CLISP, ACL, OpenMCL and MCL. Lispworks has a bug in
;; binary I/O on sockets for which Marc Battyani has a workaround.
;; CormanLisp has socket support but not for binary I/O.


;;; Entry points =======================================================
;;
;; (with-pg-connection ((con &rest open-args) &body body)
;;     A macro which opens a connection to database DBNAME, executes the
;;     BODY forms then disconnects. See function `pg-connect' for details
;;     of the connection arguments OPEN-ARGS.
;;
;; (with-pg-transaction con &body body)
;;     A macro which executes the BODY forms wrapped in an SQL transaction.
;;     CON is a connection to the database. If an error occurs during the
;;     execution of the forms, a ROLLBACK instruction is executed.
;;
;; (pg-connect dbname user &key password host port) -> connection
;;     Connect to the database DBNAME on HOST (defaults to localhost)
;;     at PORT (defaults to 5432) via TCP/IP and log in as USER. If
;;     the database requires a password, send PASSWORD (as clear text
;;     unless the backend demands crypt() authentication). Set the
;;     output date type to 'ISO', and initialize our type parser
;;     tables.
;;
;; (pg-exec connection &rest sql) -> pgresult
;;     Concatenate the SQL strings and send to the backend. Retrieve
;;     all the information returned by the database and return it in
;;     an opaque record PGRESULT.
;;
;; (pg-result pgresult what &rest args) -> info
;;     Extract information from the PGRESULT. WHAT can be one of
;;          * :connection
;;          * :status
;;          * :attributes
;;          * :tuples
;;          * :tuple tupleNumber
;;          * :oid
;;     `connection' allows you to retrieve the database connection.
;;     `status' is a string returned by the backend to indicate the
;;     status of the command; it is normally "SELECT" for a select
;;     command, "DELETE 1" if the deletion affected a single row, etc.
;;     `attributes' is a list of tuples providing metadata: the first
;;     component of each tuple is the attribute's name as a string,
;;     the second an integer representing its PostgreSQL type, and the
;;     third an integer representing the size of that type. `tuples'
;;     returns all the data retrieved from the database, as a list of
;;     lists, each list corresponding to one row of data returned by
;;     the backend. `tuple num' can be used to extract a specific
;;     tuple. `oid' allows you to retrieve the OID returned by the
;;     backend if the command was an insertion; the OID is a unique
;;     identifier for that row in the database (this is
;;     PostgreSQL-specific, please refer to the documentation for more
;;     details).
;;
;; (pg-for-each connection select-form callback)
;;     Calls CALLBACK on each tuple returned by SELECT-FORM. Declares
;;     a cursor for SELECT-FORM, then fetches tuples using repeated
;;     executions of FETCH 1, until no results are left. The cursor is
;;     then closed. The work is performed within a transaction. When
;;     you have a large amount of data to handle, this usage is more
;;     efficient than fetching all the tuples in one go.
;;
;; (pg-disconnect connection) -> nil
;;     Close the database connection.
;;
;; (pg-databases connection) -> list of strings
;;     Return a list of the databases available at this site (a
;;     database is a set of tables; in a virgin PostgreSQL
;;     installation there is a single database named "template1").
;;
;; (pg-tables connection) -> list of strings
;;     Return a list of the tables present in the database to which we
;;     are currently connected. Only include user tables: system
;;     tables are excluded.
;;
;; (pg-columns connection table) -> list of strings
;;     Return a list of the columns (or attributes) in TABLE, which
;;     must be a table in the database to which we are currently
;;     connected. We only include the column names; if you want more
;;     detailed information (attribute types, for example), it can be
;;     obtained from `pg-result' on a SELECT statement for that table.
;;
;; (pglo-create conn . args) -> oid
;;     Create a new large object (BLOB, or binary large object in
;;     other DBMSes parlance) in the database to which we are
;;     connected via CONN. Returns an OID (which is represented as a
;;     Scheme integer) which will allow you to use the large object.
;;     Optional ARGS are a Unix-style mode string which determines the
;;     permissions of the newly created large object, one of "r" for
;;     read-only permission, "w" for write-only, "rw" for read+write.
;;     Default is "r".
;;
;;     Large-object functions MUST be used within a transaction (see
;;     the macro `with-pg-transaction').
;;
;; (pglo-open conn oid . args) -> fd
;;     Open a large object whose unique identifier is OID (a Scheme
;;     integer) in the database to which we are connected via CONN.
;;     Optional ARGS is a Unix-style mode string as for pglo-create;
;;     which defaults to "r" read-only permissions. Returns a file
;;     descriptor (a Scheme integer) which can be used in other
;;     large-object functions.
;;
;; (pglo-close conn fd)
;;     Close the file descriptor FD which was associated with a large
;;     object. Note that this does not delete the large object; use
;;     `pglo-unlink' for that.
;;
;; (pglo-read conn fd bytes) -> string
;;     Read BYTES from the file descriptor FD which is associated with
;;     a large object. Return a Scheme string which should be BYTES
;;     characters long.
;;
;; (pglo-write connection fd buf)
;;     Write the bytes contained in the Scheme string BUF to the
;;     large object associated with the file descriptor FD.
;;
;; (pglo-lseek conn fd offset whence)
;;     Do the equivalent of a lseek(2) on the file descriptor FD which
;;     is associated with a large object; ie reposition the read/write
;;     file offset for that large object to OFFSET (an Scheme
;;     integer). WHENCE has the same significance as in lseek(); it
;;     should be one of SEEK_SET (set the offset to the absolute
;;     position), SEEK_CUR (set the offset relative to the current
;;     offset) or SEEK_END (set the offset relative to the end of the
;;     file). WHENCE should be a Scheme integer whose values can be
;;     obtained from the header file <unistd.h> (probably 0, 1 and 2
;;     respectively).
;;
;; (pglo-tell conn oid) -> integer
;;     Do the equivalent of an ftell(3) on the file associated with
;;     the large object whose unique identifier is OID. Returns the
;;     current position of the file offset for the object's associated
;;     file descriptor, as a Scheme integer.
;;
;; (pglo-unlink conn oid)
;;     Remove the large object whose unique identifier is OID from the
;;     system (in the current implementation of large objects in
;;     PostgreSQL, each large object is associated with an object in
;;     the filesystem).
;;
;; (pglo-import conn filename) -> oid
;;     Create a new large object and initialize it to the data
;;     contained in the file whose name is FILENAME. Returns an OID
;;     (as a CL integer). Note that is operation is only syntactic
;;     sugar around the basic large-object operations listed above.
;;
;; (pglo-export conn oid filename)
;;     Create a new file named FILENAME and fill it with the contents
;;     of the large object whose unique identifier is OID. This
;;     operation is also syntactic sugar.
;;
;; Boolean variable `*PG-DISABLE-TYPE-COERCION*' which can be set to
;; non-nil (before initiating a connection) to disable the library's
;; type coercion facility. Default is t.
;;
;;
;;
;; SECURITY NOTE: setting up PostgreSQL to accept TCP/IP connections
;; has security implications; please consult the documentation for
;; details. pg.lisp is able to use the crypt authentication method to
;; avoid sending the password in cleartext over the wire (this assumes
;; access to the `crypt' function via the FFI). It does not support
;; the Kerberos authentication method. However, it is possible to use
;; the port forwarding capabilities of ssh to establish a connection
;; to the backend over TCP/IP, which provides both a secure
;; authentication mechanism and encryption (and optionally
;; compression) of data passing through the tunnel. Here's how to do
;; it (thanks to Gene Selkov, Jr. <selkovjr@mcs.anl.gov> for the
;; description):
;;
;; 1. Establish a tunnel to the backend machine, like this:
;;
;; 	ssh -L 3333:backend.dom:5432 postgres@backend.dom
;;
;;    The first number in the -L argument, 3333, is the port number of
;;    your end of the tunnel. The second number, 5432, is the remote
;;    end of the tunnel -- the port number your backend is using. The
;;    name or the address in between the port numbers belongs to the
;;    server machine, as does the last argument to ssh that also includes
;;    the optional user name. Without the user name, ssh will try the
;;    name you are currently logged on as on the client machine. You can
;;    use any user name the server machine will accept, not necessarily
;;    those related to postgres.
;;
;; 2. Now that you have a running ssh session, you can point pg.lisp to
;;    the local host at the port number which you specified in step 1.
;;    For example,
;;
;;         (pg-connect "dbname" "user" :port 3333)
;;
;;    You can omit the port argument if you chose 5432 as the local
;;    end of the tunnel, since pg.lisp defaults to this value.
;;
;;
;; This code has been tested or reported to work with
;;
;;   * CMUCL 18d on Solaris/SPARC and Linux/x86
;;   * CLISP 2.30 on LinuxPPC and SPARC
;;   * ACL 6.1 trial/x86
;;   * PostgreSQL 6.5, 7.0, 7.1.2, 7.2.
;;
;; 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, and to Brian Lui
;; for providing fixes for ACL6.


;;; TODO ============================================================
;;
;; * add a mechanism for parsing user-defined types. The user should
;;   be able to define a parse function and a type-name; we query
;;   pg_type to get the type's OID and add the information to
;;   pg:*parsers*.


(defpackage :postgresql
  (:nicknames :pg)
  (:use :common-lisp
        #+cmu :alien
        #+cmu :c-call
        #+openmcl :ccl)
  (:export #:pg-connect #:pg-exec #:pg-result #:pg-disconnect
           #:*pg-disable-type-coercion*
           #:pg-databases #:pg-tables #:pg-columns
           #:pg-backend-version
           #:with-pg-connection
           #:with-pg-transaction
           #:pg-for-each
           #:pglo-create
           #:pglo-open
           #:pglo-close
           #:pglo-read
           #:pglo-write
           #:pglo-lseek
           #:pglo-tell
           #:pglo-unlink
           #:pglo-import
           #:pglo-export))
(in-package :postgresql)

#+allegro (require :socket)
#+lispworks (require "comm")
#+cormanlisp (require :sockets)
#+(and mcl (not openmcl)) (require "OPENTRANSPORT")


(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))
  (:report
   (lambda (exc stream)
     (format stream "Couldn't connect to PostgreSQL database at ~a:~a.
Is the postmaster running and accepting TCP connections?~%"
             (connection-failure-host exc)
             (connection-failure-port exc)))))

(define-condition authentication-failure (postgresql-error)
  ((reason :initarg :reason
           :reader authentication-failure-reason))
  (:report
   (lambda (exc 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)
     (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)
     (format stream "PostgreSQL backend error: ~a~%"
             (backend-error-reason exc)))))


(defconstant +NAMEDATALEN+ 32)          ; postgres_ext.h
(defconstant +PG_PROTOCOL_LATEST_MAJOR+ 2) ; libpq/pgcomm.h
(defconstant +PG_PROTOCOL_63_MAJOR+     1)
(defconstant +PG_PROTOCOL_62_MAJOR+     0)
(defconstant +PG_PROTOCOL_LATEST_MINOR+ 0)
(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

(defconstant +INV_ARCHIVE+ #x10000)     ; fe-lobj.c
(defconstant +INV_WRITE+   #x20000)
(defconstant +INV_READ+    #x40000)
(defconstant +LO_BUFSIZ+   1024)

;; alist of (oid . parser) pairs. This is built dynamically at
;; initialization of the connection with the database (once generated,
;; the information is shared between connections).
(defvar *parsers* '())

(defvar *pg-disable-type-coercion* nil
  "Non-nil disables the type coercion mechanism.
The default is nil, which means that data recovered from the
database is coerced to the corresponding Common Lisp type before
being returned; for example numeric data is transformed to CL
numbers, and booleans to booleans.

The coercion mechanism requires an initialization query to the
database, in order to build a table mapping type names to OIDs. This
option is provided mainly in case you wish to avoid the overhead of
this initial query. The overhead is only incurred once per session
(not per connection to the backend).")



(defstruct (pgcon (:print-function print-pgcon))
  stream pid secret notices (binary-p nil) host port)
(defstruct pgresult connection status attributes tuples)

(defun print-pgcon (self &optional (stream t) depth)
  (declare (ignore depth))
  (print-unreadable-object (self stream :type nil)
     (format stream "PostgreSQL connection to backend pid ~d at ~a:~d"
             (pgcon-pid self)
             (pgcon-host self)
             (pgcon-port self))))

(defmacro with-pg-connection ((con &rest open-args) &body body)
  "Bindspec is of the form (connection open-args), where OPEN-ARGS are
as for PG-CONNECT. The database connection is bound to the variable
CONNECTION. If the connection is unsuccessful, the forms are not
evaluated. Otherwise, the BODY forms are executed, and upon
termination, normal or otherwise, the database connection is closed."
  `(let ((,con (pg-connect ,@open-args)))
     (unwind-protect
         (progn ,@body)
       (when ,con (pg-disconnect ,con)))))

(defmacro with-pg-transaction (con &body body)
  "Execute BODY forms in a BEGIN..END block.
If a PostgreSQL error occurs during execution of the forms, execute
a ROLLBACK command.
Large-object manipulations _must_ occur within a transaction, since
the large object descriptors are only valid within the context of a
transaction."
  `(progn
     (pg-exec ,con "BEGIN WORK")
     (handler-case (prog1 (progn ,@body) (pg-exec ,con "COMMIT WORK"))
      (error (e)
       (pg-exec ,con "ROLLBACK WORK")
       (signal e)))))

(defun pg-for-each (conn select-form callback)
  "Create a cursor for SELECT-FORM, and call CALLBACK for each result.
Uses the PostgreSQL database connection CONN. SELECT-FORM must be an
SQL SELECT statement. The cursor is created using an SQL DECLARE
CURSOR command, then results are fetched successively until no results
are left. The cursor is then closed.

The work is performed within a transaction. The work can be
interrupted before all tuples have been handled by THROWing to a tag
called 'pg-finished."
  (let ((cursor (symbol-name (gensym "pgelcursor"))))
    (catch 'pg-finished
      (with-pg-transaction conn
         (pg-exec conn "DECLARE " cursor " CURSOR FOR " select-form)
         (unwind-protect
             (loop :for res = (pg-result (pg-exec conn "FETCH 1 FROM " cursor) :tuples)
                   :until (zerop (length res))
                   :do (funcall callback (first res)))
           (pg-exec conn "CLOSE " cursor))))))


(defun pg-connect (dbname user &key (host "localhost") (port 5432) (password ""))
  "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)."
  (let* ((stream (socket-connect port host))
         (connection (make-pgcon :stream stream :host host :port port))
         (user-packet-length (+ +SM_USER+ +SM_OPTIONS+ +SM_UNUSED+ +SM_TTY+)))
    ;; send the startup packet
    (send-int connection +STARTUP_PACKET_SIZE+ 4)
    (send-int connection +PG_PROTOCOL_LATEST_MAJOR+ 2)
    (send-int connection +PG_PROTOCOL_LATEST_MINOR+ 2)
    (send-string connection dbname +SM_DATABASE+)
    (send-string connection user user-packet-length)
    (flush connection)
    #+cmu (ext:finalize connection (lambda () (pg-disconnect connection)))
    (loop
     (case (read-byte stream)
       ;; ErrorResponse
       ((69) (error 'authentication-failure
                    :reason (read-cstring connection 4096)))

       ;; Authentication
       ((82)
        (case (read-net-int connection 4)
          ((0)                          ; AuthOK
           (and (not *pg-disable-type-coercion*)
                (null *parsers*)
                (initialize-parsers connection))
           (pg-exec connection "SET datestyle = 'ISO'")
           (return connection))
          ((3)                          ; AuthUnencryptedPassword
           (send-int connection (+ 5 (length password)) 4)
           (send-string connection password)
           (send-int connection 0 1)
           (flush connection))
          ((4)                          ; AuthEncryptedPassword
           (let* ((salt (read-chars connection 2))
                  (crypted (crypt password salt)))
             (format *debug-io* "Got salt of ~s~%" salt)
             (send-int connection (+ 5 (length crypted)) 4)
             (send-string connection crypted)
             (send-int connection 0 1)
             (flush connection)))
          ((1)                          ; AuthKerberos4
           (error 'authentication-failure
                  :reason "Kerberos4 authentication not supported"))
          ((2)                          ; AuthKerberos5
           (error 'authentication-failure
                  :reason "Kerberos5 authentication not supported"))
          (t (error 'authentication-failure
                    :reason "unknown authentication type"))))

       (t (error 'protocol-error
                 :reason "expected an authentication response"))))))

(defun pg-exec (connection &rest args)
  "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'."
  (let ((sql (apply #'concatenate 'simple-string args))
        (stream (pgcon-stream connection))
        (tuples '())
        (attributes '())
        (result (make-pgresult :connection connection)))
    (when (> (length sql) +MAX_MESSAGE_LEN+)
      (error "SQL statement too long" sql))
    (write-byte 81 stream)
    (send-string connection sql)
    (write-byte 0 stream)
    (flush connection)
    (do ((b (read-byte stream nil :eof)
            (read-byte stream nil :eof)))
        ((eq b :eof) (error 'protocol-error :reason "unexpected EOF from backend"))
      (case b
        ;; asynchronous notify, #\A
        ((65)
         ;; read the pid
         (read-net-int connection 4)
         (handle-notice connection))

        ;; BinaryRow, #\B
        ((66)
         (setf (pgcon-binary-p connection) t)
         (unless attributes
           (error 'protocol-error :reason "Tuple received before metadata"))
         (push (read-tuple connection attributes) tuples))

        ;; CompletedResponse, #\C
        ((67)
         (let ((status (read-cstring connection +MAX_MESSAGE_LEN+)))
           (setf (pgresult-status result) status)
           (setf (pgresult-tuples result) (nreverse tuples))
           (setf (pgresult-attributes result) attributes)
           (return result)))

        ;; AsciiRow (text data transfer), #\D
        ((68)
         (setf (pgcon-binary-p connection) nil)
         (unless attributes
           (error 'protocol-error :reason "Tuple received before metadata"))
         (push (read-tuple connection attributes) tuples))

        ;; ErrorResponse, #\E
        ((69)
         (let ((msg (read-cstring connection +MAX_MESSAGE_LEN+)))
           (error 'backend-error :reason msg)))

        ;; #\G and #\H: start copy in, start copy out

        ;; EmptyQueryResponse, #\I
        ((73)
         (let ((c (read-byte connection)))
           (when (< 0 c)
             (error 'protocol-error :reason "Garbled data"))))

        ;; BackendKeyData, #\K
        ((75)
         (setf (pgcon-pid connection) (read-net-int connection 4))
         (setf (pgcon-secret connection) (read-net-int connection 4)))

        ;; NotificationResponse, #\N
        ((78)
         (setf (pgcon-pid connection) (read-net-int connection 4))
         (handle-notice connection))

        ;; CursorResponse, #\P
        ((80)
         (let ((str (read-cstring connection +MAX_MESSAGE_LEN+)))
           (declare (ignore str))
           ;; (format *debug-io* "Portal name ~a~%" str)
           ))

        ;; RowDescription (metadata for subsequent tuples), #\T
        ((84)
         (and attributes (error "Cannot handle multiple result group"))
         (setq attributes (read-attributes connection)))

        ;; ReadyForQuery
        ((90) t)

        (t
         (error 'protocol-error
                :reason (format nil "Unknown response type from backend ~d" b)))))))


(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"
  (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))))

(defun pg-disconnect (connection)
  (write-byte 88 (pgcon-stream connection))
  (flush connection)
  (close (pgcon-stream connection))
  (values))



;; Attribute information is as follows
;;    attribute-name (string)
;;    attribute-type as an oid from table pg_type
;;    attribute-size (in bytes?)
(defun read-attributes (connection)
  (let ((attribute-count (read-net-int connection 2))
        (attributes '()))
    (do ((i attribute-count (- i 1)))
        ((zerop i) (nreverse attributes))
      (let ((type-name (read-cstring connection +MAX_MESSAGE_LEN+))
            (type-id   (read-net-int connection 4))
            (type-len  (read-net-int connection 2))
            ;; this doesn't exist in the 6.3 protocol !!
            (type-modifier (read-net-int connection 4)))
        (declare (ignore type-modifier))
        (push (list type-name type-id type-len) attributes)))))

;; the bitmap is a string, which we interpret as a sequence of bytes
(defun bitmap-ref (bitmap ref)
  (multiple-value-bind (char-ref bit-ref)
      (floor ref 8)
    (logand #b10000000 (ash (aref bitmap char-ref) bit-ref))))

;; the server starts by sending a bitmap indicating which tuples are
;; NULL. "A bit map with one bit for each field in the row. The 1st
;; field corresponds to bit 7 (MSB) of the 1st byte, the 2nd field
;; corresponds to bit 6 of the 1st byte, the 8th field corresponds to
;; bit 0 (LSB) of the 1st byte, the 9th field corresponds to bit 7 of
;; the 2nd byte, and so on. Each bit is set if the value of the
;; corresponding field is not NULL. If the number of fields is not a
;; multiple of 8, the remainder of the last byte in the bit map is
;; wasted."
(defun read-tuple (connection attributes)
  (let* ((num-attributes (length attributes))
         (num-bytes (ceiling (/ num-attributes 8)))
         (bitmap (read-bytes connection num-bytes))
         (correction (if (pgcon-binary-p connection) 0 -4))
         (tuples '()))
    (do ((i 0 (+ i 1))
         (type-ids (mapcar #'second attributes) (cdr type-ids)))
        ((= i num-attributes) (nreverse tuples))
      (cond ((zerop (bitmap-ref bitmap i))
             (push nil tuples))
            (t
             (let* ((len (+ (read-net-int connection 4) correction))
                    (raw (read-chars connection (max 0 len)))
                    (parsed (parse raw (car type-ids))))
               (push parsed tuples)))))))

;; could signal a postgresql-notification condition
(defun handle-notice (connection)
  (push (read-cstring connection +MAX_MESSAGE_LEN+)
        (pgcon-notices connection)))


;; type coercion support ==============================================
;;
;; When returning data from a SELECT statement, PostgreSQL starts by
;; sending some metadata describing the attributes. This information
;; is read by `PG:READ-ATTRIBUTES', and consists of each attribute's
;; name (as a string), its size (in bytes), and its type (as an oid
;; which points to a row in the PostgreSQL system table pg_type). Each
;; row in pg_type includes the type's name (as a string).
;;
;; We are able to parse a certain number of the PostgreSQL types (for
;; example, numeric data is converted to a numeric Common Lisp type,
;; dates are converted to the CL date representation, booleans to
;; lisp booleans). However, there isn't a fixed mapping from a
;; type to its OID which is guaranteed to be stable across database
;; installations, so we need to build a table mapping OIDs to parser
;; functions.
;;
;; This is done by the procedure `PG:INITIALIZE-PARSERS', which is run
;; the first time a connection is initiated with the database from
;; this invocation of CL, and which issues a SELECT statement to
;; extract the required information from pg_type. This initialization
;; imposes a slight overhead on the first request, which you can avoid
;; by setting `*PG-DISABLE-TYPE-COERCION*' to non-nil if it bothers you.
;; ====================================================================

(defvar type-parsers
  `(("bool"      . ,'bool-parser)
    ("char"      . ,'text-parser)
    ("char2"     . ,'text-parser)
    ("char4"     . ,'text-parser)
    ("char8"     . ,'text-parser)
    ("char16"    . ,'text-parser)
    ("text"      . ,'text-parser)
    ("varchar"   . ,'text-parser)
    ("numeric"   . ,'integer-parser)
    ("int2"      . ,'integer-parser)
    ("int4"      . ,'integer-parser)
    ("int8"      . ,'integer-parser)
    ("oid"       . ,'integer-parser)
    ("float4"    . ,'float-parser)
    ("float8"    . ,'float-parser)
    ("money"     . ,'text-parser)       ; "$12.34"
    ("abstime"   . ,'timestamp-parser)
    ("date"      . ,'date-parser)
    ("timestamp" . ,'timestamp-parser)
    ("timestamptz" . ,'timestamp-parser)
    ("datetime"  . ,'timestamp-parser)
    ("time"      . ,'text-parser)     ; preparsed "15:32:45"
    ("timetz"    . ,'text-parser)
    ("reltime"   . ,'text-parser)     ; don't know how to parse these
    ("timespan"  . ,'text-parser)
    ("tinterval" . ,'text-parser)))


;; see `man pgbuiltin' for details on PostgreSQL builtin types
(defun integer-parser (str) (parse-integer str))

(defun float-parser (str) (read-from-string str))

;; FIXME this may need support for charset decoding
(defun text-parser (str) str)

(defun bool-parser (str)
  (cond ((string= "t" str) t)
        ((string= "f" str) nil)
        (t (error "Badly formed boolean from backend: ~s" str))))

;; format for abstime/timestamp etc with ISO output syntax is
;;
;;    "1999-01-02 05:11:23.0345645+01"
;;
;; which we convert to a CL universal time. Thanks to James Anderson
;; for a fix for timestamp format in PostgreSQL 7.3 (with or without
;; tz, with or without milliseconds).
(defun timestamp-parser (str)
  (let* ((year (parse-integer (subseq str 0 4)))
         (month (parse-integer (subseq str 5 7)))
         (day (parse-integer (subseq str 8 10)))
         (hours (parse-integer (subseq str 11 13)))
         (minutes (parse-integer (subseq str 14 16)))
         (seconds (parse-integer (subseq str 17 19)))
         (start-tz (if (eql #\+ (char str (- (length str) 3)))
                       (- (length str) 3)))
         (tz (when start-tz
               (parse-integer (subseq str start-tz))))
         (milliseconds (if (eql (char str 19) #\.)
                           (parse-integer (subseq str 20 start-tz)))))
    (declare (ignore tz milliseconds))
    (encode-universal-time seconds minutes hours day month year)))

;; format for abstime/timestamp etc with ISO output syntax is
;;;    "1999-01-02 00:00:00+01"
;; which we convert to a CL universal time
(defun isodate-parser (str)
  (let ((year    (parse-integer (subseq str 0 4)))
        (month   (parse-integer (subseq str 5 7)))
        (day     (parse-integer (subseq str 8 10)))
        (hours   (parse-integer (subseq str 11 13)))
        (minutes (parse-integer (subseq str 14 16)))
        (seconds (parse-integer (subseq str 17 19)))
        (tz      (parse-integer (subseq str 19 22))))
    (encode-universal-time seconds minutes hours day month year tz)))

;; format for date with ISO output syntax is
;;;    "1999-01-02"
;; which we convert to a CL universal time
(defun date-parser (str)
  (let ((year    (parse-integer (subseq str 0 4)))
	(month   (parse-integer (subseq str 5 7)))
	(day     (parse-integer (subseq str 8 10))))
    (encode-universal-time 0 0 0 day month year)))

(defun initialize-parsers (connection)
  (let* ((pgtypes (pg-exec connection "SELECT typname,oid FROM pg_type"))
         (tuples (pg-result pgtypes :tuples)))
    (setq *parsers* '())
    (map nil
     (lambda (tuple)
       (let* ((typname (first tuple))
              (oid (parse-integer (second tuple)))
              (type (assoc typname type-parsers :test #'string=)))
         (if (consp type)
             (push (cons oid (cdr type)) *parsers*))))
     tuples)))

(defun parse (str oid)
  (let ((parser (assoc oid *parsers* :test #'eq)))
    (if (consp parser)
        (funcall (cdr parser) str)
        str)))

;; large objects support ===============================================
;;
;; Sir Humphrey: Who is Large and to what does he object?
;;
;; Large objects are the PostgreSQL way of doing what most databases
;; call BLOBs (binary large objects). In addition to being able to
;; stream data to and from large objects, PostgreSQL's
;; object-relational capabilities allow the user to provide functions
;; which act on the objects.
;;
;; For example, the user can define a new type called "circle", and
;; define a C or Tcl function called `circumference' which will act on
;; circles. There is also an inheritance mechanism in PostgreSQL.
;;
;; The PostgreSQL large object interface is similar to the Unix file
;; system, with open, read, write, lseek etc.
;;
;; Implementation note: the network protocol for large objects changed
;; around version 6.5 to use network order for integers.
;; =====================================================================

(defvar *lo-initialized* nil)
(defvar *lo-functions* '())

(defun lo-init (connection)
  (let ((res (pg-exec connection
                    "SELECT proname, oid from pg_proc WHERE "
                    "proname = 'lo_open' OR "
                    "proname = 'lo_close' OR "
                    "proname = 'lo_creat' OR "
                    "proname = 'lo_unlink' OR "
                    "proname = 'lo_lseek' OR "
                    "proname = 'lo_tell' OR "
                    "proname = 'loread' OR "
                    "proname = 'lowrite'")))
    (setq *lo-functions* '())
    (dolist (tuple (pg-result res :tuples))
      (push (cons (car tuple) (cadr tuple)) *lo-functions*))
    (unless (= 8 (length *lo-functions*))
      (error "Couldn't find OIDs for all the large object functions"))
    (setq *lo-initialized* t)))

;; 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.
(defun fn (connection fn integer-result &rest args)
  (or *lo-initialized* (lo-init connection))
  (let ((fnid (cond ((integerp fn) fn)
                    ((not (stringp fn))
                     (error "Expecting a string or an integer: ~s" fn))
                    ((assoc fn *lo-functions* :test #'string=)
                     (cdr (assoc fn *lo-functions* :test #'string=)))
                    (t (error "Unknown builtin function ~s" fn)))))
    (send-int connection 70 1)          ; function call
    (send-int connection 0 1)
    (send-int connection fnid 4)
    (send-int connection (length args) 4)
    (dolist (arg args)
      (cond ((integerp arg)
             (send-int connection 4 4)
             (send-int connection arg 4))
            ((stringp arg)
             (send-int connection (length arg) 4)
             (send-string connection arg))
            (t (error 'protocol-error
                      :reason (format nil "Unknown fastpath type ~s" arg)))))
    (flush connection)
    (loop :with result = nil
          :with ready = nil
          :for b = (read-byte (pgcon-stream connection) nil :eof) :do
          (case b
            ;; FunctionResultResponse
            ((86)
             (let ((res (read-byte (pgcon-stream connection) nil :eof)))
               (cond ((= res 0)         ; empty result
                      (return-from fn nil))
                     ((= res 71)        ; nonempty result
                      (let ((len (read-net-int connection 4)))
                        (if integer-result
                            (setq result (read-net-int connection len))
                          (setq result (read-chars connection len)))))
                     (t (error 'protocol-error :reason "wierd FunctionResultResponse")))))

            ;; end of FunctionResult
            ((48) (return-from fn result))

            ((69) (error 'backend-error :reason (read-cstring connection 4096)))

            ;; NoticeResponse
            ((78)
             (setf (pgcon-pid connection) (read-net-int connection 4))
             (handle-notice connection))

            ;; ReadyForQuery
            ((90) (setq ready t))

            (t (error 'protocol-error
                      :reason (format nil "Unexpected byte ~s" b)))))))

;; returns an OID
(defun pglo-create (connection &optional (modestr "r"))
  (let* ((mode (cond ((integerp modestr) modestr)
                     ((string= "r" modestr) +INV_READ+)
                     ((string= "w" modestr) +INV_WRITE+)
                     ((string= "rw" modestr) (logior +INV_READ+ +INV_WRITE+))
                     (t (error "Bad mode ~s" modestr))))
         (oid (fn connection "lo_creat" t mode)))
    (unless (integerp oid)
      (error 'backend-error :reason "Didn't return an OID"))
    (when (zerop oid)
      (error 'backend-error :reason "Can't create large object"))
    oid))

;; args = modestring (default "r", or "w" or "rw")
;; returns a file descriptor for use in later lo-* procedures
(defun pglo-open (connection oid &optional (modestr "r"))
  (let* ((mode (cond ((integerp modestr) modestr)
                     ((string= "r" modestr) +INV_READ+)
                     ((string= "w" modestr) +INV_WRITE+)
                     ((string= "rw" modestr) (logior +INV_READ+ +INV_WRITE+))
                     (t (error "Bad mode ~s" modestr))))
         (fd (fn connection "lo_open" t oid mode)))
    (assert (integerp fd))
    fd))

(defun pglo-close (connection fd)
  (fn connection "lo_close" t fd))

(defun pglo-read (connection fd bytes)
  (fn connection "loread" nil fd bytes))

(defun pglo-write (connection fd buf)
  (fn connection "lowrite" t fd buf))

(defun pglo-lseek (connection fd offset whence)
  (fn connection "lo_lseek" t fd offset whence))

(defun pglo-tell (connection fd)
  (fn connection "lo_tell" t fd))

(defun pglo-unlink (connection oid)
  (fn connection "lo_unlink" t oid))

(defun pglo-import (connection filename)
  (let ((buf (make-string +LO_BUFSIZ+))
        (oid (pglo-create connection "rw")))
    (with-open-file (in filename :direction :input)
       (loop :with fdout = (pglo-open connection oid "w")
             :for bytes = (read-sequence buf in)
             :until (< bytes +LO_BUFSIZ+)
             :do (pglo-write connection fdout buf)
             :finally
             (pglo-write connection fdout (subseq buf 0 bytes))
             (pglo-close connection fdout)))
    oid))

(defun pglo-export (connection oid filename)
  (with-open-file (out filename :direction :output)
     (loop :with fdin = (pglo-open connection oid "r")
           :for str = (pglo-read connection fdin +LO_BUFSIZ+)
           :until (zerop (length str))
           :do (write-sequence str out)
           :finally (pglo-close connection fdin))))


;; DBMS metainformation ================================================
;;
;; Metainformation such as the list of databases present in the
;; database management system, list of tables, attributes per table.
;; This information is not available directly, but can be deduced by
;; querying the system tables.
;;
;; Based on the queries issued by psql in response to user commands
;; `\d' and `\d tablename'; see file pgsql/src/bin/psql/psql.c
;; =====================================================================
(defun pg-databases (conn)
  "Return a list of the databases available at this site."
  (let ((res (pg-exec conn "SELECT datname FROM pg_database")))
    (reduce #'append (pg-result res :tuples))))

(defun pg-tables (conn)
  "Return a list of the tables present in this database."
  (let ((res (pg-exec conn "SELECT relname FROM pg_class, pg_user WHERE "
                      "(relkind = 'r' OR relkind = 'i' OR relkind = 'S') AND "
                      "relname !~ '^pg_' AND usesysid = relowner ORDER BY relname")))
    (reduce #'append (pg-result res :tuples))))

(defun pg-columns (conn table)
  "Return a list of the columns present in TABLE."
  (let ((res (pg-exec conn (format nil "SELECT * FROM ~s WHERE 0 = 1" table))))
    (mapcar #'first (pg-result res :attributes))))

(defun pg-backend-version (conn)
  "Return a string identifying the version and operating environment of the backend."
  (let ((res (pg-exec conn "SELECT version()")))
    (first (pg-result res :tuple 0))))


;; support routines ===================================================

;; read an integer in network byte order
(defun read-net-int (connection bytes)
  (do ((i bytes (- i 1))
       (stream (pgcon-stream connection))
       (accum 0))
      ((zerop i) accum)
    (setq accum (+ (* 256 accum) (read-byte stream)))))

(defun read-int (connection bytes)
  (do ((i bytes (- i 1))
       (stream (pgcon-stream connection))
       (multiplier 1 (* multiplier 256))
       (accum 0))
      ((zerop i) accum)
    (incf accum (* multiplier (read-byte stream)))))

#-cmu
(defun read-bytes (connection howmany)
  (let ((v (make-array howmany :element-type '(unsigned-byte 8)))
        (s (pgcon-stream connection)))
    (read-sequence v s)
    v))

;; There is a bug in CMUCL's implementation of READ-SEQUENCE on
;; network streams, which can return without reading to the end of the
;; sequence when it has to wait for data. It confuses the end-of-file
;; condition with no-more-data-currently-available. This workaround is
;; thanks to Wayne Iba.
#+cmu
(defun read-bytes (connection howmany)
  (let ((v (make-array howmany :element-type '(unsigned-byte 8)))
        (s (pgcon-stream connection)))
    (do ((continue-at (read-sequence v s :start 0 :end howmany)
		      (read-sequence v s :start continue-at :end howmany)))
	((= continue-at howmany))
      )
    v))

;; bug in WRITE-SEQUENCE in CMUCL
#+cmu
(defun write-sequence (seq stream &key start end)
  (declare (ignore start end))
  (loop :for element :across seq
        :do (write-byte element stream)))


(defun read-chars (connection howmany)
  (let ((bytes (read-bytes connection howmany))
        (str (make-string howmany)))
    (dotimes (i howmany)
      (setf (aref str i) (code-char (aref bytes i))))
    str))

(defun read-cstring (connection maxbytes)
  "Read a null-terminated string from CONNECTION."
  (let ((stream (pgcon-stream connection))
        (chars nil))
    (do ((b (read-byte stream nil nil) (read-byte stream nil nil))
         (i 0 (+ i 1)))
        ((or (= i maxbytes)             ; reached allowed length
             (null b)                   ; eof
             (zerop b))                 ; end of string
         (concatenate 'string (nreverse chars)))
      (push (code-char b) chars))))

;; highest order bits first
(defun send-int (connection int bytes)
  (let ((v (make-array bytes :element-type '(unsigned-byte 8)))
        (stream (pgcon-stream connection)))
    (do ((i (- bytes 1) (- i 1)))
        ((< i 0))
      (setf (aref v i) (rem int 256))
      (setq int (floor int 256)))
    (write-sequence v stream)))

(defun send-string (connection str &optional pad-to)
  (let* ((stream (pgcon-stream connection))
         (len (length str))
         (v (make-array len :element-type '(unsigned-byte 8))))
    ;; convert the string to a vector of bytes
    (dotimes (i len)
      (setf (aref v i) (char-code (aref str i))))
    (write-sequence v stream)
    ;; pad if necessary
    (when pad-to
      (write-sequence (make-array (- pad-to len)
                                  :initial-element 0
                                  :element-type '(unsigned-byte 8))
                      stream))))

(declaim (inline flush))
(defun flush (connection)
  (force-output (pgcon-stream connection)))

;; ;; double any ' characters. What else needs quoting?
;; (defun (quote connection obj)
;;   (cond ((number? obj) (number->string obj))
;;         ((char? obj) (string #\' obj #\'))
;;         ((eq? t obj) "'t'")
;;         ((eq? nil obj) "'f'")
;;         ((string? obj) (string-append "'" (string-replace "'" "''" obj) "'"))
;;         (t (error "Don't know how to quote that" obj))))
;;
;; ;; replace all occurrences of OLD by NEW in the string STR
;; (defun (string-replace old new str)
;;   ;; I'm lazy
;;   (regexp-substitute/global nil (regexp-quote old) str 'pre new 'post))


#+(and cmu glibc2)
(eval-when (:compile-toplevel :load-toplevel)
  (format t ";; Loading libcrypt~%")
  ;; (ext:load-foreign "/lib/libcrypt.so.1")
  (sys::load-object-file "/usr/lib/libcrypt.so")
  ;; (ext:load-foreign "/usr/lib/libcrypt.a")
  )

#+(and cmu glibc2)
(defun crypt (key salt)
  (declare (type string key salt))
  (alien:alien-funcall
   (alien:extern-alien "crypt"
          (function c-call:c-string c-call:c-string c-call:c-string))
   key salt))

#-(and cmu glibc2)
(defun crypt (key salt)
  (declare (ignore salt))
  key)


;; this is a little fiddly, because CLISP can be built without support
;; for the Linux package
;; #+CLISP
;; (defun crypt (key salt)
;;   (linux::crypt key salt))



#+cmu
(defun socket-connect (port host)
  (handler-case
   (let ((fd (ext:connect-to-inet-socket host port)))
     (sys:make-fd-stream fd :input t :output t
                         :element-type '(unsigned-byte 8)))
   (error (e)
      (declare (ignore e))
      (error 'connection-failure :host host :port port))))

#+clisp
(defun socket-connect (port host)
  (handler-case
   (#+lisp=cl socket:socket-connect
    #-lisp=cl lisp:socket-connect
    port host :element-type '(unsigned-byte 8))
   (error (e)
      (declare (ignore e))
      (error 'connection-failure :host host :port port))))


#+db-sockets
(defun socket-connect (port host)
  (handler-case
   (let ((s (sockets:make-inet-socket :stream :tcp))
         (num (car (sockets:host-ent-addresses
                    (sockets:get-host-by-name host)))))
     (sockets:socket-connect s num port)
     (sockets:socket-make-stream s :element-type '(unsigned-byte 8)
                                 :input t :output t :buffering :none))
   (error (e)
      (declare (ignore e))
      (error 'connection-failure :host host :port port))))

#+allegro
(defun socket-connect (port host)
  (handler-case
   (socket:make-socket :remote-host host
                       :remote-port port
                       :format :binary)
   (error (e)
      (declare (ignore e))
      (signal 'connection-failure :host host :port port))))

;; Lispworks 4.2 doesn't seem to implement WRITE-SEQUENCE on binary
;; streams
#+lispworks
(defun socket-connect (port host)
  (comm:open-tcp-stream host port
			:element-type '(unsigned-byte 8)
			:direction :io))

;; this doesn't work, since the Corman sockets module doesn't support
;; binary I/O on socket streams.
#+cormanlisp
(defun socket-connect (port host)
  (handler-case
   (sockets:sockets-start)
   (let ((sock (make-client-socket :host host :port port)))
     (sockets:make-socket-stream sock))
   (error (e)
      (declare (ignore e))
      (error 'connection-failure :host host :port port))))

#+openmcl
(defun socket-connect (port host)
  (let ((sock (make-socket :type :stream
                           :connect :active
                           :format :binary
                           :remote-host host
                           :remote-port port)))
    sock))

;; from John DeSoi
#+(and mcl (not openmcl))
(defun socket-connect (port host)
  (ccl::open-tcp-stream host port :element-type '(unsigned-byte 8)))

;; There is a bug in MCL (4.3.1 tested) where read-sequence and
;; write-sequence fail with binary tcp streams. These two methods
;; provide a work-around.
#+(and mcl (not openmcl))
(defmethod ccl:stream-write-sequence ((s ccl::opentransport-binary-tcp-stream)
                                      (sequence ccl::simple-unsigned-byte-vector)
                                      &key (start 0) end)
  (ccl::stream-write-vector s sequence start (or end (length sequence)))
  s)

#+(and mcl (not openmcl))
(defmethod ccl:stream-read-sequence ((s ccl::opentransport-binary-tcp-stream)
                                     (sequence ccl::simple-unsigned-byte-vector)
                                     &key (start 0) (end (length sequence)))
  (ccl::io-buffer-read-bytes-to-vector (ccl::stream-io-buffer s)
                                       sequence (- end start) start)
  end)


;; == testing ==============================================================
(defmacro with-test-connection ((conn) &body body)
  `(call-with-test-connection
     (lambda (,conn) ,@body)))

(defun call-with-test-connection (function)
  (with-pg-connection (conn "test" "emarsden" :host "melbourne" :port 5433)
    (funcall function conn)))

(defun test ()
  (with-test-connection (conn)
   (format t "Running pg.lisp tests against backend ~a~%" (pg-backend-version conn))
   (let ((r2 (pg-exec conn "CREATE TABLE pgltest (a int, b float, c money)"))
         (r3 (pg-exec conn "INSERT INTO pgltest VALUES (3, -1234.5e67, '$123.45')"))
         (r4 (pg-exec conn "DROP TABLE pgltest")))
     (format t "~%==============================================~%")
     (format t "status of CREATE is ~s~%" (pg-result r2 :status))
     (format t "status of INSERT is ~s~%" (pg-result r3 :status))
     (format t "oid of INSERT is ~s~%" (pg-result r3 :oid))
     (format t "status of DROP is ~s~%" (pg-result r4 :status))
     (format t "==============================================~%")))
  (test-insert)
  (test-date)
  (test-lo)
  (test-lo-read))

(defun test-insert ()
  (with-test-connection (conn)
    (let ((res nil)
          (count 0)
          (created t))
      (unwind-protect
           (progn
             (pg-exec conn "CREATE TABLE count_test(key int, val int)")
             (loop :for i :from 1 :to 100
                   :for sql = (format nil "INSERT INTO count_test VALUES(~s, ~s)"
                                      i (* i i))
                   :do (pg-exec conn sql))
             (setq created t)
             (setq res (pg-exec conn "SELECT count(val) FROM count_test"))
             (assert (eql 100 (first (pg-result res :tuple 0))))
             (setq res (pg-exec conn "SELECT sum(key) FROM count_test"))
             (assert (eql 5050 (first (pg-result res :tuple 0))))
             ;; this iterator does the equivalent of the sum(key) SQL statement
             ;; above, but on the client side.
             (pg-for-each conn "SELECT key FROM count_test"
                          (lambda (tuple) (incf count (first tuple))))
             (assert (= 5050 count)))
        (when created
          (pg-exec conn "DROP TABLE count_test"))))))

(defun test-date (&aux created)
  (with-test-connection (conn)
    (unwind-protect
         (progn
           (pg-exec conn "CREATE TABLE pgltest (a timestamp, b abstime, c time, d date)")
           (pg-exec conn "INSERT INTO pgltest VALUES "
                    "(current_timestamp, 'now', 'now', 'now')")
           (setq created t)
           (let* ((res (pg-exec conn "SELECT * FROM pgltest"))
                  (parsed (first (pg-result res :tuples))))
             (format t "attributes ~a~%" (pg-result res :attributes))
             (format t "Timestamp = ~s~%abstime = ~s~%time = ~s~%date = ~s~%"
                     (first parsed)
                     (second parsed)
                     (third parsed)
                     (fourth parsed))))
      (when created
        (pg-exec conn "DROP TABLE pgltest")))))

(defun test-lo ()
  (with-test-connection (conn)
   (with-pg-transaction conn
    (let* ((oid (pglo-create conn))
           (fd (pglo-open conn oid)))
      (sleep 1)
      (pglo-tell conn fd)
      (sleep 1)
      (pglo-unlink conn oid)))))

;; test of large-object interface
(defun test-lo-read ()
  (with-test-connection (conn)
   (with-pg-transaction conn
    (let* ((oid (pglo-create conn "rw"))
           (fd (pglo-open conn oid "rw")))
      (pglo-write conn fd "Hi there mate")
      (pglo-lseek conn fd 3 0)           ; SEEK_SET = 0
      (assert (= 3 (pglo-tell conn fd)))
      ;; this should print "there mate"
      (format *debug-io* "Read ~s from lo~%" (pglo-read conn fd 10))
      (pglo-close conn fd)
      (pglo-unlink conn oid)))))

#+cmu
(defun test-lo-import ()
  (with-test-connection (conn)
   (with-pg-transaction conn
    (let ((oid (pglo-import conn "/etc/group")))
      (pglo-export conn oid "/tmp/group")
      (cond ((zerop
              (ext:process-exit-code
               (ext:run-program "diff" (list "/tmp/group" "/etc/group"))))
             (format *debug-io* "pglo-import test succeeded~%")
             (unix:unix-unlink "/tmp/group"))
            (t
             (format *debug-io* "pglo-import test failed: check differences
between files /etc/group and /tmp/group")))
      (pglo-unlink conn oid)))))

(defun test-simple ()
  (let ((*pg-disable-type-coercion* t))
    (with-test-connection (conn)
     (format t "backend ~a~%" (pg-backend-version conn)))))

;; EOF