sl@0: # 2006 July 25
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 focus
sl@0: # of this test is reading and writing to the database from within a
sl@0: # virtual table xSync() callback.
sl@0: #
sl@0: # $Id: vtab7.test,v 1.4 2007/12/04 16:54:53 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: # Register the echo module. Code inside the echo module appends elements
sl@0: # to the global tcl list variable ::echo_module whenever SQLite invokes
sl@0: # certain module callbacks. This includes the xSync(), xCommit() and 
sl@0: # xRollback() callbacks. For each of these callback, two elements are
sl@0: # appended to ::echo_module, as follows:
sl@0: #
sl@0: #     Module method        Elements appended to ::echo_module
sl@0: #     -------------------------------------------------------
sl@0: #     xSync()              xSync echo($tablename)
sl@0: #     xCommit()            xCommit echo($tablename)
sl@0: #     xRollback()          xRollback echo($tablename)
sl@0: #     -------------------------------------------------------
sl@0: #
sl@0: # In each case, $tablename is replaced by the name of the real table (not
sl@0: # the echo table). By setting up a tcl trace on the ::echo_module variable,
sl@0: # code in this file arranges for a Tcl script to be executed from within
sl@0: # the echo module xSync() callback.
sl@0: #
sl@0: register_echo_module [sqlite3_connection_pointer db]
sl@0: trace add variable ::echo_module write echo_module_trace
sl@0: 
sl@0: # This Tcl proc is invoked whenever the ::echo_module variable is written.
sl@0: #
sl@0: proc echo_module_trace {args} {
sl@0:   # Filter out writes to ::echo_module that are not xSync, xCommit or 
sl@0:   # xRollback callbacks.
sl@0:   if {[llength $::echo_module] < 2} return
sl@0:   set x [lindex $::echo_module end-1]
sl@0:   if {$x ne "xSync" && $x ne "xCommit" && $x ne "xRollback"} return
sl@0: 
sl@0:   regexp {^echo.(.*).$} [lindex $::echo_module end] dummy tablename
sl@0:   # puts "Ladies and gentlemen, an $x on $tablename!"
sl@0: 
sl@0:   if {[info exists ::callbacks($x,$tablename)]} {
sl@0:     eval $::callbacks($x,$tablename)
sl@0:   }
sl@0: }
sl@0: 
sl@0: # The following tests, vtab7-1.*, test that the trace callback on 
sl@0: # ::echo_module is providing the expected tcl callbacks.
sl@0: do_test vtab7-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE abc(a, b, c);
sl@0:     CREATE VIRTUAL TABLE abc2 USING echo(abc);
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: do_test vtab7-1.2 {
sl@0:   set ::callbacks(xSync,abc) {incr ::counter}
sl@0:   set ::counter 0
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:   }
sl@0:   set ::counter
sl@0: } {1}
sl@0: 
sl@0: # Write to an existing database table from within an xSync callback.
sl@0: do_test vtab7-2.1 {
sl@0:   set ::callbacks(xSync,abc) {
sl@0:     execsql {INSERT INTO log VALUES('xSync');}
sl@0:   }
sl@0:   execsql {
sl@0:     CREATE TABLE log(msg);
sl@0:     INSERT INTO abc2 VALUES(4, 5, 6);
sl@0:     SELECT * FROM log;
sl@0:   }
sl@0: } {xSync}
sl@0: do_test vtab7-2.3 {
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(4, 5, 6);
sl@0:     SELECT * FROM log;
sl@0:   }
sl@0: } {xSync xSync}
sl@0: do_test vtab7-2.4 {
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(4, 5, 6);
sl@0:     SELECT * FROM log;
sl@0:   }
sl@0: } {xSync xSync xSync}
sl@0: 
sl@0: # Create a database table from within xSync callback.
sl@0: do_test vtab7-2.5 {
sl@0:   set ::callbacks(xSync,abc) {
sl@0:     execsql { CREATE TABLE newtab(d, e, f); }
sl@0:   }
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:     SELECT name FROM sqlite_master ORDER BY name;
sl@0:   }
sl@0: } {abc abc2 log newtab}
sl@0: 
sl@0: # Drop a database table from within xSync callback.
sl@0: # This is not allowed.  Tables cannot be dropped while
sl@0: # any other statement is active.
sl@0: #
sl@0: do_test vtab7-2.6 {
sl@0:   set ::callbacks(xSync,abc) {
sl@0:     set ::rc [catchsql { DROP TABLE newtab }]
sl@0:   }
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:     SELECT name FROM sqlite_master ORDER BY name;
sl@0:   }
sl@0: } {abc abc2 log newtab}
sl@0: do_test vtab7-2.6.1 {
sl@0:   set ::rc
sl@0: } {1 {database table is locked}}
sl@0: execsql {DROP TABLE newtab}
sl@0: 
sl@0: # Write to an attached database from xSync().
sl@0: ifcapable attach {
sl@0:   do_test vtab7-3.1 {
sl@0:     file delete -force test2.db
sl@0:     file delete -force test2.db-journal
sl@0:     execsql {
sl@0:       ATTACH 'test2.db' AS db2;
sl@0:       CREATE TABLE db2.stuff(description, shape, color);
sl@0:     }
sl@0:     set ::callbacks(xSync,abc) {
sl@0:       execsql { INSERT INTO db2.stuff VALUES('abc', 'square', 'green'); }
sl@0:     }
sl@0:     execsql {
sl@0:       INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:       SELECT * from stuff;
sl@0:     }
sl@0:   } {abc square green}
sl@0: }
sl@0: 
sl@0: # UPDATE: The next test passes, but leaks memory. So leave it out.
sl@0: #
sl@0: # The following tests test that writing to the database from within
sl@0: # the xCommit callback causes a misuse error.
sl@0: # do_test vtab7-4.1 {
sl@0: #   unset -nocomplain ::callbacks(xSync,abc)
sl@0: #   set ::callbacks(xCommit,abc) {
sl@0: #     execsql { INSERT INTO log VALUES('hello') }
sl@0: #   }
sl@0: #   catchsql {
sl@0: #     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0: #   }
sl@0: # } {1 {library routine called out of sequence}}
sl@0: 
sl@0: # These tests, vtab7-4.*, test that an SQLITE_LOCKED error is returned
sl@0: # if an attempt to write to a virtual module table or create a new 
sl@0: # virtual table from within an xSync() callback.
sl@0: do_test vtab7-4.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE def(d, e, f);
sl@0:     CREATE VIRTUAL TABLE def2 USING echo(def);
sl@0:   }
sl@0:   set ::callbacks(xSync,abc) {
sl@0:     set ::error [catchsql { INSERT INTO def2 VALUES(1, 2, 3) }]
sl@0:   }
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:   }
sl@0:   set ::error
sl@0: } {1 {database table is locked}}
sl@0: do_test vtab7-4.2 {
sl@0:   set ::callbacks(xSync,abc) {
sl@0:     set ::error [catchsql { CREATE VIRTUAL TABLE def3 USING echo(def) }]
sl@0:   }
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:   }
sl@0:   set ::error
sl@0: } {1 {database table is locked}}
sl@0: 
sl@0: do_test vtab7-4.3 {
sl@0:   set ::callbacks(xSync,abc) {
sl@0:     set ::error [catchsql { DROP TABLE def2 }]
sl@0:   }
sl@0:   execsql {
sl@0:     INSERT INTO abc2 VALUES(1, 2, 3);
sl@0:     SELECT name FROM sqlite_master ORDER BY name;
sl@0:   }
sl@0:   set ::error
sl@0: } {1 {database table is locked}}
sl@0: 
sl@0: trace remove variable ::echo_module write echo_module_trace
sl@0: unset -nocomplain ::callbacks
sl@0: 
sl@0: finish_test