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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::testprefix e_dropview 19 20proc 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 44proc 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 56proc 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 69proc 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# 77do_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# 95dropview_reopen_db 96do_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}} 100do_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} 106do_execsql_test 2.3 { 107 DROP VIEW "new view"; 108 SELECT * FROM sqlite_master WHERE name = 'new view'; 109} {} 110do_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# 123set databasedata [list_all_data] 124 125do_execsql_test 3.1.0 { SELECT * FROM temp.v1 } {{a temp} {b temp}} 126do_execsql_test 3.1.1 { DROP VIEW temp.v1 } {} 127do_catchsql_test 3.1.2 { SELECT * FROM temp.v1 } {1 {no such table: temp.v1}} 128do_test 3.1.3 { list_all_views } {main.v1 main.v2 aux.v1 aux.v2 aux.v3} 129do_test 3.1.4 { list_all_data } $databasedata 130 131do_execsql_test 3.2.0 { SELECT * FROM v1 } {{a main} {b main}} 132do_execsql_test 3.2.1 { DROP VIEW v1 } {} 133do_catchsql_test 3.2.2 { SELECT * FROM main.v1 } {1 {no such table: main.v1}} 134do_test 3.2.3 { list_all_views } {main.v2 aux.v1 aux.v2 aux.v3} 135do_test 3.2.4 { list_all_data } $databasedata 136 137do_execsql_test 3.3.0 { SELECT * FROM v2 } {{a main} {b main}} 138do_execsql_test 3.3.1 { DROP VIEW v2 } {} 139do_catchsql_test 3.3.2 { SELECT * FROM main.v2 } {1 {no such table: main.v2}} 140do_test 3.3.3 { list_all_views } {aux.v1 aux.v2 aux.v3} 141do_test 3.3.4 { list_all_data } $databasedata 142 143do_execsql_test 3.4.0 { SELECT * FROM v1 } {{a aux} {b aux}} 144do_execsql_test 3.4.1 { DROP VIEW v1 } {} 145do_catchsql_test 3.4.2 { SELECT * FROM v1 } {1 {no such table: v1}} 146do_test 3.4.3 { list_all_views } {aux.v2 aux.v3} 147do_test 3.4.4 { list_all_data } $databasedata 148 149do_execsql_test 3.4.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}} 150do_execsql_test 3.4.1 { DROP VIEW aux.v2 } {} 151do_catchsql_test 3.4.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}} 152do_test 3.4.3 { list_all_views } {aux.v3} 153do_test 3.4.4 { list_all_data } $databasedata 154 155do_execsql_test 3.5.0 { SELECT * FROM v3 } {{a aux} {b aux}} 156do_execsql_test 3.5.1 { DROP VIEW v3 } {} 157do_catchsql_test 3.5.2 { SELECT * FROM v3 } {1 {no such table: v3}} 158do_test 3.5.3 { list_all_views } {} 159do_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# 164do_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# 178do_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 192finish_test 193