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