System.Data.SQLite

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 \
      [getNativeLibraryFileName null] 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

} -body {
  unset -nocomplain pattern result

  set connection [getDbConnection]
  set result(1) [list]

  $connection EnableExtensions true

  $connection LoadExtension \
      [getNativeLibraryFileName null] 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)

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 \
      [getNativeLibraryFileName null] 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;"]

runTest {test data-1.71 {fts5 extension} -setup {
  setupDb [set fileName data-1.71.db]
} -body {
  set connection [getDbConnection]
  set result [list]

  $connection EnableExtensions true

  $connection LoadExtension \
      [getNativeLibraryFileName null] sqlite3_fts5_init

  lappend result [sql execute -execute scalar $db "SELECT fts5_source_id();"]

  lappend result [sql execute -execute scalar $db \
      "CREATE VIRTUAL TABLE t1 USING fts5(x, prefix=\"1\");"]

  foreach x [list cat dog horse house] {

    INSERT INTO big (json) VALUES('{"a" : [8, 9.1, true, false, null, "y"]}');
  }

  set connection [getDbConnection]
  set result [list]

  $connection EnableExtensions true

  $connection LoadExtension \
      [getNativeLibraryFileName null] sqlite3_json_init

  lappend result [sql execute -execute scalar $db \
      {SELECT json('{ "this" : "is", "a": [ "test" ] }');}]

  lappend result [sql execute -execute reader -format list $db \
      "SELECT rowid, json_type(json) FROM big;"]

runTest {test tkt-17045010df-1.1 {EnableExtensions & LoadExtension} -setup {
  setupDb [set fileName tkt-17045010df-1.1.db]
} -body {
  set connection [getDbConnection]

  lappend result [catchAndReturn {$connection EnableExtensions false}]

  lappend result [catchAndReturn {$connection LoadExtension \
      [getNativeLibraryFileName null] interop_test_extension_init}]

  lappend result [catchAndReturn {sql execute -execute scalar $db \
      "SELECT interopTest('test1');"}]
  lappend result [catchAndReturn {sql execute -execute scalar $db \
      "SELECT interopTest('test1', '1234');"}]

  lappend result [catchAndReturn {$connection EnableExtensions true}]

  lappend result [catchAndReturn {$connection LoadExtension \
      [getNativeLibraryFileName null] interop_test_extension_init}]

  lappend result [catchAndReturn {sql execute -execute scalar $db \
      "SELECT interopTest('test2');"}]
  lappend result [catchAndReturn {sql execute -execute scalar $db \
      "SELECT interopTest('test2', '5678');"}]

  set result

###############################################################################

package require System.Data.SQLite.Test
runSQLiteTestPrologue

###############################################################################

proc normalizeVtabResult { value } {
  set result $value

  if {[string first \r $result] != -1} then {
    set result [string map [list \r\n \n] $result]
  }

  return [string map [list \n " -- "] $result]
}

###############################################################################

runTest {test vtab-1.1 {basic virtual table support} -setup {
  set fileName vtab-1.1.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]
  set dataSource [file join [getDatabaseDirectory] $fileName]

  set sql { \

    }
  }] 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 ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult \
{^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}]}

###############################################################################

runTest {test vtab-1.2.1 {IEnumerable virtual table} -setup {
  set fileName vtab-1.2.1.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList one two three 4 5.0
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{one two three 4 5\.0 Error\
\{SQL logic error or missing database -- virtual table "t\d+" is\
read-only\}\}$}]}

###############################################################################

runTest {test vtab-1.2.2 {IEnumerable virtual table} -setup {
  set fileName vtab-1.2.2.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList one two three 4 5.0
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{one two three 4 5\.0 Error\
\{SQL logic error or missing database -- virtual table "t\d+" is\
read-only\}\}$}]}

###############################################################################

runTest {test vtab-1.3.1 {IEnumerable<T> virtual table} -setup {
  set fileName vtab-1.3.1.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList 1 2 3 4 5
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{1 2 3 4 5 Error \{SQL\
logic error or missing database -- virtual table "t\d+" is read-only\}\}$}]}


###############################################################################

