File: pg.lisp

package info (click to toggle)
cl-pg 1:20061216-4
  • links: PTS
  • area: main
  • in suites: lenny
  • size: 276 kB
  • ctags: 233
  • sloc: lisp: 3,125; makefile: 43
file content (275 lines) | stat: -rw-r--r-- 11,031 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
;;; 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