/trunk/src/sqlite/test/attach3.test
Unknown | 354 lines | 332 code | 22 blank | 0 comment | 0 complexity | c43ae5e8c3742f6e899b153fdd861e36 MD5 | raw file
Possible License(s): BSD-3-Clause
1# 2003 July 1 2# 3# The author disclaims copyright to this source code. In place of 4# 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. The 12# focus of this script is testing the ATTACH and DETACH commands 13# 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.tcl 20 21ifcapable !attach { 22 finish_test 23 return 24} 25 26# The tests in this file were written before SQLite supported recursive 27# trigger invocation, and some tests depend on that to pass. So disable 28# recursive triggers for this file. 29catchsql { pragma recursive_triggers = off } 30 31# Create tables t1 and t2 in the main database 32execsql { 33 CREATE TABLE t1(a, b); 34 CREATE TABLE t2(c, d); 35} 36 37# Create tables t1 and t2 in database file test2.db 38file delete -force test2.db 39file delete -force test2.db-journal 40sqlite3 db2 test2.db 41execsql { 42 CREATE TABLE t1(a, b); 43 CREATE TABLE t2(c, d); 44} db2 45db2 close 46 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.t2 121 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 view 164 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 BEGIN 170 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 BEGIN 186 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 sqlite_temp_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 sqlite_temp_master; 223 } 224 } {0} 225} 226} ;# endif trigger 227 228# Make sure the aux.sqlite_master table is read-only 229do_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 #811 237# 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 databases 255# 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 $y 263} 264 265ifcapable schema_pragmas&&tempdb { 266 267ifcapable !trigger { 268 execsql {create temp table dummy(dummy)} 269} 270 271# Ticket #1825 272# 273do_test attach3-12.1 { 274 db_list 275} {main temp aux} 276do_test attach3-12.2 { 277 execsql { 278 ATTACH DATABASE ? AS ? 279 } 280 db_list 281} {main temp aux {}} 282do_test attach3-12.3 { 283 execsql { 284 DETACH aux 285 } 286 db_list 287} {main temp {}} 288do_test attach3-12.4 { 289 execsql { 290 DETACH ? 291 } 292 db_list 293} {main temp} 294do_test attach3-12.5 { 295 execsql { 296 ATTACH DATABASE '' AS '' 297 } 298 db_list 299} {main temp {}} 300do_test attach3-12.6 { 301 execsql { 302 DETACH '' 303 } 304 db_list 305} {main temp} 306do_test attach3-12.7 { 307 execsql { 308 ATTACH DATABASE '' AS ? 309 } 310 db_list 311} {main temp {}} 312do_test attach3-12.8 { 313 execsql { 314 DETACH '' 315 } 316 db_list 317} {main temp} 318do_test attach3-12.9 { 319 execsql { 320 ATTACH DATABASE '' AS NULL 321 } 322 db_list 323} {main temp {}} 324do_test attach3-12.10 { 325breakpoint 326 execsql { 327 DETACH ? 328 } 329 db_list 330} {main temp} 331do_test attach3-12.11 { 332 catchsql { 333 DETACH NULL 334 } 335} {1 {no such database: }} 336do_test attach3-12.12 { 337 catchsql { 338 ATTACH null AS null; 339 ATTACH '' AS ''; 340 } 341} {1 {database is already in use}} 342do_test attach3-12.13 { 343 db_list 344} {main temp {}} 345do_test attach3-12.14 { 346 execsql { 347 DETACH ''; 348 } 349 db_list 350} {main temp} 351 352} ;# ifcapable pragma 353 354finish_test