# 2003 July 1 # # 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 this script is testing the ATTACH and DETACH commands # and related functionality. # # $Id: attach2.test,v 1.14 2004/06/09 17:37:29 drh Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # Ticket #354 # # Databases test.db and test2.db contain identical schemas. Make # sure we can attach test2.db from test.db. # do_test attach2-1.1 { db eval { CREATE TABLE t1(a,b); CREATE INDEX x1 ON t1(a); } file delete -force test2.db file delete -force test2.db-journal sqlite db2 test2.db db2 eval { CREATE TABLE t1(a,b); CREATE INDEX x1 ON t1(a); } catchsql { ATTACH 'test2.db' AS t2; } } {0 {}} # Ticket #514 # proc db_list {db} { set list {} foreach {idx name file} [execsql {PRAGMA database_list} $db] { lappend list $idx $name } return $list } db eval {DETACH t2} do_test attach2-2.1 { # lock test2.db then try to attach it. This is no longer an error because # db2 just RESERVES the database. It does not obtain a write-lock until # we COMMIT. db2 eval {BEGIN} db2 eval {UPDATE t1 SET a = 0 WHERE 0} catchsql { ATTACH 'test2.db' AS t2; } } {0 {}} do_test attach2-2.2 { # make sure test2.db did get attached. db_list db } {0 main 1 temp 2 t2} db2 eval {COMMIT} do_test attach2-2.5 { # Make sure we can read test2.db from db catchsql { SELECT name FROM t2.sqlite_master; } } {0 {t1 x1}} do_test attach2-2.6 { # lock test2.db and try to read from it. This should still work because # the lock is only a RESERVED lock which does not prevent reading. # db2 eval BEGIN db2 eval {UPDATE t1 SET a = 0 WHERE 0} catchsql { SELECT name FROM t2.sqlite_master; } } {0 {t1 x1}} do_test attach2-2.7 { # but we can still read from test1.db even though test2.db is locked. catchsql { SELECT name FROM main.sqlite_master; } } {0 {t1 x1}} do_test attach2-2.8 { # start a transaction on test.db even though test2.db is locked. catchsql { BEGIN; INSERT INTO t1 VALUES(8,9); } } {0 {}} do_test attach2-2.9 { execsql { SELECT * FROM t1 } } {8 9} do_test attach2-2.10 { # now try to write to test2.db. the write should fail catchsql { INSERT INTO t2.t1 VALUES(1,2); } } {1 {database is locked}} do_test attach2-2.11 { # when the write failed in the previous test, the transaction should # have rolled back. # # Update for version 3: A transaction is no longer rolled back if a # database is found to be busy. execsql {rollback} db2 eval ROLLBACK execsql { SELECT * FROM t1 } } {} do_test attach2-2.12 { catchsql { COMMIT } } {1 {cannot commit - no transaction is active}} # Ticket #574: Make sure it works using the non-callback API # do_test attach2-3.1 { db close set DB [sqlite db test.db] set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM] if {$rc} {lappend rc $VM} sqlite3_finalize $VM set rc } {0} do_test attach2-3.2 { set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM] if {$rc} {lappend rc $VM} sqlite3_finalize $VM set rc } {0} db close for {set i 2} {$i<=15} {incr i} { catch {db$i close} } # A procedure to verify the status of locks on a database. # proc lock_status {testnum db expected_result} { do_test attach2-$testnum [subst { execsql {PRAGMA lock_status} $db }] $expected_result } set sqlite_os_trace 0 # Tests attach2-4.* test that read-locks work correctly with attached # databases. do_test attach2-4.1 { sqlite db test.db sqlite db2 test.db execsql {ATTACH 'test2.db' as file2} execsql {ATTACH 'test2.db' as file2} db2 } {} lock_status 4.1.1 db {main unlocked temp unlocked file2 unlocked} lock_status 4.1.2 db2 {main unlocked temp unlocked file2 unlocked} do_test attach2-4.2 { # Handle 'db' read-locks test.db execsql {BEGIN} execsql {SELECT * FROM t1} # Lock status: # db - shared(main) # db2 - } {} lock_status 4.2.1 db {main shared temp shared file2 unlocked} lock_status 4.2.2 db2 {main unlocked temp unlocked file2 unlocked} do_test attach2-4.3 { # The read lock held by db does not prevent db2 from reading test.db execsql {SELECT * FROM t1} db2 } {} lock_status 4.3.1 db {main shared temp shared file2 unlocked} lock_status 4.3.2 db2 {main unlocked temp unlocked file2 unlocked} do_test attach2-4.4 { # db is holding a read lock on test.db, so we should not be able # to commit a write to test.db from db2 catchsql { INSERT INTO t1 VALUES(1, 2) } db2 } {1 {database is locked}} lock_status 4.4.1 db {main shared temp shared file2 unlocked} lock_status 4.4.2 db2 {main unlocked temp unlocked file2 unlocked} do_test attach2-4.5 { # Handle 'db2' reserves file2. execsql {BEGIN} db2 execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2 # Lock status: # db - shared(main) # db2 - reserved(file2) } {} lock_status 4.5.1 db {main shared temp shared file2 unlocked} lock_status 4.5.2 db2 {main unlocked temp reserved file2 reserved} do_test attach2-4.6.1 { # Reads are allowed against a reserved database. catchsql { SELECT * FROM file2.t1; } # Lock status: # db - shared(main), shared(file2) # db2 - reserved(file2) } {0 {}} lock_status 4.6.1.1 db {main shared temp shared file2 shared} lock_status 4.6.1.2 db2 {main unlocked temp reserved file2 reserved} do_test attach2-4.6.2 { # Writes against a reserved database are not allowed. catchsql { UPDATE file2.t1 SET a=0; } } {1 {database is locked}} lock_status 4.6.2.1 db {main shared temp reserved file2 shared} lock_status 4.6.2.2 db2 {main unlocked temp reserved file2 reserved} do_test attach2-4.7 { # Ensure handle 'db' retains the lock on the main file after # failing to obtain a write-lock on file2. catchsql { INSERT INTO t1 VALUES(1, 2) } db2 } {0 {}} lock_status 4.7.1 db {main shared temp reserved file2 shared} lock_status 4.7.2 db2 {main reserved temp reserved file2 reserved} do_test attach2-4.8 { # We should still be able to read test.db from db2 execsql {SELECT * FROM t1} db2 } {1 2} lock_status 4.8.1 db {main shared temp reserved file2 shared} lock_status 4.8.2 db2 {main reserved temp reserved file2 reserved} do_test attach2-4.9 { # Try to upgrade the handle 'db' lock. catchsql { INSERT INTO t1 VALUES(1, 2) } } {1 {database is locked}} lock_status 4.9.1 db {main shared temp reserved file2 shared} lock_status 4.9.2 db2 {main reserved temp reserved file2 reserved} do_test attach2-4.10 { # Release the locks held by handle 'db2' execsql {COMMIT} db2 } {} lock_status 4.10.1 db {main shared temp reserved file2 shared} lock_status 4.10.2 db2 {main unlocked temp unlocked file2 unlocked} do_test attach2-4.11 { execsql {SELECT * FROM file2.t1} } {1 2} do_test attach2-4.12 { execsql {INSERT INTO t1 VALUES(1, 2)} } {} do_test attach2-4.13 { # Release the locks held by handle 'db' execsql {ROLLBACK} } {} do_test attach2-4.14 { execsql {SELECT * FROM t1} db2 } {} db close db2 close file delete -force test2.db finish_test