r9808: * db-oracle/oracle-sql.lisp: enable :OWNER :ALL in DATABASE-LIST-* for

	CommonSQL compatibility.
	* tests/test-init.lisp: skip test :FDDL/TABLE/6 on Oracle since
	this column constraint syntax is not supported.
	* tests/test-fddl.lisp: change column indexed in test :FDDL/INDEXES/2
	from EMPLID to LAST_NAME since Oracle complains that EMPLID is already
	indexed.

diff --git a/ChangeLog b/ChangeLog
index bd6abb97c8738d002b533dd295af2d48670e5e89..5b104d5db21a6694eb4a79804832b94ed4b1299f 100644 (file)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,9 @@
+22 Jul 2004 Marcus Pearce <m.t.pearce@city.ac.uk> 
+       * db-oracle/oracle-sql.lisp: enable :OWNER :ALL in DATABASE-LIST-* for 
+       CommonSQL compatibility. 
+       * tests/test-init.lisp: skip test :FDDL/TABLE/6 on Oracle since 
+       this column constraint syntax is not supported. 
+
 17 Jul 2004 Marcus Pearce <m.t.pearce@city.ac.uk> 
        * doc/ref-fdml.xml: document CACHE-TABLE-QUERIES. 
        * tests/test-fdml.xml: add test for CACHE-TABLE-QUERIES. 

