#lang scribble/manual
@; Copyright (c) 2019 Joel Dueck
@;
@; Copying and distribution of this file, with or without modification,
@; are permitted in any medium without royalty provided the copyright
@; notice and this notice are preserved. This file is offered as-is,
@; without any warranty.
@(require "scribble-helpers.rkt"
scribble/example)
@(require (for-label "../pollen.rkt"
"../sqlite-tools.rkt"
racket/base
racket/contract
db
sugar/coerce))
@(define my-eval (make-base-eval))
@(my-eval '(require "sqlite-tools.rkt"))
@title{@filepath{sqlite-tools.rkt}}
@defmodule["sqlite-tools.rkt" #:packages ()]
Provides a very light set of utility functions for managing a single SQLite database. These
functions are completely agnostic as to the database schema.
It is important to note, these functions are @bold{not safe} for use with data provided by untrusted
users! In many places they use @racket[format] to blindly insert table and field names, which is
dangerous (see @secref["dbsec-sql-injection" #:doc '(lib "db/scribblings/db.scrbl")]). It’s fine for
this project, since its only use of a database is as a disposable cache that can be safely deleted
and regenerated at any time, and there are no users except me.
@section{Parameters}
@defparam[sqltools:dbc con connection? #:value "No DB connection!"]
The current database connection. This module assumes a single active connection and stores it in
this parameter so you don’t have to pass it to a function every time you execute a query. It’s
provided here so you can use it directly with the functions provided by @racketmodname[db] (all of
which are re-provided by this module for convenience).
@defboolparam[sqltools:log-queries? v #:value #f]
A kill-switch that determines whether @racket[log-query] does anything.
@section{SQL building-blocks}
@defproc[(log-query [str string?]) void?]
Prints @racket[_str] to standard output with @racket[println], but only if
@racket[(sqltools:log-queries?)] is not @code{#f}.
This is called by every function in this module that actually executes database queries (i.e., those
whose names end in @tt{!}), so if you need to you can see what’s actually being sent to the
database.
@defproc[(backtick [str stringish?]) string?]
Returns @racket[_str] surrounded by backticks.
@examples[#:eval my-eval
(backtick "field")]
@defproc[(list->sql-fields [lst (listof stringish?)]) string?]
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
(list->sql-parameters '(name rank serial))]
@deftogether[(@defproc[(bool->int [b any/c]) exact-integer?]
@defproc[(int->bool [i exact-integer?]) boolean?])]
SQLite has no “boolean” column type (in fact SQLite has no column types, period), so boolean values
must be converted to/from the values @code{1} or @code{0}. When converting @emph{from} boolean,
@code{#f} becomes @code{0} and any other value becomes @code{1}. When converting integers @emph{to}
boolean, @code{0} becomes @code{#f} and any other value becomes @code{#t}.
@examples[#:eval my-eval
(bool->int #t)
(bool->int "")
(bool->int #f)
(int->bool 1)
(int->bool -100)
(int->bool 0)]
@defproc[(vector->hash [vec vector?] [fields (listof symbolish?)]) hash?]
Returns a hash table which uses the values in @racket[_fields] as keys and the vector elements as
the values. If @racket[_vec] and @racket[_fields] do not have the same number of elements, the
returned hash will have only as many key/value pairs as the one with the fewest elements.
The functions in Racket’s @racketmodname[db] module return query results as @seclink["vectors" #:doc
'(lib "scribblings/reference/reference.scrbl")]{vectors}, but it is much easier to make use of them
as hash tables, with the field names as keys.
@examples[#:eval my-eval
(vector->hash '#("Yossarian" "Cpt" "Z-247") '(name rank serial))
(code:comment @#,elem{When v and fields are unequal in length:})
(vector->hash '#("a" "b" "c") '(1 2))
(vector->hash '#("a" "b") '(1 2 3))]
@section{Tools for making query strings}
These functions don’t actually do anything to the database. They just convert lists into various
kinds of SQL query strings. This way you can use lists to define what you want from the database
instead of writing out the queries by hand.
Again, these functions use @racket[format] to build query strings, so they aren’t safe to use with
user-supplied data; see the warning at the top of this document.
@defproc[(make-table-schema [tablename string?]
[fields (listof stringish?)]
[#:primary-key-cols pk-cols (listof stringish?) '()])
string?]
Given a table name and a list of fields, returns a string containing the SQLite-flavored query that
will create that table with those fields. If @racket[_pk-cols] is empty, the first value in
@racket[_fields] is designated in the query as the primary key.
@examples[#:eval my-eval
(make-table-schema "vals" '(a b c))
(make-table-schema "vals" '(a b c) #:primary-key-cols '(a b))]
@defproc[(make-insert/replace-query [table stringish?] [fields (listof stringish?)]) string?]
Returns a SQLite-flavored query for inserting/updating a record. The list of values to insert is
parameterized so it can be used in conjunction with a @racket[list] of values.
@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
(make-select-query 'vals '(a b c) #:where 1)]
@section{Query functions}
These functions actually execute queries on the currently active database connection stored in the
@racket[sqltools:dbc] parameter.
@defproc[(init-db! [filename (or/c path-string? 'memory 'temporary)] [query statement?] ...) void?]
Initialize the database connection using @racket[sqlite3-connect] with @racket['create] mode. The
filename may be followed by any number of queries which will immediately be executed on the
database; this can be useful for initializing tables and other setup tasks.
The database connection will be stored in the @racket[sqltools:dbc] parameter. If that parameter
already holds an active connection, it will be @racket[disconnect]ed and then replaced with the new
connection.
@defproc[(query! [query-statement statement?] [parameters any/c] ...) void?]
Executes a SQL statement using the current connection.
@defproc*[([(select-rows! [query statement?] [fieldnames (listof stringish?)]) (listof hash?)]
[(select-rows! [table stringish?] [fieldnames (listof stringish?)] [where-clause
stringish?]) (listof hash?)])]
Execute a SQL query that returns rows from the database, either using a raw query (first form
above), or using a table name, field names, and a @racket[_where-clause] (second form above).
Returns a list of hashes whose keys are the values supplied in @racket[_fieldnames].
In the first form, @racket[_fieldnames] should list @emph{in order} all the fields that will be
returned by the query. If this isn’t done, the keys for each row’s hash will likely point to values
for the wrong fields.
