/
mysql.clj
285 lines (241 loc) · 9.56 KB
/
mysql.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
280
281
282
283
284
285
(ns conjure.core.db.flavors.mysql
(:import [com.mysql.jdbc.jdbc2.optional MysqlDataSource]
java.text.SimpleDateFormat)
(:require [clojure.contrib.logging :as logging]
[clojure.contrib.str-utils :as str-utils]
[clojure.contrib.sql :as sql]
[conjure.core.util.loading-utils :as conjure-loading-utils]
[conjure.core.util.string-utils :as conjure-string-utils]))
(defn
#^{:doc "Returns an mysql datasource for a ."}
create-datasource
([connection-url] (create-datasource connection-url nil nil))
([connection-url username password]
(let [mysql-datasource (new MysqlDataSource)]
(. mysql-datasource setURL connection-url)
(if (and username password)
(. mysql-datasource setUser username)
(. mysql-datasource setPassword password))
mysql-datasource)))
(defn
#^{:doc "Returns a map for use in db-config."}
db-map [db-config]
(let [
;; The name of the production database to use.
dbname (:dbname db-config)
;; The name of the JDBC driver to use.
classname "com.mysql.jdbc.Driver"
;; The database type.
subprotocol "mysql"
;; The database path.
subname (str "//localhost/" dbname)
;; A datasource for the database.
datasource (create-datasource (format "jdbc:%s:%s" subprotocol subname))]
(merge db-config {
:classname classname
:subprotocol subprotocol
:subname subname
:datasource datasource })))
(defn
#^{:doc "Executes an sql string and returns the results as a sequence of maps."}
execute-query [db-spec sql-vector]
(do
(logging/debug (str "Executing query: " sql-vector))
(sql/with-connection db-spec
(sql/with-query-results rows sql-vector
(doall rows)))))
(defn
#^{:doc "Returns the given string surrounded by backquotes."}
backquote [s]
(str "`" s "`"))
(defn
#^{:doc "Returns the given key or string as valid table name. Basically turns
any keyword into a string, and replaces dashes with underscores."}
table-name [table]
(backquote (conjure-loading-utils/dashes-to-underscores (conjure-string-utils/str-keyword table))))
(defn
#^{:doc "Runs an update given the table, where-params and a record.
table - The name of the table to update.
where-params - The parameters to test for.
record - A map from strings or keywords (identifying columns) to updated values."}
update [db-spec table where-params record]
(do
(logging/debug (str "Update table: " table " where: " where-params " record: " record))
(sql/with-connection db-spec
(sql/update-values (table-name table) where-params record))))
(defn
#^{:doc "Runs an insert given the table, and a set of records.
table - The name of the table to update.
records - A map from strings or keywords (identifying columns) to updated values."}
insert-into [db-spec table & records]
(do
(logging/debug (str "insert into: " table " records: " records))
(sql/with-connection db-spec
(apply sql/insert-records (table-name table) records))))
(defn
#^{:doc "Returns true if the table with the given name exists."}
table-exists? [db-spec table]
(try
(let [results (execute-query db-spec [(str "SELECT * FROM " (table-name table) " LIMIT 1")])]
true)
(catch Exception e false)))
(defn
#^{:doc "Runs an sql select statement built from the given select-map. The valid keys are: table - the table to run the select statement on, select - the columns to return, where - the conditions"}
sql-find [db-spec select-map]
(let [table (:table select-map)
select-clause (or (:select select-map) "*")
where-clause (:where select-map)
limit-clause (:limit select-map)]
(execute-query db-spec
[(str "SELECT " select-clause " FROM " (table-name table)
(if where-clause (str " WHERE " where-clause) nil)
(if limit-clause (str " LIMIT " limit-clause) nil))])))
(defn
#^{:doc "Creates a new table with the given name and with columns based on the given specs."}
create-table [db-spec table & specs]
(do
(logging/debug (str "Create table: " table " with specs: " specs))
(sql/with-connection db-spec
(apply sql/create-table (table-name table) specs))))
(defn
#^{:doc "Returns the not null spec vector from the given mods map."}
not-null-mod [mods]
(if (:not-null mods) ["NOT NULL"] []))
(defn
#^{:doc "Returns the primary key spec vector from the given mods map."}
primary-key-mod [mods]
(if (:primary-key mods) ["PRIMARY KEY"] []))
(defn
#^{:doc "Returns the primary key spec vector from the given mods map."}
auto-increment-mod [mods]
(if (:auto-increment mods) ["AUTO_INCREMENT"] []))
(defn
#^{:doc "Returns the given key or string as valid column name. Basically turns
any keyword into a string, and replaces dashes with underscores."}
column-name [column]
(backquote (conjure-loading-utils/dashes-to-underscores (conjure-string-utils/str-keyword column))))
(defn
#^{:doc "Returns a new spec describing an integer with the given column and spec mods map. Use this method with the
create-table method.
Curently supported values for mods:
:not-null - If the value of this key resolves to true, then add this column will be forced to be not null.
:primary-key - If true, then make this column the primary key."}
integer
([column] (integer column {}))
([column mods]
(concat [(column-name column) "INT"] (not-null-mod mods) (auto-increment-mod mods) (primary-key-mod mods))))
(defn
#^{:doc "Returns a new spec describing the id for a table. Use this method with the create-table method."}
id []
(integer "id" { :not-null true, :primary-key true, :auto-increment true }))
(defn
#^{:doc "Returns a new spec describing a text with the given column and spec mods map. Use this method with the create-table method.
Curently supported values for mods is exactly the same as integer."}
belongs-to
([model] (belongs-to model {}))
([model mods]
(integer (str (column-name model) "_id") mods)))
(defn
#^{:doc "Returns a new spec describing a string with the given column and spec mods map. Use this method with the create-table method.
Curently supported values for mods:
:length - The length of the varchar, if not present then the varchar defaults to 255.
:not-null - If the value of this key resolves to true, then add this column will be forced to be not null.
:primary-key - If true, then make this column the primary key."}
string
([column] (string column { :length 255 }))
([column mods]
(let [length (get mods :length 255)
varchar (str "VARCHAR(" length ")")]
(concat [(column-name column) varchar] (not-null-mod mods) (primary-key-mod mods)))))
(defn
#^{:doc "Returns a new spec describing a text with the given column and spec mods map. Use this method with the
create-table method.
Curently supported values for mods:
None"}
text
([column] (text column {}))
([column mods]
[(column-name column) "TEXT"]))
(defn
#^{ :doc "Returns a new spec describing a date with the given column and spec mods map. Use this method with the
create-table method.
Curently supported values for mods:
None" }
date
([column] (date column {}))
([column mods]
[(column-name column) "DATE"]))
(defn
#^{ :doc "Returns a new spec describing a time with the given column and spec mods map. Use this method with the
create-table method.
Curently supported values for mods:
None" }
time-type
([column] (time-type column {}))
([column mods]
[(column-name column) "TIME"]))
(defn
#^{ :doc "Returns a new spec describing a date time with the given column and spec mods map. Use this method with the
create-table method.
Curently supported values for mods:
None" }
date-time
([column] (date-time column {}))
([column mods]
[(column-name column) "DATETIME"]))
(defn
#^{:doc "Deletes the table with the given name."}
drop-table [db-spec table]
(do
(logging/debug (str "Drop table: " table))
(sql/with-connection db-spec
(sql/drop-table (table-name table)))))
(defn
describe-table [db-spec table]
(do
(logging/debug (str "Describe table: " table))
(execute-query db-spec [(str "SHOW COLUMNS FROM " (table-name table))])))
(defn
#^{:doc "Deletes rows from the table with the given name."}
delete [db-spec table where]
(do
(logging/debug (str "Delete from " table " where " where))
(sql/with-connection db-spec
(sql/delete-rows (table-name table) where))))
(defn
#^{ :doc "Returns the string value of the given date for use in the database." }
format-date [date]
(. (new SimpleDateFormat "yyyy-MM-dd") format date))
(defn
#^{ :doc "Returns the string value of the given date as a date time for use in the database." }
format-date-time [date]
(. (new SimpleDateFormat "yyyy-MM-dd HH:mm:ss") format date))
(defn
#^{ :doc "Returns the string value of the given date as a time for use in the database." }
format-time [date]
(. (new SimpleDateFormat "HH:mm:ss") format date))
(defn
#^{:doc "Returns a database flavor for a mysql database."}
flavor []
{ :db-map db-map
:execute-query execute-query
:update update
:insert-into insert-into
:table-exists? table-exists?
:sql-find sql-find
:create-table create-table
:drop-table drop-table
:describe-table describe-table
:delete delete
:integer integer
:id id
:string string
:text text
:date date
:time-type time-type
:date-time date-time
:belongs-to belongs-to
:format-date format-date
:format-date-time format-date-time
:format-time format-time
})