#! /usr/bin/tclsh

# Copyright (c) 2001  David Muse
# See the file COPYING for more information.


load /usr/lib/sqlrelay/sqlrelay.so sqlrelay

proc checkUndef {value} {

	switch $value "" {
		puts -nonewline "success "
	} default {
		puts "failure "
		exit
	}
}

proc checkSuccess {value success} {

	if {$value==$success} {
		puts -nonewline "success "
	} else {
		puts "failure "
		exit
	}
}

# usage...
if {$argc<5} {
	puts "usage: sqlite.tcl host port socket user password"
	exit
}

# instantiation
set con [sqlrcon -server [lindex $argv 0] -port [lindex $argv 1] -socket [lindex $argv 2] -user [lindex $argv 3] -password [lindex $argv 4] -retrytime 0 -tries 1]
set cur [$con sqlrcur]

# get database type
puts "IDENTIFY: "
checkSuccess [$con identify] "sqlite"
puts ""

# ping
puts "PING: "
checkSuccess [$con ping] 1
puts ""

# drop existing table
$cur sendQuery "begin transaction"
catch {$cur sendQuery "drop table testtable"}
$con commit

# create a new table
puts "CREATE TEMPTABLE: "
$cur sendQuery "begin transaction"
checkSuccess [$cur sendQuery "create table testtable (testint int, testfloat float, testchar char(40), testvarchar varchar(40))"] 1
$con commit 
puts ""

puts "INSERT: "
$cur sendQuery "begin transaction"
checkSuccess [$cur sendQuery "insert into testtable values (1,1.1,'testchar1','testvarchar1')"] 1
checkSuccess [$cur sendQuery "insert into testtable values (2,2.2,'testchar2','testvarchar2')"] 1
checkSuccess [$cur sendQuery "insert into testtable values (3,3.3,'testchar3','testvarchar3')"] 1
checkSuccess [$cur sendQuery "insert into testtable values (4,4.4,'testchar4','testvarchar4')"] 1
puts ""

puts "AFFECTED ROWS: "
checkSuccess [$cur affectedRows] 0
puts ""

puts "BIND BY NAME: "
$cur prepareQuery "insert into testtable values (:var1,:var2,:var3,:var4)"
checkSuccess [$cur countBindVariables] 4
$cur inputBind "var1" 5
$cur inputBind "var2" 5.5 4 1
$cur inputBind "var3" "testchar5"
$cur inputBind "var4" "testvarchar5"
checkSuccess [$cur executeQuery] 1
$cur clearBinds
$cur inputBind "var1" 6
$cur inputBind "var2" 6.6 4 1
$cur inputBind "var3" "testchar6"
$cur inputBind "var4" "testvarchar6"
checkSuccess [$cur executeQuery] 1
puts ""

puts "ARRAY OF BINDS BY NAME: "
$cur clearBinds
$cur inputBinds {{"var1" 7} {"var2" 7.7 4 1} {"var3" "testchar7"} {"var4" "testvarchar7"}}
checkSuccess [$cur executeQuery] 1
puts ""

puts "BIND BY NAME WITH VALIDATION: "
$cur clearBinds
$cur inputBind "var1" 8
$cur inputBind "var2" 8.8 4 1
$cur inputBind "var3" "testchar8"
$cur inputBind "var4" "testvarchar8"
$cur validateBinds
checkSuccess [$cur executeQuery] 1
puts ""

puts "SELECT: "
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
puts ""

puts "COLUMN COUNT: "
checkSuccess [$cur colCount] 4
puts ""

puts "COLUMN NAMES: "
checkSuccess [$cur getColumnName 0] "testint"
checkSuccess [$cur getColumnName 1] "testfloat"
checkSuccess [$cur getColumnName 2] "testchar"
checkSuccess [$cur getColumnName 3] "testvarchar"
set cols [$cur getColumnNames]
checkSuccess [lindex $cols 0] "testint"
checkSuccess [lindex $cols 1] "testfloat"
checkSuccess [lindex $cols 2] "testchar"
checkSuccess [lindex $cols 3] "testvarchar"
puts ""

puts "COLUMN TYPES: "
checkSuccess [$cur getColumnTypeByIndex 0] "UNKNOWN"
checkSuccess [$cur getColumnTypeByName "testint"] "UNKNOWN"
checkSuccess [$cur getColumnTypeByIndex 1] "UNKNOWN"
checkSuccess [$cur getColumnTypeByName "testfloat"] "UNKNOWN"
checkSuccess [$cur getColumnTypeByIndex 2] "UNKNOWN"
checkSuccess [$cur getColumnTypeByName "testchar"] "UNKNOWN"
checkSuccess [$cur getColumnTypeByIndex 3] "UNKNOWN"
checkSuccess [$cur getColumnTypeByName "testvarchar"] "UNKNOWN"
puts ""

