AryaWu/sqlite

1# 2003 July 12#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is testing the ATTACH and DETACH commands13# and related functionality.14#15# $Id: attach2.test,v 1.38 2007/12/13 21:54:11 drh Exp $16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set testprefix attach221 22ifcapable !attach {23  finish_test24  return25}26 27# Ticket #35428#29# Databases test.db and test2.db contain identical schemas.  Make30# sure we can attach test2.db from test.db.31#32do_test attach2-1.1 {33  db eval {34    CREATE TABLE t1(a,b);35    CREATE INDEX x1 ON t1(a);36  }37  forcedelete test2.db38  forcedelete test2.db-journal39  sqlite3 db2 test2.db40  db2 eval {41    CREATE TABLE t1(a,b);42    CREATE INDEX x1 ON t1(a);43  }44  catchsql {45    ATTACH 'test2.db' AS t2;46  }47} {0 {}}48 49# Ticket #51450#51proc db_list {db} {52  set list {}53  foreach {idx name file} [execsql {PRAGMA database_list} $db] {54    lappend list $idx $name55  }56  return $list57}58db eval {DETACH t2}59do_test attach2-2.1 {60  # lock test2.db then try to attach it.  This is no longer an error because61  # db2 just RESERVES the database.  It does not obtain a write-lock until62  # we COMMIT.63  db2 eval {BEGIN}64  db2 eval {UPDATE t1 SET a = 0 WHERE 0}65  catchsql {66    ATTACH 'test2.db' AS t2;67  }68} {0 {}}69ifcapable schema_pragmas {70do_test attach2-2.2 {71  # make sure test2.db did get attached.72  db_list db73} {0 main 2 t2}74} ;# ifcapable schema_pragmas75db2 eval {COMMIT}76 77do_test attach2-2.5 {78  # Make sure we can read test2.db from db79  catchsql {80    SELECT name FROM t2.sqlite_master;81  }82} {0 {t1 x1}}83do_test attach2-2.6 {84  # lock test2.db and try to read from it.  This should still work because85  # the lock is only a RESERVED lock which does not prevent reading.86  #87  db2 eval BEGIN88  db2 eval {UPDATE t1 SET a = 0 WHERE 0}89  catchsql {90    SELECT name FROM t2.sqlite_master;91  }92} {0 {t1 x1}}93do_test attach2-2.7 {94  # but we can still read from test1.db even though test2.db is locked.95  catchsql {96    SELECT name FROM main.sqlite_master;97  }98} {0 {t1 x1}}99do_test attach2-2.8 {100  # start a transaction on test.db even though test2.db is locked.101  catchsql {102    BEGIN;103    INSERT INTO t1 VALUES(8,9);104  }105} {0 {}}106do_test attach2-2.9 {107  execsql {108    SELECT * FROM t1109  }110} {8 9}111do_test attach2-2.10 {112  # now try to write to test2.db.  the write should fail113  catchsql {114    INSERT INTO t2.t1 VALUES(1,2);115  }116} {1 {database is locked}}117do_test attach2-2.11 {118  # when the write failed in the previous test, the transaction should119  # have rolled back.120  # 121  # Update for version 3: A transaction is no longer rolled back if a122  #                       database is found to be busy.123  execsql {rollback}124  db2 eval ROLLBACK125  execsql {126    SELECT * FROM t1127  }128} {}129do_test attach2-2.12 {130  catchsql {131    COMMIT132  }133} {1 {cannot commit - no transaction is active}}134 135# Ticket #574:  Make sure it works using the non-callback API136#137do_test attach2-3.1 {138  set DB [sqlite3_connection_pointer db]139  set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM]140  if {$rc} {lappend rc $VM}141  sqlite3_step $VM142  sqlite3_finalize $VM143  set rc144} {0}145do_test attach2-3.2 {146  set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM]147  if {$rc} {lappend rc $VM}148  sqlite3_step $VM149  sqlite3_finalize $VM150  set rc151} {0}152 153db close154for {set i 2} {$i<=15} {incr i} {155  catch {db$i close}156}157 158# A procedure to verify the status of locks on a database.159#160proc lock_status {testnum db expected_result} {161  # If the database was compiled with OMIT_TEMPDB set, then 162  # the lock_status list will not contain an entry for the temp163  # db. But the test code doesn't know this, so its easiest 164  # to filter it out of the $expected_result list here.165  ifcapable !tempdb {166    set expected_result [concat \167        [lrange $expected_result 0 1] \168        [lrange $expected_result 4 end] \169    ]170  }171  do_test attach2-$testnum [subst {172    $db cache flush  ;# The lock_status pragma should not be cached173    execsql {PRAGMA lock_status} $db174  }] $expected_result175}176set sqlite_os_trace 0177 178# Tests attach2-4.* test that read-locks work correctly with attached179# databases.180do_test attach2-4.1 {181  sqlite3 db test.db182  sqlite3 db2 test.db183  execsql {ATTACH 'test2.db' as file2}184  execsql {ATTACH 'test2.db' as file2} db2185} {}186 187lock_status 4.1.1 db {main unlocked temp closed file2 unlocked}188lock_status 4.1.2 db2 {main unlocked temp closed file2 unlocked}189 190do_test attach2-4.2 {191  # Handle 'db' read-locks test.db192  execsql {BEGIN}193  execsql {SELECT * FROM t1}194  # Lock status:195  #    db  - shared(main)196  #    db2 -197} {}198 199lock_status 4.2.1 db {main shared temp closed file2 unlocked}200lock_status 4.2.2 db2 {main unlocked temp closed file2 unlocked}201 202do_test attach2-4.3 {203  # The read lock held by db does not prevent db2 from reading test.db204  execsql {SELECT * FROM t1} db2205} {}206 207lock_status 4.3.1 db {main shared temp closed file2 unlocked}208lock_status 4.3.2 db2 {main unlocked temp closed file2 unlocked}209 210do_test attach2-4.4 {211  # db is holding a read lock on test.db, so we should not be able212  # to commit a write to test.db from db2213  catchsql {214    INSERT INTO t1 VALUES(1, 2)215  } db2 216} {1 {database is locked}}217 218lock_status 4.4.1 db {main shared temp closed file2 unlocked}219lock_status 4.4.2 db2 {main unlocked temp closed file2 unlocked}220 221# We have to make sure that the cache_size and the soft_heap_limit222# are large enough to hold the entire change in memory.  If either223# is set too small, then changes will spill to the database, forcing224# a reserved lock to promote to exclusive.  That will mess up our225# test results. 226 227set soft_limit [sqlite3_soft_heap_limit 0]228 229 230do_test attach2-4.5 {231  # Handle 'db2' reserves file2.232  execsql {BEGIN} db2233  execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2234  # Lock status:235  #    db  - shared(main)236  #    db2 - reserved(file2)237} {}238 239lock_status 4.5.1 db {main shared temp closed file2 unlocked}240lock_status 4.5.2 db2 {main unlocked temp closed file2 reserved}241 242do_test attach2-4.6.1 {243  # Reads are allowed against a reserved database.244  catchsql {245    SELECT * FROM file2.t1;246  }247  # Lock status:248  #    db  - shared(main), shared(file2)249  #    db2 - reserved(file2)250} {0 {}}251 252lock_status 4.6.1.1 db {main shared temp closed file2 shared}253lock_status 4.6.1.2 db2 {main unlocked temp closed file2 reserved}254 255do_test attach2-4.6.2 {256  # Writes against a reserved database are not allowed.257  catchsql {258    UPDATE file2.t1 SET a=0;259  }260} {1 {database is locked}}261 262lock_status 4.6.2.1 db {main shared temp closed file2 shared}263lock_status 4.6.2.2 db2 {main unlocked temp closed file2 reserved}264 265do_test attach2-4.7 {266  # Ensure handle 'db' retains the lock on the main file after267  # failing to obtain a write-lock on file2.268  catchsql {269    INSERT INTO t1 VALUES(1, 2)270  } db2 271} {0 {}}272 273lock_status 4.7.1 db {main shared temp closed file2 shared}274lock_status 4.7.2 db2 {main reserved temp closed file2 reserved}275 276do_test attach2-4.8 {277  # We should still be able to read test.db from db2278  execsql {SELECT * FROM t1} db2279} {1 2}280 281lock_status 4.8.1 db {main shared temp closed file2 shared}282lock_status 4.8.2 db2 {main reserved temp closed file2 reserved}283 284do_test attach2-4.9 {285  # Try to upgrade the handle 'db' lock.286  catchsql {287    INSERT INTO t1 VALUES(1, 2)288  }289} {1 {database is locked}}290 291lock_status 4.9.1 db {main shared temp closed file2 shared}292lock_status 4.9.2 db2 {main reserved temp closed file2 reserved}293 294do_test attach2-4.10 {295  # We cannot commit db2 while db is holding a read-lock296  catchsql {COMMIT} db2297} {1 {database is locked}}298 299lock_status 4.10.1 db {main shared temp closed file2 shared}300lock_status 4.10.2 db2 {main pending temp closed file2 reserved}301 302set sqlite_os_trace 0303do_test attach2-4.11 {304  # db is able to commit.305  catchsql {COMMIT}306} {0 {}}307 308lock_status 4.11.1 db {main unlocked temp closed file2 unlocked}309lock_status 4.11.2 db2 {main pending temp closed file2 reserved}310 311do_test attach2-4.12 {312  # Now we can commit db2313  catchsql {COMMIT} db2314} {0 {}}315 316lock_status 4.12.1 db {main unlocked temp closed file2 unlocked}317lock_status 4.12.2 db2 {main unlocked temp closed file2 unlocked}318 319do_test attach2-4.13 {320  execsql {SELECT * FROM file2.t1}321} {1 2}322do_test attach2-4.14 {323  execsql {INSERT INTO t1 VALUES(1, 2)}324} {}325do_test attach2-4.15 {326  execsql {SELECT * FROM t1} db2327} {1 2 1 2}328 329db close330db2 close331forcedelete test2.db332sqlite3_soft_heap_limit $soft_limit333 334# These tests - attach2-5.* - check that the master journal file is deleted335# correctly when a multi-file transaction is committed or rolled back.336#337# Update: It's not actually created if a rollback occurs, so that test338# doesn't really prove too much.339foreach f [glob test.db*] {forcedelete $f}340do_test attach2-5.1 {341  sqlite3 db test.db342  execsql {343    ATTACH 'test.db2' AS aux;344  }345} {}346do_test attach2-5.2 {347  execsql {348    BEGIN;349    CREATE TABLE tbl(a, b, c);350    CREATE TABLE aux.tbl(a, b, c);351    COMMIT;352  }353} {}354do_test attach2-5.3 {355  lsort [glob test.db*]356} {test.db test.db2}357do_test attach2-5.4 {358  execsql {359    BEGIN;360    DROP TABLE aux.tbl;361    DROP TABLE tbl;362    ROLLBACK;363  }364} {}365do_test attach2-5.5 {366  lsort [glob test.db*]367} {test.db test.db2}368 369# Check that a database cannot be ATTACHed or DETACHed during a transaction.370do_test attach2-6.1 {371  execsql {372    BEGIN;373  }374} {}375do_test attach2-6.2 {376  catchsql {377    ATTACH 'test3.db' as aux2;378    DETACH aux2;379  }380} {0 {}}381 382# As of version 3.21.0: it is ok to DETACH from within a transaction383#384do_test attach2-6.3 {385  catchsql {386    DETACH aux;387  }388} {0 {}}389 390db close391 392ifcapable utf16 {393  forcedelete test.db2      ;# utf-16394  forcedelete test.db3      ;# utf-16395  forcedelete test.db4      ;# utf-8396  397  sqlite3 db2 test.db2 398  do_execsql_test -db db2 1.1 {399    PRAGMA encoding = 'utf16';400    CREATE TABLE t2(x);401    INSERT INTO t2 VALUES('text2');402  }403  db2 close404  405  sqlite3 db3 test.db3 406  do_execsql_test -db db3 1.2 {407    PRAGMA encoding = 'utf16';408    CREATE TABLE t3(x);409    INSERT INTO t3 VALUES('text3');410  }411  db3 close412  413  sqlite3 db4 test.db4 414  do_execsql_test -db db4 1.3 {415    PRAGMA encoding = 'utf8';416    CREATE TABLE t4(x);417    INSERT INTO t4 VALUES('text4');418  }419  db4 close420  421  reset_db422  do_execsql_test 2.1 {423    PRAGMA encoding = 'utf16';424    ATTACH 'test.db2' AS aux;425    SELECT * FROM t2;426  } {text2}427  428  reset_db429  do_execsql_test 2.2 {430    ATTACH 'test.db4' AS aux;431    SELECT * FROM t4;432  } {text4}433  434  db close435  sqlite3 db test.db2436  do_execsql_test 2.3 {437    ATTACH 'test.db3' AS aux;438    SELECT * FROM t3;439    SELECT * FROM t2;440  } {text3 text2}441  442  db close443  sqlite3 db test.db2444  do_catchsql_test 2.4 {445    ATTACH 'test.db4' AS aux;446  } {1 {attached databases must use the same text encoding as main database}}447  448  db close449}450 451finish_test452