Annotation of embedaddon/sqlite3/test/e_dropview.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_dropview.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test