###############################################################################
#
# basic.eagle --
#
# Written by Joe Mistachkin.
# Released to the public domain, use at your own risk!
#
###############################################################################
package require Eagle
package require Eagle.Library
package require Eagle.Test
runTestPrologue
###############################################################################
package require System.Data.SQLite.Test
runSQLiteTestPrologue
runSQLiteTestFilesPrologue
###############################################################################
runTest {test data-1.1 {unit tests from the 'test' project} -setup {
cleanupFile [file join [file dirname $testExeFile] Test.db3]
set fileName [file join [getDatabaseDirectory] data-1.1.db]
} -body {
set output ""
set code [catch {
#
# NOTE: For the sake of backward compatibility, the "-autoRun" argument
# must be first.
#
testClrExec $testExeFile [list -eventflags Wait -directory \
[file dirname $testExeFile] -stdout output -success 0] -autoRun \
-fileName [appendArgs \" [file nativename $fileName] \"]
} error]
set successCount [regexp -all -- {\tSucceeded\t} $output]
set failureCount [regexp -all -- {\tFailed\t} $output]
set totalCount [expr {$successCount + $failureCount}]
tputs $test_channel [appendArgs \
"---- found and executed " $totalCount " unit tests from \"" \
$testExeFile "\", " $successCount " passed, " $failureCount \
" failed\n"]
tlog "---- BEGIN STDOUT OUTPUT\n"
tlog $output
tlog "\n---- END STDOUT OUTPUT\n"
list $code [expr {$code == 0 ? "" : $error}]
} -cleanup {
cleanupFile $fileName
unset -nocomplain totalCount failureCount successCount code output \
error fileName
} -constraints {eagle SQLite file_System.Data.SQLite.dll file_test.exe\
testExec} -result {0 {}}}
###############################################################################
runTest {test data-1.2 {unit tests from the 'testlinq' project} -setup {
#
# NOTE: Re-copy the reference database file used for this unit test to the
# build directory in case it has been changed by a previous test run.
#
file copy -force $northwindEfDbFile \
[file join [getBuildDirectory] [file tail $northwindEfDbFile]]
#
# NOTE: We need to make 100% sure that the console output encoding is the
# same as when the 'testlinq.out' file was created.
#
set savedEncoding [object invoke Console OutputEncoding]
set encoding [object invoke System.Text.Encoding GetEncoding Windows-1252]
object invoke Console OutputEncoding $encoding
} -body {
set output ""
set code [catch {
testClrExec $testLinqExeFile [list -eventflags Wait -directory \
[file dirname $testLinqExeFile] -stdout output -success 0]
} error]
tlog "---- BEGIN STDOUT OUTPUT\n"
tlog $output
tlog "\n---- END STDOUT OUTPUT\n"
list $code [string equal $output [readFile $testLinqOutFile]] \
[expr {$code == 0 ? "" : $error}]
} -cleanup {
catch {object invoke Console OutputEncoding $savedEncoding}
unset -nocomplain code output error savedEncoding encoding
} -constraints \
{eagle monoToDo SQLite file_System.Data.SQLite.dll\
file_System.Data.SQLite.Linq.dll file_testlinq.exe file_northwindEF.db\
file_testlinq.out testExec} -result {0 True {}}}
###############################################################################
runTest {test data-1.3 {unit tests from the 'testef6' project} -setup {
#
# NOTE: Re-copy the reference database file used for this unit test to the
# build directory in case it has been changed by a previous test run.
#
file copy -force $northwindEfDbFile \
[file join [getBuildDirectory] [file tail $northwindEfDbFile]]
#
# NOTE: We need to make 100% sure that the console output encoding is the
# same as when the 'testlinq.out' file was created.
#
set savedEncoding [object invoke Console OutputEncoding]
set encoding [object invoke System.Text.Encoding GetEncoding Windows-1252]
object invoke Console OutputEncoding $encoding
} -body {
set output ""
set code [catch {
testClrExec $testEf6ExeFile [list -eventflags Wait -directory \
[file dirname $testEf6ExeFile] -stdout output -success 0]
} error]
tlog "---- BEGIN STDOUT OUTPUT\n"
tlog $output
tlog "\n---- END STDOUT OUTPUT\n"
list $code [string equal $output [readFile $testLinqOutFile]] \
[expr {$code == 0 ? "" : $error}]
} -cleanup {
catch {object invoke Console OutputEncoding $savedEncoding}
unset -nocomplain code output error savedEncoding encoding
} -constraints \
{eagle monoToDo SQLite file_EntityFramework.dll file_System.Data.SQLite.dll\
file_System.Data.SQLite.EF6.dll file_testef6.exe file_northwindEF.db\
file_testlinq.out testExec} -result {0 True {}}}
###############################################################################
runTest {test data-1.4 {SELECT scalar/reader, CREATE, INSERT} -setup {
setupDb [set fileName data-1.4.db]
} -body {
set result [list]
lappend result [sql execute -execute scalar $db \
"SELECT sqlite_source_id();"]
sql execute $db "CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y, z);"
sql execute $db "INSERT INTO t1 (x, y, z) VALUES(1, 'foo', 1234);"
sql execute -execute reader $db "SELECT x, y, z FROM t1;"
foreach name [lsort [array names rows]] {
lappend result [list $name $rows($name)]
}
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain name rows result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-match regexp -result {^\{\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2} [0-9a-f]{40}\}\
\{1 \{\{x 1\} \{y foo\} \{z 1234\}\}\} \{count 1\} \{names \{x y z\}\}$}}
###############################################################################
runTest {test data-1.5 {GetSchema with ReservedWords} -setup {
setupDb [set fileName data-1.5.db]
} -body {
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data;
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static DataTable GetReservedWords()
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
return connection.GetSchema("ReservedWords");
}
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
// do nothing.
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} GetReservedWords
} result] : [set result ""]}] $result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result results errors code dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 System#Data#DataTable#\d+$}}
###############################################################################
runTest {test data-1.6 {GetSchema with ForeignKeys} -setup {
setupDb [set fileName data-1.6.db]
} -body {
sql execute $db {
CREATE TABLE t1(
x INTEGER REFERENCES t2 MATCH FULL
ON UPDATE SET DEFAULT ON DELETE CASCADE
DEFAULT 1
);
}
sql execute $db "CREATE TABLE t2(x INTEGER REFERENCES t3);"
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data;
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static DataRowCollection GetForeignKeys()
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
return connection.GetSchema("ForeignKeys").Rows;
}
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
// do nothing.
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
set rows [list]
set foreignKeys [object invoke _Dynamic${id}.Test${id} GetForeignKeys]
object foreach -alias foreignKey $foreignKeys {
lappend rows [list \
[$foreignKey Item CONSTRAINT_CATALOG] \
[$foreignKey Item CONSTRAINT_NAME] \
[$foreignKey Item TABLE_CATALOG] \
[$foreignKey Item TABLE_NAME] \
[$foreignKey Item CONSTRAINT_TYPE] \
[$foreignKey Item IS_DEFERRABLE] \
[$foreignKey Item INITIALLY_DEFERRED] \
[$foreignKey Item FKEY_ID] \
[$foreignKey Item FKEY_FROM_COLUMN] \
[$foreignKey Item FKEY_TO_CATALOG] \
[$foreignKey Item FKEY_TO_TABLE] \
[$foreignKey Item FKEY_TO_COLUMN] \
[$foreignKey Item FKEY_FROM_ORDINAL_POSITION] \
[$foreignKey Item FKEY_ON_UPDATE] \
[$foreignKey Item FKEY_ON_DELETE] \
[$foreignKey Item FKEY_MATCH]]
}
set rows
} result] : [set result ""]}] $result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result rows foreignKey foreignKeys results errors code \
dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\{main FK_t1_0_0 main t1\
\{FOREIGN KEY\} False False 0 x main t2 \{\} 0 \{SET DEFAULT\} CASCADE NONE\}\
\{main FK_t2_0_0 main t2 \{FOREIGN KEY\} False False 0 x main t3 \{\} 0 \{NO\
ACTION\} \{NO ACTION\} NONE\}\}$}}
###############################################################################
runTest {test data-1.7 {SQLITE_FCNTL_WIN32_AV_RETRY} -setup {
setupDb [set fileName data-1.7.db]
} -body {
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static bool TestSetAvRetry(
ref int count,
ref int interval
)
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
//
// NOTE: Set the requested retry parameter values.
//
if (connection.SetAvRetry(ref count, ref interval) != 0)
return false;
//
// NOTE: Query the retry parameter values.
//
int newCount = -1; int newInterval = -1;
if (connection.SetAvRetry(ref newCount, ref newInterval) != 0)
return false;
//
// NOTE: Make sure the retry parameter values were set.
//
return (newCount == count && newInterval == interval);
}
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
// do nothing.
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
set savedCount -1; set savedInterval -1
object invoke _Dynamic${id}.Test${id} TestSetAvRetry \
savedCount savedInterval
set count 5; set interval 50
object invoke _Dynamic${id}.Test${id} TestSetAvRetry \
count interval
} result] : [set result ""]}] $result
} -cleanup {
if {[info exists savedCount]} then {
#
# NOTE: Restore the saved retry count, if possible.
#
catch {
set interval -1
object invoke _Dynamic${id}.Test${id} TestSetAvRetry savedCount interval
}
}
if {[info exists savedInterval]} then {
#
# NOTE: Restore the saved retry interval, if possible.
#
catch {
set count -1
object invoke _Dynamic${id}.Test${id} TestSetAvRetry count savedInterval
}
}
cleanupDb $fileName
unset -nocomplain result count interval savedCount savedInterval results \
errors code dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 True$}}
###############################################################################
runTest {test data-1.8 {properly closed database file (non-query)} -setup {
set fileName data-1.8.db
} -body {
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
set sql { \
BEGIN EXCLUSIVE TRANSACTION; \
CREATE TABLE t1(x INTEGER); \
INSERT INTO t1 (x) VALUES(1); \
SELECT x FROM t1; \
}
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static void Main()
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("${sql}",
connection))
{
command.ExecuteNonQuery();
}
}
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} Main
} result] : [set result ""]}] $result \
[close [open $dataSource RDONLY 0 "" -share None]]
} -cleanup {
cleanupDb $fileName
unset -nocomplain result results errors code sql dataSource id fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\} \{\}$}}
###############################################################################
runTest {test data-1.9 {properly closed database file (reader #1)} -setup {
set fileName data-1.9.db
} -body {
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
set sql { \
BEGIN EXCLUSIVE TRANSACTION; \
CREATE TABLE t1(x INTEGER); \
INSERT INTO t1 (x) VALUES(1); \
SELECT x FROM t1; \
}
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static void Main()
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("${sql}",
connection))
{
using (SQLiteDataReader dataReader = command.ExecuteReader())
{
// do nothing.
}
}
}
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} Main
} result] : [set result ""]}] $result \
[close [open $dataSource RDONLY 0 "" -share None]]
} -cleanup {
cleanupDb $fileName
unset -nocomplain result results errors code sql dataSource id fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\} \{\}$}}
###############################################################################
runTest {test data-1.10 {properly closed database file (reader #2)} -setup {
set fileName data-1.10.db
} -body {
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
set sql { \
BEGIN EXCLUSIVE TRANSACTION; \
CREATE TABLE t1(x INTEGER); \
INSERT INTO t1 (x) VALUES(1); \
SELECT x FROM t1; \
}
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data;
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static void Main()
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("${sql}",
connection))
{
using (SQLiteDataReader dataReader = command.ExecuteReader(
CommandBehavior.CloseConnection))
{
// do nothing.
}
}
}
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} Main
} result] : [set result ""]}] $result \
[close [open $dataSource RDONLY 0 "" -share None]]
} -cleanup {
cleanupDb $fileName
unset -nocomplain result results errors code sql dataSource id fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\} \{\}$}}
###############################################################################
runTest {test data-1.11 {Changes property} -setup {
setupDb [set fileName data-1.11.db]
} -body {
set connection [getDbConnection]
set result [list]
sql execute $db "CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y, z);"
sql execute $db "INSERT INTO t1 (x, y, z) VALUES(1, 'foo', 1234);"
sql execute $db "INSERT INTO t1 (x, y, z) VALUES(2, 'bar', 5678);"
lappend result [object invoke $connection Changes]
sql execute $db "UPDATE t1 SET y = 'foobar';"
lappend result [object invoke $connection Changes]
sql execute -execute reader $db "SELECT x, y, z FROM t1;"
lappend result [object invoke $connection Changes]
foreach name [lsort -integer [array names rows -regexp {^\d+$}]] {
lappend result [list $name $rows($name)]
}
set result
} -cleanup {
cleanupDb $fileName
freeDbConnection
unset -nocomplain name rows result connection db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-result {1 2 2 {1 {{x 1} {y foobar} {z 1234}}} {2 {{x 2} {y foobar} {z 5678}}}}}
###############################################################################
runTest {test data-1.12 {LastInsertRowId property} -setup {
setupDb [set fileName data-1.12.db]
} -body {
set connection [getDbConnection]
set result [list]
sql execute $db "CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y, z);"
sql execute $db "CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y, z);"
lappend result [object invoke $connection LastInsertRowId]
sql execute $db "INSERT INTO t1 (x, y, z) VALUES(1, 'foo', 1234);"
lappend result [object invoke $connection LastInsertRowId]
sql execute $db "INSERT INTO t1 (x, y, z) VALUES(2, 'bar', 5678);"
lappend result [object invoke $connection LastInsertRowId]
sql execute $db "UPDATE t1 SET y = 'foobar';"
lappend result [object invoke $connection LastInsertRowId]
sql execute $db "INSERT INTO t2 (y, z) VALUES('foo', 1234);"
lappend result [object invoke $connection LastInsertRowId]
sql execute $db "INSERT INTO t2 (y, z) VALUES('bar', 5678);"
lappend result [object invoke $connection LastInsertRowId]
sql execute $db "UPDATE t2 SET y = 'foobar';"
lappend result [object invoke $connection LastInsertRowId]
sql execute -execute reader $db "SELECT x, y, z FROM t1;"
lappend result [object invoke $connection LastInsertRowId]
foreach name [lsort -integer [array names rows -regexp {^\d+$}]] {
lappend result [list $name $rows($name)]
}
sql execute -execute reader $db "SELECT x, y, z FROM t2;"
lappend result [object invoke $connection LastInsertRowId]
foreach name [lsort -integer [array names rows -regexp {^\d+$}]] {
lappend result [list $name $rows($name)]
}
set result
} -cleanup {
cleanupDb $fileName
freeDbConnection
unset -nocomplain name rows result connection db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-result {0 1 2 2 1 2 2 2 {1 {{x 1} {y foobar} {z 1234}}} {2 {{x 2} {y foobar}\
{z 5678}}} 2 {1 {{x 1} {y foobar} {z 1234}}} {2 {{x 2} {y foobar} {z 5678}}}}}
###############################################################################
runTest {test data-1.13 {DateTime using Unix epoch} -setup {
setupDb [set fileName data-1.13.db] "" UnixEpoch Utc
} -body {
set result [list]
sql execute $db "CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y DATETIME);"
sql execute $db "INSERT INTO t1 (x, y) VALUES(1, 1302825600);"
sql execute $db "INSERT INTO t1 (x, y) VALUES(2, 1334448000);"
sql execute $db "INSERT INTO t1 (x, y) VALUES(3, 1365984000);"
sql execute $db "INSERT INTO t1 (x, y) VALUES(4, ?);" \
[list param1 Int32 1302825600]
sql execute $db "INSERT INTO t1 (x, y) VALUES(5, ?);" \
[list param1 Int32 1334448000]
sql execute $db "INSERT INTO t1 (x, y) VALUES(6, ?);" \
[list param1 Int32 1365984000]
sql execute -verbatim $db "INSERT INTO t1 (x, y) VALUES(7, ?);" \
[list param1 DateTime 1302825600]
sql execute -verbatim $db "INSERT INTO t1 (x, y) VALUES(8, ?);" \
[list param1 DateTime 1334448000]
sql execute -verbatim $db "INSERT INTO t1 (x, y) VALUES(9, ?);" \
[list param1 DateTime 1365984000]
sql execute -execute reader -datetimeformat [getDateTimeFormat] $db \
"SELECT x, y FROM t1 ORDER BY x;"
foreach name [lsort -integer [array names rows -regexp {^\d+$}]] {
lappend result [list $name $rows($name)]
}
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain name rows result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-result {{1 {{x 1} {y {2011-04-15 00:00:00Z}}}} {2 {{x 2} {y {2012-04-15\
00:00:00Z}}}} {3 {{x 3} {y {2013-04-15 00:00:00Z}}}} {4 {{x 4} {y {2011-04-15\
00:00:00Z}}}} {5 {{x 5} {y {2012-04-15 00:00:00Z}}}} {6 {{x 6} {y {2013-04-15\
00:00:00Z}}}} {7 {{x 7} {y {2011-04-15 00:00:00Z}}}} {8 {{x 8} {y {2012-04-15\
00:00:00Z}}}} {9 {{x 9} {y {2013-04-15 00:00:00Z}}}}}}
###############################################################################
set date [clock format [clock seconds] -format yyyy-MM-dd]
###############################################################################
runTest {test data-1.14 {DateTime using invariant culture} -setup {
setupDb [set fileName data-1.14.db] "" InvariantCulture Utc
} -body {
set result [list]
sql execute $db "CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y DATETIME);"
sql execute $db \
"INSERT INTO t1 (x, y) VALUES(1, 'Wednesday, 16 December 2009');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(2, '12:00:00');"
sql execute $db \
"INSERT INTO t1 (x, y) VALUES(3, 'Wednesday, 16 December 2009 12:00:00');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(4, '12/16/2009');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(5, '12:00');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(6, '12/16/2009 12:00');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(7, ?);" \
[list param1 DateTime "Wednesday, 16 December 2009"]
sql execute $db "INSERT INTO t1 (x, y) VALUES(8, ?);" \
[list param1 DateTime 12:00:00]
sql execute $db "INSERT INTO t1 (x, y) VALUES(9, ?);" \
[list param1 DateTime "Wednesday, 16 December 2009 12:00:00"]
sql execute $db "INSERT INTO t1 (x, y) VALUES(10, ?);" \
[list param1 DateTime 12/16/2009]
sql execute $db "INSERT INTO t1 (x, y) VALUES(11, ?);" \
[list param1 DateTime 12:00]
sql execute $db "INSERT INTO t1 (x, y) VALUES(12, ?);" \
[list param1 DateTime "12/16/2009 12:00"]
sql execute -execute reader -datetimeformat [getDateTimeFormat] $db \
"SELECT x, CAST(y AS TEXT) AS y2 FROM t1 ORDER BY x;"
foreach name [lsort -integer [array names rows -regexp {^\d+$}]] {
lappend result [list $name $rows($name)]
}
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain name rows result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-result [subst {{1 {{x 1} {y2 {Wednesday, 16 December 2009}}}} {2 {{x 2} {y2\
12:00:00}}} {3 {{x 3} {y2 {Wednesday, 16 December 2009 12:00:00}}}} {4 {{x 4}\
{y2 12/16/2009}}} {5 {{x 5} {y2 12:00}}} {6 {{x 6} {y2 {12/16/2009 12:00}}}} {7\
{{x 7} {y2 2009-12-16T00:00:00.0000000Z}}} {8 {{x 8} {y2\
${date}T12:00:00.0000000Z}}} {9 {{x 9} {y2 2009-12-16T12:00:00.0000000Z}}}\
{10 {{x 10} {y2 2009-12-16T00:00:00.0000000Z}}} {11 {{x 11} {y2\
${date}T12:00:00.0000000Z}}} {12 {{x 12} {y2 2009-12-16T12:00:00.0000000Z}}}}]}
###############################################################################
runTest {test data-1.15 {DateTime using current culture} -setup {
setupDb [set fileName data-1.15.db] "" CurrentCulture Utc
} -body {
set result [list]
sql execute $db "CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y DATETIME);"
sql execute $db \
"INSERT INTO t1 (x, y) VALUES(1, 'Wednesday, 16 December 2009');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(2, '12:00:00');"
sql execute $db \
"INSERT INTO t1 (x, y) VALUES(3, 'Wednesday, 16 December 2009 12:00:00');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(4, '12/16/2009');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(5, '12:00');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(6, '12/16/2009 12:00');"
sql execute $db "INSERT INTO t1 (x, y) VALUES(7, ?);" \
[list param1 DateTime "Wednesday, 16 December 2009"]
sql execute $db "INSERT INTO t1 (x, y) VALUES(8, ?);" \
[list param1 DateTime 12:00:00]
sql execute $db "INSERT INTO t1 (x, y) VALUES(9, ?);" \
[list param1 DateTime "Wednesday, 16 December 2009 12:00:00"]
sql execute $db "INSERT INTO t1 (x, y) VALUES(10, ?);" \
[list param1 DateTime 12/16/2009]
sql execute $db "INSERT INTO t1 (x, y) VALUES(11, ?);" \
[list param1 DateTime 12:00]
sql execute $db "INSERT INTO t1 (x, y) VALUES(12, ?);" \
[list param1 DateTime "12/16/2009 12:00"]
sql execute -execute reader -datetimeformat [getDateTimeFormat] $db \
"SELECT x, CAST(y AS TEXT) AS y2 FROM t1 ORDER BY x;"
foreach name [lsort -integer [array names rows -regexp {^\d+$}]] {
lappend result [list $name $rows($name)]
}
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain name rows result db fileName
} -constraints \
{eagle culture.invariant monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite} -result [subst {{1 {{x 1} {y2 {Wednesday, 16 December\
2009}}}} {2 {{x 2} {y2 12:00:00}}} {3 {{x 3} {y2 {Wednesday, 16 December 2009\
12:00:00}}}} {4 {{x 4} {y2 12/16/2009}}} {5 {{x 5} {y2 12:00}}} {6 {{x 6} {y2\
{12/16/2009 12:00}}}} {7 {{x 7} {y2 2009-12-16T00:00:00.0000000Z}}} {8 {{x 8}\
{y2 ${date}T12:00:00.0000000Z}}} {9 {{x 9} {y2 2009-12-16T12:00:00.0000000Z}}}\
{10 {{x 10} {y2 2009-12-16T00:00:00.0000000Z}}} {11 {{x 11} {y2\
${date}T12:00:00.0000000Z}}} {12 {{x 12} {y2 2009-12-16T12:00:00.0000000Z}}}}]}
###############################################################################
unset -nocomplain date
###############################################################################
runTest {test data-1.16 {SQLiteConnectionStringBuilder DateTime} -body {
set id [object invoke Interpreter.GetActive NextId]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System.Data.SQLite;
namespace _Dynamic${id}
{
public static class Test${id}
{
public static string GetConnectionString(
string format,
string kind,
string formatString
)
{
SQLiteConnectionStringBuilder builder =
new SQLiteConnectionStringBuilder();
builder.Add("Data Source", "test.db");
builder.Add("DateTimeFormat", format);
builder.Add("DateTimeKind", kind);
builder.Add("DateTimeFormatString", formatString);
return builder.ToString();
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
// do nothing.
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} GetConnectionString \
null null null
} result] : [set result ""]}] $result \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} GetConnectionString \
Default null null
} result] : [set result ""]}] $result \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} GetConnectionString \
null Unspecified null
} result] : [set result ""]}] $result \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} GetConnectionString \
ISO8601 Utc null
} result] : [set result ""]}] $result \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} GetConnectionString \
Ticks Local yyyy-MM-dd
} result] : [set result ""]}] $result
} -cleanup {
unset -nocomplain result results errors code id
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{Data Source=test\.db\} 0\
\{Data Source=test\.db;DateTimeFormat=(?:Default|ISO8601)\} 0 \{Data\
Source=test\.db;DateTimeKind=Unspecified\} 0 \{Data\
Source=test\.db;DateTimeFormat=(?:Default|ISO8601);DateTimeKind=Utc\} 0 \{Data\
Source=test\.db;DateTimeFormat=Ticks;DateTimeKind=Local;DateTimeFormatString=yyyy-MM-dd\}$}}
###############################################################################
runTest {test data-1.17 {SQLiteConnectionStringBuilder properties} -body {
set id [object invoke Interpreter.GetActive NextId]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System;
using System.Data.SQLite;
using System.Reflection;
using System.Text;
namespace _Dynamic${id}
{
public static class Test${id}
{
private static string ToHexString(
byte\[\] array
)
{
if (array == null)
return null;
StringBuilder result = new StringBuilder();
int length = array.Length;
for (int index = 0; index < length; index++)
result.AppendFormat("{0:x2}", array\[index\]);
return result.ToString();
}
///////////////////////////////////////////////////////////////////////
public static string GetConnectionString(
string key,
string value,
string propertyName
)
{
SQLiteConnectionStringBuilder builder =
new SQLiteConnectionStringBuilder();
if (key != null)
builder.Add(key, value);
object propertyValue = null;
if (propertyName != null)
{
propertyValue = typeof(SQLiteConnectionStringBuilder).InvokeMember(
propertyName, BindingFlags.Instance | BindingFlags.Public |
BindingFlags.GetProperty, null, builder, null);
}
return String.Format("{0}, {1}", (propertyValue is byte\[\]) ?
ToHexString((byte\[\])propertyValue) : propertyValue, builder);
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
// do nothing.
}
}
}
}] true true true results errors System.Data.SQLite.dll]
lappend results $code [expr {[info exists errors] ? $errors : ""}]
if {$code eq "Ok"} then {
set keys [list null Version Synchronous UseUTF16Encoding Pooling \
BinaryGUID "Data Source" Uri FullUri "Default Timeout" \
Enlist FailIfMissing "Legacy Format" "Read Only" \
Password "Page Size" "Max Page Count" "Cache Size" \
DateTimeFormat DateTimeKind DateTimeFormatString \
BaseSchemaName "Journal Mode" "Default IsolationLevel" \
"Foreign Keys" Flags SetDefaults ToFullPath HexPassword \
DefaultDbType DefaultTypeName NoSharedFlags]
set values [list null 3 Normal True False \
True test.db test.db file:test.db 60 \
False True False True \
secret 4096 1024 8192 \
UnixEpoch Utc yyyy-MM-dd sqlite_schema \
Memory Serializable False \
Default False False 736563726574 String \
TEXT True]
set propertyNames [list null Version SyncMode UseUTF16Encoding Pooling \
BinaryGUID DataSource Uri FullUri DefaultTimeout \
Enlist FailIfMissing LegacyFormat ReadOnly \
Password PageSize MaxPageCount CacheSize \
DateTimeFormat DateTimeKind DateTimeFormatString \
BaseSchemaName JournalMode DefaultIsolationLevel \
ForeignKeys Flags SetDefaults ToFullPath \
HexPassword DefaultDbType DefaultTypeName \
NoSharedFlags]
foreach key $keys value $values propertyName $propertyNames {
set code [catch {
object invoke _Dynamic${id}.Test${id} GetConnectionString \
$key $value $propertyName
} result]
lappend results $code $result
}
}
set results
} -cleanup {
unset -nocomplain propertyName propertyNames value key values keys result \
results errors code id
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result \
{^System#CodeDom#Compiler#CompilerResults#\d+ Ok \{\} 0 \{, \} 0 \{3,\
Version=3\} 0 \{Normal, Synchronous=Normal\} 0 \{True, UseUTF16Encoding=True\}\
0 \{False, Pooling=False\} 0 \{True, BinaryGUID=True\} 0 \{test\.db, Data\
Source=test\.db\} 0 \{test\.db, Uri=test\.db\} 0 \{file:test.db,\
FullUri=file:test\.db\} 0 \{60, Default Timeout=60\} 0 \{False, Enlist=False\}\
0 \{True, FailIfMissing=True\} 0 \{False, Legacy Format=False\} 0 \{True, Read\
Only=True\} 0 \{secret, Password=secret\} 0 \{4096, Page Size=4096\} 0 \{1024,\
Max Page Count=1024\} 0 \{8192, Cache Size=8192\} 0 \{UnixEpoch,\
DateTimeFormat=UnixEpoch\} 0 \{Utc, DateTimeKind=Utc\} 0 \{yyyy-MM-dd,\
DateTimeFormatString=yyyy-MM-dd\} 0 \{sqlite_schema,\
BaseSchemaName=sqlite_schema\} 0 \{Memory, Journal Mode=Memory\} 0\
\{Serializable, Default IsolationLevel=Serializable\} 0 \{False, Foreign\
Keys=False\} 0 \{(?:Default|LogCallbackException),\
Flags=(?:Default|LogCallbackException)\} 0 \{False, SetDefaults=False\} 0\
\{False, ToFullPath=False\} 0 {736563726574, HexPassword=736563726574} 0\
\{String, DefaultDbType=String\} 0 \{TEXT, DefaultTypeName=TEXT\} 0 \{True,\
NoSharedFlags=True\}$}}
###############################################################################
runTest {test data-1.18 {SQLiteConvert ToDateTime (Julian Day)} -body {
set dateTime [object invoke -create System.Data.SQLite.SQLiteConvert \
ToDateTime 2455928.0 Utc]
object invoke $dateTime ToString [getDateTimeFormat]
} -cleanup {
unset -nocomplain dateTime
} -constraints {eagle System.Data.SQLite} -result {2012-01-01 12:00:00Z}}
###############################################################################
runTest {test data-1.19 {SQLiteConvert ToJulianDay} -body {
expr {round([object invoke System.Data.SQLite.SQLiteConvert ToJulianDay \
"2012-01-01 12:00:00Z"])}
} -constraints {eagle System.Data.SQLite} -result {2455928}}
###############################################################################
runTest {test data-1.20 {SQLiteConvert ToUnixEpoch (Utc)} -body {
#
# NOTE: At first, the test result here may not seem correct; however, the
# same result can be seen by compiling and running the following C#
# code fragment together with the ToUnixEpoch method:
#
# DateTime dateTime;
# Console.WriteLine("dateTime = {0}, unixTime = {1}",
# dateTime = DateTime.Parse("2012-01-01 12:00:00Z"),
# ToUnixEpoch(dateTime));
#
# The basic problem here is that the Parse [and TryParse] methods of
# the DateTime structure seem to always return local time, even when
# string value clearly indicates otherwise (i.e. the trailing "Z",
# indicating UTC).
#
expr {round([object invoke System.Data.SQLite.SQLiteConvert ToUnixEpoch \
"2012-01-01 12:00:00Z"])}
} -constraints {eagle System.Data.SQLite} -result {1325390400}}
###############################################################################
runTest {test data-1.21 {SQLiteConvert ToUnixEpoch (Local)} -body {
#
# NOTE: At first, the test result here may not seem correct; however, the
# same result can be seen by compiling and running the following C#
# code fragment together with the ToUnixEpoch method:
#
# DateTime dateTime;
# Console.WriteLine("dateTime = {0}, unixTime = {1}",
# dateTime = DateTime.Parse("2012-01-01 12:00:00"),
# ToUnixEpoch(dateTime));
#
# The basic problem here is that the Parse [and TryParse] methods of
# the DateTime structure seem to always return local time, even when
# string value clearly indicates otherwise (i.e. the trailing "Z",
# indicating UTC).
#
expr {round([object invoke System.Data.SQLite.SQLiteConvert ToUnixEpoch \
"2012-01-01 12:00:00"])}
} -constraints {eagle System.Data.SQLite} -result {1325419200}}
###############################################################################
runTest {test data-1.22 {SQLiteTransaction disposal behavior} -setup {
setupDb [set fileName data-1.22.db]
} -body {
sql execute $db "CREATE TABLE t1(x TEXT);"
sql execute $db {
INSERT INTO t1 (x) VALUES('test1');
INSERT INTO t1 (x) VALUES('test2');
INSERT INTO t1 (x) VALUES('test3');
}
set sql "SELECT x FROM t1 ORDER BY x COLLATE DOTHROW;"
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System;
using System.Data.SQLite;
namespace _Dynamic${id}
{
\[SQLiteFunction(Name = "DOTHROW", FuncType = FunctionType.Collation)\]
public class Test${id} : SQLiteFunction
{
public override int Compare(
string param1,
string param2
)
{
throw new Exception("not implemented");
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
SQLiteFunction.RegisterFunction(typeof(Test${id}));
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
using (SQLiteTransaction transaction =
connection.BeginTransaction())
{
try
{
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "${sql}";
command.ExecuteNonQuery();
}
catch
{
// do nothing.
}
}
}
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} Main
} result] : [set result ""]}] $result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result results errors code sql dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}}
###############################################################################
runTest {test data-1.23 {SQLiteFunction collation exception} -setup {
setupDb [set fileName data-1.23.db]
} -body {
sql execute $db "CREATE TABLE t1(x TEXT);"
sql execute $db {
INSERT INTO t1 (x) VALUES('test1');
INSERT INTO t1 (x) VALUES('test2');
INSERT INTO t1 (x) VALUES('test3');
}
set sql "SELECT x FROM t1 ORDER BY x COLLATE DOTHROW2;"
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System;
using System.Data.SQLite;
namespace _Dynamic${id}
{
\[SQLiteFunction(Name = "DOTHROW2", FuncType = FunctionType.Collation)\]
public class Test${id} : SQLiteFunction
{
public override int Compare(
string param1,
string param2
)
{
throw new Exception("not implemented");
}
///////////////////////////////////////////////////////////////////////
public static int Main()
{
SQLiteFunction.RegisterFunction(typeof(Test${id}));
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
using (SQLiteTransaction transaction =
connection.BeginTransaction())
{
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "${sql}";
using (SQLiteDataReader dataReader = command.ExecuteReader())
{
int count = 0;
while (dataReader.Read())
count++;
return count;
}
}
}
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} Main
} result] : [set result ""]}] $result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result results errors code sql dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 1\
\{System\.Reflection\.TargetInvocationException: Exception has been thrown by\
the target of an invocation\. ---> System\.Data\.SQLite\.SQLiteException:\
interrupted.*$}}
###############################################################################
runTest {test data-1.24 {LINQ SQL_CONSTRAINTCOLUMNS resource} -body {
object invoke -flags +NonPublic \
System.Data.SQLite.Linq.Properties.Resources SQL_CONSTRAINTCOLUMNS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.Linq} -result {
CREATE TEMP VIEW SCHEMACONSTRAINTCOLUMNS AS
SELECT CONSTRAINT_CATALOG,
NULL AS CONSTRAINT_SCHEMA,
CONSTRAINT_NAME,
TABLE_CATALOG,
NULL AS TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM TEMP.SCHEMAINDEXCOLUMNS
UNION
SELECT CONSTRAINT_CATALOG,
NULL,
CONSTRAINT_NAME,
TABLE_CATALOG,
NULL,
TABLE_NAME,
FKEY_FROM_COLUMN
FROM TEMP.SCHEMAFOREIGNKEYS;
}}
###############################################################################
runTest {test data-1.25 {EF6 SQL_CONSTRAINTCOLUMNS resource} -body {
object invoke -flags +NonPublic \
System.Data.SQLite.EF6.Properties.Resources SQL_CONSTRAINTCOLUMNS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.EF6} -result {
CREATE TEMP VIEW SCHEMACONSTRAINTCOLUMNS AS
SELECT CONSTRAINT_CATALOG,
NULL AS CONSTRAINT_SCHEMA,
CONSTRAINT_NAME,
TABLE_CATALOG,
NULL AS TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM TEMP.SCHEMAINDEXCOLUMNS
UNION
SELECT CONSTRAINT_CATALOG,
NULL,
CONSTRAINT_NAME,
TABLE_CATALOG,
NULL,
TABLE_NAME,
FKEY_FROM_COLUMN
FROM TEMP.SCHEMAFOREIGNKEYS;
}}
###############################################################################
runTest {test data-1.26 {LINQ SQL_CONSTRAINTS resource} -body {
object invoke -flags +NonPublic \
System.Data.SQLite.Linq.Properties.Resources SQL_CONSTRAINTS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.Linq} -result {
CREATE TEMP VIEW SCHEMACONSTRAINTS AS
SELECT INDEX_CATALOG AS CONSTRAINT_CATALOG,
NULL AS CONSTRAINT_SCHEMA,
INDEX_NAME AS CONSTRAINT_NAME,
TABLE_CATALOG,
NULL AS TABLE_SCHEMA,
TABLE_NAME,
'PRIMARY KEY' AS CONSTRAINT_TYPE,
0 AS IS_DEFERRABLE,
0 AS INITIALLY_DEFERRED,
NULL AS CHECK_CLAUSE
FROM TEMP.SCHEMAINDEXES
WHERE PRIMARY_KEY = 1
UNION
SELECT INDEX_CATALOG,
NULL,
INDEX_NAME,
TABLE_CATALOG,
NULL,
TABLE_NAME,
'UNIQUE',
0,
0,
NULL
FROM TEMP.SCHEMAINDEXES
WHERE PRIMARY_KEY = 0 AND [UNIQUE] = 1
UNION
SELECT CONSTRAINT_CATALOG,
NULL,
CONSTRAINT_NAME,
TABLE_CATALOG,
NULL,
TABLE_NAME,
CONSTRAINT_TYPE,
IS_DEFERRABLE,
INITIALLY_DEFERRED,
NULL
FROM TEMP.SCHEMAFOREIGNKEYS;
}}
###############################################################################
runTest {test data-1.27 {EF6 SQL_CONSTRAINTS resource} -body {
object invoke -flags +NonPublic \
System.Data.SQLite.EF6.Properties.Resources SQL_CONSTRAINTS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.EF6} -result {
CREATE TEMP VIEW SCHEMACONSTRAINTS AS
SELECT INDEX_CATALOG AS CONSTRAINT_CATALOG,
NULL AS CONSTRAINT_SCHEMA,
INDEX_NAME AS CONSTRAINT_NAME,
TABLE_CATALOG,
NULL AS TABLE_SCHEMA,
TABLE_NAME,
'PRIMARY KEY' AS CONSTRAINT_TYPE,
0 AS IS_DEFERRABLE,
0 AS INITIALLY_DEFERRED,
NULL AS CHECK_CLAUSE
FROM TEMP.SCHEMAINDEXES
WHERE PRIMARY_KEY = 1
UNION
SELECT INDEX_CATALOG,
NULL,
INDEX_NAME,
TABLE_CATALOG,
NULL,
TABLE_NAME,
'UNIQUE',
0,
0,
NULL
FROM TEMP.SCHEMAINDEXES
WHERE PRIMARY_KEY = 0 AND [UNIQUE] = 1
UNION
SELECT CONSTRAINT_CATALOG,
NULL,
CONSTRAINT_NAME,
TABLE_CATALOG,
NULL,
TABLE_NAME,
CONSTRAINT_TYPE,
IS_DEFERRABLE,
INITIALLY_DEFERRED,
NULL
FROM TEMP.SCHEMAFOREIGNKEYS;
}}
###############################################################################
runTest {test data-1.28 {SQLiteDataReader GetValues w/collection} -setup {
setupDb [set fileName data-1.28.db]
} -body {
sql execute $db {
CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y TEXT);
INSERT INTO t1 (x, y) VALUES(1, 'aardvark');
INSERT INTO t1 (x, y) VALUES(2, 'bear');
INSERT INTO t1 (x, y) VALUES(3, 'chicken');
INSERT INTO t1 (x, y) VALUES(4, 'duck');
INSERT INTO t1 (x, y) VALUES(5, 'elephant');
INSERT INTO t1 (x, y) VALUES(6, 'frog');
INSERT INTO t1 (x, y) VALUES(7, 'goose');
INSERT INTO t1 (x, y) VALUES(8, 'horse');
INSERT INTO t1 (x, y) VALUES(9, 'iguana');
INSERT INTO t1 (x, y) VALUES(10, 'jellyfish');
INSERT INTO t1 (x, y) VALUES(11, 'kangaroo');
INSERT INTO t1 (x, y) VALUES(12, 'llama');
INSERT INTO t1 (x, y) VALUES(13, 'moose');
INSERT INTO t1 (x, y) VALUES(14, 'newt');
INSERT INTO t1 (x, y) VALUES(15, 'ostrich');
INSERT INTO t1 (x, y) VALUES(16, 'pig');
INSERT INTO t1 (x, y) VALUES(17, 'quail');
INSERT INTO t1 (x, y) VALUES(18, 'rhinoceros');
INSERT INTO t1 (x, y) VALUES(19, 'shark');
INSERT INTO t1 (x, y) VALUES(20, 'tiger');
INSERT INTO t1 (x, y) VALUES(21, 'unicorn');
INSERT INTO t1 (x, y) VALUES(22, 'viper');
INSERT INTO t1 (x, y) VALUES(23, 'weasel');
INSERT INTO t1 (x, y) VALUES(24, 'xerus');
INSERT INTO t1 (x, y) VALUES(25, 'yak');
INSERT INTO t1 (x, y) VALUES(26, 'zebra');
}
set connection [getDbConnection]
set command [object create -alias System.Data.SQLite.SQLiteCommand \
"SELECT x, y, x, y FROM t1 ORDER BY y DESC;" $connection]
set reader [$command -alias ExecuteReader]
set collection [$reader -alias GetValues]
set result [list]
object foreach -alias item $collection {
lappend result [$collection GetValues $item]
}
set result
} -cleanup {
freeDbConnection
unset -nocomplain result item collection reader command connection
cleanupDb $fileName; # NOTE: After object disposal.
unset -nocomplain db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{{26 26} {zebra zebra}}}
###############################################################################
runTest {test data-1.29 {LINQ ISQLiteSchemaExtensions.BuildTempSchema} -setup {
setupDb [set fileName data-1.29.db]
} -body {
set connection [getDbConnection]
set providerServices [object invoke -flags +NonPublic \
System.Data.SQLite.Linq.SQLiteProviderServices Instance]
object invoke -flags +NonPublic -type \
System.Data.SQLite.ISQLiteSchemaExtensions $providerServices \
BuildTempSchema $connection
} -cleanup {
cleanupDb $fileName
freeDbConnection
unset -nocomplain providerServices connection db fileName
} -constraints {eagle SQLite System.Data.SQLite System.Data.SQLite.Linq} \
-result {}}
###############################################################################
runTest {test data-1.30 {EF6 ISQLiteSchemaExtensions.BuildTempSchema} -setup {
setupDb [set fileName data-1.30.db]
} -body {
set connection [getDbConnection]
set providerServices [object invoke -flags +NonPublic \
System.Data.SQLite.EF6.SQLiteProviderServices Instance]
object invoke -flags +NonPublic -type \
System.Data.SQLite.ISQLiteSchemaExtensions $providerServices \
BuildTempSchema $connection
} -cleanup {
cleanupDb $fileName
freeDbConnection
unset -nocomplain providerServices connection db fileName
} -constraints {eagle SQLite System.Data.SQLite System.Data.SQLite.EF6} \
-result {}}
###############################################################################
runTest {test data-1.31 {VARCHAR / NVARCHAR types with spaces} -body {
list [object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
TypeNameToDbType null VARCHAR None] \
[object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
TypeNameToDbType null NVARCHAR None] \
[object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
TypeNameToDbType null VARCHAR(1) None] \
[object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
TypeNameToDbType null NVARCHAR(1) None] \
[object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
TypeNameToDbType null "VARCHAR (1)" None] \
[object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
TypeNameToDbType null "NVARCHAR (1)" None] \
} -constraints {eagle System.Data.SQLite} -result \
{AnsiString String AnsiString String AnsiString String}}
###############################################################################
runTest {test data-1.32 {SetMemoryStatus method} -setup {
#
# NOTE: Make sure that SQLite core library is completely shutdown prior to
# starting this test.
#
shutdownSQLite $test_channel
#
# NOTE: Create an IntPtr instance with a value of zero.
#
set zero [object invoke -create IntPtr Zero]
#
# NOTE: Create an instance of the core SQLite library interop wrapper class.
#
set sqlite3 [object create -flags +NonPublic System.Data.SQLite.SQLite3 \
Default Unspecified null $zero null true]
} -body {
set result(rc1) [object invoke -flags +NonPublic $sqlite3 SetMemoryStatus \
false]
set result(before) [object invoke -flags +NonPublic $sqlite3 MemoryUsed]
set result(ptr1) [object invoke -create -flags +NonPublic \
System.Data.SQLite.UnsafeNativeMethods sqlite3_malloc 100]
set result(after1) [object invoke -flags +NonPublic $sqlite3 MemoryUsed]
object invoke -flags +NonPublic System.Data.SQLite.UnsafeNativeMethods \
sqlite3_free $result(ptr1)
set result(after2) [object invoke -flags +NonPublic $sqlite3 MemoryUsed]
set result(rc2) [object invoke -flags +NonPublic $sqlite3 SetMemoryStatus \
true]
set result(rc3) [object invoke -flags +NonPublic \
System.Data.SQLite.UnsafeNativeMethods sqlite3_shutdown]
set result(rc4) [object invoke -flags +NonPublic $sqlite3 SetMemoryStatus \
true]
set result(ptr2) [object invoke -create -flags +NonPublic \
System.Data.SQLite.UnsafeNativeMethods sqlite3_malloc 100]
set result(after3) [object invoke -flags +NonPublic $sqlite3 MemoryUsed]
object invoke -flags +NonPublic System.Data.SQLite.UnsafeNativeMethods \
sqlite3_free $result(ptr2)
set result(after4) [object invoke -flags +NonPublic $sqlite3 MemoryUsed]
list $result(rc1) $result(rc2) $result(rc3) $result(rc4) $result(ptr1) \
$result(ptr2) $result(before) $result(after1) $result(after2) \
$result(after3) $result(after4) \
[expr {$result(before) == $result(after1)}] \
[expr {$result(before) == $result(after2)}] \
[expr {$result(after3) > 0}] \
[expr {$result(before) < $result(after3)}] \
[expr {$result(after1) < $result(after3)}] \
[expr {$result(after4) < $result(after3)}]
} -cleanup {
catch {
#
# NOTE: Make sure that SQLite core library is completely shutdown prior
# to attempting to reconfigure the memory status setting.
#
shutdownSQLite $test_channel
#
# NOTE: Attempt to make sure the default value for the process-wide
# memory usage tracking setting is restored. This is not 100%
# reliable because we have no idea what the original value was
# upon entry into this test (i.e. because the underlying core
# library property is currently write-only).
#
object invoke -flags +NonPublic System.Data.SQLite.UnsafeNativeMethods \
sqlite3_config_int SQLITE_CONFIG_MEMSTATUS 1
}
unset -nocomplain result sqlite3 zero
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^Ok Misuse Ok Ok System#IntPtr#\d+ System#IntPtr#\d+ \d+ \d+\
\d+ \d+ \d+ True True True True True True$}}
###############################################################################
runTest {test data-1.33 {SQLiteConnection.Open with SetDefaults=False} -setup {
setupDb [set fileName data-1.33.db] "" "" "" "" SetDefaults=False
} -body {
set result [list]
lappend result [sql execute -execute scalar $db "PRAGMA page_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA max_page_count;"]
lappend result [sql execute -execute scalar $db "PRAGMA legacy_file_format;"]
lappend result [sql execute -execute scalar $db "PRAGMA synchronous;"]
lappend result [sql execute -execute scalar $db "PRAGMA cache_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA journal_mode;"]
lappend result [sql execute -execute scalar $db "PRAGMA foreign_keys;"]
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1024 1073741823 0 2 2000 delete 0}}
###############################################################################
runTest {test data-1.34 {SQLiteConnection.Open with SetDefaults=True} -setup {
setupDb [set fileName data-1.34.db] "" "" "" "" SetDefaults=True
} -body {
set result [list]
lappend result [sql execute -execute scalar $db "PRAGMA page_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA max_page_count;"]
lappend result [sql execute -execute scalar $db "PRAGMA legacy_file_format;"]
lappend result [sql execute -execute scalar $db "PRAGMA synchronous;"]
lappend result [sql execute -execute scalar $db "PRAGMA cache_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA journal_mode;"]
lappend result [sql execute -execute scalar $db "PRAGMA foreign_keys;"]
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1024 1073741823 0 2 2000 delete 0}}
###############################################################################
runTest {test data-1.35 {SQLiteConnection.Open without SetDefaults} -setup {
setupDb [set fileName data-1.35.db]
} -body {
set result [list]
lappend result [sql execute -execute scalar $db "PRAGMA page_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA max_page_count;"]
lappend result [sql execute -execute scalar $db "PRAGMA legacy_file_format;"]
lappend result [sql execute -execute scalar $db "PRAGMA synchronous;"]
lappend result [sql execute -execute scalar $db "PRAGMA cache_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA journal_mode;"]
lappend result [sql execute -execute scalar $db "PRAGMA foreign_keys;"]
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1024 1073741823 0 2 2000 delete 0}}
###############################################################################
runTest {test data-1.36 {SQLiteConnection.Open with PRAGMA overrides} -setup {
#
# NOTE: Attempt to open a connection with all available PRAGMA settings
# set to non-default values in the connection string.
#
setupDb [set fileName data-1.36.db] "" "" "" "" [join [list \
"Page Size=4096" "Max Page Count=2048" "Legacy Format=True" \
Synchronous=Normal "Cache Size=4096" "Journal Mode=Wal" \
"Foreign Keys=True"] \;]
} -body {
set result [list]
lappend result [sql execute -execute scalar $db "PRAGMA page_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA max_page_count;"]
lappend result [sql execute -execute scalar $db "PRAGMA legacy_file_format;"]
lappend result [sql execute -execute scalar $db "PRAGMA synchronous;"]
lappend result [sql execute -execute scalar $db "PRAGMA cache_size;"]
lappend result [sql execute -execute scalar $db "PRAGMA journal_mode;"]
lappend result [sql execute -execute scalar $db "PRAGMA foreign_keys;"]
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{4096 2048 1 1 4096 wal 1}}
###############################################################################
runTest {test data-1.37 {sqlite3_win32_set_directory function} -setup {
set directory(base) [getDatabaseDirectory]
if {[string length $directory(base)] == 0 || \
![file exists $directory(base)] || \
![file isdirectory $directory(base)]} then {
error [appendArgs "base directory \"" $directory(base) "\" is invalid"]
}
set directory(data) [file join $directory(base) \
[appendArgs sqlite .data. [pid]]]
file mkdir $directory(data)
tputs $test_channel [appendArgs "---- created data directory \"" \
$directory(data) \"\n]
set directory(temp) [file join $directory(base) \
[appendArgs sqlite .temp. [pid]]]
file mkdir $directory(temp)
tputs $test_channel [appendArgs "---- created temporary directory \"" \
$directory(temp) \"\n]
proc threadStart {} {
while {$::i < 1000} {
set ::found(temp) [expr \
{[llength [file list $::directory(temp) etilqs_*]] > 0}]
if {$::found(temp)} then {
return
}
}
}
object import System.Threading
} -body {
set result [list]
#
# NOTE: Attempt to modify the process-wide data and temporary directory
# settings for the SQLite core library.
#
lappend result [object invoke -flags +NonPublic \
System.Data.SQLite.UnsafeNativeMethods sqlite3_win32_set_directory 1 \
$directory(data)]
lappend result [object invoke -flags +NonPublic \
System.Data.SQLite.UnsafeNativeMethods sqlite3_win32_set_directory 2 \
$directory(temp)]
setupDb [set fileName data-1.37.db] "" "" "" "" "" false false false false
sql execute $db "CREATE TABLE t1(x NOT NULL);"
for {set i 1} {$i < 100} {incr i} {
sql execute $db "INSERT INTO t1 (x) VALUES(?);" [list param1 String $i]
}
set found(data) [expr \
{[llength [file list $directory(data) $fileName]] == 1}]
set t [object create -alias Thread threadStart]
sql execute $db "BEGIN TRANSACTION;"; $t Start
for {set i 1} {$i < 1000} {incr i} {
#
# NOTE: Execute a query that should force the creation of a temporary file
# for its statement journal.
#
sql execute $db "UPDATE t1 SET x = ?;" [list param1 String $i]
#
# NOTE: Give the other thread some time to notice the temporary file.
#
after [expr {int(rand() * 1000)}]
#
# NOTE: Stop when the other thread confirms that the temporary file was
# created in the correct directory.
#
if {[info exists found(temp)] && $found(temp)} then {
break
}
}
$t Join; sql execute $db "COMMIT TRANSACTION;"
lappend result $found(data) [expr {[info exists found(temp)] ? \
$found(temp) : False}]; set result
} -cleanup {
#
# NOTE: Close the database; however, do not attempt to delete the file as
# it is not located in the database directory known to the cleanupDb
# procedure (i.e. the one returned by getDatabaseDirectory).
#
cleanupDb $fileName db true false false
#
# NOTE: Attempt to restore the process-wide data and temporary directory
# settings for the SQLite core library.
#
catch {
object invoke -flags +NonPublic System.Data.SQLite.UnsafeNativeMethods \
sqlite3_win32_set_directory 1 null
}
catch {
object invoke -flags +NonPublic System.Data.SQLite.UnsafeNativeMethods \
sqlite3_win32_set_directory 2 null
}
if {[info exists directory(data)] && \
[file exists $directory(data)] && \
[file isdirectory $directory(data)]} then {
file delete -recursive -force $directory(data)
}
if {[info exists directory(temp)] && \
[file exists $directory(temp)] && \
[file isdirectory $directory(temp)]} then {
file delete -recursive -force $directory(temp)
}
object unimport -importpattern System.Threading
if {[info exists t] && [cleanupThread $t]} then {
unset t
}
catch {object removecallback threadStart}
unset -nocomplain t found i db fileName result directory
rename threadStart ""
} -constraints {eagle windows monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite sqlite3_win32_set_directory} -result {Ok Ok True True}}
###############################################################################
runTest {test data-1.38 {serialization of SQLiteException} -body {
set serializer [object create -alias \
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter]
set stream [object create -alias System.IO.MemoryStream]
set exception(1) [object create -alias \
System.Data.SQLite.SQLiteException 14 "this is a test"]; # CantOpen
$serializer Serialize $stream $exception(1)
$stream Seek 0 Begin
set exception(2) [$serializer -alias Deserialize $stream]
list [$exception(1) ResultCode] [$exception(1) Message] \
[$exception(2) ResultCode] [$exception(2) Message] \
[expr {[$exception(1) ResultCode] eq [$exception(2) ResultCode]}] \
[expr {[$exception(1) Message] eq [$exception(2) Message]}]
} -cleanup {
unset -nocomplain exception stream serializer
} -constraints {eagle SQLite System.Data.SQLite} -result \
[string map [list \n \r\n] {CantOpen {unable to open database file
this is a test} CantOpen {unable to open database file
this is a test} True True}]}
###############################################################################
runTest {test data-1.39 {unencrypted database, with password} -setup {
setupDb [set fileName data-1.39.db]
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=12345;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -match regexp -result {^1\
\{System\.Data\.SQLite\.SQLiteException \(0x80004005\): file is encrypted or is\
not a database.*?\} 1 \{System\.Data\.SQLite\.SQLiteException \(0x80004005\):\
file is encrypted or is not a database.*?\} 0 1 0 2$}}
###############################################################################
runTest {test data-1.40 {encrypted database, wrong password} -setup {
setupDb [set fileName data-1.40.db] "" "" "" "" "Password=12345;"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=12346;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=12345;" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -match regexp -result {^1\
\{System\.Data\.SQLite\.SQLiteException \(0x80004005\): file is encrypted or is\
not a database.*?\} 1 \{System\.Data\.SQLite\.SQLiteException \(0x80004005\):\
file is encrypted or is not a database.*?\} 0 1 0 2$}}
###############################################################################
runTest {test data-1.41 {encrypted database, password w/start-space} -setup {
setupDb [set fileName data-1.41.db] "" "" "" "" "Password= 1234;"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=1234;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password= 1234;" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -result {0 1 0 1 0 1 0 3}}
###############################################################################
runTest {test data-1.42 {encrypted database, w/quoted-start-space} -setup {
setupDb [set fileName data-1.42.db] "" "" "" "" "Password=\" 1234\";"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=1234;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=\" 1234\";" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -match regexp -result {^1\
\{System\.Data\.SQLite\.SQLiteException \(0x80004005\): file is encrypted or is\
not a database.*?\} 1 \{System\.Data\.SQLite\.SQLiteException \(0x80004005\):\
file is encrypted or is not a database.*?\} 0 1 0 2$}}
###############################################################################
runTest {test data-1.43 {encrypted database, password w/mid-space} -setup {
setupDb [set fileName data-1.43.db] "" "" "" "" "Password=12 45;"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=1245;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=12 45;" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -match regexp -result {^1\
\{System\.Data\.SQLite\.SQLiteException \(0x80004005\): file is encrypted or is\
not a database.*?\} 1 \{System\.Data\.SQLite\.SQLiteException \(0x80004005\):\
file is encrypted or is not a database.*?\} 0 1 0 2$}}
###############################################################################
runTest {test data-1.44 {encrypted database, password w/end-space} -setup {
setupDb [set fileName data-1.44.db] "" "" "" "" "Password=1234 ;"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=1234;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=1234 ;" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -result {0 1 0 1 0 1 0 3}}
###############################################################################
runTest {test data-1.45 {encrypted database, w/quoted-end-space} -setup {
setupDb [set fileName data-1.45.db] "" "" "" "" "Password=\"1234 \";"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=1234;" true false
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=\"1234 \";" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -match regexp -result {^1\
\{System\.Data\.SQLite\.SQLiteException \(0x80004005\): file is encrypted or is\
not a database.*?\} 1 \{System\.Data\.SQLite\.SQLiteException \(0x80004005\):\
file is encrypted or is not a database.*?\} 0 1 0 2$}}
###############################################################################
runTest {test data-1.46 {encrypted database, password via builder} -setup {
setupDb [set fileName data-1.46.db] "" "" "" "" "Password=67 89;"
} -body {
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
cleanupDb $fileName db true false false
set connectionStringBuilder [object create -alias \
System.Data.SQLite.SQLiteConnectionStringBuilder]
$connectionStringBuilder DataSource \
[file join [getDatabaseDirectory] $fileName]
$connectionStringBuilder Password "67 89"
set connection [object create -alias \
System.Data.SQLite.SQLiteConnection \
[$connectionStringBuilder ToString] true]
$connection Open; addDbConnection $connection
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
cleanupDb $fileName db true false false
setupDb $fileName "" "" "" "" "Password=\"67 89\";" true false
lappend result [catch {sql execute $db \
"INSERT INTO t1 (x) VALUES(1);"} error] $error
lappend result [catch {sql execute -execute scalar $db \
"SELECT COUNT(*) FROM t1;"} error] $error
set result
} -cleanup {
unset -nocomplain connection
cleanupDb $fileName; # NOTE: After object disposal.
unset -nocomplain connectionStringBuilder error result db fileName
} -constraints {eagle defineConstant.System.Data.SQLite.INTEROP_CODEC monoBug28\
command.sql compile.DATA SQLite System.Data.SQLite} -result {0 1 0 1 0 1 0 3}}
###############################################################################
runTest {test data-1.47 {quoted connection string properties} -setup {
unset -nocomplain result list pair strings string
} -body {
set result [list]
set strings [list \
"OneTwo=ThreeFour" "\"OneTwo\"=\"ThreeFour\"" \
"One Two=Three Four" "\"One Two\"=\"Three Four\"" \
"OneTwo=ThreeFour;" "\"OneTwo\"=\"ThreeFour\";" \
"One Two=Three Four;" "\"One Two\"=\"Three Four\";"]
foreach string $strings {
set list [object invoke -flags +NonPublic \
System.Data.SQLite.SQLiteConnection ParseConnectionString \
$string false]
object foreach -alias pair $list {
lappend result [list [$pair Key] [$pair Value]]
}
}
set result
} -cleanup {
unset -nocomplain result list pair strings string
} -constraints {eagle System.Data.SQLite} -result {{OneTwo ThreeFour} {OneTwo\
ThreeFour} {{One Two} {Three Four}} {{One Two} {Three Four}} {OneTwo ThreeFour}\
{OneTwo ThreeFour} {{One Two} {Three Four}} {{One Two} {Three Four}}}}
###############################################################################
runTest {test data-1.48 {rollback to nested savepoint} -setup {
setupDb [set fileName data-1.48.db]
} -body {
sql execute $db "BEGIN IMMEDIATE TRANSACTION;"
sql execute $db "SAVEPOINT one;"
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "SAVEPOINT two;"
sql execute $db "INSERT INTO t1 (x) VALUES(1);"
lappend result [sql execute -execute scalar $db "SELECT COUNT(*) FROM t1;"]
sql execute $db "SAVEPOINT three;"
sql execute $db "INSERT INTO t1 (x) VALUES(2);"
lappend result [sql execute -execute scalar $db "SELECT COUNT(*) FROM t1;"]
sql execute $db "ROLLBACK TRANSACTION TO SAVEPOINT three;"
lappend result [sql execute -execute scalar $db "SELECT COUNT(*) FROM t1;"]
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1 2 1}}
###############################################################################
runTest {test data-1.49 {NoExtensionFunctions connection flag} -setup {
setupDb [set fileName data-1.49.db]
} -body {
set result [list]
lappend result [catch {sql execute -execute scalar $db \
"SELECT replicate('1234', 2);"} output] $output
cleanupDb $fileName
setupDb $fileName "" "" "" NoExtensionFunctions
lappend result [catch {sql execute -execute scalar $db \
"SELECT replicate('1234', 3);"} output] $output
cleanupDb $fileName
setupDb $fileName
lappend result [catch {sql execute -execute scalar $db \
"SELECT replicate('1234', 4);"} output] $output
set result
} -cleanup {
cleanupDb $fileName
unset -nocomplain output result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^0 12341234 1 \{System\.Data\.SQLite\.SQLiteException\
\(0x80004005\): SQL logic error or missing database.*?\} 0 1234123412341234$}}
###############################################################################
runTest {test data-1.50 {column name and index lookup} -setup {
setupDb [set fileName data-1.50.db]
} -body {
sql execute $db {
CREATE TABLE t1(x, y, z);
INSERT INTO t1 (x, y, z) VALUES(1, 'foo', 1234);
}
set dataReader [sql execute -execute reader -format datareader \
-alias $db "SELECT x, y, z FROM t1;"]
set result [list]
while {[$dataReader Read]} {
lappend result \
[list [$dataReader GetName 0] [$dataReader GetOrdinal x] \
[$dataReader Item x]] \
[list [$dataReader GetName 1] [$dataReader GetOrdinal y] \
[$dataReader Item y]] \
[list [$dataReader GetName 2] [$dataReader GetOrdinal z] \
[$dataReader Item z]]
}
set result
} -cleanup {
unset -nocomplain dataReader
cleanupDb $fileName
unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{{x 0 1} {y 1 foo} {z 2 1234}}}
###############################################################################
runTest {test data-1.51 {nullable value types} -setup {
setupDb [set fileName data-1.51.db]
} -body {
sql execute $db {
CREATE TABLE t1(x INTEGER);
INSERT INTO t1 (x) VALUES(NULL);
INSERT INTO t1 (x) VALUES(1);
}
set dataReader [sql execute -execute reader -format datareader \
-alias $db "SELECT x FROM t1 ORDER BY x;"]
set result [list]
while {[$dataReader Read]} {
foreach {a b c d e} [list "" "" "" "" ""] break
set x [$dataReader GetOrdinal x]
foreach {a b c e} [list \
[$dataReader GetName $x] [$dataReader GetValue $x] \
[catch {$dataReader GetInt64 $x} d] [$dataReader Item x]] break
lappend result [list $x $a $b $c $d $e]
}
set result
} -cleanup {
unset -nocomplain dataReader
cleanupDb $fileName
unset -nocomplain e d c b a x result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^\{0 x System#DBNull#\d+ 1\
\{System\.Reflection\.TargetInvocationException: Exception has been thrown by\
the target of an invocation\. ---> System\.InvalidCastException:.*\}\
System#DBNull#\d+\} \{0 x 1 0 1 1\}$}}
###############################################################################
runTest {test data-1.52 {static SQLiteCommand.Execute method} -setup {
unset -nocomplain result sql
} -body {
set sql(1) { \
CREATE TABLE t1(x); \
INSERT INTO t1 (x) VALUES (NULL); \
SELECT x FROM t1 ORDER BY x; \
}
set sql(2) { \
CREATE TABLE t1(x); \
INSERT INTO t1 (x) VALUES (?); \
SELECT x FROM t1 ORDER BY x; \
}
set result(1) [object invoke System.Data.SQLite.SQLiteCommand Execute \
"this will not execute" None null]
set result(2) [object invoke System.Data.SQLite.SQLiteCommand Execute \
$sql(1) NonQuery null]
set result(3) [object invoke System.Data.SQLite.SQLiteCommand Execute \
$sql(1) Scalar null]
set result(4) [object invoke System.Data.SQLite.SQLiteCommand Execute \
$sql(1) Reader null]
set result(5) [object invoke System.Data.SQLite.SQLiteCommand Execute \
"this will not execute" None null 1]
set result(6) [object invoke System.Data.SQLite.SQLiteCommand Execute \
$sql(2) NonQuery null 1]
set result(7) [object invoke System.Data.SQLite.SQLiteCommand Execute \
$sql(2) Scalar null 1]
set result(8) [object invoke System.Data.SQLite.SQLiteCommand Execute \
$sql(2) Reader null 1]
list $result(1) $result(2) $result(3) $result(4) $result(5) $result(6) \
$result(7) $result(8)
} -cleanup {
unset -nocomplain result sql
} -constraints {eagle monoBug28 SQLite System.Data.SQLite} -match regexp \
-result {^\{\} 1 System#DBNull#\d+ System#Data#SQLite#SQLiteDataReader#\d+ \{\}\
1 1 System#Data#SQLite#SQLiteDataReader#\d+$}}
###############################################################################
runTest {test data-1.53 {BindAllAsText w/DateTime} -setup {
setupDb [set fileName data-1.53.db] "" Ticks Utc BindAllAsText
} -body {
sql execute $db "CREATE TABLE t1(x);"
list [sql execute $db "INSERT INTO t1 (x) VALUES(?);" \
[list param1 DateTime [set dateTime [object invoke DateTime Parse \
"2000.02.29 13:59:58.1234567Z"]]]] [sql execute -execute reader \
-format list $db "SELECT x FROM t1;"]
} -cleanup {
cleanupDb $fileName
unset -nocomplain dateTime db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1 630874007980000000}}
###############################################################################
runTest {test data-1.54 {bind SQLiteFunction to one SQLiteConnection} -setup {
set fileName data-1.54.db
} -body {
set id [object invoke Interpreter.GetActive NextId]
set dataSource [file join [getDatabaseDirectory] $fileName]
set sql { \
SELECT MyRandom(); \
}
unset -nocomplain results errors
set code [compileCSharpWith [subst {
using System;
using System.Data.SQLite;
namespace _Dynamic${id}
{
public class Test${id} : SQLiteFunction
{
private Random random;
///////////////////////////////////////////////////////////////////////
public Test${id}()
{
random = new Random();
}
///////////////////////////////////////////////////////////////////////
public override object Invoke(
object\[\] args
)
{
return random.Next();
}
///////////////////////////////////////////////////////////////////////
public static object DoTest(bool bindFunction)
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=${dataSource};[getFlagsProperty]"))
{
connection.Open();
if (bindFunction)
{
connection.BindFunction(new SQLiteFunctionAttribute(
"MyRandom", 0, FunctionType.Scalar), new Test${id}());
}
using (SQLiteCommand command = new SQLiteCommand("${sql}",
connection))
{
return command.ExecuteScalar();
}
}
}
///////////////////////////////////////////////////////////////////////
public static void Main()
{
// do nothing.
}
}
}
}] true true true results errors System.Data.SQLite.dll]
list $code $results \
[expr {[info exists errors] ? $errors : ""}] \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} DoTest false
} result] : [set result ""]}] $result \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} DoTest true
} result] : [set result ""]}] $result \
[expr {$code eq "Ok" ? [catch {
object invoke _Dynamic${id}.Test${id} DoTest false
} result] : [set result ""]}] $result
} -cleanup {
cleanupDb $fileName
unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result \
[string map [list \n \r\n] {^Ok System#CodeDom#Compiler#CompilerResults#\d+\
\{\} 1 \{System\.Reflection\.TargetInvocationException: Exception has been\
thrown by the target of an invocation\. --->\
System\.Data\.SQLite\.SQLiteException: SQL logic error or missing database
no such function: MyRandom.*\} 0 (?:-)?\d+ 1\
\{System\.Reflection\.TargetInvocationException: Exception has been thrown by\
the target of an invocation\. ---> System\.Data\.SQLite\.SQLiteException: SQL\
logic error or missing database
no such function: MyRandom.*\}$}]}
###############################################################################
runTest {test data-1.55 {SQLiteConvert TypeNameToDbType} -setup {
unset -nocomplain result typeName
} -body {
foreach typeName [list \
BIGINT BIGUINT BINARY BIT BLOB BOOL BOOLEAN CHAR CLOB COUNTER CURRENCY \
DATE DATETIME DECIMAL DOUBLE FLOAT GENERAL GUID IDENTITY IMAGE INT INT8 \
INT16 INT32 INT64 INTEGER INTEGER8 INTEGER16 INTEGER32 INTEGER64 \
LOGICAL LONG LONGCHAR LONGTEXT LONGVARCHAR MEMO MONEY NCHAR NOTE NTEXT \
NUMBER NUMERIC NVARCHAR OLEOBJECT RAW REAL SINGLE SMALLDATE SMALLINT \
SMALLUINT STRING TEXT TIME TIMESTAMP TINYINT TINYSINT UINT UINT8 UINT16 \
UINT32 UINT64 ULONG UNIQUEIDENTIFIER UNSIGNEDINTEGER UNSIGNEDINTEGER8 \
UNSIGNEDINTEGER16 UNSIGNEDINTEGER32 UNSIGNEDINTEGER64 VARBINARY VARCHAR \
VARCHAR2 YESNO] {
lappend result [list $typeName [object invoke -flags +NonPublic \
System.Data.SQLite.SQLiteConvert TypeNameToDbType null $typeName None]]
}
set result
} -cleanup {
unset -nocomplain result typeName
} -constraints {eagle System.Data.SQLite} -result {{BIGINT Int64} {BIGUINT\
UInt64} {BINARY Binary} {BIT Boolean} {BLOB Binary} {BOOL Boolean} {BOOLEAN\
Boolean} {CHAR AnsiStringFixedLength} {CLOB String} {COUNTER Int64} {CURRENCY\
Decimal} {DATE DateTime} {DATETIME DateTime} {DECIMAL Decimal} {DOUBLE Double}\
{FLOAT Double} {GENERAL Binary} {GUID Guid} {IDENTITY Int64} {IMAGE Binary}\
{INT Int32} {INT8 SByte} {INT16 Int16} {INT32 Int32} {INT64 Int64} {INTEGER\
Int64} {INTEGER8 SByte} {INTEGER16 Int16} {INTEGER32 Int32} {INTEGER64 Int64}\
{LOGICAL Boolean} {LONG Int64} {LONGCHAR String} {LONGTEXT String} {LONGVARCHAR\
String} {MEMO String} {MONEY Decimal} {NCHAR StringFixedLength} {NOTE String}\
{NTEXT String} {NUMBER Decimal} {NUMERIC Decimal} {NVARCHAR String} {OLEOBJECT\
Binary} {RAW Binary} {REAL Double} {SINGLE Single} {SMALLDATE DateTime}\
{SMALLINT Int16} {SMALLUINT UInt16} {STRING String} {TEXT String} {TIME\
DateTime} {TIMESTAMP DateTime} {TINYINT Byte} {TINYSINT SByte} {UINT UInt32}\
{UINT8 Byte} {UINT16 UInt16} {UINT32 UInt32} {UINT64 UInt64} {ULONG UInt64}\
{UNIQUEIDENTIFIER Guid} {UNSIGNEDINTEGER UInt64} {UNSIGNEDINTEGER8 Byte}\
{UNSIGNEDINTEGER16 UInt16} {UNSIGNEDINTEGER32 UInt32} {UNSIGNEDINTEGER64\
UInt64} {VARBINARY Binary} {VARCHAR AnsiString} {VARCHAR2 AnsiString} {YESNO\
Boolean}}}
###############################################################################
runTest {test data-1.56 {totype extension} -setup {
setupDb [set fileName data-1.56.db]
} -body {
set connection [getDbConnection]
set result [list]
$connection EnableExtensions true
$connection LoadExtension [getCoreBinaryFileName] sqlite3_totype_init
lappend result [sql execute -execute scalar $db "SELECT tointeger('1');"]
lappend result [sql execute -execute scalar $db "SELECT tointeger('1x');"]
lappend result [sql execute -execute scalar $db "SELECT toreal('1.01');"]
lappend result [sql execute -execute scalar $db "SELECT toreal('1.0x');"]
set result
} -cleanup {
freeDbConnection
unset -nocomplain result connection
cleanupDb $fileName
unset -nocomplain db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1 {} 1.01 {}}}
###############################################################################
runTest {test data-1.57 {regexp extension} -setup {
setupDb [set fileName data-1.57.db]
} -body {
unset -nocomplain pattern result
set connection [getDbConnection]
set result(1) [list]
$connection EnableExtensions true
$connection LoadExtension [getCoreBinaryFileName] sqlite3_regexp_init
set pattern(1) {^\d+ [A-Z]{1,3}$}; # valid
set pattern(2) {^\d+ [C]($}; # invalid
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT REGEXP('$pattern(1)', '1 AB');"
} result(2)] $result(2)
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT REGEXP('$pattern(2)', '1 AB');"
} result(2)] [string trim [lindex [split $result(2) \n] 1]]
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT '1 AB' REGEXP '$pattern(1)';"
} result(2)] $result(2)
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT '1 AB' REGEXP '$pattern(2)';"
} result(2)] [string trim [lindex [split $result(2) \n] 1]]
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT REGEXP('$pattern(1)', '2');"
} result(2)] $result(2)
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT REGEXP('$pattern(2)', '2');"
} result(2)] [string trim [lindex [split $result(2) \n] 1]]
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT '2' REGEXP '$pattern(1)';"
} result(2)] $result(2)
lappend result(1) [catch {
sql execute -execute scalar $db "SELECT '2' REGEXP '$pattern(2)';"
} result(2)] [string trim [lindex [split $result(2) \n] 1]]
set result(1)
} -cleanup {
freeDbConnection
unset -nocomplain pattern result connection
cleanupDb $fileName
unset -nocomplain db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{0 1 1 {unmatched '('} 0 1 1 {unmatched '('} 0 0 1 {unmatched '('} 0 0 1\
{unmatched '('}}}
###############################################################################
reportSQLiteResources $test_channel
###############################################################################
runTest {test data-1.58 {SQLiteConnection.ReleaseMemory method} -setup {
setupDb [set fileName data-1.58.db]
} -body {
set result [list]
set nFree 0; set resetOk false; set nLargest 0
set code [object invoke \
System.Data.SQLite.SQLiteConnection ReleaseMemory \
-1 true true nFree resetOk nLargest]
lappend result [list $code $nFree $resetOk $nLargest]
sql execute $db "CREATE TABLE t1(x);"
sql execute $db "INSERT INTO t1 (x) VALUES(RANDOMBLOB(1048576));"
set nFree 0; set resetOk false; set nLargest 0
set code [object invoke \
System.Data.SQLite.SQLiteConnection ReleaseMemory \
-1 true true nFree resetOk nLargest]
tputs $test_channel [appendArgs \
"---- memory released by SQLite... " $nFree " bytes\n"]
lappend result [list $code $nFree $resetOk $nLargest]
cleanupDb $fileName
checkForSQLiteDirectories $test_channel true
set nFree 0; set resetOk false; set nLargest 0
set code [object invoke \
System.Data.SQLite.SQLiteConnection ReleaseMemory \
-1 true true nFree resetOk nLargest]
tputs $test_channel [appendArgs \
"---- largest free SQLite heap block... " $nLargest " bytes\n"]
lappend result [list $code $nFree $resetOk $nLargest]
} -cleanup {
cleanupDb $fileName
unset -nocomplain nLargest resetOk nFree code result db fileName
} -constraints {eagle monoBug28 configuration.Release command.sql compile.DATA\
SQLite System.Data.SQLite} -match regexp -result {^\{Busy 0 False 0\} \{Busy\
\d+ False 0\} \{Ok 0 True \d+\}$}}
###############################################################################
runTest {test data-1.59 {percentile extension} -setup {
setupDb [set fileName data-1.59.db]
} -body {
set connection [getDbConnection]
set result [list]
$connection EnableExtensions true
$connection LoadExtension [getCoreBinaryFileName] sqlite3_percentile_init
lappend result [sql execute $db "CREATE TABLE t1(x);"]
lappend result [sql execute $db "INSERT INTO t1 VALUES(1),(2),(3),(4);"]
lappend result [sql execute $db "SELECT percentile(x,25) FROM t1"]
lappend result [sql execute -execute scalar $db \
"SELECT percentile(x,25) FROM t1"]
set result
} -cleanup {
freeDbConnection
unset -nocomplain result connection
cleanupDb $fileName
unset -nocomplain db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{0 4 0 1.75}}
###############################################################################
runTest {test data-1.60 {per-connection type mappings} -setup {
setupDb [set fileName data-1.60.db] "" "" "" UseConnectionTypes
} -body {
set connection [getDbConnection]
set result [list]
lappend result [sql execute $db \
"CREATE TABLE t1(x DATE, y MYDATE);"]
set dateTime [clock format [clock scan "2014-02-01 12:34:56Z"] \
-format yyyyMMddHHmmss -gmt true]
lappend result [sql execute $db [appendArgs \
"INSERT INTO t1 (x, y) VALUES('" $dateTime "', '" $dateTime "');"]]
lappend result [sql execute -verbatim -execute reader -format list \
-datetimeformat [getDateTimeFormat] $db "SELECT x, y FROM t1;"]
lappend result [$connection ClearTypeMappings]; # 0
lappend result [$connection AddTypeMapping MYDATE DateTime false]; # 0
lappend result [$connection AddTypeMapping MYDATE DateTime true]; # 1
lappend result [$connection AddTypeMapping MYDATE DateTime false]; # 1
lappend result [$connection AddTypeMapping MYDATE DateTime true]; # 2
lappend result [$connection ClearTypeMappings]; # 2
lappend result [$connection AddTypeMapping MYDATE DateTime true]; # 0
set typeMappings [$connection GetTypeMappings]
object foreach -alias pair $typeMappings {
set typeMapping [$pair Value]
lappend result [list [$pair Key] [enumerableToList $typeMapping]]
}
lappend result [sql execute -verbatim -execute reader -format list \
-datetimeformat [getDateTimeFormat] $db "SELECT x, y FROM t1;"]
set result
} -cleanup {
freeDbConnection
unset -nocomplain typeMapping typeMappings pair result connection
cleanupDb $fileName
unset -nocomplain dateTime db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{0 1 {{2014-02-01 12:34:56} 20140201123456} 0 0 1 1 2 2 0 {MYDATE {MYDATE\
DateTime True}} {{2014-02-01 12:34:56} {2014-02-01 12:34:56}}}}
###############################################################################
runTest {test data-1.61 {SELECT without TYPES} -setup {
setupDb [set fileName data-1.61.db]
} -body {
set values [list NULL 1 'one' 1.0 X'01']
sql execute $db "CREATE TABLE t1(x, y);"
foreach x $values {
foreach y $values {
sql execute $db [appendArgs \
"INSERT INTO t1 (x, y) VALUES(" $x ", " $y ");"]
}
}
sql execute -execute reader -format list -allownull true $db \
"SELECT rowId, x, y FROM t1 ORDER BY rowId;"
} -cleanup {
cleanupDb $fileName
unset -nocomplain y x values db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1 {} {} 2 {} 1 3 {} one 4 {} 1 5 {} 1 6 1 {} 7 1 1 8 1 one 9 1 1 10 1 1 11 one\
{} 12 one 1 13 one one 14 one 1 15 one 1 16 1 {} 17 1 1 18 1 one 19 1 1 20 1 1\
21 1 {} 22 1 1 23 1 one 24 1 1 25 1 1}}
###############################################################################
runTest {test data-1.62 {SELECT with TYPES} -setup {
setupDb [set fileName data-1.62.db]
} -body {
set values [list NULL 1 'one' 1.0 X'01']
sql execute $db "CREATE TABLE t1(x, y);"
foreach x $values {
foreach y $values {
sql execute $db [appendArgs \
"INSERT INTO t1 (x, y) VALUES(" $x ", " $y ");"]
}
}
sql execute -execute reader -format list -allownull true $db \
"TYPES INTEGER, INTEGER, REAL; SELECT rowId, x, y FROM t1 ORDER BY rowId;"
} -cleanup {
cleanupDb $fileName
unset -nocomplain y x values db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{1 {} {} 2 {} 1 3 {} 0 4 {} 1 5 {} 0 6 1 {} 7 1 1 8 1 0 9 1 1 10 1 0 11 0 {} 12\
0 1 13 0 0 14 0 1 15 0 0 16 1 {} 17 1 1 18 1 0 19 1 1 20 1 0 21 0 {} 22 0 1 23\
0 0 24 0 1 25 0 0}}
###############################################################################
runTest {test data-1.63 {SQLiteDataReader HasRows property} -setup {
setupDb [set fileName data-1.63.db]
} -body {
sql execute $db {
CREATE TABLE t1(x);
CREATE TABLE t2(x);
INSERT INTO t2 (x) VALUES(1);
CREATE TABLE t3(x);
INSERT INTO t3 (x) VALUES(1);
INSERT INTO t3 (x) VALUES(2);
}
set reader(1) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t1;"]
set reader(2) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t2;"]
set reader(3) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t3;"]
set noRow "*: No current row*"
list [$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error]
} -cleanup {
unset -nocomplain reader
cleanupDb $fileName
unset -nocomplain error noRow db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{False True True False True True False True True 1 True 0 False 0 False False\
True True False False True False False True 1 True 1 True 0 False False False\
True False False False False False False 1 True 1 True 1 True}}
###############################################################################
runTest {test data-1.64 {SQLiteDataReader sticky HasRows property} -setup {
setupDb [set fileName data-1.64.db] "" "" "" StickyHasRows
} -body {
sql execute $db {
CREATE TABLE t1(x);
CREATE TABLE t2(x);
INSERT INTO t2 (x) VALUES(1);
CREATE TABLE t3(x);
INSERT INTO t3 (x) VALUES(1);
INSERT INTO t3 (x) VALUES(2);
}
set reader(1) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t1;"]
set reader(2) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t2;"]
set reader(3) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t3;"]
set noRow "*: No current row*"
list [$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) HasRows] [$reader(2) HasRows] [$reader(3) HasRows] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error]
} -cleanup {
unset -nocomplain reader
cleanupDb $fileName
unset -nocomplain error noRow db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{False True True False True True False True True 1 True 0 False 0 False False\
True True False False True False True True 1 True 1 True 0 False False True\
True False False False False True True 1 True 1 True 1 True}}
###############################################################################
runTest {test data-1.65 {SQLiteDataReader StepCount property} -setup {
setupDb [set fileName data-1.65.db]
} -body {
sql execute $db {
CREATE TABLE t1(x);
CREATE TABLE t2(x);
INSERT INTO t2 (x) VALUES(1);
CREATE TABLE t3(x);
INSERT INTO t3 (x) VALUES(1);
INSERT INTO t3 (x) VALUES(2);
}
set reader(1) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t1;"]
set reader(2) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t2;"]
set reader(3) [sql execute -execute reader -format datareader -alias \
$db "SELECT * FROM t3;"]
set noRow "*: No current row*"
list [$reader(1) StepCount] [$reader(2) StepCount] [$reader(3) StepCount] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) StepCount] [$reader(2) StepCount] [$reader(3) StepCount] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error] \
[$reader(1) StepCount] [$reader(2) StepCount] [$reader(3) StepCount] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) StepCount] [$reader(2) StepCount] [$reader(3) StepCount] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error] \
[$reader(1) StepCount] [$reader(2) StepCount] [$reader(3) StepCount] \
[$reader(1) Read] [$reader(2) Read] [$reader(3) Read] \
[$reader(1) StepCount] [$reader(2) StepCount] [$reader(3) StepCount] \
[catch {$reader(1) Item x} error] [string match $noRow $error] \
[catch {$reader(2) Item x} error] [string match $noRow $error] \
[catch {$reader(3) Item x} error] [string match $noRow $error]
} -cleanup {
unset -nocomplain reader
cleanupDb $fileName
unset -nocomplain error noRow db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{0 1 1 False True True 0 1 1 1 True 0 False 0 False 0 1 1 False False True 0 1\
2 1 True 1 True 0 False 0 1 2 False False False 0 1 2 1 True 1 True 1 True}}
###############################################################################
runTest {test data-1.66 {SQLiteConnection.SetChunkSize default} -setup {
setupDb [set fileName data-1.66.db]
} -body {
sql execute $db {
CREATE TABLE t1(a, b);
}
file size [file join [getDatabaseDirectory] $fileName]
} -cleanup {
cleanupDb $fileName
unset -nocomplain db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{2048}}
###############################################################################
runTest {test data-1.67 {SQLiteConnection.SetChunkSize method} -setup {
setupDb [set fileName data-1.67.db]
} -body {
set connection [getDbConnection]
lappend result [$connection SetChunkSize [expr {32 * 1024}]]
sql execute $db {
CREATE TABLE t1(a, b);
}
lappend result [file size [file join [getDatabaseDirectory] $fileName]]
} -cleanup {
cleanupDb $fileName
freeDbConnection
unset -nocomplain result connection db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{Ok 32768}}
###############################################################################
reportSQLiteResources $test_channel
###############################################################################
runSQLiteTestFilesEpilogue
runSQLiteTestEpilogue
runTestEpilogue