From 6b773c9d859a10b961df9c1c2c9b8a006b315aff Mon Sep 17 00:00:00 2001 From: "Kevin M. Rosenberg" Date: Tue, 18 May 2004 18:48:39 +0000 Subject: [PATCH] r9395: 18 May 2004 Kevin Rosenberg (kevin@rosenberg.net) * sql/table.lisp: Add PURGE to drop command for oracle 10g backend. To handle this difference, will need to add a new database-drop-table generic function. * db-oracle/oracle-sql.lisp: Move server-version and major-version-number to database object to allow multiple connections to Oracle servers of different versions. --- ChangeLog | 8 ++++ db-oracle/oracle-sql.lisp | 89 ++++++++++++++++++++++++++------------- sql/table.lisp | 5 ++- tests/test-basic.lisp | 11 ++--- 4 files changed, 78 insertions(+), 35 deletions(-) diff --git a/ChangeLog b/ChangeLog index 530aa69..6e5bdff 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,11 @@ +18 May 2004 Kevin Rosenberg (kevin@rosenberg.net) + * sql/table.lisp: Add PURGE to drop command for oracle 10g backend. + To handle this difference, will need to add a new database-drop-table + generic function. + * db-oracle/oracle-sql.lisp: Move server-version and + major-version-number to database object to allow multiple connections + to Oracle servers of different versions. + 18 May 2004 Marcus Pearce (m.t.pearce@city.ac.uk) * TODO: moved notes about backends to doc/TODO. * doc/TODO: added notes about backends and select extensions. diff --git a/db-oracle/oracle-sql.lisp b/db-oracle/oracle-sql.lisp index ebd7e8c..77fdefa 100644 --- a/db-oracle/oracle-sql.lisp +++ b/db-oracle/oracle-sql.lisp @@ -15,9 +15,6 @@ (in-package #:clsql-oracle) -(defvar *oracle-server-version* nil - "Version string of Oracle server.") - (defmethod database-initialize-database-type ((database-type (eql :oracle))) t) @@ -127,7 +124,19 @@ likely that we'll have to worry about the CMUCL limit.")) "Each database connection can be configured with its own date output format. In order to extract date strings from output buffers holding multiple date strings in fixed-width fields, we need to know -the length of that format."))) +the length of that format.") + (server-version + :type string + :initarg :server-version + :reader server-version + :documentation + "Version string of Oracle server.") + (major-version-number + :type (or null fixnum) + :initarg :major-version-number + :reader major-version-number + :documentation + "The major version number of Oracle, should be 8, 9, or 10"))) ;;; Handle the messy case of return code=+oci-error+, querying the @@ -258,22 +267,23 @@ the length of that format."))) ; (sql:query "select '',OWNER,TABLE_NAME,TABLE_TYPE,'' from all_catalog")) -(defmethod database-list-tables ((db oracle-database) &key owner) +(defmethod database-list-tables ((database oracle-database) &key owner) + (mapcar #'car + (database-query "select table_name from user_tables" + database nil nil)) + #+nil (values (database-query "select TABLE_NAME from all_catalog where owner not in ('PUBLIC','SYSTEM','SYS','WMSYS','EXFSYS','CTXSYS','WKSYS','WK_TEST','MDSYS','DMSYS','OLAPSYS','ORDSYS','XDB')" db nil nil))) (defmethod database-list-views ((database oracle-database) - &key (system-tables nil) owner) - (if system-tables - (database-query "select table_name from all_catalog" database nil nil) - (database-query "select table_name from all_catalog where owner != 'PUBLIC' and owner != 'SYSTEM' and owner != 'SYS'" - database nil nil))) + &key owner) + ;; (database-query "select table_name from all_catalog" database nil nil) + (mapcar #'car + (database-query "select view_name from user_views" database nil nil))) ;; Return a list of all columns in TABLE. -;; -;; The Allegro version of this also returned a second value. (defmethod list-all-table-columns (table (db oracle-database)) (declare (type string table)) @@ -302,6 +312,11 @@ the length of that format."))) 1))) ; string preresult)) +(defmethod database-list-indexes ((database oracle-database) + &key (owner nil)) + (mapcar #'car + (database-query "select index_name from user_indexes" database nil nil))) + (defmethod database-list-attributes (table (database oracle-database) &key owner) (let* ((relname (etypecase table (clsql-sys::sql-ident @@ -720,15 +735,25 @@ the length of that format."))) ;; oci-handle-alloc((dvoid *)encvhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0); ;;#+nil ) - (let ((db (make-instance 'oracle-database - :name (database-name-from-spec connection-spec - database-type) - :envhp envhp - :errhp errhp - :database-type :oracle - :svchp svchp - :dsn data-source-name - :user user))) + (let (db server-version) + (uffi:with-foreign-object (buf (:array :unsigned-char #.+errbuf-len+)) + (oci-server-version (uffi:deref-pointer svchp void-pointer) + (uffi:deref-pointer errhp void-pointer) + buf +errbuf-len+ +oci-htype-svcctx+) + (setf server-version (uffi:convert-from-foreign-string buf))) + (setq db (make-instance 'oracle-database + :name (database-name-from-spec connection-spec + database-type) + :envhp envhp + :errhp errhp + :database-type :oracle + :svchp svchp + :dsn data-source-name + :user user + :server-version server-version + :major-version-number (major-version-from-string + server-version))) + (oci-logon (uffi:deref-pointer envhp void-pointer) (uffi:deref-pointer errhp void-pointer) svchp @@ -737,20 +762,20 @@ the length of that format."))) (uffi:convert-to-cstring data-source-name) (length data-source-name) :database db) ;; :date-format-length (1+ (length date-format))))) - (uffi:with-foreign-object (buf (:array :unsigned-char 512)) - (oci-server-version (uffi:deref-pointer svchp void-pointer) - (uffi:deref-pointer errhp void-pointer) - buf - 512 - +oci-htype-svcctx+) - (setf *oracle-server-version* (uffi:convert-from-foreign-string buf))) - (setf (slot-value db 'clsql-sys::state) :open) (database-execute-command (format nil "alter session set NLS_DATE_FORMAT='~A'" (date-format db)) db) db)))) +(defun major-version-from-string (str) + (cond + ((search " 10g " str) + 10) + ((search " 9g " str) + 10))) + + ;; Close a database connection. (defmethod database-disconnect ((database oracle-database)) @@ -812,6 +837,9 @@ the length of that format."))) ".NEXTVAL FROM dual" ) :database database))) +(defmethod database-list-sequences ((database oracle-database) &key owner) + (mapcar #'car (database-query "select sequence_name from user_sequences" + database nil nil))) (defmethod database-execute-command (sql-expression (database oracle-database)) (database-query sql-expression database nil nil) @@ -924,3 +952,6 @@ the length of that format."))) (defmethod db-type-has-bigint? ((type (eql :oracle))) nil) + +(defmethod db-type-has-fancy-math? ((db-type (eql :postgresql))) + t) diff --git a/sql/table.lisp b/sql/table.lisp index 3820c19..1b28c4f 100644 --- a/sql/table.lisp +++ b/sql/table.lisp @@ -61,7 +61,10 @@ if IF-DOES-NOT-EXIST is :error." (return-from drop-table nil))) (:error t)) - (let ((expr (concatenate 'string "DROP TABLE " table-name))) + (let ((expr (concatenate 'string "DROP TABLE " table-name + (if (eq :oracle (database-type database)) + " PURGE" + "")))) (execute-command expr :database database)))) (defun list-tables (&key (owner nil) (database *default-database*)) diff --git a/tests/test-basic.lisp b/tests/test-basic.lisp index bdf671f..1cd09d2 100644 --- a/tests/test-basic.lisp +++ b/tests/test-basic.lisp @@ -202,11 +202,12 @@ (clsql-sys:number-to-sql-string test-flt) (clsql-sys:number-to-sql-string test-flt) )) - (clsql:execute-command - (format nil "INSERT INTO TYPE_BIGINT VALUES (~a,~a)" - test-int - (transform-bigint-1 test-int) - ))))) + (when (clsql-sys:db-type-has-bigint? *test-database-type*) + (clsql:execute-command + (format nil "INSERT INTO TYPE_BIGINT VALUES (~a,~a)" + test-int + (transform-bigint-1 test-int) + )))))) ;;;; Testing functions -- 2.34.1