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 (unless (slot-boundp db 'dbi-package)
31 (error "dbi-package not specified."))
32 (let ((pkg (slot-value db 'dbi-package)))
34 (error "dbi-package is nil."))
35 (setf (slot-value db 'disconnect-fn)
36 (intern (symbol-name '#:disconnect) pkg)
37 (slot-value db 'sql-fn)
38 (intern (symbol-name '#:sql) pkg)
39 (slot-value db 'close-query-fn)
40 (intern (symbol-name '#:close-query) pkg)
41 (slot-value db 'fetch-row)
42 (intern (symbol-name '#:fetch-row) pkg)
43 (slot-value db 'list-all-database-tables-fn)
44 (intern (symbol-name '#:list-all-database-tables) pkg)
45 (slot-value db 'list-all-table-columns-fn)
46 (intern (symbol-name '#:list-all-table-columns) pkg))))
50 (defmethod read-sql-value (val (type (eql 'boolean))
51 (database generic-odbc-database)
52 (db-type (eql :postgresql)))
53 (if (string= "0" val) nil t))
55 (defmethod read-sql-value (val (type (eql 'generalized-boolean))
56 (database generic-odbc-database)
57 (db-type (eql :postgresql)))
58 (if (string= "0" val) nil t))
63 (defmethod database-disconnect ((database generic-odbc-database))
64 (funcall (disconnect-fn database) (odbc-conn database))
65 (setf (odbc-conn database) nil)
68 (defmethod database-query (query-expression (database generic-odbc-database)
69 result-types field-names)
71 (funcall (sql-fn database)
72 query-expression :db (odbc-conn database)
73 :result-types result-types
74 :column-names field-names)
77 (error 'sql-database-data-error
79 :expression query-expression
80 :message "Query failed"))))
83 (defmethod database-execute-command (sql-expression (database generic-odbc-database))
85 (funcall (sql-fn database)
86 sql-expression :db (odbc-conn database))
92 (error 'sql-database-data-error
94 :expression sql-expression
95 :message "Execute command failed"))))
98 (defstruct odbc-result-set
101 (full-set nil :type boolean))
106 (defmethod database-query-result-set ((query-expression string)
107 (database generic-odbc-database)
108 &key full-set result-types)
110 (multiple-value-bind (query column-names)
111 (funcall (sql-fn database)
113 :db (odbc-conn database)
117 :result-types result-types)
119 (make-odbc-result-set :query query :full-set full-set
121 (length column-names)
122 nil ;; not able to return number of rows with odbc
125 (error 'sql-database-data-error
127 :expression query-expression
128 :message "Query result set failed"))))
130 (defmethod database-dump-result-set (result-set (database generic-odbc-database))
131 (funcall (close-query-fn database) (odbc-result-set-query result-set))
134 (defmethod database-store-next-row (result-set
135 (database generic-odbc-database)
137 (let ((row (funcall (fetch-row-fn database)
138 (odbc-result-set-query result-set) nil 'eof)))
142 (loop for elem in row
145 (setf (car rest) elem))
148 (defmethod database-list-tables ((database generic-odbc-database)
150 (declare (ignore owner))
151 (multiple-value-bind (rows col-names)
152 (funcall (list-all-database-tables-fn database) :db (odbc-conn database))
153 (declare (ignore col-names))
154 ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager
155 ;; TABLE_NAME in third column, TABLE_TYPE in fourth column
156 (loop for row in rows
157 when (and (not (string-equal "information_schema" (nth 1 row)))
158 (string-equal "TABLE" (nth 3 row)))
159 collect (nth 2 row))))
162 (defmethod database-list-views ((database generic-odbc-database)
164 (declare (ignore owner))
165 (multiple-value-bind (rows col-names)
166 (funcall (list-all-database-tables-fn database) :db (odbc-conn database))
167 (declare (ignore col-names))
168 ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager
169 ;; TABLE_NAME in third column, TABLE_TYPE in fourth column
170 (loop for row in rows
171 when (and (not (string-equal "information_schema" (nth 1 row)))
172 (string-equal "VIEW" (nth 3 row)))
173 collect (nth 2 row))))
176 (defmethod database-list-attributes ((table string) (database generic-odbc-database)
178 (declare (ignore owner))
179 (multiple-value-bind (rows col-names)
180 (funcall (list-all-table-columns-fn database) table
181 :db (odbc-conn database))
182 (declare (ignore col-names))
183 ;; COLUMN_NAME is hard-coded by odbc spec as fourth position
184 (loop for row in rows
185 collect (fourth row))))
187 (defmethod database-attribute-type ((attribute string) (table string) (database generic-odbc-database)
189 (declare (ignore owner))
190 (multiple-value-bind (rows col-names)
191 (funcall (list-all-table-columns-fn database) table
192 :db (odbc-conn database))
193 (declare (ignore col-names))
194 ;; COLUMN_NAME is hard-coded by odbc spec as fourth position
195 ;; TYPE_NAME is the sixth column
196 ;; PRECISION/COLUMN_SIZE is the seventh column
197 ;; SCALE/DECIMAL_DIGITS is the ninth column
198 ;; NULLABLE is the eleventh column
199 (loop for row in rows
200 when (string-equal attribute (fourth row))
202 (let ((size (seventh row))
203 (precision (ninth row))
204 (scale (nth 10 row)))
205 (return (values (ensure-keyword (sixth row))
206 (when size (parse-integer size))
207 (when precision (parse-integer precision))
208 (when scale (parse-integer scale))))))))