AryaWu/sqlite
0
1# 2005 December 302#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11#12# $Id: shared.test,v 1.36 2009/03/16 13:19:36 danielk1977 Exp $13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16db close17 18# These tests cannot be run without the ATTACH command.19#20ifcapable !shared_cache||!attach {21 finish_test22 return23}24 25set ::enable_shared_cache [sqlite3_enable_shared_cache 1]26 27foreach av [list 0 1] {28 29# Open the database connection and execute the auto-vacuum pragma30forcedelete test.db31sqlite3 db test.db32 33ifcapable autovacuum {34 do_test shared-[expr $av+1].1.0 {35 execsql "pragma auto_vacuum=$::av"36 execsql {pragma auto_vacuum}37 } "$av"38} else {39 if {$av} {40 db close41 break42 }43}44 45# if we're using proxy locks, we use 2 filedescriptors for a db46# that is open but NOT yet locked, after a lock is taken we'll have 3, 47# normally sqlite uses 1 (proxy locking adds the conch and the local lock)48set using_proxy 049foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {50 set using_proxy $value51}52set extrafds_prelock 053set extrafds_postlock 054if {$using_proxy>0} {55 set extrafds_prelock 156 set extrafds_postlock 257} 58 59# $av is currently 0 if this loop iteration is to test with auto-vacuum turned60# off, and 1 if it is turned on. Increment it so that (1 -> no auto-vacuum) 61# and (2 -> auto-vacuum). The sole reason for this is so that it looks nicer62# when we use this variable as part of test-case names.63#64incr av65 66# Test organization:67#68# shared-1.*: Simple test to verify basic sanity of table level locking when69# two connections share a pager cache.70# shared-2.*: Test that a read transaction can co-exist with a 71# write-transaction, including a simple test to ensure the 72# external locking protocol is still working.73# shared-3.*: Simple test of read-uncommitted mode.74# shared-4.*: Check that the schema is locked and unlocked correctly.75# shared-5.*: Test that creating/dropping schema items works when databases76# are attached in different orders to different handles.77# shared-6.*: Locking, UNION ALL queries and sub-queries.78# shared-7.*: Autovacuum and shared-cache.79# shared-8.*: Tests related to the text encoding of shared-cache databases.80# shared-9.*: TEMP triggers and shared-cache databases.81# shared-10.*: Tests of sqlite3_close().82# shared-11.*: Test transaction locking.83#84 85do_test shared-$av.1.1 {86 # Open a second database on the file test.db. It should use the same pager87 # cache and schema as the original connection. Verify that only 1 file is 88 # opened.89 sqlite3 db2 test.db90 set ::sqlite_open_file_count91 expr $sqlite_open_file_count-$extrafds_postlock92} {1}93do_test shared-$av.1.2 {94 # Add a table and a single row of data via the first connection. 95 # Ensure that the second connection can see them.96 execsql {97 CREATE TABLE abc(a, b, c);98 INSERT INTO abc VALUES(1, 2, 3);99 } db100 execsql {101 SELECT * FROM abc;102 } db2103} {1 2 3}104do_test shared-$av.1.3 {105 # Have the first connection begin a transaction and obtain a read-lock106 # on table abc. This should not prevent the second connection from 107 # querying abc.108 execsql {109 BEGIN;110 SELECT * FROM abc;111 }112 execsql {113 SELECT * FROM abc;114 } db2115} {1 2 3}116do_test shared-$av.1.4 {117 # Try to insert a row into abc via connection 2. This should fail because118 # of the read-lock connection 1 is holding on table abc (obtained in the119 # previous test case).120 catchsql {121 INSERT INTO abc VALUES(4, 5, 6);122 } db2123} {1 {database table is locked: abc}}124do_test shared-$av.1.5 {125 # Using connection 2 (the one without the open transaction), try to create126 # a new table. This should fail because of the open read transaction 127 # held by connection 1.128 catchsql {129 CREATE TABLE def(d, e, f);130 } db2131} {1 {database table is locked: sqlite_master}}132do_test shared-$av.1.6 {133 # Upgrade connection 1's transaction to a write transaction. Create134 # a new table - def - and insert a row into it. Because the connection 1135 # transaction modifies the schema, it should not be possible for 136 # connection 2 to access the database at all until the connection 1 137 # has finished the transaction.138 execsql {139 CREATE TABLE def(d, e, f);140 INSERT INTO def VALUES('IV', 'V', 'VI');141 }142} {}143do_test shared-$av.1.7 {144 # Read from the sqlite_master table with connection 1 (inside the 145 # transaction). Then test that we can not do this with connection 2. This146 # is because of the schema-modified lock established by connection 1 147 # in the previous test case.148 execsql {149 SELECT * FROM sqlite_master;150 }151 catchsql {152 SELECT * FROM sqlite_master;153 } db2154} {1 {database schema is locked: main}}155do_test shared-$av.1.8 {156 # Commit the connection 1 transaction.157 execsql {158 COMMIT;159 }160} {}161 162do_test shared-$av.2.1 {163 # Open connection db3 to the database.164 if {$::tcl_platform(platform) eq "unix"} {165 sqlite3 db3 "file:test.db?cache=private" -uri 1166 } else {167 sqlite3 db3 TEST.DB168 }169 set ::sqlite_open_file_count170 expr $sqlite_open_file_count-($extrafds_prelock+$extrafds_postlock)171} {2}172do_test shared-$av.2.2 {173 # Start read transactions on db and db2 (the shared pager cache). Ensure174 # db3 cannot write to the database.175 execsql {176 BEGIN;177 SELECT * FROM abc;178 }179 execsql {180 BEGIN;181 SELECT * FROM abc;182 } db2183 catchsql {184 INSERT INTO abc VALUES(1, 2, 3);185 } db2186} {1 {database table is locked: abc}}187do_test shared-$av.2.3 {188 # Turn db's transaction into a write-transaction. db3 should still be189 # able to read from table def (but will not see the new row). Connection190 # db2 should not be able to read def (because of the write-lock).191 192# Todo: The failed "INSERT INTO abc ..." statement in the above test193# has started a write-transaction on db2 (should this be so?). This 194# would prevent connection db from starting a write-transaction. So roll the195# db2 transaction back and replace it with a new read transaction.196 execsql {197 ROLLBACK;198 BEGIN;199 SELECT * FROM abc;200 } db2201 202 execsql {203 INSERT INTO def VALUES('VII', 'VIII', 'IX');204 }205 concat [206 catchsql { SELECT * FROM def; } db3207 ] [208 catchsql { SELECT * FROM def; } db2209 ]210} {0 {IV V VI} 1 {database table is locked: def}}211do_test shared-$av.2.4 {212 # Commit the open transaction on db. db2 still holds a read-transaction.213 # This should prevent db3 from writing to the database, but not from 214 # reading.215 execsql {216 COMMIT;217 }218 concat [219 catchsql { SELECT * FROM def; } db3220 ] [221 catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3222 ]223} {0 {IV V VI VII VIII IX} 1 {database is locked}}224 225catchsql COMMIT db2226 227do_test shared-$av.3.1.1 {228 # This test case starts a linear scan of table 'seq' using a 229 # read-uncommitted connection. In the middle of the scan, rows are added230 # to the end of the seq table (ahead of the current cursor position).231 # The uncommitted rows should be included in the results of the scan.232 execsql "233 CREATE TABLE seq(i PRIMARY KEY, x);234 INSERT INTO seq VALUES(1, '[string repeat X 500]');235 INSERT INTO seq VALUES(2, '[string repeat X 500]');236 "237 execsql {SELECT * FROM sqlite_master} db2238 execsql {PRAGMA read_uncommitted = 1} db2239 240 set ret [list]241 db2 eval {SELECT i FROM seq ORDER BY i} {242 if {$i < 4} {243 set max [execsql {SELECT max(i) FROM seq}]244 db eval {245 INSERT INTO seq SELECT i + :max, x FROM seq;246 }247 }248 lappend ret $i249 }250 set ret251} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}252do_test shared-$av.3.1.2 {253 # Another linear scan through table seq using a read-uncommitted connection.254 # This time, delete each row as it is read. Should not affect the results of255 # the scan, but the table should be empty after the scan is concluded 256 # (test 3.1.3 verifies this).257 set ret [list]258 db2 eval {SELECT i FROM seq} {259 db eval {DELETE FROM seq WHERE i = :i}260 lappend ret $i261 }262 set ret263} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}264do_test shared-$av.3.1.3 {265 execsql {266 SELECT * FROM seq;267 }268} {}269 270catch {db close}271catch {db2 close}272catch {db3 close}273 274#--------------------------------------------------------------------------275# Tests shared-4.* test that the schema locking rules are applied 276# correctly. i.e.:277#278# 1. All transactions require a read-lock on the schemas of databases they279# access.280# 2. Transactions that modify a database schema require a write-lock on that281# schema.282# 3. It is not possible to compile a statement while another handle has a 283# write-lock on the schema.284#285 286# Open two database handles db and db2. Each has a single attach database287# (as well as main):288#289# db.main -> ./test.db290# db.test2 -> ./test2.db291# db2.main -> ./test2.db292# db2.test -> ./test.db293#294forcedelete test.db295forcedelete test2.db296forcedelete test2.db-journal297sqlite3 db test.db298sqlite3 db2 test2.db299do_test shared-$av.4.1.1 {300 set sqlite_open_file_count301 expr $sqlite_open_file_count-($extrafds_prelock*2)302} {2}303do_test shared-$av.4.1.2 {304 execsql {ATTACH 'test2.db' AS test2}305 set sqlite_open_file_count306 expr $sqlite_open_file_count-($extrafds_postlock*2)307} {2}308do_test shared-$av.4.1.3 {309 execsql {ATTACH 'test.db' AS test} db2310 set sqlite_open_file_count311 expr $sqlite_open_file_count-($extrafds_postlock*2)312} {2}313 314# Sanity check: Create a table in ./test.db via handle db, and test that handle315# db2 can "see" the new table immediately. A handle using a separate pager316# cache would have to reload the database schema before this were possible.317#318do_test shared-$av.4.2.1 {319 execsql {320 CREATE TABLE abc(a, b, c);321 CREATE TABLE def(d, e, f);322 INSERT INTO abc VALUES('i', 'ii', 'iii');323 INSERT INTO def VALUES('I', 'II', 'III');324 }325} {}326do_test shared-$av.4.2.2 {327 execsql {328 SELECT * FROM test.abc;329 } db2330} {i ii iii}331 332# Open a read-transaction and read from table abc via handle 2. Check that333# handle 1 can read table abc. Check that handle 1 cannot modify table abc334# or the database schema. Then check that handle 1 can modify table def.335#336do_test shared-$av.4.3.1 {337 execsql {338 BEGIN;339 SELECT * FROM test.abc;340 } db2341} {i ii iii}342do_test shared-$av.4.3.2 {343 catchsql {344 INSERT INTO abc VALUES('iv', 'v', 'vi');345 }346} {1 {database table is locked: abc}}347do_test shared-$av.4.3.3 {348 catchsql {349 CREATE TABLE ghi(g, h, i);350 }351} {1 {database table is locked: sqlite_master}}352do_test shared-$av.4.3.3 {353 catchsql {354 INSERT INTO def VALUES('IV', 'V', 'VI');355 }356} {0 {}}357do_test shared-$av.4.3.4 {358 # Cleanup: commit the transaction opened by db2.359 execsql {360 COMMIT361 } db2362} {}363 364# Open a write-transaction using handle 1 and modify the database schema.365# Then try to execute a compiled statement to read from the same 366# database via handle 2 (fails to get the lock on sqlite_master). Also367# try to compile a read of the same database using handle 2 (also fails).368# Finally, compile a read of the other database using handle 2. This369# should also fail.370#371ifcapable compound {372 do_test shared-$av.4.4.1.2 {373 # Sanity check 1: Check that the schema is what we think it is when viewed374 # via handle 1.375 execsql {376 CREATE TABLE test2.ghi(g, h, i);377 SELECT 'test.db:'||name FROM sqlite_master 378 UNION ALL379 SELECT 'test2.db:'||name FROM test2.sqlite_master;380 }381 } {test.db:abc test.db:def test2.db:ghi}382 do_test shared-$av.4.4.1.2 {383 # Sanity check 2: Check that the schema is what we think it is when viewed384 # via handle 2.385 execsql {386 SELECT 'test2.db:'||name FROM sqlite_master 387 UNION ALL388 SELECT 'test.db:'||name FROM test.sqlite_master;389 } db2390 } {test2.db:ghi test.db:abc test.db:def}391}392 393do_test shared-$av.4.4.2 {394 set ::DB2 [sqlite3_connection_pointer db2]395 set sql {SELECT * FROM abc}396 set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]397 execsql {398 BEGIN;399 CREATE TABLE jkl(j, k, l);400 }401 sqlite3_step $::STMT1402} {SQLITE_ERROR}403do_test shared-$av.4.4.3 {404 sqlite3_finalize $::STMT1405} {SQLITE_LOCKED}406do_test shared-$av.4.4.4 {407 set rc [catch {408 set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]409 } msg]410 list $rc $msg411} {1 {(6) database schema is locked: test}}412do_test shared-$av.4.4.5 {413 set rc [catch {414 set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]415 } msg]416 list $rc $msg417} {1 {(6) database schema is locked: test}}418 419 420catch {db2 close}421catch {db close}422 423#--------------------------------------------------------------------------424# Tests shared-5.* 425#426foreach db [list test.db test1.db test2.db test3.db] {427 forcedelete $db ${db}-journal428}429do_test shared-$av.5.1.1 {430 sqlite3 db1 test.db431 sqlite3 db2 test.db432 execsql {433 ATTACH 'test1.db' AS test1;434 ATTACH 'test2.db' AS test2;435 ATTACH 'test3.db' AS test3;436 } db1437 execsql {438 ATTACH 'test3.db' AS test3;439 ATTACH 'test2.db' AS test2;440 ATTACH 'test1.db' AS test1;441 } db2442} {}443do_test shared-$av.5.1.2 {444 execsql {445 CREATE TABLE test1.t1(a, b);446 CREATE INDEX test1.i1 ON t1(a, b);447 } db1448} {}449ifcapable view {450 do_test shared-$av.5.1.3 {451 execsql {452 CREATE VIEW test1.v1 AS SELECT * FROM t1;453 } db1454 } {}455}456ifcapable trigger {457 do_test shared-$av.5.1.4 {458 execsql {459 CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN460 INSERT INTO t1 VALUES(new.a, new.b);461 END;462 } db1463 } {}464}465do_test shared-$av.5.1.5 {466 execsql {467 DROP INDEX i1;468 } db2469} {}470ifcapable view {471 do_test shared-$av.5.1.6 {472 execsql {473 DROP VIEW v1;474 } db2475 } {}476}477ifcapable trigger {478 do_test shared-$av.5.1.7 {479 execsql {480 DROP TRIGGER trig1;481 } db2482 } {}483}484do_test shared-$av.5.1.8 {485 execsql {486 DROP TABLE t1;487 } db2488} {}489ifcapable compound {490 do_test shared-$av.5.1.9 {491 execsql {492 SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master493 } db1494 } {}495}496 497#--------------------------------------------------------------------------498# Tests shared-6.* test that a query obtains all the read-locks it needs499# before starting execution of the query. This means that there is no chance500# some rows of data will be returned before a lock fails and SQLITE_LOCK501# is returned.502#503do_test shared-$av.6.1.1 {504 execsql {505 CREATE TABLE t1(a, b);506 CREATE TABLE t2(a, b);507 INSERT INTO t1 VALUES(1, 2);508 INSERT INTO t2 VALUES(3, 4);509 } db1510} {}511ifcapable compound {512 do_test shared-$av.6.1.2 {513 execsql {514 SELECT * FROM t1 UNION ALL SELECT * FROM t2;515 } db2516 } {1 2 3 4}517}518do_test shared-$av.6.1.3 {519 # Establish a write lock on table t2 via connection db2. Then make a 520 # UNION all query using connection db1 that first accesses t1, followed 521 # by t2. If the locks are grabbed at the start of the statement (as 522 # they should be), no rows are returned. If (as was previously the case)523 # they are grabbed as the tables are accessed, the t1 rows will be 524 # returned before the query fails.525 #526 execsql {527 BEGIN;528 INSERT INTO t2 VALUES(5, 6);529 } db2530 set ret [list]531 catch {532 db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {533 lappend ret $a $b534 }535 }536 set ret537} {}538do_test shared-$av.6.1.4 {539 execsql {540 COMMIT;541 BEGIN;542 INSERT INTO t1 VALUES(7, 8);543 } db2544 set ret [list]545 catch {546 db1 eval {547 SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;548 } {549 lappend ret $d550 }551 }552 set ret553} {}554 555catch {db1 close}556catch {db2 close}557foreach f [list test.db test2.db] {558 forcedelete $f ${f}-journal559}560 561#--------------------------------------------------------------------------562# Tests shared-7.* test auto-vacuum does not invalidate cursors from563# other shared-cache users when it reorganizes the database on 564# COMMIT.565#566do_test shared-$av.7.1 {567 # This test case sets up a test database in auto-vacuum mode consisting 568 # of two tables, t1 and t2. Both have a single index. Table t1 is 569 # populated first (so consists of pages toward the start of the db file), 570 # t2 second (pages toward the end of the file). 571 sqlite3 db test.db572 sqlite3 db2 test.db573 execsql {574 BEGIN;575 CREATE TABLE t1(a PRIMARY KEY, b);576 CREATE TABLE t2(a PRIMARY KEY, b);577 }578 set ::contents {}579 for {set i 0} {$i < 100} {incr i} {580 set a [string repeat "$i " 20]581 set b [string repeat "$i " 20]582 db eval {583 INSERT INTO t1 VALUES(:a, :b);584 }585 lappend ::contents [list [expr $i+1] $a $b]586 }587 execsql {588 INSERT INTO t2 SELECT * FROM t1;589 COMMIT;590 }591} {}592do_test shared-$av.7.2 {593 # This test case deletes the contents of table t1 (the one at the start of594 # the file) while many cursors are open on table t2 and its index. All of595 # the non-root pages will be moved from the end to the start of the file596 # when the DELETE is committed - this test verifies that moving the pages597 # does not disturb the open cursors.598 #599 600 proc lockrow {db tbl oids body} {601 set ret [list]602 db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {603 if {$i==[lindex $oids 0]} {604 set noids [lrange $oids 1 end]605 if {[llength $noids]==0} {606 set subret [eval $body]607 } else {608 set subret [lockrow $db $tbl $noids $body]609 }610 }611 lappend ret [list $i $a $b]612 }613 return [linsert $subret 0 $ret]614 }615 proc locktblrows {db tbl body} {616 set oids [db eval "SELECT oid FROM $tbl"]617 lockrow $db $tbl $oids $body618 }619 620 set scans [locktblrows db t2 {621 execsql {622 DELETE FROM t1;623 } db2624 }]625 set error 0626 627 # Test that each SELECT query returned the expected contents of t2.628 foreach s $scans {629 if {[lsort -integer -index 0 $s]!=$::contents} {630 set error 1631 }632 }633 set error634} {0}635 636catch {db close}637catch {db2 close}638unset -nocomplain contents639 640#--------------------------------------------------------------------------641# The following tests try to trick the shared-cache code into assuming642# the wrong encoding for a database.643#644forcedelete test.db test.db-journal645ifcapable utf16 {646 do_test shared-$av.8.1.1 {647 sqlite3 db test.db648 execsql {649 PRAGMA encoding = 'UTF-16';650 SELECT * FROM sqlite_master;651 }652 } {}653 do_test shared-$av.8.1.2 {654 string range [execsql {PRAGMA encoding;}] 0 end-2655 } {UTF-16}656 657 do_test shared-$av.8.1.3 {658 sqlite3 db2 test.db659 execsql {660 PRAGMA encoding = 'UTF-8';661 CREATE TABLE abc(a, b, c);662 } db2663 } {}664 do_test shared-$av.8.1.4 {665 execsql {666 SELECT * FROM sqlite_master;667 }668 } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"669 do_test shared-$av.8.1.5 {670 db2 close671 execsql {672 PRAGMA encoding;673 }674 } {UTF-8}675 676 forcedelete test2.db test2.db-journal677 do_test shared-$av.8.2.1 {678 execsql {679 ATTACH 'test2.db' AS aux;680 SELECT * FROM aux.sqlite_master;681 }682 } {}683 do_test shared-$av.8.2.2 {684 sqlite3 db2 test2.db685 execsql {686 PRAGMA encoding = 'UTF-16';687 CREATE TABLE def(d, e, f);688 } db2689 string range [execsql {PRAGMA encoding;} db2] 0 end-2690 } {UTF-16}691 692 catch {db close}693 catch {db2 close}694 forcedelete test.db test2.db695 696 do_test shared-$av.8.3.2 {697 sqlite3 db test.db698 execsql { CREATE TABLE def(d, e, f) }699 execsql { PRAGMA encoding }700 } {UTF-8}701 do_test shared-$av.8.3.3 {702 set zDb16 "[encoding convertto unicode test.db]\x00\x00"703 set db16 [sqlite3_open16 $zDb16 {}]704 705 set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]706 sqlite3_step $stmt707 set sql [sqlite3_column_text $stmt 0]708 sqlite3_finalize $stmt709 set sql710 } {CREATE TABLE def(d, e, f)}711 do_test shared-$av.8.3.4 {712 set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]713 sqlite3_step $stmt714 set enc [sqlite3_column_text $stmt 0]715 sqlite3_finalize $stmt716 set enc717 } {UTF-8}718 719 sqlite3_close $db16720 721# Bug #2547 is causing this to fail.722if 0 {723 do_test shared-$av.8.2.3 {724 catchsql {725 SELECT * FROM aux.sqlite_master;726 }727 } {1 {attached databases must use the same text encoding as main database}}728}729}730 731catch {db close}732catch {db2 close}733forcedelete test.db test2.db734 735#---------------------------------------------------------------------------736# The following tests - shared-9.* - test interactions between TEMP triggers737# and shared-schemas.738#739ifcapable trigger&&tempdb {740 741do_test shared-$av.9.1 {742 sqlite3 db test.db743 sqlite3 db2 test.db744 execsql {745 CREATE TABLE abc(a, b, c);746 CREATE TABLE abc_mirror(a, b, c);747 CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN 748 INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);749 END;750 INSERT INTO abc VALUES(1, 2, 3);751 SELECT * FROM abc_mirror;752 }753} {1 2 3}754do_test shared-$av.9.2 {755 execsql {756 INSERT INTO abc VALUES(4, 5, 6);757 SELECT * FROM abc_mirror;758 } db2759} {1 2 3}760do_test shared-$av.9.3 {761 db close762 db2 close763} {}764 765} ; # End shared-9.*766 767#---------------------------------------------------------------------------768# The following tests - shared-10.* - test that the library behaves 769# correctly when a connection to a shared-cache is closed. 770#771do_test shared-$av.10.1 {772 # Create a small sample database with two connections to it (db and db2).773 forcedelete test.db774 sqlite3 db test.db775 sqlite3 db2 test.db776 execsql {777 CREATE TABLE ab(a PRIMARY KEY, b);778 CREATE TABLE de(d PRIMARY KEY, e);779 INSERT INTO ab VALUES('Chiang Mai', 100000);780 INSERT INTO ab VALUES('Bangkok', 8000000);781 INSERT INTO de VALUES('Ubon', 120000);782 INSERT INTO de VALUES('Khon Kaen', 200000);783 }784} {}785do_test shared-$av.10.2 {786 # Open a read-transaction with the first connection, a write-transaction787 # with the second.788 execsql {789 BEGIN;790 SELECT * FROM ab;791 }792 execsql {793 BEGIN;794 INSERT INTO de VALUES('Pataya', 30000);795 } db2796} {}797do_test shared-$av.10.3 {798 # An external connection should be able to read the database, but not799 # prepare a write operation.800 if {$::tcl_platform(platform) eq "unix"} {801 sqlite3 db3 "file:test.db?cache=private" -uri 1802 } else {803 sqlite3 db3 TEST.DB804 }805 execsql {806 SELECT * FROM ab;807 } db3808 catchsql {809 BEGIN;810 INSERT INTO de VALUES('Pataya', 30000);811 } db3812} {1 {database is locked}}813do_test shared-$av.10.4 {814 # Close the connection with the write-transaction open815 db2 close816} {}817do_test shared-$av.10.5 {818 # Test that the db2 transaction has been automatically rolled back.819 # If it has not the ('Pataya', 30000) entry will still be in the table.820 execsql {821 SELECT * FROM de;822 }823} {Ubon 120000 {Khon Kaen} 200000}824do_test shared-$av.10.5 {825 # Closing db2 should have dropped the shared-cache back to a read-lock.826 # So db3 should be able to prepare a write...827 catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3828} {0 {}}829do_test shared-$av.10.6 {830 # ... but not commit it.831 catchsql {COMMIT} db3832} {1 {database is locked}}833do_test shared-$av.10.7 {834 # Commit the (read-only) db transaction. Check via db3 to make sure the 835 # contents of table "de" are still as they should be.836 execsql {837 COMMIT;838 }839 execsql {840 SELECT * FROM de;841 } db3842} {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}843do_test shared-$av.10.9 {844 # Commit the external transaction.845 catchsql {COMMIT} db3846} {0 {}}847integrity_check shared-$av.10.10848do_test shared-$av.10.11 {849 db close850 db3 close851} {}852 853do_test shared-$av.11.1 {854 forcedelete test.db855 sqlite3 db test.db856 sqlite3 db2 test.db857 execsql {858 CREATE TABLE abc(a, b, c);859 CREATE TABLE abc2(a, b, c);860 BEGIN;861 INSERT INTO abc VALUES(1, 2, 3);862 }863} {}864do_test shared-$av.11.2 {865 catchsql {BEGIN;} db2866 catchsql {SELECT * FROM abc;} db2867} {1 {database table is locked: abc}}868do_test shared-$av.11.3 {869 catchsql {BEGIN} db2870} {1 {cannot start a transaction within a transaction}}871do_test shared-$av.11.4 {872 catchsql {SELECT * FROM abc2;} db2873} {0 {}}874do_test shared-$av.11.5 {875 catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2876} {1 {database table is locked}}877do_test shared-$av.11.6 {878 catchsql {SELECT * FROM abc2}879} {0 {}}880do_test shared-$av.11.6 {881 execsql {882 ROLLBACK;883 PRAGMA read_uncommitted = 1;884 } db2885} {}886do_test shared-$av.11.7 {887 execsql {888 INSERT INTO abc2 VALUES(4, 5, 6);889 INSERT INTO abc2 VALUES(7, 8, 9);890 }891} {}892do_test shared-$av.11.8 {893 set res [list]894 db2 eval {895 SELECT abc.a as I, abc2.a as II FROM abc, abc2;896 } {897 execsql {898 DELETE FROM abc WHERE 1;899 }900 lappend res $I $II901 }902 set res903} {1 4 {} 7}904if {[llength [info command sqlite3_shared_cache_report]]==1} {905 ifcapable curdir {906 do_test shared-$av.11.9 {907 string tolower [sqlite3_shared_cache_report]908 } [string tolower [list [file nativename [file normalize test.db]] 2]]909 }910}911 912do_test shared-$av.11.11 {913 db close914 db2 close915} {}916 917# This tests that if it is impossible to free any pages, SQLite will918# exceed the limit set by PRAGMA cache_size.919forcedelete test.db test.db-journal920sqlite3 db test.db 921ifcapable pager_pragmas {922 do_test shared-$av.12.1 {923 execsql {924 PRAGMA cache_size = 10;925 PRAGMA cache_size;926 }927 } {10}928}929do_test shared-$av.12.2 {930 set ::db_handles [list]931 for {set i 1} {$i < 15} {incr i} {932 lappend ::db_handles db$i933 sqlite3 db$i test.db 934 execsql "CREATE TABLE db${i}(a, b, c)" db$i 935 execsql "INSERT INTO db${i} VALUES(1, 2, 3)"936 }937} {}938proc nested_select {handles} {939 [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" {940 lappend ::res $a $b $c941 if {[llength $handles]>1} {942 nested_select [lrange $handles 1 end]943 }944 }945}946do_test shared-$av.12.3 {947 set ::res [list]948 nested_select $::db_handles949 set ::res950} [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1]951 952do_test shared-$av.12.X {953 db close954 foreach h $::db_handles { 955 $h close956 }957} {}958 959# Internally, locks are acquired on shared B-Tree structures in the order960# that the structures appear in the virtual memory address space. This961# test case attempts to cause the order of the structures in memory 962# to be different from the order in which they are attached to a given963# database handle. This covers an extra line or two.964#965do_test shared-$av.13.1 {966 forcedelete test2.db test3.db test4.db test5.db967 sqlite3 db :memory:968 execsql {969 ATTACH 'test2.db' AS aux2;970 ATTACH 'test3.db' AS aux3;971 ATTACH 'test4.db' AS aux4;972 ATTACH 'test5.db' AS aux5;973 DETACH aux2;974 DETACH aux3;975 DETACH aux4;976 ATTACH 'test2.db' AS aux2;977 ATTACH 'test3.db' AS aux3;978 ATTACH 'test4.db' AS aux4;979 }980} {}981do_test shared-$av.13.2 {982 execsql {983 CREATE TABLE t1(a, b, c);984 CREATE TABLE aux2.t2(a, b, c);985 CREATE TABLE aux3.t3(a, b, c);986 CREATE TABLE aux4.t4(a, b, c);987 CREATE TABLE aux5.t5(a, b, c);988 SELECT count(*) FROM 989 aux2.sqlite_master, 990 aux3.sqlite_master, 991 aux4.sqlite_master, 992 aux5.sqlite_master993 }994} {1}995do_test shared-$av.13.3 {996 db close997} {}998 999# Test that nothing horrible happens if a connection to a shared B-Tree 1000# structure is closed while some other connection has an open cursor.1001#1002do_test shared-$av.14.1 {1003 sqlite3 db test.db1004 sqlite3 db2 test.db1005 execsql {SELECT name FROM sqlite_master}1006} {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}1007do_test shared-$av.14.2 {1008 set res [list]1009 db eval {SELECT name FROM sqlite_master} {1010 if {$name eq "db7"} {1011 db2 close1012 }1013 lappend res $name1014 }1015 set res1016} {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}1017do_test shared-$av.14.3 {1018 db close1019} {}1020 1021# Populate a database schema using connection [db]. Then drop it using1022# [db2]. This is to try to find any points where shared-schema elements1023# are allocated using the lookaside buffer of [db].1024# 1025# Mutexes are enabled for this test as that activates a couple of useful1026# assert() statements in the C code.1027#1028do_test shared-$av-15.1 {1029 forcedelete test.db1030 sqlite3 db test.db -fullmutex 11031 sqlite3 db2 test.db -fullmutex 11032 execsql {1033 CREATE TABLE t1(a, b, c);1034 CREATE INDEX i1 ON t1(a, b);1035 CREATE VIEW v1 AS SELECT * FROM t1; 1036 CREATE VIEW v2 AS SELECT * FROM t1, v1 1037 WHERE t1.c=v1.c GROUP BY t1.a ORDER BY v1.b; 1038 CREATE TRIGGER tr1 AFTER INSERT ON t1 1039 WHEN new.a!=11040 BEGIN1041 DELETE FROM t1 WHERE a=5;1042 INSERT INTO t1 VALUES(1, 2, 3);1043 UPDATE t1 SET c=c+1;1044 END;1045 1046 INSERT INTO t1 VALUES(5, 6, 7);1047 INSERT INTO t1 VALUES(8, 9, 10);1048 INSERT INTO t1 VALUES(11, 12, 13);1049 ANALYZE;1050 SELECT * FROM t1;1051 }1052} {1 2 6 8 9 12 1 2 5 11 12 14 1 2 4}1053do_test shared-$av-15.2 {1054 execsql { DROP TABLE t1 } db21055} {}1056db close1057db2 close1058 1059# Shared cache on a :memory: database. This only works for URI filenames.1060#1061do_test shared-$av-16.1 {1062 sqlite3 db1 file::memory: -uri 11063 sqlite3 db2 file::memory: -uri 11064 db1 eval {1065 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3);1066 }1067 db2 eval {1068 SELECT x FROM t1 ORDER BY x;1069 }1070} {1 2 3}1071do_test shared-$av-16.2 {1072 db2 eval {1073 INSERT INTO t1 VALUES(99);1074 DELETE FROM t1 WHERE x=2;1075 }1076 db1 eval {1077 SELECT x FROM t1 ORDER BY x;1078 }1079} {1 3 99}1080 1081# Verify that there is no cache sharing ordinary (non-URI) filenames are1082# used.1083#1084do_test shared-$av-16.3 {1085 db1 close1086 db2 close1087 sqlite3 db1 :memory:1088 sqlite3 db2 :memory:1089 db1 eval {1090 CREATE TABLE t1(x); INSERT INTO t1 VALUES(4),(5),(6);1091 }1092 catchsql {1093 SELECT * FROM t1;1094 } db21095} {1 {no such table: t1}}1096 1097# Shared cache on named memory databases.1098#1099do_test shared-$av-16.4 {1100 db1 close1101 db2 close1102 forcedelete test.db test.db-wal test.db-journal1103 sqlite3 db1 file:test.db?mode=memory -uri 11104 sqlite3 db2 file:test.db?mode=memory -uri 11105 db1 eval {1106 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3);1107 }1108 db2 eval {1109 SELECT x FROM t1 ORDER BY x;1110 }1111} {1 2 3}1112do_test shared-$av-16.5 {1113 db2 eval {1114 INSERT INTO t1 VALUES(99);1115 DELETE FROM t1 WHERE x=2;1116 }1117 db1 eval {1118 SELECT x FROM t1 ORDER BY x;1119 }1120} {1 3 99}1121do_test shared-$av-16.6 {1122 file exists test.db1123} {0} ;# Verify that the database is in-memory1124 1125# Shared cache on named memory databases with different names.1126#1127do_test shared-$av-16.7 {1128 db1 close1129 db2 close1130 forcedelete test1.db test2.db1131 sqlite3 db1 file:test1.db?mode=memory -uri 11132 sqlite3 db2 file:test2.db?mode=memory -uri 11133 db1 eval {1134 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3);1135 }1136 catchsql {1137 SELECT x FROM t1 ORDER BY x;1138 } db21139} {1 {no such table: t1}}1140do_test shared-$av-16.8 {1141 file exists test1.db1142} {0} ;# Verify that the database is in-memory1143 1144# Shared cache on named memory databases attached to readonly connections.1145#1146if {![sqlite3 -has-codec]} {1147 do_test shared-$av-16.8.1 {1148 db1 close1149 db2 close1150 1151 sqlite3 db test1.db1152 db eval { 1153 CREATE TABLE yy(a, b);1154 INSERT INTO yy VALUES(77, 88);1155 }1156 db close1157 1158 sqlite3 db1 test1.db -uri 1 -readonly 11159 sqlite3 db2 test2.db -uri 1 1160 1161 db1 eval { 1162 ATTACH 'file:mem?mode=memory&cache=shared' AS shared; 1163 CREATE TABLE shared.xx(a, b);1164 INSERT INTO xx VALUES(55, 66);1165 }1166 db2 eval { 1167 ATTACH 'file:mem?mode=memory&cache=shared' AS shared;1168 SELECT * FROM xx;1169 }1170 } {55 66}1171 1172 do_test shared-$av-16.8.2 { db1 eval { SELECT * FROM yy } } {77 88}1173 do_test shared-$av-16.8.3 { 1174 list [catch {db1 eval { INSERT INTO yy VALUES(1, 2) }} msg] $msg1175 } {1 {attempt to write a readonly database}}1176 1177 db1 close1178 db2 close1179}1180 1181} ;# end of autovacuum on/off loop1182 1183sqlite3_enable_shared_cache $::enable_shared_cache1184finish_test1185