1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/attach2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,394 @@
1.4 +# 2003 July 1
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing the ATTACH and DETACH commands
1.16 +# and related functionality.
1.17 +#
1.18 +# $Id: attach2.test,v 1.38 2007/12/13 21:54:11 drh Exp $
1.19 +#
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +ifcapable !attach {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +# Ticket #354
1.30 +#
1.31 +# Databases test.db and test2.db contain identical schemas. Make
1.32 +# sure we can attach test2.db from test.db.
1.33 +#
1.34 +do_test attach2-1.1 {
1.35 + db eval {
1.36 + CREATE TABLE t1(a,b);
1.37 + CREATE INDEX x1 ON t1(a);
1.38 + }
1.39 + file delete -force test2.db
1.40 + file delete -force test2.db-journal
1.41 + sqlite3 db2 test2.db
1.42 + db2 eval {
1.43 + CREATE TABLE t1(a,b);
1.44 + CREATE INDEX x1 ON t1(a);
1.45 + }
1.46 + catchsql {
1.47 + ATTACH 'test2.db' AS t2;
1.48 + }
1.49 +} {0 {}}
1.50 +
1.51 +# Ticket #514
1.52 +#
1.53 +proc db_list {db} {
1.54 + set list {}
1.55 + foreach {idx name file} [execsql {PRAGMA database_list} $db] {
1.56 + lappend list $idx $name
1.57 + }
1.58 + return $list
1.59 +}
1.60 +db eval {DETACH t2}
1.61 +do_test attach2-2.1 {
1.62 + # lock test2.db then try to attach it. This is no longer an error because
1.63 + # db2 just RESERVES the database. It does not obtain a write-lock until
1.64 + # we COMMIT.
1.65 + db2 eval {BEGIN}
1.66 + db2 eval {UPDATE t1 SET a = 0 WHERE 0}
1.67 + catchsql {
1.68 + ATTACH 'test2.db' AS t2;
1.69 + }
1.70 +} {0 {}}
1.71 +ifcapable schema_pragmas {
1.72 +do_test attach2-2.2 {
1.73 + # make sure test2.db did get attached.
1.74 + db_list db
1.75 +} {0 main 2 t2}
1.76 +} ;# ifcapable schema_pragmas
1.77 +db2 eval {COMMIT}
1.78 +
1.79 +do_test attach2-2.5 {
1.80 + # Make sure we can read test2.db from db
1.81 + catchsql {
1.82 + SELECT name FROM t2.sqlite_master;
1.83 + }
1.84 +} {0 {t1 x1}}
1.85 +do_test attach2-2.6 {
1.86 + # lock test2.db and try to read from it. This should still work because
1.87 + # the lock is only a RESERVED lock which does not prevent reading.
1.88 + #
1.89 + db2 eval BEGIN
1.90 + db2 eval {UPDATE t1 SET a = 0 WHERE 0}
1.91 + catchsql {
1.92 + SELECT name FROM t2.sqlite_master;
1.93 + }
1.94 +} {0 {t1 x1}}
1.95 +do_test attach2-2.7 {
1.96 + # but we can still read from test1.db even though test2.db is locked.
1.97 + catchsql {
1.98 + SELECT name FROM main.sqlite_master;
1.99 + }
1.100 +} {0 {t1 x1}}
1.101 +do_test attach2-2.8 {
1.102 + # start a transaction on test.db even though test2.db is locked.
1.103 + catchsql {
1.104 + BEGIN;
1.105 + INSERT INTO t1 VALUES(8,9);
1.106 + }
1.107 +} {0 {}}
1.108 +do_test attach2-2.9 {
1.109 + execsql {
1.110 + SELECT * FROM t1
1.111 + }
1.112 +} {8 9}
1.113 +do_test attach2-2.10 {
1.114 + # now try to write to test2.db. the write should fail
1.115 + catchsql {
1.116 + INSERT INTO t2.t1 VALUES(1,2);
1.117 + }
1.118 +} {1 {database is locked}}
1.119 +do_test attach2-2.11 {
1.120 + # when the write failed in the previous test, the transaction should
1.121 + # have rolled back.
1.122 + #
1.123 + # Update for version 3: A transaction is no longer rolled back if a
1.124 + # database is found to be busy.
1.125 + execsql {rollback}
1.126 + db2 eval ROLLBACK
1.127 + execsql {
1.128 + SELECT * FROM t1
1.129 + }
1.130 +} {}
1.131 +do_test attach2-2.12 {
1.132 + catchsql {
1.133 + COMMIT
1.134 + }
1.135 +} {1 {cannot commit - no transaction is active}}
1.136 +
1.137 +# Ticket #574: Make sure it works using the non-callback API
1.138 +#
1.139 +do_test attach2-3.1 {
1.140 + set DB [sqlite3_connection_pointer db]
1.141 + set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM]
1.142 + if {$rc} {lappend rc $VM}
1.143 + sqlite3_step $VM
1.144 + sqlite3_finalize $VM
1.145 + set rc
1.146 +} {0}
1.147 +do_test attach2-3.2 {
1.148 + set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM]
1.149 + if {$rc} {lappend rc $VM}
1.150 + sqlite3_step $VM
1.151 + sqlite3_finalize $VM
1.152 + set rc
1.153 +} {0}
1.154 +
1.155 +db close
1.156 +for {set i 2} {$i<=15} {incr i} {
1.157 + catch {db$i close}
1.158 +}
1.159 +
1.160 +# A procedure to verify the status of locks on a database.
1.161 +#
1.162 +proc lock_status {testnum db expected_result} {
1.163 + # If the database was compiled with OMIT_TEMPDB set, then
1.164 + # the lock_status list will not contain an entry for the temp
1.165 + # db. But the test code doesn't know this, so its easiest
1.166 + # to filter it out of the $expected_result list here.
1.167 + ifcapable !tempdb {
1.168 + set expected_result [concat \
1.169 + [lrange $expected_result 0 1] \
1.170 + [lrange $expected_result 4 end] \
1.171 + ]
1.172 + }
1.173 + do_test attach2-$testnum [subst {
1.174 + $db cache flush ;# The lock_status pragma should not be cached
1.175 + execsql {PRAGMA lock_status} $db
1.176 + }] $expected_result
1.177 +}
1.178 +set sqlite_os_trace 0
1.179 +
1.180 +# Tests attach2-4.* test that read-locks work correctly with attached
1.181 +# databases.
1.182 +do_test attach2-4.1 {
1.183 + sqlite3 db test.db
1.184 + sqlite3 db2 test.db
1.185 + execsql {ATTACH 'test2.db' as file2}
1.186 + execsql {ATTACH 'test2.db' as file2} db2
1.187 +} {}
1.188 +
1.189 +lock_status 4.1.1 db {main unlocked temp closed file2 unlocked}
1.190 +lock_status 4.1.2 db2 {main unlocked temp closed file2 unlocked}
1.191 +
1.192 +do_test attach2-4.2 {
1.193 + # Handle 'db' read-locks test.db
1.194 + execsql {BEGIN}
1.195 + execsql {SELECT * FROM t1}
1.196 + # Lock status:
1.197 + # db - shared(main)
1.198 + # db2 -
1.199 +} {}
1.200 +
1.201 +lock_status 4.2.1 db {main shared temp closed file2 unlocked}
1.202 +lock_status 4.2.2 db2 {main unlocked temp closed file2 unlocked}
1.203 +
1.204 +do_test attach2-4.3 {
1.205 + # The read lock held by db does not prevent db2 from reading test.db
1.206 + execsql {SELECT * FROM t1} db2
1.207 +} {}
1.208 +
1.209 +lock_status 4.3.1 db {main shared temp closed file2 unlocked}
1.210 +lock_status 4.3.2 db2 {main unlocked temp closed file2 unlocked}
1.211 +
1.212 +do_test attach2-4.4 {
1.213 + # db is holding a read lock on test.db, so we should not be able
1.214 + # to commit a write to test.db from db2
1.215 + catchsql {
1.216 + INSERT INTO t1 VALUES(1, 2)
1.217 + } db2
1.218 +} {1 {database is locked}}
1.219 +
1.220 +lock_status 4.4.1 db {main shared temp closed file2 unlocked}
1.221 +lock_status 4.4.2 db2 {main unlocked temp closed file2 unlocked}
1.222 +
1.223 +# We have to make sure that the cache_size and the soft_heap_limit
1.224 +# are large enough to hold the entire change in memory. If either
1.225 +# is set too small, then changes will spill to the database, forcing
1.226 +# a reserved lock to promote to exclusive. That will mess up our
1.227 +# test results.
1.228 +
1.229 +set soft_limit [sqlite3_soft_heap_limit 0]
1.230 +
1.231 +
1.232 +do_test attach2-4.5 {
1.233 + # Handle 'db2' reserves file2.
1.234 + execsql {BEGIN} db2
1.235 + execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2
1.236 + # Lock status:
1.237 + # db - shared(main)
1.238 + # db2 - reserved(file2)
1.239 +} {}
1.240 +
1.241 +lock_status 4.5.1 db {main shared temp closed file2 unlocked}
1.242 +lock_status 4.5.2 db2 {main unlocked temp closed file2 reserved}
1.243 +
1.244 +do_test attach2-4.6.1 {
1.245 + # Reads are allowed against a reserved database.
1.246 + catchsql {
1.247 + SELECT * FROM file2.t1;
1.248 + }
1.249 + # Lock status:
1.250 + # db - shared(main), shared(file2)
1.251 + # db2 - reserved(file2)
1.252 +} {0 {}}
1.253 +
1.254 +lock_status 4.6.1.1 db {main shared temp closed file2 shared}
1.255 +lock_status 4.6.1.2 db2 {main unlocked temp closed file2 reserved}
1.256 +
1.257 +do_test attach2-4.6.2 {
1.258 + # Writes against a reserved database are not allowed.
1.259 + catchsql {
1.260 + UPDATE file2.t1 SET a=0;
1.261 + }
1.262 +} {1 {database is locked}}
1.263 +
1.264 +lock_status 4.6.2.1 db {main shared temp closed file2 shared}
1.265 +lock_status 4.6.2.2 db2 {main unlocked temp closed file2 reserved}
1.266 +
1.267 +do_test attach2-4.7 {
1.268 + # Ensure handle 'db' retains the lock on the main file after
1.269 + # failing to obtain a write-lock on file2.
1.270 + catchsql {
1.271 + INSERT INTO t1 VALUES(1, 2)
1.272 + } db2
1.273 +} {0 {}}
1.274 +
1.275 +lock_status 4.7.1 db {main shared temp closed file2 shared}
1.276 +lock_status 4.7.2 db2 {main reserved temp closed file2 reserved}
1.277 +
1.278 +do_test attach2-4.8 {
1.279 + # We should still be able to read test.db from db2
1.280 + execsql {SELECT * FROM t1} db2
1.281 +} {1 2}
1.282 +
1.283 +lock_status 4.8.1 db {main shared temp closed file2 shared}
1.284 +lock_status 4.8.2 db2 {main reserved temp closed file2 reserved}
1.285 +
1.286 +do_test attach2-4.9 {
1.287 + # Try to upgrade the handle 'db' lock.
1.288 + catchsql {
1.289 + INSERT INTO t1 VALUES(1, 2)
1.290 + }
1.291 +} {1 {database is locked}}
1.292 +
1.293 +lock_status 4.9.1 db {main shared temp closed file2 shared}
1.294 +lock_status 4.9.2 db2 {main reserved temp closed file2 reserved}
1.295 +
1.296 +do_test attach2-4.10 {
1.297 + # We cannot commit db2 while db is holding a read-lock
1.298 + catchsql {COMMIT} db2
1.299 +} {1 {database is locked}}
1.300 +
1.301 +lock_status 4.10.1 db {main shared temp closed file2 shared}
1.302 +lock_status 4.10.2 db2 {main pending temp closed file2 reserved}
1.303 +
1.304 +set sqlite_os_trace 0
1.305 +do_test attach2-4.11 {
1.306 + # db is able to commit.
1.307 + catchsql {COMMIT}
1.308 +} {0 {}}
1.309 +
1.310 +lock_status 4.11.1 db {main unlocked temp closed file2 unlocked}
1.311 +lock_status 4.11.2 db2 {main pending temp closed file2 reserved}
1.312 +
1.313 +do_test attach2-4.12 {
1.314 + # Now we can commit db2
1.315 + catchsql {COMMIT} db2
1.316 +} {0 {}}
1.317 +
1.318 +lock_status 4.12.1 db {main unlocked temp closed file2 unlocked}
1.319 +lock_status 4.12.2 db2 {main unlocked temp closed file2 unlocked}
1.320 +
1.321 +do_test attach2-4.13 {
1.322 + execsql {SELECT * FROM file2.t1}
1.323 +} {1 2}
1.324 +do_test attach2-4.14 {
1.325 + execsql {INSERT INTO t1 VALUES(1, 2)}
1.326 +} {}
1.327 +do_test attach2-4.15 {
1.328 + execsql {SELECT * FROM t1} db2
1.329 +} {1 2 1 2}
1.330 +
1.331 +db close
1.332 +db2 close
1.333 +file delete -force test2.db
1.334 +sqlite3_soft_heap_limit $soft_limit
1.335 +
1.336 +# These tests - attach2-5.* - check that the master journal file is deleted
1.337 +# correctly when a multi-file transaction is committed or rolled back.
1.338 +#
1.339 +# Update: It's not actually created if a rollback occurs, so that test
1.340 +# doesn't really prove too much.
1.341 +foreach f [glob test.db*] {file delete -force $f}
1.342 +do_test attach2-5.1 {
1.343 + sqlite3 db test.db
1.344 + execsql {
1.345 + ATTACH 'test.db2' AS aux;
1.346 + }
1.347 +} {}
1.348 +do_test attach2-5.2 {
1.349 + execsql {
1.350 + BEGIN;
1.351 + CREATE TABLE tbl(a, b, c);
1.352 + CREATE TABLE aux.tbl(a, b, c);
1.353 + COMMIT;
1.354 + }
1.355 +} {}
1.356 +do_test attach2-5.3 {
1.357 + lsort [glob test.db*]
1.358 +} {test.db test.db2}
1.359 +do_test attach2-5.4 {
1.360 + execsql {
1.361 + BEGIN;
1.362 + DROP TABLE aux.tbl;
1.363 + DROP TABLE tbl;
1.364 + ROLLBACK;
1.365 + }
1.366 +} {}
1.367 +do_test attach2-5.5 {
1.368 + lsort [glob test.db*]
1.369 +} {test.db test.db2}
1.370 +
1.371 +# Check that a database cannot be ATTACHed or DETACHed during a transaction.
1.372 +do_test attach2-6.1 {
1.373 + execsql {
1.374 + BEGIN;
1.375 + }
1.376 +} {}
1.377 +do_test attach2-6.2 {
1.378 + catchsql {
1.379 + ATTACH 'test3.db' as aux2;
1.380 + }
1.381 +} {1 {cannot ATTACH database within transaction}}
1.382 +
1.383 +do_test attach2-6.3 {
1.384 + catchsql {
1.385 + DETACH aux;
1.386 + }
1.387 +} {1 {cannot DETACH database within transaction}}
1.388 +do_test attach2-6.4 {
1.389 + execsql {
1.390 + COMMIT;
1.391 + DETACH aux;
1.392 + }
1.393 +} {}
1.394 +
1.395 +db close
1.396 +
1.397 +finish_test