/trunk/src/sqlite/test/shared.test
Unknown | 1062 lines | 1006 code | 56 blank | 0 comment | 0 complexity | 12b01d3f4e67e9fb2d18bbc08d256681 MD5 | raw file
Possible License(s): BSD-3-Clause
- # 2005 December 30
- #
- # The author disclaims copyright to this source code. In place of
- # a legal notice, here is a blessing:
- #
- # May you do good and not evil.
- # May you find forgiveness for yourself and forgive others.
- # May you share freely, never taking more than you give.
- #
- #***********************************************************************
- #
- # $Id: shared.test,v 1.36 2009/03/16 13:19:36 danielk1977 Exp $
- set testdir [file dirname $argv0]
- source $testdir/tester.tcl
- db close
- # These tests cannot be run without the ATTACH command.
- #
- ifcapable !shared_cache||!attach {
- finish_test
- return
- }
- set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
- foreach av [list 0 1] {
- # Open the database connection and execute the auto-vacuum pragma
- file delete -force test.db
- sqlite3 db test.db
- ifcapable autovacuum {
- do_test shared-[expr $av+1].1.0 {
- execsql "pragma auto_vacuum=$::av"
- execsql {pragma auto_vacuum}
- } "$av"
- } else {
- if {$av} {
- db close
- break
- }
- }
- # if we're using proxy locks, we use 2 filedescriptors for a db
- # that is open but NOT yet locked, after a lock is taken we'll have 3,
- # normally sqlite uses 1 (proxy locking adds the conch and the local lock)
- set using_proxy 0
- foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
- set using_proxy $value
- }
- set extrafds_prelock 0
- set extrafds_postlock 0
- if {$using_proxy>0} {
- set extrafds_prelock 1
- set extrafds_postlock 2
- }
- # $av is currently 0 if this loop iteration is to test with auto-vacuum turned
- # off, and 1 if it is turned on. Increment it so that (1 -> no auto-vacuum)
- # and (2 -> auto-vacuum). The sole reason for this is so that it looks nicer
- # when we use this variable as part of test-case names.
- #
- incr av
- # Test organization:
- #
- # shared-1.*: Simple test to verify basic sanity of table level locking when
- # two connections share a pager cache.
- # shared-2.*: Test that a read transaction can co-exist with a
- # write-transaction, including a simple test to ensure the
- # external locking protocol is still working.
- # shared-3.*: Simple test of read-uncommitted mode.
- # shared-4.*: Check that the schema is locked and unlocked correctly.
- # shared-5.*: Test that creating/dropping schema items works when databases
- # are attached in different orders to different handles.
- # shared-6.*: Locking, UNION ALL queries and sub-queries.
- # shared-7.*: Autovacuum and shared-cache.
- # shared-8.*: Tests related to the text encoding of shared-cache databases.
- # shared-9.*: TEMP triggers and shared-cache databases.
- # shared-10.*: Tests of sqlite3_close().
- # shared-11.*: Test transaction locking.
- #
- do_test shared-$av.1.1 {
- # Open a second database on the file test.db. It should use the same pager
- # cache and schema as the original connection. Verify that only 1 file is
- # opened.
- sqlite3 db2 test.db
- set ::sqlite_open_file_count
- expr $sqlite_open_file_count-$extrafds_postlock
- } {1}
- do_test shared-$av.1.2 {
- # Add a table and a single row of data via the first connection.
- # Ensure that the second connection can see them.
- execsql {
- CREATE TABLE abc(a, b, c);
- INSERT INTO abc VALUES(1, 2, 3);
- } db
- execsql {
- SELECT * FROM abc;
- } db2
- } {1 2 3}
- do_test shared-$av.1.3 {
- # Have the first connection begin a transaction and obtain a read-lock
- # on table abc. This should not prevent the second connection from
- # querying abc.
- execsql {
- BEGIN;
- SELECT * FROM abc;
- }
- execsql {
- SELECT * FROM abc;
- } db2
- } {1 2 3}
- do_test shared-$av.1.4 {
- # Try to insert a row into abc via connection 2. This should fail because
- # of the read-lock connection 1 is holding on table abc (obtained in the
- # previous test case).
- catchsql {
- INSERT INTO abc VALUES(4, 5, 6);
- } db2
- } {1 {database table is locked: abc}}
- do_test shared-$av.1.5 {
- # Using connection 2 (the one without the open transaction), try to create
- # a new table. This should fail because of the open read transaction
- # held by connection 1.
- catchsql {
- CREATE TABLE def(d, e, f);
- } db2
- } {1 {database table is locked: sqlite_master}}
- do_test shared-$av.1.6 {
- # Upgrade connection 1's transaction to a write transaction. Create
- # a new table - def - and insert a row into it. Because the connection 1
- # transaction modifies the schema, it should not be possible for
- # connection 2 to access the database at all until the connection 1
- # has finished the transaction.
- execsql {
- CREATE TABLE def(d, e, f);
- INSERT INTO def VALUES('IV', 'V', 'VI');
- }
- } {}
- do_test shared-$av.1.7 {
- # Read from the sqlite_master table with connection 1 (inside the
- # transaction). Then test that we can not do this with connection 2. This
- # is because of the schema-modified lock established by connection 1
- # in the previous test case.
- execsql {
- SELECT * FROM sqlite_master;
- }
- catchsql {
- SELECT * FROM sqlite_master;
- } db2
- } {1 {database schema is locked: main}}
- do_test shared-$av.1.8 {
- # Commit the connection 1 transaction.
- execsql {
- COMMIT;
- }
- } {}
- do_test shared-$av.2.1 {
- # Open connection db3 to the database. Use a different path to the same
- # file so that db3 does *not* share the same pager cache as db and db2
- # (there should be two open file handles).
- if {$::tcl_platform(platform)=="unix"} {
- sqlite3 db3 ./test.db
- } else {
- sqlite3 db3 TEST.DB
- }
- set ::sqlite_open_file_count
- expr $sqlite_open_file_count-($extrafds_prelock+$extrafds_postlock)
- } {2}
- do_test shared-$av.2.2 {
- # Start read transactions on db and db2 (the shared pager cache). Ensure
- # db3 cannot write to the database.
- execsql {
- BEGIN;
- SELECT * FROM abc;
- }
- execsql {
- BEGIN;
- SELECT * FROM abc;
- } db2
- catchsql {
- INSERT INTO abc VALUES(1, 2, 3);
- } db2
- } {1 {database table is locked: abc}}
- do_test shared-$av.2.3 {
- # Turn db's transaction into a write-transaction. db3 should still be
- # able to read from table def (but will not see the new row). Connection
- # db2 should not be able to read def (because of the write-lock).
- # Todo: The failed "INSERT INTO abc ..." statement in the above test
- # has started a write-transaction on db2 (should this be so?). This
- # would prevent connection db from starting a write-transaction. So roll the
- # db2 transaction back and replace it with a new read transaction.
- execsql {
- ROLLBACK;
- BEGIN;
- SELECT * FROM abc;
- } db2
- execsql {
- INSERT INTO def VALUES('VII', 'VIII', 'IX');
- }
- concat [
- catchsql { SELECT * FROM def; } db3
- ] [
- catchsql { SELECT * FROM def; } db2
- ]
- } {0 {IV V VI} 1 {database table is locked: def}}
- do_test shared-$av.2.4 {
- # Commit the open transaction on db. db2 still holds a read-transaction.
- # This should prevent db3 from writing to the database, but not from
- # reading.
- execsql {
- COMMIT;
- }
- concat [
- catchsql { SELECT * FROM def; } db3
- ] [
- catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3
- ]
- } {0 {IV V VI VII VIII IX} 1 {database is locked}}
- catchsql COMMIT db2
- do_test shared-$av.3.1.1 {
- # This test case starts a linear scan of table 'seq' using a
- # read-uncommitted connection. In the middle of the scan, rows are added
- # to the end of the seq table (ahead of the current cursor position).
- # The uncommitted rows should be included in the results of the scan.
- execsql "
- CREATE TABLE seq(i PRIMARY KEY, x);
- INSERT INTO seq VALUES(1, '[string repeat X 500]');
- INSERT INTO seq VALUES(2, '[string repeat X 500]');
- "
- execsql {SELECT * FROM sqlite_master} db2
- execsql {PRAGMA read_uncommitted = 1} db2
- set ret [list]
- db2 eval {SELECT i FROM seq ORDER BY i} {
- if {$i < 4} {
- set max [execsql {SELECT max(i) FROM seq}]
- db eval {
- INSERT INTO seq SELECT i + :max, x FROM seq;
- }
- }
- lappend ret $i
- }
- set ret
- } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
- do_test shared-$av.3.1.2 {
- # Another linear scan through table seq using a read-uncommitted connection.
- # This time, delete each row as it is read. Should not affect the results of
- # the scan, but the table should be empty after the scan is concluded
- # (test 3.1.3 verifies this).
- set ret [list]
- db2 eval {SELECT i FROM seq} {
- db eval {DELETE FROM seq WHERE i = :i}
- lappend ret $i
- }
- set ret
- } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
- do_test shared-$av.3.1.3 {
- execsql {
- SELECT * FROM seq;
- }
- } {}
- catch {db close}
- catch {db2 close}
- catch {db3 close}
- #--------------------------------------------------------------------------
- # Tests shared-4.* test that the schema locking rules are applied
- # correctly. i.e.:
- #
- # 1. All transactions require a read-lock on the schemas of databases they
- # access.
- # 2. Transactions that modify a database schema require a write-lock on that
- # schema.
- # 3. It is not possible to compile a statement while another handle has a
- # write-lock on the schema.
- #
- # Open two database handles db and db2. Each has a single attach database
- # (as well as main):
- #
- # db.main -> ./test.db
- # db.test2 -> ./test2.db
- # db2.main -> ./test2.db
- # db2.test -> ./test.db
- #
- file delete -force test.db
- file delete -force test2.db
- file delete -force test2.db-journal
- sqlite3 db test.db
- sqlite3 db2 test2.db
- do_test shared-$av.4.1.1 {
- set sqlite_open_file_count
- expr $sqlite_open_file_count-($extrafds_prelock*2)
- } {2}
- do_test shared-$av.4.1.2 {
- execsql {ATTACH 'test2.db' AS test2}
- set sqlite_open_file_count
- expr $sqlite_open_file_count-($extrafds_postlock*2)
- } {2}
- do_test shared-$av.4.1.3 {
- execsql {ATTACH 'test.db' AS test} db2
- set sqlite_open_file_count
- expr $sqlite_open_file_count-($extrafds_postlock*2)
- } {2}
- # Sanity check: Create a table in ./test.db via handle db, and test that handle
- # db2 can "see" the new table immediately. A handle using a seperate pager
- # cache would have to reload the database schema before this were possible.
- #
- do_test shared-$av.4.2.1 {
- execsql {
- CREATE TABLE abc(a, b, c);
- CREATE TABLE def(d, e, f);
- INSERT INTO abc VALUES('i', 'ii', 'iii');
- INSERT INTO def VALUES('I', 'II', 'III');
- }
- } {}
- do_test shared-$av.4.2.2 {
- execsql {
- SELECT * FROM test.abc;
- } db2
- } {i ii iii}
- # Open a read-transaction and read from table abc via handle 2. Check that
- # handle 1 can read table abc. Check that handle 1 cannot modify table abc
- # or the database schema. Then check that handle 1 can modify table def.
- #
- do_test shared-$av.4.3.1 {
- execsql {
- BEGIN;
- SELECT * FROM test.abc;
- } db2
- } {i ii iii}
- do_test shared-$av.4.3.2 {
- catchsql {
- INSERT INTO abc VALUES('iv', 'v', 'vi');
- }
- } {1 {database table is locked: abc}}
- do_test shared-$av.4.3.3 {
- catchsql {
- CREATE TABLE ghi(g, h, i);
- }
- } {1 {database table is locked: sqlite_master}}
- do_test shared-$av.4.3.3 {
- catchsql {
- INSERT INTO def VALUES('IV', 'V', 'VI');
- }
- } {0 {}}
- do_test shared-$av.4.3.4 {
- # Cleanup: commit the transaction opened by db2.
- execsql {
- COMMIT
- } db2
- } {}
- # Open a write-transaction using handle 1 and modify the database schema.
- # Then try to execute a compiled statement to read from the same
- # database via handle 2 (fails to get the lock on sqlite_master). Also
- # try to compile a read of the same database using handle 2 (also fails).
- # Finally, compile a read of the other database using handle 2. This
- # should also fail.
- #
- ifcapable compound {
- do_test shared-$av.4.4.1.2 {
- # Sanity check 1: Check that the schema is what we think it is when viewed
- # via handle 1.
- execsql {
- CREATE TABLE test2.ghi(g, h, i);
- SELECT 'test.db:'||name FROM sqlite_master
- UNION ALL
- SELECT 'test2.db:'||name FROM test2.sqlite_master;
- }
- } {test.db:abc test.db:def test2.db:ghi}
- do_test shared-$av.4.4.1.2 {
- # Sanity check 2: Check that the schema is what we think it is when viewed
- # via handle 2.
- execsql {
- SELECT 'test2.db:'||name FROM sqlite_master
- UNION ALL
- SELECT 'test.db:'||name FROM test.sqlite_master;
- } db2
- } {test2.db:ghi test.db:abc test.db:def}
- }
- do_test shared-$av.4.4.2 {
- set ::DB2 [sqlite3_connection_pointer db2]
- set sql {SELECT * FROM abc}
- set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
- execsql {
- BEGIN;
- CREATE TABLE jkl(j, k, l);
- }
- sqlite3_step $::STMT1
- } {SQLITE_ERROR}
- do_test shared-$av.4.4.3 {
- sqlite3_finalize $::STMT1
- } {SQLITE_LOCKED}
- do_test shared-$av.4.4.4 {
- set rc [catch {
- set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
- } msg]
- list $rc $msg
- } {1 {(6) database schema is locked: test}}
- do_test shared-$av.4.4.5 {
- set rc [catch {
- set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]
- } msg]
- list $rc $msg
- } {1 {(6) database schema is locked: test}}
- catch {db2 close}
- catch {db close}
- #--------------------------------------------------------------------------
- # Tests shared-5.*
- #
- foreach db [list test.db test1.db test2.db test3.db] {
- file delete -force $db ${db}-journal
- }
- do_test shared-$av.5.1.1 {
- sqlite3 db1 test.db
- sqlite3 db2 test.db
- execsql {
- ATTACH 'test1.db' AS test1;
- ATTACH 'test2.db' AS test2;
- ATTACH 'test3.db' AS test3;
- } db1
- execsql {
- ATTACH 'test3.db' AS test3;
- ATTACH 'test2.db' AS test2;
- ATTACH 'test1.db' AS test1;
- } db2
- } {}
- do_test shared-$av.5.1.2 {
- execsql {
- CREATE TABLE test1.t1(a, b);
- CREATE INDEX test1.i1 ON t1(a, b);
- } db1
- } {}
- ifcapable view {
- do_test shared-$av.5.1.3 {
- execsql {
- CREATE VIEW test1.v1 AS SELECT * FROM t1;
- } db1
- } {}
- }
- ifcapable trigger {
- do_test shared-$av.5.1.4 {
- execsql {
- CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
- INSERT INTO t1 VALUES(new.a, new.b);
- END;
- } db1
- } {}
- }
- do_test shared-$av.5.1.5 {
- execsql {
- DROP INDEX i1;
- } db2
- } {}
- ifcapable view {
- do_test shared-$av.5.1.6 {
- execsql {
- DROP VIEW v1;
- } db2
- } {}
- }
- ifcapable trigger {
- do_test shared-$av.5.1.7 {
- execsql {
- DROP TRIGGER trig1;
- } db2
- } {}
- }
- do_test shared-$av.5.1.8 {
- execsql {
- DROP TABLE t1;
- } db2
- } {}
- ifcapable compound {
- do_test shared-$av.5.1.9 {
- execsql {
- SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
- } db1
- } {}
- }
- #--------------------------------------------------------------------------
- # Tests shared-6.* test that a query obtains all the read-locks it needs
- # before starting execution of the query. This means that there is no chance
- # some rows of data will be returned before a lock fails and SQLITE_LOCK
- # is returned.
- #
- do_test shared-$av.6.1.1 {
- execsql {
- CREATE TABLE t1(a, b);
- CREATE TABLE t2(a, b);
- INSERT INTO t1 VALUES(1, 2);
- INSERT INTO t2 VALUES(3, 4);
- } db1
- } {}
- ifcapable compound {
- do_test shared-$av.6.1.2 {
- execsql {
- SELECT * FROM t1 UNION ALL SELECT * FROM t2;
- } db2
- } {1 2 3 4}
- }
- do_test shared-$av.6.1.3 {
- # Establish a write lock on table t2 via connection db2. Then make a
- # UNION all query using connection db1 that first accesses t1, followed
- # by t2. If the locks are grabbed at the start of the statement (as
- # they should be), no rows are returned. If (as was previously the case)
- # they are grabbed as the tables are accessed, the t1 rows will be
- # returned before the query fails.
- #
- execsql {
- BEGIN;
- INSERT INTO t2 VALUES(5, 6);
- } db2
- set ret [list]
- catch {
- db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
- lappend ret $a $b
- }
- }
- set ret
- } {}
- do_test shared-$av.6.1.4 {
- execsql {
- COMMIT;
- BEGIN;
- INSERT INTO t1 VALUES(7, 8);
- } db2
- set ret [list]
- catch {
- db1 eval {
- SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
- } {
- lappend ret $d
- }
- }
- set ret
- } {}
- catch {db1 close}
- catch {db2 close}
- foreach f [list test.db test2.db] {
- file delete -force $f ${f}-journal
- }
- #--------------------------------------------------------------------------
- # Tests shared-7.* test auto-vacuum does not invalidate cursors from
- # other shared-cache users when it reorganizes the database on
- # COMMIT.
- #
- do_test shared-$av.7.1 {
- # This test case sets up a test database in auto-vacuum mode consisting
- # of two tables, t1 and t2. Both have a single index. Table t1 is
- # populated first (so consists of pages toward the start of the db file),
- # t2 second (pages toward the end of the file).
- sqlite3 db test.db
- sqlite3 db2 test.db
- execsql {
- BEGIN;
- CREATE TABLE t1(a PRIMARY KEY, b);
- CREATE TABLE t2(a PRIMARY KEY, b);
- }
- set ::contents {}
- for {set i 0} {$i < 100} {incr i} {
- set a [string repeat "$i " 20]
- set b [string repeat "$i " 20]
- db eval {
- INSERT INTO t1 VALUES(:a, :b);
- }
- lappend ::contents [list [expr $i+1] $a $b]
- }
- execsql {
- INSERT INTO t2 SELECT * FROM t1;
- COMMIT;
- }
- } {}
- do_test shared-$av.7.2 {
- # This test case deletes the contents of table t1 (the one at the start of
- # the file) while many cursors are open on table t2 and its index. All of
- # the non-root pages will be moved from the end to the start of the file
- # when the DELETE is committed - this test verifies that moving the pages
- # does not disturb the open cursors.
- #
- proc lockrow {db tbl oids body} {
- set ret [list]
- db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
- if {$i==[lindex $oids 0]} {
- set noids [lrange $oids 1 end]
- if {[llength $noids]==0} {
- set subret [eval $body]
- } else {
- set subret [lockrow $db $tbl $noids $body]
- }
- }
- lappend ret [list $i $a $b]
- }
- return [linsert $subret 0 $ret]
- }
- proc locktblrows {db tbl body} {
- set oids [db eval "SELECT oid FROM $tbl"]
- lockrow $db $tbl $oids $body
- }
- set scans [locktblrows db t2 {
- execsql {
- DELETE FROM t1;
- } db2
- }]
- set error 0
- # Test that each SELECT query returned the expected contents of t2.
- foreach s $scans {
- if {[lsort -integer -index 0 $s]!=$::contents} {
- set error 1
- }
- }
- set error
- } {0}
- catch {db close}
- catch {db2 close}
- unset -nocomplain contents
- #--------------------------------------------------------------------------
- # The following tests try to trick the shared-cache code into assuming
- # the wrong encoding for a database.
- #
- file delete -force test.db test.db-journal
- ifcapable utf16 {
- do_test shared-$av.8.1.1 {
- sqlite3 db test.db
- execsql {
- PRAGMA encoding = 'UTF-16';
- SELECT * FROM sqlite_master;
- }
- } {}
- do_test shared-$av.8.1.2 {
- string range [execsql {PRAGMA encoding;}] 0 end-2
- } {UTF-16}
- do_test shared-$av.8.1.3 {
- sqlite3 db2 test.db
- execsql {
- PRAGMA encoding = 'UTF-8';
- CREATE TABLE abc(a, b, c);
- } db2
- } {}
- do_test shared-$av.8.1.4 {
- execsql {
- SELECT * FROM sqlite_master;
- }
- } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
- do_test shared-$av.8.1.5 {
- db2 close
- execsql {
- PRAGMA encoding;
- }
- } {UTF-8}
- file delete -force test2.db test2.db-journal
- do_test shared-$av.8.2.1 {
- execsql {
- ATTACH 'test2.db' AS aux;
- SELECT * FROM aux.sqlite_master;
- }
- } {}
- do_test shared-$av.8.2.2 {
- sqlite3 db2 test2.db
- execsql {
- PRAGMA encoding = 'UTF-16';
- CREATE TABLE def(d, e, f);
- } db2
- string range [execsql {PRAGMA encoding;} db2] 0 end-2
- } {UTF-16}
- catch {db close}
- catch {db2 close}
- file delete -force test.db test2.db
- do_test shared-$av.8.3.2 {
- sqlite3 db test.db
- execsql { CREATE TABLE def(d, e, f) }
- execsql { PRAGMA encoding }
- } {UTF-8}
- do_test shared-$av.8.3.3 {
- set zDb16 "[encoding convertto unicode test.db]\x00\x00"
- set db16 [sqlite3_open16 $zDb16 {}]
- set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]
- sqlite3_step $stmt
- set sql [sqlite3_column_text $stmt 0]
- sqlite3_finalize $stmt
- set sql
- } {CREATE TABLE def(d, e, f)}
- do_test shared-$av.8.3.4 {
- set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]
- sqlite3_step $stmt
- set enc [sqlite3_column_text $stmt 0]
- sqlite3_finalize $stmt
- set enc
- } {UTF-8}
- sqlite3_close $db16
- # Bug #2547 is causing this to fail.
- if 0 {
- do_test shared-$av.8.2.3 {
- catchsql {
- SELECT * FROM aux.sqlite_master;
- }
- } {1 {attached databases must use the same text encoding as main database}}
- }
- }
- catch {db close}
- catch {db2 close}
- file delete -force test.db test2.db
- #---------------------------------------------------------------------------
- # The following tests - shared-9.* - test interactions between TEMP triggers
- # and shared-schemas.
- #
- ifcapable trigger&&tempdb {
- do_test shared-$av.9.1 {
- sqlite3 db test.db
- sqlite3 db2 test.db
- execsql {
- CREATE TABLE abc(a, b, c);
- CREATE TABLE abc_mirror(a, b, c);
- CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN
- INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
- END;
- INSERT INTO abc VALUES(1, 2, 3);
- SELECT * FROM abc_mirror;
- }
- } {1 2 3}
- do_test shared-$av.9.2 {
- execsql {
- INSERT INTO abc VALUES(4, 5, 6);
- SELECT * FROM abc_mirror;
- } db2
- } {1 2 3}
- do_test shared-$av.9.3 {
- db close
- db2 close
- } {}
- } ; # End shared-9.*
- #---------------------------------------------------------------------------
- # The following tests - shared-10.* - test that the library behaves
- # correctly when a connection to a shared-cache is closed.
- #
- do_test shared-$av.10.1 {
- # Create a small sample database with two connections to it (db and db2).
- file delete -force test.db
- sqlite3 db test.db
- sqlite3 db2 test.db
- execsql {
- CREATE TABLE ab(a PRIMARY KEY, b);
- CREATE TABLE de(d PRIMARY KEY, e);
- INSERT INTO ab VALUES('Chiang Mai', 100000);
- INSERT INTO ab VALUES('Bangkok', 8000000);
- INSERT INTO de VALUES('Ubon', 120000);
- INSERT INTO de VALUES('Khon Kaen', 200000);
- }
- } {}
- do_test shared-$av.10.2 {
- # Open a read-transaction with the first connection, a write-transaction
- # with the second.
- execsql {
- BEGIN;
- SELECT * FROM ab;
- }
- execsql {
- BEGIN;
- INSERT INTO de VALUES('Pataya', 30000);
- } db2
- } {}
- do_test shared-$av.10.3 {
- # An external connection should be able to read the database, but not
- # prepare a write operation.
- if {$::tcl_platform(platform)=="unix"} {
- sqlite3 db3 ./test.db
- } else {
- sqlite3 db3 TEST.DB
- }
- execsql {
- SELECT * FROM ab;
- } db3
- catchsql {
- BEGIN;
- INSERT INTO de VALUES('Pataya', 30000);
- } db3
- } {1 {database is locked}}
- do_test shared-$av.10.4 {
- # Close the connection with the write-transaction open
- db2 close
- } {}
- do_test shared-$av.10.5 {
- # Test that the db2 transaction has been automatically rolled back.
- # If it has not the ('Pataya', 30000) entry will still be in the table.
- execsql {
- SELECT * FROM de;
- }
- } {Ubon 120000 {Khon Kaen} 200000}
- do_test shared-$av.10.5 {
- # Closing db2 should have dropped the shared-cache back to a read-lock.
- # So db3 should be able to prepare a write...
- catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
- } {0 {}}
- do_test shared-$av.10.6 {
- # ... but not commit it.
- catchsql {COMMIT} db3
- } {1 {database is locked}}
- do_test shared-$av.10.7 {
- # Commit the (read-only) db transaction. Check via db3 to make sure the
- # contents of table "de" are still as they should be.
- execsql {
- COMMIT;
- }
- execsql {
- SELECT * FROM de;
- } db3
- } {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
- do_test shared-$av.10.9 {
- # Commit the external transaction.
- catchsql {COMMIT} db3
- } {0 {}}
- integrity_check shared-$av.10.10
- do_test shared-$av.10.11 {
- db close
- db3 close
- } {}
- do_test shared-$av.11.1 {
- file delete -force test.db
- sqlite3 db test.db
- sqlite3 db2 test.db
- execsql {
- CREATE TABLE abc(a, b, c);
- CREATE TABLE abc2(a, b, c);
- BEGIN;
- INSERT INTO abc VALUES(1, 2, 3);
- }
- } {}
- do_test shared-$av.11.2 {
- catchsql {BEGIN;} db2
- catchsql {SELECT * FROM abc;} db2
- } {1 {database table is locked: abc}}
- do_test shared-$av.11.3 {
- catchsql {BEGIN} db2
- } {1 {cannot start a transaction within a transaction}}
- do_test shared-$av.11.4 {
- catchsql {SELECT * FROM abc2;} db2
- } {0 {}}
- do_test shared-$av.11.5 {
- catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
- } {1 {database table is locked}}
- do_test shared-$av.11.6 {
- catchsql {SELECT * FROM abc2}
- } {0 {}}
- do_test shared-$av.11.6 {
- execsql {
- ROLLBACK;
- PRAGMA read_uncommitted = 1;
- } db2
- } {}
- do_test shared-$av.11.7 {
- execsql {
- INSERT INTO abc2 VALUES(4, 5, 6);
- INSERT INTO abc2 VALUES(7, 8, 9);
- }
- } {}
- do_test shared-$av.11.8 {
- set res [list]
- db2 eval {
- SELECT abc.a as I, abc2.a as II FROM abc, abc2;
- } {
- execsql {
- DELETE FROM abc WHERE 1;
- }
- lappend res $I $II
- }
- set res
- } {1 4 {} 7}
- if {[llength [info command sqlite3_shared_cache_report]]==1} {
- do_test shared-$av.11.9 {
- string tolower [sqlite3_shared_cache_report]
- } [string tolower [list [file nativename [file normalize test.db]] 2]]
- }
- do_test shared-$av.11.11 {
- db close
- db2 close
- } {}
- # This tests that if it is impossible to free any pages, SQLite will
- # exceed the limit set by PRAGMA cache_size.
- file delete -force test.db test.db-journal
- sqlite3 db test.db
- ifcapable pager_pragmas {
- do_test shared-$av.12.1 {
- execsql {
- PRAGMA cache_size = 10;
- PRAGMA cache_size;
- }
- } {10}
- }
- do_test shared-$av.12.2 {
- set ::db_handles [list]
- for {set i 1} {$i < 15} {incr i} {
- lappend ::db_handles db$i
- sqlite3 db$i test.db
- execsql "CREATE TABLE db${i}(a, b, c)" db$i
- execsql "INSERT INTO db${i} VALUES(1, 2, 3)"
- }
- } {}
- proc nested_select {handles} {
- [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" {
- lappend ::res $a $b $c
- if {[llength $handles]>1} {
- nested_select [lrange $handles 1 end]
- }
- }
- }
- do_test shared-$av.12.3 {
- set ::res [list]
- nested_select $::db_handles
- set ::res
- } [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1]
- do_test shared-$av.12.X {
- db close
- foreach h $::db_handles {
- $h close
- }
- } {}
- # Internally, locks are acquired on shared B-Tree structures in the order
- # that the structures appear in the virtual memory address space. This
- # test case attempts to cause the order of the structures in memory
- # to be different from the order in which they are attached to a given
- # database handle. This covers an extra line or two.
- #
- do_test shared-$av.13.1 {
- file delete -force test2.db test3.db test4.db test5.db
- sqlite3 db :memory:
- execsql {
- ATTACH 'test2.db' AS aux2;
- ATTACH 'test3.db' AS aux3;
- ATTACH 'test4.db' AS aux4;
- ATTACH 'test5.db' AS aux5;
- DETACH aux2;
- DETACH aux3;
- DETACH aux4;
- ATTACH 'test2.db' AS aux2;
- ATTACH 'test3.db' AS aux3;
- ATTACH 'test4.db' AS aux4;
- }
- } {}
- do_test shared-$av.13.2 {
- execsql {
- CREATE TABLE t1(a, b, c);
- CREATE TABLE aux2.t2(a, b, c);
- CREATE TABLE aux3.t3(a, b, c);
- CREATE TABLE aux4.t4(a, b, c);
- CREATE TABLE aux5.t5(a, b, c);
- SELECT count(*) FROM
- aux2.sqlite_master,
- aux3.sqlite_master,
- aux4.sqlite_master,
- aux5.sqlite_master
- }
- } {1}
- do_test shared-$av.13.3 {
- db close
- } {}
- # Test that nothing horrible happens if a connection to a shared B-Tree
- # structure is closed while some other connection has an open cursor.
- #
- do_test shared-$av.14.1 {
- sqlite3 db test.db
- sqlite3 db2 test.db
- execsql {SELECT name FROM sqlite_master}
- } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
- do_test shared-$av.14.2 {
- set res [list]
- db eval {SELECT name FROM sqlite_master} {
- if {$name eq "db7"} {
- db2 close
- }
- lappend res $name
- }
- set res
- } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
- do_test shared-$av.14.3 {
- db close
- } {}
- # Populate a database schema using connection [db]. Then drop it using
- # [db2]. This is to try to find any points where shared-schema elements
- # are allocated using the lookaside buffer of [db].
- #
- # Mutexes are enabled for this test as that activates a couple of useful
- # assert() statements in the C code.
- #
- do_test shared-$av-15.1 {
- file delete -force test.db
- sqlite3 db test.db -fullmutex 1
- sqlite3 db2 test.db -fullmutex 1
- execsql {
- CREATE TABLE t1(a, b, c);
- CREATE INDEX i1 ON t1(a, b);
- CREATE VIEW v1 AS SELECT * FROM t1;
- CREATE VIEW v2 AS SELECT * FROM t1, v1
- WHERE t1.c=v1.c GROUP BY t1.a ORDER BY v1.b;
- CREATE TRIGGER tr1 AFTER INSERT ON t1
- WHEN new.a!=1
- BEGIN
- DELETE FROM t1 WHERE a=5;
- INSERT INTO t1 VALUES(1, 2, 3);
- UPDATE t1 SET c=c+1;
- END;
- INSERT INTO t1 VALUES(5, 6, 7);
- INSERT INTO t1 VALUES(8, 9, 10);
- INSERT INTO t1 VALUES(11, 12, 13);
- ANALYZE;
- SELECT * FROM t1;
- }
- } {1 2 6 8 9 12 1 2 5 11 12 14 1 2 4}
- do_test shared-$av-15.2 {
- execsql { DROP TABLE t1 } db2
- } {}
- db close
- db2 close
- }
- sqlite3_enable_shared_cache $::enable_shared_cache
- finish_test