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 16set testdir [file dirname $argv0] 17source $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 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 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 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 close 73sqlite3 db test.db 74 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_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 175finish_test 176