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

#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.

@; Scribble source for the main page of the code documents.

@(require "scribble-helpers.rkt")

@title{Local Yarn: source code notes}

@author{Joel Dueck}

These are my notes about the internals of the Local Yarn source code. In other words, a personal
reference, rather than a tutorial. These pages concern only the source code itself. Refer to the
wiki for anything else.

You’ll get the most out of these notes if you have read @other-doc['(lib
"pollen/scribblings/pollen.scrbl")], and worked through the tutorials by hand.

If you’re viewing these notes on the Fossil repository, note that these pages are heavily
interlinked with the central Racket documentation at @tt{docs.racket-lang.org}, which are written
and maintained by others. Links on those pages that lead outside of that domain will not work within
this repo’s “Code Docs” frame, due to the repository’s
@ext-link["https://content-security-policy.com"]{content security policy}. To follow such links,
right-click and open the link in a new tab or window.


@local-table-of-contents[]

@include-section["pollen.scrbl"]  @; pollen.rkt
@include-section["dust.scrbl"]    @; dust.rkt
@include-section["sqlite-tools.scrbl"] @; sqlite-tools.rkt

#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-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-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.