/
sql.clj
279 lines (238 loc) · 11.4 KB
/
sql.clj
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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
;; copyright (c) 2019 Sean Corfield, all rights reserved
(ns next.jdbc.sql
"Some utility functions that make common operations easier by
providing some syntactic sugar over `execute!`/`execute-one!`.
This is intended to provide a minimal level of parity with
`clojure.java.jdbc` (`insert!`, `insert-multi!`, `query`, `find-by-keys`,
`get-by-id`, `update!`, and `delete!`).
For anything more complex, use a library like HoneySQL
https://github.com/jkk/honeysql to generate SQL + parameters.
The following options are supported:
* `:table-fn` -- specify a function used to convert table names (strings)
to SQL entity names -- see the `next.jdbc.quoted` namespace for the
most common quoting strategy functions,
* `:column-fn` -- specify a function used to convert column names (strings)
to SQL entity names -- see the `next.jdbc.quoted` namespace for the
most common quoting strategy functions.
In addition, `find-by-keys` supports `:order-by` to add an `ORDER BY`
clause to the generated SQL."
(:require [clojure.string :as str]
[next.jdbc :refer [execute! execute-one!]]))
(set! *warn-on-reflection* true)
(defn- by-keys
"Given a hash map of column names and values and a clause type
(`:set`, `:where`), return a vector of a SQL clause and its parameters.
Applies any `:column-fn` supplied in the options."
[key-map clause opts]
(let [entity-fn (:column-fn opts identity)
[where params] (reduce-kv (fn [[conds params] k v]
(let [e (entity-fn (name k))]
(if (and (= :where clause) (nil? v))
[(conj conds (str e " IS NULL")) params]
[(conj conds (str e " = ?")) (conj params v)])))
[[] []]
key-map)]
(assert (seq where) "key-map may not be empty")
(into [(str (str/upper-case (name clause)) " "
(str/join (if (= :where clause) " AND " ", ") where))]
params)))
(defn- as-keys
"Given a hash map of column names and values, return a string of all the
column names.
Applies any `:column-fn` supplied in the options."
[key-map opts]
(str/join ", " (map (comp (:column-fn opts identity) name) (keys key-map))))
(defn- as-?
"Given a hash map of column names and values, or a vector of column names,
return a string of `?` placeholders for them."
[key-map opts]
(str/join ", " (repeat (count key-map) "?")))
(defn- for-order-col
"Given a column name, or a pair of column name and direction,
return the sub-clause for addition to `ORDER BY`."
[col opts]
(let [entity-fn (:column-fn opts identity)]
(cond (keyword? col)
(entity-fn (name col))
(and (vector? col) (= 2 (count col)) (keyword? (first col)))
(str (entity-fn (name (first col)))
" "
(or (get {:asc "ASC" :desc "DESC"} (second col))
(throw (IllegalArgumentException.
(str ":order-by " col
" expected :asc or :desc")))))
:else
(throw (IllegalArgumentException.
(str ":order-by expected keyword or keyword pair,"
" found: " col))))))
(defn- for-order
"Given an `:order-by` vector, return an `ORDER BY` clause."
[order-by opts]
(when-not (vector? order-by)
(throw (IllegalArgumentException. ":order-by must be a vector")))
(assert (seq order-by) ":order-by may not be empty")
(str "ORDER BY "
(str/join ", " (map #(for-order-col % opts) order-by))))
(defn- for-query
"Given a table name and either a hash map of column names and values or a
vector of SQL (where clause) and its parameters, return a vector of the
full `SELECT` SQL string and its parameters.
Applies any `:table-fn` / `:column-fn` supplied in the options."
[table where-params opts]
(let [entity-fn (:table-fn opts identity)
where-params (if (map? where-params)
(by-keys where-params :where opts)
(into [(str "WHERE " (first where-params))]
(rest where-params)))]
(into [(str "SELECT * FROM " (entity-fn (name table))
" " (first where-params)
(when-let [order-by (:order-by opts)]
(str " " (for-order order-by opts))))]
(rest where-params))))
(defn- for-delete
"Given a table name and either a hash map of column names and values or a
vector of SQL (where clause) and its parameters, return a vector of the
full `DELETE` SQL string and its parameters.
Applies any `:table-fn` / `:column-fn` supplied in the options."
[table where-params opts]
(let [entity-fn (:table-fn opts identity)
where-params (if (map? where-params)
(by-keys where-params :where opts)
(into [(str "WHERE " (first where-params))]
(rest where-params)))]
(into [(str "DELETE FROM " (entity-fn (name table))
" " (first where-params))]
(rest where-params))))
(defn- for-update
"Given a table name, a vector of column names to set and their values, and
either a hash map of column names and values or a vector of SQL (where clause)
and its parameters, return a vector of the full `UPDATE` SQL string and its
parameters.
Applies any `:table-fn` / `:column-fn` supplied in the options."
[table key-map where-params opts]
(let [entity-fn (:table-fn opts identity)
set-params (by-keys key-map :set opts)
where-params (if (map? where-params)
(by-keys where-params :where opts)
(into [(str "WHERE " (first where-params))]
(rest where-params)))]
(-> [(str "UPDATE " (entity-fn (name table))
" " (first set-params)
" " (first where-params))]
(into (rest set-params))
(into (rest where-params)))))
(defn- for-insert
"Given a table name and a hash map of column names and their values,
return a vector of the full `INSERT` SQL string and its parameters.
Applies any `:table-fn` / `:column-fn` supplied in the options."
[table key-map opts]
(let [entity-fn (:table-fn opts identity)
params (as-keys key-map opts)
places (as-? key-map opts)]
(assert (seq key-map) "key-map may not be empty")
(into [(str "INSERT INTO " (entity-fn (name table))
" (" params ")"
" VALUES (" places ")")]
(vals key-map))))
(defn- for-insert-multi
"Given a table name, a vector of column names, and a vector of row values
(each row is a vector of its values), return a vector of the full `INSERT`
SQL string and its parameters.
Applies any `:table-fn` / `:column-fn` supplied in the options."
[table cols rows opts]
(assert (apply = (count cols) (map count rows))
"column counts are not consistent across cols and rows")
;; to avoid generating bad SQL
(assert (seq cols) "cols may not be empty")
(assert (seq rows) "rows may not be empty")
(let [table-fn (:table-fn opts identity)
column-fn (:column-fn opts identity)
params (str/join ", " (map (comp column-fn name) cols))
places (as-? (first rows) opts)]
(into [(str "INSERT INTO " (table-fn (name table))
" (" params ")"
" VALUES "
(str/join ", " (repeat (count rows) (str "(" places ")"))))]
cat
rows)))
(defn insert!
"Syntactic sugar over `execute-one!` to make inserting hash maps easier.
Given a connectable object, a table name, and a data hash map, inserts the
data as a single row in the database and attempts to return a map of generated
keys."
([connectable table key-map]
(insert! connectable table key-map {}))
([connectable table key-map opts]
(execute-one! connectable
(for-insert table key-map opts)
(merge {:return-keys true} opts))))
(defn insert-multi!
"Syntactic sugar over `execute!` to make inserting columns/rows easier.
Given a connectable object, a table name, a sequence of column names, and
a vector of rows of data (vectors of column values), inserts the data as
multiple rows in the database and attempts to return a vector of maps of
generated keys.
Note: this expands to a single SQL statement with placeholders for every
value being inserted -- for large sets of rows, this may exceed the limits
on SQL string size and/or number of parameters for your JDBC driver or your
database!"
([connectable table cols rows]
(insert-multi! connectable table cols rows {}))
([connectable table cols rows opts]
(if (seq rows)
(execute! connectable
(for-insert-multi table cols rows opts)
(merge {:return-keys true} opts))
[])))
(defn query
"Syntactic sugar over `execute!` to provide a query alias.
Given a connectable object, and a vector of SQL and its parameters,
returns a vector of hash maps of rows that match."
([connectable sql-params]
(query connectable sql-params {}))
([connectable sql-params opts]
(execute! connectable sql-params opts)))
(defn find-by-keys
"Syntactic sugar over `execute!` to make certain common queries easier.
Given a connectable object, a table name, and either a hash map of
columns and values to search on or a vector of a SQL where clause and
parameters, returns a vector of hash maps of rows that match.
If the `:order-by` option is present, add an `ORDER BY` clause. `:order-by`
should be a vector of column names or pairs of column name / direction,
which can be `:asc` or `:desc`."
([connectable table key-map]
(find-by-keys connectable table key-map {}))
([connectable table key-map opts]
(execute! connectable (for-query table key-map opts) opts)))
(defn get-by-id
"Syntactic sugar over `execute-one!` to make certain common queries easier.
Given a connectable object, a table name, and a primary key value, returns
a hash map of the first row that matches.
By default, the primary key is assumed to be `id` but that can be overridden
in the five-argument call."
([connectable table pk]
(get-by-id connectable table pk :id {}))
([connectable table pk opts]
(get-by-id connectable table pk :id opts))
([connectable table pk pk-name opts]
(execute-one! connectable (for-query table {pk-name pk} opts) opts)))
(defn update!
"Syntactic sugar over `execute-one!` to make certain common updates easier.
Given a connectable object, a table name, a hash map of columns and values
to set, and either a hash map of columns and values to search on or a vector
of a SQL where clause and parameters, perform an update on the table."
([connectable table key-map where-params]
(update! connectable table key-map where-params {}))
([connectable table key-map where-params opts]
(execute-one! connectable
(for-update table key-map where-params opts)
opts)))
(defn delete!
"Syntactic sugar over `execute-one!` to make certain common deletes easier.
Given a connectable object, a table name, and either a hash map of columns
and values to search on or a vector of a SQL where clause and parameters,
perform a delete on the table."
([connectable table where-params]
(delete! connectable table where-params {}))
([connectable table where-params opts]
(execute-one! connectable (for-delete table where-params opts) opts)))