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.1.1.1 2012/02/21 17:04:16 misho Exp $
18:
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: #
40:
41: set testdir [file dirname $argv0]
42: source $testdir/tester.tcl
43:
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}
64:
65:
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: }
88:
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: }
116:
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}
140:
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: }
167:
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}
187:
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: }
210:
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: }
225:
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: }
260:
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: } {}
295:
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}
339:
340: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to schema2.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