1. Shantanu Kumar
  2. Clj-DBSpec

Commits

Shantanu Kumar  committed 474f43e

functions and unir-tests to list catalogs, schemas, tables

  • Participants
  • Parent commits 42987a6
  • Branches default

Comments (0)

Files changed (3)

File CHANGES

View file
  • Ignore whitespace
 # Changes and TODO
 
 
-## 0.1 / 2011-Jan-??
+## 0.1 / 2011-Feb-??
 
 * Clojure 1.3 compatibility
 * Dynamic var for dbspec containing the following keys
-  * :datssource
+  * :datasource
   * :connection
   * :dbmetadata
   * :clj-to-db
 * Macro to extract Connection from DataSource and execute body in context
 * Convenience function for converting Clojure to database name
 * Convenience function for converting database to Clojure name
-* Alternative function for resultset-seq: alt-resultset-seq
+* Alternative function for clojure.core/resultset-seq: alt-resultset-seq
 * [TODO] Schema/tables/columns discovery functions

File src/main/clj/org/bituf/clj_dbspec.clj

View file
  • Ignore whitespace
                           (= colvalue-map (.asMap ^Row that)))))
 
 
+(defn row?
+  "Return true if it is a row instance, false otherwise."
+  [x]
+  (instance? Row x))
+
+
 (defn ^Row make-row
   "Create an instance of Row type."
   ([colvalue-vec colvalue-map conv-fn]
       :sup-catalogs-in-proccalls         (.supportsCatalogsInProcedureCalls       metadata)
       :sup-catalogs-in-tabledefs         (.supportsCatalogsInTableDefinitions     metadata)
       ;; (sequence of maps) assorted ResultSet attributes
-      :catalogs               (into [] (alt-resultset-seq (.getCatalogs             metadata)))
-      :client-info-properties (into [] (in/maybe-val (alt-resultset-seq (.getClientInfoProperties metadata))))
-      :schemas                (into [] (alt-resultset-seq (.getSchemas              metadata)))
-      :table-types            (into [] (alt-resultset-seq (.getTableTypes           metadata)))
-      :type-info              (into [] (alt-resultset-seq (.getTypeInfo             metadata))))))
+      :client-info-properties (into [] (in/maybe-val (alt-resultset-seq
+                                                       (.getClientInfoProperties  metadata))))
+      :table-types            (into [] (alt-resultset-seq (.getTableTypes         metadata)))
+      :type-info              (into [] (alt-resultset-seq (.getTypeInfo           metadata))))))
 
 
-;; ----- Database configuration discovery -----
+;; ----- Database introspection -----
 
 
 (defn get-catalogs
-  "Get list of catalogs in database."
+  "Return a vector of catalogs in the database, where each catalog information
+  is a map with column :table-cat (depending on you 'db-to-clj' configuration)
+  and a string value (i.e. the catalog name).
+  See also:
+  http://j.mp/eNWOa8 (Java 6 API, class DatabaseMetaData, method getCatalogs)"
   [^DatabaseMetaData dm]
   (into [] (alt-resultset-seq (.getCatalogs dm))))
 
 
 (defn get-schemas
-  "Get list of schemas in database."
-  ([^DatabaseMetaData dm]
-    (into [] (alt-resultset-seq (.getSchemas dm))))
-  ([^DatabaseMetaData dm ^String catalog ^String schema-pattern]
-    (into [] (alt-resultset-seq (.getSchemas dm catalog schema-pattern)))))
+  "Retrieve a vector of schema names (each as a map of column-value pairs)
+  available in this database. The schema columns are:
+   :table-schem   - String:  schema name
+   :table-catalog - String:  catalog name (may be nil)
+   :is-default    - Boolean: whether this is the default schema
+  Args:
+   dm  (java.sql.DatabaseMetaData)
+  See also:
+  http://j.mp/gSlOtD (Java 6 API, class DatabaseMetaData, method getSchemas)"
+  [^DatabaseMetaData dm]
+  (into [] (alt-resultset-seq (.getSchemas dm))))
 
 
-;; TODO a uniform way of getting list of tables for all database products
 (defn get-tables
-  "Get list of tables in database."
+  "Return a vector of table descriptions in database. By default include only
+  all tables in current database/catalog/schema. Depending on the 'db-to-clj'
+  configuration, each table description has the following columns:
+   :table-cat                 - String: table catalog (may be nil)
+   :table-schem               - String: table schema (may be nil)
+   :table-name                - String: table name
+   :table-type                - String: table type. Typical types are
+                                        \"TABLE\", \"VIEW\", \"SYSTEM TABLE\",
+                                        \"GLOBAL TEMPORARY\", \"LOCAL TEMPORARY\",
+                                        \"ALIAS\", \"SYNONYM\".
+   :remarks                   - String: explanatory comment on the table
+   :type-cat                  - String: the types catalog (may be nil)
+   :type-schem                - String: the types schema (may be nil)
+   :type-name                 - String: type name (may be nil)
+   :self-referencing-col-name - String: name of the designated \"identifier\"
+                                        column of a typed table (may be nil)
+   :ref-generation            - String: specifies how values in SELF_REFERENCING_COL_NAME
+                                        are created. Values are \"SYSTEM\",
+                                        \"USER\", \"DERIVED\". (may be nil)
+   :sql                       - String: the SQL/DDL used to create it (may be nil)
+  Arguments:
+   dm (java.sql.DatabaseMetaData)
+  Optional arguments:
+   :catalog        (String) a catalog name; must match the catalog name as it is
+                            stored in the database; \"\" retrieves those without
+                            a catalog; nil means that the catalog name should not
+                            be used to narrow the search
+   :schema-pattern (String) short key name - :schema
+                            a schema name pattern; must match the schema name as
+                            it is stored in the database; \"\" retrieves those
+                            without a schema; nil means that the schema name
+                            should not be used to narrow the search
+   :table-pattern  (String) short key name - :table
+                            a table name pattern; must match the table name as
+                            it is stored in the database; nil selects all
+   :types          (String) a list of table types, which must be from the list
+                            of table types returned from function get-dbmetadata
+                            (key :table-types, typical values listed below);
+                            nil returns all types
+                             \"TABLE\", \"VIEW\", \"SYSTEM TABLE\",
+                             \"GLOBAL TEMPORARY\", \"LOCAL TEMPORARY\",
+                             \"ALIAS\", \"SYNONYM\"
+  See also:
+  http://j.mp/dUeYXT (Java 6 API, class DatabaseMetaData, method getTables)"
   [^DatabaseMetaData dm
    & {:keys [catalog
-             schema-pattern     schema
-             table-name-pattern table
+             schema-pattern schema
+             table-pattern  table
              types]
-      :or {catalog            nil
-           schema-pattern     nil schema nil
-           table-name-pattern nil table  nil
-           types              (into-array
-                                String ["TABLE"])}
+      :or {catalog        nil
+           schema-pattern nil schema nil
+           table-pattern  nil table  nil
+           types          (into-array
+                            String ["TABLE"])}
       :as opt}]
   {:pre [(clojure.set/subset? (set (keys opt))
            #{:catalog
              :schema-pattern :schema
-             :table-name-pattern :table
+             :table-pattern  :table
              :types})]}
   (let [rs (.getTables dm ^String catalog ^String (or schema-pattern schema)
-             ^String (or table-name-pattern table) ^"[Ljava.lang.String;" types)]
+             ^String (or table-pattern table)
+             ^"[Ljava.lang.String;" (#(or (and (coll? %) (into-array String %))
+                                        %) types))]
     (into [] (alt-resultset-seq rs))))

