1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/triggerB.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,153 @@
1.4 +# 2008 April 15
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 updating tables with constraints within a trigger. Ticket #3055.
1.16 +#
1.17 +
1.18 +set testdir [file dirname $argv0]
1.19 +source $testdir/tester.tcl
1.20 +ifcapable {!trigger} {
1.21 + finish_test
1.22 + return
1.23 +}
1.24 +
1.25 +# Create test tables with constraints.
1.26 +#
1.27 +do_test triggerB-1.1 {
1.28 + execsql {
1.29 + CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
1.30 + INSERT INTO x(y) VALUES(1);
1.31 + INSERT INTO x(y) VALUES(1);
1.32 + CREATE TEMP VIEW vx AS SELECT x, y, 0 AS yy FROM x;
1.33 + CREATE TEMP TRIGGER tx INSTEAD OF UPDATE OF y ON vx
1.34 + BEGIN
1.35 + UPDATE x SET y = new.y WHERE x = new.x;
1.36 + END;
1.37 + SELECT * FROM vx;
1.38 + }
1.39 +} {1 1 0 2 1 0}
1.40 +do_test triggerB-1.2 {
1.41 + execsql {
1.42 + UPDATE vx SET y = yy;
1.43 + SELECT * FROM vx;
1.44 + }
1.45 +} {1 0 0 2 0 0}
1.46 +
1.47 +# Added 2008-08-22:
1.48 +#
1.49 +# Name resolution within triggers.
1.50 +#
1.51 +do_test triggerB-2.1 {
1.52 + catchsql {
1.53 + CREATE TRIGGER ty AFTER INSERT ON x BEGIN
1.54 + SELECT wen.x; -- Unrecognized name
1.55 + END;
1.56 + INSERT INTO x VALUES(1,2);
1.57 + }
1.58 +} {1 {no such column: wen.x}}
1.59 +do_test triggerB-2.2 {
1.60 + catchsql {
1.61 + CREATE TRIGGER tz AFTER UPDATE ON x BEGIN
1.62 + SELECT dlo.x; -- Unrecognized name
1.63 + END;
1.64 + UPDATE x SET y=y+1;
1.65 + }
1.66 +} {1 {no such column: dlo.x}}
1.67 +
1.68 +do_test triggerB-2.3 {
1.69 + execsql {
1.70 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
1.71 + INSERT INTO t2 VALUES(1,2);
1.72 + CREATE TABLE changes(x,y);
1.73 + CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
1.74 + INSERT INTO changes VALUES(new.a, new.b);
1.75 + END;
1.76 + }
1.77 + execsql {
1.78 + UPDATE t2 SET a=a+10;
1.79 + SELECT * FROM changes;
1.80 + }
1.81 +} {11 2}
1.82 +do_test triggerB-2.4 {
1.83 + execsql {
1.84 + CREATE TRIGGER r2t2 AFTER DELETE ON t2 BEGIN
1.85 + INSERT INTO changes VALUES(old.a, old.c);
1.86 + END;
1.87 + }
1.88 + catchsql {
1.89 + DELETE FROM t2;
1.90 + }
1.91 +} {1 {no such column: old.c}}
1.92 +
1.93 +# Triggers maintain a mask of columns from the invoking table that are
1.94 +# used in the trigger body as NEW.column or OLD.column. That mask is then
1.95 +# used to reduce the amount of information that needs to be loaded into
1.96 +# the NEW and OLD pseudo-tables at run-time.
1.97 +#
1.98 +# These tests cases check the logic for when there are many columns - more
1.99 +# than will fit in a bitmask.
1.100 +#
1.101 +do_test triggerB-3.1 {
1.102 + execsql {
1.103 + CREATE TABLE t3(
1.104 + c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
1.105 + c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
1.106 + c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
1.107 + c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
1.108 + c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
1.109 + c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
1.110 + c60, c61, c62, c63, c64, c65
1.111 + );
1.112 + CREATE TABLE t3_changes(colnum, oldval, newval);
1.113 + INSERT INTO t3 VALUES(
1.114 + 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
1.115 + 'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
1.116 + 'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
1.117 + 'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
1.118 + 'a40','a41','a42','a43','a44','a45','a46','a47','a48','a49',
1.119 + 'a50','a51','a52','a53','a54','a55','a56','a57','a58','a59',
1.120 + 'a60','a61','a62','a63','a64','a65'
1.121 + );
1.122 + }
1.123 + for {set i 0} {$i<=65} {incr i} {
1.124 + set sql [subst {
1.125 + CREATE TRIGGER t3c$i AFTER UPDATE ON t3
1.126 + WHEN old.c$i!=new.c$i BEGIN
1.127 + INSERT INTO t3_changes VALUES($i, old.c$i, new.c$i);
1.128 + END
1.129 + }]
1.130 + db eval $sql
1.131 + }
1.132 + execsql {
1.133 + SELECT * FROM t3_changes
1.134 + }
1.135 +} {}
1.136 +for {set i 0} {$i<=64} {incr i} {
1.137 + do_test triggerB-3.2.$i.1 [subst {
1.138 + execsql {
1.139 + UPDATE t3 SET c$i='b$i';
1.140 + SELECT * FROM t3_changes ORDER BY rowid DESC LIMIT 1;
1.141 + }
1.142 + }] [subst {$i a$i b$i}]
1.143 + do_test triggerB-3.2.$i.2 [subst {
1.144 + execsql {
1.145 + SELECT count(*) FROM t3_changes
1.146 + }
1.147 + }] [expr {$i+1}]
1.148 + do_test triggerB-3.2.$i.2 [subst {
1.149 + execsql {
1.150 + SELECT * FROM t3_changes WHERE colnum=$i
1.151 + }
1.152 + }] [subst {$i a$i b$i}]
1.153 +}
1.154 +
1.155 +
1.156 +finish_test