diff options
Diffstat (limited to 'test/trigger9.test')
| -rw-r--r-- | test/trigger9.test | 223 |
1 files changed, 223 insertions, 0 deletions
diff --git a/test/trigger9.test b/test/trigger9.test new file mode 100644 index 0000000..f56c8ac --- /dev/null +++ b/test/trigger9.test @@ -0,0 +1,223 @@ +# 2008 January 1 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. Specifically, +# it tests some compiler optimizations for SQL statements featuring +# triggers: +# +# +# + +# trigger9-1.* - Test that if there are no references to OLD.* cols, or a +# reference to only OLD.rowid, the data is not loaded. +# +# trigger9-2.* - Test that for NEW.* records populated by UPDATE +# statements, unused fields are populated with NULL values. +# +# trigger9-3.* - Test that the temporary tables used for OLD.* references +# in "INSTEAD OF" triggers have NULL values in unused +# fields. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +ifcapable {!trigger} { + finish_test + return +} + +proc has_rowdata {sql} { + expr {[lsearch [execsql "explain $sql"] RowData]>=0} +} + +do_test trigger9-1.1 { + execsql { + PRAGMA page_size = 1024; + CREATE TABLE t1(x, y, z); + INSERT INTO t1 VALUES('1', randstr(10000,10000), '2'); + INSERT INTO t1 VALUES('2', randstr(10000,10000), '4'); + INSERT INTO t1 VALUES('3', randstr(10000,10000), '6'); + CREATE TABLE t2(x); + } +} {} + +do_test trigger9-1.2.1 { + execsql { + BEGIN; + CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN + INSERT INTO t2 VALUES(old.rowid); + END; + DELETE FROM t1; + SELECT * FROM t2; + } +} {1 2 3} +do_test trigger9-1.2.3 { + has_rowdata {DELETE FROM t1} +} 0 +do_test trigger9-1.2.4 { execsql { ROLLBACK } } {} + +do_test trigger9-1.3.1 { + execsql { + BEGIN; + CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN + INSERT INTO t2 VALUES(old.x); + END; + DELETE FROM t1; + SELECT * FROM t2; + } +} {1 2 3} +do_test trigger9-1.3.2 { + has_rowdata {DELETE FROM t1} +} 0 +do_test trigger9-1.3.3 { execsql { ROLLBACK } } {} + +do_test trigger9-1.4.1 { + execsql { + BEGIN; + CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN + INSERT INTO t2 VALUES(old.rowid); + END; + DELETE FROM t1; + SELECT * FROM t2; + } +} {1} +do_test trigger9-1.4.2 { + has_rowdata {DELETE FROM t1} +} 0 +do_test trigger9-1.4.3 { execsql { ROLLBACK } } {} + +do_test trigger9-1.5.1 { + execsql { + BEGIN; + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO t2 VALUES(old.rowid); + END; + UPDATE t1 SET y = ''; + SELECT * FROM t2; + } +} {1 2 3} +do_test trigger9-1.5.2 { + has_rowdata {UPDATE t1 SET y = ''} +} 0 +do_test trigger9-1.5.3 { execsql { ROLLBACK } } {} + +do_test trigger9-1.6.1 { + execsql { + BEGIN; + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO t2 VALUES(old.x); + END; + UPDATE t1 SET y = ''; + SELECT * FROM t2; + } +} {1 2 3} +do_test trigger9-1.6.2 { + has_rowdata {UPDATE t1 SET y = ''} +} 0 +do_test trigger9-1.6.3 { execsql { ROLLBACK } } {} + +do_test trigger9-1.7.1 { + execsql { + BEGIN; + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN + INSERT INTO t2 VALUES(old.x); + END; + UPDATE t1 SET y = ''; + SELECT * FROM t2; + } +} {2 3} +do_test trigger9-1.7.2 { + has_rowdata {UPDATE t1 SET y = ''} +} 0 +do_test trigger9-1.7.3 { execsql { ROLLBACK } } {} + +do_test trigger9-3.1 { + execsql { + CREATE TABLE t3(a, b); + INSERT INTO t3 VALUES(1, 'one'); + INSERT INTO t3 VALUES(2, 'two'); + INSERT INTO t3 VALUES(3, 'three'); + } +} {} +do_test trigger9-3.2 { + execsql { + BEGIN; + CREATE VIEW v1 AS SELECT * FROM t3; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; + UPDATE v1 SET b = 'hello'; + SELECT * FROM t2; + ROLLBACK; + } +} {1 2 3} +do_test trigger9-3.3 { + # In this test the 'c' column of the view is not required by + # the INSTEAD OF trigger, but the expression is reused internally as + # part of the view's WHERE clause. Check that this does not cause + # a problem. + # + execsql { + BEGIN; + CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one'; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; + UPDATE v1 SET c = 'hello'; + SELECT * FROM t2; + ROLLBACK; + } +} {2 3} +do_test trigger9-3.4 { + execsql { + BEGIN; + INSERT INTO t3 VALUES(3, 'three'); + INSERT INTO t3 VALUES(3, 'four'); + CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; + UPDATE v1 SET b = 'hello'; + SELECT * FROM t2; + ROLLBACK; + } +} {1 2 3 3} + +ifcapable compound { + do_test trigger9-3.5 { + execsql { + BEGIN; + INSERT INTO t3 VALUES(1, 'uno'); + CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one'; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; + UPDATE v1 SET b = 'hello'; + SELECT * FROM t2; + ROLLBACK; + } + } {1 2 3} + do_test trigger9-3.6 { + execsql { + BEGIN; + INSERT INTO t3 VALUES(1, 'zero'); + CREATE VIEW v1 AS + SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two'; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; + UPDATE v1 SET b = 'hello'; + SELECT * FROM t2; + ROLLBACK; + } + } {2} +} + +finish_test |