◊(Local Yarn Code "Check-in [b9830871]")

Overview
Comment:Add SQL query maker to insert multiple rows
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b98308711bf1093b8ef7a8e909b8f95a58b6c2d1e22b763ce899b5419ce45157
User & Date: joel on 2019-03-04 00:38:57
Other Links: manifest | tags
Context
2019-03-08
00:01
Change scribble command in makefile so that it can link to documentation for packages installed in user scope check-in: 7e52d6a3 user: joel tags: trunk
2019-03-04
00:38
Add SQL query maker to insert multiple rows check-in: b9830871 user: joel tags: trunk
2019-03-03
22:51
Add pagetree constants to dust.rkt check-in: 5256383c user: joel tags: trunk
Changes

Modified code-docs/sqlite-tools.scrbl from [3bfd95a3] to [dda37e2d].

67
68
69
70
71
72
73








74
75
76
77
78
79
80
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88







+
+
+
+
+
+
+
+








Given a list of values, returns a single string containing the list elements in order, surrounded
by backticks and separated by commas.

@examples[#:eval my-eval
(list->sql-fields '("id" "name" "address"))
(list->sql-fields '(or use symbols))]

@defproc[(list->sql-values [lst (listof any/c)]) string?]

Given a list of values, return a string containing those values separated by commas inside a pair of
parentheses. Any string values in the list will be surrounded by quote marks.

@examples[#:eval my-eval
(list->sql-values '(0 "hello" NULL 34))]

@defproc[(list->sql-parameters [lst (listof stringish?)]) string?]

Given a list of values, return a single string with numbered parameter placeholders, suitable for
use in a parameterized SQL query.

@examples[#:eval my-eval
142
143
144
145
146
147
148








149
150
151
152
153
154
155
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171







+
+
+
+
+
+
+
+








@examples[#:eval my-eval
(make-insert/replace-query 'vals '(a b c))]

This query relies on the fact that in SQLite every table contains a @tt{rowid} column by default.
For more information on why/how the query works, see
@ext-link["https://sqlite.org/lang_insert.html"]{the SQLite docs for @tt{INSERT}}.

@defproc[(make-insert-rows-query [table stringish?] [fields (listof stringish?)] [rows (listof
(listof any/c))]) string?]

Returns a SQLite query string for inserting multiple rows.

@examples[#:eval my-eval
(make-insert-rows-query 'people '(id name) '((1 "Alice") (2 "Bob")))]

@defproc[(make-select-query [table stringish?] [fields (listof stringish?)] [#:where where-clause
stringish?]) string?]

Returns a SQLite query string for selecting rows from the database.

@examples[#:eval my-eval

Modified sqlite-tools.rkt from [7eedb68e] to [74d52a42].

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