wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2009 December 29
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 #
12 # Verify that when columns named "rowid", "oid", and "_rowid_" appear
13 # in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then
14 # the use of these columns in triggers will refer to the column and not
15 # to the actual ROWID. Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3]
16 #
17 # Also, verify that triggers created like this:
18 #
19 # CREATE TRIGGER attached.trig AFTER INSERT ON attached.tab ...
20 #
21 # can be reparsed as a main database. Ticket [d6ddba6706353915ceedc56b4e3]
22 #
23  
24 set testdir [file dirname $argv0]
25 source $testdir/tester.tcl
26 ifcapable {!trigger} {
27 finish_test
28 return
29 }
30  
31 # Triggers on tables where the table has ordinary columns named
32 # rowid, oid, and _rowid_.
33 #
34 do_test triggerD-1.1 {
35 db eval {
36 CREATE TABLE t1(rowid, oid, _rowid_, x);
37 CREATE TABLE log(a,b,c,d,e);
38 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
39 INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
40 END;
41 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
42 INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
43 END;
44 CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
45 INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
46 INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
47 END;
48 CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
49 INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
50 INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
51 END;
52 CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
53 INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
54 END;
55 CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
56 INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
57 END;
58 }
59 } {}
60 do_test triggerD-1.2 {
61 db eval {
62 INSERT INTO t1 VALUES(100,200,300,400);
63 SELECT * FROM log
64 }
65 } {r1 100 200 300 400 r2 100 200 300 400}
66 do_test triggerD-1.3 {
67 db eval {
68 DELETE FROM log;
69 UPDATE t1 SET rowid=rowid+1;
70 SELECT * FROM log
71 }
72 } {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400}
73 do_test triggerD-1.4 {
74 db eval {
75 DELETE FROM log;
76 DELETE FROM t1;
77 SELECT * FROM log
78 }
79 } {r5 101 200 300 400 r6 101 200 300 400}
80  
81 # Triggers on tables where the table does not have ordinary columns named
82 # rowid, oid, and _rowid_.
83 #
84 do_test triggerD-2.1 {
85 db eval {
86 DROP TABLE t1;
87 CREATE TABLE t1(w,x,y,z);
88 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
89 INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
90 END;
91 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
92 INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
93 END;
94 CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
95 INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
96 INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
97 END;
98 CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
99 INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
100 INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
101 END;
102 CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
103 INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
104 END;
105 CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
106 INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
107 END;
108 }
109 } {}
110 do_test triggerD-2.2 {
111 db eval {
112 DELETE FROM log;
113 INSERT INTO t1 VALUES(100,200,300,400);
114 SELECT * FROM log;
115 }
116 } {r1 -1 -1 -1 200 r2 1 1 1 200}
117 do_test triggerD-2.3 {
118 db eval {
119 DELETE FROM log;
120 UPDATE t1 SET x=x+1;
121 SELECT * FROM log
122 }
123 } {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201}
124 do_test triggerD-2.4 {
125 db eval {
126 DELETE FROM log;
127 DELETE FROM t1;
128 SELECT * FROM log
129 }
130 } {r5 1 1 1 201 r6 1 1 1 201}
131  
132  
133 ###########################################################################
134 #
135 # Ticket [985771e1161200ae5eac3162686ea6711c035d08]:
136 #
137 # When both a main database table and a TEMP table have the same name,
138 # and a main database trigge is created on the main table, the trigger
139 # is incorrectly bound to the TEMP table. For example:
140 #
141 # CREATE TABLE t1(x);
142 # CREATE TEMP TABLE t1(x);
143 # CREATE TABLE t2(z);
144 # CREATE TRIGGER main.r1 AFTER INSERT ON t1 BEGIN
145 # INSERT INTO t2 VALUES(10000 + new.x);
146 # END;
147 # INSERT INTO main.t1 VALUES(3);
148 # INSERT INTO temp.t1 VALUES(4);
149 # SELECT * FROM t2;
150 #
151 # The r1 trigger fires when the value 4 is inserted into the temp.t1
152 # table, rather than when value 3 is inserted into main.t1.
153 #
154 do_test triggerD-3.1 {
155 db eval {
156 CREATE TABLE t300(x);
157 CREATE TEMP TABLE t300(x);
158 CREATE TABLE t301(y);
159 CREATE TRIGGER main.r300 AFTER INSERT ON t300 BEGIN
160 INSERT INTO t301 VALUES(10000 + new.x);
161 END;
162 INSERT INTO main.t300 VALUES(3);
163 INSERT INTO temp.t300 VALUES(4);
164 SELECT * FROM t301;
165 }
166 } {10003}
167 do_test triggerD-3.2 {
168 db eval {
169 DELETE FROM t301;
170 CREATE TRIGGER temp.r301 AFTER INSERT ON t300 BEGIN
171 INSERT INTO t301 VALUES(20000 + new.x);
172 END;
173 INSERT INTO main.t300 VALUES(3);
174 INSERT INTO temp.t300 VALUES(4);
175 SELECT * FROM t301;
176 }
177 } {10003 20004}
178  
179  
180 #############################################################################
181 #
182 # Ticket [d6ddba6706353915ceedc56b4e3e72ecb4d77ba4]
183 #
184 # The following syntax really should not be allowed:
185 #
186 # CREATE TRIGGER xyz.trig BEFORE UPDATE ON xyz.tab BEGIN ...
187 #
188 # But a long-standing bug does allow it. And the "xyz.tab" slips into
189 # the sqlite_master table. We cannot fix the bug simply by disallowing
190 # "xyz.tab" since that could break legacy applications. We have to
191 # fix the system so that the "xyz." on "xyz.tab" is ignored.
192 # Verify that this is the case.
193 #
194 do_test triggerD-4.1 {
195 db close
196 file delete -force test.db test2.db
197 sqlite3 db test.db
198 db eval {
199 CREATE TABLE t1(x);
200 ATTACH 'test2.db' AS db2;
201 CREATE TABLE db2.t2(y);
202 CREATE TABLE db2.log(z);
203 CREATE TRIGGER db2.trig AFTER INSERT ON db2.t2 BEGIN
204 INSERT INTO log(z) VALUES(new.y);
205 END;
206 INSERT INTO t2 VALUES(123);
207 SELECT * FROM log;
208 }
209 } {123}
210 do_test triggerD-4.2 {
211 sqlite3 db2 test2.db
212 db2 eval {
213 INSERT INTO t2 VALUES(234);
214 SELECT * FROM log;
215 }
216 } {123 234}
217 db2 close
218  
219 finish_test