/* odbc.q: a simple ODBC interface
   $Id: odbc.q,v 1.4 2005/07/11 14:30:20 agraef Exp $ */

/* This file is part of the Q programming system.

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

   The Q programming system 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 General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. */

/* ODBC ("Open Database Connectivity") has become the de facto standard for
   portable and vendor independent database access. Most modern relational
   databases provide an ODBC interface so that they can be used with this
   module. The module provides the necessary operations to connect to an ODBC
   data source and retrieve or modify data using SQL statements. */

import clib;

/* The odbc_error symbol is used to return an error message and SQL state when
   the SQL server reports an error while executing any of the following
   functions. */

public odbc_error MSG STATE;

/* The following functions list the names and descriptions of the available
   data sources and drivers. The names can be passed in the DSN and DRIVER
   parameters of the connection string (see below). (It seems that at this
   time the odbc_drivers function is properly supported only on Windows,
   though.) */

public extern odbc_sources, odbc_drivers;

/* ODBC handles. These handles provide access to an ODBC connection. They are
   created with the odbc_connect function and closed when garbage-collected,
   or with an explicit call to the odbc_disconnect function. */

public extern type ODBCHandle;

/* Open and close an ODBC connection. CONN is the connection string used to
   describe the data source and various other parameters such as hostname,
   user id and password. The precise format depends on your system, so you
   should consult your local ODBC documentation for details, but generally the
   string is of the form <option>=<value>;<option>=<value>;...  and one of the
   options is DSN to name the data source name. Other commonly recognized
   options are HOST, UID, PWD and DATABASE which specify, respectively, the
   host to connect to (usually localhost by default), user name and password,
   and the default database to connect to. On Windows systems the FILEDSN
   option lets you establish a connection to a data source described by a .dsn
   file, and the DRIVER and DBQ options let you connect to a driver such as
   the MS Access ODBC driver, using the database given in the DBQ option as
   the data source. */

public extern odbc_connect CONN, odbc_disconnect DB;

/* Obtain information about an ODBC connection. Right now only a small subset
   of the information specified by the ODBC API is supported. Returns a tuple
   of strings with the following items (see the corresponding item
   descriptions in the ODBC API reference for more information):

   - DATA_SOURCE_NAME: the data source name
   - DATABASE_NAME: the default database
   - DBMS_NAME: the host DBMS name
   - DBMS_VER: the host DBMS version
   - DRIVER_NAME: the name of the ODBC driver
   - DRIVER_VER: the version of the ODBC driver
   - DRIVER_ODBC_VER: the ODBC version supported by the driver
   - ODBC_VER: the ODBC version of the driver manager */

public extern odbc_info DB;

/* Execute an SQL query and fetch results. SQL queries generally come in two
   different flavours: queries returning data (so-called result sets), and
   statements modifying the data (which have as their result the number of
   affected rows). Note that some databases allow special types of queries
   which may return multiple result sets and/or row counts.

   The sql_exec function returns either the tuple of column titles for the
   first result set, or the number of rows affected by the first SQL
   statement. If a tuple of column titles was returned, the data rows can be
   fetched one by one with sql_fetch. If no more data is available in the
   result set, sql_fetch fails. At any time, you can also call sql_more to
   switch to the next result set. Just like sql_exec, sql_more will return
   either a tuple of column titles (after which you can go on calling
   sql_fetch to fetch the data from the new result set) or a row count. If no
   more results are available, sql_more fails. When you are done with an SQL
   query you can also call the sql_close function to terminate a query and
   release the associated resources (this is also done automatically when a
   new query is started).

   NULL values in the results are converted to (), numeric data (including bit
   values) to the corresponding Q type (Int or Float), binary data to ByteStr,
   and character strings to String. All other data (e.g., time, date and
   timestamp) is converted to its character representation and returned as a
   string.

   Marked input parameters, which are replaced for `?' markers in the query,
   can be specified in the ARGS argument of sql_exec, which is a tuple of
   parameter values, or a singleton parameter value. The same types are
   recognized as for the result values (i.e., (), integers, floating point
   values, character and byte strings). Note that ARGS=() denotes that no
   parameter values are present; if you really need a singleton () (i.e.,
   NULL) parameter, you have to specify it as a 1-tuple `((),)'. */

public extern sql_exec DB QUERY ARGS;
public extern sql_fetch DB, sql_more DB, sql_close DB;

/* Convenience functions to execute an SQL query and collect results in a
   list. The sql function only returns the first result set, with the column
   titles in the first element, or a single row count. The msql function
   returns multiple result sets in a list of lists (or row counts). Marked
   parameters are passed in the same fashion as with sql_exec. */

public sql DB QUERY ARGS, msql DB QUERY ARGS;

/* We implement this tail-recursively to prevent stack overflows. */

private sql_loop, sql_done, sql_check;

sql DB QUERY ARGS	= sql_close DB || RES
			    where RES = sql_loop DB []
			      (sql_exec DB QUERY ARGS);

sql_loop DB Xs X	= reverse Xs if sql_done X;
			= sql_loop DB [X|Xs] (sql_fetch DB)
			    if sql_check X;
			= X otherwise;

sql_done (sql_fetch _)	= true;
sql_done _		= false otherwise;

sql_check X:Tuple	= not null X;
sql_check _		= false otherwise;

private msql_loop, msql_done, msql_check;

msql DB QUERY ARGS	= sql_close DB || RES
			    where RES = msql_loop DB []
			      (sql_loop DB [] (sql_exec DB QUERY ARGS));

msql_loop DB Xs X	= reverse Xs if msql_done X;
			= msql_loop DB [X|Xs] (sql_loop DB [] (sql_more DB))
			    if msql_check X;
			= X otherwise;

msql_done (sql_more _)	= true;
msql_done _		= false otherwise;

msql_check X:Int	= true;
msql_check X:List	= true;
msql_check _		= false otherwise;