# 2003 July 1 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script is testing the ATTACH and DETACH commands # and schema changes to attached databases. # # $Id: attach3.test,v 1.18 2007/10/09 08:29:32 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !attach { finish_test return } # The tests in this file were written before SQLite supported recursive # trigger invocation, and some tests depend on that to pass. So disable # recursive triggers for this file. catchsql { pragma recursive_triggers = off } # Create tables t1 and t2 in the main database execsql { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); } # Create tables t1 and t2 in database file test2.db file delete -force test2.db file delete -force test2.db-journal sqlite3 db2 test2.db execsql { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); } db2 db2 close # Create a table in the auxilary database. do_test attach3-1.1 { execsql { ATTACH 'test2.db' AS aux; } } {} do_test attach3-1.2 { execsql { CREATE TABLE aux.t3(e, f); } } {} do_test attach3-1.3 { execsql { SELECT * FROM sqlite_master WHERE name = 't3'; } } {} do_test attach3-1.4 { execsql { SELECT * FROM aux.sqlite_master WHERE name = 't3'; } } "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}" do_test attach3-1.5 { execsql { INSERT INTO t3 VALUES(1, 2); SELECT * FROM t3; } } {1 2} # Create an index on the auxilary database table. do_test attach3-2.1 { execsql { CREATE INDEX aux.i1 on t3(e); } } {} do_test attach3-2.2 { execsql { SELECT * FROM sqlite_master WHERE name = 'i1'; } } {} do_test attach3-2.3 { execsql { SELECT * FROM aux.sqlite_master WHERE name = 'i1'; } } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}" # Drop the index on the aux database table. do_test attach3-3.1 { execsql { DROP INDEX aux.i1; SELECT * FROM aux.sqlite_master WHERE name = 'i1'; } } {} do_test attach3-3.2 { execsql { CREATE INDEX aux.i1 on t3(e); SELECT * FROM aux.sqlite_master WHERE name = 'i1'; } } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}" do_test attach3-3.3 { execsql { DROP INDEX i1; SELECT * FROM aux.sqlite_master WHERE name = 'i1'; } } {} # Drop tables t1 and t2 in the auxilary database. do_test attach3-4.1 { execsql { DROP TABLE aux.t1; SELECT name FROM aux.sqlite_master; } } {t2 t3} do_test attach3-4.2 { # This will drop main.t2 execsql { DROP TABLE t2; SELECT name FROM aux.sqlite_master; } } {t2 t3} do_test attach3-4.3 { execsql { DROP TABLE t2; SELECT name FROM aux.sqlite_master; } } {t3} # Create a view in the auxilary database. ifcapable view { do_test attach3-5.1 { execsql { CREATE VIEW aux.v1 AS SELECT * FROM t3; } } {} do_test attach3-5.2 { execsql { SELECT * FROM aux.sqlite_master WHERE name = 'v1'; } } {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}} do_test attach3-5.3 { execsql { INSERT INTO aux.t3 VALUES('hello', 'world'); SELECT * FROM v1; } } {1 2 hello world} # Drop the view do_test attach3-6.1 { execsql { DROP VIEW aux.v1; } } {} do_test attach3-6.2 { execsql { SELECT * FROM aux.sqlite_master WHERE name = 'v1'; } } {} } ;# ifcapable view ifcapable {trigger} { # Create a trigger in the auxilary database. do_test attach3-7.1 { execsql { CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN INSERT INTO t3 VALUES(new.e*2, new.f*2); END; } } {} do_test attach3-7.2 { execsql { DELETE FROM t3; INSERT INTO t3 VALUES(10, 20); SELECT * FROM t3; } } {10 20 20 40} do_test attach3-5.3 { execsql { SELECT * FROM aux.sqlite_master WHERE name = 'tr1'; } } {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN INSERT INTO t3 VALUES(new.e*2, new.f*2); END}} # Drop the trigger do_test attach3-8.1 { execsql { DROP TRIGGER aux.tr1; } } {} do_test attach3-8.2 { execsql { SELECT * FROM aux.sqlite_master WHERE name = 'tr1'; } } {} ifcapable tempdb { # Try to trick SQLite into dropping the wrong temp trigger. do_test attach3-9.0 { execsql { CREATE TABLE main.t4(a, b, c); CREATE TABLE aux.t4(a, b, c); CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN SELECT 'hello world'; END; SELECT count(*) FROM sqlite_temp_master; } } {1} do_test attach3-9.1 { execsql { DROP TABLE main.t4; SELECT count(*) FROM sqlite_temp_master; } } {1} do_test attach3-9.2 { execsql { DROP TABLE aux.t4; SELECT count(*) FROM sqlite_temp_master; } } {0} } } ;# endif trigger # Make sure the aux.sqlite_master table is read-only do_test attach3-10.0 { catchsql { INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5); } } {1 {table sqlite_master may not be modified}} # Failure to attach leaves us in a workable state. # Ticket #811 # do_test attach3-11.0 { catchsql { ATTACH DATABASE '/nodir/nofile.x' AS notadb; } } {1 {unable to open database: /nodir/nofile.x}} do_test attach3-11.1 { catchsql { ATTACH DATABASE ':memory:' AS notadb; } } {0 {}} do_test attach3-11.2 { catchsql { DETACH DATABASE notadb; } } {0 {}} # Return a list of attached databases # proc db_list {} { set x [execsql { PRAGMA database_list; }] set y {} foreach {n id file} $x {lappend y $id} return $y } ifcapable schema_pragmas&&tempdb { ifcapable !trigger { execsql {create temp table dummy(dummy)} } # Ticket #1825 # do_test attach3-12.1 { db_list } {main temp aux} do_test attach3-12.2 { execsql { ATTACH DATABASE ? AS ? } db_list } {main temp aux {}} do_test attach3-12.3 { execsql { DETACH aux } db_list } {main temp {}} do_test attach3-12.4 { execsql { DETACH ? } db_list } {main temp} do_test attach3-12.5 { execsql { ATTACH DATABASE '' AS '' } db_list } {main temp {}} do_test attach3-12.6 { execsql { DETACH '' } db_list } {main temp} do_test attach3-12.7 { execsql { ATTACH DATABASE '' AS ? } db_list } {main temp {}} do_test attach3-12.8 { execsql { DETACH '' } db_list } {main temp} do_test attach3-12.9 { execsql { ATTACH DATABASE '' AS NULL } db_list } {main temp {}} do_test attach3-12.10 { breakpoint execsql { DETACH ? } db_list } {main temp} do_test attach3-12.11 { catchsql { DETACH NULL } } {1 {no such database: }} do_test attach3-12.12 { catchsql { ATTACH null AS null; ATTACH '' AS ''; } } {1 {database is already in use}} do_test attach3-12.13 { db_list } {main temp {}} do_test attach3-12.14 { execsql { DETACH ''; } db_list } {main temp} } ;# ifcapable pragma finish_test