◊(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
...
142
143
144
145
146
147
148








149
150
151
152
153
154
155

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-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
................................................................................

@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-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







>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
...
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171

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
................................................................................

@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
..
71
72
73
74
75
76
77




78
79
80
81
82
83
84
85
86


87
88
89
90
91
92
93
..
97
98
99
100
101
102
103

104
105
106
107
108
109
110
...
162
163
164
165
166
167
168






169
170
171
172
173
174
175
...
182
183
184
185
186
187
188




189
190
191
192
193
194
195
(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-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-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?))
................................................................................
(define (weave xs ys)
  (for/fold [(woven null)
             #:result (reverse woven)]
            ([x (in-list xs)]
             [y (in-list ys)])
    (cons y (cons x woven))))





;; ~~~ 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-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]))

................................................................................
;; 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? (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)
................................................................................
  (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
(define (make-select-query table fields #:where where-clause)
  (format "SELECT ~a FROM `~a` WHERE ~a"
          (list->sql-fields fields)
          table
          where-clause))

................................................................................
  (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-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







>









>







 







>
>
>
>









>
>







 







>







 







>
>
>
>
>
>







 







>
>
>
>







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
..
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
...
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
...
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
...
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
(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?))
................................................................................
(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]))

................................................................................
;; 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)
................................................................................
  (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))

................................................................................
  (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