wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 November 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 | # This file implements tests to verify that the "testable statements" in |
||
13 | # the lang_droptrigger.html document are correct. |
||
14 | # |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | set ::testprefix e_droptrigger |
||
19 | |||
20 | ifcapable !trigger { finish_test ; return } |
||
21 | |||
22 | proc do_droptrigger_tests {nm args} { |
||
23 | uplevel do_select_tests [list e_createtable-$nm] $args |
||
24 | } |
||
25 | |||
26 | proc list_all_triggers {{db db}} { |
||
27 | set res [list] |
||
28 | $db eval { PRAGMA database_list } { |
||
29 | if {$name == "temp"} { |
||
30 | set tbl sqlite_temp_master |
||
31 | } else { |
||
32 | set tbl "$name.sqlite_master" |
||
33 | } |
||
34 | lappend res {*}[ |
||
35 | db eval "SELECT '$name.' || name FROM $tbl WHERE type = 'trigger'" |
||
36 | ] |
||
37 | } |
||
38 | set res |
||
39 | } |
||
40 | |||
41 | |||
42 | proc droptrigger_reopen_db {{event INSERT}} { |
||
43 | db close |
||
44 | forcedelete test.db test.db2 |
||
45 | sqlite3 db test.db |
||
46 | |||
47 | set ::triggers_fired [list] |
||
48 | proc r {x} { lappend ::triggers_fired $x } |
||
49 | db func r r |
||
50 | |||
51 | db eval " |
||
52 | ATTACH 'test.db2' AS aux; |
||
53 | |||
54 | CREATE TEMP TABLE t1(a, b); |
||
55 | INSERT INTO t1 VALUES('a', 'b'); |
||
56 | CREATE TRIGGER tr1 AFTER $event ON t1 BEGIN SELECT r('temp.tr1') ; END; |
||
57 | |||
58 | CREATE TABLE t2(a, b); |
||
59 | INSERT INTO t2 VALUES('a', 'b'); |
||
60 | CREATE TRIGGER tr1 BEFORE $event ON t2 BEGIN SELECT r('main.tr1') ; END; |
||
61 | CREATE TRIGGER tr2 AFTER $event ON t2 BEGIN SELECT r('main.tr2') ; END; |
||
62 | |||
63 | CREATE TABLE aux.t3(a, b); |
||
64 | INSERT INTO t3 VALUES('a', 'b'); |
||
65 | CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END; |
||
66 | CREATE TRIGGER aux.tr2 AFTER $event ON t3 BEGIN SELECT r('aux.tr2') ; END; |
||
67 | CREATE TRIGGER aux.tr3 AFTER $event ON t3 BEGIN SELECT r('aux.tr3') ; END; |
||
68 | " |
||
69 | } |
||
70 | |||
71 | |||
72 | # EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt |
||
73 | # |
||
74 | do_droptrigger_tests 1.1 -repair { |
||
75 | droptrigger_reopen_db |
||
76 | } -tclquery { |
||
77 | list_all_triggers |
||
78 | } { |
||
79 | 1 "DROP TRIGGER main.tr1" |
||
80 | {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
||
81 | 2 "DROP TRIGGER IF EXISTS main.tr1" |
||
82 | {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
||
83 | 3 "DROP TRIGGER tr1" |
||
84 | {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
||
85 | 4 "DROP TRIGGER IF EXISTS tr1" |
||
86 | {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
||
87 | |||
88 | 5 "DROP TRIGGER aux.tr1" |
||
89 | {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3} |
||
90 | 6 "DROP TRIGGER IF EXISTS aux.tr1" |
||
91 | {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3} |
||
92 | |||
93 | 7 "DROP TRIGGER IF EXISTS aux.xxx" |
||
94 | {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
||
95 | 8 "DROP TRIGGER IF EXISTS aux.xxx" |
||
96 | {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
||
97 | } |
||
98 | |||
99 | # EVIDENCE-OF: R-61172-15671 The DROP TRIGGER statement removes a |
||
100 | # trigger created by the CREATE TRIGGER statement. |
||
101 | # |
||
102 | foreach {tn tbl droptrigger before after} { |
||
103 | 1 t1 "DROP TRIGGER tr1" {temp.tr1} {} |
||
104 | 2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} |
||
105 | 3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
||
106 | |||
107 | 4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} |
||
108 | 5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} |
||
109 | 6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
||
110 | |||
111 | 7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} |
||
112 | 8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} |
||
113 | 9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} |
||
114 | } { |
||
115 | |||
116 | do_test 2.$tn.1 { |
||
117 | droptrigger_reopen_db |
||
118 | execsql " INSERT INTO $tbl VALUES('1', '2') " |
||
119 | set ::triggers_fired |
||
120 | } $before |
||
121 | |||
122 | do_test 2.$tn.2 { |
||
123 | droptrigger_reopen_db |
||
124 | execsql $droptrigger |
||
125 | execsql " INSERT INTO $tbl VALUES('1', '2') " |
||
126 | set ::triggers_fired |
||
127 | } $after |
||
128 | } |
||
129 | |||
130 | # EVIDENCE-OF: R-50239-29811 Once removed, the trigger definition is no |
||
131 | # longer present in the sqlite_master (or sqlite_temp_master) table and |
||
132 | # is not fired by any subsequent INSERT, UPDATE or DELETE statements. |
||
133 | # |
||
134 | # Test cases e_droptrigger-1.* test the first part of this statement |
||
135 | # (that dropped triggers do not appear in the schema table), and tests |
||
136 | # droptrigger-2.* test that dropped triggers are not fired by INSERT |
||
137 | # statements. The following tests verify that they are not fired by |
||
138 | # UPDATE or DELETE statements. |
||
139 | # |
||
140 | foreach {tn tbl droptrigger before after} { |
||
141 | 1 t1 "DROP TRIGGER tr1" {temp.tr1} {} |
||
142 | 2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} |
||
143 | 3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
||
144 | |||
145 | 4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} |
||
146 | 5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} |
||
147 | 6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
||
148 | |||
149 | 7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} |
||
150 | 8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} |
||
151 | 9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} |
||
152 | } { |
||
153 | |||
154 | do_test 3.1.$tn.1 { |
||
155 | droptrigger_reopen_db UPDATE |
||
156 | execsql "UPDATE $tbl SET a = 'abc'" |
||
157 | set ::triggers_fired |
||
158 | } $before |
||
159 | |||
160 | do_test 3.1.$tn.2 { |
||
161 | droptrigger_reopen_db UPDATE |
||
162 | execsql $droptrigger |
||
163 | execsql "UPDATE $tbl SET a = 'abc'" |
||
164 | set ::triggers_fired |
||
165 | } $after |
||
166 | } |
||
167 | foreach {tn tbl droptrigger before after} { |
||
168 | 1 t1 "DROP TRIGGER tr1" {temp.tr1} {} |
||
169 | 2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} |
||
170 | 3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
||
171 | |||
172 | 4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} |
||
173 | 5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} |
||
174 | 6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} |
||
175 | |||
176 | 7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} |
||
177 | 8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} |
||
178 | 9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} |
||
179 | } { |
||
180 | |||
181 | do_test 3.2.$tn.1 { |
||
182 | droptrigger_reopen_db DELETE |
||
183 | execsql "DELETE FROM $tbl" |
||
184 | set ::triggers_fired |
||
185 | } $before |
||
186 | |||
187 | do_test 3.2.$tn.2 { |
||
188 | droptrigger_reopen_db DELETE |
||
189 | execsql $droptrigger |
||
190 | execsql "DELETE FROM $tbl" |
||
191 | set ::triggers_fired |
||
192 | } $after |
||
193 | } |
||
194 | |||
195 | # EVIDENCE-OF: R-37808-62273 Note that triggers are automatically |
||
196 | # dropped when the associated table is dropped. |
||
197 | # |
||
198 | do_test 4.1 { |
||
199 | droptrigger_reopen_db |
||
200 | list_all_triggers |
||
201 | } {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} |
||
202 | do_test 4.2 { |
||
203 | droptrigger_reopen_db |
||
204 | execsql "DROP TABLE t1" |
||
205 | list_all_triggers |
||
206 | } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
||
207 | do_test 4.3 { |
||
208 | droptrigger_reopen_db |
||
209 | execsql "DROP TABLE t1" |
||
210 | list_all_triggers |
||
211 | } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
||
212 | do_test 4.4 { |
||
213 | droptrigger_reopen_db |
||
214 | execsql "DROP TABLE t1" |
||
215 | list_all_triggers |
||
216 | } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} |
||
217 | |||
218 | finish_test |