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