AryaWu/sqlite
0
1# 2009 February 272#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix temptrigger17 18ifcapable {!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 triggers29# in shared-cache mode. If process 1 connections to a shared-cache and30# creates a temp trigger, the temp trigger is linked into the shared-cache31# schema. If process 2 reloads the shared-cache schema from disk, then32# it does not recreate the temp trigger belonging to process 1. From the33# 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. Check39# that the temp trigger is not fired by statements40# 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, cause44# 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#50db close51set ::enable_shared_cache [sqlite3_enable_shared_cache]52sqlite3_enable_shared_cache 153 54sqlite3 db test.db55sqlite3 db2 test.db56 57do_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 BEGIN62 INSERT INTO tt1 VALUES(new.a, new.b);63 END;64 }65} {}66 67do_test temptrigger-1.2.1 {68 execsql { INSERT INTO t1 VALUES(1, 2) }69 execsql { SELECT * FROM t1 }70} {1 2}71do_test temptrigger-1.2.2 {72 execsql { SELECT * FROM tt1 }73} {1 2}74do_test temptrigger-1.2.3 {75 execsql { INSERT INTO t1 VALUES(3, 4) } db276 execsql { SELECT * FROM t1 }77} {1 2 3 4}78do_test temptrigger-1.2.4 {79 execsql { SELECT * FROM tt1 }80} {1 2}81 82# Cause the shared-cache schema to be reloaded.83#84do_test temptrigger-1.3 {85 execsql { BEGIN; CREATE TABLE t3(a, b); ROLLBACK; } db286} {}87 88do_test temptrigger-1.4 {89 execsql { INSERT INTO t1 VALUES(5, 6) }90 execsql { SELECT * FROM tt1 }91} {1 2 5 6}92 93do_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 99catch {db close}100catch {db2 close}101 102#-------------------------------------------------------------------------103# Tests temptrigger-2.* are similar to temptrigger-1.*, except that104# 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# 107do_test temptrigger-2.1 {108 sqlite3 db test.db109 execsql {110 DELETE FROM t1;111 CREATE TEMP TABLE tt1(a, b);112 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN113 INSERT INTO tt1 VALUES(new.a, new.b);114 END;115 }116} {}117do_test temptrigger-2.2 {118 execsql {119 INSERT INTO t1 VALUES(10, 20);120 SELECT * FROM tt1;121 }122} {10 20}123do_test temptrigger-2.3 {124 sqlite3 db2 test.db125 db2 close126} {}127do_test temptrigger-2.4 {128 execsql {129 INSERT INTO t1 VALUES(30, 40);130 SELECT * FROM tt1;131 }132} {10 20 30 40}133do_test temptrigger-2.5 {134 #execsql { DROP TRIGGER tr1 }135} {}136 137catch {db close}138catch {db2 close}139sqlite3_enable_shared_cache $::enable_shared_cache140 141#-------------------------------------------------------------------------142# Test case temptrigger-3.* demonstrates a problem with temp triggers143# 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 a149# 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 external154# connection. This forces [db] to reload the 'test2.db'155# schema. Check that the temp trigger is still fired156# correctly.157#158# temptrigger-3.4: Check that the temp trigger can be dropped without error.159# 160do_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) } db2166 execsql {167 ATTACH 'test2.db' AS aux;168 CREATE TEMP TABLE tt2(a, b);169 CREATE TEMP TRIGGER tr2 AFTER INSERT ON aux.t2 BEGIN170 INSERT INTO tt2 VALUES(new.a, new.b);171 END;172 }173} {}174 175do_test temptrigger-3.2.1 {176 execsql { 177 INSERT INTO aux.t2 VALUES(1, 2);178 SELECT * FROM aux.t2;179 }180} {1 2}181do_test temptrigger-3.2.2 {182 execsql { SELECT * FROM tt2 }183} {1 2}184 185do_test temptrigger-3.3.1 {186 execsql { CREATE TABLE t3(a, b) } db2187 execsql { 188 INSERT INTO aux.t2 VALUES(3, 4);189 SELECT * FROM aux.t2;190 }191} {1 2 3 4}192do_test temptrigger-3.3.2 {193 execsql { SELECT * FROM tt2 }194} {1 2 3 4}195 196do_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 202catch { db close }203catch { db2 close }204 205 206#-------------------------------------------------------------------------207# Test that creating a temp table after a temp trigger on the same name208# has been created is an error.209#210reset_db211do_execsql_test 4.0 {212 CREATE TABLE t1(x);213 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN214 SELECT 1,2,3;215 END;216}217 218do_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 is224# deleted by an external connection.225#226reset_db227do_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 232do_test 5.1 {233 sqlite3 db2 test.db234 execsql { DROP TABLE t1 } db2235} {}236 237do_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}244db2 close245 246#-------------------------------------------------------------------------247# Check that if a second connection creates a table in an attached database248# with the same name as a table in the main database that has a temp249# trigger attached to it nothing goes awry.250#251reset_db252forcedelete test.db2253 254do_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 262do_test 6.1 {263 sqlite3 db2 test.db2264 execsql { CREATE TABLE t1(a, b, c); } db2265} {}266 267do_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 274do_catchsql_test 6.3 {275 INSERT INTO main.t1 VALUES(1);276} {1 error}277db2 close278 279#-------------------------------------------------------------------------280reset_db281forcedelete test.db2282 283do_execsql_test 7.0 {284 CREATE TABLE m1(a, b);285 ATTACH 'test.db2' AS aux;286 CREATE TABLE aux.a1(c, d);287}288 289do_execsql_test 7.1 {290 CREATE TEMP TRIGGER tr1 AFTER INSERT ON m1 BEGIN291 INSERT INTO a1 VALUES(new.a, new.b);292 END;293 294 INSERT INTO m1 VALUES(5, 6);295 SELECT * FROM aux.a1;296} {5 6}297 298do_execsql_test 7.2 {299 CREATE TABLE a1(e, f);300 INSERT INTO m1 VALUES(7, 8);301}302 303do_execsql_test 7.3.1 { SELECT * FROM main.a1 } {7 8}304do_execsql_test 7.3.2 { SELECT * FROM aux.a1 } {5 6}305 306do_execsql_test 7.4 {307 DROP TRIGGER tr1;308 CREATE TEMP TRIGGER tr1 AFTER INSERT ON m1 BEGIN309 INSERT INTO a1 SELECT d, c FROM aux.a1;310 END;311 312 DELETE FROM aux.a1;313 DELETE FROM main.a1;314 INSERT INTO aux.a1 VALUES('hello', 'world');315}316 317do_execsql_test 7.5 {318 INSERT INTO m1 VALUES(9, 10);319 SELECT * FROM main.a1;320} {world hello}321 322do_catchsql_test 7.6 {323 DROP TRIGGER tr1;324 CREATE TRIGGER tr1 AFTER INSERT ON m1 BEGIN325 INSERT INTO a1 SELECT d, c FROM aux.a1;326 END;327} {1 {trigger tr1 cannot reference objects in database aux}}328 329#-------------------------------------------------------------------------330# Check that temp triggers may INSERT/UPDATE/DELETE to fully qualified331# table names.332reset_db333forcedelete {*}[glob -nocomplain *mj*]334forcedelete test.db2335do_execsql_test 8.0 {336 ATTACH 'test.db2' AS aux;337 CREATE TABLE t1(a, b);338 CREATE TABLE t2(c, d);339 CREATE TABLE aux.t1(e, f);340 CREATE TABLE aux.t2(g, h);341}342 343do_catchsql_test 8.1.1 {344 CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN345 INSERT INTO aux.t1 VALUES(new.c, new.d);346 END;347} {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}348 349do_execsql_test 8.1.2 {350 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t2 BEGIN351 INSERT INTO aux.t1 VALUES(new.c, new.d);352 END;353 354 INSERT INTO main.t2 VALUES('x', 'y');355 SELECT * FROM aux.t1;356} {x y}357 358do_execsql_test 8.1.3 { SELECT * FROM t1 } {}359 360do_catchsql_test 8.2.1 {361 CREATE TRIGGER aux.tr2 AFTER UPDATE ON aux.t1 BEGIN362 UPDATE main.t2 SET c=new.e, d=new.f;363 END;364} {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}365 366do_execsql_test 8.2.2 {367 CREATE TEMP TRIGGER tr2 AFTER UPDATE ON aux.t1 BEGIN368 UPDATE main.t2 SET c=new.e, d=new.f;369 END;370 371 UPDATE aux.t1 SET e=1, f=2;372 SELECT * FROM t2;373} {1 2}374 375do_execsql_test 8.2.3 { SELECT * FROM aux.t2 } {}376 377do_catchsql_test 8.3.1 {378 CREATE TRIGGER tr3 AFTER DELETE ON t2 BEGIN379 DELETE FROM aux.t1;380 END;381} {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}382 383do_execsql_test 8.3.2 {384 INSERT INTO main.t1 VALUES('a', 'b');385 CREATE TEMP TRIGGER tr3 AFTER DELETE ON t2 BEGIN386 DELETE FROM aux.t1;387 END;388 389 DELETE FROM main.t2;390 SELECT * FROM aux.t1;391} {}392 393do_execsql_test 8.3.3 { SELECT * FROM t1 } {a b}394 395#-------------------------------------------------------------------------396reset_db397set nDb 8398do_test 9.0 {399 for {set ii 0} {$ii < $nDb} {incr ii} {400 db eval "ATTACH ':memory:' AS db$ii"401 db eval "CREATE TABLE db$ii.tbl(a, b, c)"402 }403 404 for {set ii 0} {$ii < ($nDb-1)} {incr ii} {405 set jj [expr $ii+1]406 db eval "407 CREATE TEMP TRIGGER tr$ii AFTER INSERT ON db$ii.tbl BEGIN408 INSERT INTO db$jj.tbl VALUES(new.b, new.c, new.a);409 END;410 "411 }412} {}413 414do_execsql_test 9.1 { INSERT INTO db0.tbl VALUES('a', 'b', 'c'); }415do_execsql_test 9.1.1 { SELECT * FROM db0.tbl } {a b c}416do_execsql_test 9.1.2 { SELECT * FROM db1.tbl } {b c a}417do_execsql_test 9.1.3 { SELECT * FROM db2.tbl } {c a b}418do_execsql_test 9.1.1 { SELECT * FROM db3.tbl } {a b c}419do_execsql_test 9.1.2 { SELECT * FROM db4.tbl } {b c a}420do_execsql_test 9.1.3 { SELECT * FROM db5.tbl } {c a b}421do_execsql_test 9.1.1 { SELECT * FROM db6.tbl } {a b c}422do_execsql_test 9.1.2 { SELECT * FROM db7.tbl } {b c a}423 424do_test 9.2 {425 for {set ii 0} {$ii < ($nDb-1)} {incr ii} {426 set jj [expr $ii+1]427 db eval "428 CREATE TEMP TRIGGER tru$ii AFTER UPDATE ON db$ii.tbl BEGIN429 UPDATE db$jj.tbl SET a=new.b, b=new.c, c=new.a;430 END;431 "432 }433} {}434 435do_execsql_test 9.3 { UPDATE db0.tbl SET a=1, b=2, c=3 }436do_execsql_test 9.3.1 { SELECT * FROM db0.tbl } {1 2 3}437do_execsql_test 9.3.2 { SELECT * FROM db1.tbl } {2 3 1}438do_execsql_test 9.3.3 { SELECT * FROM db2.tbl } {3 1 2}439do_execsql_test 9.3.1 { SELECT * FROM db3.tbl } {1 2 3}440do_execsql_test 9.3.2 { SELECT * FROM db4.tbl } {2 3 1}441do_execsql_test 9.3.3 { SELECT * FROM db5.tbl } {3 1 2}442do_execsql_test 9.3.1 { SELECT * FROM db6.tbl } {1 2 3}443do_execsql_test 9.3.2 { SELECT * FROM db7.tbl } {2 3 1}444 445do_test 9.4 {446 for {set ii 0} {$ii < ($nDb-1)} {incr ii} {447 set jj [expr $ii+1]448 db eval "449 CREATE TEMP TRIGGER trd$ii BEFORE DELETE ON db$ii.tbl BEGIN450 DELETE FROM db$jj.tbl;451 END;452 "453 }454} {}455 456do_execsql_test 9.5 { DELETE FROM db0.tbl }457do_execsql_test 9.5.1 { SELECT * FROM db0.tbl } {}458do_execsql_test 9.5.2 { SELECT * FROM db1.tbl } {}459do_execsql_test 9.5.3 { SELECT * FROM db2.tbl } {}460do_execsql_test 9.5.1 { SELECT * FROM db3.tbl } {}461do_execsql_test 9.5.2 { SELECT * FROM db4.tbl } {}462do_execsql_test 9.5.3 { SELECT * FROM db5.tbl } {}463do_execsql_test 9.5.1 { SELECT * FROM db6.tbl } {}464do_execsql_test 9.5.2 { SELECT * FROM db7.tbl } {}465 466finish_test467