1#
2# 2003 April 4
3#
4# The author disclaims copyright to this source code.  In place of
5# a legal notice, here is a blessing:
6#
7#    May you do good and not evil.
8#    May you find forgiveness for yourself and forgive others.
9#    May you share freely, never taking more than you give.
10#
11#***********************************************************************
12# This file implements regression tests for SQLite library.  The
13# focus of this script is testing the ATTACH and DETACH commands
14# and related functionality.
15#
16# $Id: attach.test,v 1.13 2004/02/14 01:39:50 drh Exp $
17#
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22for {set i 2} {$i<=15} {incr i} {
23  file delete -force test$i.db
24  file delete -force test$i.db-journal
25}
26
27do_test attach-1.1 {
28  execsql {
29    CREATE TABLE t1(a,b);
30    INSERT INTO t1 VALUES(1,2);
31    INSERT INTO t1 VALUES(3,4);
32    SELECT * FROM t1;
33  }
34} {1 2 3 4}
35do_test attach-1.2 {
36  sqlite db2 test2.db
37  execsql {
38    CREATE TABLE t2(x,y);
39    INSERT INTO t2 VALUES(1,'x');
40    INSERT INTO t2 VALUES(2,'y');
41    SELECT * FROM t2;
42  } db2
43} {1 x 2 y}
44do_test attach-1.3 {
45  execsql {
46    ATTACH DATABASE 'test2.db' AS two;
47    SELECT * FROM two.t2;
48  }
49} {1 x 2 y}
50do_test attach-1.4 {
51  execsql {
52    SELECT * FROM t2;
53  }
54} {1 x 2 y}
55do_test attach-1.5 {
56  execsql {
57    DETACH DATABASE two;
58    SELECT * FROM t1;
59  }
60} {1 2 3 4}
61do_test attach-1.6 {
62  catchsql {
63    SELECT * FROM t2;
64  }
65} {1 {no such table: t2}}
66do_test attach-1.7 {
67  catchsql {
68    SELECT * FROM two.t2;
69  }
70} {1 {no such table: two.t2}}
71do_test attach-1.8 {
72  catchsql {
73    ATTACH DATABASE 'test3.db' AS three;
74  }
75} {1 {cannot attach empty database: three}}
76do_test attach-1.9 {
77  catchsql {
78    SELECT * FROM three.sqlite_master;
79  }
80} {1 {no such table: three.sqlite_master}}
81do_test attach-1.10 {
82  catchsql {
83    DETACH DATABASE three;
84  }
85} {1 {no such database: three}}
86do_test attach-1.11 {
87  execsql {
88    ATTACH 'test.db' AS db2;
89    ATTACH 'test.db' AS db3;
90    ATTACH 'test.db' AS db4;
91    ATTACH 'test.db' AS db5;
92    ATTACH 'test.db' AS db6;
93    ATTACH 'test.db' AS db7;
94    ATTACH 'test.db' AS db8;
95    ATTACH 'test.db' AS db9;
96  }
97} {}
98proc db_list {db} {
99  set list {}
100  foreach {idx name file} [execsql {PRAGMA database_list} $db] {
101    lappend list $idx $name
102  }
103  return $list
104}
105do_test attach-1.11b {
106  db_list db
107} {0 main 1 temp 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}
108do_test attach-1.12 {
109  catchsql {
110    ATTACH 'test.db' as db2;
111  }
112} {1 {database db2 is already in use}}
113do_test attach-1.13 {
114  catchsql {
115    ATTACH 'test.db' as db5;
116  }
117} {1 {database db5 is already in use}}
118do_test attach-1.14 {
119  catchsql {
120    ATTACH 'test.db' as db9;
121  }
122} {1 {database db9 is already in use}}
123do_test attach-1.15 {
124  catchsql {
125    ATTACH 'test.db' as main;
126  }
127} {1 {database main is already in use}}
128do_test attach-1.16 {
129  catchsql {
130    ATTACH 'test.db' as temp;
131  }
132} {1 {database temp is already in use}}
133do_test attach-1.17 {
134  catchsql {
135    ATTACH 'test.db' as MAIN;
136  }
137} {1 {database MAIN is already in use}}
138do_test attach-1.18 {
139  catchsql {
140    ATTACH 'test.db' as db10;
141    ATTACH 'test.db' as db11;
142  }
143} {0 {}}
144do_test attach-1.19 {
145  catchsql {
146    ATTACH 'test.db' as db12;
147  }
148} {1 {too many attached databases - max 10}}
149do_test attach-1.20.1 {
150  execsql {
151    DETACH db5;
152  }
153  db_list db
154} {0 main 1 temp 2 db2 3 db3 4 db4 5 db11 6 db6 7 db7 8 db8 9 db9 10 db10}
155integrity_check attach-1.20.2
156do_test attach-1.21 {
157  catchsql {
158    ATTACH 'test.db' as db12;
159  }
160} {0 {}}
161do_test attach-1.22 {
162  catchsql {
163    ATTACH 'test.db' as db13;
164  }
165} {1 {too many attached databases - max 10}}
166do_test attach-1.23 {
167  catchsql {
168    DETACH db14;
169  }
170} {1 {no such database: db14}}
171do_test attach-1.24 {
172  catchsql {
173    DETACH db12;
174  }
175} {0 {}}
176do_test attach-1.25 {
177  catchsql {
178    DETACH db12;
179  }
180} {1 {no such database: db12}}
181do_test attach-1.26 {
182  catchsql {
183    DETACH main;
184  }
185} {1 {cannot detach database main}}
186do_test attach-1.27 {
187  catchsql {
188    DETACH Temp;
189  }
190} {1 {cannot detach database Temp}}
191do_test attach-1.28 {
192  catchsql {
193    DETACH db11;
194    DETACH db10;
195    DETACH db9;
196    DETACH db8;
197    DETACH db7;
198    DETACH db6;
199    DETACH db4;
200    DETACH db3;
201    DETACH db2;
202  }
203} {0 {}}
204do_test attach-1.29 {
205  db_list db
206} {0 main 1 temp}
207
208do_test attach-2.1 {
209  execsql {
210    CREATE TABLE tx(x1,x2,y1,y2);
211    CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
212      INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y);
213    END;
214    SELECT * FROM tx;
215  } db2;
216} {}
217do_test attach-2.2 {
218  execsql {
219    UPDATE t2 SET x=x+10;
220    SELECT * FROM tx;
221  } db2;
222} {1 11 x x 2 12 y y}
223do_test attach-2.3 {
224  execsql {
225    CREATE TABLE tx(x1,x2,y1,y2);
226    SELECT * FROM tx;
227  }
228} {}
229do_test attach-2.4 {
230  execsql {
231    ATTACH 'test2.db' AS db2;
232  }
233} {}
234do_test attach-2.5 {
235  execsql {
236    UPDATE db2.t2 SET x=x+10;
237    SELECT * FROM db2.tx;
238  }
239} {1 11 x x 2 12 y y 11 21 x x 12 22 y y}
240do_test attach-2.6 {
241  execsql {
242    SELECT * FROM main.tx;
243  }
244} {}
245do_test attach-2.7 {
246  execsql {
247    SELECT type, name, tbl_name FROM db2.sqlite_master;
248  }
249} {table t2 t2 table tx tx trigger r1 t2}
250do_test attach-2.8 {
251  db_list db
252} {0 main 1 temp 2 db2}
253do_test attach-2.9 {
254  execsql {
255    CREATE INDEX i2 ON t2(x);
256    SELECT * FROM t2 WHERE x>5;
257  } db2
258} {21 x 22 y}
259do_test attach-2.10 {
260  execsql {
261    SELECT type, name, tbl_name FROM sqlite_master;
262  } db2
263} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
264#do_test attach-2.11 {
265#  catchsql {
266#    SELECT * FROM t2 WHERE x>5;
267#  }
268#} {1 {database schema has changed}}
269do_test attach-2.12 {
270  db_list db
271} {0 main 1 temp 2 db2}
272do_test attach-2.13 {
273  catchsql {
274    SELECT * FROM t2 WHERE x>5;
275  }
276} {0 {21 x 22 y}}
277do_test attach-2.14 {
278  execsql {
279    SELECT type, name, tbl_name FROM sqlite_master;
280  }
281} {table t1 t1 table tx tx}
282do_test attach-2.15 {
283  execsql {
284    SELECT type, name, tbl_name FROM db2.sqlite_master;
285  }
286} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
287do_test attach-2.16 {
288  db close
289  sqlite db test.db
290  execsql {
291    ATTACH 'test2.db' AS db2;
292    SELECT type, name, tbl_name FROM db2.sqlite_master;
293  }
294} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
295
296do_test attach-3.1 {
297  db close
298  db2 close
299  sqlite db test.db
300  sqlite db2 test2.db
301  execsql {
302    SELECT * FROM t1
303  }
304} {1 2 3 4}
305do_test attach-3.2 {
306  catchsql {
307    SELECT * FROM t2
308  }
309} {1 {no such table: t2}}
310do_test attach-3.3 {
311  catchsql {
312    ATTACH DATABASE 'test2.db' AS db2;
313    SELECT * FROM t2
314  }
315} {0 {21 x 22 y}}
316
317# Even though main has a transaction, test2.db should not be locked.
318do_test attach-3.4 {
319  execsql BEGIN
320  catchsql {
321    SELECT * FROM t2;
322  } db2;
323} {0 {21 x 22 y}}
324
325# Reading from db2 should not lock test2.db
326do_test attach-3.5 {
327  execsql {SELECT * FROM t2}
328  catchsql {
329    SELECT * FROM t2;
330  } db2;
331} {0 {21 x 22 y}}
332
333# Making a change to db2 causes test2.ddb to become locked.
334do_test attach-3.6 {
335  execsql {
336    UPDATE t2 SET x=x+1 WHERE x=50;
337  }
338  catchsql {
339    SELECT * FROM t2;
340  } db2;
341} {1 {database is locked}}
342
343do_test attach-3.7 {
344  execsql ROLLBACK
345  execsql {SELECT * FROM t2} db2
346} {21 x 22 y}
347do_test attach-3.8 {
348  execsql BEGIN
349  execsql BEGIN db2
350  catchsql {SELECT * FROM t2}
351} {1 {database is locked}}
352do_test attach-3.9 {
353  catchsql {SELECT * FROM t2} db2
354} {0 {21 x 22 y}}
355do_test attach-3.10 {
356  execsql {SELECT * FROM t1}
357} {1 2 3 4}
358do_test attach-3.11 {
359  catchsql {UPDATE t1 SET a=a+1}
360} {0 {}}
361do_test attach-3.12 {
362  execsql {SELECT * FROM t1}
363} {2 2 4 4}
364do_test attach-3.13 {
365  catchsql {UPDATE t2 SET x=x+1 WHERE x=50}
366} {1 {database is locked}}
367do_test attach-3.14 {
368  # Unable to reinitialize the schema tables because the aux database
369  # is still locked.
370  catchsql {SELECT * FROM t1}
371} {1 {database is locked}}
372do_test attach-3.15 {
373  execsql COMMIT db2
374  execsql {SELECT * FROM t1}
375} {1 2 3 4}
376
377# Ticket #323
378do_test attach-4.1 {
379  execsql {DETACH db2}
380  db2 close
381  sqlite db2 test2.db
382  execsql {
383    CREATE TABLE t3(x,y);
384    CREATE UNIQUE INDEX t3i1 ON t3(x);
385    INSERT INTO t3 VALUES(1,2);
386    SELECT * FROM t3;
387  } db2;
388} {1 2}
389do_test attach-4.2 {
390  execsql {
391    CREATE TABLE t3(a,b);
392    CREATE UNIQUE INDEX t3i1b ON t3(a);
393    INSERT INTO t3 VALUES(9,10);
394    SELECT * FROM t3;
395  }
396} {9 10}
397do_test attach-4.3 {
398  execsql {
399    ATTACH DATABASE 'test2.db' AS db2;
400    SELECT * FROM db2.t3;
401  }
402} {1 2}
403do_test attach-4.4 {
404  execsql {
405    SELECT * FROM main.t3;
406  }
407} {9 10}
408do_test attach-4.5 {
409  execsql {
410    INSERT INTO db2.t3 VALUES(9,10);
411    SELECT * FROM db2.t3;
412  }
413} {1 2 9 10}
414do_test attach-4.6 {
415  execsql {
416    DETACH db2;
417  }
418  execsql {
419    CREATE TABLE t4(x);
420    CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
421      INSERT INTO t4 VALUES('db2.' || NEW.x);
422    END;
423    INSERT INTO t3 VALUES(6,7);
424    SELECT * FROM t4;
425  } db2
426} {db2.6}
427do_test attach-4.7 {
428  execsql {
429    CREATE TABLE t4(y);
430    CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
431      INSERT INTO t4 VALUES('main.' || NEW.a);
432    END;
433    INSERT INTO main.t3 VALUES(11,12);
434    SELECT * FROM main.t4;
435  }
436} {main.11}
437do_test attach-4.8 {
438  execsql {
439    ATTACH DATABASE 'test2.db' AS db2;
440    INSERT INTO db2.t3 VALUES(13,14);
441    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
442  }
443} {db2.6 db2.13 main.11}
444do_test attach-4.9 {
445  execsql {
446    INSERT INTO main.t3 VALUES(15,16);
447    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
448  }
449} {db2.6 db2.13 main.11 main.15}
450do_test attach-4.10 {
451  execsql {
452    DETACH DATABASE db2;
453  }
454  execsql {
455    CREATE VIEW v3 AS SELECT x*100+y FROM t3;
456    SELECT * FROM v3;
457  } db2
458} {102 910 607 1314}
459do_test attach-4.11 {
460  execsql {
461    CREATE VIEW v3 AS SELECT a*100+b FROM t3;
462    SELECT * FROM v3;
463  }
464} {910 1112 1516}
465do_test attach-4.12 {
466  execsql {
467    ATTACH DATABASE 'test2.db' AS db2;
468    SELECT * FROM db2.v3;
469  }
470} {102 910 607 1314}
471do_test attach-4.13 {
472  execsql {
473    SELECT * FROM main.v3;
474  }
475} {910 1112 1516}
476
477# Tests for the sqliteFix...() routines in attach.c
478#
479do_test attach-5.1 {
480  db close
481  sqlite db test.db
482  db2 close
483  file delete -force test2.db
484  sqlite db2 test2.db
485  catchsql {
486    ATTACH DATABASE 'test.db' AS orig;
487    CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN;
488      SELECT 'no-op';
489    END;
490  } db2
491} {1 {triggers may not be added to auxiliary database orig}}
492do_test attach-5.2 {
493  catchsql {
494    CREATE TABLE t5(x,y);
495    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
496      SELECT 'no-op';
497    END;
498  } db2
499} {0 {}}
500do_test attach-5.3 {
501  catchsql {
502    DROP TRIGGER r5;
503    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
504      SELECT 'no-op' FROM orig.t1;
505    END;
506  } db2
507} {1 {trigger r5 cannot reference objects in database orig}}
508do_test attach-5.4 {
509  catchsql {
510    CREATE TEMP TABLE t6(p,q,r);
511    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
512      SELECT 'no-op' FROM temp.t6;
513    END;
514  } db2
515} {1 {trigger r5 cannot reference objects in database temp}}
516do_test attach-5.5 {
517  catchsql {
518    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
519      SELECT 'no-op' || (SELECT * FROM temp.t6);
520    END;
521  } db2
522} {1 {trigger r5 cannot reference objects in database temp}}
523do_test attach-5.6 {
524  catchsql {
525    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
526      SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
527    END;
528  } db2
529} {1 {trigger r5 cannot reference objects in database temp}}
530do_test attach-5.7 {
531  catchsql {
532    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
533      SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);
534    END;
535  } db2
536} {1 {trigger r5 cannot reference objects in database temp}}
537do_test attach-5.7 {
538  catchsql {
539    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
540      SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;
541    END;
542  } db2
543} {1 {trigger r5 cannot reference objects in database temp}}
544do_test attach-5.8 {
545  catchsql {
546    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
547      INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);
548    END;
549  } db2
550} {1 {trigger r5 cannot reference objects in database temp}}
551do_test attach-5.9 {
552  catchsql {
553    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
554      DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
555    END;
556  } db2
557} {1 {trigger r5 cannot reference objects in database temp}}
558
559# Check to make sure we get a sensible error if unable to open
560# the file that we are trying to attach.
561#
562do_test attach-6.1 {
563  catchsql {
564    ATTACH DATABASE 'no-such-file' AS nosuch;
565  }
566} {1 {cannot attach empty database: nosuch}}
567file delete -force no-such-file
568if {$tcl_platform(platform)=="unix"} {
569  do_test attach-6.2 {
570    sqlite dbx cannot-read
571    dbx eval {CREATE TABLE t1(a,b,c)}
572    dbx close
573    file attributes cannot-read -permission 0000
574    catchsql {
575      ATTACH DATABASE 'cannot-read' AS noread;
576    }
577  } {1 {unable to open database: cannot-read}}
578  file delete -force cannot-read
579}
580
581for {set i 2} {$i<=15} {incr i} {
582  catch {db$i close}
583}
584file delete -force test2.db
585
586
587finish_test
588