# 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.5 2004/05/29 10:23:20 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # 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 sqlite 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 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); } } {} execsql { pragma vdbe_trace = off; } 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 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 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. 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'; } } {} # 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'; } } {} finish_test