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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable !vtab||!shared_cache { 20 finish_test 21 return 22} 23 24db close 25sqlite3_enable_shared_cache 1 26sqlite3 db test.db 27sqlite3 db2 test.db 28 29do_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 38do_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 43do_test vtab_shared-1.3 { 44 catchsql { SELECT * FROM t1 } db2 45} {1 {no such module: echo}} 46 47do_test vtab_shared-1.4 { 48 execsql { SELECT * FROM t0 } db2 49} {1 2 3} 50 51do_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 57do_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# 65do_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} 72do_test vtab_shared-1.8.2 { 73 catchsql { SELECT * FROM t1 } db2 74} {1 {database table is locked}} 75do_test vtab_shared-1.8.3 { 76 catchsql { SELECT * FROM t0 } db2 77} {1 {database table is locked: t0}} 78do_test vtab_shared-1.8.4 { 79 execsql { SELECT * FROM t0 } db 80} {1 2 3 4 5 6} 81do_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# 90foreach {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# 110do_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 118do_test vtab_shared-1.11 { 119 execsql { 120 CREATE VIRTUAL TABLE t2 USING echo(t0); 121 CREATE VIRTUAL TABLE t3 USING echo(t0); 122 } 123 execsql { SELECT * FROM t3 } db2 124} {1 2 3 4 5 6} 125 126ifcapable compound { 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 146# Try a rename or two. 147# 148ifcapable altertable { 149 do_test vtab_shared-1.13.1 { 150 execsql { ALTER TABLE t1 RENAME TO t4 } 151 execsql { SELECT * FROM t4 } db 152 } {1 2 3 4 5 6} 153 do_test vtab_shared-1.13.2 { 154 execsql { SELECT * FROM t4 } db2 155 } {1 2 3 4 5 6} 156 do_test vtab_shared-1.13.3 { 157 execsql { ALTER TABLE t2 RENAME TO t5 } 158 execsql { SELECT * FROM t4 } db2 159 } {1 2 3 4 5 6} 160} 161 162# Try an UPDATE/INSERT/DELETE on a shared vtab as the first statement after a 163# schema is loaded. 164do_test vtab_shared_1.14.1 { 165 db2 close 166 sqlite3 db2 test.db 167 register_echo_module [sqlite3_connection_pointer db2] 168 execsql { SELECT * FROM t3 } 169} {1 2 3 4 5 6} 170do_test vtab_shared_1.14.2 { 171 execsql { 172 UPDATE t3 SET c = 'six' WHERE c = 6; 173 SELECT * FROM t3; 174 } db2 175} {1 2 3 4 5 six} 176do_test vtab_shared_1.14.3 { 177 db2 close 178 sqlite3 db2 test.db 179 register_echo_module [sqlite3_connection_pointer db2] 180 execsql { SELECT * FROM t3 } 181} {1 2 3 4 5 six} 182do_test vtab_shared_1.14.4 { 183 execsql { 184 DELETE FROM t3 WHERE c = 'six'; 185 SELECT * FROM t3; 186 } db2 187} {1 2 3} 188do_test vtab_shared_1.14.5 { 189 db2 close 190 sqlite3 db2 test.db 191 register_echo_module [sqlite3_connection_pointer db2] 192 execsql { SELECT * FROM t3 } 193} {1 2 3} 194do_test vtab_shared_1.14.6 { 195 execsql { 196 INSERT INTO t3 VALUES(4, 5, 6); 197 SELECT * FROM t3; 198 } db2 199} {1 2 3 4 5 6} 200 201do_test vtab_shared_1.15.1 { 202 db2 close 203 sqlite3 db2 test.db 204 register_echo_module [sqlite3_connection_pointer db2] 205 execsql { 206 UPDATE t3 SET c = 'six' WHERE c = 6; 207 SELECT * FROM t3; 208 } db2 209} {1 2 3 4 5 six} 210do_test vtab_shared_1.15.2 { 211 db2 close 212 sqlite3 db2 test.db 213 register_echo_module [sqlite3_connection_pointer db2] 214 execsql { 215 DELETE FROM t3 WHERE c = 'six'; 216 SELECT * FROM t3; 217 } db2 218} {1 2 3} 219do_test vtab_shared_1.15.3 { 220 db2 close 221 sqlite3 db2 test.db 222 register_echo_module [sqlite3_connection_pointer db2] 223 execsql { 224 INSERT INTO t3 VALUES(4, 5, 6); 225 SELECT * FROM t3; 226 } 227} {1 2 3 4 5 6} 228 229db close 230db2 close 231sqlite3_enable_shared_cache 0 232finish_test 233