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

Overview
Comment:Check for existing connection in init-db
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: bd5c6a94087f8b69ec5030c196f7e6e1b5f5e27f770c425d950d681082badc2e
User & Date: joel on 2019-02-23 01:39:23
Other Links: manifest | tags
Context
2019-02-23
01:40
Add docs for sqlite-tools.rkt check-in: 11b31451 user: joel tags: trunk
01:39
Check for existing connection in init-db check-in: bd5c6a94 user: joel tags: trunk
2019-02-18
20:05
More work on Scribble docs check-in: a4c851a4 user: joel tags: trunk
Changes

Modified sqlite-tools.rkt from [8bbac284] to [7eedb68e].

52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
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
  [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?)]

  ;; 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?)))]))
................................................................................
          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)"
          tablename
          (list->sql-fields fields)
          tablename
          (first fields)
          (list->sql-parameters fields)))

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

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







|







 







|







 







|










>







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
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
  [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?))
                              (string? (listof stringish?) . -> . (or/c empty? hash?)))]))
................................................................................
          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)"
          tablename
          (list->sql-fields fields)
          tablename
          (first fields)
          (list->sql-parameters fields)))

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