From: Nathan Bird Date: Sat, 23 Jan 2010 20:52:48 +0000 (-0500) Subject: Updating logic for odbc database-list-{tables,views} to use owner and skip system... X-Git-Tag: v5.0.0~40 X-Git-Url: http://git.kpe.io/?p=clsql.git;a=commitdiff_plain;h=73f6c23a39a24d04746b00d8fe3878ca7a6233f1 Updating logic for odbc database-list-{tables,views} to use owner and skip system tables. SqlServer 2005 moved around their namespaces, and added more. Adding those to the hardcoded skip lists. 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. TODO: revisit owner/schema logic. From 2000->2005 sqlserver they separated owner from schema and so this will probably need to be revisited further-- not sure how given the ODBC nature. --- diff --git a/sql/generic-odbc.lisp b/sql/generic-odbc.lisp index 91310fb..9699d29 100644 --- a/sql/generic-odbc.lisp +++ b/sql/generic-odbc.lisp @@ -207,36 +207,44 @@ (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)) - (not (and (eq :mssql (database-underlying-type database)) - (string-equal "dtproperties" (nth 2 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)) - (not (and (eq :mssql (database-underlying-type database)) - (member (nth 2 row) '("sysconstraints" "syssegments") :test #'string-equal)))) - 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)