Index: code-docs/main.scrbl ================================================================== --- code-docs/main.scrbl +++ code-docs/main.scrbl @@ -6,25 +6,32 @@ @; 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. I wrote them mainly so I can -quickly bring myself back up to speed after long absences from the code. In other words, a personal -reference, rather than a tutorial. 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 viewing these notes on the Fossil repository, links that lead out to sites other than -@tt{docs.racket-lang.org} will not work within the “Code Docs” frame, due to the repository’s -@link["https://content-security-policy.com"]{content security policy}. To follow such links, +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 ADDED code-docs/sqlite-tools.scrbl Index: code-docs/sqlite-tools.scrbl ================================================================== --- code-docs/sqlite-tools.scrbl +++ code-docs/sqlite-tools.scrbl @@ -0,0 +1,186 @@ +#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.