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