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.8 2007/10/09 08:29:33 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.*
31
# Rollback of a DDL statement.........................schema-12.*
33
# Test cases schema-9.* and schema-10.* test some specific bugs
34
# that came up during development.
36
# Test cases schema-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.
41
set testdir [file dirname $argv0]
42
source $testdir/tester.tcl
45
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
47
CREATE TABLE abc(a, b, c);
52
sqlite3_finalize $::STMT
55
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
62
sqlite3_finalize $::STMT
67
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
69
CREATE VIEW v1 AS SELECT * FROM sqlite_master;
74
sqlite3_finalize $::STMT
77
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
84
sqlite3_finalize $::STMT
91
CREATE TABLE abc(a, b, c);
93
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
95
CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN
102
sqlite3_finalize $::STMT
105
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
107
DROP TRIGGER abc_trig;
112
sqlite3_finalize $::STMT
118
CREATE TABLE abc(a, b, c);
120
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
122
CREATE INDEX abc_index ON abc(a);
127
sqlite3_finalize $::STMT
130
set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
132
DROP INDEX abc_index;
137
sqlite3_finalize $::STMT
140
#---------------------------------------------------------------------
141
# Tests 5.1 to 5.4 check that prepared statements are invalidated when
142
# a database is DETACHed (but not when one is ATTACHed).
146
set sql {SELECT * FROM abc;}
147
set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
149
ATTACH 'test2.db' AS aux;
154
sqlite3_reset $::STMT
163
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
# 2007-05-02: These tests have been overcome by events. Open btree
266
# cursors no longer block CREATE TABLE. But there is no reason not
267
# to keep the tests in the test suite.
269
do_test schema-10.1 {
271
INSERT INTO abc VALUES(1, 2, 3);
273
set sql {SELECT * FROM abc}
274
set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
277
do_test schema-10.2 {
279
CREATE TABLE t2(a, b, c);
282
do_test schema-10.3 {
283
sqlite3_finalize $::STMT
285
do_test schema-10.4 {
291
do_test schema-10.5 {
295
#---------------------------------------------------------------------
296
# Attempting to delete or replace a user-function or collation sequence
297
# while there are active statements returns an SQLITE_BUSY error.
299
# schema-11.1 - 11.4: User function.
300
# schema-11.5 - 11.8: Collation sequence.
302
do_test schema-11.1 {
303
db function tstfunc {}
304
set sql {SELECT * FROM abc}
305
set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
308
do_test schema-11.2 {
309
sqlite_delete_function $::DB tstfunc
311
do_test schema-11.3 {
313
db function tstfunc {}
316
} {1 {Unable to delete/modify user-function due to active statements}}
317
do_test schema-11.4 {
318
sqlite3_finalize $::STMT
320
do_test schema-11.5 {
321
db collate tstcollate {}
322
set sql {SELECT * FROM abc}
323
set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
326
do_test schema-11.6 {
327
sqlite_delete_collation $::DB tstcollate
329
do_test schema-11.7 {
331
db collate tstcollate {}
334
} {1 {Unable to delete/modify collation sequence due to active statements}}
335
do_test schema-11.8 {
336
sqlite3_finalize $::STMT
339
# The following demonstrates why statements need to be expired whenever
340
# there is a rollback (explicit or otherwise).
342
do_test schema-12.1 {
343
# Begin a transaction and create a table. This increments
344
# the schema cookie. Then compile an SQL statement, using
345
# the current (incremented) value of the cookie.
348
CREATE TABLE t3(a, b, c);
350
set ::STMT [sqlite3_prepare $::DB "CREATE TABLE t4(a,b,c)" -1 TAIL]
352
# Rollback the transaction, resetting the schema cookie to the value
353
# it had at the start of this test case. Then create a table,
354
# incrementing the schema cookie.
357
CREATE TABLE t4(a, b, c);
360
# The schema cookie now has the same value as it did when SQL statement
361
# $::STMT was prepared. So unless it has been expired, it would be
362
# possible to run the "CREATE TABLE t4" statement and create a
364
list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT]
365
} {SQLITE_ERROR SQLITE_SCHEMA}