52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
-
+
|
[int->bool (exact-integer? . -> . boolean?)]
;; Simple SQL makers
[make-table-schema ((string? (listof stringish?))
(#:primary-key-cols (listof stringish?))
. ->* . string?)]
[make-insert/replace-query (stringish? (listof stringish?) . -> . string?)]
[make-select-query (stringish? (listof stringish?) #:where string? . -> . string?)]
[make-select-query (stringish? (listof stringish?) #:where stringish? . -> . string?)]
;; Database operations
[init-db! ((pathish?) #:rest (listof string?) . ->* . void?)]
[query! ((string?) #:rest (listof any/c) . ->* . void?)]
[select-rows! (case->
(stringish? (listof stringish?) any/c . -> . (or/c empty? hash?))
(string? (listof stringish?) . -> . (or/c empty? hash?)))]))
|
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
-
+
|
primary-key))
;; Create a query that inserts a row if it doesn’t exist (based on the first
;; column only), or updates it if it does. The returned query is parameterized,
;; and must be used with a list of values equal in length to the number of
;; fields given.
(define (make-insert/replace-query tablename fields)
(format "INSERT OR REPLACE INTO `~a` (`rowid`, ~a) values ((SELECT `rowid` FROM `~a` WHERE `~a`= ?1), ~a)"
(format "INSERT OR REPLACE INTO `~a` (`rowid`, ~a) VALUES ((SELECT `rowid` FROM `~a` WHERE `~a`= ?1), ~a)"
tablename
(list->sql-fields fields)
tablename
(first fields)
(list->sql-parameters fields)))
;; Simple row selection
|
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
|
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
|
-
+
+
|
(check-equal? (make-table-schema "posts" '("title" "date"))
"CREATE TABLE IF NOT EXISTS `posts` (`title`, `date`, PRIMARY KEY (`title`));")
(check-equal? (make-table-schema 'posts '(title date) #:primary-key-cols '(author date))
"CREATE TABLE IF NOT EXISTS `posts` (`title`, `date`, PRIMARY KEY (`author`, `date`));")
(check-equal? (make-insert/replace-query 'posts '(author title))
(string-append "INSERT OR REPLACE INTO `posts` (`rowid`, `author`, `title`) "
"values ((SELECT `rowid` FROM `posts` WHERE `author`= ?1), ?1, ?2)"))
"VALUES ((SELECT `rowid` FROM `posts` WHERE `author`= ?1), ?1, ?2)"))
(check-equal? (make-select-query 'posts '(author title) #:where 1)
"SELECT `author`, `title` FROM `posts` WHERE 1"))
(define (good-connection?)
(and (connection? (sqltools:dbc)) (connected? (sqltools:dbc))))
;; Initialize the database connection, creating the database if it does not yet exist
;; and running any provided queries (e.g., "CREATE TABLE IF NOT EXISTS...")
(define (init-db! filename . qs)
(cond [(good-connection?) (disconnect (sqltools:dbc))])
(sqltools:dbc (sqlite3-connect #:database filename #:mode 'create))
(unless (empty? qs)
(for ([q (in-list qs)])
(query! q))))
;; Run a query with logging (if enabled) and return the result
(define (query! q . parameters)
|
