     [file version.texi not included]

Texinfo
*******

Introduction
************

Guile SimpleSQL is a module for Guile, providing a straight-forward interface to SQL databases.

History
=======

Guile SimpleSQL began life as `Squile', written by Hal Roberts hroberts@storystreet.com.  Jorgen Schaefer forcer@mindless.com took over the code base and changed it quite a lot, and was responsible for the 2.0 release.  Of late, Dave Lambert d.j.lambert@sms.ed.ac.uk has taken up the baton. Dave's the one responsible for the boring name change, and, of course, any bugs :)

Getting Guile SimpleSQL
=======================

You can find the latest version of Guile SimpleSQL at its homepage, http://guile-simplesql.sourceforge.net/.

Tutorial
********

The Guile interface is very simple, but (I hope) also powerful.  There are only five functions: simplesql-open and simplesql-close, simplesql-query, simplesql-database?, and simplesql-escape.

Guile SimpleSQL currently supports MySQL and PostgreSQL engines, but the API is straightforward, so adding more backends shouldn't be difficult.

A sample session that will work with any Postgresql server:

     guile> (use-modules (database simplesql))
     guile> (define db (simplesql-open 'postgresql "template1"))
     guile> db
     #<simplesql-database 0x80b1f38 "template1">
     guile> (simplesql-database? db)
     #t
     guile> (simplesql-query db "SELECT * from pg_user;")
     (#("usename" "usesysid" "usecreatedb" "usetrace" "usesuper" "usecatupd" "passwd" "valuntil")
      #("postgres" 1 #t #t #t #t "********" ())
      #("djl" 100 #t #f #f #f "********" ()))
     guile> (simplesql-close db)
     guile> db
     #<simplesql-database 0x80b1f38 closed>

It's that easy.

Reference
*********

Functions
=========

 -- Primitive: simplesql-open api database [host [user [password]]]

     Attempt to open the database DATABASE, using the backend indicated by API.  API should be either 'mysql or 'postgresql.  If successful, it returns a db-conn SQL object with which to referenced the newly opened database connection.

     Examples:

          (simplesql-open 'mysql "addresses")
          (simplesql-open 'mysql "addresses" "address-server" "joe" "binzo")

     There is an alternative argument list using keyword/value pairs.  The legal keywords are:

     #:api
          The SQL backend to use.

     #:database
          The database to access to.

     #:host
          The hostname or IP address of the server.

     #:port
          The port to connect to.  If an integer, this is the IP/TCP port number.  If a string, it is the Unix domain socket extension as interpreted by the backend.

     #:user
          The username to connect as.

     #:password
          The password.

     Ordering of keywords is unimportant, but the keyword arguments must come after any non-keyworded arguments, if any.  Values must be supplied for the API and database parameters.

     Examples:

          (simplesql-open 'mysql "addresses" #:port 7654)
          (simplesql-open 'mysql #:database "addresses" #:password "binzo" #:user "joe")
          (simplesql-open #:database "addresses" #:port 7654 #:api 'mysql)

     And some examples of using the port argument to access TCP and Unix domain sockets:

          (simplesql-open 'mysql "addresses" #:port 3306)
          (simplesql-open 'mysql "addresses" #:host "localhost" #:port "/var/run/mysqld/mysqld.sock")
          (simplesql-open 'postgresql "addresses" #:port 5432)
          (simplesql-open 'postgresql "addresses" #:host "/var/run/postgresql/" #:port "5432")

 -- Primitive: simplesql-close db-conn

     Closes the connection DB-CONN and invalidates the reference.

     simplesql-close closes a database connection opened with simplesql-open. The connection will be closed if the DB-CONN object is garbage collected, but this function allows for a greater control over the close time.

     Example:

          (simplesql-close db)

 -- Primitive: simplesql-query db-conn query

     DB-CONN is a connection object, and QUERY is string containing an SQL query.

     This function returns a list of vectors. The first vector contains strings with the field names, while the remaining vectors contain the data. How the data is represented is up to the backend, but generally, any type of number will stay a number, any type of string will stay a string, any type of binary data will be represented as a string, and dates and times will be represented as a list of the form (YEAR MONTH DAY HOUR MINUTE SECOND).

     simplesql-query queries a database opened with simplesql-open.  DB-CONN is a database object created by simplesql-open.  QUERY is the text of the query. simplesql-query returns either the number of rows affected (for update and insert queries and their ilk) or a list of vectors that contains a vector of the field names followed by a vector for each row (for select queries and its ilk).

     One of the things that makes Guile SimpleSQL a nice interface to sql is that it translates the fields into their appropriate types, so numbers of all sorts become Scheme numbers, blobs become u8vectors, *chars become latin1-strings, and dates become lists of numbers in the form of (<second> <min> ...).

     It takes the database to query as a first argument, and any number of remaining arguments (though at least one). They all will be concatanated eventually, though strings will be converted to latin1 while u8vectors will be insterted literally into the query.

     To get a better feel for what queries look like, just try a few queries and look at the results.

     Examples:

          (simplesql-query db "select number from people where name = 'Sarah'")
          (simplesql-query db "show tables")
          (simplesql-query db "update people set number = '555-1234' "
                        "where name = 'Sarah'")

 -- Primitive: simplesql-database? obj

     This function returns #t if OBJ is an SQL connection object, and #f otherwise

 -- Primitive: simplesql-escape string

     string
          Any Scheme string or u8vector

     This function does SQL escaping on the specified string so it can be used in SQL queries without danger

     Example:

          (simplesql-escape "foo\0bar")

Data types
==========

Date and time
-------------

Time is encoded as a vector containing several fields from the struct tm structure (see Calendar time (The GNU C Library Reference Manual)).  This is, helpfully, the format Guile uses for decomposed times anyway.

In order they are:

  * Seconds from start of this minute

  * Minutes from the start of this hour

  * Hours past midnight

  * Day of the month (an ordinal!)

  * Months since start of the year

  * Years since 1900.

  * Weekdays since Sunday.

  * Days since start of year.

  * Positive if daylight savings time is active at the time described, 0 if not, and negative if it's unknown.

  * A string identifying the time zone.

If your system lacks the strptime function, SimpleSQL will return the time as an uninterpreted string.

Supported databases
*******************

Adding backends
===============

Currently, Guile SimpleSQL supports only two databases, but it's easy to write a new backend.

You have to provide three functions:

 -- Primitive: void * sql_open_func (const char * DATABASE, const char * HOST, int PORT, const char * SOCKET const char * USER, const char * PASSWORD, SCM * ERR)

     This function should return a pointer to a record which describes the database connection. The other functions will be called with this pointer.  Return NULL if an error occured, and set ERR to a Scheme string describing the error. It will be included in the error message using ~s.  DATABASE must be non-NULL.  The other variables may be NULL or 0 if no value was supplied to simplesql-open: you should use a sensible default in such cases.  Only one of PORT and SOCKET will ever be non-zero.

 -- Primitive: SCM sql_query_func (void * DB, const char * QUERY, int QUERYLEN)

     This function should return a list of vectors with the query results. The first vector should contain the names of the fields, while the remaining vectors should contain the values as Scheme objects. Chose a fitting representation.

 -- Primitive: void sql_close_func (void *DB)

     This function just should close the database connection.

And call this one:

 -- Primitive: int sql_register_api (const char * API, sql_open_func OPEN, sql_query_func QUERY, sql_close_func CLOSE)

     This function can be used at any time to add new APIs to SimpleSQL.  You have to pass a name for the API (e.g. "MySQL"), and the three access functions. Then you're done.