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