/ Check-in [6197822c]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test file e_dropview.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:6197822cc8310fd7e1d7151683833e8b39fe631a
User & Date: dan 2010-11-30 12:12:25
Context
2010-12-01
08:04
Avoid recursive calls to sqlite3VdbeMemRelease() when deleting VM frames used by trigger programs. check-in: 119ffe95 user: dan tags: trunk
2010-11-30
12:12
Add test file e_dropview.test. check-in: 6197822c user: dan tags: trunk
2010-11-29
18:36
Fix a warning in os_unix.c. check-in: ee8dc8c8 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_droptrigger.test.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
..
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
#
# This file implements tests to verify that the "testable statements" in 
# the lang_droptrigger.html document are correct.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix e_createtable

ifcapable !trigger { finish_test ; return }

proc do_droptable_tests {nm args} {
  uplevel do_select_tests [list e_createtable-$nm] $args
}

proc list_all_triggers {{db db}} {
  set res [list]
  $db eval { PRAGMA database_list } {
    if {$name == "temp"} {
................................................................................
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt
#
do_droptable_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            
      {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  2   "DROP TRIGGER IF EXISTS main.tr1"  







|



|







 







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
..
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
#
# This file implements tests to verify that the "testable statements" in 
# the lang_droptrigger.html document are correct.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix e_droptrigger

ifcapable !trigger { finish_test ; return }

proc do_droptrigger_tests {nm args} {
  uplevel do_select_tests [list e_createtable-$nm] $args
}

proc list_all_triggers {{db db}} {
  set res [list]
  $db eval { PRAGMA database_list } {
    if {$name == "temp"} {
................................................................................
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt
#
do_droptrigger_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            
      {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  2   "DROP TRIGGER IF EXISTS main.tr1"  

Added test/e_dropview.test.

































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
# 2010 November 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in 
# the lang_dropview.html document are correct.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix e_dropview

proc dropview_reopen_db {} {
  db close
  forcedelete test.db test.db2
  sqlite3 db test.db

  db eval {
    ATTACH 'test.db2' AS aux;
    CREATE TABLE t1(a, b); 
    INSERT INTO t1 VALUES('a main', 'b main');
    CREATE VIEW v1 AS SELECT * FROM t1;
    CREATE VIEW v2 AS SELECT * FROM t1;

    CREATE TEMP TABLE t1(a, b);
    INSERT INTO temp.t1 VALUES('a temp', 'b temp');
    CREATE VIEW temp.v1 AS SELECT * FROM t1;

    CREATE TABLE aux.t1(a, b);
    INSERT INTO aux.t1 VALUES('a aux', 'b aux');
    CREATE VIEW aux.v1 AS SELECT * FROM t1;
    CREATE VIEW aux.v2 AS SELECT * FROM t1;
    CREATE VIEW aux.v3 AS SELECT * FROM t1;
  }
}

proc list_all_views {{db db}} {
  set res [list]
  $db eval { PRAGMA database_list } {
    set tbl "$name.sqlite_master"
    if {$name == "temp"} { set tbl sqlite_temp_master }

    set sql "SELECT '$name.' || name FROM $tbl WHERE type = 'view'"
    lappend res {*}[$db eval $sql]
  }
  set res
}

proc list_all_data {{db db}} {
  set res [list]
  $db eval { PRAGMA database_list } {
    set tbl "$name.sqlite_master"
    if {$name == "temp"} { set tbl sqlite_temp_master }

    db eval "SELECT '$name.' || name AS x FROM $tbl WHERE type = 'table'" {
      lappend res [list $x [db eval "SELECT * FROM $x"]]
    }
  }
  set res
}

proc do_dropview_tests {nm args} {
  uplevel do_select_tests $nm $args
}

# EVIDENCE-OF: R-21739-51207 -- syntax diagram drop-view-stmt
#
# All paths in the syntax diagram for DROP VIEW are tested by tests 1.*.
#
do_dropview_tests 1 -repair {
  dropview_reopen_db
} -tclquery {
  list_all_views
} {
  1   "DROP VIEW v1"                  {main.v1 main.v2 aux.v1 aux.v2 aux.v3}
  2   "DROP VIEW v2"                  {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
  3   "DROP VIEW main.v1"             {main.v2 temp.v1 aux.v1 aux.v2 aux.v3}
  4   "DROP VIEW main.v2"             {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
  5   "DROP VIEW IF EXISTS v1"        {main.v1 main.v2 aux.v1 aux.v2 aux.v3}
  6   "DROP VIEW IF EXISTS v2"        {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
  7   "DROP VIEW IF EXISTS main.v1"   {main.v2 temp.v1 aux.v1 aux.v2 aux.v3}
  8   "DROP VIEW IF EXISTS main.v2"   {main.v1 temp.v1 aux.v1 aux.v2 aux.v3}
}

# EVIDENCE-OF: R-27002-52307 The DROP VIEW statement removes a view
# created by the CREATE VIEW statement.
#
dropview_reopen_db
do_execsql_test 2.1 {
  CREATE VIEW "new view" AS SELECT * FROM t1 AS x, t1 AS y;
  SELECT * FROM "new view";
} {{a main} {b main} {a main} {b main}}
do_execsql_test 2.2 {;
  SELECT * FROM sqlite_master WHERE name = 'new view';
} {
  view {new view} {new view} 0 
  {CREATE VIEW "new view" AS SELECT * FROM t1 AS x, t1 AS y}
}
do_execsql_test 2.3 {
  DROP VIEW "new view";
  SELECT * FROM sqlite_master WHERE name = 'new view';
} {}
do_catchsql_test 2.4 {
  SELECT * FROM "new view"
} {1 {no such table: new view}}

# EVIDENCE-OF: R-00359-41639 The view definition is removed from the
# database schema, but no actual data in the underlying base tables is
# modified.
#
#     For each view in the database, check that it can be queried. Then drop
#     it. Check that it can no longer be queried and is no longer listed
#     in any schema table. Then check that the contents of the db tables have 
#     not changed
#
set databasedata [list_all_data]

do_execsql_test  3.1.0 { SELECT * FROM temp.v1 } {{a temp} {b temp}}
do_execsql_test  3.1.1 { DROP VIEW temp.v1 } {}
do_catchsql_test 3.1.2 { SELECT * FROM temp.v1 } {1 {no such table: temp.v1}}
do_test          3.1.3 { list_all_views } {main.v1 main.v2 aux.v1 aux.v2 aux.v3}
do_test          3.1.4 { list_all_data  } $databasedata

do_execsql_test  3.2.0 { SELECT * FROM v1 } {{a main} {b main}}
do_execsql_test  3.2.1 { DROP VIEW v1 } {}
do_catchsql_test 3.2.2 { SELECT * FROM main.v1 } {1 {no such table: main.v1}}
do_test          3.2.3 { list_all_views } {main.v2 aux.v1 aux.v2 aux.v3}
do_test          3.2.4 { list_all_data  } $databasedata

do_execsql_test  3.3.0 { SELECT * FROM v2 } {{a main} {b main}}
do_execsql_test  3.3.1 { DROP VIEW v2 } {}
do_catchsql_test 3.3.2 { SELECT * FROM main.v2 } {1 {no such table: main.v2}}
do_test          3.3.3 { list_all_views } {aux.v1 aux.v2 aux.v3}
do_test          3.3.4 { list_all_data  } $databasedata

do_execsql_test  3.4.0 { SELECT * FROM v1 } {{a aux} {b aux}}
do_execsql_test  3.4.1 { DROP VIEW v1 } {}
do_catchsql_test 3.4.2 { SELECT * FROM v1 } {1 {no such table: v1}}
do_test          3.4.3 { list_all_views } {aux.v2 aux.v3}
do_test          3.4.4 { list_all_data  } $databasedata

do_execsql_test  3.4.0 { SELECT * FROM aux.v2 } {{a aux} {b aux}}
do_execsql_test  3.4.1 { DROP VIEW aux.v2 } {}
do_catchsql_test 3.4.2 { SELECT * FROM aux.v2 } {1 {no such table: aux.v2}}
do_test          3.4.3 { list_all_views } {aux.v3}
do_test          3.4.4 { list_all_data  } $databasedata

do_execsql_test  3.5.0 { SELECT * FROM v3 } {{a aux} {b aux}}
do_execsql_test  3.5.1 { DROP VIEW v3 } {}
do_catchsql_test 3.5.2 { SELECT * FROM v3 } {1 {no such table: v3}}
do_test          3.5.3 { list_all_views } {}
do_test          3.5.4 { list_all_data  } $databasedata

# EVIDENCE-OF: R-25558-37487 If the specified view cannot be found and
# the IF EXISTS clause is not present, it is an error.
#
do_dropview_tests 4 -repair {
  dropview_reopen_db 
} -errorformat {
  no such view: %s
} {
  1   "DROP VIEW xx"                  xx
  2   "DROP VIEW main.xx"             main.xx
  3   "DROP VIEW temp.v2"             temp.v2
}

# EVIDENCE-OF: R-07490-32536 If the specified view cannot be found and
# an IF EXISTS clause is present in the DROP VIEW statement, then the
# statement is a no-op.
#
do_dropview_tests 5 -repair {
  dropview_reopen_db
} -tclquery {
  list_all_views
  expr {[list_all_views] == "main.v1 main.v2 temp.v1 aux.v1 aux.v2 aux.v3"}
} {
  1    "DROP VIEW IF EXISTS xx"       1
  2    "DROP VIEW IF EXISTS main.xx"  1
  3    "DROP VIEW IF EXISTS temp.v2"  1
}




finish_test