1 # 2003 July 1

     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 schema changes to attached databases.

    14 #

    15 # $Id: attach3.test,v 1.18 2007/10/09 08:29:32 danielk1977 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 # Create tables t1 and t2 in the main database

    27 execsql {

    28   CREATE TABLE t1(a, b);

    29   CREATE TABLE t2(c, d);

    30 }

    31 

    32 # Create tables t1 and t2 in database file test2.db

    33 file delete -force test2.db

    34 file delete -force test2.db-journal

    35 sqlite3 db2 test2.db

    36 execsql {

    37   CREATE TABLE t1(a, b);

    38   CREATE TABLE t2(c, d);

    39 } db2

    40 db2 close

    41 

    42 # Create a table in the auxilary database.

    43 do_test attach3-1.1 {

    44   execsql {

    45     ATTACH 'test2.db' AS aux;

    46   }

    47 } {}

    48 do_test attach3-1.2 {

    49   execsql {

    50     CREATE TABLE aux.t3(e, f);

    51   }

    52 } {}

    53 do_test attach3-1.3 {

    54   execsql {

    55     SELECT * FROM sqlite_master WHERE name = 't3';

    56   }

    57 } {}

    58 do_test attach3-1.4 {

    59   execsql {

    60     SELECT * FROM aux.sqlite_master WHERE name = 't3';

    61   }

    62 } "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"

    63 do_test attach3-1.5 {

    64   execsql {

    65     INSERT INTO t3 VALUES(1, 2);

    66     SELECT * FROM t3;

    67   }

    68 } {1 2}

    69 

    70 # Create an index on the auxilary database table.

    71 do_test attach3-2.1 {

    72   execsql {

    73     CREATE INDEX aux.i1 on t3(e);

    74   }

    75 } {}

    76 do_test attach3-2.2 {

    77   execsql {

    78     SELECT * FROM sqlite_master WHERE name = 'i1';

    79   }

    80 } {}

    81 do_test attach3-2.3 {

    82   execsql {

    83     SELECT * FROM aux.sqlite_master WHERE name = 'i1';

    84   }

    85 } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"

    86 

    87 # Drop the index on the aux database table.

    88 do_test attach3-3.1 {

    89   execsql {

    90     DROP INDEX aux.i1;

    91     SELECT * FROM aux.sqlite_master WHERE name = 'i1';

    92   }

    93 } {}

    94 do_test attach3-3.2 {

    95   execsql {

    96     CREATE INDEX aux.i1 on t3(e);

    97     SELECT * FROM aux.sqlite_master WHERE name = 'i1';

    98   }

    99 } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"

   100 do_test attach3-3.3 {

   101   execsql {

   102     DROP INDEX i1;

   103     SELECT * FROM aux.sqlite_master WHERE name = 'i1';

   104   }

   105 } {}

   106 

   107 # Drop tables t1 and t2 in the auxilary database.

   108 do_test attach3-4.1 {

   109   execsql {

   110     DROP TABLE aux.t1;

   111     SELECT name FROM aux.sqlite_master;

   112   }

   113 } {t2 t3}

   114 do_test attach3-4.2 {

   115   # This will drop main.t2

   116   execsql {

   117     DROP TABLE t2;

   118     SELECT name FROM aux.sqlite_master;

   119   }

   120 } {t2 t3}

   121 do_test attach3-4.3 {

   122   execsql {

   123     DROP TABLE t2;

   124     SELECT name FROM aux.sqlite_master;

   125   }

   126 } {t3}

   127 

   128 # Create a view in the auxilary database.

   129 ifcapable view {

   130 do_test attach3-5.1 {

   131   execsql {

   132     CREATE VIEW aux.v1 AS SELECT * FROM t3;

   133   }

   134 } {}

   135 do_test attach3-5.2 {

   136   execsql {

   137     SELECT * FROM aux.sqlite_master WHERE name = 'v1';

   138   }

   139 } {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}

   140 do_test attach3-5.3 {

   141   execsql {

   142     INSERT INTO aux.t3 VALUES('hello', 'world');

   143     SELECT * FROM v1;

   144   }

   145 } {1 2 hello world}

   146 

   147 # Drop the view 

   148 do_test attach3-6.1 {

   149   execsql {

   150     DROP VIEW aux.v1;

   151   }

   152 } {}

   153 do_test attach3-6.2 {

   154   execsql {

   155     SELECT * FROM aux.sqlite_master WHERE name = 'v1';

   156   }

   157 } {}

   158 } ;# ifcapable view

   159 

   160 ifcapable {trigger} {

   161 # Create a trigger in the auxilary database.

   162 do_test attach3-7.1 {

   163   execsql {

   164     CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN

   165       INSERT INTO t3 VALUES(new.e*2, new.f*2);

   166     END;

   167   }

   168 } {}

   169 do_test attach3-7.2 {

   170   execsql {

   171     DELETE FROM t3;

   172     INSERT INTO t3 VALUES(10, 20);

   173     SELECT * FROM t3;

   174   }

   175 } {10 20 20 40}

   176 do_test attach3-5.3 {

   177   execsql {

   178     SELECT * FROM aux.sqlite_master WHERE name = 'tr1';

   179   }

   180 } {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN

   181       INSERT INTO t3 VALUES(new.e*2, new.f*2);

   182     END}}

   183 

   184 # Drop the trigger 

   185 do_test attach3-8.1 {

   186   execsql {

   187     DROP TRIGGER aux.tr1;

   188   }

   189 } {}

   190 do_test attach3-8.2 {

   191   execsql {

   192     SELECT * FROM aux.sqlite_master WHERE name = 'tr1';

   193   }

   194 } {}

   195 

   196 ifcapable tempdb {

   197   # Try to trick SQLite into dropping the wrong temp trigger.

   198   do_test attach3-9.0 {

   199     execsql {

   200       CREATE TABLE main.t4(a, b, c);

   201       CREATE TABLE aux.t4(a, b, c);

   202       CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN 

   203         SELECT 'hello world';

   204       END;

   205       SELECT count(*) FROM sqlite_temp_master;

   206     }

   207   } {1}

   208   do_test attach3-9.1 {

   209     execsql {

   210       DROP TABLE main.t4;

   211       SELECT count(*) FROM sqlite_temp_master;

   212     }

   213   } {1}

   214   do_test attach3-9.2 {

   215     execsql {

   216       DROP TABLE aux.t4;

   217       SELECT count(*) FROM sqlite_temp_master;

   218     }

   219   } {0}

   220 }

   221 } ;# endif trigger

   222 

   223 # Make sure the aux.sqlite_master table is read-only

   224 do_test attach3-10.0 {

   225   catchsql {

   226     INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);

   227   }

   228 } {1 {table sqlite_master may not be modified}}

   229 

   230 # Failure to attach leaves us in a workable state.

   231 # Ticket #811

   232 # Symbian OS: '/' in the file name replaced with '\'

   233 do_test attach3-11.0 {

   234   catchsql {

   235     ATTACH DATABASE '\nodir\nofile.x' AS notadb;

   236   }

   237 } {1 {unable to open database: \nodir\nofile.x}}

   238 do_test attach3-11.1 {

   239   catchsql {

   240     ATTACH DATABASE ':memory:' AS notadb;

   241   }

   242 } {0 {}}

   243 do_test attach3-11.2 {

   244   catchsql {

   245     DETACH DATABASE notadb;

   246   }

   247 } {0 {}}

   248 

   249 # Return a list of attached databases

   250 #

   251 proc db_list {} {

   252   set x [execsql {

   253     PRAGMA database_list;

   254   }]

   255   set y {}

   256   foreach {n id file} $x {lappend y $id}

   257   return $y

   258 }

   259 

   260 ifcapable schema_pragmas&&tempdb {

   261 

   262 ifcapable !trigger {

   263   execsql {create temp table dummy(dummy)}

   264 }

   265 

   266 # Ticket #1825

   267 #

   268 do_test attach3-12.1 {

   269   db_list

   270 } {main temp aux}

   271 do_test attach3-12.2 {

   272   execsql {

   273     ATTACH DATABASE ? AS ?

   274   }

   275   db_list

   276 } {main temp aux {}}

   277 do_test attach3-12.3 {

   278   execsql {

   279     DETACH aux

   280   }

   281   db_list

   282 } {main temp {}}

   283 do_test attach3-12.4 {

   284   execsql {

   285     DETACH ?

   286   }

   287   db_list

   288 } {main temp}

   289 do_test attach3-12.5 {

   290   execsql {

   291     ATTACH DATABASE '' AS ''

   292   }

   293   db_list

   294 } {main temp {}}

   295 do_test attach3-12.6 {

   296   execsql {

   297     DETACH ''

   298   }

   299   db_list

   300 } {main temp}

   301 do_test attach3-12.7 {

   302   execsql {

   303     ATTACH DATABASE '' AS ?

   304   }

   305   db_list

   306 } {main temp {}}

   307 do_test attach3-12.8 {

   308   execsql {

   309     DETACH ''

   310   }

   311   db_list

   312 } {main temp}

   313 do_test attach3-12.9 {

   314   execsql {

   315     ATTACH DATABASE '' AS NULL

   316   }

   317   db_list

   318 } {main temp {}}

   319 do_test attach3-12.10 {

   320   execsql {

   321     DETACH ?

   322   }

   323   db_list

   324 } {main temp}

   325 do_test attach3-12.11 {

   326   catchsql {

   327     DETACH NULL

   328   }

   329 } {1 {no such database: }}

   330 do_test attach3-12.12 {

   331   catchsql {

   332     ATTACH null AS null;

   333     ATTACH '' AS '';

   334   }

   335 } {1 {database  is already in use}}

   336 do_test attach3-12.13 {

   337   db_list

   338 } {main temp {}}

   339 do_test attach3-12.14 {

   340   execsql {

   341     DETACH '';

   342   }

   343   db_list

   344 } {main temp}

   345 

   346 } ;# ifcapable pragma

   347 

   348 finish_test