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
|
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
|
+
+
+
+
+
+
+
+
+
|
(provide
(contract-out
;; Utility functions
[log-query (string? . -> . void?)]
[vector->hash (vector? (listof symbolish?) . -> . hash?)]
[backtick (stringish? . -> . string?)]
[list->sql-fields ((listof stringish?) . -> . string?)]
[list->sql-values ((listof stringish?) . -> . string?)]
[list->sql-parameters ((listof any/c) . -> . string?)]
[bool->int (any/c . -> . exact-integer?)]
[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-insert-rows-query (stringish? (listof stringish?) (listof (listof stringish?)) . -> . 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?)))]))
;; ~~~ Private use ~~~
(define uninitialized-connection "No DB connection!")
(define (weave xs ys)
(for/fold [(woven null)
#:result (reverse woven)]
([x (in-list xs)]
[y (in-list ys)])
(cons y (cons x woven))))
(define (sql-val v)
(cond [(string? v) (string-append "\"" v "\"")]
[else (format "~a" v)]))
;; ~~~ Provided parameters ~~~
(define sqltools:dbc (make-parameter uninitialized-connection))
(define sqltools:log-queries? (make-parameter #f))
;; ~~~ Provided utility functions ~~~
(define (backtick str) (format "`~a`" str))
(define (list->sql-fields fields) (apply string-append (add-between (map backtick fields) ", ")))
(define (list->sql-values vals)
(string-append "(" (apply string-append (add-between (map sql-val vals) ", ")) ")"))
(define (list->sql-parameters fields)
(apply string-append (add-between (map (λ(x) (format "?~a" (add1 x))) (range (length fields))) ", ")))
;; For storing/reading boolean values (SQLite uses integers)
(define (bool->int b?)
(cond [b? 1] [else 0]))
(define (int->bool i)
(not (= i 0)))
;; TESTING: utility functions…
(module+ test
(check-equal? (backtick "field") "`field`")
(check-equal? (list->sql-fields '("f1" "f2" "f3")) "`f1`, `f2`, `f3`")
(check-equal? (list->sql-fields '(f1 f2 f3)) "`f1`, `f2`, `f3`") ; Can use symbols too
(check-equal? (list->sql-parameters '("name" "rank" "serial")) "?1, ?2, ?3")
(check-equal? (list->sql-parameters '(name rank serial)) "?1, ?2, ?3")
(check-equal? (list->sql-values '(100 "hello" 3)) "(100, \"hello\", 3)")
(check-equal? (weave '(x y z) '(1 2 3)) '(x 1 y 2 z 3))
(check-equal? (bool->int #f) 0)
(check-equal? (bool->int #t) 1)
(check-equal? (bool->int "xblargh") 1)
(check-equal? (int->bool 0) #f)
(check-equal? (int->bool 1) #t)
|
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
|
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
|
+
+
+
+
+
+
+
+
+
+
|
(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)))
;; Create a query that inserts multiple rows.
(define (make-insert-rows-query tablename fields rows)
(define row-values
(apply string-append (add-between (map list->sql-values rows) ", ")))
(format "INSERT INTO `~a` (~a) VALUES ~a;" tablename (list->sql-fields fields) row-values))
;; Simple row selection
(define (make-select-query table fields #:where where-clause)
(format "SELECT ~a FROM `~a` WHERE ~a"
(list->sql-fields fields)
table
where-clause))
;; TESTING: SQL query makers...
(module+ test
(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"))
"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)"))
(check-equal? (make-insert-rows-query 'series '(id name num) '((1 "Journal" 11) (2 "Ideas" 4)))
(string-append "INSERT INTO `series` (`id`, `name`, `num`) "
"VALUES (1, \"Journal\", 11), (2, \"Ideas\", 4);"))
(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
|
