| blob | 61d7f731d32999953652d3c193fb5acf0243987e |
1 # 2006 November 08
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 # This file implements regression tests for SQLite library.
12 #
13 # This file tests the various conditions under which an SQLITE_SCHEMA
14 # error should be returned. This is a copy of schema.test that
15 # has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare
16 #
17 # $Id: schema2.test,v 1.4 2009/02/04 17:40:58 drh Exp $
19 #---------------------------------------------------------------------
20 # When any of the following types of SQL statements or actions are
21 # executed, all pre-compiled statements are invalidated. An attempt
22 # to execute an invalidated statement always returns SQLITE_SCHEMA.
23 #
24 # CREATE/DROP TABLE...................................schema2-1.*
25 # CREATE/DROP VIEW....................................schema2-2.*
26 # CREATE/DROP TRIGGER.................................schema2-3.*
27 # CREATE/DROP INDEX...................................schema2-4.*
28 # DETACH..............................................schema2-5.*
29 # Deleting a user-function............................schema2-6.*
30 # Deleting a collation sequence.......................schema2-7.*
31 # Setting or changing the authorization function......schema2-8.*
32 #
33 # Test cases schema2-9.* and schema2-10.* test some specific bugs
34 # that came up during development.
35 #
36 # Test cases schema2-11.* test that it is impossible to delete or
37 # change a collation sequence or user-function while SQL statements
38 # are executing. Adding new collations or functions is allowed.
39 #
41 set testdir [file dirname $argv0]
42 source $testdir/tester.tcl
44 do_test schema2-1.1 {
45 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
46 execsql {
47 CREATE TABLE abc(a, b, c);
48 }
49 sqlite3_step $::STMT
50 } {SQLITE_ROW}
51 do_test schema2-1.2 {
52 sqlite3_finalize $::STMT
53 } {SQLITE_OK}
54 do_test schema2-1.3 {
55 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
56 execsql {
57 DROP TABLE abc;
58 }
59 sqlite3_step $::STMT
60 } {SQLITE_DONE}
61 do_test schema2-1.4 {
62 sqlite3_finalize $::STMT
63 } {SQLITE_OK}
66 ifcapable view {
67 do_test schema2-2.1 {
68 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
69 execsql {
70 CREATE VIEW v1 AS SELECT * FROM sqlite_master;
71 }
72 sqlite3_step $::STMT
73 } {SQLITE_ROW}
74 do_test schema2-2.2 {
75 sqlite3_finalize $::STMT
76 } {SQLITE_OK}
77 do_test schema2-2.3 {
78 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
79 execsql {
80 DROP VIEW v1;
81 }
82 sqlite3_step $::STMT
83 } {SQLITE_DONE}
84 do_test schema2-2.4 {
85 sqlite3_finalize $::STMT
86 } {SQLITE_OK}
87 }
89 ifcapable trigger {
90 do_test schema2-3.1 {
91 execsql {
92 CREATE TABLE abc(a, b, c);
93 }
94 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
95 execsql {
96 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN
97 SELECT 1, 2, 3;
98 END;
99 }
100 sqlite3_step $::STMT
101 } {SQLITE_ROW}
102 do_test schema2-3.2 {
103 sqlite3_finalize $::STMT
104 } {SQLITE_OK}
105 do_test schema2-3.3 {
106 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
107 execsql {
108 DROP TRIGGER abc_trig;
109 }
110 sqlite3_step $::STMT
111 } {SQLITE_ROW}
112 do_test schema2-3.4 {
113 sqlite3_finalize $::STMT
114 } {SQLITE_OK}
115 }
117 do_test schema2-4.1 {
118 catchsql {
119 CREATE TABLE abc(a, b, c);
120 }
121 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
122 execsql {
123 CREATE INDEX abc_index ON abc(a);
124 }
125 sqlite3_step $::STMT
126 } {SQLITE_ROW}
127 do_test schema2-4.2 {
128 sqlite3_finalize $::STMT
129 } {SQLITE_OK}
130 do_test schema2-4.3 {
131 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
132 execsql {
133 DROP INDEX abc_index;
134 }
135 sqlite3_step $::STMT
136 } {SQLITE_ROW}
137 do_test schema2-4.4 {
138 sqlite3_finalize $::STMT
139 } {SQLITE_OK}
141 #---------------------------------------------------------------------
142 # Tests 5.1 to 5.4 check that prepared statements are invalidated when
143 # a database is DETACHed (but not when one is ATTACHed).
144 #
145 ifcapable attach {
146 do_test schema2-5.1 {
147 set sql {SELECT * FROM abc;}
148 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
149 execsql {
150 ATTACH 'test2.db' AS aux;
151 }
152 sqlite3_step $::STMT
153 } {SQLITE_DONE}
154 do_test schema2-5.2 {
155 sqlite3_reset $::STMT
156 } {SQLITE_OK}
157 do_test schema2-5.3 {
158 execsql {
159 DETACH aux;
160 }
161 sqlite3_step $::STMT
162 } {SQLITE_DONE}
163 do_test schema2-5.4 {
164 sqlite3_finalize $::STMT
165 } {SQLITE_OK}
166 }
168 #---------------------------------------------------------------------
169 # Tests 6.* check that prepared statements are invalidated when
170 # a user-function is deleted (but not when one is added).
171 do_test schema2-6.1 {
172 set sql {SELECT * FROM abc;}
173 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
174 db function hello_function {}
175 sqlite3_step $::STMT
176 } {SQLITE_DONE}
177 do_test schema2-6.2 {
178 sqlite3_reset $::STMT
179 } {SQLITE_OK}
180 do_test schema2-6.3 {
181 sqlite_delete_function $::DB hello_function
182 sqlite3_step $::STMT
183 } {SQLITE_DONE}
184 do_test schema2-6.4 {
185 sqlite3_finalize $::STMT
186 } {SQLITE_OK}
188 #---------------------------------------------------------------------
189 # Tests 7.* check that prepared statements are invalidated when
190 # a collation sequence is deleted (but not when one is added).
191 #
192 ifcapable utf16 {
193 do_test schema2-7.1 {
194 set sql {SELECT * FROM abc;}
195 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
196 add_test_collate $::DB 1 1 1
197 sqlite3_step $::STMT
198 } {SQLITE_DONE}
199 do_test schema2-7.2 {
200 sqlite3_reset $::STMT
201 } {SQLITE_OK}
202 do_test schema2-7.3 {
203 add_test_collate $::DB 0 0 0
204 sqlite3_step $::STMT
205 } {SQLITE_DONE}
206 do_test schema2-7.4 {
207 sqlite3_finalize $::STMT
208 } {SQLITE_OK}
209 }
211 #---------------------------------------------------------------------
212 # Tests 8.1 and 8.2 check that prepared statements are invalidated when
213 # the authorization function is set.
214 #
215 ifcapable auth {
216 do_test schema2-8.1 {
217 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
218 db auth {}
219 sqlite3_step $::STMT
220 } {SQLITE_ROW}
221 do_test schema2-8.3 {
222 sqlite3_finalize $::STMT
223 } {SQLITE_OK}
224 }
226 #---------------------------------------------------------------------
227 # schema2-9.1: Test that if a table is dropped by one database connection,
228 # other database connections are aware of the schema change.
229 # schema2-9.2: Test that if a view is dropped by one database connection,
230 # other database connections are aware of the schema change.
231 #
232 do_test schema2-9.1 {
233 sqlite3 db2 test.db
234 execsql {
235 DROP TABLE abc;
236 } db2
237 db2 close
238 catchsql {
239 SELECT * FROM abc;
240 }
241 } {1 {no such table: abc}}
242 execsql {
243 CREATE TABLE abc(a, b, c);
244 }
245 ifcapable view {
246 do_test schema2-9.2 {
247 execsql {
248 CREATE VIEW abcview AS SELECT * FROM abc;
249 }
250 sqlite3 db2 test.db
251 execsql {
252 DROP VIEW abcview;
253 } db2
254 db2 close
255 catchsql {
256 SELECT * FROM abcview;
257 }
258 } {1 {no such table: abcview}}
259 }
261 #---------------------------------------------------------------------
262 # Test that if a CREATE TABLE statement fails because there are other
263 # btree cursors open on the same database file it does not corrupt
264 # the sqlite_master table.
265 #
266 # 2007-05-02: These tests have been overcome by events. Open btree
267 # cursors no longer block CREATE TABLE. But there is no reason not
268 # to keep the tests in the test suite.
269 #
270 do_test schema2-10.1 {
271 execsql {
272 INSERT INTO abc VALUES(1, 2, 3);
273 }
274 set sql {SELECT * FROM abc}
275 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
276 sqlite3_step $::STMT
277 } {SQLITE_ROW}
278 do_test schema2-10.2 {
279 catchsql {
280 CREATE TABLE t2(a, b, c);
281 }
282 } {0 {}}
283 do_test schema2-10.3 {
284 sqlite3_finalize $::STMT
285 } {SQLITE_OK}
286 do_test schema2-10.4 {
287 sqlite3 db2 test.db
288 execsql {
289 SELECT * FROM abc
290 } db2
291 } {1 2 3}
292 do_test schema2-10.5 {
293 db2 close
294 } {}
296 #---------------------------------------------------------------------
297 # Attempting to delete or replace a user-function or collation sequence
298 # while there are active statements returns an SQLITE_BUSY error.
299 #
300 # schema2-11.1 - 11.4: User function.
301 # schema2-11.5 - 11.8: Collation sequence.
302 #
303 do_test schema2-11.1 {
304 db function tstfunc {}
305 set sql {SELECT * FROM abc}
306 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
307 sqlite3_step $::STMT
308 } {SQLITE_ROW}
309 do_test schema2-11.2 {
310 sqlite_delete_function $::DB tstfunc
311 } {SQLITE_BUSY}
312 do_test schema2-11.3 {
313 set rc [catch {
314 db function tstfunc {}
315 } msg]
316 list $rc $msg
317 } {1 {unable to delete/modify user-function due to active statements}}
318 do_test schema2-11.4 {
319 sqlite3_finalize $::STMT
320 } {SQLITE_OK}
321 do_test schema2-11.5 {
322 db collate tstcollate {}
323 set sql {SELECT * FROM abc}
324 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
325 sqlite3_step $::STMT
326 } {SQLITE_ROW}
327 do_test schema2-11.6 {
328 sqlite_delete_collation $::DB tstcollate
329 } {SQLITE_BUSY}
330 do_test schema2-11.7 {
331 set rc [catch {
332 db collate tstcollate {}
333 } msg]
334 list $rc $msg
335 } {1 {unable to delete/modify collation sequence due to active statements}}
336 do_test schema2-11.8 {
337 sqlite3_finalize $::STMT
338 } {SQLITE_OK}
340 finish_test