Return to triggerB.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/triggerB.test, revision 1.1

1.1     ! misho       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