sql.clj

(ns extended.sql
  "Extensions to clojure.contrib.sql that handle autogenerated ids."
  (:use clojure.contrib.sql)
  ;; TODO: change to clojure.contrib.string for 1.2
  (:use [clojure.contrib.java-utils :only [as-str]]))


;;==== Internal functions ======================================================

(defn- join 
  "Joins the items in the given collection into a single string separated
   with the string separator."
  [separator col]
  (apply str (interpose separator col)))

(defn- sql-for-insert 
  "Converts a table identifier (keyword or string) and a hash identifying
   a record into an sql insert statement compatible with prepareStatement
    Returns [sql values-to-insert]"
  [table record]
  (let [table-name (as-str table)
        columns (map as-str (keys record))
        values (vals record)
        n (count columns)
        template (join "," (replicate n "?"))
        column-names (join "," columns)
        sql (format "insert into %s (%s) values (%s)"
                    table-name column-names template)]
    [sql values]))


;;==== Functions/macros for use by macros ======================================

(defn run-chained 
  "Runs the given database insert functions on the given
   database spec within a transaction. Each function is passed a hash
   identifying the keys of the previous inserts."
  [db insert-fns]
  (with-connection db
    (transaction
      (loop [id {}
             todo insert-fns]
        (if (empty? todo)
          id
          (let [[table insert-fn] (first todo)
                inserted-id (insert-fn id)]
            (recur (assoc id table inserted-id)
                   (rest todo))))))))

(defmacro build-insert-fns 
  "Converts a vector of [:table { record }] into a vector of database
   insert functions."
  [table-records]
  (vec
   (for [[table record] (partition 2 table-records)]
     `[~table (fn [~'id]
                (insert-record ~table ~record))])))


;;==== Functions/macros for external use =======================================

(defn 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."
  [table record]
  (let [[sql values] (sql-for-insert table record)]
    (with-open [statement (.prepareStatement (connection) sql)]
      (doseq [[index value] (map vector (iterate inc 1) values)]
              (.setObject statement index value))
      (.execute statement)
      (if-let [rs (.getGeneratedKeys statement)]
        (if (.next rs)
          (if-let [id (.getObject rs 1)]
            id
            nil)
          nil)
        nil))))

                          
(defmacro insert-with-id 
  "Insert records within a single transaction into the database described by 
   the given db spec. 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 db-spec
          :department {:name \"xfiles\"
                       :location \"secret\"}
          :employee   {:department (id :department)
                       :name \"Mr X\"})"
  [db & table-records]
  `(let [insert-fns# (build-insert-fns ~table-records)]
     (run-chained ~db insert-fns#)))