Return to triggerB.test CVS log

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

sqlite3

    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