wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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