sql-table.scm: High-Level Database Library

sql-table.scm: High-Level Database Library
***************************************************************************

by David Fisher ([email protected])
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
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    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
  define-table.

  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
  define-sql-type.
* (<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
statement

(define-sql-type ternary-bit (one-of/c #t #f 'maybe)
		 	     (lambda (x)
			       (cond
			         ((eq? x 'maybe) "m")
				 (x "t")
				 (#t "f")))
                             (lambda (x)
			       (cond
			         ((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.