sl@0: # 2008 April 15
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice', here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library. Specifically,
sl@0: # it tests updating tables with constraints within a trigger.  Ticket #3055.
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: ifcapable {!trigger} {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: # Create test tables with constraints.
sl@0: #
sl@0: do_test triggerB-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
sl@0:     INSERT INTO x(y) VALUES(1);
sl@0:     INSERT INTO x(y) VALUES(1);
sl@0:     CREATE TEMP VIEW vx AS SELECT x, y, 0 AS yy FROM x;
sl@0:     CREATE TEMP TRIGGER tx INSTEAD OF UPDATE OF y ON vx
sl@0:     BEGIN
sl@0:       UPDATE x SET y = new.y WHERE x = new.x;
sl@0:     END;
sl@0:     SELECT * FROM vx;
sl@0:   }
sl@0: } {1 1 0 2 1 0}
sl@0: do_test triggerB-1.2 {
sl@0:   execsql {
sl@0:     UPDATE vx SET y = yy;
sl@0:     SELECT * FROM vx;
sl@0:   }
sl@0: } {1 0 0 2 0 0}
sl@0: 
sl@0: # Added 2008-08-22:
sl@0: #
sl@0: # Name resolution within triggers.
sl@0: #
sl@0: do_test triggerB-2.1 {
sl@0:   catchsql {
sl@0:     CREATE TRIGGER ty AFTER INSERT ON x BEGIN
sl@0:        SELECT wen.x; -- Unrecognized name
sl@0:     END;
sl@0:     INSERT INTO x VALUES(1,2);
sl@0:   }
sl@0: } {1 {no such column: wen.x}}
sl@0: do_test triggerB-2.2 {
sl@0:   catchsql {
sl@0:     CREATE TRIGGER tz AFTER UPDATE ON x BEGIN
sl@0:        SELECT dlo.x; -- Unrecognized name
sl@0:     END;
sl@0:     UPDATE x SET y=y+1;
sl@0:   }
sl@0: } {1 {no such column: dlo.x}}
sl@0: 
sl@0: do_test triggerB-2.3 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
sl@0:     INSERT INTO t2 VALUES(1,2);
sl@0:     CREATE TABLE changes(x,y);
sl@0:     CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
sl@0:       INSERT INTO changes VALUES(new.a, new.b);
sl@0:     END;
sl@0:   }
sl@0:   execsql {
sl@0:     UPDATE t2 SET a=a+10;
sl@0:     SELECT * FROM changes;
sl@0:   }
sl@0: } {11 2}
sl@0: do_test triggerB-2.4 {
sl@0:   execsql {
sl@0:     CREATE TRIGGER r2t2 AFTER DELETE ON t2 BEGIN
sl@0:       INSERT INTO changes VALUES(old.a, old.c);
sl@0:     END;
sl@0:   }
sl@0:   catchsql {
sl@0:     DELETE FROM t2;
sl@0:   }
sl@0: } {1 {no such column: old.c}}
sl@0: 
sl@0: # Triggers maintain a mask of columns from the invoking table that are
sl@0: # used in the trigger body as NEW.column or OLD.column.  That mask is then
sl@0: # used to reduce the amount of information that needs to be loaded into
sl@0: # the NEW and OLD pseudo-tables at run-time.
sl@0: #
sl@0: # These tests cases check the logic for when there are many columns - more
sl@0: # than will fit in a bitmask.
sl@0: #
sl@0: do_test triggerB-3.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(
sl@0:        c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,
sl@0:        c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
sl@0:        c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
sl@0:        c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
sl@0:        c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
sl@0:        c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
sl@0:        c60, c61, c62, c63, c64, c65
sl@0:     );
sl@0:     CREATE TABLE t3_changes(colnum, oldval, newval);
sl@0:     INSERT INTO t3 VALUES(
sl@0:        'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
sl@0:        'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
sl@0:        'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
sl@0:        'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
sl@0:        'a40','a41','a42','a43','a44','a45','a46','a47','a48','a49',
sl@0:        'a50','a51','a52','a53','a54','a55','a56','a57','a58','a59',
sl@0:        'a60','a61','a62','a63','a64','a65'
sl@0:     );
sl@0:   }
sl@0:   for {set i 0} {$i<=65} {incr i} {
sl@0:     set sql [subst {
sl@0:       CREATE TRIGGER t3c$i AFTER UPDATE ON t3
sl@0:          WHEN old.c$i!=new.c$i BEGIN
sl@0:           INSERT INTO t3_changes VALUES($i, old.c$i, new.c$i);
sl@0:       END
sl@0:     }]
sl@0:     db eval $sql
sl@0:   }
sl@0:   execsql {
sl@0:     SELECT * FROM t3_changes
sl@0:   }
sl@0: } {}
sl@0: for {set i 0} {$i<=64} {incr i} {
sl@0:   do_test triggerB-3.2.$i.1 [subst {
sl@0:     execsql {
sl@0:       UPDATE t3 SET c$i='b$i';
sl@0:       SELECT * FROM t3_changes ORDER BY rowid DESC LIMIT 1;
sl@0:     }
sl@0:   }] [subst {$i a$i b$i}]
sl@0:   do_test triggerB-3.2.$i.2 [subst {
sl@0:     execsql {
sl@0:       SELECT count(*) FROM t3_changes
sl@0:     }
sl@0:   }] [expr {$i+1}]
sl@0:   do_test triggerB-3.2.$i.2 [subst {
sl@0:     execsql {
sl@0:       SELECT * FROM t3_changes WHERE colnum=$i
sl@0:     }
sl@0:   }] [subst {$i a$i b$i}]
sl@0: }
sl@0:   
sl@0: 
sl@0: finish_test