wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 November 30 |
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_dropview.html document are correct. |
||
14 | # |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | set ::testprefix e_dropview |
||
19 | |||
20 | proc dropview_reopen_db {} { |
||
21 | db close |
||
22 | forcedelete test.db test.db2 |
||
23 | sqlite3 db test.db |
||
24 | |||
25 | db eval { |
||
26 | ATTACH 'test.db2' AS aux; |
||
27 | CREATE TABLE t1(a, b); |
||
28 | INSERT INTO t1 VALUES('a main', 'b main'); |
||
29 | CREATE VIEW v1 AS SELECT * FROM t1; |
||
30 | CREATE VIEW v2 AS SELECT * FROM t1; |
||
31 | |||
32 | CREATE TEMP TABLE t1(a, b); |
||
33 | INSERT INTO temp.t1 VALUES('a temp', 'b temp'); |
||
34 | CREATE VIEW temp.v1 AS SELECT * FROM t1; |
||
35 | |||
36 | CREATE TABLE aux.t1(a, b); |
||
37 | INSERT INTO aux.t1 VALUES('a aux', 'b aux'); |
||
38 | CREATE VIEW aux.v1 AS SELECT * FROM t1; |
||
39 | CREATE VIEW aux.v2 AS SELECT * FROM t1; |
||
40 | CREATE VIEW aux.v3 AS SELECT * FROM t1; |
||
41 | } |
||
42 | } |
||
43 | |||
44 | proc list_all_views {{db db}} { |
||
45 | set res [list] |
||
46 | $db eval { PRAGMA database_list } { |
||
47 | set tbl "$name.sqlite_master" |
||
48 | if {$name == "temp"} { set tbl sqlite_temp_master } |
||
49 | |||
50 | set sql "SELECT '$name.' || name FROM $tbl WHERE type = 'view'" |
||
51 | lappend res {*}[$db eval $sql] |
||
52 | } |
||
53 | set res |
||
54 | } |
||
55 | |||
56 | proc list_all_data {{db db}} { |
||
57 | set res [list] |
||
58 | $db eval { PRAGMA database_list } { |
||
59 | set tbl "$name.sqlite_master" |
||
60 | if {$name == "temp"} { set tbl sqlite_temp_master } |
||
61 | |||
62 | db eval "SELECT '$name.' || name AS x FROM $tbl WHERE type = 'table'" { |
||
63 | lappend res [list $x [db eval "SELECT * FROM $x"]] |
||
64 | } |
||
65 | } |
||
66 | set res |
||
67 | } |
||
68 | |||
69 | proc do_dropview_tests {nm args} { |
||
70 | uplevel do_select_tests $nm $args |
||
71 | } |
||
72 | |||
73 | # EVIDENCE-OF: R-21739-51207 -- syntax diagram drop-view-stmt |
||
74 | # |
||
75 | # All paths in the syntax diagram for DROP VIEW are tested by tests 1.*. |
||
76 | # |
||
77 | do_dropview_tests 1 -repair { |
||
78 | dropview_reopen_db |
||
79 | } -tclquery { |
||
80 | list_all_views |
||
81 | } { |
||
82 | 1 "DROP VIEW v1" {main.v1 main.v2 aux.v1 aux.v2 aux.v3} |
||
83 | 2 "DROP VIEW v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3} |
||
84 | 3 "DROP VIEW main.v1" {main.v2 temp.v1 aux.v1 aux.v2 aux.v3} |
||
85 | 4 "DROP VIEW main.v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3} |
||
86 | 5 "DROP VIEW IF EXISTS v1" {main.v1 main.v2 aux.v1 aux.v2 aux.v3} |
||
87 | 6 "DROP VIEW IF EXISTS v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3} |
||
88 | 7 "DROP VIEW IF EXISTS main.v1" {main.v2 temp.v1 aux.v1 aux.v2 aux.v3} |
||
89 | 8 "DROP VIEW IF EXISTS main.v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3} |
||
90 | } |
||
91 | |||
92 | # EVIDENCE-OF: R-27002-52307 The DROP VIEW statement removes a view |
||
93 | # created by the CREATE VIEW statement. |
||
94 | # |
||
95 | dropview_reopen_db |
||
96 | do_execsql_test 2.1 { |
||
97 | CREATE VIEW "new view" AS SELECT * FROM t1 AS x, t1 AS y; |
||
98 | SELECT * FROM "new view"; |
||
99 | } {{a main} {b main} {a main} {b main}} |
||
100 | do_execsql_test 2.2 {; |
||
101 | SELECT * FROM sqlite_master WHERE name = 'new view'; |
||
102 | } { |
||
103 | view {new view} {new view} 0 |
||
104 | {CREATE VIEW "new view" AS SELECT * FROM t1 AS x, t1 AS y} |
||
105 | } |
||
106 | do_execsql_test 2.3 { |
||
107 | DROP VIEW "new view"; |
||
108 | SELECT * FROM sqlite_master WHERE name = 'new view'; |
||
109 | } {} |
||
110 | do_catchsql_test 2.4 { |
||
111 | SELECT * FROM "new view" |
||
112 | } {1 {no such table: new view}} |
||
113 | |||
114 | # EVIDENCE-OF: R-00359-41639 The view definition is removed from the |
||
115 | # database schema, but no actual data in the underlying base tables is |
||
116 | # modified. |
||
117 | # |
||
118 | # For each view in the database, check that it can be queried. Then drop |
||
119 | # it. Check that it can no longer be queried and is no longer listed |
||
120 | # in any schema table. Then check that the contents of the db tables have |
||
121 | # not changed |
||
122 | # |
||
123 | set databasedata [list_all_data] |
||
124 | |||
125 | do_execsql_test 3.1.0 { SELECT * FROM temp.v1 } {{a temp} {b temp}} |
||
126 | do_execsql_test 3.1.1 { DROP VIEW temp.v1 } {} |
||
127 | do_catchsql_test 3.1.2 { SELECT * FROM temp.v1 } {1 {no such table: temp.v1}} |
||
128 | do_test 3.1.3 { list_all_views } {main.v1 main.v2 aux.v1 aux.v2 aux.v3} |
||
129 | do_test 3.1.4 { list_all_data } $databasedata |
||
130 | |||
131 | do_execsql_test 3.2.0 { SELECT * FROM v1 } {{a main} {b main}} |
||
132 | do_execsql_test 3.2.1 { DROP VIEW v1 } {} |
||
133 | do_catchsql_test 3.2.2 { SELECT * FROM main.v1 } {1 {no such table: main.v1}} |
||
134 | do_test 3.2.3 { list_all_views } {main.v2 aux.v1 aux.v2 aux.v3} |
||
135 | do_test 3.2.4 { list_all_data } $databasedata |
||
136 | |||
137 | do_execsql_test 3.3.0 { SELECT * FROM v2 } {{a main} {b main}} |
||
138 | do_execsql_test 3.3.1 { DROP VIEW v2 } {} |
||
139 | do_catchsql_test 3.3.2 { SELECT * FROM main.v2 } {1 {no such table: main.v2}} |
||
140 | do_test 3.3.3 { list_all_views } {aux.v1 aux.v2 aux.v3} |
||
141 | do_test 3.3.4 { list_all_data } $databasedata |
||
142 | |||
143 | do_execsql_test 3.4.0 { SELECT * FROM v1 } {{a aux} {b aux}} |
||
144 | do_execsql_test 3.4.1 { DROP VIEW v1 } {} |
||
145 | do_catchsql_test 3.4.2 { SELECT * FROM v1 } {1 {no such table: v1}} |
||
146 | do_test 3.4.3 { list_all_views } {aux.v2 aux.v3} |
||
147 | do_test 3.4.4 { list_all_data } $databasedata |
||
148 | |||
149 | do_execsql_test 3.4.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}} |
||
150 | do_execsql_test 3.4.1 { DROP VIEW aux.v2 } {} |
||
151 | do_catchsql_test 3.4.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}} |
||
152 | do_test 3.4.3 { list_all_views } {aux.v3} |
||
153 | do_test 3.4.4 { list_all_data } $databasedata |
||
154 | |||
155 | do_execsql_test 3.5.0 { SELECT * FROM v3 } {{a aux} {b aux}} |
||
156 | do_execsql_test 3.5.1 { DROP VIEW v3 } {} |
||
157 | do_catchsql_test 3.5.2 { SELECT * FROM v3 } {1 {no such table: v3}} |
||
158 | do_test 3.5.3 { list_all_views } {} |
||
159 | do_test 3.5.4 { list_all_data } $databasedata |
||
160 | |||
161 | # EVIDENCE-OF: R-25558-37487 If the specified view cannot be found and |
||
162 | # the IF EXISTS clause is not present, it is an error. |
||
163 | # |
||
164 | do_dropview_tests 4 -repair { |
||
165 | dropview_reopen_db |
||
166 | } -errorformat { |
||
167 | no such view: %s |
||
168 | } { |
||
169 | 1 "DROP VIEW xx" xx |
||
170 | 2 "DROP VIEW main.xx" main.xx |
||
171 | 3 "DROP VIEW temp.v2" temp.v2 |
||
172 | } |
||
173 | |||
174 | # EVIDENCE-OF: R-07490-32536 If the specified view cannot be found and |
||
175 | # an IF EXISTS clause is present in the DROP VIEW statement, then the |
||
176 | # statement is a no-op. |
||
177 | # |
||
178 | do_dropview_tests 5 -repair { |
||
179 | dropview_reopen_db |
||
180 | } -tclquery { |
||
181 | list_all_views |
||
182 | expr {[list_all_views] == "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3"} |
||
183 | } { |
||
184 | 1 "DROP VIEW IF EXISTS xx" 1 |
||
185 | 2 "DROP VIEW IF EXISTS main.xx" 1 |
||
186 | 3 "DROP VIEW IF EXISTS temp.v2" 1 |
||
187 | } |
||
188 | |||
189 | |||
190 | |||
191 | |||
192 | finish_test |