AryaWu/sqlite
0
1# 2003 April 42#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing the ATTACH and DETACH commands13# 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.tcl20 21ifcapable !attach {22 finish_test23 return24}25 26for {set i 2} {$i<=15} {incr i} {27 forcedelete test$i.db28 forcedelete test$i.db-journal29}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.db41 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 } db247} {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}54 55# Tests for the sqlite3_db_filename interface56#57do_test attach-1.3.1 {58 file tail [sqlite3_db_filename db main]59} {test.db}60do_test attach-1.3.2 {61 file tail [sqlite3_db_filename db MAIN]62} {test.db}63do_test attach-1.3.3 {64 file tail [sqlite3_db_filename db temp]65} {}66do_test attach-1.3.4 {67 file tail [sqlite3_db_filename db two]68} {test2.db}69do_test attach-1.3.5 {70 file tail [sqlite3_db_filename db three]71} {}72 73do_test attach-1.4 {74 execsql {75 SELECT * FROM t2;76 }77} {1 x 2 y}78do_test attach-1.5 {79 execsql {80 DETACH DATABASE two;81 SELECT * FROM t1;82 }83} {1 2 3 4}84do_test attach-1.6 {85 catchsql {86 SELECT * FROM t2;87 }88} {1 {no such table: t2}}89do_test attach-1.7 {90 catchsql {91 SELECT * FROM two.t2;92 }93} {1 {no such table: two.t2}}94do_test attach-1.8 {95 catchsql {96 ATTACH DATABASE 'test3.db' AS three;97 }98} {0 {}}99do_test attach-1.9 {100 catchsql {101 SELECT * FROM three.sqlite_master;102 }103} {0 {}}104do_test attach-1.10 {105 catchsql {106 DETACH DATABASE [three];107 }108} {0 {}}109do_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} {}121proc db_list {db} {122 set list {}123 foreach {idx name file} [execsql {PRAGMA database_list} $db] {124 lappend list $idx $name125 }126 return $list127}128ifcapable schema_pragmas {129do_test attach-1.11b {130 db_list db131} {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}132} ;# ifcapable schema_pragmas 133do_test attach-1.12 {134 catchsql {135 ATTACH 'test.db' as db2;136 }137} {1 {database db2 is already in use}}138do_test attach-1.12.2 {139 db errorcode140} {1}141do_test attach-1.13 {142 catchsql {143 ATTACH 'test.db' as db5;144 }145} {1 {database db5 is already in use}}146do_test attach-1.14 {147 catchsql {148 ATTACH 'test.db' as db9;149 }150} {1 {database db9 is already in use}}151do_catchsql_test attach-1.15 {152 ATTACH 'test.db' as main;153} {1 {database main is already in use}}154ifcapable 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}161do_catchsql_test attach-1.17 {162 ATTACH 'test.db' as MAIN;163} {1 {database MAIN is already in use}}164do_test attach-1.18 {165 catchsql {166 ATTACH 'test.db' as db10;167 ATTACH 'test.db' as db11;168 }169} {0 {}}170if {$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 errorcode178 } {1}179}180do_test attach-1.20.1 {181 execsql {182 DETACH db5;183 }184} {}185ifcapable schema_pragmas {186do_test attach-1.20.2 {187 db_list db188} {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11}189} ;# ifcapable schema_pragmas190integrity_check attach-1.20.3191ifcapable tempdb {192 execsql {select * from temp.sqlite_master}193}194do_test attach-1.21 {195 catchsql {196 ATTACH 'test.db' as db12;197 }198} {0 {}}199if {$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 errorcode207 } {1}208}209do_test attach-1.23 {210 catchsql {211 DETACH "db14";212 }213} {1 {no such database: db14}}214do_test attach-1.24 {215 catchsql {216 DETACH db12;217 }218} {0 {}}219do_test attach-1.25 {220 catchsql {221 DETACH db12;222 }223} {1 {no such database: db12}}224do_test attach-1.26 {225 catchsql {226 DETACH main;227 }228} {1 {cannot detach database main}}229 230 231ifcapable 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 245do_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 {}}258ifcapable schema_pragmas {259 ifcapable tempdb {260 do_test attach-1.29 {261 db_list db262 } {0 main 1 temp}263 } else {264 do_test attach-1.29 {265 db_list db266 } {0 main}267 }268} ;# ifcapable schema_pragmas269 270ifcapable {trigger} { # Only do the following tests if triggers are enabled271do_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 BEGIN275 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} {}280do_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}286do_test attach-2.3 {287 execsql {288 CREATE TABLE tx(x1,x2,y1,y2);289 SELECT * FROM tx;290 }291} {}292do_test attach-2.4 {293 execsql {294 ATTACH 'test2.db' AS db2;295 }296} {}297do_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}303do_test attach-2.6 {304 execsql {305 SELECT * FROM main.tx;306 }307} {}308do_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 314ifcapable schema_pragmas&&tempdb {315 do_test attach-2.8 {316 db_list db317 } {0 main 1 temp 2 db2}318} ;# ifcapable schema_pragmas&&tempdb319ifcapable schema_pragmas&&!tempdb {320 do_test attach-2.8 {321 db_list db322 } {0 main 2 db2}323} ;# ifcapable schema_pragmas&&!tempdb324 325do_test attach-2.9 {326 execsql {327 CREATE INDEX i2 ON t2(x);328 SELECT * FROM t2 WHERE x>5;329 } db2330} {21 x 22 y}331do_test attach-2.10 {332 execsql {333 SELECT type, name, tbl_name FROM sqlite_master;334 } db2335} {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}}341ifcapable schema_pragmas {342 ifcapable tempdb {343 do_test attach-2.12 {344 db_list db345 } {0 main 1 temp 2 db2}346 } else {347 do_test attach-2.12 {348 db_list db349 } {0 main 2 db2}350 }351} ;# ifcapable schema_pragmas352do_test attach-2.13 {353 catchsql {354 SELECT * FROM t2 WHERE x>5;355 }356} {0 {21 x 22 y}}357do_test attach-2.14 {358 execsql {359 SELECT type, name, tbl_name FROM sqlite_master;360 }361} {table t1 t1 table tx tx}362do_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}367do_test attach-2.16 {368 db close369 sqlite3 db test.db370 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 377do_test attach-3.1 {378 db close379 db2 close380 sqlite3 db test.db381 sqlite3 db2 test2.db382 execsql {383 SELECT * FROM t1384 }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 triggers389# had been enabled.390ifcapable {!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 404do_test attach-3.2 {405 catchsql {406 SELECT * FROM t2407 }408} {1 {no such table: t2}}409do_test attach-3.3 {410 catchsql {411 ATTACH DATABASE 'test2.db' AS db2;412 SELECT * FROM t2413 }414} {0 {21 x 22 y}}415 416# Even though 'db' has started a transaction, it should not yet have417# a lock on test2.db so 'db2' should be readable.418do_test attach-3.4 {419 execsql BEGIN420 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 not426# prevent test2.db from being read by db2.427do_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 get435# a reserved lock. It should still be accessible through db2.436do_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 445do_test attach-3.7 {446 execsql ROLLBACK447 execsql {SELECT * FROM t2} db2448} {21 x 22 y}449 450# Start transactions on both db and db2. Once again, just because451# we make a change to test2.db using db2, only a RESERVED lock is452# obtained, so test2.db should still be readable using db.453#454do_test attach-3.8 {455 execsql BEGIN456 execsql BEGIN db2457 execsql {UPDATE t2 SET x=0 WHERE 0} db2458 catchsql {SELECT * FROM t2}459} {0 {21 x 22 y}}460 461# It is also still accessible from db2.462do_test attach-3.9 {463 catchsql {SELECT * FROM t2} db2464} {0 {21 x 22 y}}465 466do_test attach-3.10 {467 execsql {SELECT * FROM t1}468} {1 2 3 4}469 470do_test attach-3.11 {471 catchsql {UPDATE t1 SET a=a+1}472} {0 {}}473do_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 tables478# in test2.db.479do_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 back484# for a locked database.485execsql {ROLLBACK}486 487# db is able to reread its schema because db2 still only holds a488# reserved lock.489do_test attach-3.14 {490 catchsql {SELECT * FROM t1}491} {0 {1 2 3 4}}492do_test attach-3.15 {493 execsql COMMIT db2494 execsql {SELECT * FROM t1}495} {1 2 3 4}496 497# Ticket #323498do_test attach-4.1 {499 execsql {DETACH db2}500 db2 close501 sqlite3 db2 test2.db502 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}509do_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}517do_test attach-4.3 {518 execsql {519 ATTACH DATABASE 'test2.db' AS db2;520 SELECT * FROM db2.t3;521 }522} {1 2}523do_test attach-4.4 {524 execsql {525 SELECT * FROM main.t3;526 }527} {9 10}528do_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}534execsql {535 DETACH db2;536}537ifcapable {trigger} {538 do_test attach-4.6 {539 execsql {540 CREATE TABLE t4(x);541 CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN542 INSERT INTO t4 VALUES('db2.' || NEW.x);543 END;544 INSERT INTO t3 VALUES(6,7);545 SELECT * FROM t4;546 } db2547 } {db2.6}548 do_test attach-4.7 {549 execsql {550 CREATE TABLE t4(y);551 CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN552 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}559ifcapable {!trigger} {560 # When we do not have trigger support, set up the table like they561 # would have been had triggers been there. The tests that follow need562 # 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 } db2569 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 sure578# the schema for both main and db2 is valid before starting to execute579# the first query of the UNION ALL. If we wait to test the validity of580# the schema for main until after the first query has run, that test will581# fail and the query will abort but we will have already output some582# results. When the query is retried, the results will be repeated.583#584ifcapable compound {585do_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 593do_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 compound601 602ifcapable !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 !compound610 611ifcapable view {612do_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 } db2620} {102 910 607 1314}621do_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}627do_test attach-4.12 {628 execsql {629 ATTACH DATABASE 'test2.db' AS db2;630 SELECT * FROM db2.v3;631 }632} {102 910 607 1314}633do_test attach-4.13 {634 execsql {635 SELECT * FROM main.v3;636 }637} {910 1112 1516}638} ;# ifcapable view639 640# Tests for the sqliteFix...() routines in attach.c641#642ifcapable {trigger} {643do_test attach-5.1 {644 db close645 sqlite3 db test.db646 db2 close647 forcedelete test2.db648 sqlite3 db2 test2.db649 catchsql {650 ATTACH DATABASE 'test.db' AS orig;651 CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN652 SELECT 'no-op';653 END;654 } db2655} {1 {trigger r1 cannot reference objects in database orig}}656do_test attach-5.2 {657 catchsql {658 CREATE TABLE t5(x,y);659 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN660 SELECT 'no-op';661 END;662 } db2663} {0 {}}664do_test attach-5.3 {665 catchsql {666 DROP TRIGGER r5;667 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN668 SELECT 'no-op' FROM orig.t1;669 END;670 } db2671} {1 {trigger r5 cannot reference objects in database orig}}672ifcapable 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 BEGIN677 SELECT 'no-op' FROM temp.t6;678 END;679 } db2680 } {1 {trigger r5 cannot reference objects in database temp}}681}682ifcapable subquery {683 do_test attach-5.5 {684 catchsql {685 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN686 SELECT 'no-op' || (SELECT * FROM temp.t6);687 END;688 } db2689 } {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 BEGIN693 SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);694 END;695 } db2696 } {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 BEGIN700 SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);701 END;702 } db2703 } {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 BEGIN707 SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;708 END;709 } db2710 } {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 BEGIN714 INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);715 END;716 } db2717 } {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 BEGIN721 DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);722 END;723 } db2724 } {1 {trigger r5 cannot reference objects in database temp}}725} ;# endif subquery726ifcapable json1&&vtab {727 do_test attach-5.10 {728 db close729 catch {db2 close}730 forcedelete test.db731 sqlite3 db test.db732 db eval {733 CREATE TABLE t1(x);734 CREATE TABLE t2(a,b);735 CREATE TRIGGER x1 AFTER INSERT ON t1 BEGIN736 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 json1752} ;# endif trigger753 754# Check to make sure we get a sensible error if unable to open755# the file that we are trying to attach.756#757do_test attach-6.1 {758 catchsql {759 ATTACH DATABASE 'no-such-file' AS nosuch;760 }761} {0 {}}762if {$tcl_platform(platform) eq "unix"} {763 do_test attach-6.2 {764 sqlite3 dbx cannot-read765 dbx eval {CREATE TABLE t1(a,b,c)}766 dbx close767 file attributes cannot-read -permission 0000768 if {[file writable cannot-read]} {769 puts "\n**** Tests do not work when run as root ****"770 forcedelete cannot-read771 exit 1772 }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 errorcode779 } {14}780 forcedelete cannot-read781}782 783# Check the error message if we try to access a database that has784# not been attached.785do_test attach-6.3 {786 catchsql {787 CREATE TABLE no_such_db.t1(a, b, c);788 }789} {1 {unknown database no_such_db}}790for {set i 2} {$i<=15} {incr i} {791 catch {db$i close}792}793db close794forcedelete test2.db795forcedelete no-such-file796 797ifcapable subquery {798 do_test attach-7.1 {799 forcedelete test.db test.db-journal800 sqlite3 db test.db801 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 it810#811do_test attach-8.1 {812 set fd [open test2.db w]813 puts $fd "This file is not a valid SQLite database"814 close $fd815 catchsql {816 ATTACH 'test2.db' AS t2;817 }818} {1 {file is not a database}}819do_test attach-8.2 {820 db errorcode821} {26}822forcedelete test2.db823do_test attach-8.3 {824 sqlite3 db2 test2.db825 db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE}826 catchsql {827 ATTACH 'test2.db' AS t2;828 }829} {1 {database is locked}}830do_test attach-8.4 {831 db errorcode832} {5}833db2 close834forcedelete test2.db835 836# Test that it is possible to attach the same database more than837# once when not in shared-cache mode. That this is not possible in838# shared-cache mode is tested in shared7.test.839do_test attach-9.1 {840 forcedelete test4.db841 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}849do_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}}856do_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#866do_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}879do_test attach-10.2 {880 lrange [execsql {881 PRAGMA database_list;882 }] 9 end883} {4 noname {} 5 inmem {}}884 885# Attach with a very long URI filename.886#887db close888sqlite3 db test.db -uri 1889do_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-15897# False positive when running integrity_check on a connection with898# attached databases. 899#900db close901sqlite3 db :memory:902do_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/a006d86f72914#915reset_db916do_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 928finish_test929