000001 # 2010 November 30
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file implements tests to verify that the "testable statements" in
000013 # the lang_dropview.html document are correct.
000014 #
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018 set ::testprefix e_dropview
000019
000020 proc dropview_reopen_db {} {
000021 db close
000022 forcedelete test.db test.db2
000023 sqlite3 db test.db
000024
000025 db eval {
000026 ATTACH 'test.db2' AS aux;
000027 CREATE TABLE t1(a, b);
000028 INSERT INTO t1 VALUES('a main', 'b main');
000029 CREATE VIEW v1 AS SELECT * FROM t1;
000030 CREATE VIEW v2 AS SELECT * FROM t1;
000031
000032 CREATE TEMP TABLE t1(a, b);
000033 INSERT INTO temp.t1 VALUES('a temp', 'b temp');
000034 CREATE VIEW temp.v1 AS SELECT * FROM t1;
000035
000036 CREATE TABLE aux.t1(a, b);
000037 INSERT INTO aux.t1 VALUES('a aux', 'b aux');
000038 CREATE VIEW aux.v1 AS SELECT * FROM t1;
000039 CREATE VIEW aux.v2 AS SELECT * FROM t1;
000040 CREATE VIEW aux.v3 AS SELECT * FROM t1;
000041 }
000042 }
000043
000044 proc list_all_views {{db db}} {
000045 set res [list]
000046 $db eval { PRAGMA database_list } {
000047 set tbl "$name.sqlite_master"
000048 if {$name == "temp"} { set tbl temp.sqlite_master }
000049
000050 set sql "SELECT '$name.' || name FROM $tbl WHERE type = 'view'"
000051 lappend res {*}[$db eval $sql]
000052 }
000053 set res
000054 }
000055
000056 proc list_all_data {{db db}} {
000057 set res [list]
000058 $db eval { PRAGMA database_list } {
000059 set tbl "$name.sqlite_master"
000060 if {$name == "temp"} { set tbl sqlite_temp_master }
000061
000062 db eval "SELECT '$name.' || name AS x FROM $tbl WHERE type = 'table'" {
000063 lappend res [list $x [db eval "SELECT * FROM $x"]]
000064 }
000065 }
000066 set res
000067 }
000068
000069 proc do_dropview_tests {nm args} {
000070 uplevel do_select_tests $nm $args
000071 }
000072
000073 # -- syntax diagram drop-view-stmt
000074 #
000075 # All paths in the syntax diagram for DROP VIEW are tested by tests 1.*.
000076 #
000077 do_dropview_tests 1 -repair {
000078 dropview_reopen_db
000079 } -tclquery {
000080 list_all_views
000081 } {
000082 1 "DROP VIEW v1" {main.v1 main.v2 aux.v1 aux.v2 aux.v3}
000083 2 "DROP VIEW v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
000084 3 "DROP VIEW main.v1" {main.v2 temp.v1 aux.v1 aux.v2 aux.v3}
000085 4 "DROP VIEW main.v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
000086 5 "DROP VIEW IF EXISTS v1" {main.v1 main.v2 aux.v1 aux.v2 aux.v3}
000087 6 "DROP VIEW IF EXISTS v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
000088 7 "DROP VIEW IF EXISTS main.v1" {main.v2 temp.v1 aux.v1 aux.v2 aux.v3}
000089 8 "DROP VIEW IF EXISTS main.v2" {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
000090 }
000091
000092 # EVIDENCE-OF: R-27002-52307 The DROP VIEW statement removes a view
000093 # created by the CREATE VIEW statement.
000094 #
000095 dropview_reopen_db
000096 do_execsql_test 2.1 {
000097 CREATE VIEW "new view" AS SELECT * FROM t1 AS x, t1 AS y;
000098 SELECT * FROM "new view";
000099 } {{a main} {b main} {a main} {b main}}
000100 do_execsql_test 2.2 {;
000101 SELECT * FROM sqlite_master WHERE name = 'new view';
000102 } {
000103 view {new view} {new view} 0
000104 {CREATE VIEW "new view" AS SELECT * FROM t1 AS x, t1 AS y}
000105 }
000106 do_execsql_test 2.3 {
000107 DROP VIEW "new view";
000108 SELECT * FROM sqlite_master WHERE name = 'new view';
000109 } {}
000110 do_catchsql_test 2.4 {
000111 SELECT * FROM "new view"
000112 } {1 {no such table: new view}}
000113
000114 # EVIDENCE-OF: R-00359-41639 The view definition is removed from the
000115 # database schema, but no actual data in the underlying base tables is
000116 # modified.
000117 #
000118 # For each view in the database, check that it can be queried. Then drop
000119 # it. Check that it can no longer be queried and is no longer listed
000120 # in any schema table. Then check that the contents of the db tables have
000121 # not changed
000122 #
000123 set databasedata [list_all_data]
000124
000125 do_execsql_test 3.1.0 { SELECT * FROM temp.v1 } {{a temp} {b temp}}
000126 do_execsql_test 3.1.1 { DROP VIEW temp.v1 } {}
000127 do_catchsql_test 3.1.2 { SELECT * FROM temp.v1 } {1 {no such table: temp.v1}}
000128 do_test 3.1.3 { list_all_views } {main.v1 main.v2 aux.v1 aux.v2 aux.v3}
000129 do_test 3.1.4 { string compare [list_all_data] $databasedata } 0
000130
000131 do_execsql_test 3.2.0 { SELECT * FROM v1 } {{a main} {b main}}
000132 do_execsql_test 3.2.1 { DROP VIEW v1 } {}
000133 do_catchsql_test 3.2.2 { SELECT * FROM main.v1 } {1 {no such table: main.v1}}
000134 do_test 3.2.3 { list_all_views } {main.v2 aux.v1 aux.v2 aux.v3}
000135 do_test 3.2.4 { string compare [list_all_data] $databasedata } 0
000136
000137 do_execsql_test 3.3.0 { SELECT * FROM v2 } {{a main} {b main}}
000138 do_execsql_test 3.3.1 { DROP VIEW v2 } {}
000139 do_catchsql_test 3.3.2 { SELECT * FROM main.v2 } {1 {no such table: main.v2}}
000140 do_test 3.3.3 { list_all_views } {aux.v1 aux.v2 aux.v3}
000141 do_test 3.3.4 { string compare [list_all_data] $databasedata } 0
000142
000143 do_execsql_test 3.4.0 { SELECT * FROM v1 } {{a aux} {b aux}}
000144 do_execsql_test 3.4.1 { DROP VIEW v1 } {}
000145 do_catchsql_test 3.4.2 { SELECT * FROM v1 } {1 {no such table: v1}}
000146 do_test 3.4.3 { list_all_views } {aux.v2 aux.v3}
000147 do_test 3.4.4 { string compare [list_all_data] $databasedata } 0
000148
000149 do_execsql_test 3.5.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}}
000150 do_execsql_test 3.5.1 { DROP VIEW aux.v2 } {}
000151 do_catchsql_test 3.5.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}}
000152 do_test 3.5.3 { list_all_views } {aux.v3}
000153 do_test 3.5.4 { string compare [list_all_data] $databasedata } 0
000154
000155 do_execsql_test 3.6.0 { SELECT * FROM v3 } {{a aux} {b aux}}
000156 do_execsql_test 3.6.1 { DROP VIEW v3 } {}
000157 do_catchsql_test 3.6.2 { SELECT * FROM v3 } {1 {no such table: v3}}
000158 do_test 3.6.3 { list_all_views } {}
000159 do_test 3.6.4 { string compare [list_all_data] $databasedata } 0
000160
000161 # EVIDENCE-OF: R-25558-37487 If the specified view cannot be found and
000162 # the IF EXISTS clause is not present, it is an error.
000163 #
000164 do_dropview_tests 4 -repair {
000165 dropview_reopen_db
000166 } -errorformat {
000167 no such view: %s
000168 } {
000169 1 "DROP VIEW xx" xx
000170 2 "DROP VIEW main.xx" main.xx
000171 3 "DROP VIEW temp.v2" temp.v2
000172 }
000173
000174 # EVIDENCE-OF: R-07490-32536 If the specified view cannot be found and
000175 # an IF EXISTS clause is present in the DROP VIEW statement, then the
000176 # statement is a no-op.
000177 #
000178 do_dropview_tests 5 -repair {
000179 dropview_reopen_db
000180 } -tclquery {
000181 list_all_views
000182 #expr {[list_all_views] == "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3"}
000183 } {
000184 1 "DROP VIEW IF EXISTS xx" "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3"
000185 2 "DROP VIEW IF EXISTS main.xx" "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3"
000186 3 "DROP VIEW IF EXISTS temp.v2" "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3"
000187 }
000188
000189
000190
000191
000192 finish_test