From a4097e19c5157e87b9991549bc44f3ef598aeb90 Mon Sep 17 00:00:00 2001 From: "Kevin M. Rosenberg" Date: Tue, 20 Apr 2004 23:25:35 +0000 Subject: [PATCH] r9114: fixes for list-indexes --- base/db-interface.lisp | 9 +++++++++ base/package.lisp | 3 +++ db-mysql/mysql-sql.lisp | 5 +++++ db-odbc/odbc-api.lisp | 3 --- db-odbc/odbc-sql.lisp | 26 +++++++++++++++++++++----- sql/package.lisp | 4 ++++ sql/table.lisp | 2 ++ tests/test-fddl.lisp | 16 ++++++++-------- 8 files changed, 52 insertions(+), 16 deletions(-) diff --git a/base/db-interface.lisp b/base/db-interface.lisp index d257a5f..d874a7f 100644 --- a/base/db-interface.lisp +++ b/base/db-interface.lisp @@ -204,6 +204,15 @@ the given lisp type and parameters.")) (defgeneric oid (object) (:documentation "Return the unique ID of a database object.")) +;;; Database backend capabilities + +(defgeneric db-use-column-on-drop-index? (database) + (:method (database) + (declare (ignore database)) + ;; Standard SQL does not use column name on DROP INDEX + nil) + (:documentation "NIL [default] lif database does not use column name on DROP INDEX.")) + ;;; Large objects support (Marc Battyani) diff --git a/base/package.lisp b/base/package.lisp index daea90f..0301230 100644 --- a/base/package.lisp +++ b/base/package.lisp @@ -285,6 +285,9 @@ #:transaction-level #:transaction + ;; Database features specialized by backend + #:db-use-column-on-drop-index? + )) (:documentation "This is the INTERNAL SQL-Interface package of CLSQL-BASE.")) diff --git a/db-mysql/mysql-sql.lisp b/db-mysql/mysql-sql.lisp index c047b97..7cc6f50 100644 --- a/db-mysql/mysql-sql.lisp +++ b/db-mysql/mysql-sql.lisp @@ -419,6 +419,11 @@ (database-disconnect database) (setf (slot-value database 'clsql-base-sys::state) :closed)))))) +;;; Database capabilities + +(defmethod db-use-column-on-drop-index? ((database mysql-database)) + t) (when (clsql-base-sys:database-type-library-loaded :mysql) (clsql-base-sys:initialize-database-type :database-type :mysql)) + diff --git a/db-odbc/odbc-api.lisp b/db-odbc/odbc-api.lisp index 5ff3d09..03860af 100644 --- a/db-odbc/odbc-api.lisp +++ b/db-odbc/odbc-api.lisp @@ -943,9 +943,6 @@ as possible second argument) to the desired representation of date/time/timestam (defun %table-statistics (table hstmt &key unique (ensure t)) (with-cstrings ((table-cs table)) (with-error-handling (:hstmt hstmt) - (print hstmt) - (print table-cs) - (print (uffi:convert-from-cstring table-cs)) (SQLStatistics hstmt +null-ptr+ 0 diff --git a/db-odbc/odbc-sql.lisp b/db-odbc/odbc-sql.lisp index 2fad848..0da1a04 100644 --- a/db-odbc/odbc-sql.lisp +++ b/db-odbc/odbc-sql.lisp @@ -286,17 +286,33 @@ &key (owner nil)) (let ((result '())) (dolist (table (database-list-tables database :owner owner) result) - (append (database-list-table-indexes table database :owner owner) - result)))) + (setq result + (append (database-list-table-indexes table database :owner owner) + result))))) (defmethod database-list-table-indexes (table (database odbc-database) &key (owner nil)) + (declare (ignore owner)) (multiple-value-bind (rows col-names) - (odbc-dbi:list-table-indexes table :db (database-odbc-conn database)) + (odbc-dbi:list-table-indexes + (string-downcase table) + :db (database-odbc-conn database)) (declare (ignore col-names)) ;; INDEX_NAME is hard-coded in sixth position by ODBC driver - (loop for row in rows collect (nth 5 row)))) + ;; FIXME: ??? is hard-coded in the fourth position + (do ((results nil) + (loop-rows rows (cdr loop-rows))) + ((null loop-rows) (nreverse results)) + (let* ((row (car loop-rows)) + (col (nth 5 row)) + (type (nth 3 row))) + (unless (or (find col results :test #'string-equal) + #+ignore (equal "0" type)) + (push col results)))))) + +(defmethod database-initialize-database-type ((database-type (eql :odbc))) + ;; nothing to do + t) -#+ignore (when (clsql-base-sys:database-type-library-loaded :odbc) (clsql-base-sys:initialize-database-type :database-type :odbc)) diff --git a/sql/package.lisp b/sql/package.lisp index 302bc42..1a17ce6 100644 --- a/sql/package.lisp +++ b/sql/package.lisp @@ -231,6 +231,10 @@ #:database-commit-transaction #:transaction-level #:transaction + + ;; Database capabilities + #:db-use-column-on-drop-index? + )) (:export ;; "Private" exports for use by interface packages diff --git a/sql/table.lisp b/sql/table.lisp index 52b5c1a..6491fad 100644 --- a/sql/table.lisp +++ b/sql/table.lisp @@ -173,6 +173,8 @@ specification of a table to drop the index from." (unless (index-exists-p index-name :database database) (return-from drop-index))) (:error t)) + (unless (db-use-column-on-drop-index? database) + (setq on nil)) (execute-command (format nil "DROP INDEX ~A~A" index-name (if (null on) "" (concatenate 'string " ON " diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp index e231592..98cd216 100644 --- a/tests/test-fddl.lisp +++ b/tests/test-fddl.lisp @@ -176,24 +176,24 @@ ;; test list-table-indexes (deftest :fddl/index/3 (progn - (clsql:create-index [bar] :on [employee] :attributes - '([last-name]) :unique nil) - (clsql:create-index [foo] :on [employee] :attributes - '([first-name]) :unique nil) + (clsql:execute-command "CREATE TABLE I3TEST (a char(10), b integer)") + (clsql:create-index [bar] :on [i3test] :attributes + '([a]) :unique t) + (clsql:create-index [foo] :on [i3test] :attributes + '([b]) :unique nil) (values (sort (mapcar #'string-downcase - (clsql:list-table-indexes [employee] :owner *test-database-user*)) + (clsql:list-table-indexes [i3test] :owner *test-database-user*)) #'string-lessp) (sort (clsql:list-table-indexes [company] :owner *test-database-user*) #'string-lessp) (progn - (clsql:drop-index [bar] :on [employee]) - (clsql:drop-index [foo] :on [employee]) + (clsql:drop-index [bar] :on [i3test]) + (clsql:drop-index [foo] :on [i3test]) t))) - ("bar" "foo") nil t) ;; create an sequence, test for existence, drop it and test again -- 2.34.1