X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=sql%2Fgeneric-odbc.lisp;h=d64db208b1b3a62b190b4773624ab67cd8adb12b;hp=5d82fdd38b81c4e0ed66a07d2a4c3d5d8146c590;hb=f103c1a5416d2f22820d66020e4f9c18c766d894;hpb=a050eddab954ab3045d8427cf0c5563142266833 diff --git a/sql/generic-odbc.lisp b/sql/generic-odbc.lisp index 5d82fdd..d64db20 100644 --- a/sql/generic-odbc.lisp +++ b/sql/generic-odbc.lisp @@ -1,8 +1,6 @@ ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- ;;;; ************************************************************************* ;;;; -;;;; $Id$ -;;;; ;;;; Generic ODBC layer, used by db-odbc and db-aodbc backends ;;;; ;;;; This file is part of CLSQL. @@ -27,37 +25,89 @@ (defmethod initialize-instance :after ((db generic-odbc-database) &rest all-keys) + (declare (ignore all-keys)) (unless (slot-boundp db 'dbi-package) (error "dbi-package not specified.")) (let ((pkg (slot-value db 'dbi-package))) (unless pkg (error "dbi-package is nil.")) (setf (slot-value db 'disconnect-fn) - (intern (symbol-name '#:disconnect) pkg) - (slot-value db 'sql-fn) - (intern (symbol-name '#:sql) pkg) - (slot-value db 'close-query-fn) - (intern (symbol-name '#:close-query) pkg) - (slot-value db 'fetch-row) - (intern (symbol-name '#:fetch-row) pkg) - (slot-value db 'list-all-database-tables-fn) - (intern (symbol-name '#:list-all-database-tables) pkg) - (slot-value db 'list-all-table-columns-fn) - (intern (symbol-name '#:list-all-table-columns) pkg)))) + (intern (symbol-name '#:disconnect) pkg) + (slot-value db 'sql-fn) + (intern (symbol-name '#:sql) pkg) + (slot-value db 'close-query-fn) + (intern (symbol-name '#:close-query) pkg) + (slot-value db 'fetch-row) + (intern (symbol-name '#:fetch-row) pkg) + (slot-value db 'list-all-database-tables-fn) + (intern (symbol-name '#:list-all-database-tables) pkg) + (slot-value db 'list-all-table-columns-fn) + (intern (symbol-name '#:list-all-table-columns) pkg)))) ;;; Object methods (defmethod read-sql-value (val (type (eql 'boolean)) - (database generic-odbc-database) - (db-type (eql :postgresql))) + (database generic-odbc-database) + (db-type (eql :postgresql))) (if (string= "0" val) nil t)) (defmethod read-sql-value (val (type (eql 'generalized-boolean)) - (database generic-odbc-database) - (db-type (eql :postgresql))) + (database generic-odbc-database) + (db-type (eql :postgresql))) (if (string= "0" val) nil t)) - +(defmethod read-sql-value (val (type (eql 'boolean)) database + (db-type (eql :mssql))) + (declare (ignore database)) + (etypecase val + (string (if (string= "0" val) nil t)) + (integer (if (zerop val) nil t)))) + +(defmethod read-sql-value (val (type (eql 'generalized-boolean)) database + (db-type (eql :mssql))) + (declare (ignore database)) + (etypecase val + (string (if (string= "0" val) nil t)) + (integer (if (zerop val) nil t)))) + +;;; Type methods + +(defmethod database-get-type-specifier ((type symbol) args database + (db-type (eql :mssql))) + "Special database types for MSSQL backends" + (declare (ignore database db-type args)) + (case type + (wall-time "DATETIME") + (date "SMALLDATETIME") + ((generalized-boolean boolean) "BIT") + ((longchar text) "ntext") + ((varchar string) + (if args + (format nil "NVARCHAR(~A)" (car args)) + (format nil "NVARCHAR(~D)" *default-string-length*))) + (t (call-next-method)))) + +;;; Generation of SQL strings from lisp expressions + +(defmethod database-output-sql ((tee (eql t)) (database generic-odbc-database)) + (case (database-underlying-type database) + (:mssql "1") + (t "'Y'"))) + +;;; Database backend capabilities + +(defmethod db-type-use-fully-qualified-column-on-drop-index? ((db-type (eql :mssql))) + t) + +(defmethod db-type-has-boolean-where? ((db-type (eql :mssql))) + nil) + +(defmethod db-type-has-intersect? ((db-type (eql :mssql))) + nil) + +(defmethod db-type-has-except? ((db-type (eql :mssql))) + nil) + ;;; Backend methods (defmethod database-disconnect ((database generic-odbc-database)) @@ -65,34 +115,34 @@ (setf (odbc-conn database) nil) t) -(defmethod database-query (query-expression (database generic-odbc-database) - result-types field-names) +(defmethod database-query (query-expression (database generic-odbc-database) + result-types field-names) (handler-case (funcall (sql-fn database) - query-expression :db (odbc-conn database) - :result-types result-types - :column-names field-names) + query-expression :db (odbc-conn database) + :result-types result-types + :column-names field-names) #+ignore (error () (error 'sql-database-data-error - :database database - :expression query-expression - :message "Query failed")))) + :database database + :expression query-expression + :message "Query failed")))) (defmethod database-execute-command (sql-expression (database generic-odbc-database)) (handler-case (funcall (sql-fn database) - sql-expression :db (odbc-conn database)) + sql-expression :db (odbc-conn database)) #+ignore (sql-error (e) (error e)) #+ignore (error () (error 'sql-database-data-error - :database database - :expression sql-expression - :message "Execute command failed")))) + :database database + :expression sql-expression + :message "Execute command failed")))) (defstruct odbc-result-set @@ -104,92 +154,108 @@ (defmethod database-query-result-set ((query-expression string) - (database generic-odbc-database) - &key full-set result-types) - (handler-case + (database generic-odbc-database) + &key full-set result-types) + (handler-case (multiple-value-bind (query column-names) - (funcall (sql-fn database) - query-expression - :db (odbc-conn database) - :row-count nil - :column-names t - :query t - :result-types result-types) - (values - (make-odbc-result-set :query query :full-set full-set - :types result-types) - (length column-names) - nil ;; not able to return number of rows with odbc - )) + (funcall (sql-fn database) + query-expression + :db (odbc-conn database) + :row-count nil + :column-names t + :query t + :result-types result-types) + (values + (make-odbc-result-set :query query :full-set full-set + :types result-types) + (length column-names) + nil ;; not able to return number of rows with odbc + )) (error () (error 'sql-database-data-error - :database database - :expression query-expression - :message "Query result set failed")))) + :database database + :expression query-expression + :message "Query result set failed")))) (defmethod database-dump-result-set (result-set (database generic-odbc-database)) (funcall (close-query-fn database) (odbc-result-set-query result-set)) t) (defmethod database-store-next-row (result-set - (database generic-odbc-database) - list) + (database generic-odbc-database) + list) (let ((row (funcall (fetch-row-fn database) - (odbc-result-set-query result-set) nil 'eof))) + (odbc-result-set-query result-set) nil 'eof))) (if (eq row 'eof) - nil + nil (progn - (loop for elem in row - for rest on list - do - (setf (car rest) elem)) - list)))) + (loop for elem in row + for rest on list + do + (setf (car rest) elem)) + list)))) -(defmethod database-list-tables ((database generic-odbc-database) - &key (owner nil)) - (declare (ignore owner)) + +(defun %database-list-* (database type owner) + "Internal function used by database-list-tables and +database-list-views" (multiple-value-bind (rows col-names) (funcall (list-all-database-tables-fn database) :db (odbc-conn database)) (declare (ignore col-names)) + ;; http://msdn.microsoft.com/en-us/library/ms711831%28VS.85%29.aspx ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager ;; TABLE_NAME in third column, TABLE_TYPE in fourth column - (loop for row in rows - when (and (not (string-equal "information_schema" (nth 1 row))) - (string-equal "TABLE" (nth 3 row))) - collect (nth 2 row)))) + (loop for (category schema name ttype . rest) in rows + when (and (string-equal type ttype) + (or (null owner) (string-equal owner schema)) + ;; unless requesting by name, skip system schema + (not (and (null owner) + (member schema '("information_schema" "sys") + :test #'string-equal))) + ;; skip system specific tables in mssql2000 + (not (and (eql :mssql (database-underlying-type database)) + (member name '("dtproperties" "sysconstraints" + "syssegments") + :test #'string-equal)))) + collect name))) + +(defmethod database-list-tables ((database generic-odbc-database) + &key (owner nil)) + "Since ODBC doesn't expose the owner we use that parameter to filter +on schema since that's what tends to be exposed. Some DBs like mssql +2000 conflate the two so at least there it works nicely." + (%database-list-* database "TABLE" owner)) (defmethod database-list-views ((database generic-odbc-database) - &key (owner nil)) - (declare (ignore owner)) - (multiple-value-bind (rows col-names) - (funcall (list-all-database-tables-fn database) :db (odbc-conn database)) - (declare (ignore col-names)) - ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager - ;; TABLE_NAME in third column, TABLE_TYPE in fourth column - (loop for row in rows - when (and (not (string-equal "information_schema" (nth 1 row))) - (string-equal "VIEW" (nth 3 row))) - collect (nth 2 row)))) + &key (owner nil)) + "Since ODBC doesn't expose the owner we use that parameter to filter +on schema since that's what tends to be exposed. Some DBs like mssql +2000 conflate the two so at least there it works nicely." + (%database-list-* database "VIEW" owner)) -(defmethod database-list-attributes ((table string) (database generic-odbc-database) - &key (owner nil)) +(defmethod database-list-attributes ((table %database-identifier) (database generic-odbc-database) + &key (owner nil) + &aux (table (unescaped-database-identifier table))) (declare (ignore owner)) (multiple-value-bind (rows col-names) (funcall (list-all-table-columns-fn database) table - :db (odbc-conn database)) + :db (odbc-conn database)) (declare (ignore col-names)) ;; COLUMN_NAME is hard-coded by odbc spec as fourth position (loop for row in rows - collect (fourth row)))) + collect (fourth row)))) -(defmethod database-attribute-type ((attribute string) (table string) (database generic-odbc-database) - &key (owner nil)) +(defmethod database-attribute-type ((attribute %database-identifier) (table %database-identifier) + (database generic-odbc-database) + &key (owner nil) + &aux (table (unescaped-database-identifier table)) + (attribute (unescaped-database-identifier attribute))) (declare (ignore owner)) (multiple-value-bind (rows col-names) (funcall (list-all-table-columns-fn database) table - :db (odbc-conn database)) + :db (odbc-conn database)) (declare (ignore col-names)) ;; COLUMN_NAME is hard-coded by odbc spec as fourth position ;; TYPE_NAME is the sixth column @@ -197,12 +263,12 @@ ;; SCALE/DECIMAL_DIGITS is the ninth column ;; NULLABLE is the eleventh column (loop for row in rows - when (string-equal attribute (fourth row)) - do - (let ((size (seventh row)) - (precision (ninth row)) - (scale (nth 10 row))) - (return (values (ensure-keyword (sixth row)) - (when size (parse-integer size)) - (when precision (parse-integer precision)) - (when scale (parse-integer scale)))))))) + when (string-equal attribute (fourth row)) + do + (let ((size (seventh row)) + (precision (ninth row)) + (scale (nth 10 row))) + (return (values (ensure-keyword (sixth row)) + (when size (parse-integer size)) + (when precision (parse-integer precision)) + (when scale (parse-integer scale))))))))