diff --git a/db-oracle/oracle-sql.lisp b/db-oracle/oracle-sql.lisp
index 5ee5ce4e1290f99fd7baa588d7f8d305ce20fea7..d59665de16c8815b5fc572004b4c6827ac62e75c 100644 (file)
--- a/db-oracle/oracle-sql.lisp
+++ b/db-oracle/oracle-sql.lisp
@@ -234,68 +234,88 @@ the length of that format.")
 
 (defmethod database-list-tables ((database oracle-database) &key owner)
   (let ((query
-         (if owner
-             (format nil
-                     "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'"
-                     owner)
-             "select table_name from user_tables")))
+         (cond ((null owner) 
+                 "select table_name from user_tables")
+                ((eq owner :all) 
+                 "select table_name from all_tables")
+                (t 
+                 (format nil
+                         "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'"
+                     owner)))))
     (mapcar #'car (database-query query database nil nil))))
 
 
 (defmethod database-list-views ((database oracle-database) &key owner)
   (let ((query
-         (if owner
-             (format nil
-                     "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'"
-                     owner)
-             "select view_name from user_views")))
+         (cond ((null owner) 
+                 "select view_name from user_views")
+                ((eq owner :all)
+                 "select view_name from all_views")
+                (t 
+                 (format nil
+                         "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'"
+                         owner)))))
     (mapcar #'car
-         (database-query query database nil nil))))
+            (database-query query database nil nil))))
 
 (defmethod database-list-indexes ((database oracle-database)
                                   &key (owner nil))
   (let ((query
-         (if owner
-             (format nil
-                     "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
-                     owner)
-             "select index_name from user_indexes")))
+         (cond ((null owner)
+                "select index_name from user_indexes")
+               ((eq owner :all) 
+                "select index_name from all_indexes")
+               (t (format nil
+                          "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
+                          owner)))))
     (mapcar #'car (database-query query database nil nil))))
 
 (defmethod database-list-table-indexes (table (database oracle-database)
                                        &key (owner nil))
   (let ((query
-         (if owner
-             (format nil
-                     "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.table_name='~A' and user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
-                     table owner)
-             (format nil "select index_name from user_indexes where table_name='~A'"
-                     table))))
+         (cond ((null owner)
+                (format nil "select index_name from user_indexes where table_name='~A'"
+                        table))
+               ((eq owner :all) 
+                (format nil "select index_name from all_indexes where table_name='~A'"
+                        table))
+               (t 
+                (format nil
+                        "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.table_name='~A' and user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
+                        table owner)))))
     (mapcar #'car (database-query query database nil nil))))
 
 
 (defmethod database-list-attributes (table (database oracle-database) &key owner)
   (let ((query
-         (if owner
-             (format nil
-                     "select user_tab_columns.column_name from user_tab_columns,all_tables where user_tab_columns.table_name='~A' and all_tables.table_name=user_tab_columns.table_name and all_tables.owner='~:@(~A~)'"
-                     table owner)
-             (format nil
-                     "select column_name from user_tab_columns where table_name='~A'"
-                     table))))
+         (cond ((null owner)
+                (format nil "select column_name from user_tab_columns where table_name='~A'"
+                        table))
+               ((eq owner :all)
+                (format nil "select column_name from all_tab_columns where table_name='~A'"
+                        table))
+               (t 
+                (format nil
+                        "select user_tab_columns.column_name from user_tab_columns,all_tables where user_tab_columns.table_name='~A' and all_tables.table_name=user_tab_columns.table_name and all_tables.owner='~:@(~A~)'"
+                        table owner)))))
     (mapcar #'car (database-query query database nil nil))))
 
 (defmethod database-attribute-type (attribute (table string)
                                         (database oracle-database)
                                         &key (owner nil))
   (let ((query          
-        (if owner
-            (format nil
-                    "select data_type,data_length,data_scale,nullable from user_tab_columns,all_tables where user_tab_columns.table_name='~A' and column_name='~A' and all_tables.table_name=user_tab_columns.table_name and all_tables.owner='~:@(~A~)'"
-                    table attribute owner)
-            (format nil
-                    "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'"
-                    table attribute))))
+        (cond ((null owner)
+                (format nil
+                        "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'"
+                        table attribute))
+               ((eq owner :all)
+                (format nil
+                        "select data_type,data_length,data_scale,nullable from all_tab_columns where table_name='~A' and column_name='~A'"
+                        table attribute))
+               (t 
+                (format nil
+                        "select data_type,data_length,data_scale,nullable from user_tab_columns,all_tables where user_tab_columns.table_name='~A' and column_name='~A' and all_tables.table_name=user_tab_columns.table_name and all_tables.owner='~:@(~A~)'"
+                        table attribute owner)))))
     (destructuring-bind (type length scale nullable) (car (database-query query database :auto nil))
       (values (ensure-keyword type) length scale 
              (if (char-equal #\Y (schar nullable 0)) 1 0)))))
@@ -889,11 +909,14 @@ the length of that format.")
 
 (defmethod database-list-sequences ((database oracle-database) &key owner)
   (let ((query
-        (if owner
-            (format nil
-                    "select user_sequences.sequence_name from user_sequences,all_sequences where user_sequences.sequence_name=all_sequences.sequence_name and all_sequences.sequence_owner='~:@(~A~)'"
-                    owner)
-            "select sequence_name from user_sequences")))
+        (cond ((null owner) 
+                "select sequence_name from user_sequences")
+               ((eq owner :all)
+                "select sequence_name from all_sequences")
+               (t 
+                (format nil
+                        "select user_sequences.sequence_name from user_sequences,all_sequences where user_sequences.sequence_name=all_sequences.sequence_name and all_sequences.sequence_owner='~:@(~A~)'"
+                        owner)))))
     (mapcar #'car (database-query query database nil nil))))
 
 (defmethod database-execute-command (sql-expression (database oracle-database))

diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp
index 61329ff7149289a2c685ef21ff4a4fc07cb41989..8c54c3fb4319824227dcdcc73b75b20f48a13bc3 100644 (file)
--- a/tests/test-fddl.lisp
+++ b/tests/test-fddl.lisp
@@ -244,7 +244,7 @@
     (let ((names '("foo" foo [foo]))
           (result '()))
       (dolist (name names)
-        (clsql:create-index name :on [employee] :attributes '([emplid]))
+        (clsql:create-index name :on [employee] :attributes '([last-name]))
         (push (clsql:index-exists-p name :owner *test-database-user*) result)
        (clsql:drop-index name :on [employee] :if-does-not-exist :ignore))
       (apply #'values result))

diff --git a/tests/test-init.lisp b/tests/test-init.lisp
index ac294a7e68b3de8735e92c4b5911cfa85e5a929e..e3a0a03098fbad4ae001f6cccd2dff56656f9e8f 100644 (file)
--- a/tests/test-init.lisp
+++ b/tests/test-init.lisp
@@ -593,7 +593,8 @@
                (clsql-sys:in test :fdml/query/6 :fdml/select/31))
           (push (cons test "union not supported") skip-tests))
          ((and (eq *test-database-type* :oracle)
-               (clsql-sys:in test :fdml/query/8 :fdml/select/21))
+               (clsql-sys:in test :fdml/query/8 :fdml/select/21 
+                              :fddl/table/6))
           (push (cons test "syntax not supported") skip-tests))
           ((and (not (member *test-database-underlying-type* 
                              '(:postgresql :oracle)))