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 17 ifcapable {!trigger || !shared_cache} { finish_test ; return } 18 19 # Test cases: 20 # 21 # temptrigger-1.*: Shared cache problem. 22 # temptrigger-2.*: A similar shared cache problem. 23 # temptrigger-3.*: Attached database problem. 24 # 25 26 #------------------------------------------------------------------------- 27 # Test case temptrigger-1.* demonstrates a problem with temp triggers 28 # in shared-cache mode. If process 1 connections to a shared-cache and 29 # creates a temp trigger, the temp trigger is linked into the shared-cache 30 # schema. If process 2 reloads the shared-cache schema from disk, then 31 # it does not recreate the temp trigger belonging to process 1. From the 32 # point of view of process 1, the temp trigger just disappeared. 33 # 34 # temptrigger-1.1: In shared cache mode, create a table in the main 35 # database and add a temp trigger to it. 36 # 37 # temptrigger-1.2: Check that the temp trigger is correctly fired. Check 38 # that the temp trigger is not fired by statements 39 # executed by a second connection connected to the 40 # same shared cache. 41 # 42 # temptrigger-1.3: Using the second connection to the shared-cache, cause 43 # the shared-cache schema to be reloaded. 44 # 45 # temptrigger-1.4: Check that the temp trigger is still fired correctly. 46 # 47 # temptrigger-1.5: Check that the temp trigger can be dropped without error. 48 # 49 db close 50 set ::enable_shared_cache [sqlite3_enable_shared_cache] 51 sqlite3_enable_shared_cache 1 52 53 sqlite3 db test.db 54 sqlite3 db2 test.db 55 56 do_test temptrigger-1.1 { 57 execsql { 58 CREATE TABLE t1(a, b); 59 CREATE TEMP TABLE tt1(a, b); 60 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 61 INSERT INTO tt1 VALUES(new.a, new.b); 62 END; 63 } 64 } {} 65 66 do_test temptrigger-1.2.1 { 67 execsql { INSERT INTO t1 VALUES(1, 2) } 68 execsql { SELECT * FROM t1 } 69 } {1 2} 70 do_test temptrigger-1.2.2 { 71 execsql { SELECT * FROM tt1 } 72 } {1 2} 73 do_test temptrigger-1.2.3 { 74 execsql { INSERT INTO t1 VALUES(3, 4) } db2 75 execsql { SELECT * FROM t1 } 76 } {1 2 3 4} 77 do_test temptrigger-1.2.4 { 78 execsql { SELECT * FROM tt1 } 79 } {1 2} 80 81 # Cause the shared-cache schema to be reloaded. 82 # 83 do_test temptrigger-1.3 { 84 execsql { BEGIN; CREATE TABLE t3(a, b); ROLLBACK; } db2 85 } {} 86 87 do_test temptrigger-1.4 { 88 execsql { INSERT INTO t1 VALUES(5, 6) } 89 execsql { SELECT * FROM tt1 } 90 } {1 2 5 6} 91 92 do_test temptrigger-1.5 { 93 # Before the bug was fixed, the following 'DROP TRIGGER' hit an 94 # assert if executed. 95 #execsql { DROP TRIGGER tr1 } 96 } {} 97 98 catch {db close} 99 catch {db2 close} 100 101 #------------------------------------------------------------------------- 102 # Tests temptrigger-2.* are similar to temptrigger-1.*, except that 103 # temptrigger-2.3 simply opens and closes a connection to the shared-cache. 104 # It does not do anything special to cause the schema to be reloaded. 105 # 106 do_test temptrigger-2.1 { 107 sqlite3 db test.db 108 execsql { 109 DELETE FROM t1; 110 CREATE TEMP TABLE tt1(a, b); 111 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN 112 INSERT INTO tt1 VALUES(new.a, new.b); 113 END; 114 } 115 } {} 116 do_test temptrigger-2.2 { 117 execsql { 118 INSERT INTO t1 VALUES(10, 20); 119 SELECT * FROM tt1; 120 } 121 } {10 20} 122 do_test temptrigger-2.3 { 123 sqlite3 db2 test.db 124 db2 close 125 } {} 126 do_test temptrigger-2.4 { 127 execsql { 128 INSERT INTO t1 VALUES(30, 40); 129 SELECT * FROM tt1; 130 } 131 } {10 20 30 40} 132 do_test temptrigger-2.5 { 133 #execsql { DROP TRIGGER tr1 } 134 } {} 135 136 catch {db close} 137 catch {db2 close} 138 sqlite3_enable_shared_cache $::enable_shared_cache 139 140 #------------------------------------------------------------------------- 141 # Test case temptrigger-3.* demonstrates a problem with temp triggers 142 # on tables located in attached databases. At one point when SQLite reloaded 143 # the schema of an attached database (because some other connection had 144 # changed the schema cookie) it was not re-creating temp triggers attached 145 # to tables located within the attached database. 146 # 147 # temptrigger-3.1: Attach database 'test2.db' to connection [db]. Add a 148 # temp trigger to a table in 'test2.db'. 149 # 150 # temptrigger-3.2: Check that the temp trigger is correctly fired. 151 # 152 # temptrigger-3.3: Update the schema of 'test2.db' using an external 153 # connection. This forces [db] to reload the 'test2.db' 154 # schema. Check that the temp trigger is still fired 155 # correctly. 156 # 157 # temptrigger-3.4: Check that the temp trigger can be dropped without error. 158 # 159 do_test temptrigger-3.1 { 160 catch { file delete -force test2.db test2.db-journal } 161 catch { file delete -force test.db test.db-journal } 162 sqlite3 db test.db 163 sqlite3 db2 test2.db 164 execsql { CREATE TABLE t2(a, b) } db2 165 execsql { 166 ATTACH 'test2.db' AS aux; 167 CREATE TEMP TABLE tt2(a, b); 168 CREATE TEMP TRIGGER tr2 AFTER INSERT ON aux.t2 BEGIN 169 INSERT INTO tt2 VALUES(new.a, new.b); 170 END; 171 } 172 } {} 173 174 do_test temptrigger-3.2.1 { 175 execsql { 176 INSERT INTO aux.t2 VALUES(1, 2); 177 SELECT * FROM aux.t2; 178 } 179 } {1 2} 180 do_test temptrigger-3.2.2 { 181 execsql { SELECT * FROM tt2 } 182 } {1 2} 183 184 do_test temptrigger-3.3.1 { 185 execsql { CREATE TABLE t3(a, b) } db2 186 execsql { 187 INSERT INTO aux.t2 VALUES(3, 4); 188 SELECT * FROM aux.t2; 189 } 190 } {1 2 3 4} 191 do_test temptrigger-3.3.2 { 192 execsql { SELECT * FROM tt2 } 193 } {1 2 3 4} 194 195 do_test temptrigger-3.4 { 196 # Before the bug was fixed, the following 'DROP TRIGGER' hit an 197 # assert if executed. 198 #execsql { DROP TRIGGER tr2 } 199 } {} 200 201 catch { db close } 202 catch { db2 close } 203 204 finish_test 205
