AryaWu/sqlite

1# 2003 July 12#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is testing the ATTACH and DETACH commands13# and schema changes to attached databases.14#15# $Id: attach3.test,v 1.18 2007/10/09 08:29:32 danielk1977 Exp $16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21ifcapable !attach {22  finish_test23  return24}25 26# The tests in this file were written before SQLite supported recursive27# trigger invocation, and some tests depend on that to pass. So disable28# recursive triggers for this file.29catchsql { pragma recursive_triggers = off } 30 31# Create tables t1 and t2 in the main database32execsql {33  CREATE TABLE t1(a, b);34  CREATE TABLE t2(c, d);35}36 37# Create tables t1 and t2 in database file test2.db38forcedelete test2.db39forcedelete test2.db-journal40sqlite3 db2 test2.db41execsql {42  CREATE TABLE t1(a, b);43  CREATE TABLE t2(c, d);44} db245db2 close46 47# Create a table in the auxilary database.48do_test attach3-1.1 {49  execsql {50    ATTACH 'test2.db' AS aux;51  }52} {}53do_test attach3-1.2 {54  execsql {55    CREATE TABLE aux.t3(e, f);56  }57} {}58do_test attach3-1.3 {59  execsql {60    SELECT * FROM sqlite_master WHERE name = 't3';61  }62} {}63do_test attach3-1.4 {64  execsql {65    SELECT * FROM aux.sqlite_master WHERE name = 't3';66  }67} "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"68do_test attach3-1.5 {69  execsql {70    INSERT INTO t3 VALUES(1, 2);71    SELECT * FROM t3;72  }73} {1 2}74 75# Create an index on the auxilary database table.76do_test attach3-2.1 {77  execsql {78    CREATE INDEX aux.i1 on t3(e);79  }80} {}81do_test attach3-2.2 {82  execsql {83    SELECT * FROM sqlite_master WHERE name = 'i1';84  }85} {}86do_test attach3-2.3 {87  execsql {88    SELECT * FROM aux.sqlite_master WHERE name = 'i1';89  }90} "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"91 92# Drop the index on the aux database table.93do_test attach3-3.1 {94  execsql {95    DROP INDEX aux.i1;96    SELECT * FROM aux.sqlite_master WHERE name = 'i1';97  }98} {}99do_test attach3-3.2 {100  execsql {101    CREATE INDEX aux.i1 on t3(e);102    SELECT * FROM aux.sqlite_master WHERE name = 'i1';103  }104} "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"105do_test attach3-3.3 {106  execsql {107    DROP INDEX i1;108    SELECT * FROM aux.sqlite_master WHERE name = 'i1';109  }110} {}111 112# Drop tables t1 and t2 in the auxilary database.113do_test attach3-4.1 {114  execsql {115    DROP TABLE aux.t1;116    SELECT name FROM aux.sqlite_master;117  }118} {t2 t3}119do_test attach3-4.2 {120  # This will drop main.t2121  execsql {122    DROP TABLE t2;123    SELECT name FROM aux.sqlite_master;124  }125} {t2 t3}126do_test attach3-4.3 {127  execsql {128    DROP TABLE t2;129    SELECT name FROM aux.sqlite_master;130  }131} {t3}132 133# Create a view in the auxilary database.134ifcapable view {135do_test attach3-5.1 {136  execsql {137    CREATE VIEW aux.v1 AS SELECT * FROM t3;138  }139} {}140do_test attach3-5.2 {141  execsql {142    SELECT * FROM aux.sqlite_master WHERE name = 'v1';143  }144} {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}145do_test attach3-5.3 {146  execsql {147    INSERT INTO aux.t3 VALUES('hello', 'world');148    SELECT * FROM v1;149  }150} {1 2 hello world}151 152# Drop the view 153do_test attach3-6.1 {154  execsql {155    DROP VIEW aux.v1;156  }157} {}158do_test attach3-6.2 {159  execsql {160    SELECT * FROM aux.sqlite_master WHERE name = 'v1';161  }162} {}163} ;# ifcapable view164 165ifcapable {trigger} {166# Create a trigger in the auxilary database.167do_test attach3-7.1 {168  execsql {169    CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN170      INSERT INTO t3 VALUES(new.e*2, new.f*2);171    END;172  }173} {}174do_test attach3-7.2 {175  execsql {176    DELETE FROM t3;177    INSERT INTO t3 VALUES(10, 20);178    SELECT * FROM t3;179  }180} {10 20 20 40}181do_test attach3-5.3 {182  execsql {183    SELECT * FROM aux.sqlite_master WHERE name = 'tr1';184  }185} {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN186      INSERT INTO t3 VALUES(new.e*2, new.f*2);187    END}}188 189# Drop the trigger 190do_test attach3-8.1 {191  execsql {192    DROP TRIGGER aux.tr1;193  }194} {}195do_test attach3-8.2 {196  execsql {197    SELECT * FROM aux.sqlite_master WHERE name = 'tr1';198  }199} {}200 201ifcapable tempdb {202  # Try to trick SQLite into dropping the wrong temp trigger.203  do_test attach3-9.0 {204    execsql {205      CREATE TABLE main.t4(a, b, c);206      CREATE TABLE aux.t4(a, b, c);207      CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN 208        SELECT 'hello world';209      END;210      SELECT count(*) FROM temp.sqlite_master;211    }212  } {1}213  do_test attach3-9.1 {214    execsql {215      DROP TABLE main.t4;216      SELECT count(*) FROM sqlite_temp_master;217    }218  } {1}219  do_test attach3-9.2 {220    execsql {221      DROP TABLE aux.t4;222      SELECT count(*) FROM temp.sqlite_master;223    }224  } {0}225}226} ;# endif trigger227 228# Make sure the aux.sqlite_master table is read-only229do_test attach3-10.0 {230  catchsql {231    INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);232  }233} {1 {table sqlite_master may not be modified}}234 235# Failure to attach leaves us in a workable state.236# Ticket #811237#238do_test attach3-11.0 {239  catchsql {240    ATTACH DATABASE '/nodir/nofile.x' AS notadb;241  }242} {1 {unable to open database: /nodir/nofile.x}}243do_test attach3-11.1 {244  catchsql {245    ATTACH DATABASE ':memory:' AS notadb;246  }247} {0 {}}248do_test attach3-11.2 {249  catchsql {250    DETACH DATABASE notadb;251  }252} {0 {}}253 254# Return a list of attached databases255#256proc db_list {} {257  set x [execsql {258    PRAGMA database_list;259  }]260  set y {}261  foreach {n id file} $x {lappend y $id}262  return $y263}264 265ifcapable schema_pragmas&&tempdb {266 267ifcapable !trigger {268  execsql {create temp table dummy(dummy)}269}270 271# Ticket #1825272#273do_test attach3-12.1 {274  db_list275} {main temp aux}276do_test attach3-12.2 {277  execsql {278    ATTACH DATABASE ? AS ?279  }280  db_list281} {main temp aux {}}282do_test attach3-12.3 {283  execsql {284    DETACH aux285  }286  db_list287} {main temp {}}288do_test attach3-12.4 {289  execsql {290    DETACH ?291  }292  db_list293} {main temp}294do_test attach3-12.5 {295  execsql {296    ATTACH DATABASE '' AS ''297  }298  db_list299} {main temp {}}300do_test attach3-12.6 {301  execsql {302    DETACH ''303  }304  db_list305} {main temp}306do_test attach3-12.7 {307  execsql {308    ATTACH DATABASE '' AS ?309  }310  db_list311} {main temp {}}312do_test attach3-12.8 {313  execsql {314    DETACH ''315  }316  db_list317} {main temp}318do_test attach3-12.9 {319  execsql {320    ATTACH DATABASE '' AS NULL321  }322  db_list323} {main temp {}}324do_test attach3-12.10 {325  execsql {326    DETACH ?327  }328  db_list329} {main temp}330do_test attach3-12.11 {331  catchsql {332    DETACH NULL333  }334} {1 {no such database: }}335do_test attach3-12.12 {336  catchsql {337    ATTACH null AS null;338    ATTACH '' AS '';339  }340} {1 {database  is already in use}}341do_test attach3-12.13 {342  db_list343} {main temp {}}344do_test attach3-12.14 {345  execsql {346    DETACH '';347  }348  db_list349} {main temp}350 351} ;# ifcapable pragma352 353finish_test354