1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
6 ;;;; Generic ODBC layer, used by db-odbc and db-aodbc backends
8 ;;;; This file is part of CLSQL.
10 ;;;; CLSQL users are granted the rights to distribute and use this software
11 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
12 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
13 ;;;; *************************************************************************
15 (in-package #:clsql-sys)
17 (defclass generic-odbc-database (database)
18 ((dbi-package :initarg :dbi-package :reader dbi-package)
19 (odbc-conn :initarg :odbc-conn :initform nil :accessor odbc-conn)
20 (disconnect-fn :reader disconnect-fn)
21 (sql-fn :reader sql-fn)
22 (close-query-fn :reader close-query-fn)
23 (fetch-row :reader fetch-row-fn)
24 (list-all-database-tables-fn :reader list-all-database-tables-fn)
25 (list-all-table-columns-fn :reader list-all-table-columns-fn))
26 (:documentation "Encapsulate same behavior across odbc and aodbc backends."))
28 (defmethod initialize-instance :after ((db generic-odbc-database)
30 (declare (ignore all-keys))
31 (unless (slot-boundp db 'dbi-package)
32 (error "dbi-package not specified."))
33 (let ((pkg (slot-value db 'dbi-package)))
35 (error "dbi-package is nil."))
36 (setf (slot-value db 'disconnect-fn)
37 (intern (symbol-name '#:disconnect) pkg)
38 (slot-value db 'sql-fn)
39 (intern (symbol-name '#:sql) pkg)
40 (slot-value db 'close-query-fn)
41 (intern (symbol-name '#:close-query) pkg)
42 (slot-value db 'fetch-row)
43 (intern (symbol-name '#:fetch-row) pkg)
44 (slot-value db 'list-all-database-tables-fn)
45 (intern (symbol-name '#:list-all-database-tables) pkg)
46 (slot-value db 'list-all-table-columns-fn)
47 (intern (symbol-name '#:list-all-table-columns) pkg))))
51 (defmethod read-sql-value (val (type (eql 'boolean))
52 (database generic-odbc-database)
53 (db-type (eql :postgresql)))
54 (if (string= "0" val) nil t))
56 (defmethod read-sql-value (val (type (eql 'generalized-boolean))
57 (database generic-odbc-database)
58 (db-type (eql :postgresql)))
59 (if (string= "0" val) nil t))
61 (defmethod read-sql-value (val (type (eql 'boolean)) database
62 (db-type (eql :mssql)))
63 (declare (ignore database))
65 (string (if (string= "0" val) nil t))
66 (integer (if (zerop val) nil t))))
68 (defmethod read-sql-value (val (type (eql 'generalized-boolean)) database
69 (db-type (eql :mssql)))
70 (declare (ignore database))
72 (string (if (string= "0" val) nil t))
73 (integer (if (zerop val) nil t))))
77 (defmethod database-get-type-specifier ((type (eql 'wall-time)) args database
78 (db-type (eql :mssql)))
79 (declare (ignore args database))
82 (defmethod database-get-type-specifier ((type (eql 'boolean)) args database
83 (db-type (eql :mssql)))
84 (declare (ignore args database))
87 (defmethod database-get-type-specifier ((type (eql 'generalized-boolean)) args database
88 (db-type (eql :mssql)))
89 (declare (ignore args database))
92 ;;; Generation of SQL strings from lisp expressions
94 (defmethod database-output-sql ((tee (eql t)) (database generic-odbc-database))
95 (case (database-underlying-type database)
99 (defmethod database-output-sql-as-type ((type (eql 'boolean)) val database
100 (db-type (eql :mssql)))
101 (declare (ignore database))
104 (defmethod database-output-sql-as-type ((type (eql 'generalized-boolean)) val database
105 (db-type (eql :mssql)))
106 (declare (ignore database))
109 ;;; Database backend capabilities
111 (defmethod db-type-use-fully-qualified-column-on-drop-index? ((db-type (eql :mssql)))
114 (defmethod db-type-has-boolean-where? ((db-type (eql :mssql)))
117 (defmethod db-type-has-intersect? ((db-type (eql :mssql)))
120 (defmethod db-type-has-except? ((db-type (eql :mssql)))
125 (defmethod database-disconnect ((database generic-odbc-database))
126 (funcall (disconnect-fn database) (odbc-conn database))
127 (setf (odbc-conn database) nil)
130 (defmethod database-query (query-expression (database generic-odbc-database)
131 result-types field-names)
133 (funcall (sql-fn database)
134 query-expression :db (odbc-conn database)
135 :result-types result-types
136 :column-names field-names)
139 (error 'sql-database-data-error
141 :expression query-expression
142 :message "Query failed"))))
145 (defmethod database-execute-command (sql-expression (database generic-odbc-database))
147 (funcall (sql-fn database)
148 sql-expression :db (odbc-conn database))
154 (error 'sql-database-data-error
156 :expression sql-expression
157 :message "Execute command failed"))))
160 (defstruct odbc-result-set
163 (full-set nil :type boolean))
168 (defmethod database-query-result-set ((query-expression string)
169 (database generic-odbc-database)
170 &key full-set result-types)
172 (multiple-value-bind (query column-names)
173 (funcall (sql-fn database)
175 :db (odbc-conn database)
179 :result-types result-types)
181 (make-odbc-result-set :query query :full-set full-set
183 (length column-names)
184 nil ;; not able to return number of rows with odbc
187 (error 'sql-database-data-error
189 :expression query-expression
190 :message "Query result set failed"))))
192 (defmethod database-dump-result-set (result-set (database generic-odbc-database))
193 (funcall (close-query-fn database) (odbc-result-set-query result-set))
196 (defmethod database-store-next-row (result-set
197 (database generic-odbc-database)
199 (let ((row (funcall (fetch-row-fn database)
200 (odbc-result-set-query result-set) nil 'eof)))
204 (loop for elem in row
207 (setf (car rest) elem))
210 (defmethod database-list-tables ((database generic-odbc-database)
212 (declare (ignore owner))
213 (multiple-value-bind (rows col-names)
214 (funcall (list-all-database-tables-fn database) :db (odbc-conn database))
215 (declare (ignore col-names))
216 ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager
217 ;; TABLE_NAME in third column, TABLE_TYPE in fourth column
218 (loop for row in rows
219 when (and (not (string-equal "information_schema" (nth 1 row)))
220 (string-equal "TABLE" (nth 3 row))
221 (not (and (eq :mssql (database-underlying-type database))
222 (string-equal "dtproperties" (nth 2 row)))))
223 collect (nth 2 row))))
226 (defmethod database-list-views ((database generic-odbc-database)
228 (multiple-value-bind (rows col-names)
229 (funcall (list-all-database-tables-fn database) :db (odbc-conn database))
230 (declare (ignore col-names))
231 ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager
232 ;; TABLE_NAME in third column, TABLE_TYPE in fourth column
233 (loop for (category view-owner name type . rest) in rows
234 when (and (or (null owner) (string-equal owner view-owner) )
235 (not (string-equal "information_schema" view-owner))
236 (string-equal "VIEW" type)
237 (not (and (eq :mssql (database-underlying-type database))
238 (member name '("sysconstraints" "syssegments") :test #'string-equal))))
242 (defmethod database-list-attributes ((table string) (database generic-odbc-database)
244 (declare (ignore owner))
245 (multiple-value-bind (rows col-names)
246 (funcall (list-all-table-columns-fn database) table
247 :db (odbc-conn database))
248 (declare (ignore col-names))
249 ;; COLUMN_NAME is hard-coded by odbc spec as fourth position
250 (loop for row in rows
251 collect (fourth row))))
253 (defmethod database-attribute-type ((attribute string) (table string) (database generic-odbc-database)
255 (declare (ignore owner))
256 (multiple-value-bind (rows col-names)
257 (funcall (list-all-table-columns-fn database) table
258 :db (odbc-conn database))
259 (declare (ignore col-names))
260 ;; COLUMN_NAME is hard-coded by odbc spec as fourth position
261 ;; TYPE_NAME is the sixth column
262 ;; PRECISION/COLUMN_SIZE is the seventh column
263 ;; SCALE/DECIMAL_DIGITS is the ninth column
264 ;; NULLABLE is the eleventh column
265 (loop for row in rows
266 when (string-equal attribute (fourth row))
268 (let ((size (seventh row))
269 (precision (ninth row))
270 (scale (nth 10 row)))
271 (return (values (ensure-keyword (sixth row))
272 (when size (parse-integer size))
273 (when precision (parse-integer precision))
274 (when scale (parse-integer scale))))))))