1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/schema2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,340 @@
1.4 +# 2006 November 08
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. This is a copy of schema.test that
1.18 +# has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare
1.19 +#
1.20 +# $Id: schema2.test,v 1.3 2007/10/09 08:29:33 danielk1977 Exp $
1.21 +
1.22 +#---------------------------------------------------------------------
1.23 +# When any of the following types of SQL statements or actions are
1.24 +# executed, all pre-compiled statements are invalidated. An attempt
1.25 +# to execute an invalidated statement always returns SQLITE_SCHEMA.
1.26 +#
1.27 +# CREATE/DROP TABLE...................................schema2-1.*
1.28 +# CREATE/DROP VIEW....................................schema2-2.*
1.29 +# CREATE/DROP TRIGGER.................................schema2-3.*
1.30 +# CREATE/DROP INDEX...................................schema2-4.*
1.31 +# DETACH..............................................schema2-5.*
1.32 +# Deleting a user-function............................schema2-6.*
1.33 +# Deleting a collation sequence.......................schema2-7.*
1.34 +# Setting or changing the authorization function......schema2-8.*
1.35 +#
1.36 +# Test cases schema2-9.* and schema2-10.* test some specific bugs
1.37 +# that came up during development.
1.38 +#
1.39 +# Test cases schema2-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 schema2-1.1 {
1.48 + set ::STMT [sqlite3_prepare_v2 $::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_ROW}
1.54 +do_test schema2-1.2 {
1.55 + sqlite3_finalize $::STMT
1.56 +} {SQLITE_OK}
1.57 +do_test schema2-1.3 {
1.58 + set ::STMT [sqlite3_prepare_v2 $::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_DONE}
1.64 +do_test schema2-1.4 {
1.65 + sqlite3_finalize $::STMT
1.66 +} {SQLITE_OK}
1.67 +
1.68 +
1.69 +ifcapable view {
1.70 + do_test schema2-2.1 {
1.71 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.72 + execsql {
1.73 + CREATE VIEW v1 AS SELECT * FROM sqlite_master;
1.74 + }
1.75 + sqlite3_step $::STMT
1.76 + } {SQLITE_ROW}
1.77 + do_test schema2-2.2 {
1.78 + sqlite3_finalize $::STMT
1.79 + } {SQLITE_OK}
1.80 + do_test schema2-2.3 {
1.81 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.82 + execsql {
1.83 + DROP VIEW v1;
1.84 + }
1.85 + sqlite3_step $::STMT
1.86 + } {SQLITE_DONE}
1.87 + do_test schema2-2.4 {
1.88 + sqlite3_finalize $::STMT
1.89 + } {SQLITE_OK}
1.90 +}
1.91 +
1.92 +ifcapable trigger {
1.93 + do_test schema2-3.1 {
1.94 + execsql {
1.95 + CREATE TABLE abc(a, b, c);
1.96 + }
1.97 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.98 + execsql {
1.99 + CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN
1.100 + SELECT 1, 2, 3;
1.101 + END;
1.102 + }
1.103 + sqlite3_step $::STMT
1.104 + } {SQLITE_ROW}
1.105 + do_test schema2-3.2 {
1.106 + sqlite3_finalize $::STMT
1.107 + } {SQLITE_OK}
1.108 + do_test schema2-3.3 {
1.109 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.110 + execsql {
1.111 + DROP TRIGGER abc_trig;
1.112 + }
1.113 + sqlite3_step $::STMT
1.114 + } {SQLITE_ROW}
1.115 + do_test schema2-3.4 {
1.116 + sqlite3_finalize $::STMT
1.117 + } {SQLITE_OK}
1.118 +}
1.119 +
1.120 +do_test schema2-4.1 {
1.121 + catchsql {
1.122 + CREATE TABLE abc(a, b, c);
1.123 + }
1.124 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.125 + execsql {
1.126 + CREATE INDEX abc_index ON abc(a);
1.127 + }
1.128 + sqlite3_step $::STMT
1.129 +} {SQLITE_ROW}
1.130 +do_test schema2-4.2 {
1.131 + sqlite3_finalize $::STMT
1.132 +} {SQLITE_OK}
1.133 +do_test schema2-4.3 {
1.134 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.135 + execsql {
1.136 + DROP INDEX abc_index;
1.137 + }
1.138 + sqlite3_step $::STMT
1.139 +} {SQLITE_ROW}
1.140 +do_test schema2-4.4 {
1.141 + sqlite3_finalize $::STMT
1.142 +} {SQLITE_OK}
1.143 +
1.144 +#---------------------------------------------------------------------
1.145 +# Tests 5.1 to 5.4 check that prepared statements are invalidated when
1.146 +# a database is DETACHed (but not when one is ATTACHed).
1.147 +#
1.148 +ifcapable attach {
1.149 + do_test schema2-5.1 {
1.150 + set sql {SELECT * FROM abc;}
1.151 + set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
1.152 + execsql {
1.153 + ATTACH 'test2.db' AS aux;
1.154 + }
1.155 + sqlite3_step $::STMT
1.156 + } {SQLITE_DONE}
1.157 + do_test schema2-5.2 {
1.158 + sqlite3_reset $::STMT
1.159 + } {SQLITE_OK}
1.160 + do_test schema2-5.3 {
1.161 + execsql {
1.162 + DETACH aux;
1.163 + }
1.164 + sqlite3_step $::STMT
1.165 + } {SQLITE_DONE}
1.166 + do_test schema2-5.4 {
1.167 + sqlite3_finalize $::STMT
1.168 + } {SQLITE_OK}
1.169 +}
1.170 +
1.171 +#---------------------------------------------------------------------
1.172 +# Tests 6.* check that prepared statements are invalidated when
1.173 +# a user-function is deleted (but not when one is added).
1.174 +do_test schema2-6.1 {
1.175 + set sql {SELECT * FROM abc;}
1.176 + set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
1.177 + db function hello_function {}
1.178 + sqlite3_step $::STMT
1.179 +} {SQLITE_DONE}
1.180 +do_test schema2-6.2 {
1.181 + sqlite3_reset $::STMT
1.182 +} {SQLITE_OK}
1.183 +do_test schema2-6.3 {
1.184 + sqlite_delete_function $::DB hello_function
1.185 + sqlite3_step $::STMT
1.186 +} {SQLITE_DONE}
1.187 +do_test schema2-6.4 {
1.188 + sqlite3_finalize $::STMT
1.189 +} {SQLITE_OK}
1.190 +
1.191 +#---------------------------------------------------------------------
1.192 +# Tests 7.* check that prepared statements are invalidated when
1.193 +# a collation sequence is deleted (but not when one is added).
1.194 +#
1.195 +ifcapable utf16 {
1.196 + do_test schema2-7.1 {
1.197 + set sql {SELECT * FROM abc;}
1.198 + set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
1.199 + add_test_collate $::DB 1 1 1
1.200 + sqlite3_step $::STMT
1.201 + } {SQLITE_DONE}
1.202 + do_test schema2-7.2 {
1.203 + sqlite3_reset $::STMT
1.204 + } {SQLITE_OK}
1.205 + do_test schema2-7.3 {
1.206 + add_test_collate $::DB 0 0 0
1.207 + sqlite3_step $::STMT
1.208 + } {SQLITE_DONE}
1.209 + do_test schema2-7.4 {
1.210 + sqlite3_finalize $::STMT
1.211 + } {SQLITE_OK}
1.212 +}
1.213 +
1.214 +#---------------------------------------------------------------------
1.215 +# Tests 8.1 and 8.2 check that prepared statements are invalidated when
1.216 +# the authorization function is set.
1.217 +#
1.218 +ifcapable auth {
1.219 + do_test schema2-8.1 {
1.220 + set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]
1.221 + db auth {}
1.222 + sqlite3_step $::STMT
1.223 + } {SQLITE_ROW}
1.224 + do_test schema2-8.3 {
1.225 + sqlite3_finalize $::STMT
1.226 + } {SQLITE_OK}
1.227 +}
1.228 +
1.229 +#---------------------------------------------------------------------
1.230 +# schema2-9.1: Test that if a table is dropped by one database connection,
1.231 +# other database connections are aware of the schema change.
1.232 +# schema2-9.2: Test that if a view is dropped by one database connection,
1.233 +# other database connections are aware of the schema change.
1.234 +#
1.235 +do_test schema2-9.1 {
1.236 + sqlite3 db2 test.db
1.237 + execsql {
1.238 + DROP TABLE abc;
1.239 + } db2
1.240 + db2 close
1.241 + catchsql {
1.242 + SELECT * FROM abc;
1.243 + }
1.244 +} {1 {no such table: abc}}
1.245 +execsql {
1.246 + CREATE TABLE abc(a, b, c);
1.247 +}
1.248 +ifcapable view {
1.249 + do_test schema2-9.2 {
1.250 + execsql {
1.251 + CREATE VIEW abcview AS SELECT * FROM abc;
1.252 + }
1.253 + sqlite3 db2 test.db
1.254 + execsql {
1.255 + DROP VIEW abcview;
1.256 + } db2
1.257 + db2 close
1.258 + catchsql {
1.259 + SELECT * FROM abcview;
1.260 + }
1.261 + } {1 {no such table: abcview}}
1.262 +}
1.263 +
1.264 +#---------------------------------------------------------------------
1.265 +# Test that if a CREATE TABLE statement fails because there are other
1.266 +# btree cursors open on the same database file it does not corrupt
1.267 +# the sqlite_master table.
1.268 +#
1.269 +# 2007-05-02: These tests have been overcome by events. Open btree
1.270 +# cursors no longer block CREATE TABLE. But there is no reason not
1.271 +# to keep the tests in the test suite.
1.272 +#
1.273 +do_test schema2-10.1 {
1.274 + execsql {
1.275 + INSERT INTO abc VALUES(1, 2, 3);
1.276 + }
1.277 + set sql {SELECT * FROM abc}
1.278 + set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
1.279 + sqlite3_step $::STMT
1.280 +} {SQLITE_ROW}
1.281 +do_test schema2-10.2 {
1.282 + catchsql {
1.283 + CREATE TABLE t2(a, b, c);
1.284 + }
1.285 +} {0 {}}
1.286 +do_test schema2-10.3 {
1.287 + sqlite3_finalize $::STMT
1.288 +} {SQLITE_OK}
1.289 +do_test schema2-10.4 {
1.290 + sqlite3 db2 test.db
1.291 + execsql {
1.292 + SELECT * FROM abc
1.293 + } db2
1.294 +} {1 2 3}
1.295 +do_test schema2-10.5 {
1.296 + db2 close
1.297 +} {}
1.298 +
1.299 +#---------------------------------------------------------------------
1.300 +# Attempting to delete or replace a user-function or collation sequence
1.301 +# while there are active statements returns an SQLITE_BUSY error.
1.302 +#
1.303 +# schema2-11.1 - 11.4: User function.
1.304 +# schema2-11.5 - 11.8: Collation sequence.
1.305 +#
1.306 +do_test schema2-11.1 {
1.307 + db function tstfunc {}
1.308 + set sql {SELECT * FROM abc}
1.309 + set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
1.310 + sqlite3_step $::STMT
1.311 +} {SQLITE_ROW}
1.312 +do_test schema2-11.2 {
1.313 + sqlite_delete_function $::DB tstfunc
1.314 +} {SQLITE_BUSY}
1.315 +do_test schema2-11.3 {
1.316 + set rc [catch {
1.317 + db function tstfunc {}
1.318 + } msg]
1.319 + list $rc $msg
1.320 +} {1 {Unable to delete/modify user-function due to active statements}}
1.321 +do_test schema2-11.4 {
1.322 + sqlite3_finalize $::STMT
1.323 +} {SQLITE_OK}
1.324 +do_test schema2-11.5 {
1.325 + db collate tstcollate {}
1.326 + set sql {SELECT * FROM abc}
1.327 + set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
1.328 + sqlite3_step $::STMT
1.329 +} {SQLITE_ROW}
1.330 +do_test schema2-11.6 {
1.331 + sqlite_delete_collation $::DB tstcollate
1.332 +} {SQLITE_BUSY}
1.333 +do_test schema2-11.7 {
1.334 + set rc [catch {
1.335 + db collate tstcollate {}
1.336 + } msg]
1.337 + list $rc $msg
1.338 +} {1 {Unable to delete/modify collation sequence due to active statements}}
1.339 +do_test schema2-11.8 {
1.340 + sqlite3_finalize $::STMT
1.341 +} {SQLITE_OK}
1.342 +
1.343 +finish_test