1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trigger9.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,223 @@
1.4 +# 2008 January 1
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. Specifically,
1.15 +# it tests some compiler optimizations for SQL statements featuring
1.16 +# triggers:
1.17 +#
1.18 +#
1.19 +#
1.20 +
1.21 +# trigger9-1.* - Test that if there are no references to OLD.* cols, or a
1.22 +# reference to only OLD.rowid, the data is not loaded.
1.23 +#
1.24 +# trigger9-2.* - Test that for NEW.* records populated by UPDATE
1.25 +# statements, unused fields are populated with NULL values.
1.26 +#
1.27 +# trigger9-3.* - Test that the temporary tables used for OLD.* references
1.28 +# in "INSTEAD OF" triggers have NULL values in unused
1.29 +# fields.
1.30 +#
1.31 +
1.32 +set testdir [file dirname $argv0]
1.33 +source $testdir/tester.tcl
1.34 +ifcapable {!trigger} {
1.35 + finish_test
1.36 + return
1.37 +}
1.38 +
1.39 +proc has_rowdata {sql} {
1.40 + expr {[lsearch [execsql "explain $sql"] RowData]>=0}
1.41 +}
1.42 +
1.43 +do_test trigger9-1.1 {
1.44 + execsql {
1.45 + PRAGMA page_size = 1024;
1.46 + CREATE TABLE t1(x, y, z);
1.47 + INSERT INTO t1 VALUES('1', randstr(10000,10000), '2');
1.48 + INSERT INTO t1 VALUES('2', randstr(10000,10000), '4');
1.49 + INSERT INTO t1 VALUES('3', randstr(10000,10000), '6');
1.50 + CREATE TABLE t2(x);
1.51 + }
1.52 +} {}
1.53 +
1.54 +do_test trigger9-1.2.1 {
1.55 + execsql {
1.56 + BEGIN;
1.57 + CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN
1.58 + INSERT INTO t2 VALUES(old.rowid);
1.59 + END;
1.60 + DELETE FROM t1;
1.61 + SELECT * FROM t2;
1.62 + }
1.63 +} {1 2 3}
1.64 +do_test trigger9-1.2.3 {
1.65 + has_rowdata {DELETE FROM t1}
1.66 +} 0
1.67 +do_test trigger9-1.2.4 { execsql { ROLLBACK } } {}
1.68 +
1.69 +do_test trigger9-1.3.1 {
1.70 + execsql {
1.71 + BEGIN;
1.72 + CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN
1.73 + INSERT INTO t2 VALUES(old.x);
1.74 + END;
1.75 + DELETE FROM t1;
1.76 + SELECT * FROM t2;
1.77 + }
1.78 +} {1 2 3}
1.79 +do_test trigger9-1.3.2 {
1.80 + has_rowdata {DELETE FROM t1}
1.81 +} 1
1.82 +do_test trigger9-1.3.3 { execsql { ROLLBACK } } {}
1.83 +
1.84 +do_test trigger9-1.4.1 {
1.85 + execsql {
1.86 + BEGIN;
1.87 + CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN
1.88 + INSERT INTO t2 VALUES(old.rowid);
1.89 + END;
1.90 + DELETE FROM t1;
1.91 + SELECT * FROM t2;
1.92 + }
1.93 +} {1}
1.94 +do_test trigger9-1.4.2 {
1.95 + has_rowdata {DELETE FROM t1}
1.96 +} 1
1.97 +do_test trigger9-1.4.3 { execsql { ROLLBACK } } {}
1.98 +
1.99 +do_test trigger9-1.5.1 {
1.100 + execsql {
1.101 + BEGIN;
1.102 + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
1.103 + INSERT INTO t2 VALUES(old.rowid);
1.104 + END;
1.105 + UPDATE t1 SET y = '';
1.106 + SELECT * FROM t2;
1.107 + }
1.108 +} {1 2 3}
1.109 +do_test trigger9-1.5.2 {
1.110 + has_rowdata {UPDATE t1 SET y = ''}
1.111 +} 0
1.112 +do_test trigger9-1.5.3 { execsql { ROLLBACK } } {}
1.113 +
1.114 +do_test trigger9-1.6.1 {
1.115 + execsql {
1.116 + BEGIN;
1.117 + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
1.118 + INSERT INTO t2 VALUES(old.x);
1.119 + END;
1.120 + UPDATE t1 SET y = '';
1.121 + SELECT * FROM t2;
1.122 + }
1.123 +} {1 2 3}
1.124 +do_test trigger9-1.6.2 {
1.125 + has_rowdata {UPDATE t1 SET y = ''}
1.126 +} 1
1.127 +do_test trigger9-1.6.3 { execsql { ROLLBACK } } {}
1.128 +
1.129 +do_test trigger9-1.7.1 {
1.130 + execsql {
1.131 + BEGIN;
1.132 + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN
1.133 + INSERT INTO t2 VALUES(old.x);
1.134 + END;
1.135 + UPDATE t1 SET y = '';
1.136 + SELECT * FROM t2;
1.137 + }
1.138 +} {2 3}
1.139 +do_test trigger9-1.7.2 {
1.140 + has_rowdata {UPDATE t1 SET y = ''}
1.141 +} 1
1.142 +do_test trigger9-1.7.3 { execsql { ROLLBACK } } {}
1.143 +
1.144 +do_test trigger9-3.1 {
1.145 + execsql {
1.146 + CREATE TABLE t3(a, b);
1.147 + INSERT INTO t3 VALUES(1, 'one');
1.148 + INSERT INTO t3 VALUES(2, 'two');
1.149 + INSERT INTO t3 VALUES(3, 'three');
1.150 + }
1.151 +} {}
1.152 +do_test trigger9-3.2 {
1.153 + execsql {
1.154 + BEGIN;
1.155 + CREATE VIEW v1 AS SELECT * FROM t3;
1.156 + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
1.157 + INSERT INTO t2 VALUES(old.a);
1.158 + END;
1.159 + UPDATE v1 SET b = 'hello';
1.160 + SELECT * FROM t2;
1.161 + ROLLBACK;
1.162 + }
1.163 +} {1 2 3}
1.164 +do_test trigger9-3.3 {
1.165 + # In this test the 'c' column of the view is not required by
1.166 + # the INSTEAD OF trigger, but the expression is reused internally as
1.167 + # part of the view's WHERE clause. Check that this does not cause
1.168 + # a problem.
1.169 + #
1.170 + execsql {
1.171 + BEGIN;
1.172 + CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one';
1.173 + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
1.174 + INSERT INTO t2 VALUES(old.a);
1.175 + END;
1.176 + UPDATE v1 SET c = 'hello';
1.177 + SELECT * FROM t2;
1.178 + ROLLBACK;
1.179 + }
1.180 +} {2 3}
1.181 +do_test trigger9-3.4 {
1.182 + execsql {
1.183 + BEGIN;
1.184 + INSERT INTO t3 VALUES(3, 'three');
1.185 + INSERT INTO t3 VALUES(3, 'four');
1.186 + CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3;
1.187 + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
1.188 + INSERT INTO t2 VALUES(old.a);
1.189 + END;
1.190 + UPDATE v1 SET b = 'hello';
1.191 + SELECT * FROM t2;
1.192 + ROLLBACK;
1.193 + }
1.194 +} {1 2 3 3}
1.195 +
1.196 +ifcapable compound {
1.197 + do_test trigger9-3.5 {
1.198 + execsql {
1.199 + BEGIN;
1.200 + INSERT INTO t3 VALUES(1, 'uno');
1.201 + CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one';
1.202 + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
1.203 + INSERT INTO t2 VALUES(old.a);
1.204 + END;
1.205 + UPDATE v1 SET b = 'hello';
1.206 + SELECT * FROM t2;
1.207 + ROLLBACK;
1.208 + }
1.209 + } {1 2 3}
1.210 + do_test trigger9-3.6 {
1.211 + execsql {
1.212 + BEGIN;
1.213 + INSERT INTO t3 VALUES(1, 'zero');
1.214 + CREATE VIEW v1 AS
1.215 + SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';
1.216 + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
1.217 + INSERT INTO t2 VALUES(old.a);
1.218 + END;
1.219 + UPDATE v1 SET b = 'hello';
1.220 + SELECT * FROM t2;
1.221 + ROLLBACK;
1.222 + }
1.223 + } {2}
1.224 +}
1.225 +
1.226 +finish_test