1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/shared.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1013 @@
1.4 +# 2005 December 30
1.5 +#
1.6 +# Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.
1.7 +#
1.8 +# The author disclaims copyright to this source code. In place of
1.9 +# a legal notice, here is a blessing:
1.10 +#
1.11 +# May you do good and not evil.
1.12 +# May you find forgiveness for yourself and forgive others.
1.13 +# May you share freely, never taking more than you give.
1.14 +#
1.15 +#***********************************************************************
1.16 +#
1.17 +# $Id: shared.test,v 1.34 2008/07/12 14:52:20 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +db close
1.22 +
1.23 +# These tests cannot be run without the ATTACH command.
1.24 +#
1.25 +ifcapable !shared_cache||!attach {
1.26 + finish_test
1.27 + return
1.28 +}
1.29 +
1.30 +set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
1.31 +
1.32 +foreach av [list 0 1] {
1.33 +
1.34 +# Open the database connection and execute the auto-vacuum pragma
1.35 +file delete -force test.db
1.36 +sqlite3 db test.db
1.37 +
1.38 +ifcapable autovacuum {
1.39 + do_test shared-[expr $av+1].1.0 {
1.40 + execsql "pragma auto_vacuum=$::av"
1.41 + execsql {pragma auto_vacuum}
1.42 + } "$av"
1.43 +} else {
1.44 + if {$av} {
1.45 + db close
1.46 + break
1.47 + }
1.48 +}
1.49 +
1.50 +# $av is currently 0 if this loop iteration is to test with auto-vacuum turned
1.51 +# off, and 1 if it is turned on. Increment it so that (1 -> no auto-vacuum)
1.52 +# and (2 -> auto-vacuum). The sole reason for this is so that it looks nicer
1.53 +# when we use this variable as part of test-case names.
1.54 +#
1.55 +incr av
1.56 +
1.57 +# Test organization:
1.58 +#
1.59 +# shared-1.*: Simple test to verify basic sanity of table level locking when
1.60 +# two connections share a pager cache.
1.61 +# shared-2.*: Test that a read transaction can co-exist with a
1.62 +# write-transaction, including a simple test to ensure the
1.63 +# external locking protocol is still working.
1.64 +# shared-3.*: Simple test of read-uncommitted mode.
1.65 +# shared-4.*: Check that the schema is locked and unlocked correctly.
1.66 +# shared-5.*: Test that creating/dropping schema items works when databases
1.67 +# are attached in different orders to different handles.
1.68 +# shared-6.*: Locking, UNION ALL queries and sub-queries.
1.69 +# shared-7.*: Autovacuum and shared-cache.
1.70 +# shared-8.*: Tests related to the text encoding of shared-cache databases.
1.71 +# shared-9.*: TEMP triggers and shared-cache databases.
1.72 +# shared-10.*: Tests of sqlite3_close().
1.73 +# shared-11.*: Test transaction locking.
1.74 +#
1.75 +
1.76 +do_test shared-$av.1.1 {
1.77 + # Open a second database on the file test.db. It should use the same pager
1.78 + # cache and schema as the original connection. Verify that only 1 file is
1.79 + # opened.
1.80 + sqlite3 db2 test.db
1.81 + set ::sqlite_open_file_count
1.82 +} {1}
1.83 +do_test shared-$av.1.2 {
1.84 + # Add a table and a single row of data via the first connection.
1.85 + # Ensure that the second connection can see them.
1.86 + execsql {
1.87 + CREATE TABLE abc(a, b, c);
1.88 + INSERT INTO abc VALUES(1, 2, 3);
1.89 + } db
1.90 + execsql {
1.91 + SELECT * FROM abc;
1.92 + } db2
1.93 +} {1 2 3}
1.94 +do_test shared-$av.1.3 {
1.95 + # Have the first connection begin a transaction and obtain a read-lock
1.96 + # on table abc. This should not prevent the second connection from
1.97 + # querying abc.
1.98 + execsql {
1.99 + BEGIN;
1.100 + SELECT * FROM abc;
1.101 + }
1.102 + execsql {
1.103 + SELECT * FROM abc;
1.104 + } db2
1.105 +} {1 2 3}
1.106 +do_test shared-$av.1.4 {
1.107 + # Try to insert a row into abc via connection 2. This should fail because
1.108 + # of the read-lock connection 1 is holding on table abc (obtained in the
1.109 + # previous test case).
1.110 + catchsql {
1.111 + INSERT INTO abc VALUES(4, 5, 6);
1.112 + } db2
1.113 +} {1 {database table is locked: abc}}
1.114 +do_test shared-$av.1.5 {
1.115 + # Using connection 2 (the one without the open transaction), try to create
1.116 + # a new table. This should fail because of the open read transaction
1.117 + # held by connection 1.
1.118 + catchsql {
1.119 + CREATE TABLE def(d, e, f);
1.120 + } db2
1.121 +} {1 {database table is locked: sqlite_master}}
1.122 +do_test shared-$av.1.6 {
1.123 + # Upgrade connection 1's transaction to a write transaction. Create
1.124 + # a new table - def - and insert a row into it. Because the connection 1
1.125 + # transaction modifies the schema, it should not be possible for
1.126 + # connection 2 to access the database at all until the connection 1
1.127 + # has finished the transaction.
1.128 + execsql {
1.129 + CREATE TABLE def(d, e, f);
1.130 + INSERT INTO def VALUES('IV', 'V', 'VI');
1.131 + }
1.132 +} {}
1.133 +do_test shared-$av.1.7 {
1.134 + # Read from the sqlite_master table with connection 1 (inside the
1.135 + # transaction). Then test that we can not do this with connection 2. This
1.136 + # is because of the schema-modified lock established by connection 1
1.137 + # in the previous test case.
1.138 + execsql {
1.139 + SELECT * FROM sqlite_master;
1.140 + }
1.141 + catchsql {
1.142 + SELECT * FROM sqlite_master;
1.143 + } db2
1.144 +} {1 {database schema is locked: main}}
1.145 +do_test shared-$av.1.8 {
1.146 + # Commit the connection 1 transaction.
1.147 + execsql {
1.148 + COMMIT;
1.149 + }
1.150 +} {}
1.151 +
1.152 +do_test shared-$av.2.1 {
1.153 + # Open connection db3 to the database. Use a different path to the same
1.154 + # file so that db3 does *not* share the same pager cache as db and db2
1.155 + # (there should be two open file handles).
1.156 + if {$::tcl_platform(platform)=="unix"} {
1.157 + sqlite3 db3 ./test.db
1.158 + } else {
1.159 + sqlite3 db3 TEST.DB
1.160 + }
1.161 + set ::sqlite_open_file_count
1.162 +} {2}
1.163 +do_test shared-$av.2.2 {
1.164 + # Start read transactions on db and db2 (the shared pager cache). Ensure
1.165 + # db3 cannot write to the database.
1.166 + execsql {
1.167 + BEGIN;
1.168 + SELECT * FROM abc;
1.169 + }
1.170 + execsql {
1.171 + BEGIN;
1.172 + SELECT * FROM abc;
1.173 + } db2
1.174 + catchsql {
1.175 + INSERT INTO abc VALUES(1, 2, 3);
1.176 + } db2
1.177 +} {1 {database table is locked: abc}}
1.178 +do_test shared-$av.2.3 {
1.179 + # Turn db's transaction into a write-transaction. db3 should still be
1.180 + # able to read from table def (but will not see the new row). Connection
1.181 + # db2 should not be able to read def (because of the write-lock).
1.182 +
1.183 +# Todo: The failed "INSERT INTO abc ..." statement in the above test
1.184 +# has started a write-transaction on db2 (should this be so?). This
1.185 +# would prevent connection db from starting a write-transaction. So roll the
1.186 +# db2 transaction back and replace it with a new read transaction.
1.187 + execsql {
1.188 + ROLLBACK;
1.189 + BEGIN;
1.190 + SELECT * FROM abc;
1.191 + } db2
1.192 +
1.193 + execsql {
1.194 + INSERT INTO def VALUES('VII', 'VIII', 'IX');
1.195 + }
1.196 + concat [
1.197 + catchsql { SELECT * FROM def; } db3
1.198 + ] [
1.199 + catchsql { SELECT * FROM def; } db2
1.200 + ]
1.201 +} {0 {IV V VI} 1 {database table is locked: def}}
1.202 +do_test shared-$av.2.4 {
1.203 + # Commit the open transaction on db. db2 still holds a read-transaction.
1.204 + # This should prevent db3 from writing to the database, but not from
1.205 + # reading.
1.206 + execsql {
1.207 + COMMIT;
1.208 + }
1.209 + concat [
1.210 + catchsql { SELECT * FROM def; } db3
1.211 + ] [
1.212 + catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3
1.213 + ]
1.214 +} {0 {IV V VI VII VIII IX} 1 {database is locked}}
1.215 +
1.216 +catchsql COMMIT db2
1.217 +
1.218 +do_test shared-$av.3.1.1 {
1.219 + # This test case starts a linear scan of table 'seq' using a
1.220 + # read-uncommitted connection. In the middle of the scan, rows are added
1.221 + # to the end of the seq table (ahead of the current cursor position).
1.222 + # The uncommitted rows should be included in the results of the scan.
1.223 + execsql "
1.224 + CREATE TABLE seq(i PRIMARY KEY, x);
1.225 + INSERT INTO seq VALUES(1, '[string repeat X 500]');
1.226 + INSERT INTO seq VALUES(2, '[string repeat X 500]');
1.227 + "
1.228 + execsql {SELECT * FROM sqlite_master} db2
1.229 + execsql {PRAGMA read_uncommitted = 1} db2
1.230 +
1.231 + set ret [list]
1.232 + db2 eval {SELECT i FROM seq ORDER BY i} {
1.233 + if {$i < 4} {
1.234 + set max [execsql {SELECT max(i) FROM seq}]
1.235 + db eval {
1.236 + INSERT INTO seq SELECT i + :max, x FROM seq;
1.237 + }
1.238 + }
1.239 + lappend ret $i
1.240 + }
1.241 + set ret
1.242 +} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
1.243 +do_test shared-$av.3.1.2 {
1.244 + # Another linear scan through table seq using a read-uncommitted connection.
1.245 + # This time, delete each row as it is read. Should not affect the results of
1.246 + # the scan, but the table should be empty after the scan is concluded
1.247 + # (test 3.1.3 verifies this).
1.248 + set ret [list]
1.249 + db2 eval {SELECT i FROM seq} {
1.250 + db eval {DELETE FROM seq WHERE i = :i}
1.251 + lappend ret $i
1.252 + }
1.253 + set ret
1.254 +} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
1.255 +do_test shared-$av.3.1.3 {
1.256 + execsql {
1.257 + SELECT * FROM seq;
1.258 + }
1.259 +} {}
1.260 +
1.261 +catch {db close}
1.262 +catch {db2 close}
1.263 +catch {db3 close}
1.264 +
1.265 +#--------------------------------------------------------------------------
1.266 +# Tests shared-4.* test that the schema locking rules are applied
1.267 +# correctly. i.e.:
1.268 +#
1.269 +# 1. All transactions require a read-lock on the schemas of databases they
1.270 +# access.
1.271 +# 2. Transactions that modify a database schema require a write-lock on that
1.272 +# schema.
1.273 +# 3. It is not possible to compile a statement while another handle has a
1.274 +# write-lock on the schema.
1.275 +#
1.276 +
1.277 +# Open two database handles db and db2. Each has a single attach database
1.278 +# (as well as main):
1.279 +#
1.280 +# db.main -> ./test.db
1.281 +# db.test2 -> ./test2.db
1.282 +# db2.main -> ./test2.db
1.283 +# db2.test -> ./test.db
1.284 +#
1.285 +file delete -force test.db
1.286 +file delete -force test2.db
1.287 +file delete -force test2.db-journal
1.288 +sqlite3 db test.db
1.289 +sqlite3 db2 test2.db
1.290 +do_test shared-$av.4.1.1 {
1.291 + set sqlite_open_file_count
1.292 +} {2}
1.293 +do_test shared-$av.4.1.2 {
1.294 + execsql {ATTACH 'test2.db' AS test2}
1.295 + set sqlite_open_file_count
1.296 +} {2}
1.297 +do_test shared-$av.4.1.3 {
1.298 + execsql {ATTACH 'test.db' AS test} db2
1.299 + set sqlite_open_file_count
1.300 +} {2}
1.301 +
1.302 +# Sanity check: Create a table in ./test.db via handle db, and test that handle
1.303 +# db2 can "see" the new table immediately. A handle using a seperate pager
1.304 +# cache would have to reload the database schema before this were possible.
1.305 +#
1.306 +do_test shared-$av.4.2.1 {
1.307 + execsql {
1.308 + CREATE TABLE abc(a, b, c);
1.309 + CREATE TABLE def(d, e, f);
1.310 + INSERT INTO abc VALUES('i', 'ii', 'iii');
1.311 + INSERT INTO def VALUES('I', 'II', 'III');
1.312 + }
1.313 +} {}
1.314 +do_test shared-$av.4.2.2 {
1.315 + execsql {
1.316 + SELECT * FROM test.abc;
1.317 + } db2
1.318 +} {i ii iii}
1.319 +
1.320 +# Open a read-transaction and read from table abc via handle 2. Check that
1.321 +# handle 1 can read table abc. Check that handle 1 cannot modify table abc
1.322 +# or the database schema. Then check that handle 1 can modify table def.
1.323 +#
1.324 +do_test shared-$av.4.3.1 {
1.325 + execsql {
1.326 + BEGIN;
1.327 + SELECT * FROM test.abc;
1.328 + } db2
1.329 +} {i ii iii}
1.330 +do_test shared-$av.4.3.2 {
1.331 + catchsql {
1.332 + INSERT INTO abc VALUES('iv', 'v', 'vi');
1.333 + }
1.334 +} {1 {database table is locked: abc}}
1.335 +do_test shared-$av.4.3.3 {
1.336 + catchsql {
1.337 + CREATE TABLE ghi(g, h, i);
1.338 + }
1.339 +} {1 {database table is locked: sqlite_master}}
1.340 +do_test shared-$av.4.3.3 {
1.341 + catchsql {
1.342 + INSERT INTO def VALUES('IV', 'V', 'VI');
1.343 + }
1.344 +} {0 {}}
1.345 +do_test shared-$av.4.3.4 {
1.346 + # Cleanup: commit the transaction opened by db2.
1.347 + execsql {
1.348 + COMMIT
1.349 + } db2
1.350 +} {}
1.351 +
1.352 +# Open a write-transaction using handle 1 and modify the database schema.
1.353 +# Then try to execute a compiled statement to read from the same
1.354 +# database via handle 2 (fails to get the lock on sqlite_master). Also
1.355 +# try to compile a read of the same database using handle 2 (also fails).
1.356 +# Finally, compile a read of the other database using handle 2. This
1.357 +# should also fail.
1.358 +#
1.359 +ifcapable compound {
1.360 + do_test shared-$av.4.4.1.2 {
1.361 + # Sanity check 1: Check that the schema is what we think it is when viewed
1.362 + # via handle 1.
1.363 + execsql {
1.364 + CREATE TABLE test2.ghi(g, h, i);
1.365 + SELECT 'test.db:'||name FROM sqlite_master
1.366 + UNION ALL
1.367 + SELECT 'test2.db:'||name FROM test2.sqlite_master;
1.368 + }
1.369 + } {test.db:abc test.db:def test2.db:ghi}
1.370 + do_test shared-$av.4.4.1.2 {
1.371 + # Sanity check 2: Check that the schema is what we think it is when viewed
1.372 + # via handle 2.
1.373 + execsql {
1.374 + SELECT 'test2.db:'||name FROM sqlite_master
1.375 + UNION ALL
1.376 + SELECT 'test.db:'||name FROM test.sqlite_master;
1.377 + } db2
1.378 + } {test2.db:ghi test.db:abc test.db:def}
1.379 +}
1.380 +
1.381 +do_test shared-$av.4.4.2 {
1.382 + set ::DB2 [sqlite3_connection_pointer db2]
1.383 + set sql {SELECT * FROM abc}
1.384 + set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
1.385 + execsql {
1.386 + BEGIN;
1.387 + CREATE TABLE jkl(j, k, l);
1.388 + }
1.389 + sqlite3_step $::STMT1
1.390 +} {SQLITE_ERROR}
1.391 +do_test shared-$av.4.4.3 {
1.392 + sqlite3_finalize $::STMT1
1.393 +} {SQLITE_LOCKED}
1.394 +do_test shared-$av.4.4.4 {
1.395 + set rc [catch {
1.396 + set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
1.397 + } msg]
1.398 + list $rc $msg
1.399 +} {1 {(6) database schema is locked: test}}
1.400 +do_test shared-$av.4.4.5 {
1.401 + set rc [catch {
1.402 + set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]
1.403 + } msg]
1.404 + list $rc $msg
1.405 +} {1 {(6) database schema is locked: test}}
1.406 +
1.407 +
1.408 +catch {db2 close}
1.409 +catch {db close}
1.410 +
1.411 +#--------------------------------------------------------------------------
1.412 +# Tests shared-5.*
1.413 +#
1.414 +foreach db [list test.db test1.db test2.db test3.db] {
1.415 + file delete -force $db ${db}-journal
1.416 +}
1.417 +do_test shared-$av.5.1.1 {
1.418 + sqlite3 db1 test.db
1.419 + sqlite3 db2 test.db
1.420 + execsql {
1.421 + ATTACH 'test1.db' AS test1;
1.422 + ATTACH 'test2.db' AS test2;
1.423 + ATTACH 'test3.db' AS test3;
1.424 + } db1
1.425 + execsql {
1.426 + ATTACH 'test3.db' AS test3;
1.427 + ATTACH 'test2.db' AS test2;
1.428 + ATTACH 'test1.db' AS test1;
1.429 + } db2
1.430 +} {}
1.431 +do_test shared-$av.5.1.2 {
1.432 + execsql {
1.433 + CREATE TABLE test1.t1(a, b);
1.434 + CREATE INDEX test1.i1 ON t1(a, b);
1.435 + } db1
1.436 +} {}
1.437 +ifcapable view {
1.438 + do_test shared-$av.5.1.3 {
1.439 + execsql {
1.440 + CREATE VIEW test1.v1 AS SELECT * FROM t1;
1.441 + } db1
1.442 + } {}
1.443 +}
1.444 +ifcapable trigger {
1.445 + do_test shared-$av.5.1.4 {
1.446 + execsql {
1.447 + CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
1.448 + INSERT INTO t1 VALUES(new.a, new.b);
1.449 + END;
1.450 + } db1
1.451 + } {}
1.452 +}
1.453 +do_test shared-$av.5.1.5 {
1.454 + execsql {
1.455 + DROP INDEX i1;
1.456 + } db2
1.457 +} {}
1.458 +ifcapable view {
1.459 + do_test shared-$av.5.1.6 {
1.460 + execsql {
1.461 + DROP VIEW v1;
1.462 + } db2
1.463 + } {}
1.464 +}
1.465 +ifcapable trigger {
1.466 + do_test shared-$av.5.1.7 {
1.467 + execsql {
1.468 + DROP TRIGGER trig1;
1.469 + } db2
1.470 + } {}
1.471 +}
1.472 +do_test shared-$av.5.1.8 {
1.473 + execsql {
1.474 + DROP TABLE t1;
1.475 + } db2
1.476 +} {}
1.477 +ifcapable compound {
1.478 + do_test shared-$av.5.1.9 {
1.479 + execsql {
1.480 + SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
1.481 + } db1
1.482 + } {}
1.483 +}
1.484 +
1.485 +#--------------------------------------------------------------------------
1.486 +# Tests shared-6.* test that a query obtains all the read-locks it needs
1.487 +# before starting execution of the query. This means that there is no chance
1.488 +# some rows of data will be returned before a lock fails and SQLITE_LOCK
1.489 +# is returned.
1.490 +#
1.491 +do_test shared-$av.6.1.1 {
1.492 + execsql {
1.493 + CREATE TABLE t1(a, b);
1.494 + CREATE TABLE t2(a, b);
1.495 + INSERT INTO t1 VALUES(1, 2);
1.496 + INSERT INTO t2 VALUES(3, 4);
1.497 + } db1
1.498 +} {}
1.499 +ifcapable compound {
1.500 + do_test shared-$av.6.1.2 {
1.501 + execsql {
1.502 + SELECT * FROM t1 UNION ALL SELECT * FROM t2;
1.503 + } db2
1.504 + } {1 2 3 4}
1.505 +}
1.506 +do_test shared-$av.6.1.3 {
1.507 + # Establish a write lock on table t2 via connection db2. Then make a
1.508 + # UNION all query using connection db1 that first accesses t1, followed
1.509 + # by t2. If the locks are grabbed at the start of the statement (as
1.510 + # they should be), no rows are returned. If (as was previously the case)
1.511 + # they are grabbed as the tables are accessed, the t1 rows will be
1.512 + # returned before the query fails.
1.513 + #
1.514 + execsql {
1.515 + BEGIN;
1.516 + INSERT INTO t2 VALUES(5, 6);
1.517 + } db2
1.518 + set ret [list]
1.519 + catch {
1.520 + db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
1.521 + lappend ret $a $b
1.522 + }
1.523 + }
1.524 + set ret
1.525 +} {}
1.526 +do_test shared-$av.6.1.4 {
1.527 + execsql {
1.528 + COMMIT;
1.529 + BEGIN;
1.530 + INSERT INTO t1 VALUES(7, 8);
1.531 + } db2
1.532 + set ret [list]
1.533 + catch {
1.534 + db1 eval {
1.535 + SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
1.536 + } {
1.537 + lappend ret $d
1.538 + }
1.539 + }
1.540 + set ret
1.541 +} {}
1.542 +
1.543 +catch {db1 close}
1.544 +catch {db2 close}
1.545 +foreach f [list test.db test2.db] {
1.546 + file delete -force $f ${f}-journal
1.547 +}
1.548 +
1.549 +#--------------------------------------------------------------------------
1.550 +# Tests shared-7.* test auto-vacuum does not invalidate cursors from
1.551 +# other shared-cache users when it reorganizes the database on
1.552 +# COMMIT.
1.553 +#
1.554 +do_test shared-$av.7.1 {
1.555 + # This test case sets up a test database in auto-vacuum mode consisting
1.556 + # of two tables, t1 and t2. Both have a single index. Table t1 is
1.557 + # populated first (so consists of pages toward the start of the db file),
1.558 + # t2 second (pages toward the end of the file).
1.559 + sqlite3 db test.db
1.560 + sqlite3 db2 test.db
1.561 + execsql {
1.562 + BEGIN;
1.563 + CREATE TABLE t1(a PRIMARY KEY, b);
1.564 + CREATE TABLE t2(a PRIMARY KEY, b);
1.565 + }
1.566 + set ::contents {}
1.567 + for {set i 0} {$i < 100} {incr i} {
1.568 + set a [string repeat "$i " 20]
1.569 + set b [string repeat "$i " 20]
1.570 + db eval {
1.571 + INSERT INTO t1 VALUES(:a, :b);
1.572 + }
1.573 + lappend ::contents [list [expr $i+1] $a $b]
1.574 + }
1.575 + execsql {
1.576 + INSERT INTO t2 SELECT * FROM t1;
1.577 + COMMIT;
1.578 + }
1.579 +} {}
1.580 +
1.581 +#
1.582 +# Symbian: "stack overflow" if "shared-$av.7.2" is executed
1.583 +#
1.584 +if {$tcl_platform(platform)!="symbian"} {
1.585 + do_test shared-$av.7.2 {
1.586 + # This test case deletes the contents of table t1 (the one at the start of
1.587 + # the file) while many cursors are open on table t2 and its index. All of
1.588 + # the non-root pages will be moved from the end to the start of the file
1.589 + # when the DELETE is committed - this test verifies that moving the pages
1.590 + # does not disturb the open cursors.
1.591 + #
1.592 +
1.593 + proc lockrow {db tbl oids body} {
1.594 + set ret [list]
1.595 + db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
1.596 + if {$i==[lindex $oids 0]} {
1.597 + set noids [lrange $oids 1 end]
1.598 + if {[llength $noids]==0} {
1.599 + set subret [eval $body]
1.600 + } else {
1.601 + set subret [lockrow $db $tbl $noids $body]
1.602 + }
1.603 + }
1.604 + lappend ret [list $i $a $b]
1.605 + }
1.606 + return [linsert $subret 0 $ret]
1.607 + }
1.608 + proc locktblrows {db tbl body} {
1.609 + set oids [db eval "SELECT oid FROM $tbl"]
1.610 + lockrow $db $tbl $oids $body
1.611 + }
1.612 +
1.613 + set scans [locktblrows db t2 {
1.614 + execsql {
1.615 + DELETE FROM t1;
1.616 + } db2
1.617 + }]
1.618 + set error 0
1.619 +
1.620 + # Test that each SELECT query returned the expected contents of t2.
1.621 + foreach s $scans {
1.622 + if {[lsort -integer -index 0 $s]!=$::contents} {
1.623 + set error 1
1.624 + }
1.625 + }
1.626 + set error
1.627 + } {0}
1.628 +}
1.629 +
1.630 +catch {db close}
1.631 +catch {db2 close}
1.632 +unset -nocomplain contents
1.633 +
1.634 +#--------------------------------------------------------------------------
1.635 +# The following tests try to trick the shared-cache code into assuming
1.636 +# the wrong encoding for a database.
1.637 +#
1.638 +file delete -force test.db test.db-journal
1.639 +ifcapable utf16 {
1.640 + do_test shared-$av.8.1.1 {
1.641 + sqlite3 db test.db
1.642 + execsql {
1.643 + PRAGMA encoding = 'UTF-16';
1.644 + SELECT * FROM sqlite_master;
1.645 + }
1.646 + } {}
1.647 + do_test shared-$av.8.1.2 {
1.648 + string range [execsql {PRAGMA encoding;}] 0 end-2
1.649 + } {UTF-16}
1.650 +
1.651 + do_test shared-$av.8.1.3 {
1.652 + sqlite3 db2 test.db
1.653 + execsql {
1.654 + PRAGMA encoding = 'UTF-8';
1.655 + CREATE TABLE abc(a, b, c);
1.656 + } db2
1.657 + } {}
1.658 + do_test shared-$av.8.1.4 {
1.659 + execsql {
1.660 + SELECT * FROM sqlite_master;
1.661 + }
1.662 + } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
1.663 + do_test shared-$av.8.1.5 {
1.664 + db2 close
1.665 + execsql {
1.666 + PRAGMA encoding;
1.667 + }
1.668 + } {UTF-8}
1.669 +
1.670 + file delete -force test2.db test2.db-journal
1.671 + do_test shared-$av.8.2.1 {
1.672 + execsql {
1.673 + ATTACH 'test2.db' AS aux;
1.674 + SELECT * FROM aux.sqlite_master;
1.675 + }
1.676 + } {}
1.677 + do_test shared-$av.8.2.2 {
1.678 + sqlite3 db2 test2.db
1.679 + execsql {
1.680 + PRAGMA encoding = 'UTF-16';
1.681 + CREATE TABLE def(d, e, f);
1.682 + } db2
1.683 + string range [execsql {PRAGMA encoding;} db2] 0 end-2
1.684 + } {UTF-16}
1.685 +
1.686 + catch {db close}
1.687 + catch {db2 close}
1.688 + file delete -force test.db test2.db
1.689 +
1.690 + do_test shared-$av.8.3.2 {
1.691 + sqlite3 db test.db
1.692 + execsql { CREATE TABLE def(d, e, f) }
1.693 + execsql { PRAGMA encoding }
1.694 + } {UTF-8}
1.695 + do_test shared-$av.8.3.3 {
1.696 + set zDb16 "[encoding convertto unicode test.db]\x00\x00"
1.697 + set db16 [sqlite3_open16 $zDb16 {}]
1.698 +
1.699 + set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]
1.700 + sqlite3_step $stmt
1.701 + set sql [sqlite3_column_text $stmt 0]
1.702 + sqlite3_finalize $stmt
1.703 + set sql
1.704 + } {CREATE TABLE def(d, e, f)}
1.705 + do_test shared-$av.8.3.4 {
1.706 + set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]
1.707 + sqlite3_step $stmt
1.708 + set enc [sqlite3_column_text $stmt 0]
1.709 + sqlite3_finalize $stmt
1.710 + set enc
1.711 + } {UTF-8}
1.712 +
1.713 + sqlite3_close $db16
1.714 +
1.715 +# Bug #2547 is causing this to fail.
1.716 +if 0 {
1.717 + do_test shared-$av.8.2.3 {
1.718 + catchsql {
1.719 + SELECT * FROM aux.sqlite_master;
1.720 + }
1.721 + } {1 {attached databases must use the same text encoding as main database}}
1.722 +}
1.723 +}
1.724 +
1.725 +catch {db close}
1.726 +catch {db2 close}
1.727 +file delete -force test.db test2.db
1.728 +
1.729 +#---------------------------------------------------------------------------
1.730 +# The following tests - shared-9.* - test interactions between TEMP triggers
1.731 +# and shared-schemas.
1.732 +#
1.733 +ifcapable trigger&&tempdb {
1.734 +
1.735 +do_test shared-$av.9.1 {
1.736 + sqlite3 db test.db
1.737 + sqlite3 db2 test.db
1.738 + execsql {
1.739 + CREATE TABLE abc(a, b, c);
1.740 + CREATE TABLE abc_mirror(a, b, c);
1.741 + CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN
1.742 + INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
1.743 + END;
1.744 + INSERT INTO abc VALUES(1, 2, 3);
1.745 + SELECT * FROM abc_mirror;
1.746 + }
1.747 +} {1 2 3}
1.748 +do_test shared-$av.9.2 {
1.749 + execsql {
1.750 + INSERT INTO abc VALUES(4, 5, 6);
1.751 + SELECT * FROM abc_mirror;
1.752 + } db2
1.753 +} {1 2 3}
1.754 +do_test shared-$av.9.3 {
1.755 + db close
1.756 + db2 close
1.757 +} {}
1.758 +
1.759 +} ; # End shared-9.*
1.760 +
1.761 +#---------------------------------------------------------------------------
1.762 +# The following tests - shared-10.* - test that the library behaves
1.763 +# correctly when a connection to a shared-cache is closed.
1.764 +#
1.765 +do_test shared-$av.10.1 {
1.766 + # Create a small sample database with two connections to it (db and db2).
1.767 + file delete -force test.db
1.768 + sqlite3 db test.db
1.769 + sqlite3 db2 test.db
1.770 + execsql {
1.771 + CREATE TABLE ab(a PRIMARY KEY, b);
1.772 + CREATE TABLE de(d PRIMARY KEY, e);
1.773 + INSERT INTO ab VALUES('Chiang Mai', 100000);
1.774 + INSERT INTO ab VALUES('Bangkok', 8000000);
1.775 + INSERT INTO de VALUES('Ubon', 120000);
1.776 + INSERT INTO de VALUES('Khon Kaen', 200000);
1.777 + }
1.778 +} {}
1.779 +do_test shared-$av.10.2 {
1.780 + # Open a read-transaction with the first connection, a write-transaction
1.781 + # with the second.
1.782 + execsql {
1.783 + BEGIN;
1.784 + SELECT * FROM ab;
1.785 + }
1.786 + execsql {
1.787 + BEGIN;
1.788 + INSERT INTO de VALUES('Pataya', 30000);
1.789 + } db2
1.790 +} {}
1.791 +do_test shared-$av.10.3 {
1.792 + # An external connection should be able to read the database, but not
1.793 + # prepare a write operation.
1.794 + if {$::tcl_platform(platform)=="unix"} {
1.795 + sqlite3 db3 ./test.db
1.796 + } else {
1.797 + sqlite3 db3 TEST.DB
1.798 + }
1.799 + execsql {
1.800 + SELECT * FROM ab;
1.801 + } db3
1.802 + catchsql {
1.803 + BEGIN;
1.804 + INSERT INTO de VALUES('Pataya', 30000);
1.805 + } db3
1.806 +} {1 {database is locked}}
1.807 +do_test shared-$av.10.4 {
1.808 + # Close the connection with the write-transaction open
1.809 + db2 close
1.810 +} {}
1.811 +do_test shared-$av.10.5 {
1.812 + # Test that the db2 transaction has been automatically rolled back.
1.813 + # If it has not the ('Pataya', 30000) entry will still be in the table.
1.814 + execsql {
1.815 + SELECT * FROM de;
1.816 + }
1.817 +} {Ubon 120000 {Khon Kaen} 200000}
1.818 +do_test shared-$av.10.5 {
1.819 + # Closing db2 should have dropped the shared-cache back to a read-lock.
1.820 + # So db3 should be able to prepare a write...
1.821 + catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
1.822 +} {0 {}}
1.823 +do_test shared-$av.10.6 {
1.824 + # ... but not commit it.
1.825 + catchsql {COMMIT} db3
1.826 +} {1 {database is locked}}
1.827 +do_test shared-$av.10.7 {
1.828 + # Commit the (read-only) db transaction. Check via db3 to make sure the
1.829 + # contents of table "de" are still as they should be.
1.830 + execsql {
1.831 + COMMIT;
1.832 + }
1.833 + execsql {
1.834 + SELECT * FROM de;
1.835 + } db3
1.836 +} {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
1.837 +do_test shared-$av.10.9 {
1.838 + # Commit the external transaction.
1.839 + catchsql {COMMIT} db3
1.840 +} {0 {}}
1.841 +integrity_check shared-$av.10.10
1.842 +do_test shared-$av.10.11 {
1.843 + db close
1.844 + db3 close
1.845 +} {}
1.846 +
1.847 +do_test shared-$av.11.1 {
1.848 + file delete -force test.db
1.849 + sqlite3 db test.db
1.850 + sqlite3 db2 test.db
1.851 + execsql {
1.852 + CREATE TABLE abc(a, b, c);
1.853 + CREATE TABLE abc2(a, b, c);
1.854 + BEGIN;
1.855 + INSERT INTO abc VALUES(1, 2, 3);
1.856 + }
1.857 +} {}
1.858 +do_test shared-$av.11.2 {
1.859 + catchsql {BEGIN;} db2
1.860 + catchsql {SELECT * FROM abc;} db2
1.861 +} {1 {database table is locked: abc}}
1.862 +do_test shared-$av.11.3 {
1.863 + catchsql {BEGIN} db2
1.864 +} {1 {cannot start a transaction within a transaction}}
1.865 +do_test shared-$av.11.4 {
1.866 + catchsql {SELECT * FROM abc2;} db2
1.867 +} {0 {}}
1.868 +do_test shared-$av.11.5 {
1.869 + catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
1.870 +} {1 {database is locked}}
1.871 +do_test shared-$av.11.6 {
1.872 + catchsql {SELECT * FROM abc2}
1.873 +} {0 {}}
1.874 +do_test shared-$av.11.6 {
1.875 + execsql {
1.876 + ROLLBACK;
1.877 + PRAGMA read_uncommitted = 1;
1.878 + } db2
1.879 +} {}
1.880 +do_test shared-$av.11.7 {
1.881 + execsql {
1.882 + INSERT INTO abc2 VALUES(4, 5, 6);
1.883 + INSERT INTO abc2 VALUES(7, 8, 9);
1.884 + }
1.885 +} {}
1.886 +do_test shared-$av.11.8 {
1.887 + set res [list]
1.888 + db2 eval {
1.889 + SELECT abc.a as I, abc2.a as II FROM abc, abc2;
1.890 + } {
1.891 + execsql {
1.892 + DELETE FROM abc WHERE 1;
1.893 + }
1.894 + lappend res $I $II
1.895 + }
1.896 + set res
1.897 +} {1 4 {} 7}
1.898 +if {[llength [info command sqlite3_shared_cache_report]]==1} {
1.899 + do_test shared-$av.11.9 {
1.900 + string tolower [sqlite3_shared_cache_report]
1.901 + } [string tolower [list [file nativename [file normalize test.db]] 2]]
1.902 +}
1.903 +
1.904 +do_test shared-$av.11.11 {
1.905 + db close
1.906 + db2 close
1.907 +} {}
1.908 +
1.909 +# This tests that if it is impossible to free any pages, SQLite will
1.910 +# exceed the limit set by PRAGMA cache_size.
1.911 +file delete -force test.db test.db-journal
1.912 +sqlite3 db test.db
1.913 +ifcapable pager_pragmas {
1.914 + do_test shared-$av.12.1 {
1.915 + execsql {
1.916 + PRAGMA cache_size = 10;
1.917 + PRAGMA cache_size;
1.918 + }
1.919 + } {10}
1.920 +}
1.921 +do_test shared-$av.12.2 {
1.922 + set ::db_handles [list]
1.923 + for {set i 1} {$i < 15} {incr i} {
1.924 + lappend ::db_handles db$i
1.925 + sqlite3 db$i test.db
1.926 + execsql "CREATE TABLE db${i}(a, b, c)" db$i
1.927 + execsql "INSERT INTO db${i} VALUES(1, 2, 3)"
1.928 + }
1.929 +} {}
1.930 +proc nested_select {handles} {
1.931 + [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" {
1.932 + lappend ::res $a $b $c
1.933 + if {[llength $handles]>1} {
1.934 + nested_select [lrange $handles 1 end]
1.935 + }
1.936 + }
1.937 +}
1.938 +do_test shared-$av.12.3 {
1.939 + set ::res [list]
1.940 + nested_select $::db_handles
1.941 + set ::res
1.942 +} [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1]
1.943 +
1.944 +do_test shared-$av.12.X {
1.945 + db close
1.946 + foreach h $::db_handles {
1.947 + $h close
1.948 + }
1.949 +} {}
1.950 +
1.951 +# Internally, locks are acquired on shared B-Tree structures in the order
1.952 +# that the structures appear in the virtual memory address space. This
1.953 +# test case attempts to cause the order of the structures in memory
1.954 +# to be different from the order in which they are attached to a given
1.955 +# database handle. This covers an extra line or two.
1.956 +#
1.957 +do_test shared-$av.13.1 {
1.958 + file delete -force test2.db test3.db test4.db test5.db
1.959 + sqlite3 db :memory:
1.960 + execsql {
1.961 + ATTACH 'test2.db' AS aux2;
1.962 + ATTACH 'test3.db' AS aux3;
1.963 + ATTACH 'test4.db' AS aux4;
1.964 + ATTACH 'test5.db' AS aux5;
1.965 + DETACH aux2;
1.966 + DETACH aux3;
1.967 + DETACH aux4;
1.968 + ATTACH 'test2.db' AS aux2;
1.969 + ATTACH 'test3.db' AS aux3;
1.970 + ATTACH 'test4.db' AS aux4;
1.971 + }
1.972 +} {}
1.973 +do_test shared-$av.13.2 {
1.974 + execsql {
1.975 + CREATE TABLE t1(a, b, c);
1.976 + CREATE TABLE aux2.t2(a, b, c);
1.977 + CREATE TABLE aux3.t3(a, b, c);
1.978 + CREATE TABLE aux4.t4(a, b, c);
1.979 + CREATE TABLE aux5.t5(a, b, c);
1.980 + SELECT count(*) FROM
1.981 + aux2.sqlite_master,
1.982 + aux3.sqlite_master,
1.983 + aux4.sqlite_master,
1.984 + aux5.sqlite_master
1.985 + }
1.986 +} {1}
1.987 +do_test shared-$av.13.3 {
1.988 + db close
1.989 +} {}
1.990 +
1.991 +# Test that nothing horrible happens if a connection to a shared B-Tree
1.992 +# structure is closed while some other connection has an open cursor.
1.993 +#
1.994 +do_test shared-$av.14.1 {
1.995 + sqlite3 db test.db
1.996 + sqlite3 db2 test.db
1.997 + execsql {SELECT name FROM sqlite_master}
1.998 +} {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
1.999 +do_test shared-$av.14.2 {
1.1000 + set res [list]
1.1001 + db eval {SELECT name FROM sqlite_master} {
1.1002 + if {$name eq "db7"} {
1.1003 + db2 close
1.1004 + }
1.1005 + lappend res $name
1.1006 + }
1.1007 + set res
1.1008 +} {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
1.1009 +do_test shared-$av.14.3 {
1.1010 + db close
1.1011 +} {}
1.1012 +
1.1013 +}
1.1014 +
1.1015 +sqlite3_enable_shared_cache $::enable_shared_cache
1.1016 +finish_test