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 176
