sql-table.scm: High-Level Database Library

sql-table.scm: High-Level Database Library

by David Fisher (
Version 0.1

    Copyright (C) 2007  David Fisher

    This library is free software; you can redistribute it and/or
    modify it under the terms of the GNU Lesser General Public
    License as published by the Free Software Foundation; either
    version 2.1 of the License, or (at your option) any later version.

    This library is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  
    02110-1301  USA


The package sql-table provides a more Scheme-y interface to a SQL
database.  With sql-table, a single line of Scheme code defines 95% of
the queries necessary for a typical database application.  This package
depends on Hans Oesterholt's sqli/sqld package.

The metaphor used for a SQL table is a set of records.  The actions
permitted on a SQL table are: retrieving records from a table, adding
a record to a table, getting a field from a record, and altering a
field in a record.

The define-table macro defines the functions necessary to manipulate a
SQL table.

(define-table <table> (<field> ...) (<index> ...))

This defines a table with fields <field> ... and indices <index> ...
It defines a number of functions to interact with a table that has
this form in a database, but does not by itself create, alter or
verify any external database.  

For example, the declaration

(define-table user ((id integer) name password) (id name))

defines a table name user with three fields: id, name and password.
This table has two indices, id and name.  The index id is known as the
primary index.

A table definition with n fields and m indices defines 2n+m functions,
and one macro.  An index consists of one or more fields.  There are
two ways to declare an index:

* <field> : this declares an index consisting of a single field.
* (<field> ...) : this declares a compound index consisting of the
  fields <field> ...

For each index, define-table defines a function:

* get-<table>-by-<index> : this gets a list of records where the
  <index> field matches the argument to the function.  For example,
  for the table user presented above, define-table defines two
  functions, get-user-by-id, and get-user-by-name.
  Invocations of get-user-by-name have the form

  (get-user-by-name <sqli> <name>) => (<user> ...)

  For example, in order to get all of the users name "bill" from
  the sqli database db-hook, one would write

  (get-user-by-name db-hook "bill")

  This returns a list of records.  In order to get or set fields from
  these records, it is necessary to use the other funtions defined by

  For compound indices, the getter function uses the names of all
  fields, separated by hypens.  For example, if the user example had
  an index (name password), define-table would declare a function
  get-user-by-name-password, that would be invoked
  (get-user-by-name-password "bill" "llib").

There are three ways to define a field.

* <name> : this defines a field named <name>, that corresponds to a
  SQL column named <name>, that contains a string.
* (<name> <type>) : this defines a field named <name>, that
  corresponds to a SQL column named <name>, that contains an object of
  type <type>.  SQL types are covered in more depth under
* (<name> <column> <type>) : this defines a field named <name>, that
  corresponds to a SQL column named <column>, that contains an object
  of type <type>.

For each field, define-table defines two functions.

* <table>-<field> : this gets the value of field <field> from records
  of table <table>.  So, in the user example, one would get the
  passwords of all users named "bill" with the Scheme expression

  (map user-password (get-user-by-name "bill"))

* set-<table>-<field>! : this sets the value of field <field> in
  records of table <table>.  In order to reset the passwords of all
  users named bill, one would use the Scheme code

  (map (lambda (user) (set-user-password! user "")) (get-user-by-name "bill"))

Adding a record to a table is a bit more complex.  The define-table
macro defines another macro, add-<table>.

* (add-<table> <field> ...) : this evaluates into a function that adds
  a record into a table and sets the fields <field> ... to its
  arguments.  The rest of of the fields are set to their
  table-dependent default.  For example, to add a new user with name
  "bill" and password "llib" to a table specified by the sqli object
  db-hook, one would use the expression

  ((add-user name password) db-hook "bill" "llib")

  This expression does not specify the value of the field id.  It is
  up to the DBMS to determine the value of that field.

Each field in a table has a type.  There are a number of types defined
initially, including

* string
* integer

Additional types can be defined by the define-sql-type keyword.

(define-sql-type <type> <contract> <to-string> <from-string>)

This statement defines a type named <type>.  The define-sql-type
statement must be called with three values:

* <contract>, a contract that matches values of the type
* <to-string>, a function that converts values of the type into strings
* <from-string>, a function that converts string into values of the type

For example, to define a three-valued type, one would use the

(define-sql-type ternary-bit (one-of/c #t #f 'maybe)
		 	     (lambda (x)
			         ((eq? x 'maybe) "m")
				 (x "t")
				 (#t "f")))
                             (lambda (x)
			         ((equal? x "m") 'maybe)
				 ((equal? x "t") #t)
				 (#t #f))))

(define-sql-default-type <contract> <to-string> <from-string>)

This defines the type to use if the type part of a field declaration
is not present.  The arguments work identically to define-sql-type.