File src/test/clj/org/bituf/test_clj_dbspec.clj

View file
  • Ignore whitespace
   (:use clojure.test))
 
 
+(def query "SELECT 1")
+
+
+(defn make-datasource
+  []
+  (dbcp/h2-memory-datasource))
+
+
 (defn echo
   [x]
   (pp/pprint x)
 (defn exec-query
   [^Connection conn]
   (is (instance? ResultSet
-        (-> (.createStatement (:connection spec/*dbspec*))
-          (.executeQuery "SELECT 1;")))))
+        (-> (.createStatement conn)
+          (.executeQuery query)))))
 
 
 (deftest test-with-datasource-connection
   (testing "Get connection from DataSource and execute code in context"
-    (let [ds (dbcp/h2-memory-datasource)]
+    (let [ds (make-datasource)]
       (spec/with-datasource-connection ds
         (exec-query (:connection spec/*dbspec*)))))
   (testing "Create connection and execute as dbspec"
     (is (= (spec/db-to-cljident "Hello_Morris") :hello-morris))))
 
 
+(deftest test-db-introspect
+  (testing "Database introspection"
+    (let [ds (make-datasource)]
+      (spec/with-datasource-connection ds
+        (let [dm (.getMetaData (:connection spec/*dbspec*))
+              ; run tests
+              rt (fn [retval ^String case-name]
+                   (do
+                     (is (vector? retval)
+                       (format "%s returns a vector" case-name))
+                     (is (or (empty? retval) (every? spec/row? retval))
+                       (format "Every element returned by %s should be a map, found %s"
+                         case-name (with-out-str (pp/pprint retval))))))]
+          ; get-catalogs
+          (rt (spec/get-catalogs dm) "get-catalogs")
+          ; get-schemas
+          (rt (spec/get-schemas dm)  "get-schemas")
+          ; get-tables
+          (rt (spec/get-tables dm)   "get-tables")
+          (rt (spec/get-tables dm
+                :catalog        nil
+                :schema-pattern nil
+                :table-pattern  nil
+                :types          (into-array String [])) "get-tables"))))))
+
+
 (defn test-ns-hook []
   (test-make-connection)
   (test-with-datasource-connection)
   (test-clj-to-dbident)
-  (test-db-to-cljident))
+  (test-db-to-cljident)
+  (test-db-introspect))