AryaWu/sqlite

1# 2010 November 302#3# The author disclaims copyright to this source code.  In place of4# 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.tcl18set ::testprefix e_dropview19 20proc dropview_reopen_db {} {21  db close22  forcedelete test.db test.db223  sqlite3 db test.db24 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 temp.sqlite_master }49 50    set sql "SELECT '$name.' || name FROM $tbl WHERE type = 'view'"51    lappend res {*}[$db eval $sql]52  }53  set res54}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 res67}68 69proc do_dropview_tests {nm args} {70  uplevel do_select_tests $nm $args71}72 73# -- syntax diagram drop-view-stmt74#75# All paths in the syntax diagram for DROP VIEW are tested by tests 1.*.76#77do_dropview_tests 1 -repair {78  dropview_reopen_db79} -tclquery {80  list_all_views81} {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 view93# created by the CREATE VIEW statement.94#95dropview_reopen_db96do_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 the115# database schema, but no actual data in the underlying base tables is116# modified.117#118#     For each view in the database, check that it can be queried. Then drop119#     it. Check that it can no longer be queried and is no longer listed120#     in any schema table. Then check that the contents of the db tables have 121#     not changed122#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 { string compare [list_all_data] $databasedata } 0130 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 { string compare [list_all_data] $databasedata } 0136 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 { string compare [list_all_data] $databasedata } 0142 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 { string compare [list_all_data] $databasedata } 0148 149do_execsql_test  3.5.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}}150do_execsql_test  3.5.1 { DROP VIEW aux.v2 } {}151do_catchsql_test 3.5.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}}152do_test          3.5.3 { list_all_views } {aux.v3}153do_test          3.5.4 { string compare [list_all_data] $databasedata } 0154 155do_execsql_test  3.6.0 { SELECT * FROM v3 } {{a aux} {b aux}}156do_execsql_test  3.6.1 { DROP VIEW v3 } {}157do_catchsql_test 3.6.2 { SELECT * FROM v3 } {1 {no such table: v3}}158do_test          3.6.3 { list_all_views } {}159do_test          3.6.4 { string compare [list_all_data] $databasedata } 0160 161# EVIDENCE-OF: R-25558-37487 If the specified view cannot be found and162# 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: %s168} {169  1   "DROP VIEW xx"                  xx170  2   "DROP VIEW main.xx"             main.xx171  3   "DROP VIEW temp.v2"             temp.v2172}173 174# EVIDENCE-OF: R-07490-32536 If the specified view cannot be found and175# an IF EXISTS clause is present in the DROP VIEW statement, then the176# statement is a no-op.177#178do_dropview_tests 5 -repair {179  dropview_reopen_db180} -tclquery {181  list_all_views182  #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 192finish_test193