1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
5 ;;;; Name: postgresql-sql.sql
6 ;;;; Purpose: High-level PostgreSQL interface using UFFI
7 ;;;; Programmers: Kevin M. Rosenberg based on
8 ;;;; Original code by Pierre R. Mai
9 ;;;; Date Started: Feb 2002
11 ;;;; $Id: postgresql-sql.cl,v 1.10 2002/03/27 08:09:25 kevin Exp $
13 ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg
14 ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai
16 ;;;; CLSQL users are granted the rights to distribute and use this software
17 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
18 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
19 ;;;; *************************************************************************
21 (declaim (optimize (debug 3) (speed 3) (safety 1) (compilation-speed 0)))
24 (defpackage :clsql-postgresql
25 (:use :common-lisp :clsql-sys :postgresql :clsql-uffi)
26 (:export #:postgresql-database)
27 (:documentation "This is the CLSQL interface to PostgreSQL."))
29 (in-package :clsql-postgresql)
31 ;;; Field conversion functions
33 (defun canonicalize-types (types num-fields res-ptr)
36 (canonicalize-type-list types num-fields))
38 (let ((new-types '()))
39 (dotimes (i num-fields)
41 (let* ((type (PQftype res-ptr i)))
50 ((#.pgsql-ftype#float4
56 (nreverse new-types)))
61 (defun tidy-error-message (message)
62 (unless (stringp message)
63 (setq message (uffi:convert-from-foreign-string message)))
64 (let ((message (string-right-trim '(#\Return #\Newline) message)))
66 ((< (length message) (length "ERROR:"))
68 ((string= message "ERROR:" :end1 6)
69 (string-left-trim '(#\Space) (subseq message 6)))
73 (defmethod database-initialize-database-type ((database-type
77 (uffi:def-type pgsql-conn-def pgsql-conn)
78 (uffi:def-type pgsql-result-def pgsql-result)
81 (defclass postgresql-database (database)
82 ((conn-ptr :accessor database-conn-ptr :initarg :conn-ptr
83 :type pgsql-conn-def)))
85 (defmethod database-name-from-spec (connection-spec (database-type
87 (check-connection-spec connection-spec database-type
88 (host db user password &optional port options tty))
89 (destructuring-bind (host db user password &optional port options tty)
91 (declare (ignore password options tty))
92 (concatenate 'string host (if port ":") (if port port) "/" db "/" user)))
95 (defmethod database-connect (connection-spec (database-type (eql :postgresql)))
96 (check-connection-spec connection-spec database-type
97 (host db user password &optional port options tty))
98 (destructuring-bind (host db user password &optional port options tty)
100 (uffi:with-cstrings ((host-native host)
102 (password-native password)
105 (options-native options)
107 (let ((connection (PQsetdbLogin host-native port-native
108 options-native tty-native
109 db-native user-native
111 (declare (type pgsql-conn-def connection))
112 (when (not (eq (PQstatus connection)
113 pgsql-conn-status-type#connection-ok))
114 (error 'clsql-connect-error
115 :database-type database-type
116 :connection-spec connection-spec
117 :errno (PQstatus connection)
118 :error (tidy-error-message
119 (PQerrorMessage connection))))
120 (make-instance 'postgresql-database
121 :name (database-name-from-spec connection-spec
123 :conn-ptr connection)))))
126 (defmethod database-disconnect ((database postgresql-database))
127 (PQfinish (database-conn-ptr database))
128 (setf (database-conn-ptr database) nil)
131 (defmethod database-query (query-expression (database postgresql-database) types)
132 (let ((conn-ptr (database-conn-ptr database)))
133 (declare (type pgsql-conn-def conn-ptr))
134 (uffi:with-cstring (query-native query-expression)
135 (let ((result (PQexec conn-ptr query-native)))
136 (when (uffi:null-pointer-p result)
137 (error 'clsql-sql-error
139 :expression query-expression
141 :error (tidy-error-message (PQerrorMessage conn-ptr))))
143 (case (PQresultStatus result)
144 (#.pgsql-exec-status-type#empty-query
146 (#.pgsql-exec-status-type#tuples-ok
147 (let ((num-fields (PQnfields result)))
149 (canonicalize-types types num-fields
151 (loop for tuple-index from 0 below (PQntuples result)
153 (loop for i from 0 below num-fields
155 (if (zerop (PQgetisnull result tuple-index i))
157 (PQgetvalue result tuple-index i)
161 (error 'clsql-sql-error
163 :expression query-expression
164 :errno (PQresultStatus result)
165 :error (tidy-error-message
166 (PQresultErrorMessage result)))))
167 (PQclear result))))))
169 (defmethod database-execute-command (sql-expression
170 (database postgresql-database))
171 (let ((conn-ptr (database-conn-ptr database)))
172 (declare (type pgsql-conn-def conn-ptr))
173 (uffi:with-cstring (sql-native sql-expression)
174 (let ((result (PQexec conn-ptr sql-native)))
175 (when (uffi:null-pointer-p result)
176 (error 'clsql-sql-error
178 :expression sql-expression
180 :error (tidy-error-message (PQerrorMessage conn-ptr))))
182 (case (PQresultStatus result)
183 (#.pgsql-exec-status-type#command-ok
185 ((#.pgsql-exec-status-type#empty-query
186 #.pgsql-exec-status-type#tuples-ok)
187 (warn "Strange result...")
190 (error 'clsql-sql-error
192 :expression sql-expression
193 :errno (PQresultStatus result)
194 :error (tidy-error-message
195 (PQresultErrorMessage result)))))
196 (PQclear result))))))
198 (defstruct postgresql-result-set
199 (res-ptr (uffi:make-null-pointer 'pgsql-result)
200 :type pgsql-result-def)
202 (num-tuples 0 :type integer)
203 (num-fields 0 :type integer)
204 (tuple-index 0 :type integer))
206 (defmethod database-query-result-set (query-expression (database postgresql-database)
208 (let ((conn-ptr (database-conn-ptr database)))
209 (declare (type pgsql-conn-def conn-ptr))
210 (uffi:with-cstring (query-native query-expression)
211 (let ((result (PQexec conn-ptr query-native)))
212 (when (uffi:null-pointer-p result)
213 (error 'clsql-sql-error
215 :expression query-expression
217 :error (tidy-error-message (PQerrorMessage conn-ptr))))
218 (case (PQresultStatus result)
219 ((#.pgsql-exec-status-type#empty-query
220 #.pgsql-exec-status-type#tuples-ok)
221 (let ((result-set (make-postgresql-result-set
223 :num-fields (PQnfields result)
224 :num-tuples (PQntuples result)
225 :types (canonicalize-types
234 (PQnfields result)))))
237 (error 'clsql-sql-error
239 :expression query-expression
240 :errno (PQresultStatus result)
241 :error (tidy-error-message
242 (PQresultErrorMessage result)))
243 (PQclear result))))))))
245 (defmethod database-dump-result-set (result-set (database postgresql-database))
246 (let ((res-ptr (postgresql-result-set-res-ptr result-set)))
247 (declare (type pgsql-result-def res-ptr))
251 (defmethod database-store-next-row (result-set (database postgresql-database)
253 (let ((result (postgresql-result-set-res-ptr result-set))
254 (types (postgresql-result-set-types result-set)))
255 (declare (type pgsql-result-def result))
256 (if (>= (postgresql-result-set-tuple-index result-set)
257 (postgresql-result-set-num-tuples result-set))
259 (loop with tuple-index = (postgresql-result-set-tuple-index result-set)
260 for i from 0 below (postgresql-result-set-num-fields result-set)
264 (if (zerop (PQgetisnull result tuple-index i))
266 (PQgetvalue result tuple-index i)
270 (incf (postgresql-result-set-tuple-index result-set))