Annotation of embedaddon/sqlite3/test/attach2.test, revision 1.1
1.1 ! misho 1: # 2003 July 1
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # 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: # This file implements regression tests for SQLite library. The
! 12: # focus of this script is testing the ATTACH and DETACH commands
! 13: # and related functionality.
! 14: #
! 15: # $Id: attach2.test,v 1.38 2007/12/13 21:54:11 drh Exp $
! 16: #
! 17:
! 18: set testdir [file dirname $argv0]
! 19: source $testdir/tester.tcl
! 20:
! 21: ifcapable !attach {
! 22: finish_test
! 23: return
! 24: }
! 25:
! 26: # Ticket #354
! 27: #
! 28: # Databases test.db and test2.db contain identical schemas. Make
! 29: # sure we can attach test2.db from test.db.
! 30: #
! 31: do_test attach2-1.1 {
! 32: db eval {
! 33: CREATE TABLE t1(a,b);
! 34: CREATE INDEX x1 ON t1(a);
! 35: }
! 36: forcedelete test2.db
! 37: forcedelete test2.db-journal
! 38: sqlite3 db2 test2.db
! 39: db2 eval {
! 40: CREATE TABLE t1(a,b);
! 41: CREATE INDEX x1 ON t1(a);
! 42: }
! 43: catchsql {
! 44: ATTACH 'test2.db' AS t2;
! 45: }
! 46: } {0 {}}
! 47:
! 48: # Ticket #514
! 49: #
! 50: proc db_list {db} {
! 51: set list {}
! 52: foreach {idx name file} [execsql {PRAGMA database_list} $db] {
! 53: lappend list $idx $name
! 54: }
! 55: return $list
! 56: }
! 57: db eval {DETACH t2}
! 58: do_test attach2-2.1 {
! 59: # lock test2.db then try to attach it. This is no longer an error because
! 60: # db2 just RESERVES the database. It does not obtain a write-lock until
! 61: # we COMMIT.
! 62: db2 eval {BEGIN}
! 63: db2 eval {UPDATE t1 SET a = 0 WHERE 0}
! 64: catchsql {
! 65: ATTACH 'test2.db' AS t2;
! 66: }
! 67: } {0 {}}
! 68: ifcapable schema_pragmas {
! 69: do_test attach2-2.2 {
! 70: # make sure test2.db did get attached.
! 71: db_list db
! 72: } {0 main 2 t2}
! 73: } ;# ifcapable schema_pragmas
! 74: db2 eval {COMMIT}
! 75:
! 76: do_test attach2-2.5 {
! 77: # Make sure we can read test2.db from db
! 78: catchsql {
! 79: SELECT name FROM t2.sqlite_master;
! 80: }
! 81: } {0 {t1 x1}}
! 82: do_test attach2-2.6 {
! 83: # lock test2.db and try to read from it. This should still work because
! 84: # the lock is only a RESERVED lock which does not prevent reading.
! 85: #
! 86: db2 eval BEGIN
! 87: db2 eval {UPDATE t1 SET a = 0 WHERE 0}
! 88: catchsql {
! 89: SELECT name FROM t2.sqlite_master;
! 90: }
! 91: } {0 {t1 x1}}
! 92: do_test attach2-2.7 {
! 93: # but we can still read from test1.db even though test2.db is locked.
! 94: catchsql {
! 95: SELECT name FROM main.sqlite_master;
! 96: }
! 97: } {0 {t1 x1}}
! 98: do_test attach2-2.8 {
! 99: # start a transaction on test.db even though test2.db is locked.
! 100: catchsql {
! 101: BEGIN;
! 102: INSERT INTO t1 VALUES(8,9);
! 103: }
! 104: } {0 {}}
! 105: do_test attach2-2.9 {
! 106: execsql {
! 107: SELECT * FROM t1
! 108: }
! 109: } {8 9}
! 110: do_test attach2-2.10 {
! 111: # now try to write to test2.db. the write should fail
! 112: catchsql {
! 113: INSERT INTO t2.t1 VALUES(1,2);
! 114: }
! 115: } {1 {database is locked}}
! 116: do_test attach2-2.11 {
! 117: # when the write failed in the previous test, the transaction should
! 118: # have rolled back.
! 119: #
! 120: # Update for version 3: A transaction is no longer rolled back if a
! 121: # database is found to be busy.
! 122: execsql {rollback}
! 123: db2 eval ROLLBACK
! 124: execsql {
! 125: SELECT * FROM t1
! 126: }
! 127: } {}
! 128: do_test attach2-2.12 {
! 129: catchsql {
! 130: COMMIT
! 131: }
! 132: } {1 {cannot commit - no transaction is active}}
! 133:
! 134: # Ticket #574: Make sure it works using the non-callback API
! 135: #
! 136: do_test attach2-3.1 {
! 137: set DB [sqlite3_connection_pointer db]
! 138: set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM]
! 139: if {$rc} {lappend rc $VM}
! 140: sqlite3_step $VM
! 141: sqlite3_finalize $VM
! 142: set rc
! 143: } {0}
! 144: do_test attach2-3.2 {
! 145: set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM]
! 146: if {$rc} {lappend rc $VM}
! 147: sqlite3_step $VM
! 148: sqlite3_finalize $VM
! 149: set rc
! 150: } {0}
! 151:
! 152: db close
! 153: for {set i 2} {$i<=15} {incr i} {
! 154: catch {db$i close}
! 155: }
! 156:
! 157: # A procedure to verify the status of locks on a database.
! 158: #
! 159: proc lock_status {testnum db expected_result} {
! 160: # If the database was compiled with OMIT_TEMPDB set, then
! 161: # the lock_status list will not contain an entry for the temp
! 162: # db. But the test code doesn't know this, so its easiest
! 163: # to filter it out of the $expected_result list here.
! 164: ifcapable !tempdb {
! 165: set expected_result [concat \
! 166: [lrange $expected_result 0 1] \
! 167: [lrange $expected_result 4 end] \
! 168: ]
! 169: }
! 170: do_test attach2-$testnum [subst {
! 171: $db cache flush ;# The lock_status pragma should not be cached
! 172: execsql {PRAGMA lock_status} $db
! 173: }] $expected_result
! 174: }
! 175: set sqlite_os_trace 0
! 176:
! 177: # Tests attach2-4.* test that read-locks work correctly with attached
! 178: # databases.
! 179: do_test attach2-4.1 {
! 180: sqlite3 db test.db
! 181: sqlite3 db2 test.db
! 182: execsql {ATTACH 'test2.db' as file2}
! 183: execsql {ATTACH 'test2.db' as file2} db2
! 184: } {}
! 185:
! 186: lock_status 4.1.1 db {main unlocked temp closed file2 unlocked}
! 187: lock_status 4.1.2 db2 {main unlocked temp closed file2 unlocked}
! 188:
! 189: do_test attach2-4.2 {
! 190: # Handle 'db' read-locks test.db
! 191: execsql {BEGIN}
! 192: execsql {SELECT * FROM t1}
! 193: # Lock status:
! 194: # db - shared(main)
! 195: # db2 -
! 196: } {}
! 197:
! 198: lock_status 4.2.1 db {main shared temp closed file2 unlocked}
! 199: lock_status 4.2.2 db2 {main unlocked temp closed file2 unlocked}
! 200:
! 201: do_test attach2-4.3 {
! 202: # The read lock held by db does not prevent db2 from reading test.db
! 203: execsql {SELECT * FROM t1} db2
! 204: } {}
! 205:
! 206: lock_status 4.3.1 db {main shared temp closed file2 unlocked}
! 207: lock_status 4.3.2 db2 {main unlocked temp closed file2 unlocked}
! 208:
! 209: do_test attach2-4.4 {
! 210: # db is holding a read lock on test.db, so we should not be able
! 211: # to commit a write to test.db from db2
! 212: catchsql {
! 213: INSERT INTO t1 VALUES(1, 2)
! 214: } db2
! 215: } {1 {database is locked}}
! 216:
! 217: lock_status 4.4.1 db {main shared temp closed file2 unlocked}
! 218: lock_status 4.4.2 db2 {main unlocked temp closed file2 unlocked}
! 219:
! 220: # We have to make sure that the cache_size and the soft_heap_limit
! 221: # are large enough to hold the entire change in memory. If either
! 222: # is set too small, then changes will spill to the database, forcing
! 223: # a reserved lock to promote to exclusive. That will mess up our
! 224: # test results.
! 225:
! 226: set soft_limit [sqlite3_soft_heap_limit 0]
! 227:
! 228:
! 229: do_test attach2-4.5 {
! 230: # Handle 'db2' reserves file2.
! 231: execsql {BEGIN} db2
! 232: execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2
! 233: # Lock status:
! 234: # db - shared(main)
! 235: # db2 - reserved(file2)
! 236: } {}
! 237:
! 238: lock_status 4.5.1 db {main shared temp closed file2 unlocked}
! 239: lock_status 4.5.2 db2 {main unlocked temp closed file2 reserved}
! 240:
! 241: do_test attach2-4.6.1 {
! 242: # Reads are allowed against a reserved database.
! 243: catchsql {
! 244: SELECT * FROM file2.t1;
! 245: }
! 246: # Lock status:
! 247: # db - shared(main), shared(file2)
! 248: # db2 - reserved(file2)
! 249: } {0 {}}
! 250:
! 251: lock_status 4.6.1.1 db {main shared temp closed file2 shared}
! 252: lock_status 4.6.1.2 db2 {main unlocked temp closed file2 reserved}
! 253:
! 254: do_test attach2-4.6.2 {
! 255: # Writes against a reserved database are not allowed.
! 256: catchsql {
! 257: UPDATE file2.t1 SET a=0;
! 258: }
! 259: } {1 {database is locked}}
! 260:
! 261: lock_status 4.6.2.1 db {main shared temp closed file2 shared}
! 262: lock_status 4.6.2.2 db2 {main unlocked temp closed file2 reserved}
! 263:
! 264: do_test attach2-4.7 {
! 265: # Ensure handle 'db' retains the lock on the main file after
! 266: # failing to obtain a write-lock on file2.
! 267: catchsql {
! 268: INSERT INTO t1 VALUES(1, 2)
! 269: } db2
! 270: } {0 {}}
! 271:
! 272: lock_status 4.7.1 db {main shared temp closed file2 shared}
! 273: lock_status 4.7.2 db2 {main reserved temp closed file2 reserved}
! 274:
! 275: do_test attach2-4.8 {
! 276: # We should still be able to read test.db from db2
! 277: execsql {SELECT * FROM t1} db2
! 278: } {1 2}
! 279:
! 280: lock_status 4.8.1 db {main shared temp closed file2 shared}
! 281: lock_status 4.8.2 db2 {main reserved temp closed file2 reserved}
! 282:
! 283: do_test attach2-4.9 {
! 284: # Try to upgrade the handle 'db' lock.
! 285: catchsql {
! 286: INSERT INTO t1 VALUES(1, 2)
! 287: }
! 288: } {1 {database is locked}}
! 289:
! 290: lock_status 4.9.1 db {main shared temp closed file2 shared}
! 291: lock_status 4.9.2 db2 {main reserved temp closed file2 reserved}
! 292:
! 293: do_test attach2-4.10 {
! 294: # We cannot commit db2 while db is holding a read-lock
! 295: catchsql {COMMIT} db2
! 296: } {1 {database is locked}}
! 297:
! 298: lock_status 4.10.1 db {main shared temp closed file2 shared}
! 299: lock_status 4.10.2 db2 {main pending temp closed file2 reserved}
! 300:
! 301: set sqlite_os_trace 0
! 302: do_test attach2-4.11 {
! 303: # db is able to commit.
! 304: catchsql {COMMIT}
! 305: } {0 {}}
! 306:
! 307: lock_status 4.11.1 db {main unlocked temp closed file2 unlocked}
! 308: lock_status 4.11.2 db2 {main pending temp closed file2 reserved}
! 309:
! 310: do_test attach2-4.12 {
! 311: # Now we can commit db2
! 312: catchsql {COMMIT} db2
! 313: } {0 {}}
! 314:
! 315: lock_status 4.12.1 db {main unlocked temp closed file2 unlocked}
! 316: lock_status 4.12.2 db2 {main unlocked temp closed file2 unlocked}
! 317:
! 318: do_test attach2-4.13 {
! 319: execsql {SELECT * FROM file2.t1}
! 320: } {1 2}
! 321: do_test attach2-4.14 {
! 322: execsql {INSERT INTO t1 VALUES(1, 2)}
! 323: } {}
! 324: do_test attach2-4.15 {
! 325: execsql {SELECT * FROM t1} db2
! 326: } {1 2 1 2}
! 327:
! 328: db close
! 329: db2 close
! 330: forcedelete test2.db
! 331: sqlite3_soft_heap_limit $soft_limit
! 332:
! 333: # These tests - attach2-5.* - check that the master journal file is deleted
! 334: # correctly when a multi-file transaction is committed or rolled back.
! 335: #
! 336: # Update: It's not actually created if a rollback occurs, so that test
! 337: # doesn't really prove too much.
! 338: foreach f [glob test.db*] {forcedelete $f}
! 339: do_test attach2-5.1 {
! 340: sqlite3 db test.db
! 341: execsql {
! 342: ATTACH 'test.db2' AS aux;
! 343: }
! 344: } {}
! 345: do_test attach2-5.2 {
! 346: execsql {
! 347: BEGIN;
! 348: CREATE TABLE tbl(a, b, c);
! 349: CREATE TABLE aux.tbl(a, b, c);
! 350: COMMIT;
! 351: }
! 352: } {}
! 353: do_test attach2-5.3 {
! 354: lsort [glob test.db*]
! 355: } {test.db test.db2}
! 356: do_test attach2-5.4 {
! 357: execsql {
! 358: BEGIN;
! 359: DROP TABLE aux.tbl;
! 360: DROP TABLE tbl;
! 361: ROLLBACK;
! 362: }
! 363: } {}
! 364: do_test attach2-5.5 {
! 365: lsort [glob test.db*]
! 366: } {test.db test.db2}
! 367:
! 368: # Check that a database cannot be ATTACHed or DETACHed during a transaction.
! 369: do_test attach2-6.1 {
! 370: execsql {
! 371: BEGIN;
! 372: }
! 373: } {}
! 374: do_test attach2-6.2 {
! 375: catchsql {
! 376: ATTACH 'test3.db' as aux2;
! 377: }
! 378: } {1 {cannot ATTACH database within transaction}}
! 379:
! 380: do_test attach2-6.3 {
! 381: catchsql {
! 382: DETACH aux;
! 383: }
! 384: } {1 {cannot DETACH database within transaction}}
! 385: do_test attach2-6.4 {
! 386: execsql {
! 387: COMMIT;
! 388: DETACH aux;
! 389: }
! 390: } {}
! 391:
! 392: db close
! 393:
! 394: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to attach2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test