puts "COLUMN LENGTH: "
checkSuccess [$cur getColumnLengthByIndex 0] 0
checkSuccess [$cur getColumnLengthByName "testint"] 0
checkSuccess [$cur getColumnLengthByIndex 1] 0
checkSuccess [$cur getColumnLengthByName "testfloat"] 0
checkSuccess [$cur getColumnLengthByIndex 2] 0
checkSuccess [$cur getColumnLengthByName "testchar"] 0
checkSuccess [$cur getColumnLengthByIndex 3] 0
checkSuccess [$cur getColumnLengthByName "testvarchar"] 0
puts ""

puts "LONGEST COLUMN: "
checkSuccess [$cur getLongestByIndex 0] 1
checkSuccess [$cur getLongestByName "testint"] 1
checkSuccess [$cur getLongestByIndex 1] 3
checkSuccess [$cur getLongestByName "testfloat"] 3
checkSuccess [$cur getLongestByIndex 2] 9
checkSuccess [$cur getLongestByName "testchar"] 9
checkSuccess [$cur getLongestByIndex 3] 12
checkSuccess [$cur getLongestByName "testvarchar"] 12
puts ""

puts "ROW COUNT: "
checkSuccess [$cur rowCount] 8
puts ""

puts "TOTAL ROWS: "
checkSuccess [$cur totalRows] 8
puts ""

puts "FIRST ROW INDEX: "
checkSuccess [$cur firstRowIndex] 0
puts ""

puts "END OF RESULT SET: "
checkSuccess [$cur endOfResultSet] 1
puts ""

puts "FIELDS BY INDEX: "
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 0 1] "1.1"
checkSuccess [$cur getFieldByIndex 0 2] "testchar1"
checkSuccess [$cur getFieldByIndex 0 3] "testvarchar1"
puts ""
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkSuccess [$cur getFieldByIndex 7 1] "8.8"
checkSuccess [$cur getFieldByIndex 7 2] "testchar8"
checkSuccess [$cur getFieldByIndex 7 3] "testvarchar8"
puts ""

puts "FIELD LENGTHS BY INDEX: "
checkSuccess [$cur getFieldLengthByIndex 0 0] 1
checkSuccess [$cur getFieldLengthByIndex 0 1] 3
checkSuccess [$cur getFieldLengthByIndex 0 2] 9
checkSuccess [$cur getFieldLengthByIndex 0 3] 12
puts ""
checkSuccess [$cur getFieldLengthByIndex 7 0] 1
checkSuccess [$cur getFieldLengthByIndex 7 1] 3
checkSuccess [$cur getFieldLengthByIndex 7 2] 9
checkSuccess [$cur getFieldLengthByIndex 7 3] 12
puts ""

puts "FIELDS BY NAME: "
checkSuccess [$cur getFieldByName 0 "testint"] "1"
checkSuccess [$cur getFieldByName 0 "testfloat"] "1.1"
checkSuccess [$cur getFieldByName 0 "testchar"] "testchar1"
checkSuccess [$cur getFieldByName 0 "testvarchar"] "testvarchar1"
puts ""
checkSuccess [$cur getFieldByName 7 "testint"] "8"
checkSuccess [$cur getFieldByName 7 "testfloat"] "8.8"
checkSuccess [$cur getFieldByName 7 "testchar"] "testchar8"
checkSuccess [$cur getFieldByName 7 "testvarchar"] "testvarchar8"
puts ""

puts "FIELD LENGTHS BY NAME: "
checkSuccess [$cur getFieldLengthByName 0 "testint"] 1
checkSuccess [$cur getFieldLengthByName 0 "testfloat"] 3
checkSuccess [$cur getFieldLengthByName 0 "testchar"] 9
checkSuccess [$cur getFieldLengthByName 0 "testvarchar"] 12
puts ""
checkSuccess [$cur getFieldLengthByName 7 "testint"] 1
checkSuccess [$cur getFieldLengthByName 7 "testfloat"] 3
checkSuccess [$cur getFieldLengthByName 7 "testchar"] 9
checkSuccess [$cur getFieldLengthByName 7 "testvarchar"] 12
puts ""

