| 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 temp.sqlite_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 { string compare [list_all_data] $databasedata } 0 |
| 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 { string compare [list_all_data] $databasedata } 0 |
| 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 { string compare [list_all_data] $databasedata } 0 |
| 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 { string compare [list_all_data] $databasedata } 0 |
| 148 |
|
| 149 |
do_execsql_test 3.5.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}} |
| 150 |
do_execsql_test 3.5.1 { DROP VIEW aux.v2 } {} |
| 151 |
do_catchsql_test 3.5.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}} |
| 152 |
do_test 3.5.3 { list_all_views } {aux.v3} |
| 153 |
do_test 3.5.4 { string compare [list_all_data] $databasedata } 0 |
| 154 |
|
| 155 |
do_execsql_test 3.6.0 { SELECT * FROM v3 } {{a aux} {b aux}} |
| 156 |
do_execsql_test 3.6.1 { DROP VIEW v3 } {} |
| 157 |
do_catchsql_test 3.6.2 { SELECT * FROM v3 } {1 {no such table: v3}} |
| 158 |
do_test 3.6.3 { list_all_views } {} |
| 159 |
do_test 3.6.4 { string compare [list_all_data] $databasedata } 0 |
| 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" "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3" |
| 185 |
2 "DROP VIEW IF EXISTS main.xx" "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3" |
| 186 |
3 "DROP VIEW IF EXISTS temp.v2" "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3" |
| 187 |
} |
| 188 |
|
| 189 |
|
| 190 |
|
| 191 |
|
| 192 |
finish_test |