wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2008 April 15
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. Specifically,
12 # it tests updating tables with constraints within a trigger. Ticket #3055.
13 #
14  
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 ifcapable {!trigger} {
18 finish_test
19 return
20 }
21  
22 # Create test tables with constraints.
23 #
24 do_test triggerB-1.1 {
25 execsql {
26 CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
27 INSERT INTO x(y) VALUES(1);
28 INSERT INTO x(y) VALUES(1);
29 CREATE TEMP VIEW vx AS SELECT x, y, 0 AS yy FROM x;
30 CREATE TEMP TRIGGER tx INSTEAD OF UPDATE OF y ON vx
31 BEGIN
32 UPDATE x SET y = new.y WHERE x = new.x;
33 END;
34 SELECT * FROM vx;
35 }
36 } {1 1 0 2 1 0}
37 do_test triggerB-1.2 {
38 execsql {
39 UPDATE vx SET y = yy;
40 SELECT * FROM vx;
41 }
42 } {1 0 0 2 0 0}
43  
44 # Added 2008-08-22:
45 #
46 # Name resolution within triggers.
47 #
48 do_test triggerB-2.1 {
49 catchsql {
50 CREATE TRIGGER ty AFTER INSERT ON x BEGIN
51 SELECT wen.x; -- Unrecognized name
52 END;
53 INSERT INTO x VALUES(1,2);
54 }
55 } {1 {no such column: wen.x}}
56 do_test triggerB-2.2 {
57 catchsql {
58 CREATE TRIGGER tz AFTER UPDATE ON x BEGIN
59 SELECT dlo.x; -- Unrecognized name
60 END;
61 UPDATE x SET y=y+1;
62 }
63 } {1 {no such column: dlo.x}}
64  
65 do_test triggerB-2.3 {
66 execsql {
67 CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
68 INSERT INTO t2 VALUES(1,2);
69 CREATE TABLE changes(x,y);
70 CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
71 INSERT INTO changes VALUES(new.a, new.b);
72 END;
73 }
74 execsql {
75 UPDATE t2 SET a=a+10;
76 SELECT * FROM changes;
77 }
78 } {11 2}
79 do_test triggerB-2.4 {
80 execsql {
81 CREATE TRIGGER r2t2 AFTER DELETE ON t2 BEGIN
82 INSERT INTO changes VALUES(old.a, old.c);
83 END;
84 }
85 catchsql {
86 DELETE FROM t2;
87 }
88 } {1 {no such column: old.c}}
89  
90 # Triggers maintain a mask of columns from the invoking table that are
91 # used in the trigger body as NEW.column or OLD.column. That mask is then
92 # used to reduce the amount of information that needs to be loaded into
93 # the NEW and OLD pseudo-tables at run-time.
94 #
95 # These tests cases check the logic for when there are many columns - more
96 # than will fit in a bitmask.
97 #
98 do_test triggerB-3.1 {
99 execsql {
100 CREATE TABLE t3(
101 c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
102 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
103 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
104 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
105 c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
106 c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
107 c60, c61, c62, c63, c64, c65
108 );
109 CREATE TABLE t3_changes(colnum, oldval, newval);
110 INSERT INTO t3 VALUES(
111 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
112 'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
113 'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
114 'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
115 'a40','a41','a42','a43','a44','a45','a46','a47','a48','a49',
116 'a50','a51','a52','a53','a54','a55','a56','a57','a58','a59',
117 'a60','a61','a62','a63','a64','a65'
118 );
119 }
120 for {set i 0} {$i<=65} {incr i} {
121 set sql [subst {
122 CREATE TRIGGER t3c$i AFTER UPDATE ON t3
123 WHEN old.c$i!=new.c$i BEGIN
124 INSERT INTO t3_changes VALUES($i, old.c$i, new.c$i);
125 END
126 }]
127 db eval $sql
128 }
129 execsql {
130 SELECT * FROM t3_changes
131 }
132 } {}
133 for {set i 0} {$i<=64} {incr i} {
134 do_test triggerB-3.2.$i.1 [subst {
135 execsql {
136 UPDATE t3 SET c$i='b$i';
137 SELECT * FROM t3_changes ORDER BY rowid DESC LIMIT 1;
138 }
139 }] [subst {$i a$i b$i}]
140 do_test triggerB-3.2.$i.2 [subst {
141 execsql {
142 SELECT count(*) FROM t3_changes
143 }
144 }] [expr {$i+1}]
145 do_test triggerB-3.2.$i.2 [subst {
146 execsql {
147 SELECT * FROM t3_changes WHERE colnum=$i
148 }
149 }] [subst {$i a$i b$i}]
150 }
151  
152  
153 finish_test