I've written some sql helper functions that will do things like list
the objects in the database and describe a table. I've found these
handy when doing interactive development as I don't have to jump over
to another app to see what the make up of tables are. I've also used
it in some scenarios when generating code from the database schema.
The functions are below, they make use only of standard JDK java.sql
functions and classes. I'd like to see if there is any interest in
adding these to one of the contrib libraries - I'd be happy to clean
them up, create a patch or send a pull request (I've already submitted
a contributor agreement).
Best Regards,
Kyle Burton
(defn schemas
"Returns a list of the schema names in the database."
[db]
(sql/with-connection db
(let [schemas (.getSchemas (.getMetaData (sql/connection)))]
(loop [has-next (.next schemas)
res []]
(if has-next
(let [schema (.getString schemas 1)]
(recur (.next schemas)
(conj res schema)))
res)))))
(defn schema-objects
"Returns a list of maps describing the objects in the database. The
maps include: :catalog, :schema, :name, :type and :remarks as per
the JDBC spec."
[db schema]
(sql/with-connection db
(let [db-meta (.getMetaData (sql/connection))
;; NB: "public" is the default for postgres
tables (.getTables db-meta nil schema "%" nil)]
(loop [has-next (.next tables)
res []]
(if has-next
(let [table {:catalog (.getString tables 1)
:schema (.getString tables 2)
:name (.getString tables 3)
:type (.getString tables 4)
:remakrs (.getString tables 5)}]
(recur (.next tables)
(conj res table)))
res)))))
(defn schema-tables [db schema]
(filter #(= (:type %1) "TABLE") (schema-objects db schema)))
(defn- range-sql [end]
(range 1 (+ 1 end)))
(defn describe-table
"Returns a list of column descriptions (maps) for the table. The
maps
contain: :name, :catalog, :display-zie, :type, :precision, :scale
:is-auto-increment, :is-case-sensitive, :is-currency
:is-definitely-writable, :is-nullable, :is-read-only
:is-searchable, :is-signed, :is-writable."
[db table-name]
(sql/with-connection db
(let [ps (.prepareStatement (sql/connection) (format "SELECT * FROM
%s WHERE 0 = 1" table-name))
rs (.executeQuery ps)
rs-meta (.getMetaData rs)]
(loop [[idx & idxs] (range-sql (.getColumnCount rs-meta))
res []]
(if idx
(recur idxs (conj res {:name
(.getColumnName rs-meta idx)
:catalog
(.getCatalogName rs-meta idx)
:display-size
(.getColumnDisplaySize rs-meta idx)
:type
(.getColumnType rs-meta idx)
:precision (.getPrecision
rs-meta idx)
:scale (.getScale rs-meta idx)
:is-auto-increment
(.isAutoIncrement rs-meta idx)
:is-case-sensitive
(.isCaseSensitive rs-meta idx)
:is-currency (.isCurrency
rs-meta idx)
:is-definitely-writable
(.isDefinitelyWritable rs-meta idx)
:is-nullable (.isNullable
rs-meta idx)
:is-read-only (.isReadOnly
rs-meta idx)
:is-searchable (.isSearchable
rs-meta idx)
:is-signed (.isSigned rs-meta idx)
:is-writable (.isWritable
rs-meta idx)}))
res)))))
--
Twitter: @kyleburton
Blog: http://asymmetrical-view.com/
Fun: http://snapclean.me/
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
