;;;; Original code by Pierre R. Mai
;;;; Date Started: Feb 2002
;;;;
-;;;; $Id: postgresql-sql.cl,v 1.2 2002/03/23 17:07:40 kevin Exp $
+;;;; $Id: postgresql-sql.cl,v 1.16 2002/05/10 08:05:48 marc.battyani Exp $
;;;;
;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg
;;;; and Copyright (c) 1999-2001 by Pierre R. Mai
(in-package :cl-user)
(defpackage :clsql-postgresql
- (:use :common-lisp :clsql-sys :postgresql)
+ (:use :common-lisp :clsql-sys :postgresql :clsql-uffi)
(:export #:postgresql-database)
(:documentation "This is the CLSQL interface to PostgreSQL."))
(in-package :clsql-postgresql)
+;;; Field conversion functions
+
+(defun make-type-list-for-auto (num-fields res-ptr)
+ (let ((new-types '()))
+ (dotimes (i num-fields)
+ (declare (fixnum i))
+ (let* ((type (PQftype res-ptr i)))
+ (push
+ (case type
+ ((#.pgsql-ftype#bytea
+ #.pgsql-ftype#int2
+ #.pgsql-ftype#int4)
+ :int32)
+ (#.pgsql-ftype#int8
+ :int64)
+ ((#.pgsql-ftype#float4
+ #.pgsql-ftype#float8)
+ :double)
+ (otherwise
+ t))
+ new-types)))
+ (nreverse new-types)))
+
+(defun canonicalize-types (types num-fields res-ptr)
+ (if (null types)
+ nil
+ (let ((auto-list (make-type-list-for-auto num-fields res-ptr)))
+ (cond
+ ((listp types)
+ (canonicalize-type-list types auto-list))
+ ((eq types :auto)
+ auto-list)
+ (t
+ nil)))))
(defun tidy-error-message (message)
(unless (stringp message)
((conn-ptr :accessor database-conn-ptr :initarg :conn-ptr
:type pgsql-conn-def)))
+(defmethod database-type ((database postgresql-database))
+ :postgresql)
+
(defmethod database-name-from-spec (connection-spec (database-type
(eql :postgresql)))
(check-connection-spec connection-spec database-type
(make-instance 'postgresql-database
:name (database-name-from-spec connection-spec
database-type)
+ :connection-spec connection-spec
:conn-ptr connection)))))
(setf (database-conn-ptr database) nil)
t)
-(defmethod database-query (query-expression (database postgresql-database))
+(defmethod database-query (query-expression (database postgresql-database) types)
(let ((conn-ptr (database-conn-ptr database)))
(declare (type pgsql-conn-def conn-ptr))
(uffi:with-cstring (query-native query-expression)
(#.pgsql-exec-status-type#empty-query
nil)
(#.pgsql-exec-status-type#tuples-ok
- (loop for tuple-index from 0 below (PQntuples result)
- collect
- (loop for i from 0 below (PQnfields result)
- collect
- (if (zerop (PQgetisnull result tuple-index i))
- (uffi:convert-from-cstring
- (PQgetvalue result tuple-index i))
- nil))))
+ (let ((num-fields (PQnfields result)))
+ (setq types
+ (canonicalize-types types num-fields
+ result))
+ (loop for tuple-index from 0 below (PQntuples result)
+ collect
+ (loop for i from 0 below num-fields
+ collect
+ (if (zerop (PQgetisnull result tuple-index i))
+ (convert-raw-field
+ (PQgetvalue result tuple-index i)
+ types i)
+ nil)))))
(t
(error 'clsql-sql-error
:database database
(defstruct postgresql-result-set
(res-ptr (uffi:make-null-pointer 'pgsql-result)
:type pgsql-result-def)
- (num-tuples 0)
- (num-fields 0)
- (tuple-index 0))
+ (types nil)
+ (num-tuples 0 :type integer)
+ (num-fields 0 :type integer)
+ (tuple-index 0 :type integer))
-(defmethod database-query-result-set (query-expression
- (database postgresql-database)
- &optional full-set)
+(defmethod database-query-result-set (query-expression (database postgresql-database)
+ &key full-set types)
(let ((conn-ptr (database-conn-ptr database)))
(declare (type pgsql-conn-def conn-ptr))
(uffi:with-cstring (query-native query-expression)
(case (PQresultStatus result)
((#.pgsql-exec-status-type#empty-query
#.pgsql-exec-status-type#tuples-ok)
- (if full-set
- (values (make-postgresql-result-set
+ (let ((result-set (make-postgresql-result-set
:res-ptr result
:num-fields (PQnfields result)
- :num-tuples (PQntuples result))
- (PQnfields result)
- (PQntuples result))
- (values (make-postgresql-result-set
- :res-ptr result
- :num-fields (PQnfields result)
- :num-tuples (PQntuples result))
- (PQnfields result))))
- (t
- (unwind-protect
+ :num-tuples (PQntuples result)
+ :types (canonicalize-types
+ types
+ (PQnfields result)
+ result))))
+ (if full-set
+ (values result-set
+ (PQnfields result)
+ (PQntuples result))
+ (values result-set
+ (PQnfields result)))))
+ (t
+ (unwind-protect
(error 'clsql-sql-error
:database database
:expression query-expression
(defmethod database-store-next-row (result-set (database postgresql-database)
list)
- (let ((result (postgresql-result-set-res-ptr result-set)))
+ (let ((result (postgresql-result-set-res-ptr result-set))
+ (types (postgresql-result-set-types result-set)))
(declare (type pgsql-result-def result))
(if (>= (postgresql-result-set-tuple-index result-set)
(postgresql-result-set-num-tuples result-set))
do
(setf (car rest)
(if (zerop (PQgetisnull result tuple-index i))
- (uffi:convert-from-cstring
- (PQgetvalue result tuple-index i))
+ (convert-raw-field
+ (PQgetvalue result tuple-index i)
+ types i)
nil))
finally
(incf (postgresql-result-set-tuple-index result-set))
(return list)))))
+
+;;; Large objects support (Marc B)
+
+(defmethod database-create-large-object ((database postgresql-database))
+ (lo-create (database-conn-ptr database)
+ (logior postgresql::+INV_WRITE+ postgresql::+INV_READ+)))
+
+(defmethod database-write-large-object (object-id (data string) (database postgresql-database))
+ (let ((ptr (database-conn-ptr database))
+ (length (length data))
+ (result nil)
+ (fd nil))
+ (with-transaction (:database database)
+ (unwind-protect
+ (progn
+ (setf fd (lo-open ptr object-id postgresql::+INV_WRITE+))
+ (when (>= fd 0)
+ (when (= (lo-write ptr fd data length) length)
+ (setf result t))))
+ (progn
+ (when (and fd (>= fd 0))
+ (lo-close ptr fd))
+ )))
+ result))
+
+;; (MB) the begin/commit/rollback stuff will be removed when with-transaction wil be implemented
+(defmethod database-read-large-object (object-id (database postgresql-database))
+ (let ((ptr (database-conn-ptr database))
+ (buffer nil)
+ (result nil)
+ (length 0)
+ (fd nil))
+ (unwind-protect
+ (progn
+ (database-execute-command "begin" database)
+ (setf fd (lo-open ptr object-id postgresql::+INV_READ+))
+ (when (>= fd 0)
+ (setf length (lo-lseek ptr fd 0 2))
+ (lo-lseek ptr fd 0 0)
+ (when (> length 0)
+ (setf buffer (uffi:allocate-foreign-string
+ length :unsigned t))
+ (when (= (lo-read ptr fd buffer length) length)
+ (setf result (uffi:convert-from-foreign-string
+ buffer :length length :null-terminated-p nil))))))
+ (progn
+ (when buffer (uffi:free-foreign-object buffer))
+ (when (and fd (>= fd 0)) (lo-close ptr fd))
+ (database-execute-command (if result "commit" "rollback") database)))
+ result))
+
+(defmethod database-delete-large-object (object-id (database postgresql-database))
+ (lo-unlink (database-conn-ptr database) object-id))