\input texinfo  @c -*-texinfo-*-

@c Copyright (C) 2001, 2004 David J. Lambert

@c %**start of header
@setfilename guile-simplesql.info
@settitle Guile SimpleSQL manual
@c %**end of header

@dircategory The Algorithmic Language Scheme
@direntry
* Guile SimpleSQL: (guile-simplesql).   Access to PostgreSQL databases from Guile.
@end direntry

@include version.texi

@titlepage
@title Guile SimpleSQL Manual
@subtitle For use with Guile SimpleSQL @value{VERSION}

@author Dave Lambert
@page
@end titlepage
@page


@ifnottex
@node Top, Introduction, (dir), (dir)
@top Texinfo

@end ifnottex

@menu
* Introduction::                
* Tutorial::                    
* Reference::                   
* Supported databases::         
@end menu

@node Introduction, Tutorial, Top, Top
@chapter Introduction

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

@section History

Guile SimpleSQL began life as `Squile', written by Hal Roberts
@email{hroberts@@storystreet.com}.  Jorgen Schaefer
@email{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 @email{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 :)

@section Getting Guile SimpleSQL

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

@node Tutorial, Reference, Introduction, Top
@chapter Tutorial

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

Guile SimpleSQL currently supports MySQL and PostgreSQL engines, but
the @sc{api} is straightforward, so adding more backends shouldn't be
difficult.

A sample session that will work with any Postgresql server:

@example
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>
@end example

It's that easy.


@node Reference, Supported databases, Tutorial, Top
@chapter Reference

@menu
* Functions::                   
* Data types::                  
@end menu

@node Functions, Data types, Reference, Reference
@section Functions

@deffn Primitive simplesql-open api database [host [user [password]]]

Attempt to open the database @var{database}, using the backend
indicated by @var{api}.  @var{api} should be either
@code{'mysql} or @code{'postgresql}.  If successful, it returns a
@code{db-conn} @sc{sql} object with which to referenced the newly
opened database connection.

Examples:
@example
(simplesql-open 'mysql "addresses")
(simplesql-open 'mysql "addresses" "address-server" "joe" "binzo")
@end example

There is an alternative argument list using keyword/value pairs.  The
legal keywords are:
@table @code 
@item #:api
The @sc{sql} backend to use.
@item #:database
The database to access to.
@item #:host
The hostname or IP address of the server.
@item #:port
The port to connect to.  If an integer, this is the @sc{ip/tcp} port
number.  If a string, it is the Unix domain socket extension as
interpreted by the backend.
@item #:user
The username to connect as.
@item #:password
The password.
@end table

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

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

And some examples of using the port argument to access @sc{tcp} and
Unix domain sockets:
@example
(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")
@end example

@end deffn

@deffn Primitive simplesql-close db-conn

Closes the connection @var{db-conn} and invalidates the reference.

@code{simplesql-close} closes a database connection opened with
@code{simplesql-open}. The connection will be closed if the
@var{db-conn} object is garbage collected, but this function allows
for a greater control over the close time.

Example:
@example
(simplesql-close db)
@end example
@end deffn


@deffn Primitive simplesql-query db-conn query

@var{db-conn} is a connection object, and @var{query} is string
containing an @sc{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
@code{(@var{year month day hour minute second})}.

@code{simplesql-query} queries a database opened with
@code{simplesql-open}.  @var{db-conn} is a database object created by
@code{simplesql-open}.  @var{query} is the text of the query.
@code{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 @code{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,
@code{*chars} become latin1-strings, and dates become lists of numbers
in the form of (<second> <min> @dots{}).

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:

@lisp
(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'")
@end lisp
@end deffn


@deffn Primitive simplesql-database? obj

This function returns @code{#t} if @var{obj} is an @sc{sql} connection
object, and @code{#f} otherwise
@end deffn


@deffn Primitive simplesql-escape string
@table @var
@item string
Any Scheme string or u8vector
@end table

This function does @sc{sql} escaping on the specified string so it can be
used in @sc{sql} queries without danger

Example:
@example
(simplesql-escape "foo\0bar")
@end example
@end deffn


@node Data types,  , Functions, Reference
@section Data types

@menu
* Date and time::               
@end menu

@node Date and time,  , Data types, Data types
@subsection Date and time

Time is encoded as a vector containing several fields from the
@code{struct tm} structure (@pxref{Broken-down Time,,Calendar
time,libc.info,The GNU C Library Reference Manual}).  This is,
helpfully, the format Guile uses for decomposed times anyway.

In order they are:

@itemize

@item Seconds from start of this minute

@item Minutes from the start of this hour

@item Hours past midnight

@item Day of the month (an ordinal!)

@item Months since start of the year

@item Years since 1900.

@item Weekdays since Sunday.

@item Days since start of year.

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

@item A string identifying the time zone.

@end itemize

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

@node Supported databases,  , Reference, Top
@chapter Supported databases

@menu
* Adding backends::             
@end menu

@node Adding backends,  , Supported databases, Supported databases
@section Adding backends

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

You have to provide three functions:

@deftypefn {Primitive} {void *} sql_open_func (const char * @var{database}, const char * @var{host}, int @var{port}, const char * @var{socket} const char * @var{user}, const char * @var{password}, SCM * @var{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 @code{NULL} if an error occured, and set @var{err} to
a Scheme string describing the error. It will be included in the error
message using @code{~s}.  @var{database} must be non-@var{NULL}.  The
other variables may be @code{NULL} or @code{0} if no value was
supplied to @code{simplesql-open}: you should use a sensible default
in such cases.  Only one of @var{port} and @var{socket} will ever be
non-zero.

@end deftypefn

@deftypefn {Primitive} SCM sql_query_func (void * @var{db}, const char * @var{query}, int @var{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.

@end deftypefn


@deftypefn {Primitive} void sql_close_func (void *@var{db})

This function just should close the database connection.
@end deftypefn

And call this one:

@deftypefn {Primitive} int sql_register_api (const char * @var{api}, sql_open_func @var{open}, sql_query_func @var{query}, sql_close_func @var{close})

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

@end deftypefn

@contents
@bye