diff options
Diffstat (limited to 'test/shared.test')
| -rw-r--r-- | test/shared.test | 1062 |
1 files changed, 1062 insertions, 0 deletions
diff --git a/test/shared.test b/test/shared.test new file mode 100644 index 0000000..37564e6 --- /dev/null +++ b/test/shared.test @@ -0,0 +1,1062 @@ +# 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 +forcedelete 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 +# +forcedelete test.db +forcedelete test2.db +forcedelete 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] { + forcedelete $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] { + forcedelete $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. +# +forcedelete 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} + + forcedelete 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} + forcedelete 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} +forcedelete 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). + forcedelete 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 { + forcedelete 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. +forcedelete 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 { + forcedelete 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 { + forcedelete 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 |