| OLD | NEW |
|---|
| (Empty) |
| 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 # -- 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 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