puts "FIELDS BY ARRAY: "
set fields [$cur getRow 0]
checkSuccess [lindex $fields 0] "1"
checkSuccess [lindex $fields 1] "1.1"
checkSuccess [lindex $fields 2] "testchar1"
checkSuccess [lindex $fields 3] "testvarchar1"
puts ""

puts "FIELD LENGTHS BY ARRAY: "
set fieldlens [$cur getRowLengths 0]
checkSuccess [lindex $fieldlens 0] 1
checkSuccess [lindex $fieldlens 1] 3
checkSuccess [lindex $fieldlens 2] 9
checkSuccess [lindex $fieldlens 3] 12
puts ""

puts "INDIVIDUAL SUBSTITUTIONS: "
$cur sendQuery "drop table testtable1"
checkSuccess [$cur sendQuery "create table testtable1 (col1 int, col2 char, col3 float)"] 1
$cur prepareQuery "insert into testtable1 values (\$(var1),'\$(var2)',\$(var3))"
$cur substitution "var1" 1
$cur substitution "var2" "hello"
$cur substitution "var3" 10.5556 6 4
checkSuccess [$cur executeQuery] 1
puts ""

puts "FIELDS: "
checkSuccess [$cur sendQuery "select * from testtable1"] 1
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 0 1] "hello"
checkSuccess [$cur getFieldByIndex 0 2] "10.5556"
checkSuccess [$cur sendQuery "delete from testtable1"] 1
puts ""

puts "ARRAY SUBSTITUTIONS: "
$cur prepareQuery "insert into testtable1 values (\$(var1),'\$(var2)',\$(var3))"
$cur substitutions {{"var1" 1} {"var2" "hello"} {"var3" 10.5556 6 4}}
checkSuccess [$cur executeQuery] 1
puts ""

puts "FIELDS: "
checkSuccess [$cur sendQuery "select * from testtable1"] 1
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 0 1] "hello"
checkSuccess [$cur getFieldByIndex 0 2] "10.5556"
checkSuccess [$cur sendQuery "delete from testtable1"] 1
puts ""

puts "RESULT SET BUFFER SIZE: "
checkSuccess [$cur getResultSetBufferSize] 0
$cur setResultSetBufferSize 2
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkSuccess [$cur getResultSetBufferSize] 2
puts ""
checkSuccess [$cur firstRowIndex] 0
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 2
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
puts ""
checkSuccess [$cur firstRowIndex] 2
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 4
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur firstRowIndex] 6
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 8
checkUndef [$cur getFieldByIndex 8 0]
puts ""
checkSuccess [$cur firstRowIndex] 8
checkSuccess [$cur endOfResultSet] 1
checkSuccess [$cur rowCount] 8
puts ""

puts "DONT GET COLUMN INFO: "
$cur dontGetColumnInfo
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkUndef [$cur getColumnName 0]
checkSuccess [$cur getColumnLengthByIndex 0] 0
checkUndef [$cur getColumnTypeByIndex 0]
$cur getColumnInfo
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkSuccess [$cur getColumnName 0] "testint"
checkSuccess [$cur getColumnLengthByIndex 0] 0
checkSuccess [$cur getColumnTypeByIndex 0] "UNKNOWN"
puts ""

puts "SUSPENDED SESSION: "
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
$cur suspendResultSet
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
puts ""
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
checkSuccess [$cur getFieldByIndex 3 0] "4"
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
$cur suspendResultSet
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
puts ""
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
checkSuccess [$cur getFieldByIndex 3 0] "4"
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
$cur suspendResultSet
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
puts ""
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
checkSuccess [$cur getFieldByIndex 3 0] "4"
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""

puts "SUSPENDED RESULT SET: "
$cur setResultSetBufferSize 2
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkSuccess [$cur getFieldByIndex 2 0] "3"
set id [$cur getResultSetId]
$cur suspendResultSet
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
checkSuccess [$cur resumeResultSet $id] 1
puts ""
checkSuccess [$cur firstRowIndex] 4
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 6
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur firstRowIndex] 6
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 8
checkUndef [$cur getFieldByIndex 8 0]
puts ""
checkSuccess [$cur firstRowIndex] 8
checkSuccess [$cur endOfResultSet] 1
checkSuccess [$cur rowCount] 8
$cur setResultSetBufferSize 0
puts ""

puts "CACHED RESULT SET: "
$cur cacheToFile "cachefile1"
$cur setCacheTtl 200
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
set filename [$cur getCacheFileName]
checkSuccess $filename "cachefile1"
$cur cacheOff
checkSuccess [$cur openCachedResultSet $filename] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""

