sql: revamped interfaces, fixed bugs, improved examples

2 files changed, 71 insertions, 45 deletions

diff --git a/src/clojure/contrib/sql/sql.clj b/src/clojure/contrib/sql/sql.clj
index 467e6e16..e1becf56 100644
--- a/src/clojure/contrib/sql/sql.clj
+++ b/src/clojure/contrib/sql/sql.clj
@@ -1,10 +1,10 @@
-;;  Copyright (c) Stephen C. Gilardi. All rights reserved.
-;;  The use and distribution terms for this software are covered by the
-;;  Common Public License 1.0 (http://opensource.org/licenses/cpl.php)
-;;  which can be found in the file CPL.TXT at the root of this distribution.
-;;  By using this software in any fashion, you are agreeing to be bound by
-;;  the terms of this license.
-;;  You must not remove this notice, or any other, from this software.
+;;  Copyright (c) Stephen C. Gilardi. All rights reserved. The use and
+;;  distribution terms for this software are covered by the Common Public
+;;  License 1.0 (http://opensource.org/licenses/cpl.php) which can be found
+;;  in the file CPL.TXT at the root of this distribution. By using this
+;;  software in any fashion, you are agreeing to be bound by the terms of
+;;  this license. You must not remove this notice, or any other, from this
+;;  software.
 ;;
 ;;  sql.clj
 ;;
@@ -13,17 +13,16 @@
 ;;  See clojure.contrib.sql.test for an example
 ;;
 ;;  scgilardi (gmail)
-;;  23 April 2008
+;;  Created 2 April 2008
 
 (ns clojure.contrib.sql
   (:import
    (java.sql DriverManager Connection PreparedStatement ResultSet)))
 
-(defn get-connection
+(defn connection
   "Attempts to get a connection to a database via a jdbc URL"
   [subprotocol db-name]
-  (let [url (str "jdbc:" subprotocol ":" db-name)]
-    (DriverManager/getConnection url)))
+  (DriverManager/getConnection (format "jdbc:%s:%s" subprotocol db-name)))
 
 (defmacro with-connection
   "Evaluates body in the context of a connection to a database. Any updates
@@ -32,24 +31,24 @@
   [con init & body]
   `(with-open ~con ~init
      (try
-      (.setAutoCommit ~con false))
+      (.setAutoCommit ~con false)
       ~@body
       (.commit ~con)
       (catch Exception e#
-             (.rollback ~con)
-             (throw (Exception. "transaction rolled back" e#)))))
+        (.rollback ~con)
+        (throw (Exception. "transaction rolled back" e#))))))
 
-(defn execute-commands
-  "Executes a sequence of SQL commands that do not return results"
-  [con commands]
+(defn do-commands
+  "Executes SQL commands that do not return results"
+  [con & commands]
   (with-open stmt (.createStatement con)
     (doseq cmd commands
       (.addBatch stmt cmd))
     (.executeBatch stmt)))
 
-(defn execute-prepared-statement
-  "Executes a prepared statement with a sequence of parameter sets"
-  [con sql sets]
+(defn do-prepared
+  "Executes a prepared statement with parameter sets"
+  [con sql & sets]
   (with-open stmt (.prepareStatement con sql)
     (doseq set sets
       (doseq [index value] (map vector (iterate inc 1) set)
@@ -57,7 +56,21 @@
       (.addBatch stmt ))
     (.executeBatch stmt)))
 
-(defmacro with-query-results
+(defn create-table
+  "Creates a table given a name and column specifications"
+  [con name & cols]
+  (do-commands con
+    (format "create table %s (%s)"
+            name
+            (apply str (interpose "," cols)))))
+
+(defn drop-table
+  "Drops a table give its name"
+  [con name]
+  (do-commands con
+    (format "drop table %s" name)))
+
+(defmacro with-results
   "Executes a query and then evaluates body repeatedly with rec bound to
   each of the generated results in turn"
   [rec con sql & body]
diff --git a/src/clojure/contrib/sql/test/test.clj b/src/clojure/contrib/sql/test/test.clj
index f098c9aa..90edd457 100644
--- a/src/clojure/contrib/sql/test/test.clj
+++ b/src/clojure/contrib/sql/test/test.clj
@@ -1,47 +1,60 @@
+;;  Copyright (c) Stephen C. Gilardi. All rights reserved. The use and
+;;  distribution terms for this software are covered by the Common Public
+;;  License 1.0 (http://opensource.org/licenses/cpl.php) which can be found
+;;  in the file CPL.TXT at the root of this distribution. By using this
+;;  software in any fashion, you are agreeing to be bound by the terms of
+;;  this license. You must not remove this notice, or any other, from this
+;;  software.
+;;
+;;  test.clj
+;;
+;;  test/example for clojure.contrib.sql.test
+;;
+;;  scgilardi (gmail)
+;;  Created 13 September 2008
+
 (ns clojure.contrib.sql.test
   (:use clojure.contrib.sql))
 
 (Class/forName "org.apache.derby.jdbc.EmbeddedDriver")
 
 (defn db []
-  (get-connection "derby" "/tmp/clojure.contrib.sql.test.db;create=true"))
-
-(defn db-drop []
-  (with-connection con (db)
-    (try                   
-     (execute-commands con
-       ["drop table fruit"])
-     (catch Exception e))))
+  (connection "derby" "/tmp/clojure.contrib.sql.test.db;create=true"))
 
 (defn db-write []
-  (db-drop)
   (with-connection con (db)
-    (execute-commands con
-      ["create table fruit (name varchar(32), appearance varchar(32), cost int, grade real)"])
-	(seq
-	 (execute-prepared-statement con
-	   "insert into fruit values (?, ?, ?, ?)"
-	   [["Apple" "red" 59 87]
-		["Banana" "yellow" 29 92.2]
-		["Peach" "fuzzy" 139 90.0]
-		["Orange" "juicy" 89 88.6]]))))
-  
+    (try
+     (drop-table con "fruit")
+     (catch Exception e))
+    (create-table con
+      "fruit"
+      "name varchar(32)"
+      "appearance varchar(32)"
+      "cost int"
+      "grade real")
+    (do-prepared con
+      "insert into fruit values (?, ?, ?, ?)"
+      ["Apple" "red" 59 87]
+      ["Banana" "yellow" 29 92.2]
+      ["Peach" "fuzzy" 139 90.0]
+      ["Orange" "juicy" 89 88.6])))
+
 (defn db-read []
   (with-connection con (db)
-    (with-query-results rec con
+    (with-results rec con
       "select * from fruit"
       (println rec))))
 
 (defn db-grade-a []
   (with-connection con (db)
-    (with-query-results rec con
+    (with-results rec con
       "select name, cost from fruit where grade >= 90"
       (println rec))))
 
 (defn db-exception []
   (with-connection con (db)
-    (execute-prepared-statement con
+    (do-prepared con
       "insert into fruit (name, appearance) values (?, ?)"
-      [["Grape" "yummy"]
-       ["Pear" "bruised"]])
+      ["Grape" "yummy"]
+      ["Pear" "bruised"])
     (throw (Exception. "an exception"))))