gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/temptrigger.test (about) 1 # 2009 February 27 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 # 12 # $Id: temptrigger.test,v 1.3 2009/04/15 13:07:19 drh Exp $ 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix temptrigger 17 18 ifcapable {!trigger || !shared_cache} { finish_test ; return } 19 20 # Test cases: 21 # 22 # temptrigger-1.*: Shared cache problem. 23 # temptrigger-2.*: A similar shared cache problem. 24 # temptrigger-3.*: Attached database problem. 25 # 26 27 #------------------------------------------------------------------------- 28 # Test case temptrigger-1.* demonstrates a problem with temp triggers 29 # in shared-cache mode. If process 1 connections to a shared-cache and 30 # creates a temp trigger, the temp trigger is linked into the shared-cache 31 # schema. If process 2 reloads the shared-cache schema from disk, then 32 # it does not recreate the temp trigger belonging to process 1. From the 33 # point of view of process 1, the temp trigger just disappeared. 34 # 35 # temptrigger-1.1: In shared cache mode, create a table in the main 36 # database and add a temp trigger to it. 37 # 38 # temptrigger-1.2: Check that the temp trigger is correctly fired. Check 39 # that the temp trigger is not fired by statements 40 # executed by a second connection connected to the 41 # same shared cache. 42 # 43 # temptrigger-1.3: Using the second connection to the shared-cache, cause 44 # the shared-cache schema to be reloaded. 45 # 46 # temptrigger-1.4: Check that the temp trigger is still fired correctly. 47 # 48 # temptrigger-1.5: Check that the temp trigger can be dropped without error. 49 # 50 db close 51 set ::enable_shared_cache [sqlite3_enable_shared_cache] 52 sqlite3_enable_shared_cache 1 53 54 sqlite3 db test.db 55 sqlite3 db2 test.db 56 57 do_test temptrigger-1.1 { 58 execsql { 59 CREATE TABLE t1(a, b); 60 CREATE TEMP TABLE tt1(a, b); 61 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 62 INSERT INTO tt1 VALUES(new.a, new.b); 63 END; 64 } 65 } {} 66 67 do_test temptrigger-1.2.1 { 68 execsql { INSERT INTO t1 VALUES(1, 2) } 69 execsql { SELECT * FROM t1 } 70 } {1 2} 71 do_test temptrigger-1.2.2 { 72 execsql { SELECT * FROM tt1 } 73 } {1 2} 74 do_test temptrigger-1.2.3 { 75 execsql { INSERT INTO t1 VALUES(3, 4) } db2 76 execsql { SELECT * FROM t1 } 77 } {1 2 3 4} 78 do_test temptrigger-1.2.4 { 79 execsql { SELECT * FROM tt1 } 80 } {1 2} 81 82 # Cause the shared-cache schema to be reloaded. 83 # 84 do_test temptrigger-1.3 { 85 execsql { BEGIN; CREATE TABLE t3(a, b); ROLLBACK; } db2 86 } {} 87 88 do_test temptrigger-1.4 { 89 execsql { INSERT INTO t1 VALUES(5, 6) } 90 execsql { SELECT * FROM tt1 } 91 } {1 2 5 6} 92 93 do_test temptrigger-1.5 { 94 # Before the bug was fixed, the following 'DROP TRIGGER' hit an 95 # assert if executed. 96 #execsql { DROP TRIGGER tr1 } 97 } {} 98 99 catch {db close} 100 catch {db2 close} 101 102 #------------------------------------------------------------------------- 103 # Tests temptrigger-2.* are similar to temptrigger-1.*, except that 104 # temptrigger-2.3 simply opens and closes a connection to the shared-cache. 105 # It does not do anything special to cause the schema to be reloaded. 106 # 107 do_test temptrigger-2.1 { 108 sqlite3 db test.db 109 execsql { 110 DELETE FROM t1; 111 CREATE TEMP TABLE tt1(a, b); 112 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 113 INSERT INTO tt1 VALUES(new.a, new.b); 114 END; 115 } 116 } {} 117 do_test temptrigger-2.2 { 118 execsql { 119 INSERT INTO t1 VALUES(10, 20); 120 SELECT * FROM tt1; 121 } 122 } {10 20} 123 do_test temptrigger-2.3 { 124 sqlite3 db2 test.db 125 db2 close 126 } {} 127 do_test temptrigger-2.4 { 128 execsql { 129 INSERT INTO t1 VALUES(30, 40); 130 SELECT * FROM tt1; 131 } 132 } {10 20 30 40} 133 do_test temptrigger-2.5 { 134 #execsql { DROP TRIGGER tr1 } 135 } {} 136 137 catch {db close} 138 catch {db2 close} 139 sqlite3_enable_shared_cache $::enable_shared_cache 140 141 #------------------------------------------------------------------------- 142 # Test case temptrigger-3.* demonstrates a problem with temp triggers 143 # on tables located in attached databases. At one point when SQLite reloaded 144 # the schema of an attached database (because some other connection had 145 # changed the schema cookie) it was not re-creating temp triggers attached 146 # to tables located within the attached database. 147 # 148 # temptrigger-3.1: Attach database 'test2.db' to connection [db]. Add a 149 # temp trigger to a table in 'test2.db'. 150 # 151 # temptrigger-3.2: Check that the temp trigger is correctly fired. 152 # 153 # temptrigger-3.3: Update the schema of 'test2.db' using an external 154 # connection. This forces [db] to reload the 'test2.db' 155 # schema. Check that the temp trigger is still fired 156 # correctly. 157 # 158 # temptrigger-3.4: Check that the temp trigger can be dropped without error. 159 # 160 do_test temptrigger-3.1 { 161 catch { forcedelete test2.db test2.db-journal } 162 catch { forcedelete test.db test.db-journal } 163 sqlite3 db test.db 164 sqlite3 db2 test2.db 165 execsql { CREATE TABLE t2(a, b) } db2 166 execsql { 167 ATTACH 'test2.db' AS aux; 168 CREATE TEMP TABLE tt2(a, b); 169 CREATE TEMP TRIGGER tr2 AFTER INSERT ON aux.t2 BEGIN 170 INSERT INTO tt2 VALUES(new.a, new.b); 171 END; 172 } 173 } {} 174 175 do_test temptrigger-3.2.1 { 176 execsql { 177 INSERT INTO aux.t2 VALUES(1, 2); 178 SELECT * FROM aux.t2; 179 } 180 } {1 2} 181 do_test temptrigger-3.2.2 { 182 execsql { SELECT * FROM tt2 } 183 } {1 2} 184 185 do_test temptrigger-3.3.1 { 186 execsql { CREATE TABLE t3(a, b) } db2 187 execsql { 188 INSERT INTO aux.t2 VALUES(3, 4); 189 SELECT * FROM aux.t2; 190 } 191 } {1 2 3 4} 192 do_test temptrigger-3.3.2 { 193 execsql { SELECT * FROM tt2 } 194 } {1 2 3 4} 195 196 do_test temptrigger-3.4 { 197 # Before the bug was fixed, the following 'DROP TRIGGER' hit an 198 # assert if executed. 199 #execsql { DROP TRIGGER tr2 } 200 } {} 201 202 catch { db close } 203 catch { db2 close } 204 205 206 #------------------------------------------------------------------------- 207 # Test that creating a temp table after a temp trigger on the same name 208 # has been created is an error. 209 # 210 reset_db 211 do_execsql_test 4.0 { 212 CREATE TABLE t1(x); 213 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN 214 SELECT 1,2,3; 215 END; 216 } 217 218 do_execsql_test 4.1 { 219 CREATE TEMP TABLE t1(x); 220 } 221 222 #------------------------------------------------------------------------- 223 # Test that no harm is done if the table a temp trigger is attached to is 224 # deleted by an external connection. 225 # 226 reset_db 227 do_execsql_test 5.0 { 228 CREATE TABLE t1(x); 229 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN SELECT 1,2,3; END; 230 } 231 232 do_test 5.1 { 233 sqlite3 db2 test.db 234 execsql { DROP TABLE t1 } db2 235 } {} 236 237 do_execsql_test 5.2 { 238 SELECT * FROM sqlite_master; 239 SELECT * FROM temp.sqlite_master; 240 } { 241 trigger tr1 t1 0 242 {CREATE TRIGGER tr1 BEFORE INSERT ON t1 BEGIN SELECT 1,2,3; END} 243 } 244 db2 close 245 246 #------------------------------------------------------------------------- 247 # Check that if a second connection creates a table in an attached database 248 # with the same name as a table in the main database that has a temp 249 # trigger attached to it nothing goes awry. 250 # 251 reset_db 252 forcedelete test.db2 253 254 do_execsql_test 6.0 { 255 CREATE TABLE t1(x); 256 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN 257 SELECT raise(ABORT, 'error'); 258 END; 259 ATTACH 'test.db2' AS aux; 260 } 261 262 do_test 6.1 { 263 sqlite3 db2 test.db2 264 execsql { CREATE TABLE t1(a, b, c); } db2 265 } {} 266 267 do_execsql_test 6.2 { 268 SELECT type,name,tbl_name,sql FROM aux.sqlite_master; 269 INSERT INTO aux.t1 VALUES(1,2,3); 270 } { 271 table t1 t1 {CREATE TABLE t1(a, b, c)} 272 } 273 274 do_catchsql_test 6.3 { 275 INSERT INTO main.t1 VALUES(1); 276 } {1 error} 277 db2 close 278 279 finish_test