gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/trigger9.test (about) 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 set ::testprefix trigger9 36 37 proc has_rowdata {sql} { 38 expr {[lsearch [execsql "explain $sql"] RowData]>=0} 39 } 40 41 do_test trigger9-1.1 { 42 execsql { 43 PRAGMA page_size = 1024; 44 CREATE TABLE t1(x, y, z); 45 INSERT INTO t1 VALUES('1', randstr(10000,10000), '2'); 46 INSERT INTO t1 VALUES('2', randstr(10000,10000), '4'); 47 INSERT INTO t1 VALUES('3', randstr(10000,10000), '6'); 48 CREATE TABLE t2(x); 49 } 50 } {} 51 52 do_test trigger9-1.2.1 { 53 execsql { 54 BEGIN; 55 CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN 56 INSERT INTO t2 VALUES(old.rowid); 57 END; 58 DELETE FROM t1; 59 SELECT * FROM t2; 60 } 61 } {1 2 3} 62 do_test trigger9-1.2.3 { 63 has_rowdata {DELETE FROM t1} 64 } 0 65 do_test trigger9-1.2.4 { execsql { ROLLBACK } } {} 66 67 do_test trigger9-1.3.1 { 68 execsql { 69 BEGIN; 70 CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN 71 INSERT INTO t2 VALUES(old.x); 72 END; 73 DELETE FROM t1; 74 SELECT * FROM t2; 75 } 76 } {1 2 3} 77 do_test trigger9-1.3.2 { 78 has_rowdata {DELETE FROM t1} 79 } 0 80 do_test trigger9-1.3.3 { execsql { ROLLBACK } } {} 81 82 do_test trigger9-1.4.1 { 83 execsql { 84 BEGIN; 85 CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN 86 INSERT INTO t2 VALUES(old.rowid); 87 END; 88 DELETE FROM t1; 89 SELECT * FROM t2; 90 } 91 } {1} 92 do_test trigger9-1.4.2 { 93 has_rowdata {DELETE FROM t1} 94 } 0 95 do_test trigger9-1.4.3 { execsql { ROLLBACK } } {} 96 97 do_test trigger9-1.5.1 { 98 execsql { 99 BEGIN; 100 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN 101 INSERT INTO t2 VALUES(old.rowid); 102 END; 103 UPDATE t1 SET y = ''; 104 SELECT * FROM t2; 105 } 106 } {1 2 3} 107 do_test trigger9-1.5.2 { 108 has_rowdata {UPDATE t1 SET y = ''} 109 } 0 110 do_test trigger9-1.5.3 { execsql { ROLLBACK } } {} 111 112 do_test trigger9-1.6.1 { 113 execsql { 114 BEGIN; 115 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN 116 INSERT INTO t2 VALUES(old.x); 117 END; 118 UPDATE t1 SET y = ''; 119 SELECT * FROM t2; 120 } 121 } {1 2 3} 122 do_test trigger9-1.6.2 { 123 has_rowdata {UPDATE t1 SET y = ''} 124 } 0 125 do_test trigger9-1.6.3 { execsql { ROLLBACK } } {} 126 127 do_test trigger9-1.7.1 { 128 execsql { 129 BEGIN; 130 CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN 131 INSERT INTO t2 VALUES(old.x); 132 END; 133 UPDATE t1 SET y = ''; 134 SELECT * FROM t2; 135 } 136 } {2 3} 137 do_test trigger9-1.7.2 { 138 has_rowdata {UPDATE t1 SET y = ''} 139 } 0 140 do_test trigger9-1.7.3 { execsql { ROLLBACK } } {} 141 142 do_test trigger9-3.1 { 143 execsql { 144 CREATE TABLE t3(a, b); 145 INSERT INTO t3 VALUES(1, 'one'); 146 INSERT INTO t3 VALUES(2, 'two'); 147 INSERT INTO t3 VALUES(3, 'three'); 148 } 149 } {} 150 do_test trigger9-3.2 { 151 execsql { 152 BEGIN; 153 CREATE VIEW v1 AS SELECT * FROM t3; 154 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 155 INSERT INTO t2 VALUES(old.a); 156 END; 157 UPDATE v1 SET b = 'hello'; 158 SELECT * FROM t2; 159 ROLLBACK; 160 } 161 } {1 2 3} 162 do_test trigger9-3.3 { 163 # In this test the 'c' column of the view is not required by 164 # the INSTEAD OF trigger, but the expression is reused internally as 165 # part of the view's WHERE clause. Check that this does not cause 166 # a problem. 167 # 168 execsql { 169 BEGIN; 170 CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one'; 171 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 172 INSERT INTO t2 VALUES(old.a); 173 END; 174 UPDATE v1 SET c = 'hello'; 175 SELECT * FROM t2; 176 ROLLBACK; 177 } 178 } {2 3} 179 do_test trigger9-3.4 { 180 execsql { 181 BEGIN; 182 INSERT INTO t3 VALUES(3, 'three'); 183 INSERT INTO t3 VALUES(3, 'four'); 184 CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3; 185 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 186 INSERT INTO t2 VALUES(old.a); 187 END; 188 UPDATE v1 SET b = 'hello'; 189 SELECT * FROM t2; 190 ROLLBACK; 191 } 192 } {1 2 3 3} 193 194 ifcapable compound { 195 do_test trigger9-3.5 { 196 execsql { 197 BEGIN; 198 INSERT INTO t3 VALUES(1, 'uno'); 199 CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one'; 200 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 201 INSERT INTO t2 VALUES(old.a); 202 END; 203 UPDATE v1 SET b = 'hello'; 204 SELECT * FROM t2; 205 ROLLBACK; 206 } 207 } {1 2 3} 208 do_test trigger9-3.6 { 209 execsql { 210 BEGIN; 211 INSERT INTO t3 VALUES(1, 'zero'); 212 CREATE VIEW v1 AS 213 SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two'; 214 CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN 215 INSERT INTO t2 VALUES(old.a); 216 END; 217 UPDATE v1 SET b = 'hello'; 218 SELECT * FROM t2; 219 ROLLBACK; 220 } 221 } {2} 222 } 223 224 reset_db 225 do_execsql_test 4.1 { 226 CREATE TABLE t1(a, b); 227 CREATE TABLE log(x); 228 INSERT INTO t1 VALUES(1, 2); 229 INSERT INTO t1 VALUES(3, 4); 230 CREATE VIEW v1 AS SELECT a, b FROM t1; 231 232 CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN 233 INSERT INTO log VALUES('delete'); 234 END; 235 236 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON v1 BEGIN 237 INSERT INTO log VALUES('update'); 238 END; 239 240 CREATE TRIGGER tr3 INSTEAD OF INSERT ON v1 BEGIN 241 INSERT INTO log VALUES('insert'); 242 END; 243 } 244 245 do_catchsql_test 4.2 { 246 DELETE FROM v1 WHERE rowid=1; 247 } {1 {no such column: rowid}} 248 249 do_catchsql_test 4.3 { 250 UPDATE v1 SET a=b WHERE rowid=2; 251 } {1 {no such column: rowid}} 252 253 finish_test