1 # 2008 January 1 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 implements regression tests for SQLite library. Specifically, 12 # it tests some compiler optimizations for SQL statements featuring 13 # triggers: 14 # 15 # 16 # 17 18 # trigger9-1.* - Test that if there are no references to OLD.* cols, or a 19 # reference to only OLD.rowid, the data is not loaded. 20 # 21 # trigger9-2.* - Test that for NEW.* records populated by UPDATE 22 # statements, unused fields are populated with NULL values. 23 # 24 # trigger9-3.* - Test that the temporary tables used for OLD.* references 25 # in "INSTEAD OF" triggers have NULL values in unused 26 # fields. 27 # 28 29 set testdir [file dirname $argv0] 30 source $testdir/tester.tcl 31 ifcapable {!trigger} { 32 finish_test 33 return 34 } 35 36 proc has_rowdata {sql} { 37 expr {[lsearch [execsql "explain $sql"] RowData]>=0} 38 } 39 40 do_test trigger9-1.1 { 41 execsql { 42 PRAGMA page_size = 1024; 43 CREATE TABLE t1(x, y, z); 44 INSERT INTO t1 VALUES('1', randstr(10000,10000), '2'); 45 INSERT INTO t1 VALUES('2', randstr(10000,10000), '4'); 46 INSERT INTO t1 VALUES('3', randstr(10000,10000), '6'); 47 CREATE TABLE t2(x); 48 } 49 } {} 50 51 do_test trigger9-1.2.1 { 52 execsql { 53 BEGIN; 54 CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN 55 INSERT INTO t2 VALUES(old.rowid); 56 END; 57 DELETE FROM t1; 58 SELECT * FROM t2; 59 } 60 } {1 2 3} 61 do_test trigger9-1.2.3 { 62 has_rowdata {DELETE FROM t1} 63 } 0 64 do_test trigger9-1.2.4 { execsql { ROLLBACK } } {} 65 66 do_test trigger9-1.3.1 { 67 execsql { 68 BEGIN; 69 CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN 70 INSERT INTO t2 VALUES(old.x); 71 END; 72 DELETE FROM t1; 73 SELECT * FROM t2; 74 } 75 } {1 2 3} 76 do_test trigger9-1.3.2 { 77 has_rowdata {DELETE FROM t1} 78 } 0 79 do_test trigger9-1.3.3 { execsql { ROLLBACK } } {} 80 81 do_test trigger9-1.4.1 { 82 execsql { 83 BEGIN; 84 CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN 85 INSERT INTO t2 VALUES(old.rowid); 86 END; 87 DELETE FROM t1; 88 SELECT * FROM t2; 89 } 90 } {1} 91 do_test trigger9-1.4.2 { 92 has_rowdata {DELETE FROM t1} 93 } 0 94 do_test trigger9-1.4.3 { execsql { ROLLBACK } } {} 95 96 do_test trigger9-1.5.1 { 97 execsql { 98 BEGIN; 99 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN 100 INSERT INTO t2 VALUES(old.rowid); 101 END; 102 UPDATE t1 SET y = ''; 103 SELECT * FROM t2; 104 } 105 } {1 2 3} 106 do_test trigger9-1.5.2 { 107 has_rowdata {UPDATE t1 SET y = ''} 108 } 0 109 do_test trigger9-1.5.3 { execsql { ROLLBACK } } {} 110 111 do_test trigger9-1.6.1 { 112 execsql { 113 BEGIN; 114 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN 115 INSERT INTO t2 VALUES(old.x); 116 END; 117 UPDATE t1 SET y = ''; 118 SELECT * FROM t2; 119 } 120 } {1 2 3} 121 do_test trigger9-1.6.2 { 122 has_rowdata {UPDATE t1 SET y = ''} 123 } 0 124 do_test trigger9-1.6.3 { execsql { ROLLBACK } } {} 125 126 do_test trigger9-1.7.1 { 127 execsql { 128 BEGIN; 129 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN 130 INSERT INTO t2 VALUES(old.x); 131 END; 132 UPDATE t1 SET y = ''; 133 SELECT * FROM t2; 134 } 135 } {2 3} 136 do_test trigger9-1.7.2 { 137 has_rowdata {UPDATE t1 SET y = ''} 138 } 0 139 do_test trigger9-1.7.3 { execsql { ROLLBACK } } {} 140 141 do_test trigger9-3.1 { 142 execsql { 143 CREATE TABLE t3(a, b); 144 INSERT INTO t3 VALUES(1, 'one'); 145 INSERT INTO t3 VALUES(2, 'two'); 146 INSERT INTO t3 VALUES(3, 'three'); 147 } 148 } {} 149 do_test trigger9-3.2 { 150 execsql { 151 BEGIN; 152 CREATE VIEW v1 AS SELECT * FROM t3; 153 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 154 INSERT INTO t2 VALUES(old.a); 155 END; 156 UPDATE v1 SET b = 'hello'; 157 SELECT * FROM t2; 158 ROLLBACK; 159 } 160 } {1 2 3} 161 do_test trigger9-3.3 { 162 # In this test the 'c' column of the view is not required by 163 # the INSTEAD OF trigger, but the expression is reused internally as 164 # part of the view's WHERE clause. Check that this does not cause 165 # a problem. 166 # 167 execsql { 168 BEGIN; 169 CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one'; 170 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 171 INSERT INTO t2 VALUES(old.a); 172 END; 173 UPDATE v1 SET c = 'hello'; 174 SELECT * FROM t2; 175 ROLLBACK; 176 } 177 } {2 3} 178 do_test trigger9-3.4 { 179 execsql { 180 BEGIN; 181 INSERT INTO t3 VALUES(3, 'three'); 182 INSERT INTO t3 VALUES(3, 'four'); 183 CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3; 184 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 185 INSERT INTO t2 VALUES(old.a); 186 END; 187 UPDATE v1 SET b = 'hello'; 188 SELECT * FROM t2; 189 ROLLBACK; 190 } 191 } {1 2 3 3} 192 193 ifcapable compound { 194 do_test trigger9-3.5 { 195 execsql { 196 BEGIN; 197 INSERT INTO t3 VALUES(1, 'uno'); 198 CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one'; 199 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 200 INSERT INTO t2 VALUES(old.a); 201 END; 202 UPDATE v1 SET b = 'hello'; 203 SELECT * FROM t2; 204 ROLLBACK; 205 } 206 } {1 2 3} 207 do_test trigger9-3.6 { 208 execsql { 209 BEGIN; 210 INSERT INTO t3 VALUES(1, 'zero'); 211 CREATE VIEW v1 AS 212 SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two'; 213 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 214 INSERT INTO t2 VALUES(old.a); 215 END; 216 UPDATE v1 SET b = 'hello'; 217 SELECT * FROM t2; 218 ROLLBACK; 219 } 220 } {2} 221 } 222 223 finish_test 224
