sl@0: # 2008 April 10
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is is verifying that the xUpdate, xSync, xCommit
sl@0: # and xRollback methods are only invoked after an xBegin or xCreate.
sl@0: # Ticket #3083.
sl@0: #
sl@0: # $Id: vtabC.test,v 1.1 2008/04/28 20:27:54 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable !vtab {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: 
sl@0: # N will be the number of virtual tables we have defined.
sl@0: #
sl@0: unset -nocomplain N
sl@0: for {set N 1} {$N<=20} {incr N} {
sl@0:   db close
sl@0:   file delete -force test.db test.db-journal
sl@0:   sqlite3 db test.db
sl@0:   register_echo_module [sqlite3_connection_pointer db]
sl@0: 
sl@0:   # Create $N tables and $N virtual tables to echo them.
sl@0:   #
sl@0:   unset -nocomplain tablist
sl@0:   set tablist {}
sl@0:   do_test vtabC-1.$N.1 {
sl@0:     for {set i 1} {$i<=$::N} {incr i} {
sl@0:       execsql "CREATE TABLE t${i}(x)"
sl@0:       execsql "CREATE VIRTUAL TABLE vt$i USING echo(t$i)"
sl@0:       lappend ::tablist t$i vt$i
sl@0:     }
sl@0:     execsql {SELECT count(*) FROM sqlite_master}
sl@0:   } [expr {$N*2}]
sl@0:   do_test vtabC-1.$N.2 {
sl@0:     execsql {SELECT name FROM sqlite_master}
sl@0:   } $tablist
sl@0: 
sl@0:   # Create a table m and add triggers to make changes on all
sl@0:   # of the virtual tables when m is changed.
sl@0:   #
sl@0:   do_test vtabC-1.$N.3 {
sl@0:     execsql {CREATE TABLE m(a)}
sl@0:     set sql "CREATE TRIGGER rins AFTER INSERT ON m BEGIN\n"
sl@0:     for {set i 1} {$i<=$::N} {incr i} {
sl@0:       append sql "  INSERT INTO vt$i VALUES(NEW.a+$i);\n"
sl@0:     }
sl@0:     append sql "END;"
sl@0:     execsql $sql
sl@0:     execsql {SELECT count(*) FROM sqlite_master}
sl@0:   } [expr {$N*2+2}]
sl@0:   do_test vtabC-1.$N.4 {
sl@0:     execsql {
sl@0:       INSERT INTO m VALUES(1000);
sl@0:       SELECT * FROM m;
sl@0:     }
sl@0:   } {1000}
sl@0:   for {set j 1} {$j<=$::N} {incr j} {
sl@0:     do_test vtabC-1.$N.5.$j {
sl@0:       execsql "SELECT * FROM t$::j"
sl@0:     } [expr {$j+1000}]
sl@0:     do_test vtabC-1.$N.6.$j {
sl@0:       execsql "SELECT * FROM vt$::j"
sl@0:     } [expr {$j+1000}]
sl@0:   }
sl@0:   do_test vtabC-1.$N.7 {
sl@0:     set sql "CREATE TRIGGER rins2 BEFORE INSERT ON m BEGIN\n"
sl@0:     for {set i 1} {$i<=$::N} {incr i} {
sl@0:       append sql "  INSERT INTO vt$i VALUES(NEW.a+$i*100);\n"
sl@0:     }
sl@0:     for {set i 1} {$i<=$::N} {incr i} {
sl@0:       append sql "  INSERT INTO vt$i VALUES(NEW.a+$i*10000);\n"
sl@0:     }
sl@0:     append sql "END;"
sl@0:     execsql $sql
sl@0:     execsql {SELECT count(*) FROM sqlite_master}
sl@0:   } [expr {$N*2+3}]
sl@0:   do_test vtabC-1.$N.8 {
sl@0:     execsql {
sl@0:       INSERT INTO m VALUES(9000000);
sl@0:       SELECT * FROM m;
sl@0:     }
sl@0:   } {1000 9000000}
sl@0:   unset -nocomplain res
sl@0:   for {set j 1} {$j<=$::N} {incr j} {
sl@0:     set res [expr {$j+1000}]
sl@0:     lappend res [expr {$j*100+9000000}]
sl@0:     lappend res [expr {$j*10000+9000000}]
sl@0:     lappend res [expr {$j+9000000}]
sl@0:     do_test vtabC-1.$N.9.$j {
sl@0:       execsql "SELECT * FROM t$::j"
sl@0:     } $res
sl@0:     do_test vtabC-1.$N.10.$j {
sl@0:       execsql "SELECT * FROM vt$::j"
sl@0:     } $res
sl@0:   }
sl@0: }
sl@0: unset -nocomplain res N i j
sl@0: 
sl@0: 
sl@0: finish_test