puts "COLUMN COUNT FOR CACHED RESULT SET: "
checkSuccess [$cur colCount] 4
puts ""

puts "COLUMN NAMES FOR CACHED RESULT SET: "
checkSuccess [$cur getColumnName 0] "testint"
checkSuccess [$cur getColumnName 1] "testfloat"
checkSuccess [$cur getColumnName 2] "testchar"
checkSuccess [$cur getColumnName 3] "testvarchar"
set cols [$cur getColumnNames]
checkSuccess [lindex $cols 0] "testint"
checkSuccess [lindex $cols 1] "testfloat"
checkSuccess [lindex $cols 2] "testchar"
checkSuccess [lindex $cols 3] "testvarchar"
puts ""

puts "CACHED RESULT SET WITH RESULT SET BUFFER SIZE: "
$cur setResultSetBufferSize 2
$cur cacheToFile "cachefile1"
$cur setCacheTtl 200
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
set filename [$cur getCacheFileName]
checkSuccess $filename "cachefile1"
$cur cacheOff
checkSuccess [$cur openCachedResultSet $filename] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
$cur setResultSetBufferSize 0
puts ""

puts "FROM ONE CACHE FILE TO ANOTHER: "
$cur cacheToFile "cachefile2"
checkSuccess [$cur openCachedResultSet "cachefile1"] 1
$cur cacheOff
checkSuccess [$cur openCachedResultSet "cachefile2"] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
puts ""

puts "FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: "
$cur setResultSetBufferSize 2
$cur cacheToFile "cachefile2"
checkSuccess [$cur openCachedResultSet "cachefile1"] 1
$cur cacheOff
checkSuccess [$cur openCachedResultSet "cachefile2"] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
$cur setResultSetBufferSize 0
puts ""

puts "CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: "
$cur setResultSetBufferSize 2
$cur cacheToFile "cachefile1"
$cur setCacheTtl 200
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkSuccess [$cur getFieldByIndex 2 0] "3"
set filename [$cur getCacheFileName]
checkSuccess $filename "cachefile1"
set id [$cur getResultSetId]
$cur suspendResultSet
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
puts ""
checkSuccess [$con resumeSession $port $socket] 1
checkSuccess [$cur resumeCachedResultSet $id $filename] 1
puts ""
checkSuccess [$cur firstRowIndex] 4
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 6
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur firstRowIndex] 6
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 8
checkUndef [$cur getFieldByIndex 8 0]
puts ""
checkSuccess [$cur firstRowIndex] 8
checkSuccess [$cur endOfResultSet] 1
checkSuccess [$cur rowCount] 8
$cur cacheOff
puts ""
checkSuccess [$cur openCachedResultSet $filename] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
$cur setResultSetBufferSize 0
puts ""

puts "COMMIT AND ROLLBACK: "
set secondcon [sqlrcon -server [lindex $argv 0] -port [lindex $argv 1] -socket [lindex $argv 2] -user [lindex $argv 3] -password [lindex $argv 4] -retrytime 0 -tries 1]
set secondcur [$secondcon sqlrcur]
checkSuccess [$secondcur sendQuery "select count(*) from testtable"] 1
checkSuccess [$secondcur getFieldByIndex 0 0] "0"
checkSuccess [$con commit] 1
checkSuccess [$secondcur sendQuery "select count(*) from testtable"] 1
checkSuccess [$secondcur getFieldByIndex 0 0] "8"
checkSuccess [$cur sendQuery "insert into testtable values (10,10.1,'testchar10','testvarchar10')"] 1
checkSuccess [$secondcur sendQuery "select count(*) from testtable"] 1
checkSuccess [$secondcur getFieldByIndex 0 0] "9"
puts ""

puts "FINISHED SUSPENDED SESSION: "
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
set id [$cur getResultSetId]
$cur suspendResultSet
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
checkSuccess [$cur resumeResultSet $id] 1
checkUndef [$cur getFieldByIndex 4 0]
checkUndef [$cur getFieldByIndex 5 0]
checkUndef [$cur getFieldByIndex 6 0]
checkUndef [$cur getFieldByIndex 7 0]
puts ""

# drop existing table
$cur sendQuery "drop table testtable"

# invalid queries...
puts "INVALID QUERIES: "
catch {checkSuccess [$cur sendQuery "select * from testtable"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable"] 0}
puts ""
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
puts ""
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
puts ""