gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/vtab_shared.test (about) 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 set testprefix vtab_shared 19 20 ifcapable !vtab||!shared_cache { 21 finish_test 22 return 23 } 24 25 db close 26 sqlite3_enable_shared_cache 1 27 sqlite3 db test.db 28 sqlite3 db2 test.db 29 30 do_test vtab_shared-1.1 { 31 register_echo_module [sqlite3_connection_pointer db] 32 execsql { 33 CREATE TABLE t0(a, b, c); 34 INSERT INTO t0 VALUES(1, 2, 3); 35 CREATE VIRTUAL TABLE t1 USING echo(t0); 36 } 37 } {} 38 39 do_test vtab_shared-1.2 { 40 execsql { SELECT * FROM t1 } db 41 } {1 2 3} 42 43 # Fails because the 'echo' module has not been registered with connection db2 44 do_test vtab_shared-1.3 { 45 catchsql { SELECT * FROM t1 } db2 46 } {1 {no such module: echo}} 47 48 do_test vtab_shared-1.4 { 49 execsql { SELECT * FROM t0 } db2 50 } {1 2 3} 51 52 do_test vtab_shared-1.5 { 53 register_echo_module [sqlite3_connection_pointer db2] 54 execsql { SELECT * FROM t1 } db 55 } {1 2 3} 56 57 # Works after the module is registered with db2 58 do_test vtab_shared-1.6 { 59 execsql { SELECT * FROM t1 } db2 60 } {1 2 3} 61 62 # Set a write-lock on table t0 using connection [db]. Then try to read from 63 # virtual table t1 using [db2]. That this returns an SQLITE_LOCKED error 64 # shows that the correct sqlite3_vtab is being used. 65 # 66 do_test vtab_shared-1.8.1 { 67 execsql { 68 BEGIN; 69 INSERT INTO t1 VALUES(4, 5, 6); 70 SELECT * FROM t1; 71 } 72 } {1 2 3 4 5 6} 73 do_test vtab_shared-1.8.2 { 74 catchsql { SELECT * FROM t1 } db2 75 } {1 {database table is locked}} 76 do_test vtab_shared-1.8.3 { 77 catchsql { SELECT * FROM t0 } db2 78 } {1 {database table is locked: t0}} 79 do_test vtab_shared-1.8.4 { 80 execsql { SELECT * FROM t0 } db 81 } {1 2 3 4 5 6} 82 do_test vtab_shared-1.8.5 { 83 execsql { COMMIT } db 84 execsql { SELECT * FROM t1 } db2 85 } {1 2 3 4 5 6} 86 87 # While a SELECT is active on virtual table t1 via connection [db], close 88 # [db2]. This causes the schema to be reset internally. Verify that this 89 # does not cause a problem. 90 # 91 foreach {iTest dbSelect dbClose} { 92 1 db db2 93 2 db db2 94 3 db2 db 95 } { 96 do_test vtab_shared-1.9.$iTest { 97 set res [list] 98 $dbSelect eval { SELECT * FROM t1 } { 99 if {$a == 1} {$dbClose close} 100 lappend res $a $b $c 101 } 102 sqlite3 $dbClose test.db 103 register_echo_module [sqlite3_connection_pointer $dbClose] 104 set res 105 } {1 2 3 4 5 6} 106 } 107 108 # Ensure that it is not possible for one connection to DROP a virtual 109 # table while a second connection is reading from the database. 110 # 111 do_test vtab_shared-1.10 { 112 db eval { SELECT * FROM t1 } { 113 set error [catchsql { DROP TABLE t1 } db2] 114 break 115 } 116 set error 117 } {1 {database table is locked: sqlite_master}} 118 119 do_test vtab_shared-1.11 { 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 ifcapable compound { 128 do_test vtab_shared-1.12.1 { 129 db close 130 execsql { 131 SELECT * FROM t1 UNION ALL 132 SELECT * FROM t2 UNION ALL 133 SELECT * FROM t3 134 } db2 135 } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6} 136 do_test vtab_shared-1.12.2 { 137 sqlite3 db test.db 138 register_echo_module [sqlite3_connection_pointer db] 139 execsql { 140 SELECT * FROM t1 UNION ALL 141 SELECT * FROM t2 UNION ALL 142 SELECT * FROM t3 143 } db 144 } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6} 145 } 146 147 # Try a rename or two. 148 # 149 ifcapable altertable { 150 do_test vtab_shared-1.13.1 { 151 execsql { ALTER TABLE t1 RENAME TO t4 } 152 execsql { SELECT * FROM t4 } db 153 } {1 2 3 4 5 6} 154 do_test vtab_shared-1.13.2 { 155 execsql { SELECT * FROM t4 } db2 156 } {1 2 3 4 5 6} 157 do_test vtab_shared-1.13.3 { 158 execsql { ALTER TABLE t2 RENAME TO t5 } 159 execsql { SELECT * FROM t4 } db2 160 } {1 2 3 4 5 6} 161 } 162 163 # Try an UPDATE/INSERT/DELETE on a shared vtab as the first statement after a 164 # schema is loaded. 165 do_test vtab_shared_1.14.1 { 166 db2 close 167 sqlite3 db2 test.db 168 register_echo_module [sqlite3_connection_pointer db2] 169 execsql { SELECT * FROM t3 } 170 } {1 2 3 4 5 6} 171 do_test vtab_shared_1.14.2 { 172 execsql { 173 UPDATE t3 SET c = 'six' WHERE c = 6; 174 SELECT * FROM t3; 175 } db2 176 } {1 2 3 4 5 six} 177 do_test vtab_shared_1.14.3 { 178 db2 close 179 sqlite3 db2 test.db 180 register_echo_module [sqlite3_connection_pointer db2] 181 execsql { SELECT * FROM t3 } 182 } {1 2 3 4 5 six} 183 do_test vtab_shared_1.14.4 { 184 execsql { 185 DELETE FROM t3 WHERE c = 'six'; 186 SELECT * FROM t3; 187 } db2 188 } {1 2 3} 189 do_test vtab_shared_1.14.5 { 190 db2 close 191 sqlite3 db2 test.db 192 register_echo_module [sqlite3_connection_pointer db2] 193 execsql { SELECT * FROM t3 } 194 } {1 2 3} 195 do_test vtab_shared_1.14.6 { 196 execsql { 197 INSERT INTO t3 VALUES(4, 5, 6); 198 SELECT * FROM t3; 199 } db2 200 } {1 2 3 4 5 6} 201 202 do_test vtab_shared_1.15.1 { 203 db2 close 204 sqlite3 db2 test.db 205 register_echo_module [sqlite3_connection_pointer db2] 206 execsql { 207 UPDATE t3 SET c = 'six' WHERE c = 6; 208 SELECT * FROM t3; 209 } db2 210 } {1 2 3 4 5 six} 211 do_test vtab_shared_1.15.2 { 212 db2 close 213 sqlite3 db2 test.db 214 register_echo_module [sqlite3_connection_pointer db2] 215 execsql { 216 DELETE FROM t3 WHERE c = 'six'; 217 SELECT * FROM t3; 218 } db2 219 } {1 2 3} 220 do_test vtab_shared_1.15.3 { 221 db2 close 222 sqlite3 db2 test.db 223 register_echo_module [sqlite3_connection_pointer db2] 224 execsql { 225 INSERT INTO t3 VALUES(4, 5, 6); 226 SELECT * FROM t3; 227 } 228 } {1 2 3 4 5 6} 229 230 db close 231 db2 close 232 233 #--------------------------------------------------------------- 234 # Test calling sqlite3_close() with vtabs on the disconnect list. 235 # 236 ifcapable rtree { 237 reset_db 238 do_test 2.1.1 { 239 sqlite3 db test.db 240 sqlite3 db2 test.db 241 242 # Create a virtual table using [db]. 243 execsql { 244 CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2); 245 INSERT INTO rt VALUES(1, 2 ,3); 246 SELECT * FROM rt; 247 } 248 249 # Drop the virtual table using [db2]. The sqlite3_vtab object belonging 250 # to [db] is moved to the sqlite3.pDisconnect list. 251 execsql { DROP TABLE rt } db2 252 253 # Immediately close [db]. At one point this would fail due to the 254 # unfinalized statements held by the un-xDisconnect()ed sqlite3_vtab. 255 db close 256 } {} 257 db2 close 258 } 259 260 ifcapable fts3 { 261 # Same test as above, except using fts3 instead of rtree. 262 reset_db 263 do_test 2.2.1 { 264 sqlite3 db test.db 265 sqlite3 db2 test.db 266 execsql { 267 CREATE VIRTUAL TABLE ft USING fts3; 268 INSERT INTO ft VALUES('hello world'); 269 SELECT * FROM ft; 270 } 271 execsql { DROP TABLE ft } db2 272 db close 273 } {} 274 db2 close 275 } 276 277 sqlite3_enable_shared_cache 0 278 finish_test