wasCSharpSQLite – Blame information for rev 1
?pathlinks?
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 |