clj-sql

This package was written by developers who use clojure.contrib.sql but needed to add a few features to support their projects. Most of the functionality and API is inherited from clojure.contrib.sql but the following additions have been made:

• Functions to describe tables and schema objects describe-table, schemas, schema-objects
• Table and field names can include dashes e.g. :customer-invoice.
• Functions to do inserts returning autogenerated ids. insert-with-id,insert-record,do-insert
• A function to retrieve query results using a database cursor. with-query-results-cursor
• Query results with keys converted by a given function. with-query-results-keys
• Log SQL to a java.io.Writer by binding *show-sql*

Available on clojars for use with Leiningen, Cake and Maven. There is a google group for discussion and help with this package.

Example

(def *db* { ... })   ;; Uses same datasource as clojure.contrib.sql

(with-connection *db*
  (create-table
   :an-example
   [:id :integer "PRIMARY KEY" "AUTO_INCREMENT"]
   [:a-long-fieldname "varchar(255)"])
  (create-table
   :another-example
   [:id :integer "PRIMARY KEY" "AUTO_INCREMENT"]
   [:first-example :integer]
   [:another-long-fieldname "varchar(255)"]
   ["FOREIGN KEY" [:first-example] "references" :an-example [:id]))

(with-connection *db*
  (describe-table :an-example))
 
(with-connection *db*
   (with-query-results-cursor 1000 ["select * from big_table"]
     (fn [res]
       ;; Do something with 1000 maps
       )))
      
(with-connection *db*
   (with-query-results-keys res identity ["select * from a_table"]
      ;; Query results held in res will have unconverted string keys
      ))
      
(with-connection *db*
  (insert-with-id
    :an-example      {:a-long-fieldname "Example text"}
    :another-example {:first-example          (id :an-example)
                      :another-long-fieldname "More example text"}))

(binding [clj-sql.core/*show-sql* *err*]
   ;; Any commands that generate or use SQL will log the SQL to *err*
   )

Functions not in clojure.contrib.sql

describe-table

Returns a list of column descriptions (maps) for the table. The maps contain: :name, :catalog, :display-size, :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.

 (describe-table "mytable")

do-insert

Executes arbitary sql containing a single insert and returns the autogenerated id of an inserted record if available. e.g.

 (do-insert "insert into department (name) 
              select ? where not exists
              (select id from department where department = ?)"
            ["xfiles" "xfiles"])

insert-with-id

Insert records within a single transaction into the current datasource. The record format is :table { record-hash }. The record hashes can optionally access a hashmap 'id' which holds the autogenerated ids of previous inserts keyed by the table name. e.g.

  (insert-with-id 
      :department {:name "xfiles"
                   :location "secret"}
      :employee   {:department (id :department)
                   :name "Mr X"})

insert-record

Equivalent of clojure.contrib.sql/insert-records that only inserts a single record but returns the autogenerated id of that record if available.

schemas

Returns a list of the schema names in the database.

 (schemas)

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.

(schema-objects "PUBLIC")

with-query-results-cursor

Executes a query, then calls func (fn [res] ...) each time fetch-size has been retrieved from the database. The res argument to func is a seq of the results. sql-params is a vector containing a string providing the (optionally parameterized) SQL query followed by values for any parameters.

Takes an optional keyword :keyfn that will be used to map column names into keys of the returned maps.

This functions relies on the database and the JDBC driver supporting the .setFetchSize method on statement objects and is known not to use cursors with H2, Derby and Mysql.

   (with-connection db
     (with-query-results-cursor
        50
        ["select * from table where department = ?" "XFiles"]
        (fn [res]
          ;; do something with a sequence of up to 50 maps
          )
        :keyfn identity))

with-query-results-keys

Executes a query, then evaluates body with results bound to a seq of the results whose keys have been mapped with a given function. sql-params is a vector containing a string providing the (optionally parameterized) SQL query followed by values for any parameters.

 (with-query-results-keys res key-fn 
                         ["select * from table where dept = ?" "XFiles"]
      ;; Do something with the results held in res
      )