3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
13 # This file tests the various conditions under which an SQLITE_SCHEMA
14 # error should be returned.
16 # $Id: schema.test,v 1.4 2005/01/29 01:54:18 danielk1977 Exp $
18 #---------------------------------------------------------------------
19 # When any of the following types of SQL statements or actions are
20 # executed, all pre-compiled statements are invalidated. An attempt
21 # to execute an invalidated statement always returns SQLITE_SCHEMA.
23 # CREATE/DROP TABLE...................................schema-1.*
24 # CREATE/DROP VIEW....................................schema-2.*
25 # CREATE/DROP TRIGGER.................................schema-3.*
26 # CREATE/DROP INDEX...................................schema-4.*
27 # DETACH..............................................schema-5.*
28 # Deleting a user-function............................schema-6.*
29 # Deleting a collation sequence.......................schema-7.*
30 # Setting or changing the authorization function......schema-8.*
32 # Note: Test cases schema-6.* are missing right now.
34 # Test cases schema-9.* and schema-10.* test some specific bugs
35 # that came up during development.
37 # Test cases schema-11.* test that it is impossible to delete or
38 # change a collation sequence or user-function while SQL statements
39 # are executing. Adding new collations or functions is allowed.
41 # Note: Test cases schema-11.* are also missing right now.
43 set testdir [file dirname $argv0]
44 source $testdir/tester.tcl
47 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
49 CREATE TABLE abc(a, b, c);
54 sqlite3_finalize $::STMT
57 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
64 sqlite3_finalize $::STMT
69 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
71 CREATE VIEW v1 AS SELECT * FROM sqlite_master;
76 sqlite3_finalize $::STMT
79 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
86 sqlite3_finalize $::STMT
93 CREATE TABLE abc(a, b, c);
95 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
97 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN
104 sqlite3_finalize $::STMT
107 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
109 DROP TRIGGER abc_trig;
114 sqlite3_finalize $::STMT
120 CREATE TABLE abc(a, b, c);
122 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
124 CREATE INDEX abc_index ON abc(a);
129 sqlite3_finalize $::STMT
132 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
134 DROP INDEX abc_index;
139 sqlite3_finalize $::STMT
142 #---------------------------------------------------------------------
143 # Tests 5.1 to 5.4 check that prepared statements are invalidated when
144 # a database is DETACHed (but not when one is ATTACHed).
147 set sql {SELECT * FROM abc;}
148 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
150 ATTACH 'test2.db' AS aux;
155 sqlite3_reset $::STMT
164 sqlite3_finalize $::STMT
167 #---------------------------------------------------------------------
168 # Tests 6.* check that prepared statements are invalidated when
169 # a user-function is deleted (but not when one is added).
171 set sql {SELECT * FROM abc;}
172 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
173 db function hello_function {}
177 sqlite3_reset $::STMT
180 sqlite_delete_function $::DB hello_function
184 sqlite3_finalize $::STMT
187 #---------------------------------------------------------------------
188 # Tests 7.* check that prepared statements are invalidated when
189 # a collation sequence is deleted (but not when one is added).
193 set sql {SELECT * FROM abc;}
194 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
195 add_test_collate $::DB 1 1 1
199 sqlite3_reset $::STMT
202 add_test_collate $::DB 0 0 0
206 sqlite3_finalize $::STMT
210 #---------------------------------------------------------------------
211 # Tests 8.1 and 8.2 check that prepared statements are invalidated when
212 # the authorization function is set.
216 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
221 sqlite3_finalize $::STMT
225 #---------------------------------------------------------------------
226 # schema-9.1: Test that if a table is dropped by one database connection,
227 # other database connections are aware of the schema change.
228 # schema-9.2: Test that if a view is dropped by one database connection,
229 # other database connections are aware of the schema change.
240 } {1 {no such table: abc}}
242 CREATE TABLE abc(a, b, c);
247 CREATE VIEW abcview AS SELECT * FROM abc;
255 SELECT * FROM abcview;
257 } {1 {no such table: abcview}}
260 #---------------------------------------------------------------------
261 # Test that if a CREATE TABLE statement fails because there are other
262 # btree cursors open on the same database file it does not corrupt
263 # the sqlite_master table.
265 do_test schema-10.1 {
267 INSERT INTO abc VALUES(1, 2, 3);
269 set sql {SELECT * FROM abc}
270 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
273 do_test schema-10.2 {
275 CREATE TABLE t2(a, b, c);
277 } {1 {database table is locked}}
278 do_test schema-10.3 {
279 sqlite3_finalize $::STMT
281 do_test schema-10.4 {
287 do_test schema-10.5 {
291 #---------------------------------------------------------------------
292 # Attempting to delete or replace a user-function or collation sequence
293 # while there are active statements returns an SQLITE_BUSY error.
295 # schema-11.1 - 11.4: User function.
296 # schema-11.5 - 11.8: Collation sequence.
298 do_test schema-11.1 {
299 db function tstfunc {}
300 set sql {SELECT * FROM abc}
301 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
304 do_test schema-11.2 {
305 sqlite_delete_function $::DB tstfunc
307 do_test schema-11.3 {
309 db function tstfunc {}
312 } {1 {Unable to delete/modify user-function due to active statements}}
313 do_test schema-11.4 {
314 sqlite3_finalize $::STMT
316 do_test schema-11.5 {
317 db collate tstcollate {}
318 set sql {SELECT * FROM abc}
319 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
322 do_test schema-11.6 {
323 sqlite_delete_collation $::DB tstcollate
325 do_test schema-11.7 {
327 db collate tstcollate {}
330 } {1 {Unable to delete/modify collation sequence due to active statements}}
331 do_test schema-11.8 {
332 sqlite3_finalize $::STMT