blob: e4dbd5be160bd6ab428b0f1fade80551db89e2db (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
;; 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
;;
;; A Clojure interface to sql databases via jdbc
;;
;; See clojure.contrib.sql.test for an example
;;
;; scgilardi (gmail)
;; Created 2 April 2008
(ns clojure.contrib.sql
(:use clojure.contrib.except
clojure.contrib.sql.internal))
(defmacro with-connection
"Evaluates body in the context of a new connection to a database then
closes it. db-spec is a map containing string values for these required
keys:
:classname the jdbc driver class name
:subprotocol the jdbc subprotocol
:subname the jdbc subname
db-spec may contain additional key-value pairs that are passed along to
the driver as properties such as :user, :password, etc."
[db-spec & body]
`(do
(Class/forName (:classname ~db-spec))
(with-open [con#
(java.sql.DriverManager/getConnection
(format "jdbc:%s:%s" (:subprotocol ~db-spec) (:subname ~db-spec))
(properties (dissoc ~db-spec :classname :subprotocol :subname)))]
(binding [*db* (assoc *db* :connection con# :level 0)]
~@body))))
(defmacro transaction
"Evaluates body as a transaction on the open database connection. Updates
are committed together as a group after evaluating body or rolled back on
any uncaught exception. Any nested transactions will be absorbed into the
outermost transaction."
[& body]
`(let [con# (connection)
level# (:level *db*)]
(binding [*db* (assoc *db* :level (inc level#))]
(let [auto-commit# (.getAutoCommit con#)]
(when (zero? level#)
(.setAutoCommit con# false))
(try
(let [value# (do ~@body)]
(when (zero? level#)
(.commit con#))
value#)
(catch Exception e#
(.rollback con#)
(throw (Exception.
(format "transaction rolled back: %s"
(.getMessage e#)) e#)))
(finally
(when (zero? level#)
(.setAutoCommit con# auto-commit#))))))))
(defn do-commands
"Executes SQL commands that don't return results on the open database
connection"
[& commands]
(with-open [stmt (.createStatement (connection))]
(doseq [cmd commands]
(.addBatch stmt cmd))
(.executeBatch stmt)))
(defn do-prepared
"Executes a prepared statement on the open database connection with
parameter sets"
[sql & sets]
(with-open [stmt (.prepareStatement (connection) sql)]
(doseq [set sets]
(doseq [[index value] (map vector (iterate inc 1) set)]
(.setObject stmt index value))
(.addBatch stmt))
(.executeBatch stmt)))
(defn create-table
"Creates a table on the open database connection given a name (a string
or keyword) and column specs. A column spec is a vector containing a name
and optionally a type and other items such as constraints, each a string
or keyword."
[name & column-specs]
(do-commands
(format "create table %s (%s)"
(the-str name)
(apply str
(map the-str
(apply concat
(interpose [", "]
(map (partial interpose " ") column-specs))))))))
(defn drop-table
"Drops a table on the open database connection given its name (a string
or keyword)"
[name]
(do-commands
(format "drop table %s" (the-str name))))
(defn insert-values
"Inserts values into columns of a table. columns is a vector of column
names (strings or keywords) and each value is a vector of values for
those columns. To insert complete rows (all columns), use insert-rows."
[table column-names & values]
(let [count (count (first values))
template (apply str (interpose "," (replicate count "?")))
columns (if (seq column-names)
(format "(%s)"
(apply str (interpose "," (map the-str column-names))))
"")]
(apply do-prepared
(format "insert into %s %s values (%s)"
(the-str table) columns template)
values)))
(defn insert-rows
"Inserts complete rows into a table. Each row is a vector of values for
each of the table's columns in order."
[table & rows]
(apply insert-values table nil rows))
(defmacro with-results
"Executes a query and then evaluates body with results bound to a seq of
the results"
[results sql & body]
`(with-open [stmt# (.prepareStatement (connection) ~sql)]
(with-open [rset# (.executeQuery stmt#)]
(let [~results (resultset-seq rset#)]
~@body))))
|
