wasCSharpSQLite – Blame information for rev 1

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