From 7bb481fda9ecb134804b49c2ce77ca28f7eea583 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Fri, 30 Mar 2012 20:42:12 -0400 Subject: Imported Upstream version 2.0.3 --- test/attach3.test | 354 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 354 insertions(+) create mode 100644 test/attach3.test (limited to 'test/attach3.test') diff --git a/test/attach3.test b/test/attach3.test new file mode 100644 index 0000000..f861425 --- /dev/null +++ b/test/attach3.test @@ -0,0 +1,354 @@ +# 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 +forcedelete test2.db +forcedelete 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 -- cgit v1.2.3