Annotation of embedaddon/sqlite3/test/schema4.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to schema4.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test