Return to schema4.test CVS log

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

sqlite3

    1: # 2010 September 28
    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. The
   12: # focus of this file is testing that a trigger may have the same
   13: # name as an index, view or table in the same database.
   14: #
   15: 
   16: set testdir [file dirname $argv0]
   17: source $testdir/tester.tcl
   18: 
   19: #--------------------------------------------------------------------------
   20: # Test organization:
   21: #
   22: #   schema4-1.*: Dropping and creating triggers and other objects where
   23: #     triggers and at least on other object share a name.
   24: #
   25: #   schema4-2.*: Renaming tables where there is a trigger that shares the
   26: #     name of the table or one of its indices.
   27: #
   28: 
   29: do_execsql_test schema4-1.1 {
   30:   CREATE TABLE log(x, a, b);
   31:   CREATE TABLE tbl(a, b);
   32: 
   33:   CREATE TABLE t1(a, b);
   34:   CREATE VIEW v1 AS SELECT * FROM tbl;
   35:   CREATE INDEX i1 ON tbl(a);
   36: } {}
   37: 
   38: do_execsql_test schema4-1.2 {
   39:   CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN
   40:     INSERT INTO log VALUES('after insert', new.a, new.b);
   41:   END;
   42:   CREATE TRIGGER v1 AFTER UPDATE ON tbl BEGIN
   43:     INSERT INTO log VALUES('after update', new.a, new.b);
   44:   END;
   45:   CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN
   46:     INSERT INTO log VALUES('after delete', old.a, old.b);
   47:   END;
   48: } {}
   49: 
   50: do_execsql_test schema4-1.3 {
   51:   INSERT INTO tbl VALUES(1, 2);
   52:   UPDATE tbl SET b=a+b, a=a+1;
   53:   DELETE FROM tbl;
   54: 
   55:   SELECT x, a, b FROM log;
   56: } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
   57: 
   58: do_execsql_test schema4-1.4 {
   59:   DELETE FROM log;
   60: 
   61:   DROP INDEX i1;
   62:   DROP TABLE t1;
   63:   DROP VIEW v1;
   64: 
   65:   INSERT INTO tbl VALUES(1, 2);
   66:   UPDATE tbl SET b=a+b, a=a+1;
   67:   DELETE FROM tbl;
   68: 
   69:   SELECT x, a, b FROM log;
   70: } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
   71: 
   72: db close
   73: sqlite3 db test.db
   74: 
   75: do_execsql_test schema4-1.5 {
   76:   DELETE FROM log;
   77:   INSERT INTO tbl VALUES(1, 2);
   78:   UPDATE tbl SET b=a+b, a=a+1;
   79:   DELETE FROM tbl;
   80:   SELECT x, a, b FROM log;
   81: } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
   82: 
   83: do_execsql_test schema4-1.6 {
   84:   CREATE TABLE t1(a, b);
   85:   CREATE VIEW v1 AS SELECT * FROM tbl;
   86:   CREATE INDEX i1 ON tbl(a);
   87: } {}
   88: 
   89: ifcapable fts3 {
   90:   do_execsql_test schema4-1.7 {
   91:     DROP TABLE t1;
   92:     CREATE VIRTUAL TABLE t1 USING fts3;
   93:   } {}
   94: 
   95:   do_execsql_test schema4-1.8 {
   96:     DELETE FROM log;
   97:     DROP TABLE t1;
   98:     INSERT INTO tbl VALUES(1, 2);
   99:     UPDATE tbl SET b=a+b, a=a+1;
  100:     DELETE FROM tbl;
  101:     SELECT x, a, b FROM log;
  102:   } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
  103: }
  104: 
  105: ifcapable altertable {
  106:   drop_all_tables
  107:   do_execsql_test schema4-2.1 {
  108:     CREATE TABLE log(x, a, b);
  109:     CREATE TABLE tbl(a, b);
  110:   
  111:     CREATE TABLE t1(a, b);
  112:     CREATE INDEX i1 ON t1(a, b);
  113:   } {}
  114:   
  115:   do_execsql_test schema4-2.2 {
  116:     CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN
  117:       INSERT INTO log VALUES('after insert', new.a, new.b);
  118:     END;
  119:     CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN
  120:       INSERT INTO log VALUES('after delete', old.a, old.b);
  121:     END;
  122:   } {}
  123: 
  124:   do_execsql_test schema4-2.3 { ALTER TABLE t1 RENAME TO t2 } {}
  125: 
  126:   do_execsql_test schema4-2.4 { 
  127:     INSERT INTO tbl VALUES('a', 'b');
  128:     DELETE FROM tbl;
  129:     SELECT * FROM log;
  130:   } {{after insert} a b {after delete} a b}
  131: 
  132:   db close
  133:   sqlite3 db test.db
  134: 
  135:   do_execsql_test schema4-2.5 { 
  136:     DELETE FROM log;
  137:     INSERT INTO tbl VALUES('c', 'd');
  138:     DELETE FROM tbl;
  139:     SELECT * FROM log;
  140:   } {{after insert} c d {after delete} c d}
  141: 
  142:   do_execsql_test schema4-2.6 {
  143:     CREATE TEMP TRIGGER x1 AFTER UPDATE ON tbl BEGIN
  144:       INSERT INTO log VALUES('after update', new.a, new.b);
  145:     END;
  146: 
  147:     CREATE TEMP TABLE x1(x);
  148:     INSERT INTO x1 VALUES(123);
  149:   } {}
  150: 
  151:   do_execsql_test schema4-2.8 {
  152:     select sql from sqlite_temp_master WHERE type='table';
  153:   } {{CREATE TABLE x1(x)}}
  154: 
  155:   do_execsql_test schema4-2.7 { ALTER TABLE tbl RENAME TO tbl2 } {}
  156: 
  157:   do_execsql_test schema4-2.9 {
  158:     select sql from sqlite_temp_master WHERE type='table';
  159:   } {{CREATE TABLE x1(x)}}
  160: 
  161:   do_execsql_test schema4-2.10 { 
  162:     DELETE FROM log;
  163:     INSERT INTO tbl2 VALUES('e', 'f');
  164:     UPDATE tbl2 SET a='g', b='h';
  165:     DELETE FROM tbl2;
  166:     SELECT * FROM log;
  167:   } {{after insert} e f {after update} g h {after delete} g h}
  168: 
  169:   do_execsql_test schema4-2.11 {
  170:     INSERT INTO x1 VALUES(456);
  171:     SELECT * FROM x1
  172:   } {123 456}
  173: }
  174: 
  175: finish_test