# 2007 March 24 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The focus # of these tests is exclusive access mode (i.e. the thing activated by # "PRAGMA locking_mode = EXCLUSIVE"). # # $Id: exclusive.test,v 1.8 2008/04/17 14:16:42 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!pager_pragmas} { finish_test return } file delete -force test2.db-journal file delete -force test2.db file delete -force test3.db-journal file delete -force test3.db file delete -force test4.db-journal file delete -force test4.db # The locking mode for the TEMP table is always "exclusive" for # on-disk tables and "normal" for in-memory tables. # if {[info exists TEMP_STORE] && $TEMP_STORE>=2} { set temp_mode normal } else { set temp_mode exclusive } #---------------------------------------------------------------------- # Test cases exclusive-1.X test the PRAGMA logic. # do_test exclusive-1.0 { execsql { pragma locking_mode; pragma main.locking_mode; pragma temp.locking_mode; } } [list normal normal $temp_mode] do_test exclusive-1.1 { execsql { pragma locking_mode = exclusive; } } {exclusive} do_test exclusive-1.2 { execsql { pragma locking_mode; pragma main.locking_mode; pragma temp.locking_mode; } } [list exclusive exclusive $temp_mode] do_test exclusive-1.3 { execsql { pragma locking_mode = normal; } } {normal} do_test exclusive-1.4 { execsql { pragma locking_mode; pragma main.locking_mode; pragma temp.locking_mode; } } [list normal normal $temp_mode] do_test exclusive-1.5 { execsql { pragma locking_mode = invalid; } } {normal} do_test exclusive-1.6 { execsql { pragma locking_mode; pragma main.locking_mode; pragma temp.locking_mode; } } [list normal normal $temp_mode] ifcapable attach { do_test exclusive-1.7 { execsql { pragma locking_mode = exclusive; ATTACH 'test2.db' as aux; } execsql { pragma main.locking_mode; pragma aux.locking_mode; } } {exclusive exclusive} do_test exclusive-1.8 { execsql { pragma main.locking_mode = normal; } execsql { pragma main.locking_mode; pragma temp.locking_mode; pragma aux.locking_mode; } } [list normal $temp_mode exclusive] do_test exclusive-1.9 { execsql { pragma locking_mode; } } {exclusive} do_test exclusive-1.10 { execsql { ATTACH 'test3.db' as aux2; } execsql { pragma main.locking_mode; pragma aux.locking_mode; pragma aux2.locking_mode; } } {normal exclusive exclusive} do_test exclusive-1.11 { execsql { pragma aux.locking_mode = normal; } execsql { pragma main.locking_mode; pragma aux.locking_mode; pragma aux2.locking_mode; } } {normal normal exclusive} do_test exclusive-1.12 { execsql { pragma locking_mode = normal; } execsql { pragma main.locking_mode; pragma temp.locking_mode; pragma aux.locking_mode; pragma aux2.locking_mode; } } [list normal $temp_mode normal normal] do_test exclusive-1.13 { execsql { ATTACH 'test4.db' as aux3; } execsql { pragma main.locking_mode; pragma temp.locking_mode; pragma aux.locking_mode; pragma aux2.locking_mode; pragma aux3.locking_mode; } } [list normal $temp_mode normal normal normal] do_test exclusive-1.99 { execsql { DETACH aux; DETACH aux2; DETACH aux3; } } {} } #---------------------------------------------------------------------- # Test cases exclusive-2.X verify that connections in exclusive # locking_mode do not relinquish locks. # do_test exclusive-2.0 { execsql { CREATE TABLE abc(a, b, c); INSERT INTO abc VALUES(1, 2, 3); PRAGMA locking_mode = exclusive; } } {exclusive} do_test exclusive-2.1 { sqlite3 db2 test.db execsql { INSERT INTO abc VALUES(4, 5, 6); SELECT * FROM abc; } db2 } {1 2 3 4 5 6} do_test exclusive-2.2 { # This causes connection 'db' (in exclusive mode) to establish # a shared-lock on the db. The other connection should now be # locked out as a writer. execsql { SELECT * FROM abc; } db } {1 2 3 4 5 6} do_test exclusive-2.4 { execsql { SELECT * FROM abc; } db2 } {1 2 3 4 5 6} do_test exclusive-2.5 { catchsql { INSERT INTO abc VALUES(7, 8, 9); } db2 } {1 {database is locked}} sqlite3_soft_heap_limit 0 do_test exclusive-2.6 { # Because connection 'db' only has a shared-lock, the other connection # will be able to get a RESERVED, but will fail to upgrade to EXCLUSIVE. execsql { BEGIN; INSERT INTO abc VALUES(7, 8, 9); } db2 catchsql { COMMIT } db2 } {1 {database is locked}} do_test exclusive-2.7 { catchsql { COMMIT } db2 } {1 {database is locked}} do_test exclusive-2.8 { execsql { ROLLBACK; } db2 } {} sqlite3_soft_heap_limit $soft_limit do_test exclusive-2.9 { # Write the database to establish the exclusive lock with connection 'db. execsql { INSERT INTO abc VALUES(7, 8, 9); } db catchsql { SELECT * FROM abc; } db2 } {1 {database is locked}} do_test exclusive-2.10 { # Changing the locking-mode does not release any locks. execsql { PRAGMA locking_mode = normal; } db catchsql { SELECT * FROM abc; } db2 } {1 {database is locked}} do_test exclusive-2.11 { # After changing the locking mode, accessing the db releases locks. execsql { SELECT * FROM abc; } db execsql { SELECT * FROM abc; } db2 } {1 2 3 4 5 6 7 8 9} db2 close #---------------------------------------------------------------------- # Tests exclusive-3.X - test that a connection in exclusive mode # truncates instead of deletes the journal file when committing # a transaction. # proc filestate {fname} { set exists 0 set content 0 if {[file exists $fname]} { set exists 1 set hdr [hexio_read $fname 0 28] set content \ [expr {$hdr!="00000000000000000000000000000000000000000000000000000000"}] } list $exists $content } do_test exclusive-3.0 { filestate test.db-journal } {0 0} do_test exclusive-3.1 { execsql { PRAGMA locking_mode = exclusive; BEGIN; DELETE FROM abc; } filestate test.db-journal } {1 1} do_test exclusive-3.2 { execsql { COMMIT; } filestate test.db-journal } {1 0} do_test exclusive-3.3 { execsql { INSERT INTO abc VALUES('A', 'B', 'C'); SELECT * FROM abc; } } {A B C} do_test exclusive-3.4 { execsql { BEGIN; UPDATE abc SET a = 1, b = 2, c = 3; ROLLBACK; SELECT * FROM abc; } } {A B C} do_test exclusive-3.5 { filestate test.db-journal } {1 0} do_test exclusive-3.6 { execsql { PRAGMA locking_mode = normal; SELECT * FROM abc; } filestate test.db-journal } {0 0} #---------------------------------------------------------------------- # Tests exclusive-4.X - test that rollback works correctly when # in exclusive-access mode. # # The following procedure computes a "signature" for table "t3". If # T3 changes in any way, the signature should change. # # This is used to test ROLLBACK. We gather a signature for t3, then # make lots of changes to t3, then rollback and take another signature. # The two signatures should be the same. # proc signature {} { return [db eval {SELECT count(*), md5sum(x) FROM t3}] } do_test exclusive-4.0 { execsql { PRAGMA locking_mode = exclusive; } execsql { PRAGMA default_cache_size = 10; } execsql { BEGIN; CREATE TABLE t3(x TEXT); INSERT INTO t3 VALUES(randstr(10,400)); INSERT INTO t3 VALUES(randstr(10,400)); INSERT INTO t3 SELECT randstr(10,400) FROM t3; INSERT INTO t3 SELECT randstr(10,400) FROM t3; INSERT INTO t3 SELECT randstr(10,400) FROM t3; INSERT INTO t3 SELECT randstr(10,400) FROM t3; COMMIT; } execsql {SELECT count(*) FROM t3;} } {32} set ::X [signature] do_test exclusive-4.1 { execsql { BEGIN; DELETE FROM t3 WHERE random()%10!=0; INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; SELECT count(*) FROM t3; ROLLBACK; } signature } $::X do_test exclusive-4.2 { execsql { BEGIN; DELETE FROM t3 WHERE random()%10!=0; INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; DELETE FROM t3 WHERE random()%10!=0; INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; ROLLBACK; } signature } $::X do_test exclusive-4.3 { execsql { INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; } } {} do_test exclusive-4.4 { catch {set ::X [signature]} } {0} do_test exclusive-4.5 { execsql { PRAGMA locking_mode = NORMAL; DROP TABLE t3; DROP TABLE abc; } } {normal} #---------------------------------------------------------------------- # Tests exclusive-5.X - test that statement journals are truncated # instead of deleted when in exclusive access mode. # # Close and reopen the database so that the temp database is no # longer active. # db close sqlite db test.db do_test exclusive-5.0 { execsql { CREATE TABLE abc(a UNIQUE, b UNIQUE, c UNIQUE); BEGIN; INSERT INTO abc VALUES(1, 2, 3); INSERT INTO abc SELECT a+1, b+1, c+1 FROM abc; } } {} do_test exclusive-5.1 { # Three files are open: The db, journal and statement-journal. set sqlite_open_file_count } {3} do_test exclusive-5.2 { execsql { COMMIT; } # One file open: the db. set sqlite_open_file_count } {1} do_test exclusive-5.3 { execsql { PRAGMA locking_mode = exclusive; BEGIN; INSERT INTO abc VALUES(5, 6, 7); } # Two files open: the db and journal. set sqlite_open_file_count } {2} do_test exclusive-5.4 { execsql { INSERT INTO abc SELECT a+10, b+10, c+10 FROM abc; } # Three files are open: The db, journal and statement-journal. set sqlite_open_file_count } {3} do_test exclusive-5.5 { execsql { COMMIT; } # Three files are still open: The db, journal and statement-journal. set sqlite_open_file_count } {3} do_test exclusive-5.6 { execsql { PRAGMA locking_mode = normal; SELECT * FROM abc; } } {normal 1 2 3 2 3 4 5 6 7 11 12 13 12 13 14 15 16 17} do_test exclusive-5.7 { # Just the db open. set sqlite_open_file_count } {1} finish_test