AryaWu/sqlite

1# 2010 September 282#3# The author disclaims copyright to this source code.  In place of4# 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. The12# focus of this file is testing that a trigger may have the same13# name as an index, view or table in the same database.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19#--------------------------------------------------------------------------20# Test organization:21#22#   schema4-1.*: Dropping and creating triggers and other objects where23#     triggers and at least on other object share a name.24#25#   schema4-2.*: Renaming tables where there is a trigger that shares the26#     name of the table or one of its indices.27#28 29do_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 38do_execsql_test schema4-1.2 {39  CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN40    INSERT INTO log VALUES('after insert', new.a, new.b);41  END;42  CREATE TRIGGER v1 AFTER UPDATE ON tbl BEGIN43    INSERT INTO log VALUES('after update', new.a, new.b);44  END;45  CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN46    INSERT INTO log VALUES('after delete', old.a, old.b);47  END;48} {}49 50do_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 58do_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 72db close73sqlite3 db test.db74 75do_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 83do_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 89ifcapable 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 105ifcapable altertable {106  drop_all_tables107  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 BEGIN117      INSERT INTO log VALUES('after insert', new.a, new.b);118    END;119    CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN120      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 close133  sqlite3 db test.db134 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 BEGIN144      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 temp.sqlite_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 x1172  } {123 456}173}174 175finish_test176