To use the database independent API, client code only has to import the module dbi. This module provides four logical sets of features for connection management, statement management, row data access and persistent records. If you prefer to write all SQL statements yourself, your code will likely use everything except the persistent record features. If you don't want to touch SQL at all, your code may work with just the connection management and persistent record features.
You open a database connection using the procedure connect:
(define db (connect "sqlite3:~/.fossil" #:autoload? #t))
The first argument is a connection specification that may be either a pair consisting of a symbol indicating the database driver to use and an association list containing driver specific information, or an absolute URI, or a string representing an absolute URI. URIs are automatically converted to the list format, hence the above code snippet could equivalently be written as:
(define db (connect '(sqlite3 [path . "~/.fossil"]) #:autoload? #t))
The procedure connect also accepts some keyword arguments. Specifying #:autoload? #t causes automatic loading of the driver indicated by the connection specification.
When you are done with database operations, you close the connection to the database using
By default, statements are automatically cached and reused, so you only have to create them where needed and don't have to worry about resource leakage.
To create a statement, you can use the procedure prepare:
(define stmt (prepare db #<<EOS SELECT * FROM global_config WHERE value = ~q AND ~s LIKE ~q:like; EOS 'name))
As you can see, the SQL source of the statement may contain placeholders: ~s inserts a properly quoted identifier from the symbols passed to prepare as optional arguments. ~q marks a place where a query parameter should be inserted, which can optionally be given a name by appending a colon and some symbol.
Once your statement is prepared, you can execute it using the procedure rows-fold:
(rows-fold (lambda (row seed) ...) seed stmt 1 #:like "repo:%")
rows-fold's first two arguments are a procedure called for every result row that is produced by the statement and a seed value for that procedure. The procedure and seed are used similarly to those for SRFI-1's fold procedure.
If you pass the standard procedure void as the folding procedure, the statement execution may be optimized to never call the folding procedure at all.
The remaining arguments to rows-fold specify the statement to execute and the values that should be bound to its query parameters. In addition to positional arguments, which are bound to successive query parameters in sequence, you can also pass keyword arguments that are bound to named query parameters. Note that the behaviour caused by multiple uses of the same named query parameter in the SQL source code is currently undefined.
Instead of passing a prepared statement to rows-fold you may also pass a database and an SQL source string as two separate arguments. In that case prepare is automatically called on the database and the string to produce a statement and symbols for prepare's third argument may be mixed in with the other query parameters.
You can also use the SRFI-42 comprehension :rows instead of rows-fold to iterate over the result rows of a statement. The signature of :rows is analoguous to that of rows-fold, for example:
(list-ec (:rows row db "SELECT * FROM global_config WHERE ~s LIKE ~q:like;" 'name #:like "repo:%") ...)
Row Data Access
The row records passed to folding procedures or comprehension variables are linked directly with the underlying database driver. They do not have to stay valid longer than until the next result row is produced from the same statement or all results from the same statement have been enumerated.
To extract data from result rows, you can use the procedure row-ref, that accepts a row record and either a symbolic column name or an index and returns the data found in the column. You can also convert an entire row into a list of column values using row->list, into an association list of column names and values using row->alist or into a vector of column values using row->vector.
The last example could be completed like this:
(list-ec (:rows row db "SELECT * FROM global_config WHERE ~s LIKE ~q:like;" 'name #:like "repo:%") (row->alist row))
You can define a persistent record type in a similar way as a SRFI-9 record type using the syntactic form define-persistent-record-type:
(define-persistent-record-type global_config (make-config-item name value) (config-item name) row->config-item config-item? config-item-sync! [name config-item-name] [value config-item-value config-item-value-set!])
The record name is used as the table identifier and the field keys specified after the constructors are used as column identifiers.
There are also several additions to the SRFI-9 syntax:
- The record name actually becomes a bound identifier holding a persistent record type descriptor.
- An additional constructor clause is used to indicate the fields that should be used to uniquely identify the database row corresponding to the record and a constructor retrieving a matching record from a database is defined (config-item in the above example).
- A constructor converting a result row into a persistent record is defined (row->config-item in the above example). It tries to retrieve all the fields from the row by name, but you may also pass it an optional second argument containing the column indices or names to use.
- A record synchronizer is defined (config-item-sync! in the above example). This procedure can be applied to a record instance and a database connection to synchronize the contents of the record with the database.
- In contrast to CHICKEN's SRFI-9 implementation, if a setter is defined for a record field it is also attached to the getter as a SRFI-17 setter.
You could use the above persistent record type definition to update our example database like this:
(define setting:dbi (rows-fold (lambda (row seed) (row->config-item row)) #f db "SELECT * FROM global_config WHERE name LIKE ~q;" "repo:%/dbi.fossil")) (set! (config-item-value setting:dbi) "42") (config-item-sync! setting:dbi db)
To help you manage connections between different tables, there is also the syntactic form define-persistent-record-link.