This is fantastic! Does it work reliably for most JDBC drivers? Some
drivers may not implement meta data API very well, and some drivers
may have their own idiosyncrasies. You can perhaps setup a GitHub
project and push to Clojars if this doesn't get into contrib any time
soon.
Regards,
Shantanu
On Oct 15, 12:16 am, "Kyle R. Burton" <kyle.bur...@gmail.com> wrote:
> 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
