# 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.12 2004/06/07 16:27:47 drh Exp $
#
set sqlite_os_trace 0
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}
}
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
} {}
do_test attach2-4.2 {
# Handle 'db' read-locks test.db
execsql {BEGIN}
execsql {SELECT * FROM t1}
} {}
do_test attach2-4.3 {
# The read lock held by db does not prevent db2 from reading test.db
execsql {SELECT * FROM t1} db2
} {}
do_test attach2-4.4 {
# db is only holding a read lock on test.db, so we should not be able
# to commit a write to test.db from db2
set r [catch {
execsql {
INSERT INTO t1 VALUES(1, 2)
} db2
} msg]
list $r $msg
} {1 {database is locked}}
do_test attach2-4.5 {
# Handle 'db2' reserves file2.
execsql {BEGIN} db2
execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2
} {}
do_test attach2-4.6.1 {
# Reads are allowed against a reserved database.
catchsql {
SELECT * FROM file2.t1;
}
} {0 {}}
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}}
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
} {1 {database is locked}}
do_test attach2-4.8 {
# Read lock the main file with db2. Now both db and db2 have a read lock
# on the main file, db2 has a write-lock on file2.
execsql {SELECT * FROM t1} db2
} {}
do_test attach2-4.9 {
# Try to upgrade the handle 'db' lock.
catchsql {
INSERT INTO t1 VALUES(1, 2)
}
list $r $msg
} {1 {database is locked}}
do_test attach2-4.10 {
# Release the locks held by handle 'db2'
execsql {COMMIT} db2
} {}
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