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