Home | History | Annotate | Download | only in test 
      1 # 2008 April 15
      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 updating tables with constraints within a trigger.  Ticket #3055.
     13 #
     14 
     15 set testdir [file dirname $argv0]
     16 source $testdir/tester.tcl
     17 ifcapable {!trigger} {
     18   finish_test
     19   return
     20 }
     21 
     22 # Create test tables with constraints.
     23 #
     24 do_test triggerB-1.1 {
     25   execsql {
     26     CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
     27     INSERT INTO x(y) VALUES(1);
     28     INSERT INTO x(y) VALUES(1);
     29     CREATE TEMP VIEW vx AS SELECT x, y, 0 AS yy FROM x;
     30     CREATE TEMP TRIGGER tx INSTEAD OF UPDATE OF y ON vx
     31     BEGIN
     32       UPDATE x SET y = new.y WHERE x = new.x;
     33     END;
     34     SELECT * FROM vx;
     35   }
     36 } {1 1 0 2 1 0}
     37 do_test triggerB-1.2 {
     38   execsql {
     39     UPDATE vx SET y = yy;
     40     SELECT * FROM vx;
     41   }
     42 } {1 0 0 2 0 0}
     43 
     44 # Added 2008-08-22:
     45 #
     46 # Name resolution within triggers.
     47 #
     48 do_test triggerB-2.1 {
     49   catchsql {
     50     CREATE TRIGGER ty AFTER INSERT ON x BEGIN
     51        SELECT wen.x; -- Unrecognized name
     52     END;
     53     INSERT INTO x VALUES(1,2);
     54   }
     55 } {1 {no such column: wen.x}}
     56 do_test triggerB-2.2 {
     57   catchsql {
     58     CREATE TRIGGER tz AFTER UPDATE ON x BEGIN
     59        SELECT dlo.x; -- Unrecognized name
     60     END;
     61     UPDATE x SET y=y+1;
     62   }
     63 } {1 {no such column: dlo.x}}
     64 
     65 do_test triggerB-2.3 {
     66   execsql {
     67     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
     68     INSERT INTO t2 VALUES(1,2);
     69     CREATE TABLE changes(x,y);
     70     CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
     71       INSERT INTO changes VALUES(new.a, new.b);
     72     END;
     73   }
     74   execsql {
     75     UPDATE t2 SET a=a+10;
     76     SELECT * FROM changes;
     77   }
     78 } {11 2}
     79 do_test triggerB-2.4 {
     80   execsql {
     81     CREATE TRIGGER r2t2 AFTER DELETE ON t2 BEGIN
     82       INSERT INTO changes VALUES(old.a, old.c);
     83     END;
     84   }
     85   catchsql {
     86     DELETE FROM t2;
     87   }
     88 } {1 {no such column: old.c}}
     89 
     90 # Triggers maintain a mask of columns from the invoking table that are
     91 # used in the trigger body as NEW.column or OLD.column.  That mask is then
     92 # used to reduce the amount of information that needs to be loaded into
     93 # the NEW and OLD pseudo-tables at run-time.
     94 #
     95 # These tests cases check the logic for when there are many columns - more
     96 # than will fit in a bitmask.
     97 #
     98 do_test triggerB-3.1 {
     99   execsql {
    100     CREATE TABLE t3(
    101        c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,
    102        c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
    103        c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
    104        c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
    105        c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
    106        c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
    107        c60, c61, c62, c63, c64, c65
    108     );
    109     CREATE TABLE t3_changes(colnum, oldval, newval);
    110     INSERT INTO t3 VALUES(
    111        'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
    112        'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
    113        'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
    114        'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
    115        'a40','a41','a42','a43','a44','a45','a46','a47','a48','a49',
    116        'a50','a51','a52','a53','a54','a55','a56','a57','a58','a59',
    117        'a60','a61','a62','a63','a64','a65'
    118     );
    119   }
    120   for {set i 0} {$i<=65} {incr i} {
    121     set sql [subst {
    122       CREATE TRIGGER t3c$i AFTER UPDATE ON t3
    123          WHEN old.c$i!=new.c$i BEGIN
    124           INSERT INTO t3_changes VALUES($i, old.c$i, new.c$i);
    125       END
    126     }]
    127     db eval $sql
    128   }
    129   execsql {
    130     SELECT * FROM t3_changes
    131   }
    132 } {}
    133 for {set i 0} {$i<=64} {incr i} {
    134   do_test triggerB-3.2.$i.1 [subst {
    135     execsql {
    136       UPDATE t3 SET c$i='b$i';
    137       SELECT * FROM t3_changes ORDER BY rowid DESC LIMIT 1;
    138     }
    139   }] [subst {$i a$i b$i}]
    140   do_test triggerB-3.2.$i.2 [subst {
    141     execsql {
    142       SELECT count(*) FROM t3_changes
    143     }
    144   }] [expr {$i+1}]
    145   do_test triggerB-3.2.$i.2 [subst {
    146     execsql {
    147       SELECT * FROM t3_changes WHERE colnum=$i
    148     }
    149   }] [subst {$i a$i b$i}]
    150 }
    151   
    152 
    153 finish_test
    154