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

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

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

  (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