runTest {test vtab-1.3.2 {IEnumerable<T> virtual table} -setup {
  set fileName vtab-1.3.2.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList 1 2 3 4 5
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{1 2 3 4 5 Error \{SQL\
logic error or missing database -- virtual table "t\d+" is read-only\}\}$}]}


###############################################################################

runTest {test vtab-1.4 {virtual table function support} -setup {
  set fileName vtab-1.4.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\{\} b25l \{SQL logic\
error or missing database -- unable to use function Base64 in the requested\
context\} \{SQL logic error or missing database -- no such function:\

Base65\}\}$}]}

###############################################################################

runTest {test vtab-1.5 {virtual table function support} -setup {
  set fileName vtab-1.5.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\{\} b25l \{SQL logic\
error or missing database -- (?:unable to use function Base64 in the requested\

context|need exactly one argument, got 3)\} \{SQL logic error or missing\
database -- no such function: Base65\}\}$}]}

###############################################################################

runTest {test vtab-1.6 {virtual table rename support} -setup {
  set fileName vtab-1.6.db
} -body {
  set id [object invoke Interpreter.GetActive NextId]

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\{\} t\d+ \{\} x\d+\}$}]}

###############################################################################

runTest {test vtab-1.7 {virtual table xBestIndex marshalling (1)} -setup {
  set fileName vtab-1.7.db
} -body {

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList one two three 4 5.0
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{0 5 two three one 5\.0\
4 \{ConstraintUsages is OK\}\}$}]}

###############################################################################

runTest {test vtab-1.8 {virtual table xBestIndex marshalling (2)} -setup {
  set fileName vtab-1.8.db

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList one two three 4 5.0
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{0 5 one three\
\{ConstraintUsages is OK\}\}$}]}

###############################################################################

runTest {test vtab-1.9 {IEnumerable virtual table w/large byte array} -setup {
  set fileName vtab-1.9.db

    }
  }] 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} GetSum
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -time true -constraints {eagle command.object monoBug28 command.sql\
compile.DATA SQLite System.Data.SQLite\
defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE compileCSharp} -match \
regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 (?:-)?\d+$}]}

###############################################################################

runTest {test vtab-1.10 {virtual table xRowId uniqueness} -setup {
  set fileName vtab-1.10.db
} -body {

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList one one two one two two
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{0 4294967296 4294967296\
8589934592 8589934592 12884901888 12884901888 17179869184 17179869184\
21474836480 21474836480 25769803776 25769803776\}$}]}

###############################################################################

runTest {test vtab-1.11 {IEnumerable virtual table w/object identity} -setup {

    }
  }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} GetList one two three 4 5.0
      } result] : [set result ""]}] [normalizeVtabResult $result]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE\
compileCSharp} -match regexp -result [normalizeVtabResult {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{-?\d+ one -?\d+ two -?\d+\
three -?\d+ 4 -?\d+ 5\.0\}$}]}

###############################################################################

runTest {test vtab-1.12 {SQLiteIndex managed-to-native-to-managed} -setup {
  set nConstraint 3; set nOrderBy 3

  unset -nocomplain constraintUsage
  unset -nocomplain orderBy nOrderBy iOrderBy
  unset -nocomplain constraint nConstraint iConstraint
  unset -nocomplain pIndex index
} -constraints {eagle command.object SQLite System.Data.SQLite\
defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE} -result {}}

###############################################################################

rename normalizeVtabResult ""

###############################################################################

runSQLiteTestEpilogue
runTestEpilogue

        # NOTE: Otherwise, return both the generic POSIX file name and the
        #       Mac OS X file name, since we do not currently have an easy
        #       way to detect which of those platforms we are running on.
        #
        return [list libsqlite3.so libsqlite3.dylib]
      }
    }

    proc getNativeLibraryFileName { {default ""} } {
      set directory [file dirname [getCoreBinaryFileName]]

      foreach fileNameOnly [getNativeLibraryFileNamesOnly] {
        set fileName [file join $directory $fileNameOnly]

        if {[file exists $fileName]} then {
          return $fileName
        }
      }

      return $default
    }

    proc isBuildAvailable { native directory {varName ""} } {
      #
      # NOTE: Build the fully qualified file name for the primary assembly
      #       containing the System.Data.SQLite managed components.  It
      #       should be noted that this assembly file may also contain the
      #       native components, if a native build is in use.