1# 2003 April 4 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: attach.test,v 1.52 2009/05/29 14:39:08 drh Exp $ 16# 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21ifcapable !attach { 22 finish_test 23 return 24} 25 26for {set i 2} {$i<=15} {incr i} { 27 file delete -force test$i.db 28 file delete -force test$i.db-journal 29} 30 31do_test attach-1.1 { 32 execsql { 33 CREATE TABLE t1(a,b); 34 INSERT INTO t1 VALUES(1,2); 35 INSERT INTO t1 VALUES(3,4); 36 SELECT * FROM t1; 37 } 38} {1 2 3 4} 39do_test attach-1.2 { 40 sqlite3 db2 test2.db 41 execsql { 42 CREATE TABLE t2(x,y); 43 INSERT INTO t2 VALUES(1,'x'); 44 INSERT INTO t2 VALUES(2,'y'); 45 SELECT * FROM t2; 46 } db2 47} {1 x 2 y} 48do_test attach-1.3 { 49 execsql { 50 ATTACH DATABASE 'test2.db' AS two; 51 SELECT * FROM two.t2; 52 } 53} {1 x 2 y} 54do_test attach-1.4 { 55 execsql { 56 SELECT * FROM t2; 57 } 58} {1 x 2 y} 59do_test attach-1.5 { 60 execsql { 61 DETACH DATABASE two; 62 SELECT * FROM t1; 63 } 64} {1 2 3 4} 65do_test attach-1.6 { 66 catchsql { 67 SELECT * FROM t2; 68 } 69} {1 {no such table: t2}} 70do_test attach-1.7 { 71 catchsql { 72 SELECT * FROM two.t2; 73 } 74} {1 {no such table: two.t2}} 75do_test attach-1.8 { 76 catchsql { 77 ATTACH DATABASE 'test3.db' AS three; 78 } 79} {0 {}} 80do_test attach-1.9 { 81 catchsql { 82 SELECT * FROM three.sqlite_master; 83 } 84} {0 {}} 85do_test attach-1.10 { 86 catchsql { 87 DETACH DATABASE [three]; 88 } 89} {0 {}} 90do_test attach-1.11 { 91 execsql { 92 ATTACH 'test.db' AS db2; 93 ATTACH 'test.db' AS db3; 94 ATTACH 'test.db' AS db4; 95 ATTACH 'test.db' AS db5; 96 ATTACH 'test.db' AS db6; 97 ATTACH 'test.db' AS db7; 98 ATTACH 'test.db' AS db8; 99 ATTACH 'test.db' AS db9; 100 } 101} {} 102proc db_list {db} { 103 set list {} 104 foreach {idx name file} [execsql {PRAGMA database_list} $db] { 105 lappend list $idx $name 106 } 107 return $list 108} 109ifcapable schema_pragmas { 110do_test attach-1.11b { 111 db_list db 112} {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9} 113} ;# ifcapable schema_pragmas 114do_test attach-1.12 { 115 catchsql { 116 ATTACH 'test.db' as db2; 117 } 118} {1 {database db2 is already in use}} 119do_test attach-1.12.2 { 120 db errorcode 121} {1} 122do_test attach-1.13 { 123 catchsql { 124 ATTACH 'test.db' as db5; 125 } 126} {1 {database db5 is already in use}} 127do_test attach-1.14 { 128 catchsql { 129 ATTACH 'test.db' as db9; 130 } 131} {1 {database db9 is already in use}} 132do_test attach-1.15 { 133 catchsql { 134 ATTACH 'test.db' as main; 135 } 136} {1 {database main is already in use}} 137ifcapable tempdb { 138 do_test attach-1.16 { 139 catchsql { 140 ATTACH 'test.db' as temp; 141 } 142 } {1 {database temp is already in use}} 143} 144do_test attach-1.17 { 145 catchsql { 146 ATTACH 'test.db' as MAIN; 147 } 148} {1 {database MAIN is already in use}} 149do_test attach-1.18 { 150 catchsql { 151 ATTACH 'test.db' as db10; 152 ATTACH 'test.db' as db11; 153 } 154} {0 {}} 155if {$SQLITE_MAX_ATTACHED==10} { 156 do_test attach-1.19 { 157 catchsql { 158 ATTACH 'test.db' as db12; 159 } 160 } {1 {too many attached databases - max 10}} 161 do_test attach-1.19.1 { 162 db errorcode 163 } {1} 164} 165do_test attach-1.20.1 { 166 execsql { 167 DETACH db5; 168 } 169} {} 170ifcapable schema_pragmas { 171do_test attach-1.20.2 { 172 db_list db 173} {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11} 174} ;# ifcapable schema_pragmas 175integrity_check attach-1.20.3 176ifcapable tempdb { 177 execsql {select * from sqlite_temp_master} 178} 179do_test attach-1.21 { 180 catchsql { 181 ATTACH 'test.db' as db12; 182 } 183} {0 {}} 184if {$SQLITE_MAX_ATTACHED==10} { 185 do_test attach-1.22 { 186 catchsql { 187 ATTACH 'test.db' as db13; 188 } 189 } {1 {too many attached databases - max 10}} 190 do_test attach-1.22.1 { 191 db errorcode 192 } {1} 193} 194do_test attach-1.23 { 195 catchsql { 196 DETACH "db14"; 197 } 198} {1 {no such database: db14}} 199do_test attach-1.24 { 200 catchsql { 201 DETACH db12; 202 } 203} {0 {}} 204do_test attach-1.25 { 205 catchsql { 206 DETACH db12; 207 } 208} {1 {no such database: db12}} 209do_test attach-1.26 { 210 catchsql { 211 DETACH main; 212 } 213} {1 {cannot detach database main}} 214 215ifcapable tempdb { 216 do_test attach-1.27 { 217 catchsql { 218 DETACH Temp; 219 } 220 } {1 {cannot detach database Temp}} 221} else { 222 do_test attach-1.27 { 223 catchsql { 224 DETACH Temp; 225 } 226 } {1 {no such database: Temp}} 227} 228 229do_test attach-1.28 { 230 catchsql { 231 DETACH db11; 232 DETACH db10; 233 DETACH db9; 234 DETACH db8; 235 DETACH db7; 236 DETACH db6; 237 DETACH db4; 238 DETACH db3; 239 DETACH db2; 240 } 241} {0 {}} 242ifcapable schema_pragmas { 243 ifcapable tempdb { 244 do_test attach-1.29 { 245 db_list db 246 } {0 main 1 temp} 247 } else { 248 do_test attach-1.29 { 249 db_list db 250 } {0 main} 251 } 252} ;# ifcapable schema_pragmas 253 254ifcapable {trigger} { # Only do the following tests if triggers are enabled 255do_test attach-2.1 { 256 execsql { 257 CREATE TABLE tx(x1,x2,y1,y2); 258 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 259 INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y); 260 END; 261 SELECT * FROM tx; 262 } db2; 263} {} 264do_test attach-2.2 { 265 execsql { 266 UPDATE t2 SET x=x+10; 267 SELECT * FROM tx; 268 } db2; 269} {1 11 x x 2 12 y y} 270do_test attach-2.3 { 271 execsql { 272 CREATE TABLE tx(x1,x2,y1,y2); 273 SELECT * FROM tx; 274 } 275} {} 276do_test attach-2.4 { 277 execsql { 278 ATTACH 'test2.db' AS db2; 279 } 280} {} 281do_test attach-2.5 { 282 execsql { 283 UPDATE db2.t2 SET x=x+10; 284 SELECT * FROM db2.tx; 285 } 286} {1 11 x x 2 12 y y 11 21 x x 12 22 y y} 287do_test attach-2.6 { 288 execsql { 289 SELECT * FROM main.tx; 290 } 291} {} 292do_test attach-2.7 { 293 execsql { 294 SELECT type, name, tbl_name FROM db2.sqlite_master; 295 } 296} {table t2 t2 table tx tx trigger r1 t2} 297 298ifcapable schema_pragmas&&tempdb { 299 do_test attach-2.8 { 300 db_list db 301 } {0 main 1 temp 2 db2} 302} ;# ifcapable schema_pragmas&&tempdb 303ifcapable schema_pragmas&&!tempdb { 304 do_test attach-2.8 { 305 db_list db 306 } {0 main 2 db2} 307} ;# ifcapable schema_pragmas&&!tempdb 308 309do_test attach-2.9 { 310 execsql { 311 CREATE INDEX i2 ON t2(x); 312 SELECT * FROM t2 WHERE x>5; 313 } db2 314} {21 x 22 y} 315do_test attach-2.10 { 316 execsql { 317 SELECT type, name, tbl_name FROM sqlite_master; 318 } db2 319} {table t2 t2 table tx tx trigger r1 t2 index i2 t2} 320#do_test attach-2.11 { 321# catchsql { 322# SELECT * FROM t2 WHERE x>5; 323# } 324#} {1 {database schema has changed}} 325ifcapable schema_pragmas { 326 ifcapable tempdb { 327 do_test attach-2.12 { 328 db_list db 329 } {0 main 1 temp 2 db2} 330 } else { 331 do_test attach-2.12 { 332 db_list db 333 } {0 main 2 db2} 334 } 335} ;# ifcapable schema_pragmas 336do_test attach-2.13 { 337 catchsql { 338 SELECT * FROM t2 WHERE x>5; 339 } 340} {0 {21 x 22 y}} 341do_test attach-2.14 { 342 execsql { 343 SELECT type, name, tbl_name FROM sqlite_master; 344 } 345} {table t1 t1 table tx tx} 346do_test attach-2.15 { 347 execsql { 348 SELECT type, name, tbl_name FROM db2.sqlite_master; 349 } 350} {table t2 t2 table tx tx trigger r1 t2 index i2 t2} 351do_test attach-2.16 { 352 db close 353 sqlite3 db test.db 354 execsql { 355 ATTACH 'test2.db' AS db2; 356 SELECT type, name, tbl_name FROM db2.sqlite_master; 357 } 358} {table t2 t2 table tx tx trigger r1 t2 index i2 t2} 359} ;# End of ifcapable {trigger} 360 361do_test attach-3.1 { 362 db close 363 db2 close 364 sqlite3 db test.db 365 sqlite3 db2 test2.db 366 execsql { 367 SELECT * FROM t1 368 } 369} {1 2 3 4} 370 371# If we are testing a version of the code that lacks trigger support, 372# adjust the database contents so that they are the same if triggers 373# had been enabled. 374ifcapable {!trigger} { 375 db2 eval { 376 DELETE FROM t2; 377 INSERT INTO t2 VALUES(21, 'x'); 378 INSERT INTO t2 VALUES(22, 'y'); 379 CREATE TABLE tx(x1,x2,y1,y2); 380 INSERT INTO tx VALUES(1, 11, 'x', 'x'); 381 INSERT INTO tx VALUES(2, 12, 'y', 'y'); 382 INSERT INTO tx VALUES(11, 21, 'x', 'x'); 383 INSERT INTO tx VALUES(12, 22, 'y', 'y'); 384 CREATE INDEX i2 ON t2(x); 385 } 386} 387 388do_test attach-3.2 { 389 catchsql { 390 SELECT * FROM t2 391 } 392} {1 {no such table: t2}} 393do_test attach-3.3 { 394 catchsql { 395 ATTACH DATABASE 'test2.db' AS db2; 396 SELECT * FROM t2 397 } 398} {0 {21 x 22 y}} 399 400# Even though 'db' has started a transaction, it should not yet have 401# a lock on test2.db so 'db2' should be readable. 402do_test attach-3.4 { 403 execsql BEGIN 404 catchsql { 405 SELECT * FROM t2; 406 } db2; 407} {0 {21 x 22 y}} 408 409# Reading from test2.db from db within a transaction should not 410# prevent test2.db from being read by db2. 411do_test attach-3.5 { 412 execsql {SELECT * FROM t2} 413 catchsql { 414 SELECT * FROM t2; 415 } db2; 416} {0 {21 x 22 y}} 417 418# Making a change to test2.db through db causes test2.db to get 419# a reserved lock. It should still be accessible through db2. 420do_test attach-3.6 { 421 execsql { 422 UPDATE t2 SET x=x+1 WHERE x=50; 423 } 424 catchsql { 425 SELECT * FROM t2; 426 } db2; 427} {0 {21 x 22 y}} 428 429do_test attach-3.7 { 430 execsql ROLLBACK 431 execsql {SELECT * FROM t2} db2 432} {21 x 22 y} 433 434# Start transactions on both db and db2. Once again, just because 435# we make a change to test2.db using db2, only a RESERVED lock is 436# obtained, so test2.db should still be readable using db. 437# 438do_test attach-3.8 { 439 execsql BEGIN 440 execsql BEGIN db2 441 execsql {UPDATE t2 SET x=0 WHERE 0} db2 442 catchsql {SELECT * FROM t2} 443} {0 {21 x 22 y}} 444 445# It is also still accessible from db2. 446do_test attach-3.9 { 447 catchsql {SELECT * FROM t2} db2 448} {0 {21 x 22 y}} 449 450do_test attach-3.10 { 451 execsql {SELECT * FROM t1} 452} {1 2 3 4} 453 454do_test attach-3.11 { 455 catchsql {UPDATE t1 SET a=a+1} 456} {0 {}} 457do_test attach-3.12 { 458 execsql {SELECT * FROM t1} 459} {2 2 4 4} 460 461# db2 has a RESERVED lock on test2.db, so db cannot write to any tables 462# in test2.db. 463do_test attach-3.13 { 464 catchsql {UPDATE t2 SET x=x+1 WHERE x=50} 465} {1 {database is locked}} 466 467# Change for version 3. Transaction is no longer rolled back 468# for a locked database. 469execsql {ROLLBACK} 470 471# db is able to reread its schema because db2 still only holds a 472# reserved lock. 473do_test attach-3.14 { 474 catchsql {SELECT * FROM t1} 475} {0 {1 2 3 4}} 476do_test attach-3.15 { 477 execsql COMMIT db2 478 execsql {SELECT * FROM t1} 479} {1 2 3 4} 480 481# Ticket #323 482do_test attach-4.1 { 483 execsql {DETACH db2} 484 db2 close 485 sqlite3 db2 test2.db 486 execsql { 487 CREATE TABLE t3(x,y); 488 CREATE UNIQUE INDEX t3i1 ON t3(x); 489 INSERT INTO t3 VALUES(1,2); 490 SELECT * FROM t3; 491 } db2; 492} {1 2} 493do_test attach-4.2 { 494 execsql { 495 CREATE TABLE t3(a,b); 496 CREATE UNIQUE INDEX t3i1b ON t3(a); 497 INSERT INTO t3 VALUES(9,10); 498 SELECT * FROM t3; 499 } 500} {9 10} 501do_test attach-4.3 { 502 execsql { 503 ATTACH DATABASE 'test2.db' AS db2; 504 SELECT * FROM db2.t3; 505 } 506} {1 2} 507do_test attach-4.4 { 508 execsql { 509 SELECT * FROM main.t3; 510 } 511} {9 10} 512do_test attach-4.5 { 513 execsql { 514 INSERT INTO db2.t3 VALUES(9,10); 515 SELECT * FROM db2.t3; 516 } 517} {1 2 9 10} 518execsql { 519 DETACH db2; 520} 521ifcapable {trigger} { 522 do_test attach-4.6 { 523 execsql { 524 CREATE TABLE t4(x); 525 CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN 526 INSERT INTO t4 VALUES('db2.' || NEW.x); 527 END; 528 INSERT INTO t3 VALUES(6,7); 529 SELECT * FROM t4; 530 } db2 531 } {db2.6} 532 do_test attach-4.7 { 533 execsql { 534 CREATE TABLE t4(y); 535 CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN 536 INSERT INTO t4 VALUES('main.' || NEW.a); 537 END; 538 INSERT INTO main.t3 VALUES(11,12); 539 SELECT * FROM main.t4; 540 } 541 } {main.11} 542} 543ifcapable {!trigger} { 544 # When we do not have trigger support, set up the table like they 545 # would have been had triggers been there. The tests that follow need 546 # this setup. 547 execsql { 548 CREATE TABLE t4(x); 549 INSERT INTO t3 VALUES(6,7); 550 INSERT INTO t4 VALUES('db2.6'); 551 INSERT INTO t4 VALUES('db2.13'); 552 } db2 553 execsql { 554 CREATE TABLE t4(y); 555 INSERT INTO main.t3 VALUES(11,12); 556 INSERT INTO t4 VALUES('main.11'); 557 } 558} 559 560 561# This one is tricky. On the UNION ALL select, we have to make sure 562# the schema for both main and db2 is valid before starting to execute 563# the first query of the UNION ALL. If we wait to test the validity of 564# the schema for main until after the first query has run, that test will 565# fail and the query will abort but we will have already output some 566# results. When the query is retried, the results will be repeated. 567# 568ifcapable compound { 569do_test attach-4.8 { 570 execsql { 571 ATTACH DATABASE 'test2.db' AS db2; 572 INSERT INTO db2.t3 VALUES(13,14); 573 SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; 574 } 575} {db2.6 db2.13 main.11} 576 577do_test attach-4.9 { 578 ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} 579 execsql { 580 INSERT INTO main.t3 VALUES(15,16); 581 SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; 582 } 583} {db2.6 db2.13 main.11 main.15} 584} ;# ifcapable compound 585 586ifcapable !compound { 587 ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} 588 execsql { 589 ATTACH DATABASE 'test2.db' AS db2; 590 INSERT INTO db2.t3 VALUES(13,14); 591 INSERT INTO main.t3 VALUES(15,16); 592 } 593} ;# ifcapable !compound 594 595ifcapable view { 596do_test attach-4.10 { 597 execsql { 598 DETACH DATABASE db2; 599 } 600 execsql { 601 CREATE VIEW v3 AS SELECT x*100+y FROM t3; 602 SELECT * FROM v3; 603 } db2 604} {102 910 607 1314} 605do_test attach-4.11 { 606 execsql { 607 CREATE VIEW v3 AS SELECT a*100+b FROM t3; 608 SELECT * FROM v3; 609 } 610} {910 1112 1516} 611do_test attach-4.12 { 612 execsql { 613 ATTACH DATABASE 'test2.db' AS db2; 614 SELECT * FROM db2.v3; 615 } 616} {102 910 607 1314} 617do_test attach-4.13 { 618 execsql { 619 SELECT * FROM main.v3; 620 } 621} {910 1112 1516} 622} ;# ifcapable view 623 624# Tests for the sqliteFix...() routines in attach.c 625# 626ifcapable {trigger} { 627do_test attach-5.1 { 628 db close 629 sqlite3 db test.db 630 db2 close 631 file delete -force test2.db 632 sqlite3 db2 test2.db 633 catchsql { 634 ATTACH DATABASE 'test.db' AS orig; 635 CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN 636 SELECT 'no-op'; 637 END; 638 } db2 639} {1 {trigger r1 cannot reference objects in database orig}} 640do_test attach-5.2 { 641 catchsql { 642 CREATE TABLE t5(x,y); 643 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 644 SELECT 'no-op'; 645 END; 646 } db2 647} {0 {}} 648do_test attach-5.3 { 649 catchsql { 650 DROP TRIGGER r5; 651 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 652 SELECT 'no-op' FROM orig.t1; 653 END; 654 } db2 655} {1 {trigger r5 cannot reference objects in database orig}} 656ifcapable tempdb { 657 do_test attach-5.4 { 658 catchsql { 659 CREATE TEMP TABLE t6(p,q,r); 660 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 661 SELECT 'no-op' FROM temp.t6; 662 END; 663 } db2 664 } {1 {trigger r5 cannot reference objects in database temp}} 665} 666ifcapable subquery { 667 do_test attach-5.5 { 668 catchsql { 669 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 670 SELECT 'no-op' || (SELECT * FROM temp.t6); 671 END; 672 } db2 673 } {1 {trigger r5 cannot reference objects in database temp}} 674 do_test attach-5.6 { 675 catchsql { 676 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 677 SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); 678 END; 679 } db2 680 } {1 {trigger r5 cannot reference objects in database temp}} 681 do_test attach-5.7 { 682 catchsql { 683 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 684 SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6); 685 END; 686 } db2 687 } {1 {trigger r5 cannot reference objects in database temp}} 688 do_test attach-5.7 { 689 catchsql { 690 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 691 SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1; 692 END; 693 } db2 694 } {1 {trigger r5 cannot reference objects in database temp}} 695 do_test attach-5.8 { 696 catchsql { 697 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 698 INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5); 699 END; 700 } db2 701 } {1 {trigger r5 cannot reference objects in database temp}} 702 do_test attach-5.9 { 703 catchsql { 704 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN 705 DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); 706 END; 707 } db2 708 } {1 {trigger r5 cannot reference objects in database temp}} 709} ;# endif subquery 710} ;# endif trigger 711 712# Check to make sure we get a sensible error if unable to open 713# the file that we are trying to attach. 714# 715do_test attach-6.1 { 716 catchsql { 717 ATTACH DATABASE 'no-such-file' AS nosuch; 718 } 719} {0 {}} 720if {$tcl_platform(platform)=="unix"} { 721 do_test attach-6.2 { 722 sqlite3 dbx cannot-read 723 dbx eval {CREATE TABLE t1(a,b,c)} 724 dbx close 725 file attributes cannot-read -permission 0000 726 if {[file writable cannot-read]} { 727 puts "\n**** Tests do not work when run as root ****" 728 file delete -force cannot-read 729 exit 1 730 } 731 catchsql { 732 ATTACH DATABASE 'cannot-read' AS noread; 733 } 734 } {1 {unable to open database: cannot-read}} 735 do_test attach-6.2.2 { 736 db errorcode 737 } {14} 738 file delete -force cannot-read 739} 740 741# Check the error message if we try to access a database that has 742# not been attached. 743do_test attach-6.3 { 744 catchsql { 745 CREATE TABLE no_such_db.t1(a, b, c); 746 } 747} {1 {unknown database no_such_db}} 748for {set i 2} {$i<=15} {incr i} { 749 catch {db$i close} 750} 751db close 752file delete -force test2.db 753file delete -force no-such-file 754 755ifcapable subquery { 756 do_test attach-7.1 { 757 file delete -force test.db test.db-journal 758 sqlite3 db test.db 759 catchsql { 760 DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY 761 REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL ) 762 } 763 } {1 {no such table: AAAAAA}} 764} 765 766# Create a malformed file (a file that is not a valid database) 767# and try to attach it 768# 769do_test attach-8.1 { 770 set fd [open test2.db w] 771 puts $fd "This file is not a valid SQLite database" 772 close $fd 773 catchsql { 774 ATTACH 'test2.db' AS t2; 775 } 776} {1 {file is encrypted or is not a database}} 777do_test attach-8.2 { 778 db errorcode 779} {26} 780file delete -force test2.db 781do_test attach-8.3 { 782 sqlite3 db2 test2.db 783 db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE} 784 catchsql { 785 ATTACH 'test2.db' AS t2; 786 } 787} {1 {database is locked}} 788do_test attach-8.4 { 789 db errorcode 790} {5} 791db2 close 792file delete -force test2.db 793 794# Test that it is possible to attach the same database more than 795# once when not in shared-cache mode. That this is not possible in 796# shared-cache mode is tested in shared7.test. 797do_test attach-9.1 { 798 file delete -force test4.db 799 execsql { 800 ATTACH 'test4.db' AS aux1; 801 CREATE TABLE aux1.t1(a, b); 802 INSERT INTO aux1.t1 VALUES(1, 2); 803 ATTACH 'test4.db' AS aux2; 804 SELECT * FROM aux2.t1; 805 } 806} {1 2} 807do_test attach-9.2 { 808 catchsql { 809 BEGIN; 810 INSERT INTO aux1.t1 VALUES(3, 4); 811 INSERT INTO aux2.t1 VALUES(5, 6); 812 } 813} {1 {database is locked}} 814do_test attach-9.3 { 815 execsql { 816 COMMIT; 817 SELECT * FROM aux2.t1; 818 } 819} {1 2 3 4} 820 821# Ticket [abe728bbc311d81334dae9762f0db87c07a98f79]. 822# Multi-database commit on an attached TEMP database. 823# 824do_test attach-10.1 { 825 execsql { 826 ATTACH '' AS noname; 827 ATTACH ':memory:' AS inmem; 828 BEGIN; 829 CREATE TABLE noname.noname(x); 830 CREATE TABLE inmem.inmem(y); 831 CREATE TABLE main.main(z); 832 COMMIT; 833 SELECT name FROM noname.sqlite_master; 834 SELECT name FROM inmem.sqlite_master; 835 } 836} {noname inmem} 837do_test attach-10.2 { 838 lrange [execsql { 839 PRAGMA database_list; 840 }] 9 end 841} {4 noname {} 5 inmem {}} 842 843finish_test 844