/* odbc_examp.q: examples for the ODBC interface */

/* This program allows you to create some sample tables in your default
   database (`init' function) and invoke a simple SQL interpreter (`mysql').
   After editing the config information below, try something like `init;
   mysql' and then type some SQL queries. The sample database consists of the
   two tables `pet' and `event' from the MySQL documentation, Chapter 3. */

import odbc;

/* CONFIG SECTION -- Customize this for your local setup. *******************/

/* Here you have to specify the connect string which lets you connect to the
   database you want to use. The details are system-specific; on some systems
   it is sufficient to fill in the DSN field, on others you may have to also
   specify HOST, DATABASE, UID and PWD. See odbc.q for the available options.
   Some examples are provided below. */

def CONNECT = "DSN=myodbc"; // default MySQL database and user
// def CONNECT = "DSN=myodbc;UID=root;PWD="; // specify user and password
// def CONNECT = "DSN=myodbc;DATABASE=test"; // specify the default database
// def CONNECT = "FILEDSN=test.dsn"; // use a .dsn file (Windows)
// def CONNECT = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=test.mdb";
		// connect to an existing MS access database (Windows)

/****************************************************************************/

/* Handle ODBC errors. */

odbc_error MSG STATE	= throw (MSG,STATE);

report ERR		= fprintf ERROR "Error: %s (%s).\n" ERR ||
			  fflush ERROR;

/* The database connection. */

def DB = catch report (odbc_connect CONNECT);

/****************************************************************************/

/* Structure and data for the sample "menagerie" tables. This has been
   pilfered from the MySQL documentation. */

def PET_DESC =
  [("name",	"VARCHAR(20)"),
   ("owner",	"VARCHAR(20)"),
   ("species",	"VARCHAR(20)"),
   ("sex",	"CHAR(1)"),
   ("birth",	"DATE"),
   ("death",	"DATE")],
  EVENT_DESC =
  [("name",	"VARCHAR(20)"),
   ("date",	"DATE"),
   ("type",	"VARCHAR(15)"),
   ("remark",	"VARCHAR(255)")];

def PET_DATA =
  [("Fluffy",	"Harold",	"cat",	 "f",	"1993-02-04",	()),
   ("Claws",	"Gwen",		"cat",	 "m",	"1994-03-17",	()),
   ("Buffy",	"Harold",	"dog",	 "f",	"1989-05-13",	()),
   ("Fang",	"Benny",	"dog",	 "m",	"1990-08-27",	()),
   ("Bowser",	"Diane",	"dog",	 "m",	"1979-08-31",	"1995-07-29"),
   ("Chirpy",	"Gwen",		"bird",	 "f",	"1998-09-11",	()),
   ("Whistler",	"Gwen",		"bird",	 (),	"1997-12-09",	()),
   ("Slim",	"Benny",	"snake", "m",	"1996-04-29",	())],
  EVENT_DATA =
  [("Fluffy",	"1995-05-15",	"litter",	"4 kittens, 3 female, 1 male"),
   ("Buffy",	"1993-06-23",	"litter",	"5 puppies, 2 female, 3 male"),
   ("Buffy",	"1994-06-19",	"litter",	"3 puppies, 3 female"),
   ("Chirpy",	"1999-03-21",	"vet",		"needed beak straightened"),
   ("Slim",	"1997-08-03",	"vet",		"broken rib"),
   ("Bowser",	"1991-10-12",	"kennel",	()),
   ("Fang",	"1991-10-12",	"kennel",	()),
   ("Fang",	"1998-08-28",	"birthday",	"Gave him a new chew toy"),
   ("Claws",	"1998-03-17",	"birthday",	"Gave him a new flea collar"),
   ("Whistler",	"1998-12-09",	"birthday",	"First birthday")];

/* Initialize the tables. Run this once to create the tables and populate them
   with data. */

private init_table NAME DESC DATA, fields DESC, params DESC;

init			= catch report
			  (init_table "pet" PET_DESC PET_DATA ||
			   init_table "event" EVENT_DESC EVENT_DATA);

init_table NAME DESC DATA
			= sql DB (sprintf "create table %s (%s)"
				  (NAME, fields DESC)) () ||
			  do (sql DB QUERY) DATA
			    where QUERY =
			      sprintf "insert into %s values (%s)"
			      (NAME,params DESC);

fields DESC		= join "," (map (sprintf "%s %s") DESC);

params DESC		= join "," (map (cst "?") DESC);

/****************************************************************************/

/* Poor man's SQL interpreter. Prompts you for SQL queries, executes them and
   displays the results. Enter an empty line or end-of-file to quit the
   interpreter. */

private prompt, display SETS, display_set SET, display_row WIDTHS ROW;

mysql			= exec prompt;

prompt			= writes "\nSQL> " || flush || reads;

exec QUERY:String	= catch report (display (msql DB QUERY ())) || mysql
			    if not null QUERY;
exec _			= () otherwise;

/* Display results. */

display SETS:List	= do display_set SETS;

/* Display a result set. */

private widths RESULTS, rule WIDTH;

display_set ROWS:Int	= printf "%d rows affected.\n" ROWS;
display_set RESULTS:List
			= writes "\n" ||
			  // header
			  display_row WIDTHS HEADER ||
			  display_row WIDTHS RULES ||
			  // data
			  do (display_row WIDTHS) DATA ||
			  // footer
			  display_row WIDTHS RULES ||
			  printf "%d rows in set.\n" (#DATA)
			    if not null DATA
			    where WIDTHS = widths RESULTS,
			      [HEADER|DATA] = RESULTS,
			      RULES = (tuple (map rule WIDTHS));
			= writes "Empty set.\n" otherwise;

/* Determine column widths. */

private maxwidths Xs Ys;

widths RESULTS		= foldl maxwidths INIT RESULTS
			    where INIT = map (cst 0) (list (hd RESULTS));

private value_str VALUE;

maxwidths Xs Ys		= zipwith max Xs (map ((#).value_str) (list Ys));

/* Display a data row. */

private column WIDTH VALUE;

display_row WIDTHS ROW	= printf "%s\n"
			  (join "   " (zipwith column WIDTHS (list ROW)));

/* Rule of given width. */

rule WIDTH		= cat (map (cst "-") [1..WIDTH]);

/* Format a data value. */

private format WIDTH S;

column WIDTH VALUE	= format WIDTH (value_str VALUE);

format WIDTH S		= sprintf (sprintf "%%-%ds" WIDTH) S;

/* Convert data values to strings. */

value_str ()		= "NULL";
value_str VALUE:Num	= str VALUE;
value_str VALUE:String	= VALUE;
value_str _		= "***" otherwise;