wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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