1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/attach.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,792 @@
1.4 +# 2003 April 4
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: attach.test,v 1.49 2008/07/12 14:52:20 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 +for {set i 2} {$i<=15} {incr i} {
1.30 + file delete -force test$i.db
1.31 + file delete -force test$i.db-journal
1.32 +}
1.33 +
1.34 +do_test attach-1.1 {
1.35 + execsql {
1.36 + CREATE TABLE t1(a,b);
1.37 + INSERT INTO t1 VALUES(1,2);
1.38 + INSERT INTO t1 VALUES(3,4);
1.39 + SELECT * FROM t1;
1.40 + }
1.41 +} {1 2 3 4}
1.42 +do_test attach-1.2 {
1.43 + sqlite3 db2 test2.db
1.44 + execsql {
1.45 + CREATE TABLE t2(x,y);
1.46 + INSERT INTO t2 VALUES(1,'x');
1.47 + INSERT INTO t2 VALUES(2,'y');
1.48 + SELECT * FROM t2;
1.49 + } db2
1.50 +} {1 x 2 y}
1.51 +do_test attach-1.3 {
1.52 + execsql {
1.53 + ATTACH DATABASE 'test2.db' AS two;
1.54 + SELECT * FROM two.t2;
1.55 + }
1.56 +} {1 x 2 y}
1.57 +do_test attach-1.4 {
1.58 + execsql {
1.59 + SELECT * FROM t2;
1.60 + }
1.61 +} {1 x 2 y}
1.62 +do_test attach-1.5 {
1.63 + execsql {
1.64 + DETACH DATABASE two;
1.65 + SELECT * FROM t1;
1.66 + }
1.67 +} {1 2 3 4}
1.68 +do_test attach-1.6 {
1.69 + catchsql {
1.70 + SELECT * FROM t2;
1.71 + }
1.72 +} {1 {no such table: t2}}
1.73 +do_test attach-1.7 {
1.74 + catchsql {
1.75 + SELECT * FROM two.t2;
1.76 + }
1.77 +} {1 {no such table: two.t2}}
1.78 +do_test attach-1.8 {
1.79 + catchsql {
1.80 + ATTACH DATABASE 'test3.db' AS three;
1.81 + }
1.82 +} {0 {}}
1.83 +do_test attach-1.9 {
1.84 + catchsql {
1.85 + SELECT * FROM three.sqlite_master;
1.86 + }
1.87 +} {0 {}}
1.88 +do_test attach-1.10 {
1.89 + catchsql {
1.90 + DETACH DATABASE [three];
1.91 + }
1.92 +} {0 {}}
1.93 +do_test attach-1.11 {
1.94 + execsql {
1.95 + ATTACH 'test.db' AS db2;
1.96 + ATTACH 'test.db' AS db3;
1.97 + ATTACH 'test.db' AS db4;
1.98 + ATTACH 'test.db' AS db5;
1.99 + ATTACH 'test.db' AS db6;
1.100 + ATTACH 'test.db' AS db7;
1.101 + ATTACH 'test.db' AS db8;
1.102 + ATTACH 'test.db' AS db9;
1.103 + }
1.104 +} {}
1.105 +proc db_list {db} {
1.106 + set list {}
1.107 + foreach {idx name file} [execsql {PRAGMA database_list} $db] {
1.108 + lappend list $idx $name
1.109 + }
1.110 + return $list
1.111 +}
1.112 +ifcapable schema_pragmas {
1.113 +do_test attach-1.11b {
1.114 + db_list db
1.115 +} {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}
1.116 +} ;# ifcapable schema_pragmas
1.117 +do_test attach-1.12 {
1.118 + catchsql {
1.119 + ATTACH 'test.db' as db2;
1.120 + }
1.121 +} {1 {database db2 is already in use}}
1.122 +do_test attach-1.12.2 {
1.123 + db errorcode
1.124 +} {1}
1.125 +do_test attach-1.13 {
1.126 + catchsql {
1.127 + ATTACH 'test.db' as db5;
1.128 + }
1.129 +} {1 {database db5 is already in use}}
1.130 +do_test attach-1.14 {
1.131 + catchsql {
1.132 + ATTACH 'test.db' as db9;
1.133 + }
1.134 +} {1 {database db9 is already in use}}
1.135 +do_test attach-1.15 {
1.136 + catchsql {
1.137 + ATTACH 'test.db' as main;
1.138 + }
1.139 +} {1 {database main is already in use}}
1.140 +ifcapable tempdb {
1.141 + do_test attach-1.16 {
1.142 + catchsql {
1.143 + ATTACH 'test.db' as temp;
1.144 + }
1.145 + } {1 {database temp is already in use}}
1.146 +}
1.147 +do_test attach-1.17 {
1.148 + catchsql {
1.149 + ATTACH 'test.db' as MAIN;
1.150 + }
1.151 +} {1 {database MAIN is already in use}}
1.152 +do_test attach-1.18 {
1.153 + catchsql {
1.154 + ATTACH 'test.db' as db10;
1.155 + ATTACH 'test.db' as db11;
1.156 + }
1.157 +} {0 {}}
1.158 +do_test attach-1.19 {
1.159 + catchsql {
1.160 + ATTACH 'test.db' as db12;
1.161 + }
1.162 +} {1 {too many attached databases - max 10}}
1.163 +do_test attach-1.19.1 {
1.164 + db errorcode
1.165 +} {1}
1.166 +do_test attach-1.20.1 {
1.167 + execsql {
1.168 + DETACH db5;
1.169 + }
1.170 +} {}
1.171 +ifcapable schema_pragmas {
1.172 +do_test attach-1.20.2 {
1.173 + db_list db
1.174 +} {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11}
1.175 +} ;# ifcapable schema_pragmas
1.176 +integrity_check attach-1.20.3
1.177 +ifcapable tempdb {
1.178 + execsql {select * from sqlite_temp_master}
1.179 +}
1.180 +do_test attach-1.21 {
1.181 + catchsql {
1.182 + ATTACH 'test.db' as db12;
1.183 + }
1.184 +} {0 {}}
1.185 +do_test attach-1.22 {
1.186 + catchsql {
1.187 + ATTACH 'test.db' as db13;
1.188 + }
1.189 +} {1 {too many attached databases - max 10}}
1.190 +do_test attach-1.22.1 {
1.191 + db errorcode
1.192 +} {1}
1.193 +do_test attach-1.23 {
1.194 + catchsql {
1.195 + DETACH "db14";
1.196 + }
1.197 +} {1 {no such database: db14}}
1.198 +do_test attach-1.24 {
1.199 + catchsql {
1.200 + DETACH db12;
1.201 + }
1.202 +} {0 {}}
1.203 +do_test attach-1.25 {
1.204 + catchsql {
1.205 + DETACH db12;
1.206 + }
1.207 +} {1 {no such database: db12}}
1.208 +do_test attach-1.26 {
1.209 + catchsql {
1.210 + DETACH main;
1.211 + }
1.212 +} {1 {cannot detach database main}}
1.213 +
1.214 +ifcapable tempdb {
1.215 + do_test attach-1.27 {
1.216 + catchsql {
1.217 + DETACH Temp;
1.218 + }
1.219 + } {1 {cannot detach database Temp}}
1.220 +} else {
1.221 + do_test attach-1.27 {
1.222 + catchsql {
1.223 + DETACH Temp;
1.224 + }
1.225 + } {1 {no such database: Temp}}
1.226 +}
1.227 +
1.228 +do_test attach-1.28 {
1.229 + catchsql {
1.230 + DETACH db11;
1.231 + DETACH db10;
1.232 + DETACH db9;
1.233 + DETACH db8;
1.234 + DETACH db7;
1.235 + DETACH db6;
1.236 + DETACH db4;
1.237 + DETACH db3;
1.238 + DETACH db2;
1.239 + }
1.240 +} {0 {}}
1.241 +ifcapable schema_pragmas {
1.242 + ifcapable tempdb {
1.243 + do_test attach-1.29 {
1.244 + db_list db
1.245 + } {0 main 1 temp}
1.246 + } else {
1.247 + do_test attach-1.29 {
1.248 + db_list db
1.249 + } {0 main}
1.250 + }
1.251 +} ;# ifcapable schema_pragmas
1.252 +
1.253 +ifcapable {trigger} { # Only do the following tests if triggers are enabled
1.254 +do_test attach-2.1 {
1.255 + execsql {
1.256 + CREATE TABLE tx(x1,x2,y1,y2);
1.257 + CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
1.258 + INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y);
1.259 + END;
1.260 + SELECT * FROM tx;
1.261 + } db2;
1.262 +} {}
1.263 +do_test attach-2.2 {
1.264 + execsql {
1.265 + UPDATE t2 SET x=x+10;
1.266 + SELECT * FROM tx;
1.267 + } db2;
1.268 +} {1 11 x x 2 12 y y}
1.269 +do_test attach-2.3 {
1.270 + execsql {
1.271 + CREATE TABLE tx(x1,x2,y1,y2);
1.272 + SELECT * FROM tx;
1.273 + }
1.274 +} {}
1.275 +do_test attach-2.4 {
1.276 + execsql {
1.277 + ATTACH 'test2.db' AS db2;
1.278 + }
1.279 +} {}
1.280 +do_test attach-2.5 {
1.281 + execsql {
1.282 + UPDATE db2.t2 SET x=x+10;
1.283 + SELECT * FROM db2.tx;
1.284 + }
1.285 +} {1 11 x x 2 12 y y 11 21 x x 12 22 y y}
1.286 +do_test attach-2.6 {
1.287 + execsql {
1.288 + SELECT * FROM main.tx;
1.289 + }
1.290 +} {}
1.291 +do_test attach-2.7 {
1.292 + execsql {
1.293 + SELECT type, name, tbl_name FROM db2.sqlite_master;
1.294 + }
1.295 +} {table t2 t2 table tx tx trigger r1 t2}
1.296 +
1.297 +ifcapable schema_pragmas&&tempdb {
1.298 + do_test attach-2.8 {
1.299 + db_list db
1.300 + } {0 main 1 temp 2 db2}
1.301 +} ;# ifcapable schema_pragmas&&tempdb
1.302 +ifcapable schema_pragmas&&!tempdb {
1.303 + do_test attach-2.8 {
1.304 + db_list db
1.305 + } {0 main 2 db2}
1.306 +} ;# ifcapable schema_pragmas&&!tempdb
1.307 +
1.308 +do_test attach-2.9 {
1.309 + execsql {
1.310 + CREATE INDEX i2 ON t2(x);
1.311 + SELECT * FROM t2 WHERE x>5;
1.312 + } db2
1.313 +} {21 x 22 y}
1.314 +do_test attach-2.10 {
1.315 + execsql {
1.316 + SELECT type, name, tbl_name FROM sqlite_master;
1.317 + } db2
1.318 +} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
1.319 +#do_test attach-2.11 {
1.320 +# catchsql {
1.321 +# SELECT * FROM t2 WHERE x>5;
1.322 +# }
1.323 +#} {1 {database schema has changed}}
1.324 +ifcapable schema_pragmas {
1.325 + ifcapable tempdb {
1.326 + do_test attach-2.12 {
1.327 + db_list db
1.328 + } {0 main 1 temp 2 db2}
1.329 + } else {
1.330 + do_test attach-2.12 {
1.331 + db_list db
1.332 + } {0 main 2 db2}
1.333 + }
1.334 +} ;# ifcapable schema_pragmas
1.335 +do_test attach-2.13 {
1.336 + catchsql {
1.337 + SELECT * FROM t2 WHERE x>5;
1.338 + }
1.339 +} {0 {21 x 22 y}}
1.340 +do_test attach-2.14 {
1.341 + execsql {
1.342 + SELECT type, name, tbl_name FROM sqlite_master;
1.343 + }
1.344 +} {table t1 t1 table tx tx}
1.345 +do_test attach-2.15 {
1.346 + execsql {
1.347 + SELECT type, name, tbl_name FROM db2.sqlite_master;
1.348 + }
1.349 +} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
1.350 +do_test attach-2.16 {
1.351 + db close
1.352 + sqlite3 db test.db
1.353 + execsql {
1.354 + ATTACH 'test2.db' AS db2;
1.355 + SELECT type, name, tbl_name FROM db2.sqlite_master;
1.356 + }
1.357 +} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
1.358 +} ;# End of ifcapable {trigger}
1.359 +
1.360 +do_test attach-3.1 {
1.361 + db close
1.362 + db2 close
1.363 + sqlite3 db test.db
1.364 + sqlite3 db2 test2.db
1.365 + execsql {
1.366 + SELECT * FROM t1
1.367 + }
1.368 +} {1 2 3 4}
1.369 +
1.370 +# If we are testing a version of the code that lacks trigger support,
1.371 +# adjust the database contents so that they are the same if triggers
1.372 +# had been enabled.
1.373 +ifcapable {!trigger} {
1.374 + db2 eval {
1.375 + DELETE FROM t2;
1.376 + INSERT INTO t2 VALUES(21, 'x');
1.377 + INSERT INTO t2 VALUES(22, 'y');
1.378 + CREATE TABLE tx(x1,x2,y1,y2);
1.379 + INSERT INTO tx VALUES(1, 11, 'x', 'x');
1.380 + INSERT INTO tx VALUES(2, 12, 'y', 'y');
1.381 + INSERT INTO tx VALUES(11, 21, 'x', 'x');
1.382 + INSERT INTO tx VALUES(12, 22, 'y', 'y');
1.383 + CREATE INDEX i2 ON t2(x);
1.384 + }
1.385 +}
1.386 +
1.387 +do_test attach-3.2 {
1.388 + catchsql {
1.389 + SELECT * FROM t2
1.390 + }
1.391 +} {1 {no such table: t2}}
1.392 +do_test attach-3.3 {
1.393 + catchsql {
1.394 + ATTACH DATABASE 'test2.db' AS db2;
1.395 + SELECT * FROM t2
1.396 + }
1.397 +} {0 {21 x 22 y}}
1.398 +
1.399 +# Even though 'db' has started a transaction, it should not yet have
1.400 +# a lock on test2.db so 'db2' should be readable.
1.401 +do_test attach-3.4 {
1.402 + execsql BEGIN
1.403 + catchsql {
1.404 + SELECT * FROM t2;
1.405 + } db2;
1.406 +} {0 {21 x 22 y}}
1.407 +
1.408 +# Reading from test2.db from db within a transaction should not
1.409 +# prevent test2.db from being read by db2.
1.410 +do_test attach-3.5 {
1.411 + execsql {SELECT * FROM t2}
1.412 + catchsql {
1.413 + SELECT * FROM t2;
1.414 + } db2;
1.415 +} {0 {21 x 22 y}}
1.416 +
1.417 +# Making a change to test2.db through db causes test2.db to get
1.418 +# a reserved lock. It should still be accessible through db2.
1.419 +do_test attach-3.6 {
1.420 + execsql {
1.421 + UPDATE t2 SET x=x+1 WHERE x=50;
1.422 + }
1.423 + catchsql {
1.424 + SELECT * FROM t2;
1.425 + } db2;
1.426 +} {0 {21 x 22 y}}
1.427 +
1.428 +do_test attach-3.7 {
1.429 + execsql ROLLBACK
1.430 + execsql {SELECT * FROM t2} db2
1.431 +} {21 x 22 y}
1.432 +
1.433 +# Start transactions on both db and db2. Once again, just because
1.434 +# we make a change to test2.db using db2, only a RESERVED lock is
1.435 +# obtained, so test2.db should still be readable using db.
1.436 +#
1.437 +do_test attach-3.8 {
1.438 + execsql BEGIN
1.439 + execsql BEGIN db2
1.440 + execsql {UPDATE t2 SET x=0 WHERE 0} db2
1.441 + catchsql {SELECT * FROM t2}
1.442 +} {0 {21 x 22 y}}
1.443 +
1.444 +# It is also still accessible from db2.
1.445 +do_test attach-3.9 {
1.446 + catchsql {SELECT * FROM t2} db2
1.447 +} {0 {21 x 22 y}}
1.448 +
1.449 +do_test attach-3.10 {
1.450 + execsql {SELECT * FROM t1}
1.451 +} {1 2 3 4}
1.452 +
1.453 +do_test attach-3.11 {
1.454 + catchsql {UPDATE t1 SET a=a+1}
1.455 +} {0 {}}
1.456 +do_test attach-3.12 {
1.457 + execsql {SELECT * FROM t1}
1.458 +} {2 2 4 4}
1.459 +
1.460 +# db2 has a RESERVED lock on test2.db, so db cannot write to any tables
1.461 +# in test2.db.
1.462 +do_test attach-3.13 {
1.463 + catchsql {UPDATE t2 SET x=x+1 WHERE x=50}
1.464 +} {1 {database is locked}}
1.465 +
1.466 +# Change for version 3. Transaction is no longer rolled back
1.467 +# for a locked database.
1.468 +execsql {ROLLBACK}
1.469 +
1.470 +# db is able to reread its schema because db2 still only holds a
1.471 +# reserved lock.
1.472 +do_test attach-3.14 {
1.473 + catchsql {SELECT * FROM t1}
1.474 +} {0 {1 2 3 4}}
1.475 +do_test attach-3.15 {
1.476 + execsql COMMIT db2
1.477 + execsql {SELECT * FROM t1}
1.478 +} {1 2 3 4}
1.479 +
1.480 +# Ticket #323
1.481 +do_test attach-4.1 {
1.482 + execsql {DETACH db2}
1.483 + db2 close
1.484 + sqlite3 db2 test2.db
1.485 + execsql {
1.486 + CREATE TABLE t3(x,y);
1.487 + CREATE UNIQUE INDEX t3i1 ON t3(x);
1.488 + INSERT INTO t3 VALUES(1,2);
1.489 + SELECT * FROM t3;
1.490 + } db2;
1.491 +} {1 2}
1.492 +do_test attach-4.2 {
1.493 + execsql {
1.494 + CREATE TABLE t3(a,b);
1.495 + CREATE UNIQUE INDEX t3i1b ON t3(a);
1.496 + INSERT INTO t3 VALUES(9,10);
1.497 + SELECT * FROM t3;
1.498 + }
1.499 +} {9 10}
1.500 +do_test attach-4.3 {
1.501 + execsql {
1.502 + ATTACH DATABASE 'test2.db' AS db2;
1.503 + SELECT * FROM db2.t3;
1.504 + }
1.505 +} {1 2}
1.506 +do_test attach-4.4 {
1.507 + execsql {
1.508 + SELECT * FROM main.t3;
1.509 + }
1.510 +} {9 10}
1.511 +do_test attach-4.5 {
1.512 + execsql {
1.513 + INSERT INTO db2.t3 VALUES(9,10);
1.514 + SELECT * FROM db2.t3;
1.515 + }
1.516 +} {1 2 9 10}
1.517 +execsql {
1.518 + DETACH db2;
1.519 +}
1.520 +ifcapable {trigger} {
1.521 + do_test attach-4.6 {
1.522 + execsql {
1.523 + CREATE TABLE t4(x);
1.524 + CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
1.525 + INSERT INTO t4 VALUES('db2.' || NEW.x);
1.526 + END;
1.527 + INSERT INTO t3 VALUES(6,7);
1.528 + SELECT * FROM t4;
1.529 + } db2
1.530 + } {db2.6}
1.531 + do_test attach-4.7 {
1.532 + execsql {
1.533 + CREATE TABLE t4(y);
1.534 + CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
1.535 + INSERT INTO t4 VALUES('main.' || NEW.a);
1.536 + END;
1.537 + INSERT INTO main.t3 VALUES(11,12);
1.538 + SELECT * FROM main.t4;
1.539 + }
1.540 + } {main.11}
1.541 +}
1.542 +ifcapable {!trigger} {
1.543 + # When we do not have trigger support, set up the table like they
1.544 + # would have been had triggers been there. The tests that follow need
1.545 + # this setup.
1.546 + execsql {
1.547 + CREATE TABLE t4(x);
1.548 + INSERT INTO t3 VALUES(6,7);
1.549 + INSERT INTO t4 VALUES('db2.6');
1.550 + INSERT INTO t4 VALUES('db2.13');
1.551 + } db2
1.552 + execsql {
1.553 + CREATE TABLE t4(y);
1.554 + INSERT INTO main.t3 VALUES(11,12);
1.555 + INSERT INTO t4 VALUES('main.11');
1.556 + }
1.557 +}
1.558 +
1.559 +
1.560 +# This one is tricky. On the UNION ALL select, we have to make sure
1.561 +# the schema for both main and db2 is valid before starting to execute
1.562 +# the first query of the UNION ALL. If we wait to test the validity of
1.563 +# the schema for main until after the first query has run, that test will
1.564 +# fail and the query will abort but we will have already output some
1.565 +# results. When the query is retried, the results will be repeated.
1.566 +#
1.567 +ifcapable compound {
1.568 +do_test attach-4.8 {
1.569 + execsql {
1.570 + ATTACH DATABASE 'test2.db' AS db2;
1.571 + INSERT INTO db2.t3 VALUES(13,14);
1.572 + SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
1.573 + }
1.574 +} {db2.6 db2.13 main.11}
1.575 +
1.576 +do_test attach-4.9 {
1.577 + ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
1.578 + execsql {
1.579 + INSERT INTO main.t3 VALUES(15,16);
1.580 + SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
1.581 + }
1.582 +} {db2.6 db2.13 main.11 main.15}
1.583 +} ;# ifcapable compound
1.584 +
1.585 +ifcapable !compound {
1.586 + ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
1.587 + execsql {
1.588 + ATTACH DATABASE 'test2.db' AS db2;
1.589 + INSERT INTO db2.t3 VALUES(13,14);
1.590 + INSERT INTO main.t3 VALUES(15,16);
1.591 + }
1.592 +} ;# ifcapable !compound
1.593 +
1.594 +ifcapable view {
1.595 +do_test attach-4.10 {
1.596 + execsql {
1.597 + DETACH DATABASE db2;
1.598 + }
1.599 + execsql {
1.600 + CREATE VIEW v3 AS SELECT x*100+y FROM t3;
1.601 + SELECT * FROM v3;
1.602 + } db2
1.603 +} {102 910 607 1314}
1.604 +do_test attach-4.11 {
1.605 + execsql {
1.606 + CREATE VIEW v3 AS SELECT a*100+b FROM t3;
1.607 + SELECT * FROM v3;
1.608 + }
1.609 +} {910 1112 1516}
1.610 +do_test attach-4.12 {
1.611 + execsql {
1.612 + ATTACH DATABASE 'test2.db' AS db2;
1.613 + SELECT * FROM db2.v3;
1.614 + }
1.615 +} {102 910 607 1314}
1.616 +do_test attach-4.13 {
1.617 + execsql {
1.618 + SELECT * FROM main.v3;
1.619 + }
1.620 +} {910 1112 1516}
1.621 +} ;# ifcapable view
1.622 +
1.623 +# Tests for the sqliteFix...() routines in attach.c
1.624 +#
1.625 +ifcapable {trigger} {
1.626 +do_test attach-5.1 {
1.627 + db close
1.628 + sqlite3 db test.db
1.629 + db2 close
1.630 + file delete -force test2.db
1.631 + sqlite3 db2 test2.db
1.632 + catchsql {
1.633 + ATTACH DATABASE 'test.db' AS orig;
1.634 + CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN
1.635 + SELECT 'no-op';
1.636 + END;
1.637 + } db2
1.638 +} {1 {trigger r1 cannot reference objects in database orig}}
1.639 +do_test attach-5.2 {
1.640 + catchsql {
1.641 + CREATE TABLE t5(x,y);
1.642 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.643 + SELECT 'no-op';
1.644 + END;
1.645 + } db2
1.646 +} {0 {}}
1.647 +do_test attach-5.3 {
1.648 + catchsql {
1.649 + DROP TRIGGER r5;
1.650 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.651 + SELECT 'no-op' FROM orig.t1;
1.652 + END;
1.653 + } db2
1.654 +} {1 {trigger r5 cannot reference objects in database orig}}
1.655 +ifcapable tempdb {
1.656 + do_test attach-5.4 {
1.657 + catchsql {
1.658 + CREATE TEMP TABLE t6(p,q,r);
1.659 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.660 + SELECT 'no-op' FROM temp.t6;
1.661 + END;
1.662 + } db2
1.663 + } {1 {trigger r5 cannot reference objects in database temp}}
1.664 +}
1.665 +ifcapable subquery {
1.666 + do_test attach-5.5 {
1.667 + catchsql {
1.668 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.669 + SELECT 'no-op' || (SELECT * FROM temp.t6);
1.670 + END;
1.671 + } db2
1.672 + } {1 {trigger r5 cannot reference objects in database temp}}
1.673 + do_test attach-5.6 {
1.674 + catchsql {
1.675 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.676 + SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
1.677 + END;
1.678 + } db2
1.679 + } {1 {trigger r5 cannot reference objects in database temp}}
1.680 + do_test attach-5.7 {
1.681 + catchsql {
1.682 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.683 + SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);
1.684 + END;
1.685 + } db2
1.686 + } {1 {trigger r5 cannot reference objects in database temp}}
1.687 + do_test attach-5.7 {
1.688 + catchsql {
1.689 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.690 + SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;
1.691 + END;
1.692 + } db2
1.693 + } {1 {trigger r5 cannot reference objects in database temp}}
1.694 + do_test attach-5.8 {
1.695 + catchsql {
1.696 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.697 + INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);
1.698 + END;
1.699 + } db2
1.700 + } {1 {trigger r5 cannot reference objects in database temp}}
1.701 + do_test attach-5.9 {
1.702 + catchsql {
1.703 + CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
1.704 + DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
1.705 + END;
1.706 + } db2
1.707 + } {1 {trigger r5 cannot reference objects in database temp}}
1.708 +} ;# endif subquery
1.709 +} ;# endif trigger
1.710 +
1.711 +# Check to make sure we get a sensible error if unable to open
1.712 +# the file that we are trying to attach.
1.713 +#
1.714 +do_test attach-6.1 {
1.715 + catchsql {
1.716 + ATTACH DATABASE 'no-such-file' AS nosuch;
1.717 + }
1.718 +} {0 {}}
1.719 +if {$tcl_platform(platform)=="unix"} {
1.720 + do_test attach-6.2 {
1.721 + sqlite3 dbx cannot-read
1.722 + dbx eval {CREATE TABLE t1(a,b,c)}
1.723 + dbx close
1.724 + file attributes cannot-read -permission 0000
1.725 + if {[file writable cannot-read]} {
1.726 + puts "\n**** Tests do not work when run as root ****"
1.727 + file delete -force cannot-read
1.728 + exit 1
1.729 + }
1.730 + catchsql {
1.731 + ATTACH DATABASE 'cannot-read' AS noread;
1.732 + }
1.733 + } {1 {unable to open database: cannot-read}}
1.734 + do_test attach-6.2.2 {
1.735 + db errorcode
1.736 + } {14}
1.737 + file delete -force cannot-read
1.738 +}
1.739 +
1.740 +# Check the error message if we try to access a database that has
1.741 +# not been attached.
1.742 +do_test attach-6.3 {
1.743 + catchsql {
1.744 + CREATE TABLE no_such_db.t1(a, b, c);
1.745 + }
1.746 +} {1 {unknown database no_such_db}}
1.747 +for {set i 2} {$i<=15} {incr i} {
1.748 + catch {db$i close}
1.749 +}
1.750 +db close
1.751 +file delete -force test2.db
1.752 +file delete -force no-such-file
1.753 +
1.754 +ifcapable subquery {
1.755 + do_test attach-7.1 {
1.756 + file delete -force test.db test.db-journal
1.757 + sqlite3 db test.db
1.758 + catchsql {
1.759 + DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY
1.760 + REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL )
1.761 + }
1.762 + } {1 {invalid name: "RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY
1.763 + REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL )"}}
1.764 +}
1.765 +
1.766 +# Create a malformed file (a file that is not a valid database)
1.767 +# and try to attach it
1.768 +#
1.769 +do_test attach-8.1 {
1.770 + set fd [open test2.db w]
1.771 + puts $fd "This file is not a valid SQLite database"
1.772 + close $fd
1.773 + catchsql {
1.774 + ATTACH 'test2.db' AS t2;
1.775 + }
1.776 +} {1 {file is encrypted or is not a database}}
1.777 +do_test attach-8.2 {
1.778 + db errorcode
1.779 +} {26}
1.780 +file delete -force test2.db
1.781 +do_test attach-8.3 {
1.782 + sqlite3 db2 test2.db
1.783 + db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE}
1.784 + catchsql {
1.785 + ATTACH 'test2.db' AS t2;
1.786 + }
1.787 +} {1 {database is locked}}
1.788 +do_test attach-8.4 {
1.789 + db errorcode
1.790 +} {5}
1.791 +db2 close
1.792 +file delete -force test2.db
1.793 +
1.794 +
1.795 +finish_test