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