diff options
Diffstat (limited to 'components/external/SQLite-3.8.1/test/shared.test')
| -rw-r--r-- | components/external/SQLite-3.8.1/test/shared.test | 1184 |
1 files changed, 0 insertions, 1184 deletions
diff --git a/components/external/SQLite-3.8.1/test/shared.test b/components/external/SQLite-3.8.1/test/shared.test deleted file mode 100644 index 4eab476582..0000000000 --- a/components/external/SQLite-3.8.1/test/shared.test +++ /dev/null @@ -1,1184 +0,0 @@ -# 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} { - ifcapable curdir { - 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 - -# Shared cache on a :memory: database. This only works for URI filenames. -# -do_test shared-$av-16.1 { - sqlite3 db1 file::memory: -uri 1 - sqlite3 db2 file::memory: -uri 1 - db1 eval { - CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3); - } - db2 eval { - SELECT x FROM t1 ORDER BY x; - } -} {1 2 3} -do_test shared-$av-16.2 { - db2 eval { - INSERT INTO t1 VALUES(99); - DELETE FROM t1 WHERE x=2; - } - db1 eval { - SELECT x FROM t1 ORDER BY x; - } -} {1 3 99} - -# Verify that there is no cache sharing ordinary (non-URI) filenames are -# used. -# -do_test shared-$av-16.3 { - db1 close - db2 close - sqlite3 db1 :memory: - sqlite3 db2 :memory: - db1 eval { - CREATE TABLE t1(x); INSERT INTO t1 VALUES(4),(5),(6); - } - catchsql { - SELECT * FROM t1; - } db2 -} {1 {no such table: t1}} - -# Shared cache on named memory databases. -# -do_test shared-$av-16.4 { - db1 close - db2 close - forcedelete test.db test.db-wal test.db-journal - sqlite3 db1 file:test.db?mode=memory -uri 1 - sqlite3 db2 file:test.db?mode=memory -uri 1 - db1 eval { - CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3); - } - db2 eval { - SELECT x FROM t1 ORDER BY x; - } -} {1 2 3} -do_test shared-$av-16.5 { - db2 eval { - INSERT INTO t1 VALUES(99); - DELETE FROM t1 WHERE x=2; - } - db1 eval { - SELECT x FROM t1 ORDER BY x; - } -} {1 3 99} -do_test shared-$av-16.6 { - file exists test.db -} {0} ;# Verify that the database is in-memory - -# Shared cache on named memory databases with different names. -# -do_test shared-$av-16.7 { - db1 close - db2 close - forcedelete test1.db test2.db - sqlite3 db1 file:test1.db?mode=memory -uri 1 - sqlite3 db2 file:test2.db?mode=memory -uri 1 - db1 eval { - CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3); - } - catchsql { - SELECT x FROM t1 ORDER BY x; - } db2 -} {1 {no such table: t1}} -do_test shared-$av-16.8 { - file exists test1.db -} {0} ;# Verify that the database is in-memory - -# Shared cache on named memory databases attached to readonly connections. -# -do_test shared-$av-16.8.1 { - db1 close - db2 close - - sqlite3 db test1.db - db eval { - CREATE TABLE yy(a, b); - INSERT INTO yy VALUES(77, 88); - } - db close - - sqlite3 db1 test1.db -uri 1 -readonly 1 - sqlite3 db2 test2.db -uri 1 - - db1 eval { - ATTACH 'file:mem?mode=memory&cache=shared' AS shared; - CREATE TABLE shared.xx(a, b); - INSERT INTO xx VALUES(55, 66); - } - db2 eval { - ATTACH 'file:mem?mode=memory&cache=shared' AS shared; - SELECT * FROM xx; - } -} {55 66} - -do_test shared-$av-16.8.2 { db1 eval { SELECT * FROM yy } } {77 88} -do_test shared-$av-16.8.3 { - list [catch {db1 eval { INSERT INTO yy VALUES(1, 2) }} msg] $msg -} {1 {attempt to write a readonly database}} - -db1 close -db2 close - -} ;# end of autovacuum on/off loop - -sqlite3_enable_shared_cache $::enable_shared_cache -finish_test |