From 30186614582039bdc3d3f86bc5165ef300c5d3e0 Mon Sep 17 00:00:00 2001 From: Russ Tyndall Date: Mon, 12 Sep 2011 11:13:27 -0400 Subject: [PATCH] fixed a bugs in list-attribute(s|-types) in fddl that I introduced with %database-identifier stuff (essentially I need unquoted column names but was passing around quoted ones). Pass around %database-identifiers instead and only get the un/escaped version as you are about to stick it in a query. --- db-mysql/mysql-sql.lisp | 13 +++++++++---- db-odbc/odbc-api.lisp | 20 +++++++++++--------- db-odbc/odbc-dbi.lisp | 28 +++++++++++++++++----------- sql/fddl.lisp | 14 ++++++++------ sql/generic-odbc.lisp | 12 ++++++++---- sql/generic-postgresql.lisp | 12 ++++++++---- sql/utils.lisp | 6 ++++++ 7 files changed, 67 insertions(+), 38 deletions(-) diff --git a/db-mysql/mysql-sql.lisp b/db-mysql/mysql-sql.lisp index 857bcd5..0038da9 100644 --- a/db-mysql/mysql-sql.lisp +++ b/db-mysql/mysql-sql.lisp @@ -404,8 +404,10 @@ (unless (find col results :test #'string-equal) (push col results))))) -(defmethod database-list-attributes ((table string) (database mysql-database) - &key (owner nil)) +(defmethod database-list-attributes ((table clsql-sys::%database-identifier) + (database mysql-database) + &key (owner nil) + &aux (table (unescaped-database-identifier table))) (declare (ignore owner)) (mapcar #'car (database-query @@ -413,9 +415,12 @@ table database)) database nil nil))) -(defmethod database-attribute-type (attribute (table string) +(defmethod database-attribute-type ((attribute clsql-sys::%database-identifier) + (table clsql-sys::%database-identifier) (database mysql-database) - &key (owner nil)) + &key (owner nil) + &aux (table (unescaped-database-identifier table)) + (attribute (unescaped-database-identifier attribute))) (declare (ignore owner)) (let ((row (car (database-query (format nil diff --git a/db-odbc/odbc-api.lisp b/db-odbc/odbc-api.lisp index f01be31..af36bb9 100644 --- a/db-odbc/odbc-api.lisp +++ b/db-odbc/odbc-api.lisp @@ -1017,16 +1017,18 @@ as possible second argument) to the desired representation of date/time/timestam (with-error-handling (:hstmt hstmt) (SQLTables hstmt +null-ptr+ 0 +null-ptr+ 0 +null-ptr+ 0 +null-ptr+ 0))) -(defun %table-statistics (table hstmt &key unique (ensure t)) +(defun %table-statistics (table hstmt &key unique (ensure t) + &aux (table (princ-to-string + (clsql-sys::unescaped-database-identifier table)))) (with-cstrings ((table-cs table)) - (with-error-handling (:hstmt hstmt) - (SQLStatistics - hstmt - +null-ptr+ 0 - +null-ptr+ 0 - table-cs $SQL_NTS - (if unique $SQL_INDEX_UNIQUE $SQL_INDEX_ALL) - (if ensure $SQL_ENSURE $SQL_QUICK))))) + (with-error-handling (:hstmt hstmt) + (SQLStatistics + hstmt + +null-ptr+ 0 + +null-ptr+ 0 + table-cs $SQL_NTS + (if unique $SQL_INDEX_UNIQUE $SQL_INDEX_ALL) + (if ensure $SQL_ENSURE $SQL_QUICK))))) (defun %list-data-sources (henv) (let ((results nil)) diff --git a/db-odbc/odbc-dbi.lisp b/db-odbc/odbc-dbi.lisp index 319a8cc..a3eb50b 100644 --- a/db-odbc/odbc-dbi.lisp +++ b/db-odbc/odbc-dbi.lisp @@ -207,22 +207,28 @@ the query against." )) (coerce (column-names query) 'list)))) (db-close-query query)))) -(defun list-table-indexes (table &key db unique hstmt) +(defun list-table-indexes (table &key db unique hstmt + &aux (table + (princ-to-string + (clsql-sys::unescaped-database-identifier table)))) (declare (ignore hstmt)) (let ((query (get-free-query db))) (unwind-protect - (progn - (with-slots (hstmt) query - (unless hstmt - (setf hstmt (%new-statement-handle (hdbc db)))) - (%table-statistics table hstmt :unique unique) - (%initialize-query query nil nil) - (values - (db-fetch-query-results query) - (coerce (column-names query) 'list)))) + (progn + (with-slots (hstmt) query + (unless hstmt + (setf hstmt (%new-statement-handle (hdbc db)))) + (%table-statistics table hstmt :unique unique) + (%initialize-query query nil nil) + (values + (db-fetch-query-results query) + (coerce (column-names query) 'list)))) (db-close-query query)))) -(defun list-all-table-columns (table &key db hstmt) +(defun list-all-table-columns (table &key db hstmt + &aux (table + (princ-to-string + (clsql-sys::unescaped-database-identifier table)))) (declare (ignore hstmt)) (db-describe-columns db nil nil table nil)) ;; use nil rather than "" for unspecified values diff --git a/sql/fddl.lisp b/sql/fddl.lisp index 267ee29..39a2a0c 100644 --- a/sql/fddl.lisp +++ b/sql/fddl.lisp @@ -309,8 +309,10 @@ nil by default which means that only attributes owned by users are listed. If OWNER is a string denoting a user name, only attributes owned by OWNER are listed. If OWNER is :all then all attributes are listed." - (database-list-attributes (escaped-database-identifier name database) database - :owner owner)) + (database-list-attributes + (database-identifier name database) + database + :owner owner)) (defun attribute-type (attribute table &key (owner nil) (database *default-database*)) @@ -323,8 +325,8 @@ returned. If OWNER is a string denoting a user name, the attribute, if it exists, must be owned by OWNER else nil is returned, whereas if OWNER is :all then the attribute, if it exists, will be returned regardless of its owner." - (database-attribute-type (escaped-database-identifier attribute database) - (escaped-database-identifier table database) + (database-attribute-type (database-identifier attribute database) + (database-identifier table database) database :owner owner)) @@ -342,7 +344,7 @@ second element is its SQL type, the third is the type precision, the fourth is the scale of the attribute and the fifth is 1 if the attribute accepts null values and otherwise 0." (with-slots (attribute-cache) database - (let ((table-ident (escaped-database-identifier table database))) + (let ((table-ident (database-identifier table database))) (multiple-value-bind (val found) (gethash table-ident attribute-cache) (if (and found (second val)) (second val) @@ -350,7 +352,7 @@ the attribute accepts null values and otherwise 0." (cons attribute (multiple-value-list (database-attribute-type - (escaped-database-identifier attribute + (database-identifier attribute database) table-ident database diff --git a/sql/generic-odbc.lisp b/sql/generic-odbc.lisp index bd4d01c..4995c25 100644 --- a/sql/generic-odbc.lisp +++ b/sql/generic-odbc.lisp @@ -250,8 +250,9 @@ on schema since that's what tends to be exposed. Some DBs like mssql (%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 @@ -261,8 +262,11 @@ on schema since that's what tends to be exposed. Some DBs like mssql (loop for row in rows 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 diff --git a/sql/generic-postgresql.lisp b/sql/generic-postgresql.lisp index ecf6ddf..4c726da 100644 --- a/sql/generic-postgresql.lisp +++ b/sql/generic-postgresql.lisp @@ -140,10 +140,11 @@ database nil nil)) result)))) -(defmethod database-list-attributes ((table string) +(defmethod database-list-attributes ((table %database-identifier) (database generic-postgresql-database) &key (owner nil)) - (let* ((owner-clause + (let* ((table (unescaped-database-identifier table)) + (owner-clause (cond ((stringp owner) (format nil " AND (relowner=(SELECT usesysid FROM pg_user WHERE usename='~A'))" owner)) ((null owner) " AND (not (relowner=1))") @@ -166,9 +167,12 @@ "tableoid") :test #'equal)) result)))) -(defmethod database-attribute-type (attribute (table string) +(defmethod database-attribute-type ((attribute %database-identifier) + (table %database-identifier) (database generic-postgresql-database) - &key (owner nil)) + &key (owner nil) + &aux (table (unescaped-database-identifier table)) + (attribute (unescaped-database-identifier attribute))) (let ((row (car (database-query (format nil "SELECT pg_type.typname,pg_attribute.attlen,pg_attribute.atttypmod,pg_attribute.attnotnull FROM pg_type,pg_class,pg_attribute WHERE pg_class.oid=pg_attribute.attrelid AND pg_class.relname='~A' AND pg_attribute.attname='~A' AND pg_attribute.atttypid=pg_type.oid~A" (string-downcase table) diff --git a/sql/utils.lisp b/sql/utils.lisp index d5b31ed..52ee019 100644 --- a/sql/utils.lisp +++ b/sql/utils.lisp @@ -16,6 +16,12 @@ (in-package #:clsql-sys) +(defun %get-int (v) + (etypecase v + (string (parse-integer v :junk-allowed t)) + (integer v) + (number (truncate v)))) + (defvar +whitespace-chars+ '(#\space #\tab #\newline #\return ;; Tested: sbcl unicode, allegrocl, openmcl,clisp use #\no-break_space -- 2.34.1