;;;=head1 Test code forMysql
;;;
;;;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
;;;
;;;You want to have a look at L<the Test program for PostgreSQL first|sqli_sample_psql>.
;;;We begin with importing or requiring the right modules. You can see that here
;;;the right driver (Mysql) is loaded.
;;;
;;;=verbatim scm,8
(module mysql-test mzscheme
	(require (lib "time.ss" "srfi" "19"))
	(require (planet "sqld-mysql-internal.scm" ("oesterholt" "sqlid.plt" 1 2)))
	(require (planet "sqli.scm" ("oesterholt" "sqlid.plt" 1 2)))
;##

(define (displayp . args)
  (define (p args)
    (if (null? args)
	(newline)
	(begin
	  (display (car args))
	  (p (cdr args)))))
  (p args))


(define (main . argv)
;;;=verbatim
;;;
;;;See here the difference between the Mysql version of this test program and the
;;;PostgreSQL version. B<This is where the PostgreSQL version differs from
;;;the Mysql version>
;;;
;;;=verbatim scm,8
  (let* ((sqld (sqld-mysql-new "user=test passwd=test host=localhost db=test"))
;;;=verbatim
;;;
;;;The rest is the same and won't be explained.
;;;
;;;=verbatim scm,8
	 (sqli (sqli-connect sqld))
	 (results (sqli-query sqli "CREATE TABLE test (name varchar(1000),age numeric(4),nice boolean, dt datetime)"))
	 (i 0))

    ; Look at result

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

    ; version and name

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

    (displayp "select * from test - test")

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

    ; Transaction of insert statements.
    
    (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))

    ; Register a query

    (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)
    (displayp (sqli-exec sqli 'select 'name '> 2))
    (displayp "FETCH 10:" (sqli-fetch sqli 10))

    ; Register an other query

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

    ; Use closure.
    (displayp "CLOSURE TEST")

    (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,$2)" #f (current-date))

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

      (clos 'hithere "errors"))

    (displayp sqli)
    (displayp (sqli-exec sqli 'and))
    (displayp (sqli-fetchall sqli))

  (sqli-disconnect sqli)

  0))

;#+ 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: mysql-test.scm,v 1.2 2006/01/04 20:15:19 HansOesterholt Exp $E<lb>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;=cut