Overview
| Comment: | Check for existing connection in init-db |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
bd5c6a94087f8b69ec5030c196f7e6e1 |
| 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 |
[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?)]
| | | 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 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 |
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)
| | | 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)"
tablename
(list->sql-fields fields)
tablename
(first fields)
(list->sql-parameters fields)))
;; Simple row selection
|
| ︙ | ︙ | |||
180 181 182 183 184 185 186 |
(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`) "
| | > | 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)"))
(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)
|
| ︙ | ︙ |