sdbi.txt

Many thanks to https://www.thesaurus.com!


The Scheme Database Interface (sdbi) API supports databases with text
query languages that supply rectangular AKA as tabular results.


Opaque object types:

chain (of components that make up a "tech stack"), connection,
statement, result-set


Databases:

(db-chain-construct alist) -> chain

For any given database, altering the chain to get to it should not
change the following code in any way.

(db-chain-dismantle chain) -> undefined

(db-chain-adjust chain alist) -> undefined

Options for -construct and -adjust include:

(request-timeout . timespect-interval)

Set timeout in seconds and nanoseconds for each request on the
connection. Signal an error if request does not complete.

(connection-pooling . variety)

Where variety is #t to use the chain's default pooling, or a list of
procedures to supply a different pooling system, #f for none.

(db-chain? obj) -> boolean


(db-connection-open chain alist) -> connection

The connection is virtual if connection pooling is enabled by
db-chain-construct.

The alist argument maps strings to strings, and is a Schemification of
database conection strings. See connectionstrings.com for
documentation on the formats for many databases and drivers.

(db-connection? obj) -> boolean

(db-connection-close connection) -> undefined


(db-interrupt connection [some sort of time thing]) -> undefined

Attempts to interrupt any requests currently in progress on
connection. This must be called from a different thread than the
requesting thread.

~~~~ what should the optional argument be, and mean?  Interrupt after
a delay specified by an interval?  Interrupt any time after a timespec
in the future?


(db-with-transaction connection thunk) -> what thunk returns

(db-rollback connection) -> unspecified

(db-in-transaction? connection) -> boolean

Is the connection currently running a transaction?


Statements and result sets

~~~~ See if -statement and -exec can and should should be combined:

(db-statement connection ~~~~string-to-be-named bindings [config-alist]) -> statement

It is an error to mutate either the ~~~~string-to-be-named or
bindings; this enables caching of either compiled but unbound
statements, fully bound statements, or both.

config-alist could include for example do or don't prepare statement.
Cassandra allows per statement options like consistency level,
declared idempotentence,

(db-statement-free statement)

Frees any resouces associated with statement, required by e.g. Cassandra.

(db-statement? obj) -> boolean

(db-exec connection statement) -> implementation-dependent

Use this procedure when statement does not return a result set.

(db-get-result connection statement [thunk]) -> result-set or what thunk returns

Executes statement, and thunk supplied, passes it the result-set.


---------------- ~~~~ below unchanged except "sql-" -> "db-"

(db-read result-set) -> list

Returns a list of Scheme objects representing the next available row
of the result-set. NULL is represented by the symbol null.

(db-read-all result-set) -> list of lists

Returns all available rows in this result-set.

(db-for-each proc result-set) -> unspecified

Applies each result of result-set to proc for its side effects.

(db-map->list proc result-set) -> list

Applies each result of result-set to proc and returns a list of the results.

(db-fold proc knil result-set) -> any

Call proc on each result of result-set and the current state
(initially knil). Order of arguments?

(db-column-fold proc-list column-list knil-list result-set) -> any

Folds specified columns simultaneously, where column-list specifies
the names of the columns of interest, proc-list is the folding procs
corresponding to the chosen columns, and knil-list is the initial
values of the current states of each fold.


Blobs

ISSUE from John: Should this be gotten rid of? It's not strictly
necessary, but handling really large blobs without it will be
messy. Ideally it should give us ports, but adding ports to a Scheme
implementation is hard.

ANSWER from John: I think that the blob API should be generalized to
reading arrays and multisets somehow.  In both cases the idea is that
the content of a cell (row-column pair) may be too large to just
return all at once.

(db-make-blob connection table column rowid length) -> blob

Make a blob of length bytes, all of which are 0, in the specified
location. The current position of the blob is set to 0. Blobs cannot
be extended.

(db-open-blob connection table column rowid [old-blob]) -> blob

Open the blob at the specified location. The current position of the
blob is set to 0. If old-blob is provided, it may be reused.

(db-blob? obj) -> boolean

(db-blob-read blob count) -> bytevector

Reads count bytes from the current position of blob into a newly
allocated bytevector and returns it. Stops if the end of the blob is
reached.

(db-blob-read! to at blob count) -> unspecified

Reads into bytevector to starting at position at until count bytes are
read or to is full, whichever comes first.

(db-blob-read-all blob) -> bytevector

(db-blob-write blob count bytevector [start end]) -> unspecified

Writes the bytes of bytevector from start (inclusive) to end
(exclusive) at the current position of blob.

(db-blob-position blob) -> exact integer

(db-blob-set-position! blob position) -> unspecified


Exceptions

~~~~ replace this with "systems error handing" SRFI exceptiones.

(make-db-exception code message) -> db-exception

(db-exception? obj) -> boolean

(db-exception-code db-exception) -> database type-dependent object

(db-exception-message db-exception) -> string

(db-exception-connection db-exception) -> connection


Meta

This is not standardized over database types, so provided here. There
isn't much, but what there is is generally useful.

(db-tables connection) -> list of symbols

The symbols correspond to database tables (including views) accessible
to the current user.

(db-columns connection table) -> list of symbols

The symbols represent column names, and appear in ordinal position.

(db-column-type db table column) -> string

Returns the declared type of the specified table and column. The
result is a string whose possible values depend on the database
type. Note that in SQLite, because of its dynamic typing, it is not
guaranteed that the Scheme type of objects stored in the column have
the type specified by this procedure, or even that they all have the
same Scheme type.