1 # 2007 April 16 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 tests interactions between the virtual table and 12 # shared-schema functionality. 13 # 14 # $Id: vtab_shared.test,v 1.3 2009/07/24 17:58:53 danielk1977 Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 ifcapable !vtab||!shared_cache { 20 finish_test 21 return 22 } 23 24 db close 25 sqlite3_enable_shared_cache 1 26 sqlite3 db test.db 27 sqlite3 db2 test.db 28 29 do_test vtab_shared-1.1 { 30 register_echo_module [sqlite3_connection_pointer db] 31 execsql { 32 CREATE TABLE t0(a, b, c); 33 INSERT INTO t0 VALUES(1, 2, 3); 34 CREATE VIRTUAL TABLE t1 USING echo(t0); 35 } 36 } {} 37 38 do_test vtab_shared-1.2 { 39 execsql { SELECT * FROM t1 } db 40 } {1 2 3} 41 42 # Fails because the 'echo' module has not been registered with connection db2 43 do_test vtab_shared-1.3 { 44 catchsql { SELECT * FROM t1 } db2 45 } {1 {no such module: echo}} 46 47 do_test vtab_shared-1.4 { 48 execsql { SELECT * FROM t0 } db2 49 } {1 2 3} 50 51 do_test vtab_shared-1.5 { 52 register_echo_module [sqlite3_connection_pointer db2] 53 execsql { SELECT * FROM t1 } db 54 } {1 2 3} 55 56 # Works after the module is registered with db2 57 do_test vtab_shared-1.6 { 58 execsql { SELECT * FROM t1 } db2 59 } {1 2 3} 60 61 # Set a write-lock on table t0 using connection [db]. Then try to read from 62 # virtual table t1 using [db2]. That this returns an SQLITE_LOCKED error 63 # shows that the correct sqlite3_vtab is being used. 64 # 65 do_test vtab_shared-1.8.1 { 66 execsql { 67 BEGIN; 68 INSERT INTO t1 VALUES(4, 5, 6); 69 SELECT * FROM t1; 70 } 71 } {1 2 3 4 5 6} 72 do_test vtab_shared-1.8.2 { 73 catchsql { SELECT * FROM t1 } db2 74 } {1 {database table is locked}} 75 do_test vtab_shared-1.8.3 { 76 catchsql { SELECT * FROM t0 } db2 77 } {1 {database table is locked: t0}} 78 do_test vtab_shared-1.8.4 { 79 execsql { SELECT * FROM t0 } db 80 } {1 2 3 4 5 6} 81 do_test vtab_shared-1.8.5 { 82 execsql { COMMIT } db 83 execsql { SELECT * FROM t1 } db2 84 } {1 2 3 4 5 6} 85 86 # While a SELECT is active on virtual table t1 via connection [db], close 87 # [db2]. This causes the schema to be reset internally. Verify that this 88 # does not cause a problem. 89 # 90 foreach {iTest dbSelect dbClose} { 91 1 db db2 92 2 db db2 93 3 db2 db 94 } { 95 do_test vtab_shared-1.9.$iTest { 96 set res [list] 97 $dbSelect eval { SELECT * FROM t1 } { 98 if {$a == 1} {$dbClose close} 99 lappend res $a $b $c 100 } 101 sqlite3 $dbClose test.db 102 register_echo_module [sqlite3_connection_pointer $dbClose] 103 set res 104 } {1 2 3 4 5 6} 105 } 106 107 # Ensure that it is not possible for one connection to DROP a virtual 108 # table while a second connection is reading from the database. 109 # 110 do_test vtab_shared-1.10 { 111 db eval { SELECT * FROM t1 } { 112 set error [catchsql { DROP TABLE t1 } db2] 113 break 114 } 115 set error 116 } {1 {database table is locked: sqlite_master}} 117 118 do_test vtab_shared-1.11 { 119 breakpoint 120 execsql { 121 CREATE VIRTUAL TABLE t2 USING echo(t0); 122 CREATE VIRTUAL TABLE t3 USING echo(t0); 123 } 124 execsql { SELECT * FROM t3 } db2 125 } {1 2 3 4 5 6} 126 127 do_test vtab_shared-1.12.1 { 128 db close 129 execsql { 130 SELECT * FROM t1 UNION ALL 131 SELECT * FROM t2 UNION ALL 132 SELECT * FROM t3 133 } db2 134 } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6} 135 do_test vtab_shared-1.12.2 { 136 sqlite3 db test.db 137 register_echo_module [sqlite3_connection_pointer db] 138 execsql { 139 SELECT * FROM t1 UNION ALL 140 SELECT * FROM t2 UNION ALL 141 SELECT * FROM t3 142 } db 143 } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6} 144 145 # Try a rename or two. 146 # 147 ifcapable altertable { 148 do_test vtab_shared-1.13.1 { 149 execsql { ALTER TABLE t1 RENAME TO t4 } 150 execsql { SELECT * FROM t4 } db 151 } {1 2 3 4 5 6} 152 do_test vtab_shared-1.13.2 { 153 execsql { SELECT * FROM t4 } db2 154 } {1 2 3 4 5 6} 155 do_test vtab_shared-1.13.3 { 156 execsql { ALTER TABLE t2 RENAME TO t5 } 157 execsql { SELECT * FROM t4 } db2 158 } {1 2 3 4 5 6} 159 } 160 161 # Try an UPDATE/INSERT/DELETE on a shared vtab as the first statement after a 162 # schema is loaded. 163 do_test vtab_shared_1.14.1 { 164 db2 close 165 sqlite3 db2 test.db 166 register_echo_module [sqlite3_connection_pointer db2] 167 execsql { SELECT * FROM t3 } 168 } {1 2 3 4 5 6} 169 do_test vtab_shared_1.14.2 { 170 execsql { 171 UPDATE t3 SET c = 'six' WHERE c = 6; 172 SELECT * FROM t3; 173 } db2 174 } {1 2 3 4 5 six} 175 do_test vtab_shared_1.14.3 { 176 db2 close 177 sqlite3 db2 test.db 178 register_echo_module [sqlite3_connection_pointer db2] 179 execsql { SELECT * FROM t3 } 180 } {1 2 3 4 5 six} 181 do_test vtab_shared_1.14.4 { 182 execsql { 183 DELETE FROM t3 WHERE c = 'six'; 184 SELECT * FROM t3; 185 } db2 186 } {1 2 3} 187 do_test vtab_shared_1.14.5 { 188 db2 close 189 sqlite3 db2 test.db 190 register_echo_module [sqlite3_connection_pointer db2] 191 execsql { SELECT * FROM t3 } 192 } {1 2 3} 193 do_test vtab_shared_1.14.6 { 194 execsql { 195 INSERT INTO t3 VALUES(4, 5, 6); 196 SELECT * FROM t3; 197 } db2 198 } {1 2 3 4 5 6} 199 200 do_test vtab_shared_1.15.1 { 201 db2 close 202 sqlite3 db2 test.db 203 register_echo_module [sqlite3_connection_pointer db2] 204 execsql { 205 UPDATE t3 SET c = 'six' WHERE c = 6; 206 SELECT * FROM t3; 207 } db2 208 } {1 2 3 4 5 six} 209 do_test vtab_shared_1.15.2 { 210 db2 close 211 sqlite3 db2 test.db 212 register_echo_module [sqlite3_connection_pointer db2] 213 execsql { 214 DELETE FROM t3 WHERE c = 'six'; 215 SELECT * FROM t3; 216 } db2 217 } {1 2 3} 218 do_test vtab_shared_1.15.3 { 219 db2 close 220 sqlite3 db2 test.db 221 register_echo_module [sqlite3_connection_pointer db2] 222 execsql { 223 INSERT INTO t3 VALUES(4, 5, 6); 224 SELECT * FROM t3; 225 } 226 } {1 2 3 4 5 6} 227 228 db close 229 db2 close 230 sqlite3_enable_shared_cache 0 231 finish_test 232
