Return to e_dropview.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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 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: 
   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: # EVIDENCE-OF: R-53136-36436 -- 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 { list_all_data  } $databasedata
  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 { list_all_data  } $databasedata
  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 { list_all_data  } $databasedata
  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 { list_all_data  } $databasedata
  148: 
  149: do_execsql_test  3.4.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}}
  150: do_execsql_test  3.4.1 { DROP VIEW aux.v2 } {}
  151: do_catchsql_test 3.4.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}}
  152: do_test          3.4.3 { list_all_views } {aux.v3}
  153: do_test          3.4.4 { list_all_data  } $databasedata
  154: 
  155: do_execsql_test  3.5.0 { SELECT * FROM v3 } {{a aux} {b aux}}
  156: do_execsql_test  3.5.1 { DROP VIEW v3 } {}
  157: do_catchsql_test 3.5.2 { SELECT * FROM v3 } {1 {no such table: v3}}
  158: do_test          3.5.3 { list_all_views } {}
  159: do_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: #
  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"       1
  185:   2    "DROP VIEW IF EXISTS main.xx"  1
  186:   3    "DROP VIEW IF EXISTS temp.v2"  1
  187: }
  188: 
  189: 
  190: 
  191: 
  192: finish_test