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.

diff --git a/ChangeLog b/ChangeLog
index 530aa699e153a16693daea677cddc100fd49847a..6e5bdffaf2ba29949f3addea02c4c88467077ec8 100644 (file)
--- 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 ebd7e8cb1f54887ac9900ae0cc1fcbee1e45a0b6..77fdefacff2667327793779576c80646c36adea5 100644 (file)
--- 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 3820c19bd61d623a02ee50aeae155fe605c15789..1b28c4ff10db3b6b37bf181ee09b702576978b3e 100644 (file)
--- 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 bdf671f2c62e455aa1d8abc32592cb00f1de5ba5..1cd09d2b50fe1171da577a48444b2f093e5597a6 100644 (file)
--- 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