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.49 2008/07/12 14:52:20 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   file delete -force test$i.db

    28   file delete -force 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 do_test attach-1.4 {

    55   execsql {

    56     SELECT * FROM t2;

    57   }

    58 } {1 x 2 y}

    59 do_test attach-1.5 {

    60   execsql {

    61     DETACH DATABASE two;

    62     SELECT * FROM t1;

    63   }

    64 } {1 2 3 4}

    65 do_test attach-1.6 {

    66   catchsql {

    67     SELECT * FROM t2;

    68   }

    69 } {1 {no such table: t2}}

    70 do_test attach-1.7 {

    71   catchsql {

    72     SELECT * FROM two.t2;

    73   }

    74 } {1 {no such table: two.t2}}

    75 do_test attach-1.8 {

    76   catchsql {

    77     ATTACH DATABASE 'test3.db' AS three;

    78   }

    79 } {0 {}}

    80 do_test attach-1.9 {

    81   catchsql {

    82     SELECT * FROM three.sqlite_master;

    83   }

    84 } {0 {}}

    85 do_test attach-1.10 {

    86   catchsql {

    87     DETACH DATABASE [three];

    88   }

    89 } {0 {}}

    90 do_test attach-1.11 {

    91   execsql {

    92     ATTACH 'test.db' AS db2;

    93     ATTACH 'test.db' AS db3;

    94     ATTACH 'test.db' AS db4;

    95     ATTACH 'test.db' AS db5;

    96     ATTACH 'test.db' AS db6;

    97     ATTACH 'test.db' AS db7;

    98     ATTACH 'test.db' AS db8;

    99     ATTACH 'test.db' AS db9;

   100   }

   101 } {}

   102 proc db_list {db} {

   103   set list {}

   104   foreach {idx name file} [execsql {PRAGMA database_list} $db] {

   105     lappend list $idx $name

   106   }

   107   return $list

   108 }

   109 ifcapable schema_pragmas {

   110 do_test attach-1.11b {

   111   db_list db

   112 } {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}

   113 } ;# ifcapable schema_pragmas 

   114 do_test attach-1.12 {

   115   catchsql {

   116     ATTACH 'test.db' as db2;

   117   }

   118 } {1 {database db2 is already in use}}

   119 do_test attach-1.12.2 {

   120   db errorcode

   121 } {1}

   122 do_test attach-1.13 {

   123   catchsql {

   124     ATTACH 'test.db' as db5;

   125   }

   126 } {1 {database db5 is already in use}}

   127 do_test attach-1.14 {

   128   catchsql {

   129     ATTACH 'test.db' as db9;

   130   }

   131 } {1 {database db9 is already in use}}

   132 do_test attach-1.15 {

   133   catchsql {

   134     ATTACH 'test.db' as main;

   135   }

   136 } {1 {database main is already in use}}

   137 ifcapable tempdb {

   138   do_test attach-1.16 {

   139     catchsql {

   140       ATTACH 'test.db' as temp;

   141     }

   142   } {1 {database temp is already in use}}

   143 }

   144 do_test attach-1.17 {

   145   catchsql {

   146     ATTACH 'test.db' as MAIN;

   147   }

   148 } {1 {database MAIN is already in use}}

   149 do_test attach-1.18 {

   150   catchsql {

   151     ATTACH 'test.db' as db10;

   152     ATTACH 'test.db' as db11;

   153   }

   154 } {0 {}}

   155 do_test attach-1.19 {

   156   catchsql {

   157     ATTACH 'test.db' as db12;

   158   }

   159 } {1 {too many attached databases - max 10}}

   160 do_test attach-1.19.1 {

   161   db errorcode

   162 } {1}

   163 do_test attach-1.20.1 {

   164   execsql {

   165     DETACH db5;

   166   }

   167 } {}

   168 ifcapable schema_pragmas {

   169 do_test attach-1.20.2 {

   170   db_list db

   171 } {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11}

   172 } ;# ifcapable schema_pragmas

   173 integrity_check attach-1.20.3

   174 ifcapable tempdb {

   175   execsql {select * from sqlite_temp_master}

   176 }

   177 do_test attach-1.21 {

   178   catchsql {

   179     ATTACH 'test.db' as db12;

   180   }

   181 } {0 {}}

   182 do_test attach-1.22 {

   183   catchsql {

   184     ATTACH 'test.db' as db13;

   185   }

   186 } {1 {too many attached databases - max 10}}

   187 do_test attach-1.22.1 {

   188   db errorcode

   189 } {1}

   190 do_test attach-1.23 {

   191   catchsql {

   192     DETACH "db14";

   193   }

   194 } {1 {no such database: db14}}

   195 do_test attach-1.24 {

   196   catchsql {

   197     DETACH db12;

   198   }

   199 } {0 {}}

   200 do_test attach-1.25 {

   201   catchsql {

   202     DETACH db12;

   203   }

   204 } {1 {no such database: db12}}

   205 do_test attach-1.26 {

   206   catchsql {

   207     DETACH main;

   208   }

   209 } {1 {cannot detach database main}}

   210 

   211 ifcapable tempdb {

   212   do_test attach-1.27 {

   213     catchsql {

   214       DETACH Temp;

   215     }

   216   } {1 {cannot detach database Temp}}

   217 } else {

   218   do_test attach-1.27 {

   219     catchsql {

   220       DETACH Temp;

   221     }

   222   } {1 {no such database: Temp}}

   223 }

   224 

   225 do_test attach-1.28 {

   226   catchsql {

   227     DETACH db11;

   228     DETACH db10;

   229     DETACH db9;

   230     DETACH db8;

   231     DETACH db7;

   232     DETACH db6;

   233     DETACH db4;

   234     DETACH db3;

   235     DETACH db2;

   236   }

   237 } {0 {}}

   238 ifcapable schema_pragmas {

   239   ifcapable tempdb {

   240     do_test attach-1.29 {

   241       db_list db

   242     } {0 main 1 temp}

   243   } else {

   244     do_test attach-1.29 {

   245       db_list db

   246     } {0 main}

   247   }

   248 } ;# ifcapable schema_pragmas

   249 

   250 ifcapable {trigger} {  # Only do the following tests if triggers are enabled

   251 do_test attach-2.1 {

   252   execsql {

   253     CREATE TABLE tx(x1,x2,y1,y2);

   254     CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN

   255       INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y);

   256     END;

   257     SELECT * FROM tx;

   258   } db2;

   259 } {}

   260 do_test attach-2.2 {

   261   execsql {

   262     UPDATE t2 SET x=x+10;

   263     SELECT * FROM tx;

   264   } db2;

   265 } {1 11 x x 2 12 y y}

   266 do_test attach-2.3 {

   267   execsql {

   268     CREATE TABLE tx(x1,x2,y1,y2);

   269     SELECT * FROM tx;

   270   }

   271 } {}

   272 do_test attach-2.4 {

   273   execsql {

   274     ATTACH 'test2.db' AS db2;

   275   }

   276 } {}

   277 do_test attach-2.5 {

   278   execsql {

   279     UPDATE db2.t2 SET x=x+10;

   280     SELECT * FROM db2.tx;

   281   }

   282 } {1 11 x x 2 12 y y 11 21 x x 12 22 y y}

   283 do_test attach-2.6 {

   284   execsql {

   285     SELECT * FROM main.tx;

   286   }

   287 } {}

   288 do_test attach-2.7 {

   289   execsql {

   290     SELECT type, name, tbl_name FROM db2.sqlite_master;

   291   }

   292 } {table t2 t2 table tx tx trigger r1 t2}

   293 

   294 ifcapable schema_pragmas&&tempdb {

   295   do_test attach-2.8 {

   296     db_list db

   297   } {0 main 1 temp 2 db2}

   298 } ;# ifcapable schema_pragmas&&tempdb

   299 ifcapable schema_pragmas&&!tempdb {

   300   do_test attach-2.8 {

   301     db_list db

   302   } {0 main 2 db2}

   303 } ;# ifcapable schema_pragmas&&!tempdb

   304 

   305 do_test attach-2.9 {

   306   execsql {

   307     CREATE INDEX i2 ON t2(x);

   308     SELECT * FROM t2 WHERE x>5;

   309   } db2

   310 } {21 x 22 y}

   311 do_test attach-2.10 {

   312   execsql {

   313     SELECT type, name, tbl_name FROM sqlite_master;

   314   } db2

   315 } {table t2 t2 table tx tx trigger r1 t2 index i2 t2}

   316 #do_test attach-2.11 {

   317 #  catchsql { 

   318 #    SELECT * FROM t2 WHERE x>5;

   319 #  }

   320 #} {1 {database schema has changed}}

   321 ifcapable schema_pragmas {

   322   ifcapable tempdb {

   323     do_test attach-2.12 {

   324       db_list db

   325     } {0 main 1 temp 2 db2}

   326   } else {

   327     do_test attach-2.12 {

   328       db_list db

   329     } {0 main 2 db2}

   330   }

   331 } ;# ifcapable schema_pragmas

   332 do_test attach-2.13 {

   333   catchsql {

   334     SELECT * FROM t2 WHERE x>5;

   335   }

   336 } {0 {21 x 22 y}}

   337 do_test attach-2.14 {

   338   execsql {

   339     SELECT type, name, tbl_name FROM sqlite_master;

   340   }

   341 } {table t1 t1 table tx tx}

   342 do_test attach-2.15 {

   343   execsql {

   344     SELECT type, name, tbl_name FROM db2.sqlite_master;

   345   }

   346 } {table t2 t2 table tx tx trigger r1 t2 index i2 t2}

   347 do_test attach-2.16 {

   348   db close

   349   sqlite3 db test.db

   350   execsql {

   351     ATTACH 'test2.db' AS db2;

   352     SELECT type, name, tbl_name FROM db2.sqlite_master;

   353   }

   354 } {table t2 t2 table tx tx trigger r1 t2 index i2 t2}

   355 } ;# End of ifcapable {trigger}

   356 

   357 do_test attach-3.1 {

   358   db close

   359   db2 close

   360   sqlite3 db test.db

   361   sqlite3 db2 test2.db

   362   execsql {

   363     SELECT * FROM t1

   364   }

   365 } {1 2 3 4}

   366 

   367 # If we are testing a version of the code that lacks trigger support,

   368 # adjust the database contents so that they are the same if triggers

   369 # had been enabled.

   370 ifcapable {!trigger} {

   371   db2 eval {

   372     DELETE FROM t2;

   373     INSERT INTO t2 VALUES(21, 'x');

   374     INSERT INTO t2 VALUES(22, 'y');

   375     CREATE TABLE tx(x1,x2,y1,y2);

   376     INSERT INTO tx VALUES(1, 11, 'x', 'x');

   377     INSERT INTO tx VALUES(2, 12, 'y', 'y');

   378     INSERT INTO tx VALUES(11, 21, 'x', 'x');

   379     INSERT INTO tx VALUES(12, 22, 'y', 'y');

   380     CREATE INDEX i2 ON t2(x);

   381   }

   382 }

   383 

   384 do_test attach-3.2 {

   385   catchsql {

   386     SELECT * FROM t2

   387   }

   388 } {1 {no such table: t2}}

   389 do_test attach-3.3 {

   390   catchsql {

   391     ATTACH DATABASE 'test2.db' AS db2;

   392     SELECT * FROM t2

   393   }

   394 } {0 {21 x 22 y}}

   395 

   396 # Even though 'db' has started a transaction, it should not yet have

   397 # a lock on test2.db so 'db2' should be readable.

   398 do_test attach-3.4 {

   399   execsql BEGIN

   400   catchsql {

   401     SELECT * FROM t2;

   402   } db2;

   403 } {0 {21 x 22 y}}

   404 

   405 # Reading from test2.db from db within a transaction should not

   406 # prevent test2.db from being read by db2.

   407 do_test attach-3.5 {

   408   execsql {SELECT * FROM t2}

   409   catchsql {

   410     SELECT * FROM t2;

   411   } db2;

   412 } {0 {21 x 22 y}}

   413 

   414 # Making a change to test2.db through db  causes test2.db to get

   415 # a reserved lock.  It should still be accessible through db2.

   416 do_test attach-3.6 {

   417   execsql {

   418     UPDATE t2 SET x=x+1 WHERE x=50;

   419   }

   420   catchsql {

   421     SELECT * FROM t2;

   422   } db2;

   423 } {0 {21 x 22 y}}

   424 

   425 do_test attach-3.7 {

   426   execsql ROLLBACK

   427   execsql {SELECT * FROM t2} db2

   428 } {21 x 22 y}

   429 

   430 # Start transactions on both db and db2.  Once again, just because

   431 # we make a change to test2.db using db2, only a RESERVED lock is

   432 # obtained, so test2.db should still be readable using db.

   433 #

   434 do_test attach-3.8 {

   435   execsql BEGIN

   436   execsql BEGIN db2

   437   execsql {UPDATE t2 SET x=0 WHERE 0} db2

   438   catchsql {SELECT * FROM t2}

   439 } {0 {21 x 22 y}}

   440 

   441 # It is also still accessible from db2.

   442 do_test attach-3.9 {

   443   catchsql {SELECT * FROM t2} db2

   444 } {0 {21 x 22 y}}

   445 

   446 do_test attach-3.10 {

   447   execsql {SELECT * FROM t1}

   448 } {1 2 3 4}

   449 

   450 do_test attach-3.11 {

   451   catchsql {UPDATE t1 SET a=a+1}

   452 } {0 {}}

   453 do_test attach-3.12 {

   454   execsql {SELECT * FROM t1}

   455 } {2 2 4 4}

   456 

   457 # db2 has a RESERVED lock on test2.db, so db cannot write to any tables

   458 # in test2.db.

   459 do_test attach-3.13 {

   460   catchsql {UPDATE t2 SET x=x+1 WHERE x=50}

   461 } {1 {database is locked}}

   462 

   463 # Change for version 3. Transaction is no longer rolled back

   464 # for a locked database.

   465 execsql {ROLLBACK}

   466 

   467 # db is able to reread its schema because db2 still only holds a

   468 # reserved lock.

   469 do_test attach-3.14 {

   470   catchsql {SELECT * FROM t1}

   471 } {0 {1 2 3 4}}

   472 do_test attach-3.15 {

   473   execsql COMMIT db2

   474   execsql {SELECT * FROM t1}

   475 } {1 2 3 4}

   476 

   477 # Ticket #323

   478 do_test attach-4.1 {

   479   execsql {DETACH db2}

   480   db2 close

   481   sqlite3 db2 test2.db

   482   execsql {

   483     CREATE TABLE t3(x,y);

   484     CREATE UNIQUE INDEX t3i1 ON t3(x);

   485     INSERT INTO t3 VALUES(1,2);

   486     SELECT * FROM t3;

   487   } db2;

   488 } {1 2}

   489 do_test attach-4.2 {

   490   execsql {

   491     CREATE TABLE t3(a,b);

   492     CREATE UNIQUE INDEX t3i1b ON t3(a);

   493     INSERT INTO t3 VALUES(9,10);

   494     SELECT * FROM t3;

   495   }

   496 } {9 10}

   497 do_test attach-4.3 {

   498   execsql {

   499     ATTACH DATABASE 'test2.db' AS db2;

   500     SELECT * FROM db2.t3;

   501   }

   502 } {1 2}

   503 do_test attach-4.4 {

   504   execsql {

   505     SELECT * FROM main.t3;

   506   }

   507 } {9 10}

   508 do_test attach-4.5 {

   509   execsql {

   510     INSERT INTO db2.t3 VALUES(9,10);

   511     SELECT * FROM db2.t3;

   512   }

   513 } {1 2 9 10}

   514 execsql {

   515   DETACH db2;

   516 }

   517 ifcapable {trigger} {

   518   do_test attach-4.6 {

   519     execsql {

   520       CREATE TABLE t4(x);

   521       CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN

   522         INSERT INTO t4 VALUES('db2.' || NEW.x);

   523       END;

   524       INSERT INTO t3 VALUES(6,7);

   525       SELECT * FROM t4;

   526     } db2

   527   } {db2.6}

   528   do_test attach-4.7 {

   529     execsql {

   530       CREATE TABLE t4(y);

   531       CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN

   532         INSERT INTO t4 VALUES('main.' || NEW.a);

   533       END;

   534       INSERT INTO main.t3 VALUES(11,12);

   535       SELECT * FROM main.t4;

   536     }

   537   } {main.11}

   538 }

   539 ifcapable {!trigger} {

   540   # When we do not have trigger support, set up the table like they

   541   # would have been had triggers been there.  The tests that follow need

   542   # this setup.

   543   execsql {

   544     CREATE TABLE t4(x);

   545     INSERT INTO t3 VALUES(6,7);

   546     INSERT INTO t4 VALUES('db2.6');

   547     INSERT INTO t4 VALUES('db2.13');

   548   } db2

   549   execsql {

   550     CREATE TABLE t4(y);

   551     INSERT INTO main.t3 VALUES(11,12);

   552     INSERT INTO t4 VALUES('main.11');

   553   }

   554 }

   555 

   556 

   557 # This one is tricky.  On the UNION ALL select, we have to make sure

   558 # the schema for both main and db2 is valid before starting to execute

   559 # the first query of the UNION ALL.  If we wait to test the validity of

   560 # the schema for main until after the first query has run, that test will

   561 # fail and the query will abort but we will have already output some

   562 # results.  When the query is retried, the results will be repeated.

   563 #

   564 ifcapable compound {

   565 do_test attach-4.8 {

   566   execsql {

   567     ATTACH DATABASE 'test2.db' AS db2;

   568     INSERT INTO db2.t3 VALUES(13,14);

   569     SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;

   570   }

   571 } {db2.6 db2.13 main.11}

   572 

   573 do_test attach-4.9 {

   574   ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}

   575   execsql {

   576     INSERT INTO main.t3 VALUES(15,16);

   577     SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;

   578   }

   579 } {db2.6 db2.13 main.11 main.15}

   580 } ;# ifcapable compound

   581 

   582 ifcapable !compound {

   583   ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}

   584   execsql {

   585     ATTACH DATABASE 'test2.db' AS db2;

   586     INSERT INTO db2.t3 VALUES(13,14);

   587     INSERT INTO main.t3 VALUES(15,16);

   588   } 

   589 } ;# ifcapable !compound

   590 

   591 ifcapable view {

   592 do_test attach-4.10 {

   593   execsql {

   594     DETACH DATABASE db2;

   595   }

   596   execsql {

   597     CREATE VIEW v3 AS SELECT x*100+y FROM t3;

   598     SELECT * FROM v3;

   599   } db2

   600 } {102 910 607 1314}

   601 do_test attach-4.11 {

   602   execsql {

   603     CREATE VIEW v3 AS SELECT a*100+b FROM t3;

   604     SELECT * FROM v3;

   605   }

   606 } {910 1112 1516}

   607 do_test attach-4.12 {

   608   execsql {

   609     ATTACH DATABASE 'test2.db' AS db2;

   610     SELECT * FROM db2.v3;

   611   }

   612 } {102 910 607 1314}

   613 do_test attach-4.13 {

   614   execsql {

   615     SELECT * FROM main.v3;

   616   }

   617 } {910 1112 1516}

   618 } ;# ifcapable view

   619 

   620 # Tests for the sqliteFix...() routines in attach.c

   621 #

   622 ifcapable {trigger} {

   623 do_test attach-5.1 {

   624   db close

   625   sqlite3 db test.db

   626   db2 close

   627   file delete -force test2.db

   628   sqlite3 db2 test2.db

   629   catchsql {

   630     ATTACH DATABASE 'test.db' AS orig;

   631     CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN

   632       SELECT 'no-op';

   633     END;

   634   } db2

   635 } {1 {trigger r1 cannot reference objects in database orig}}

   636 do_test attach-5.2 {

   637   catchsql {

   638     CREATE TABLE t5(x,y);

   639     CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   640       SELECT 'no-op';

   641     END;

   642   } db2

   643 } {0 {}}

   644 do_test attach-5.3 {

   645   catchsql {

   646     DROP TRIGGER r5;

   647     CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   648       SELECT 'no-op' FROM orig.t1;

   649     END;

   650   } db2

   651 } {1 {trigger r5 cannot reference objects in database orig}}

   652 ifcapable tempdb {

   653   do_test attach-5.4 {

   654     catchsql {

   655       CREATE TEMP TABLE t6(p,q,r);

   656       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   657         SELECT 'no-op' FROM temp.t6;

   658       END;

   659     } db2

   660   } {1 {trigger r5 cannot reference objects in database temp}}

   661 }

   662 ifcapable subquery {

   663   do_test attach-5.5 {

   664     catchsql {

   665       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   666         SELECT 'no-op' || (SELECT * FROM temp.t6);

   667       END;

   668     } db2

   669   } {1 {trigger r5 cannot reference objects in database temp}}

   670   do_test attach-5.6 {

   671     catchsql {

   672       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   673         SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);

   674       END;

   675     } db2

   676   } {1 {trigger r5 cannot reference objects in database temp}}

   677   do_test attach-5.7 {

   678     catchsql {

   679       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   680         SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);

   681       END;

   682     } db2

   683   } {1 {trigger r5 cannot reference objects in database temp}}

   684   do_test attach-5.7 {

   685     catchsql {

   686       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   687         SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;

   688       END;

   689     } db2

   690   } {1 {trigger r5 cannot reference objects in database temp}}

   691   do_test attach-5.8 {

   692     catchsql {

   693       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   694         INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);

   695       END;

   696     } db2

   697   } {1 {trigger r5 cannot reference objects in database temp}}

   698   do_test attach-5.9 {

   699     catchsql {

   700       CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN

   701         DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);

   702       END;

   703     } db2

   704   } {1 {trigger r5 cannot reference objects in database temp}}

   705 } ;# endif subquery

   706 } ;# endif trigger

   707 

   708 # Check to make sure we get a sensible error if unable to open

   709 # the file that we are trying to attach.

   710 #

   711 do_test attach-6.1 {

   712   catchsql {

   713     ATTACH DATABASE 'no-such-file' AS nosuch;

   714   }

   715 } {0 {}}

   716 if {$tcl_platform(platform)=="unix"} {

   717   do_test attach-6.2 {

   718     sqlite3 dbx cannot-read

   719     dbx eval {CREATE TABLE t1(a,b,c)}

   720     dbx close

   721     file attributes cannot-read -permission 0000

   722     if {[file writable cannot-read]} {

   723       puts "\n**** Tests do not work when run as root ****"

   724       file delete -force cannot-read

   725       exit 1

   726     }

   727     catchsql {

   728       ATTACH DATABASE 'cannot-read' AS noread;

   729     }

   730   } {1 {unable to open database: cannot-read}}

   731   do_test attach-6.2.2 {

   732     db errorcode

   733   } {14}

   734   file delete -force cannot-read

   735 }

   736 

   737 # Check the error message if we try to access a database that has

   738 # not been attached.

   739 do_test attach-6.3 {

   740   catchsql {

   741     CREATE TABLE no_such_db.t1(a, b, c);

   742   }

   743 } {1 {unknown database no_such_db}}

   744 for {set i 2} {$i<=15} {incr i} {

   745   catch {db$i close}

   746 }

   747 db close

   748 file delete -force test2.db

   749 file delete -force no-such-file

   750 

   751 ifcapable subquery {

   752   do_test attach-7.1 {

   753     file delete -force test.db test.db-journal

   754     sqlite3 db test.db

   755     catchsql {

   756       DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY 

   757       REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL )

   758     }

   759   } {1 {invalid name: "RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY 

   760       REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL )"}}

   761 }

   762 

   763 # Create a malformed file (a file that is not a valid database)

   764 # and try to attach it

   765 #

   766 do_test attach-8.1 {

   767   set fd [open test2.db w]

   768   puts $fd "This file is not a valid SQLite database"

   769   close $fd

   770   catchsql {

   771     ATTACH 'test2.db' AS t2;

   772   }

   773 } {1 {file is encrypted or is not a database}}

   774 do_test attach-8.2 {

   775   db errorcode

   776 } {26}

   777 file delete -force test2.db

   778 do_test attach-8.3 {

   779   sqlite3 db2 test2.db

   780   db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE}

   781   catchsql {

   782     ATTACH 'test2.db' AS t2;

   783   }

   784 } {1 {database is locked}}

   785 do_test attach-8.4 {

   786   db errorcode

   787 } {5}

   788 db2 close

   789 file delete -force test2.db

   790 

   791 

   792 finish_test