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
))
64 (defmethod database-disconnect ((database generic-odbc-database
))
65 (funcall (disconnect-fn database
) (odbc-conn database
))
66 (setf (odbc-conn database
) nil
)
69 (defmethod database-query (query-expression (database generic-odbc-database
)
70 result-types field-names
)
72 (funcall (sql-fn database
)
73 query-expression
:db
(odbc-conn database
)
74 :result-types result-types
75 :column-names field-names
)
78 (error 'sql-database-data-error
80 :expression query-expression
81 :message
"Query failed"))))
84 (defmethod database-execute-command (sql-expression (database generic-odbc-database
))
86 (funcall (sql-fn database
)
87 sql-expression
:db
(odbc-conn database
))
93 (error 'sql-database-data-error
95 :expression sql-expression
96 :message
"Execute command failed"))))
99 (defstruct odbc-result-set
102 (full-set nil
:type boolean
))
107 (defmethod database-query-result-set ((query-expression string
)
108 (database generic-odbc-database
)
109 &key full-set result-types
)
111 (multiple-value-bind (query column-names
)
112 (funcall (sql-fn database
)
114 :db
(odbc-conn database
)
118 :result-types result-types
)
120 (make-odbc-result-set :query query
:full-set full-set
122 (length column-names
)
123 nil
;; not able to return number of rows with odbc
126 (error 'sql-database-data-error
128 :expression query-expression
129 :message
"Query result set failed"))))
131 (defmethod database-dump-result-set (result-set (database generic-odbc-database
))
132 (funcall (close-query-fn database
) (odbc-result-set-query result-set
))
135 (defmethod database-store-next-row (result-set
136 (database generic-odbc-database
)
138 (let ((row (funcall (fetch-row-fn database
)
139 (odbc-result-set-query result-set
) nil
'eof
)))
143 (loop for elem in row
146 (setf (car rest
) elem
))
149 (defmethod database-list-tables ((database generic-odbc-database
)
151 (declare (ignore owner
))
152 (multiple-value-bind (rows col-names
)
153 (funcall (list-all-database-tables-fn database
) :db
(odbc-conn database
))
154 (declare (ignore col-names
))
155 ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager
156 ;; TABLE_NAME in third column, TABLE_TYPE in fourth column
157 (loop for row in rows
158 when
(and (not (string-equal "information_schema" (nth 1 row
)))
159 (string-equal "TABLE" (nth 3 row
)))
160 collect
(nth 2 row
))))
163 (defmethod database-list-views ((database generic-odbc-database
)
165 (declare (ignore owner
))
166 (multiple-value-bind (rows col-names
)
167 (funcall (list-all-database-tables-fn database
) :db
(odbc-conn database
))
168 (declare (ignore col-names
))
169 ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager
170 ;; TABLE_NAME in third column, TABLE_TYPE in fourth column
171 (loop for row in rows
172 when
(and (not (string-equal "information_schema" (nth 1 row
)))
173 (string-equal "VIEW" (nth 3 row
)))
174 collect
(nth 2 row
))))
177 (defmethod database-list-attributes ((table string
) (database generic-odbc-database
)
179 (declare (ignore owner
))
180 (multiple-value-bind (rows col-names
)
181 (funcall (list-all-table-columns-fn database
) table
182 :db
(odbc-conn database
))
183 (declare (ignore col-names
))
184 ;; COLUMN_NAME is hard-coded by odbc spec as fourth position
185 (loop for row in rows
186 collect
(fourth row
))))
188 (defmethod database-attribute-type ((attribute string
) (table string
) (database generic-odbc-database
)
190 (declare (ignore owner
))
191 (multiple-value-bind (rows col-names
)
192 (funcall (list-all-table-columns-fn database
) table
193 :db
(odbc-conn database
))
194 (declare (ignore col-names
))
195 ;; COLUMN_NAME is hard-coded by odbc spec as fourth position
196 ;; TYPE_NAME is the sixth column
197 ;; PRECISION/COLUMN_SIZE is the seventh column
198 ;; SCALE/DECIMAL_DIGITS is the ninth column
199 ;; NULLABLE is the eleventh column
200 (loop for row in rows
201 when
(string-equal attribute
(fourth row
))
203 (let ((size (seventh row
))
204 (precision (ninth row
))
205 (scale (nth 10 row
)))
206 (return (values (ensure-keyword (sixth row
))
207 (when size
(parse-integer size
))
208 (when precision
(parse-integer precision
))
209 (when scale
(parse-integer scale
))))))))