Debian Codegraph @

test/attach.test

raw
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) eq "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