sqlite-extensions

Overview

A set of utilities and extensions for using SQLite3 with Guile Scheme.

Installation

This package depends on libsqlite3-dev and (sqlite3). Please make sure those are installed first.

Copy this folder into one of the directories in your site-dir:

scheme@(guile-user)> (%site-dir)
$5 = ("/usr/local/share/guile/site/3.0/" "/usr/share/guile/3.0" "/usr/share/guile/site/3.0" "/usr/share/guile/site" "/usr/share/guile")

Check the value of executing %site-dir in your guile repl.

Modules

(sqlite-extensions base)

scheme procedure: (query db-filename sql . args)

Run SQL on the SQLite database pointed to by DB-FILENAME, replacing parameters in SQL with ARGS, and return a list of hash tables with the requested data.

scheme procedure: (execute-non-query! db-filename sql . args)

Run SQL on the SQLite database pointed to by DB-FILENAME, replacing parameters in SQL with ARGS, returns unspecified. Recommended for use with side-effects.

scheme procedure: (execute-scalar db-filename sql . args)

Run SQL on the SQLite database pointed to by DB-FILENAME, replacing parameters in SQL with ARGS. Returns the value in the first column of the first row of results as a value.

scheme procedure: (execute-batch! db-filename sql-forms)

Run each of the SQL-FORMS on DB-FILENAME.

SQL-FORMS are of the syntax (SQL . args)

Return value is unspecified.

scheme procedure: (table-exists? db-filename tablename)

Check if the table TABLENAME exists in DB-FILENAME, returns a boolean.

(sqlite-extensions records)

scheme syntax: define-sqlite-table-record-type

(define-sqlite-view-record-type type-name
  view-name
  (constructor fieldname ...)
  predicate
  view-query
  all-selector by-id-selector
  (fieldname accessor sql-column-name) ...  )

Create a new record type backed by a SQLite table, and make various ‘define’s for using it. This syntax can only occur at the top-level, not nested within some other form. See the documentation for SRFI-9 in the Guile Reference Manual for more details, especially for CONSTRUCTOR, PREDICATE, ACCESSOR and MODIFIER.

TABLE-NAME is the name to use for the CREATE TABLE statement generated by this special form.

ALL-SELECTOR is bound to a function to be called as ‘(ALL-SELECTOR db-filename)’ that returns all record objects mapped to the SQLite table mapped to TYPE-NAME where DB-FILENAME is a string pointing to a sqlite database.

BY-ID-SELECTOR is bound to a procedure to be called as ‘(BY-ID-SELECTOR db-filename id)’ that returns the record object matching ID

BY-ID-DELETOR is bound to a procedure to be called as ‘(BY-ID-SELECTOR db-filename id)’ that deletes the record object matching ID in the SQlite table mapped to TYPE-NAME located in the database in DB-FILENAME

One can view the generate table definition for the record type by using the generated TYPE-NAME-table-definition variable.

The generated procedure CONSTRUCTOR-from-db-row can be used with ‘query’ from (sqlite-extensions base) to automatically map the raw hash table results into the record type,

scheme syntax: define-sqlite-view-record-type

(define-sqlite-view-record-type type-name
  view-name
  (constructor fieldname ...)
  predicate
  view-query
  all-selector by-id-selector
  (fieldname accessor sql-column-name) ...)

Create a record type backed by a SQLite View, and make various ‘define’s for using it. This syntax can only occur at the top-level, not nested within some other form. See the documentation for SRFI-9 in the Guile Reference Manual for more details, especially for CONSTRUCTOR, PREDICATE, ACCESSOR and MODIFIER.

VIEW-NAME is the string used by the CREATE VIEW statement generated by this special form.

VIEW-QUERY is the sql query used to populate the view, it cannot have semicolons in it.

ALL-SELECTOR and BY-ID-SELECTOR are the same as ‘define-sqlite-table-record-type’, but does not have deletors nor accept modifiers in its field specifications because Views in SQL are read-only due to their nature as being the result of an query, as opposed to having access to the physical storage of the data you’re working with.

One can get the view definition generated by the special form with the auto-generated variable TYPE-NAME-view-definition.

The procedure CONSTRUCTOR-from-db-row is also defined in case of queries resulting in similarly-shaped data.