1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/schema.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,367 @@
1.4 +# 2005 Jan 24
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file tests the various conditions under which an SQLITE_SCHEMA
1.17 +# error should be returned.
1.18 +#
1.19 +# $Id: schema.test,v 1.8 2007/10/09 08:29:33 danielk1977 Exp $
1.20 +
1.21 +#---------------------------------------------------------------------
1.22 +# When any of the following types of SQL statements or actions are
1.23 +# executed, all pre-compiled statements are invalidated. An attempt
1.24 +# to execute an invalidated statement always returns SQLITE_SCHEMA.
1.25 +#
1.26 +# CREATE/DROP TABLE...................................schema-1.*
1.27 +# CREATE/DROP VIEW....................................schema-2.*
1.28 +# CREATE/DROP TRIGGER.................................schema-3.*
1.29 +# CREATE/DROP INDEX...................................schema-4.*
1.30 +# DETACH..............................................schema-5.*
1.31 +# Deleting a user-function............................schema-6.*
1.32 +# Deleting a collation sequence.......................schema-7.*
1.33 +# Setting or changing the authorization function......schema-8.*
1.34 +# Rollback of a DDL statement.........................schema-12.*
1.35 +#
1.36 +# Test cases schema-9.* and schema-10.* test some specific bugs
1.37 +# that came up during development.
1.38 +#
1.39 +# Test cases schema-11.* test that it is impossible to delete or
1.40 +# change a collation sequence or user-function while SQL statements
1.41 +# are executing. Adding new collations or functions is allowed.
1.42 +#
1.43 +
1.44 +set testdir [file dirname $argv0]
1.45 +source $testdir/tester.tcl
1.46 +
1.47 +do_test schema-1.1 {
1.48 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.49 + execsql {
1.50 + CREATE TABLE abc(a, b, c);
1.51 + }
1.52 + sqlite3_step $::STMT
1.53 +} {SQLITE_ERROR}
1.54 +do_test schema-1.2 {
1.55 + sqlite3_finalize $::STMT
1.56 +} {SQLITE_SCHEMA}
1.57 +do_test schema-1.3 {
1.58 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.59 + execsql {
1.60 + DROP TABLE abc;
1.61 + }
1.62 + sqlite3_step $::STMT
1.63 +} {SQLITE_ERROR}
1.64 +do_test schema-1.4 {
1.65 + sqlite3_finalize $::STMT
1.66 +} {SQLITE_SCHEMA}
1.67 +
1.68 +ifcapable view {
1.69 + do_test schema-2.1 {
1.70 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.71 + execsql {
1.72 + CREATE VIEW v1 AS SELECT * FROM sqlite_master;
1.73 + }
1.74 + sqlite3_step $::STMT
1.75 + } {SQLITE_ERROR}
1.76 + do_test schema-2.2 {
1.77 + sqlite3_finalize $::STMT
1.78 + } {SQLITE_SCHEMA}
1.79 + do_test schema-2.3 {
1.80 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.81 + execsql {
1.82 + DROP VIEW v1;
1.83 + }
1.84 + sqlite3_step $::STMT
1.85 + } {SQLITE_ERROR}
1.86 + do_test schema-2.4 {
1.87 + sqlite3_finalize $::STMT
1.88 + } {SQLITE_SCHEMA}
1.89 +}
1.90 +
1.91 +ifcapable trigger {
1.92 + do_test schema-3.1 {
1.93 + execsql {
1.94 + CREATE TABLE abc(a, b, c);
1.95 + }
1.96 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.97 + execsql {
1.98 + CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN
1.99 + SELECT 1, 2, 3;
1.100 + END;
1.101 + }
1.102 + sqlite3_step $::STMT
1.103 + } {SQLITE_ERROR}
1.104 + do_test schema-3.2 {
1.105 + sqlite3_finalize $::STMT
1.106 + } {SQLITE_SCHEMA}
1.107 + do_test schema-3.3 {
1.108 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.109 + execsql {
1.110 + DROP TRIGGER abc_trig;
1.111 + }
1.112 + sqlite3_step $::STMT
1.113 + } {SQLITE_ERROR}
1.114 + do_test schema-3.4 {
1.115 + sqlite3_finalize $::STMT
1.116 + } {SQLITE_SCHEMA}
1.117 +}
1.118 +
1.119 +do_test schema-4.1 {
1.120 + catchsql {
1.121 + CREATE TABLE abc(a, b, c);
1.122 + }
1.123 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.124 + execsql {
1.125 + CREATE INDEX abc_index ON abc(a);
1.126 + }
1.127 + sqlite3_step $::STMT
1.128 +} {SQLITE_ERROR}
1.129 +do_test schema-4.2 {
1.130 + sqlite3_finalize $::STMT
1.131 +} {SQLITE_SCHEMA}
1.132 +do_test schema-4.3 {
1.133 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.134 + execsql {
1.135 + DROP INDEX abc_index;
1.136 + }
1.137 + sqlite3_step $::STMT
1.138 +} {SQLITE_ERROR}
1.139 +do_test schema-4.4 {
1.140 + sqlite3_finalize $::STMT
1.141 +} {SQLITE_SCHEMA}
1.142 +
1.143 +#---------------------------------------------------------------------
1.144 +# Tests 5.1 to 5.4 check that prepared statements are invalidated when
1.145 +# a database is DETACHed (but not when one is ATTACHed).
1.146 +#
1.147 +ifcapable attach {
1.148 + do_test schema-5.1 {
1.149 + set sql {SELECT * FROM abc;}
1.150 + set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
1.151 + execsql {
1.152 + ATTACH 'test2.db' AS aux;
1.153 + }
1.154 + sqlite3_step $::STMT
1.155 + } {SQLITE_DONE}
1.156 + do_test schema-5.2 {
1.157 + sqlite3_reset $::STMT
1.158 + } {SQLITE_OK}
1.159 + do_test schema-5.3 {
1.160 + execsql {
1.161 + DETACH aux;
1.162 + }
1.163 + sqlite3_step $::STMT
1.164 + } {SQLITE_ERROR}
1.165 + do_test schema-5.4 {
1.166 + sqlite3_finalize $::STMT
1.167 + } {SQLITE_SCHEMA}
1.168 +}
1.169 +
1.170 +#---------------------------------------------------------------------
1.171 +# Tests 6.* check that prepared statements are invalidated when
1.172 +# a user-function is deleted (but not when one is added).
1.173 +do_test schema-6.1 {
1.174 + set sql {SELECT * FROM abc;}
1.175 + set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
1.176 + db function hello_function {}
1.177 + sqlite3_step $::STMT
1.178 +} {SQLITE_DONE}
1.179 +do_test schema-6.2 {
1.180 + sqlite3_reset $::STMT
1.181 +} {SQLITE_OK}
1.182 +do_test schema-6.3 {
1.183 + sqlite_delete_function $::DB hello_function
1.184 + sqlite3_step $::STMT
1.185 +} {SQLITE_ERROR}
1.186 +do_test schema-6.4 {
1.187 + sqlite3_finalize $::STMT
1.188 +} {SQLITE_SCHEMA}
1.189 +
1.190 +#---------------------------------------------------------------------
1.191 +# Tests 7.* check that prepared statements are invalidated when
1.192 +# a collation sequence is deleted (but not when one is added).
1.193 +#
1.194 +ifcapable utf16 {
1.195 + do_test schema-7.1 {
1.196 + set sql {SELECT * FROM abc;}
1.197 + set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
1.198 + add_test_collate $::DB 1 1 1
1.199 + sqlite3_step $::STMT
1.200 + } {SQLITE_DONE}
1.201 + do_test schema-7.2 {
1.202 + sqlite3_reset $::STMT
1.203 + } {SQLITE_OK}
1.204 + do_test schema-7.3 {
1.205 + add_test_collate $::DB 0 0 0
1.206 + sqlite3_step $::STMT
1.207 + } {SQLITE_ERROR}
1.208 + do_test schema-7.4 {
1.209 + sqlite3_finalize $::STMT
1.210 + } {SQLITE_SCHEMA}
1.211 +}
1.212 +
1.213 +#---------------------------------------------------------------------
1.214 +# Tests 8.1 and 8.2 check that prepared statements are invalidated when
1.215 +# the authorization function is set.
1.216 +#
1.217 +ifcapable auth {
1.218 + do_test schema-8.1 {
1.219 + set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.220 + db auth {}
1.221 + sqlite3_step $::STMT
1.222 + } {SQLITE_ERROR}
1.223 + do_test schema-8.3 {
1.224 + sqlite3_finalize $::STMT
1.225 + } {SQLITE_SCHEMA}
1.226 +}
1.227 +
1.228 +#---------------------------------------------------------------------
1.229 +# schema-9.1: Test that if a table is dropped by one database connection,
1.230 +# other database connections are aware of the schema change.
1.231 +# schema-9.2: Test that if a view is dropped by one database connection,
1.232 +# other database connections are aware of the schema change.
1.233 +#
1.234 +do_test schema-9.1 {
1.235 + sqlite3 db2 test.db
1.236 + execsql {
1.237 + DROP TABLE abc;
1.238 + } db2
1.239 + db2 close
1.240 + catchsql {
1.241 + SELECT * FROM abc;
1.242 + }
1.243 +} {1 {no such table: abc}}
1.244 +execsql {
1.245 + CREATE TABLE abc(a, b, c);
1.246 +}
1.247 +ifcapable view {
1.248 + do_test schema-9.2 {
1.249 + execsql {
1.250 + CREATE VIEW abcview AS SELECT * FROM abc;
1.251 + }
1.252 + sqlite3 db2 test.db
1.253 + execsql {
1.254 + DROP VIEW abcview;
1.255 + } db2
1.256 + db2 close
1.257 + catchsql {
1.258 + SELECT * FROM abcview;
1.259 + }
1.260 + } {1 {no such table: abcview}}
1.261 +}
1.262 +
1.263 +#---------------------------------------------------------------------
1.264 +# Test that if a CREATE TABLE statement fails because there are other
1.265 +# btree cursors open on the same database file it does not corrupt
1.266 +# the sqlite_master table.
1.267 +#
1.268 +# 2007-05-02: These tests have been overcome by events. Open btree
1.269 +# cursors no longer block CREATE TABLE. But there is no reason not
1.270 +# to keep the tests in the test suite.
1.271 +#
1.272 +do_test schema-10.1 {
1.273 + execsql {
1.274 + INSERT INTO abc VALUES(1, 2, 3);
1.275 + }
1.276 + set sql {SELECT * FROM abc}
1.277 + set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
1.278 + sqlite3_step $::STMT
1.279 +} {SQLITE_ROW}
1.280 +do_test schema-10.2 {
1.281 + catchsql {
1.282 + CREATE TABLE t2(a, b, c);
1.283 + }
1.284 +} {0 {}}
1.285 +do_test schema-10.3 {
1.286 + sqlite3_finalize $::STMT
1.287 +} {SQLITE_OK}
1.288 +do_test schema-10.4 {
1.289 + sqlite3 db2 test.db
1.290 + execsql {
1.291 + SELECT * FROM abc
1.292 + } db2
1.293 +} {1 2 3}
1.294 +do_test schema-10.5 {
1.295 + db2 close
1.296 +} {}
1.297 +
1.298 +#---------------------------------------------------------------------
1.299 +# Attempting to delete or replace a user-function or collation sequence
1.300 +# while there are active statements returns an SQLITE_BUSY error.
1.301 +#
1.302 +# schema-11.1 - 11.4: User function.
1.303 +# schema-11.5 - 11.8: Collation sequence.
1.304 +#
1.305 +do_test schema-11.1 {
1.306 + db function tstfunc {}
1.307 + set sql {SELECT * FROM abc}
1.308 + set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
1.309 + sqlite3_step $::STMT
1.310 +} {SQLITE_ROW}
1.311 +do_test schema-11.2 {
1.312 + sqlite_delete_function $::DB tstfunc
1.313 +} {SQLITE_BUSY}
1.314 +do_test schema-11.3 {
1.315 + set rc [catch {
1.316 + db function tstfunc {}
1.317 + } msg]
1.318 + list $rc $msg
1.319 +} {1 {Unable to delete/modify user-function due to active statements}}
1.320 +do_test schema-11.4 {
1.321 + sqlite3_finalize $::STMT
1.322 +} {SQLITE_OK}
1.323 +do_test schema-11.5 {
1.324 + db collate tstcollate {}
1.325 + set sql {SELECT * FROM abc}
1.326 + set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
1.327 + sqlite3_step $::STMT
1.328 +} {SQLITE_ROW}
1.329 +do_test schema-11.6 {
1.330 + sqlite_delete_collation $::DB tstcollate
1.331 +} {SQLITE_BUSY}
1.332 +do_test schema-11.7 {
1.333 + set rc [catch {
1.334 + db collate tstcollate {}
1.335 + } msg]
1.336 + list $rc $msg
1.337 +} {1 {Unable to delete/modify collation sequence due to active statements}}
1.338 +do_test schema-11.8 {
1.339 + sqlite3_finalize $::STMT
1.340 +} {SQLITE_OK}
1.341 +
1.342 +# The following demonstrates why statements need to be expired whenever
1.343 +# there is a rollback (explicit or otherwise).
1.344 +#
1.345 +do_test schema-12.1 {
1.346 + # Begin a transaction and create a table. This increments
1.347 + # the schema cookie. Then compile an SQL statement, using
1.348 + # the current (incremented) value of the cookie.
1.349 + execsql {
1.350 + BEGIN;
1.351 + CREATE TABLE t3(a, b, c);
1.352 + }
1.353 + set ::STMT [sqlite3_prepare $::DB "CREATE TABLE t4(a,b,c)" -1 TAIL]
1.354 +
1.355 + # Rollback the transaction, resetting the schema cookie to the value
1.356 + # it had at the start of this test case. Then create a table,
1.357 + # incrementing the schema cookie.
1.358 + execsql {
1.359 + ROLLBACK;
1.360 + CREATE TABLE t4(a, b, c);
1.361 + }
1.362 +
1.363 + # The schema cookie now has the same value as it did when SQL statement
1.364 + # $::STMT was prepared. So unless it has been expired, it would be
1.365 + # possible to run the "CREATE TABLE t4" statement and create a
1.366 + # duplicate table.
1.367 + list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT]
1.368 +} {SQLITE_ERROR SQLITE_SCHEMA}
1.369 +
1.370 +finish_test