;;;=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.
;;;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 db2-test mzscheme
	(require (lib "time.ss" "srfi" "19"))
	(require (planet "sqli.scm" ("oesterholt" "sqlid.plt" 1 2)))
	(require (planet "sqld-db2-internal.scm" ("oesterholt" "sqlid.plt" 1 2)))
;;;=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* (
	 (host (if (null? argv) "localhost" 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
	 (user (getenv "USER"))
	 (pass (getenv "PASSWD"))
	 (sqld (sqld-db2-new (let ((dsn (string-append "alias=test user=" user " passwd=" pass)))
			       (display (format "dsn=~a~%" dsn))
			       dsn)))
;;;=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(250), age numeric(4), nice smallint, 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))

      ;;; NOTE! THIS IS DIFFERENT FROM MOST OTHER DATABASES!
      (clos 'query "INSERT INTO test (name, age, nice) 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   : >> db2-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: db2-test.scm,v 1.1 2006/01/04 20:15:18 HansOesterholt Exp $E<lb>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;=cut