1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/attach3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,348 @@
1.4 +# 2003 July 1
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing the ATTACH and DETACH commands
1.16 +# and schema changes to attached databases.
1.17 +#
1.18 +# $Id: attach3.test,v 1.18 2007/10/09 08:29:32 danielk1977 Exp $
1.19 +#
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +ifcapable !attach {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +# Create tables t1 and t2 in the main database
1.30 +execsql {
1.31 + CREATE TABLE t1(a, b);
1.32 + CREATE TABLE t2(c, d);
1.33 +}
1.34 +
1.35 +# Create tables t1 and t2 in database file test2.db
1.36 +file delete -force test2.db
1.37 +file delete -force test2.db-journal
1.38 +sqlite3 db2 test2.db
1.39 +execsql {
1.40 + CREATE TABLE t1(a, b);
1.41 + CREATE TABLE t2(c, d);
1.42 +} db2
1.43 +db2 close
1.44 +
1.45 +# Create a table in the auxilary database.
1.46 +do_test attach3-1.1 {
1.47 + execsql {
1.48 + ATTACH 'test2.db' AS aux;
1.49 + }
1.50 +} {}
1.51 +do_test attach3-1.2 {
1.52 + execsql {
1.53 + CREATE TABLE aux.t3(e, f);
1.54 + }
1.55 +} {}
1.56 +do_test attach3-1.3 {
1.57 + execsql {
1.58 + SELECT * FROM sqlite_master WHERE name = 't3';
1.59 + }
1.60 +} {}
1.61 +do_test attach3-1.4 {
1.62 + execsql {
1.63 + SELECT * FROM aux.sqlite_master WHERE name = 't3';
1.64 + }
1.65 +} "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"
1.66 +do_test attach3-1.5 {
1.67 + execsql {
1.68 + INSERT INTO t3 VALUES(1, 2);
1.69 + SELECT * FROM t3;
1.70 + }
1.71 +} {1 2}
1.72 +
1.73 +# Create an index on the auxilary database table.
1.74 +do_test attach3-2.1 {
1.75 + execsql {
1.76 + CREATE INDEX aux.i1 on t3(e);
1.77 + }
1.78 +} {}
1.79 +do_test attach3-2.2 {
1.80 + execsql {
1.81 + SELECT * FROM sqlite_master WHERE name = 'i1';
1.82 + }
1.83 +} {}
1.84 +do_test attach3-2.3 {
1.85 + execsql {
1.86 + SELECT * FROM aux.sqlite_master WHERE name = 'i1';
1.87 + }
1.88 +} "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
1.89 +
1.90 +# Drop the index on the aux database table.
1.91 +do_test attach3-3.1 {
1.92 + execsql {
1.93 + DROP INDEX aux.i1;
1.94 + SELECT * FROM aux.sqlite_master WHERE name = 'i1';
1.95 + }
1.96 +} {}
1.97 +do_test attach3-3.2 {
1.98 + execsql {
1.99 + CREATE INDEX aux.i1 on t3(e);
1.100 + SELECT * FROM aux.sqlite_master WHERE name = 'i1';
1.101 + }
1.102 +} "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
1.103 +do_test attach3-3.3 {
1.104 + execsql {
1.105 + DROP INDEX i1;
1.106 + SELECT * FROM aux.sqlite_master WHERE name = 'i1';
1.107 + }
1.108 +} {}
1.109 +
1.110 +# Drop tables t1 and t2 in the auxilary database.
1.111 +do_test attach3-4.1 {
1.112 + execsql {
1.113 + DROP TABLE aux.t1;
1.114 + SELECT name FROM aux.sqlite_master;
1.115 + }
1.116 +} {t2 t3}
1.117 +do_test attach3-4.2 {
1.118 + # This will drop main.t2
1.119 + execsql {
1.120 + DROP TABLE t2;
1.121 + SELECT name FROM aux.sqlite_master;
1.122 + }
1.123 +} {t2 t3}
1.124 +do_test attach3-4.3 {
1.125 + execsql {
1.126 + DROP TABLE t2;
1.127 + SELECT name FROM aux.sqlite_master;
1.128 + }
1.129 +} {t3}
1.130 +
1.131 +# Create a view in the auxilary database.
1.132 +ifcapable view {
1.133 +do_test attach3-5.1 {
1.134 + execsql {
1.135 + CREATE VIEW aux.v1 AS SELECT * FROM t3;
1.136 + }
1.137 +} {}
1.138 +do_test attach3-5.2 {
1.139 + execsql {
1.140 + SELECT * FROM aux.sqlite_master WHERE name = 'v1';
1.141 + }
1.142 +} {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}
1.143 +do_test attach3-5.3 {
1.144 + execsql {
1.145 + INSERT INTO aux.t3 VALUES('hello', 'world');
1.146 + SELECT * FROM v1;
1.147 + }
1.148 +} {1 2 hello world}
1.149 +
1.150 +# Drop the view
1.151 +do_test attach3-6.1 {
1.152 + execsql {
1.153 + DROP VIEW aux.v1;
1.154 + }
1.155 +} {}
1.156 +do_test attach3-6.2 {
1.157 + execsql {
1.158 + SELECT * FROM aux.sqlite_master WHERE name = 'v1';
1.159 + }
1.160 +} {}
1.161 +} ;# ifcapable view
1.162 +
1.163 +ifcapable {trigger} {
1.164 +# Create a trigger in the auxilary database.
1.165 +do_test attach3-7.1 {
1.166 + execsql {
1.167 + CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
1.168 + INSERT INTO t3 VALUES(new.e*2, new.f*2);
1.169 + END;
1.170 + }
1.171 +} {}
1.172 +do_test attach3-7.2 {
1.173 + execsql {
1.174 + DELETE FROM t3;
1.175 + INSERT INTO t3 VALUES(10, 20);
1.176 + SELECT * FROM t3;
1.177 + }
1.178 +} {10 20 20 40}
1.179 +do_test attach3-5.3 {
1.180 + execsql {
1.181 + SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
1.182 + }
1.183 +} {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN
1.184 + INSERT INTO t3 VALUES(new.e*2, new.f*2);
1.185 + END}}
1.186 +
1.187 +# Drop the trigger
1.188 +do_test attach3-8.1 {
1.189 + execsql {
1.190 + DROP TRIGGER aux.tr1;
1.191 + }
1.192 +} {}
1.193 +do_test attach3-8.2 {
1.194 + execsql {
1.195 + SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
1.196 + }
1.197 +} {}
1.198 +
1.199 +ifcapable tempdb {
1.200 + # Try to trick SQLite into dropping the wrong temp trigger.
1.201 + do_test attach3-9.0 {
1.202 + execsql {
1.203 + CREATE TABLE main.t4(a, b, c);
1.204 + CREATE TABLE aux.t4(a, b, c);
1.205 + CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN
1.206 + SELECT 'hello world';
1.207 + END;
1.208 + SELECT count(*) FROM sqlite_temp_master;
1.209 + }
1.210 + } {1}
1.211 + do_test attach3-9.1 {
1.212 + execsql {
1.213 + DROP TABLE main.t4;
1.214 + SELECT count(*) FROM sqlite_temp_master;
1.215 + }
1.216 + } {1}
1.217 + do_test attach3-9.2 {
1.218 + execsql {
1.219 + DROP TABLE aux.t4;
1.220 + SELECT count(*) FROM sqlite_temp_master;
1.221 + }
1.222 + } {0}
1.223 +}
1.224 +} ;# endif trigger
1.225 +
1.226 +# Make sure the aux.sqlite_master table is read-only
1.227 +do_test attach3-10.0 {
1.228 + catchsql {
1.229 + INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
1.230 + }
1.231 +} {1 {table sqlite_master may not be modified}}
1.232 +
1.233 +# Failure to attach leaves us in a workable state.
1.234 +# Ticket #811
1.235 +# Symbian OS: '/' in the file name replaced with '\'
1.236 +do_test attach3-11.0 {
1.237 + catchsql {
1.238 + ATTACH DATABASE '\nodir\nofile.x' AS notadb;
1.239 + }
1.240 +} {1 {unable to open database: \nodir\nofile.x}}
1.241 +do_test attach3-11.1 {
1.242 + catchsql {
1.243 + ATTACH DATABASE ':memory:' AS notadb;
1.244 + }
1.245 +} {0 {}}
1.246 +do_test attach3-11.2 {
1.247 + catchsql {
1.248 + DETACH DATABASE notadb;
1.249 + }
1.250 +} {0 {}}
1.251 +
1.252 +# Return a list of attached databases
1.253 +#
1.254 +proc db_list {} {
1.255 + set x [execsql {
1.256 + PRAGMA database_list;
1.257 + }]
1.258 + set y {}
1.259 + foreach {n id file} $x {lappend y $id}
1.260 + return $y
1.261 +}
1.262 +
1.263 +ifcapable schema_pragmas&&tempdb {
1.264 +
1.265 +ifcapable !trigger {
1.266 + execsql {create temp table dummy(dummy)}
1.267 +}
1.268 +
1.269 +# Ticket #1825
1.270 +#
1.271 +do_test attach3-12.1 {
1.272 + db_list
1.273 +} {main temp aux}
1.274 +do_test attach3-12.2 {
1.275 + execsql {
1.276 + ATTACH DATABASE ? AS ?
1.277 + }
1.278 + db_list
1.279 +} {main temp aux {}}
1.280 +do_test attach3-12.3 {
1.281 + execsql {
1.282 + DETACH aux
1.283 + }
1.284 + db_list
1.285 +} {main temp {}}
1.286 +do_test attach3-12.4 {
1.287 + execsql {
1.288 + DETACH ?
1.289 + }
1.290 + db_list
1.291 +} {main temp}
1.292 +do_test attach3-12.5 {
1.293 + execsql {
1.294 + ATTACH DATABASE '' AS ''
1.295 + }
1.296 + db_list
1.297 +} {main temp {}}
1.298 +do_test attach3-12.6 {
1.299 + execsql {
1.300 + DETACH ''
1.301 + }
1.302 + db_list
1.303 +} {main temp}
1.304 +do_test attach3-12.7 {
1.305 + execsql {
1.306 + ATTACH DATABASE '' AS ?
1.307 + }
1.308 + db_list
1.309 +} {main temp {}}
1.310 +do_test attach3-12.8 {
1.311 + execsql {
1.312 + DETACH ''
1.313 + }
1.314 + db_list
1.315 +} {main temp}
1.316 +do_test attach3-12.9 {
1.317 + execsql {
1.318 + ATTACH DATABASE '' AS NULL
1.319 + }
1.320 + db_list
1.321 +} {main temp {}}
1.322 +do_test attach3-12.10 {
1.323 + execsql {
1.324 + DETACH ?
1.325 + }
1.326 + db_list
1.327 +} {main temp}
1.328 +do_test attach3-12.11 {
1.329 + catchsql {
1.330 + DETACH NULL
1.331 + }
1.332 +} {1 {no such database: }}
1.333 +do_test attach3-12.12 {
1.334 + catchsql {
1.335 + ATTACH null AS null;
1.336 + ATTACH '' AS '';
1.337 + }
1.338 +} {1 {database is already in use}}
1.339 +do_test attach3-12.13 {
1.340 + db_list
1.341 +} {main temp {}}
1.342 +do_test attach3-12.14 {
1.343 + execsql {
1.344 + DETACH '';
1.345 + }
1.346 + db_list
1.347 +} {main temp}
1.348 +
1.349 +} ;# ifcapable pragma
1.350 +
1.351 +finish_test