#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" "../dust.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]. @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?)]) hash?] [(select-rows! [table stringish?] [fieldnames (listof stringish?)] [where-clause stringish?]) 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 hash table whose keys are the field names supplied. In the first form, @racket[_fieldnames] should list all the fields that will be returned by the query, in order, since this list will be used as the keys for the hash table result.