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   file delete -force test2.db

    37   file delete -force 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 file delete -force 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*] {file delete -force $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