Texinfo
*******

1 Introduction
**************

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

1.1 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 :)

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

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

2 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.

3 Reference
***********

3.1 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")

3.2 Data types
==============

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

Time is encoded as a vector containing several fields from the `struct
tm' structure (see Calendar time (libc.info)).
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.

4 Supported databases
*********************

4.1 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.