000001 # 2003 July 1
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this script is testing the ATTACH and DETACH commands
000013 # and related functionality.
000014 #
000015 # $Id: attach2.test,v 1.38 2007/12/13 21:54:11 drh Exp $
000016 #
000017
000018 set testdir [file dirname $argv0]
000019 source $testdir/tester.tcl
000020
000021 ifcapable !attach {
000022 finish_test
000023 return
000024 }
000025
000026 # Ticket #354
000027 #
000028 # Databases test.db and test2.db contain identical schemas. Make
000029 # sure we can attach test2.db from test.db.
000030 #
000031 do_test attach2-1.1 {
000032 db eval {
000033 CREATE TABLE t1(a,b);
000034 CREATE INDEX x1 ON t1(a);
000035 }
000036 forcedelete test2.db
000037 forcedelete test2.db-journal
000038 sqlite3 db2 test2.db
000039 db2 eval {
000040 CREATE TABLE t1(a,b);
000041 CREATE INDEX x1 ON t1(a);
000042 }
000043 catchsql {
000044 ATTACH 'test2.db' AS t2;
000045 }
000046 } {0 {}}
000047
000048 # Ticket #514
000049 #
000050 proc db_list {db} {
000051 set list {}
000052 foreach {idx name file} [execsql {PRAGMA database_list} $db] {
000053 lappend list $idx $name
000054 }
000055 return $list
000056 }
000057 db eval {DETACH t2}
000058 do_test attach2-2.1 {
000059 # lock test2.db then try to attach it. This is no longer an error because
000060 # db2 just RESERVES the database. It does not obtain a write-lock until
000061 # we COMMIT.
000062 db2 eval {BEGIN}
000063 db2 eval {UPDATE t1 SET a = 0 WHERE 0}
000064 catchsql {
000065 ATTACH 'test2.db' AS t2;
000066 }
000067 } {0 {}}
000068 ifcapable schema_pragmas {
000069 do_test attach2-2.2 {
000070 # make sure test2.db did get attached.
000071 db_list db
000072 } {0 main 2 t2}
000073 } ;# ifcapable schema_pragmas
000074 db2 eval {COMMIT}
000075
000076 do_test attach2-2.5 {
000077 # Make sure we can read test2.db from db
000078 catchsql {
000079 SELECT name FROM t2.sqlite_master;
000080 }
000081 } {0 {t1 x1}}
000082 do_test attach2-2.6 {
000083 # lock test2.db and try to read from it. This should still work because
000084 # the lock is only a RESERVED lock which does not prevent reading.
000085 #
000086 db2 eval BEGIN
000087 db2 eval {UPDATE t1 SET a = 0 WHERE 0}
000088 catchsql {
000089 SELECT name FROM t2.sqlite_master;
000090 }
000091 } {0 {t1 x1}}
000092 do_test attach2-2.7 {
000093 # but we can still read from test1.db even though test2.db is locked.
000094 catchsql {
000095 SELECT name FROM main.sqlite_master;
000096 }
000097 } {0 {t1 x1}}
000098 do_test attach2-2.8 {
000099 # start a transaction on test.db even though test2.db is locked.
000100 catchsql {
000101 BEGIN;
000102 INSERT INTO t1 VALUES(8,9);
000103 }
000104 } {0 {}}
000105 do_test attach2-2.9 {
000106 execsql {
000107 SELECT * FROM t1
000108 }
000109 } {8 9}
000110 do_test attach2-2.10 {
000111 # now try to write to test2.db. the write should fail
000112 catchsql {
000113 INSERT INTO t2.t1 VALUES(1,2);
000114 }
000115 } {1 {database is locked}}
000116 do_test attach2-2.11 {
000117 # when the write failed in the previous test, the transaction should
000118 # have rolled back.
000119 #
000120 # Update for version 3: A transaction is no longer rolled back if a
000121 # database is found to be busy.
000122 execsql {rollback}
000123 db2 eval ROLLBACK
000124 execsql {
000125 SELECT * FROM t1
000126 }
000127 } {}
000128 do_test attach2-2.12 {
000129 catchsql {
000130 COMMIT
000131 }
000132 } {1 {cannot commit - no transaction is active}}
000133
000134 # Ticket #574: Make sure it works using the non-callback API
000135 #
000136 do_test attach2-3.1 {
000137 set DB [sqlite3_connection_pointer db]
000138 set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM]
000139 if {$rc} {lappend rc $VM}
000140 sqlite3_step $VM
000141 sqlite3_finalize $VM
000142 set rc
000143 } {0}
000144 do_test attach2-3.2 {
000145 set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM]
000146 if {$rc} {lappend rc $VM}
000147 sqlite3_step $VM
000148 sqlite3_finalize $VM
000149 set rc
000150 } {0}
000151
000152 db close
000153 for {set i 2} {$i<=15} {incr i} {
000154 catch {db$i close}
000155 }
000156
000157 # A procedure to verify the status of locks on a database.
000158 #
000159 proc lock_status {testnum db expected_result} {
000160 # If the database was compiled with OMIT_TEMPDB set, then
000161 # the lock_status list will not contain an entry for the temp
000162 # db. But the test code doesn't know this, so its easiest
000163 # to filter it out of the $expected_result list here.
000164 ifcapable !tempdb {
000165 set expected_result [concat \
000166 [lrange $expected_result 0 1] \
000167 [lrange $expected_result 4 end] \
000168 ]
000169 }
000170 do_test attach2-$testnum [subst {
000171 $db cache flush ;# The lock_status pragma should not be cached
000172 execsql {PRAGMA lock_status} $db
000173 }] $expected_result
000174 }
000175 set sqlite_os_trace 0
000176
000177 # Tests attach2-4.* test that read-locks work correctly with attached
000178 # databases.
000179 do_test attach2-4.1 {
000180 sqlite3 db test.db
000181 sqlite3 db2 test.db
000182 execsql {ATTACH 'test2.db' as file2}
000183 execsql {ATTACH 'test2.db' as file2} db2
000184 } {}
000185
000186 lock_status 4.1.1 db {main unlocked temp closed file2 unlocked}
000187 lock_status 4.1.2 db2 {main unlocked temp closed file2 unlocked}
000188
000189 do_test attach2-4.2 {
000190 # Handle 'db' read-locks test.db
000191 execsql {BEGIN}
000192 execsql {SELECT * FROM t1}
000193 # Lock status:
000194 # db - shared(main)
000195 # db2 -
000196 } {}
000197
000198 lock_status 4.2.1 db {main shared temp closed file2 unlocked}
000199 lock_status 4.2.2 db2 {main unlocked temp closed file2 unlocked}
000200
000201 do_test attach2-4.3 {
000202 # The read lock held by db does not prevent db2 from reading test.db
000203 execsql {SELECT * FROM t1} db2
000204 } {}
000205
000206 lock_status 4.3.1 db {main shared temp closed file2 unlocked}
000207 lock_status 4.3.2 db2 {main unlocked temp closed file2 unlocked}
000208
000209 do_test attach2-4.4 {
000210 # db is holding a read lock on test.db, so we should not be able
000211 # to commit a write to test.db from db2
000212 catchsql {
000213 INSERT INTO t1 VALUES(1, 2)
000214 } db2
000215 } {1 {database is locked}}
000216
000217 lock_status 4.4.1 db {main shared temp closed file2 unlocked}
000218 lock_status 4.4.2 db2 {main unlocked temp closed file2 unlocked}
000219
000220 # We have to make sure that the cache_size and the soft_heap_limit
000221 # are large enough to hold the entire change in memory. If either
000222 # is set too small, then changes will spill to the database, forcing
000223 # a reserved lock to promote to exclusive. That will mess up our
000224 # test results.
000225
000226 set soft_limit [sqlite3_soft_heap_limit 0]
000227
000228
000229 do_test attach2-4.5 {
000230 # Handle 'db2' reserves file2.
000231 execsql {BEGIN} db2
000232 execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2
000233 # Lock status:
000234 # db - shared(main)
000235 # db2 - reserved(file2)
000236 } {}
000237
000238 lock_status 4.5.1 db {main shared temp closed file2 unlocked}
000239 lock_status 4.5.2 db2 {main unlocked temp closed file2 reserved}
000240
000241 do_test attach2-4.6.1 {
000242 # Reads are allowed against a reserved database.
000243 catchsql {
000244 SELECT * FROM file2.t1;
000245 }
000246 # Lock status:
000247 # db - shared(main), shared(file2)
000248 # db2 - reserved(file2)
000249 } {0 {}}
000250
000251 lock_status 4.6.1.1 db {main shared temp closed file2 shared}
000252 lock_status 4.6.1.2 db2 {main unlocked temp closed file2 reserved}
000253
000254 do_test attach2-4.6.2 {
000255 # Writes against a reserved database are not allowed.
000256 catchsql {
000257 UPDATE file2.t1 SET a=0;
000258 }
000259 } {1 {database is locked}}
000260
000261 lock_status 4.6.2.1 db {main shared temp closed file2 shared}
000262 lock_status 4.6.2.2 db2 {main unlocked temp closed file2 reserved}
000263
000264 do_test attach2-4.7 {
000265 # Ensure handle 'db' retains the lock on the main file after
000266 # failing to obtain a write-lock on file2.
000267 catchsql {
000268 INSERT INTO t1 VALUES(1, 2)
000269 } db2
000270 } {0 {}}
000271
000272 lock_status 4.7.1 db {main shared temp closed file2 shared}
000273 lock_status 4.7.2 db2 {main reserved temp closed file2 reserved}
000274
000275 do_test attach2-4.8 {
000276 # We should still be able to read test.db from db2
000277 execsql {SELECT * FROM t1} db2
000278 } {1 2}
000279
000280 lock_status 4.8.1 db {main shared temp closed file2 shared}
000281 lock_status 4.8.2 db2 {main reserved temp closed file2 reserved}
000282
000283 do_test attach2-4.9 {
000284 # Try to upgrade the handle 'db' lock.
000285 catchsql {
000286 INSERT INTO t1 VALUES(1, 2)
000287 }
000288 } {1 {database is locked}}
000289
000290 lock_status 4.9.1 db {main shared temp closed file2 shared}
000291 lock_status 4.9.2 db2 {main reserved temp closed file2 reserved}
000292
000293 do_test attach2-4.10 {
000294 # We cannot commit db2 while db is holding a read-lock
000295 catchsql {COMMIT} db2
000296 } {1 {database is locked}}
000297
000298 lock_status 4.10.1 db {main shared temp closed file2 shared}
000299 lock_status 4.10.2 db2 {main pending temp closed file2 reserved}
000300
000301 set sqlite_os_trace 0
000302 do_test attach2-4.11 {
000303 # db is able to commit.
000304 catchsql {COMMIT}
000305 } {0 {}}
000306
000307 lock_status 4.11.1 db {main unlocked temp closed file2 unlocked}
000308 lock_status 4.11.2 db2 {main pending temp closed file2 reserved}
000309
000310 do_test attach2-4.12 {
000311 # Now we can commit db2
000312 catchsql {COMMIT} db2
000313 } {0 {}}
000314
000315 lock_status 4.12.1 db {main unlocked temp closed file2 unlocked}
000316 lock_status 4.12.2 db2 {main unlocked temp closed file2 unlocked}
000317
000318 do_test attach2-4.13 {
000319 execsql {SELECT * FROM file2.t1}
000320 } {1 2}
000321 do_test attach2-4.14 {
000322 execsql {INSERT INTO t1 VALUES(1, 2)}
000323 } {}
000324 do_test attach2-4.15 {
000325 execsql {SELECT * FROM t1} db2
000326 } {1 2 1 2}
000327
000328 db close
000329 db2 close
000330 forcedelete test2.db
000331 sqlite3_soft_heap_limit $soft_limit
000332
000333 # These tests - attach2-5.* - check that the master journal file is deleted
000334 # correctly when a multi-file transaction is committed or rolled back.
000335 #
000336 # Update: It's not actually created if a rollback occurs, so that test
000337 # doesn't really prove too much.
000338 foreach f [glob test.db*] {forcedelete $f}
000339 do_test attach2-5.1 {
000340 sqlite3 db test.db
000341 execsql {
000342 ATTACH 'test.db2' AS aux;
000343 }
000344 } {}
000345 do_test attach2-5.2 {
000346 execsql {
000347 BEGIN;
000348 CREATE TABLE tbl(a, b, c);
000349 CREATE TABLE aux.tbl(a, b, c);
000350 COMMIT;
000351 }
000352 } {}
000353 do_test attach2-5.3 {
000354 lsort [glob test.db*]
000355 } {test.db test.db2}
000356 do_test attach2-5.4 {
000357 execsql {
000358 BEGIN;
000359 DROP TABLE aux.tbl;
000360 DROP TABLE tbl;
000361 ROLLBACK;
000362 }
000363 } {}
000364 do_test attach2-5.5 {
000365 lsort [glob test.db*]
000366 } {test.db test.db2}
000367
000368 # Check that a database cannot be ATTACHed or DETACHed during a transaction.
000369 do_test attach2-6.1 {
000370 execsql {
000371 BEGIN;
000372 }
000373 } {}
000374 do_test attach2-6.2 {
000375 catchsql {
000376 ATTACH 'test3.db' as aux2;
000377 }
000378 } {1 {cannot ATTACH database within transaction}}
000379
000380 # EVIDENCE-OF: R-59740-55581 This statement will fail if SQLite is in
000381 # the middle of a transaction.
000382 #
000383 do_test attach2-6.3 {
000384 catchsql {
000385 DETACH aux;
000386 }
000387 } {1 {cannot DETACH database within transaction}}
000388 do_test attach2-6.4 {
000389 execsql {
000390 COMMIT;
000391 DETACH aux;
000392 }
000393 } {}
000394
000395 db close
000396
000397 finish_test