AryaWu/sqlite

1# 2025-09-022#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# 12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix bestindexE17 18ifcapable !vtab {19  finish_test20  return21}22 23register_tcl_module db24 25proc pretty_constraint {lCol cons} {26  array set C $cons27 28  set ret ""29  if {$C(usable)} {30    set OP(eq) =31    set ret "[lindex $lCol $C(column)]$OP($C(op))?"32  }33 34  return $ret35}36 37proc vtab_command {zName lCol method args} {38  switch -- $method {39    xConnect {40      return "CREATE TABLE $zName ([join $lCol ,]) "41    }42 43    xBestIndex {44      set hdl [lindex $args 0]45      set conslist [$hdl constraints]46 47      set ret [list]48      foreach cons $conslist {49        set pretty [pretty_constraint $lCol $cons]50        if {$pretty != ""} { lappend ret $pretty }51      }52 53      lappend ::xBestIndex "$zName: [join $ret { AND }]"54 55      return "cost 1000 rows 1000 idxnum 555"56    }57  }58 59  return {}60}61 62proc do_bestindex_test {tn sql lCons} {63  set ::xBestIndex [list]64  do_execsql_test $tn.1 $sql65  uplevel [list do_test $tn.2 [list set ::xBestIndex] [list {*}$lCons]]66}67 68proc create_vtab {tname clist} {69  set cmd [list vtab_command $tname $clist]70  execsql "71    CREATE VIRTUAL TABLE $tname USING tcl('$cmd')72  "73}74 75do_test 1.0 {76  create_vtab x1 {a b c}77} {}78 79do_bestindex_test 1.1 {80  SELECT * FROM x1 WHERE a=?81} {{x1: a=?}}82 83do_bestindex_test 1.2 {84  SELECT * FROM x1 WHERE a=? AND b=?85} {{x1: a=? AND b=?}}86 87#--------------------------------------------------------------------------88reset_db89register_tcl_module db90 91do_test 2.0 {92  create_vtab Delivery       {id customer}93  create_vtab ReturnDelivery {id customer}94  create_vtab Customer       {oid name}95} {}96 97do_bestindex_test 2.1 {98  SELECT Delivery.ID, Customer.Name99  FROM Delivery LEFT JOIN100  Customer ON Delivery.Customer = Customer.OID101} {102  {Delivery: }103  {Customer: oid=?}104}105 106do_bestindex_test 2.2 {107  SELECT * FROM108  (109     SELECT Delivery.ID, Customer.Name110     FROM Delivery LEFT JOIN111     Customer ON Delivery.Customer = Customer.OID112 113     UNION114 115     SELECT ReturnDelivery.ID, Customer.Name116     FROM ReturnDelivery LEFT JOIN117     Customer ON ReturnDelivery.Customer = Customer.OID118  )119  WHERE ID = 1120} {121  {Delivery: id=?} 122  {Customer: oid=?} 123  {ReturnDelivery: id=?} 124  {Customer: oid=?}125}126 127#--------------------------------------------------------------------------128reset_db129register_tcl_module db eponymous_cmd130 131proc eponymous_cmd {method args} {132  switch -- $method {133    xConnect {134      db eval { SELECT * FROM sqlite_schema }135      return "CREATE TABLE t1 (a, b)"136    }137 138    xBestIndex {139      return "idxnum 555"140    }141 142    xFilter {143      return [list sql {SELECT 123, 'A', 'B'}]144    }145 146    xUpdate {147      return 123148    }149 150  }151 152  return {}153}154 155do_execsql_test 3.1.0 {156  PRAGMA table_info = tcl157} {158  0 a {} 0 {} 0 1 b {} 0 {} 0159}160do_execsql_test 3.1.1 {161  SELECT rowid, * FROM tcl162} {123 A B}163do_execsql_test 3.1.2 {164  INSERT INTO tcl VALUES('i', 'ii') RETURNING *;165} {i ii}166do_test 3.1.3 {167  db last_insert_rowid168} {123}169 170do_execsql_test 3.1.4 {171  CREATE TABLE x1(x);172}173 174db close175sqlite3 db  test.db176register_tcl_module db eponymous_cmd177sqlite3 db2 test.db178 179# Load the schema into connection [db]180do_execsql_test 3.2.1 { SELECT * FROM x1 }181 182# Modify the schema on disk183do_execsql_test -db db2 3.2.2 { CREATE TABLE x2(x) }184 185# Insert with RETURNING trigger on eponymous table.186do_execsql_test 3.2.3 {187  INSERT INTO tcl VALUES('i', 'ii') RETURNING *;188} {i ii}189 190finish_test191 192