1 # 2006 July 25

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # 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 # This file implements regression tests for SQLite library. The focus

    12 # of this test is reading and writing to the database from within a

    13 # virtual table xSync() callback.

    14 #

    15 # $Id: vtab7.test,v 1.4 2007/12/04 16:54:53 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 ifcapable !vtab {

    21   finish_test

    22   return

    23 }

    24 

    25 # Register the echo module. Code inside the echo module appends elements

    26 # to the global tcl list variable ::echo_module whenever SQLite invokes

    27 # certain module callbacks. This includes the xSync(), xCommit() and 

    28 # xRollback() callbacks. For each of these callback, two elements are

    29 # appended to ::echo_module, as follows:

    30 #

    31 #     Module method        Elements appended to ::echo_module

    32 #     -------------------------------------------------------

    33 #     xSync()              xSync echo($tablename)

    34 #     xCommit()            xCommit echo($tablename)

    35 #     xRollback()          xRollback echo($tablename)

    36 #     -------------------------------------------------------

    37 #

    38 # In each case, $tablename is replaced by the name of the real table (not

    39 # the echo table). By setting up a tcl trace on the ::echo_module variable,

    40 # code in this file arranges for a Tcl script to be executed from within

    41 # the echo module xSync() callback.

    42 #

    43 register_echo_module [sqlite3_connection_pointer db]

    44 trace add variable ::echo_module write echo_module_trace

    45 

    46 # This Tcl proc is invoked whenever the ::echo_module variable is written.

    47 #

    48 proc echo_module_trace {args} {

    49   # Filter out writes to ::echo_module that are not xSync, xCommit or 

    50   # xRollback callbacks.

    51   if {[llength $::echo_module] < 2} return

    52   set x [lindex $::echo_module end-1]

    53   if {$x ne "xSync" && $x ne "xCommit" && $x ne "xRollback"} return

    54 

    55   regexp {^echo.(.*).$} [lindex $::echo_module end] dummy tablename

    56   # puts "Ladies and gentlemen, an $x on $tablename!"

    57 

    58   if {[info exists ::callbacks($x,$tablename)]} {

    59     eval $::callbacks($x,$tablename)

    60   }

    61 }

    62 

    63 # The following tests, vtab7-1.*, test that the trace callback on 

    64 # ::echo_module is providing the expected tcl callbacks.

    65 do_test vtab7-1.1 {

    66   execsql {

    67     CREATE TABLE abc(a, b, c);

    68     CREATE VIRTUAL TABLE abc2 USING echo(abc);

    69   }

    70 } {}

    71 

    72 do_test vtab7-1.2 {

    73   set ::callbacks(xSync,abc) {incr ::counter}

    74   set ::counter 0

    75   execsql {

    76     INSERT INTO abc2 VALUES(1, 2, 3);

    77   }

    78   set ::counter

    79 } {1}

    80 

    81 # Write to an existing database table from within an xSync callback.

    82 do_test vtab7-2.1 {

    83   set ::callbacks(xSync,abc) {

    84     execsql {INSERT INTO log VALUES('xSync');}

    85   }

    86   execsql {

    87     CREATE TABLE log(msg);

    88     INSERT INTO abc2 VALUES(4, 5, 6);

    89     SELECT * FROM log;

    90   }

    91 } {xSync}

    92 do_test vtab7-2.3 {

    93   execsql {

    94     INSERT INTO abc2 VALUES(4, 5, 6);

    95     SELECT * FROM log;

    96   }

    97 } {xSync xSync}

    98 do_test vtab7-2.4 {

    99   execsql {

   100     INSERT INTO abc2 VALUES(4, 5, 6);

   101     SELECT * FROM log;

   102   }

   103 } {xSync xSync xSync}

   104 

   105 # Create a database table from within xSync callback.

   106 do_test vtab7-2.5 {

   107   set ::callbacks(xSync,abc) {

   108     execsql { CREATE TABLE newtab(d, e, f); }

   109   }

   110   execsql {

   111     INSERT INTO abc2 VALUES(1, 2, 3);

   112     SELECT name FROM sqlite_master ORDER BY name;

   113   }

   114 } {abc abc2 log newtab}

   115 

   116 # Drop a database table from within xSync callback.

   117 # This is not allowed.  Tables cannot be dropped while

   118 # any other statement is active.

   119 #

   120 do_test vtab7-2.6 {

   121   set ::callbacks(xSync,abc) {

   122     set ::rc [catchsql { DROP TABLE newtab }]

   123   }

   124   execsql {

   125     INSERT INTO abc2 VALUES(1, 2, 3);

   126     SELECT name FROM sqlite_master ORDER BY name;

   127   }

   128 } {abc abc2 log newtab}

   129 do_test vtab7-2.6.1 {

   130   set ::rc

   131 } {1 {database table is locked}}

   132 execsql {DROP TABLE newtab}

   133 

   134 # Write to an attached database from xSync().

   135 ifcapable attach {

   136   do_test vtab7-3.1 {

   137     file delete -force test2.db

   138     file delete -force test2.db-journal

   139     execsql {

   140       ATTACH 'test2.db' AS db2;

   141       CREATE TABLE db2.stuff(description, shape, color);

   142     }

   143     set ::callbacks(xSync,abc) {

   144       execsql { INSERT INTO db2.stuff VALUES('abc', 'square', 'green'); }

   145     }

   146     execsql {

   147       INSERT INTO abc2 VALUES(1, 2, 3);

   148       SELECT * from stuff;

   149     }

   150   } {abc square green}

   151 }

   152 

   153 # UPDATE: The next test passes, but leaks memory. So leave it out.

   154 #

   155 # The following tests test that writing to the database from within

   156 # the xCommit callback causes a misuse error.

   157 # do_test vtab7-4.1 {

   158 #   unset -nocomplain ::callbacks(xSync,abc)

   159 #   set ::callbacks(xCommit,abc) {

   160 #     execsql { INSERT INTO log VALUES('hello') }

   161 #   }

   162 #   catchsql {

   163 #     INSERT INTO abc2 VALUES(1, 2, 3);

   164 #   }

   165 # } {1 {library routine called out of sequence}}

   166 

   167 # These tests, vtab7-4.*, test that an SQLITE_LOCKED error is returned

   168 # if an attempt to write to a virtual module table or create a new 

   169 # virtual table from within an xSync() callback.

   170 do_test vtab7-4.1 {

   171   execsql {

   172     CREATE TABLE def(d, e, f);

   173     CREATE VIRTUAL TABLE def2 USING echo(def);

   174   }

   175   set ::callbacks(xSync,abc) {

   176     set ::error [catchsql { INSERT INTO def2 VALUES(1, 2, 3) }]

   177   }

   178   execsql {

   179     INSERT INTO abc2 VALUES(1, 2, 3);

   180   }

   181   set ::error

   182 } {1 {database table is locked}}

   183 do_test vtab7-4.2 {

   184   set ::callbacks(xSync,abc) {

   185     set ::error [catchsql { CREATE VIRTUAL TABLE def3 USING echo(def) }]

   186   }

   187   execsql {

   188     INSERT INTO abc2 VALUES(1, 2, 3);

   189   }

   190   set ::error

   191 } {1 {database table is locked}}

   192 

   193 do_test vtab7-4.3 {

   194   set ::callbacks(xSync,abc) {

   195     set ::error [catchsql { DROP TABLE def2 }]

   196   }

   197   execsql {

   198     INSERT INTO abc2 VALUES(1, 2, 3);

   199     SELECT name FROM sqlite_master ORDER BY name;

   200   }

   201   set ::error

   202 } {1 {database table is locked}}

   203 

   204 trace remove variable ::echo_module write echo_module_trace

   205 unset -nocomplain ::callbacks

   206 

   207 finish_test