;;;=head1 Test code for PostgreSQL
;;;
;;;This program gives more insight about how to use SQLI/SQLD It is a small test program
;;;that is distributed with the SQLI/SQLD package. Works for both bigloo and mzscheme.
;;;To prepare it for a scheme dialect, you need C<staste>.
;;;
;;;=head2 Code explained
;;;
;;;We begin with importing or requiring the right modules. You can see that here
;;;the right driver (PostgreSQL) is loaded.
;;;
;;;=verbatim scm,8
(module psql-test mzscheme
	(require (lib "time.ss" "srfi" "19"))
	(require "sqli.scm")
	(require "sqld-psql-internal.scm")
;;;=verbatim
;;;
;;;Some code to display a list of arguments keeps us from the need for a swarm of display
;;;invocations.
;;;
;;;=verbatim scm,8
(define (displayp . args)
  (define (p args)
    (if (null? args)
	(newline)
	(begin
	  (display (car args))
	  (p (cdr args)))))
  (p args))
;;;=verbatim
;;;
;;;Now for the main function. Let's first process some arguments
;;;
;;;=verbatim scm,8
(define (main argv)
  (let* (
;#+ mzscheme
	 (host (if (null? argv) "localhost" argv))
;#+ bigloo
	 (host (if (null? (cdr argv)) "localhost" (cadr argv)))
;##
;;;=verbatim
;;;
;;;Here is the code that differs the most for all SQLI/SQLD programs, because here we instantiate
;;;the driver. This is the SQLD part of SQLI/SQLD.
;;;
;;;=verbatim scm,8
	 (sqld (sqld-psql-new (string-append "user=test dbname=test password=test")))
;;;=verbatim
;;;
;;;Next, we can connect to the database and wrap the connection into the generic SQLI interface.
;;;
;;;=verbatim scm,8
	 (sqli (sqli-connect sqld))
;;;=verbatim
;;;
;;;Right away we try to create a tabe in the database.
;;;
;;;=verbatim scm,8
	 (results (sqli-query sqli "CREATE TABLE test (name varchar,age numeric(4),nice boolean,dt timestamp)"))
	 (i 0))
;;;=verbatim
;;;
;;;What comes now is a lot of testing code.
;;;
;;;=verbatim scm,8
    ; Look at result of the table creation

    (displayp results)
    (displayp (sqli-error? sqli) " - " (sqli-error-message sqli))

    ; version and name of the driver

    (displayp (sqli-version) " - " (sqli-driver-name sqld) " - " (sqli-driver-version sqld))
    
    ; First test: Select * from test.

    (do 
	((a (sqli-fetchrow sqli) (sqli-fetchrow sqli)))
	((eq? a #f) #t)
      (display a))
    (displayp)

    ; Transaction of insert statements.
    
    (sqli-begin sqli)

    (do 
	((i 1 (+ i 1)))
	((> i 10) #t)
      (sqli-query sqli "INSERT INTO test VALUES ($1, $2, $3, $4)" (string-append "row'" (number->string i)) i #t (current-date)))

    (sqli-commit sqli)

    ; Select from database

    (set! results (sqli-query sqli "SELECT * FROM test"))
    (displayp (sqli-error? sqli) " - " (sqli-error-message sqli))
    (displayp (sqli-fetchall sqli))
    (displayp)

    ; Wrong SQL syntax.
    
    (sqli-begin sqli)
    (sqli-query sqli "INSERT INTO test VALUES ('joost',42);SELEKT * FROM test")
    (displayp (sqli-error? sqli) " - " (sqli-error-message sqli))
    (if (sqli-error? sqli)
	(sqli-rollback sqli)
	(sqli-commit sqli))
;;;=verbatim
;;;
;;;Registering queries is a nice feature of SQLI. One can register a query under a name and
;;;provide an implicit conversion function for select statements, or the expected types from
;;;a select statement, so that conversion to scheme types is done automatically.
;;;
;;;The following nice properties apply to registering queries:
;;;
;;;=over 1
;;;
;;;=item 1
;;;
;;;It is more efficient, because a registered query is preprocessed by SQLI on the
;;;possible arguments and stored as a list of query parts instead of a string.
;;;
;;;=item 1
;;;
;;;More important, one can register queries specific to a certain database SQL dialect
;;;under a name, thereby making the code more portable.
;;;
;;;=back
;;;
;;;=verbatim scm,8
    ; Register a query. This query is registered with a conversion function
    ; to convert the results of the select statement. Another thing to notice,
    ; is that the column for the select statement and the operator for the
    ; where part are parameterized ($1 and $2).

    (sqli-register sqli 'select 
		   "SELECT $1 FROM test WHERE age $2 $3"
		    (let ((c 0))
		     (lambda (a) 
		       (begin
			 (set! c (+ c 1))
			 (list c (car a))))))
    (displayp sqli)

    ; Here the registered query is executed for column name, with operator >.
    ; the first 10 rows of the result of the query are fetched and displayed.

    (displayp (sqli-exec sqli 'select 'name '> 2))
    (displayp (sqli-fetch sqli 10))

    ; Register an other query. This one demonstrates the type conversion
    ; capabilities of SQLI. We can see that the results of the select from test
    ; will be converted to string, integer, boolean and date.

    (sqli-register sqli 'select-convert
		   "SELECT * FROM test WHERE age=10"
		   'string 'integer 'boolean 'date)
    (displayp sqli)

    ; Here the registered query is executed. All results are fetched and displayed.

    (displayp (sqli-exec sqli 'select-convert))
    (displayp (sqli-fetchall sqli))
;;;=verbatim
;;;
;;;The next part uses the 'closure' interface of sqli, which is just a wrapper
;;;interface around the normal C<(sqli-[anything])> calls.
;;;
;;;=verbatim scm,8
    ; Use closure.

    (let ((clos (sqli-closure sqli)))
      (displayp (clos 'exec 'select 'age '<= 2))
      (displayp (clos 'fetchall))
      (clos 'register 'count "SELECT COUNT(name) FROM test"
	    (lambda (row) (car row)))
      (clos 'exec 'count);
      (displayp (clos 'fetchrow))

      (clos 'register 'and "SELECT nice FROM test" 
	    (lambda (row) 
	      (let ((b (clos 'convert (car row) 'boolean)))
		(list b))))
      
      (displayp (clos 'exec 'and))
      (displayp (clos 'fetchall))

      (clos 'query "INSERT INTO test VALUES ('false',0,$1)" #f)

      (displayp (clos 'exec 'and))
      (displayp (clos 'fetchall))

      (clos 'hithere "errors"))

    (displayp sqli)
    (displayp (sqli-exec sqli 'and))
    (displayp (sqli-fetchall sqli))
;;;=verbatim
;;;
;;;In the end, one needs to disconnect from the database. This is mandatory,
;;;because it will cleanup the C structures, that are not garbage collected
;;;automatically. Apart from the fact that it is never a good idea to stop
;;;your program with an open connection to the database.
;;;
;;;=verbatim scm,8
  (sqli-disconnect sqli)

  0))
;;;=verbatim
;;;
;;;Now for some mzscheme specific code...
;;;
;;;=verbatim scm,8
;#+ mzscheme
(provide main))
;##
;;;=verbatim
;;;
;;;That's all to it!
;;;
;;;=head2 Info   
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;S<C<Package   : >> psql-test.scmE<lb>
;;;S<C<Author    : >> Hans Oesterholt-Dijkema.E<lb>
;;;S<C<Copyright : >> HOD 2004/2005.E<lb>
;;;S<C<License   : >> The Elemental Programming Artistic License.E<lb>
;;;S<C<CVS       : >> $Id: psql-test.scm,v 1.12 2006/01/04 20:15:19 HansOesterholt Exp $E<lb>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;=cut