drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 1 | # 2003 April 4 |
| 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 related functionality. |
| 14 | # |
drh | fd773cf | 2009-05-29 14:39:07 +0000 | [diff] [blame] | 15 | # $Id: attach.test,v 1.52 2009/05/29 14:39:08 drh Exp $ |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 16 | # |
| 17 | |
| 18 | set testdir [file dirname $argv0] |
| 19 | source $testdir/tester.tcl |
| 20 | |
danielk1977 | 5a8f937 | 2007-10-09 08:29:32 +0000 | [diff] [blame] | 21 | ifcapable !attach { |
| 22 | finish_test |
| 23 | return |
| 24 | } |
| 25 | |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 26 | for {set i 2} {$i<=15} {incr i} { |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 27 | forcedelete test$i.db |
| 28 | forcedelete test$i.db-journal |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 29 | } |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 30 | |
| 31 | do_test attach-1.1 { |
| 32 | execsql { |
| 33 | CREATE TABLE t1(a,b); |
| 34 | INSERT INTO t1 VALUES(1,2); |
| 35 | INSERT INTO t1 VALUES(3,4); |
| 36 | SELECT * FROM t1; |
| 37 | } |
| 38 | } {1 2 3 4} |
| 39 | do_test attach-1.2 { |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 40 | sqlite3 db2 test2.db |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 41 | execsql { |
| 42 | CREATE TABLE t2(x,y); |
| 43 | INSERT INTO t2 VALUES(1,'x'); |
| 44 | INSERT INTO t2 VALUES(2,'y'); |
| 45 | SELECT * FROM t2; |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 46 | } db2 |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 47 | } {1 x 2 y} |
| 48 | do_test attach-1.3 { |
| 49 | execsql { |
| 50 | ATTACH DATABASE 'test2.db' AS two; |
| 51 | SELECT * FROM two.t2; |
| 52 | } |
| 53 | } {1 x 2 y} |
| 54 | do_test attach-1.4 { |
| 55 | execsql { |
| 56 | SELECT * FROM t2; |
| 57 | } |
| 58 | } {1 x 2 y} |
| 59 | do_test attach-1.5 { |
| 60 | execsql { |
| 61 | DETACH DATABASE two; |
| 62 | SELECT * FROM t1; |
| 63 | } |
| 64 | } {1 2 3 4} |
| 65 | do_test attach-1.6 { |
| 66 | catchsql { |
| 67 | SELECT * FROM t2; |
| 68 | } |
| 69 | } {1 {no such table: t2}} |
| 70 | do_test attach-1.7 { |
| 71 | catchsql { |
| 72 | SELECT * FROM two.t2; |
| 73 | } |
| 74 | } {1 {no such table: two.t2}} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 75 | do_test attach-1.8 { |
| 76 | catchsql { |
| 77 | ATTACH DATABASE 'test3.db' AS three; |
| 78 | } |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 79 | } {0 {}} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 80 | do_test attach-1.9 { |
| 81 | catchsql { |
| 82 | SELECT * FROM three.sqlite_master; |
| 83 | } |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 84 | } {0 {}} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 85 | do_test attach-1.10 { |
| 86 | catchsql { |
drh | ae8b361 | 2005-03-15 02:04:12 +0000 | [diff] [blame] | 87 | DETACH DATABASE [three]; |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 88 | } |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 89 | } {0 {}} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 90 | do_test attach-1.11 { |
| 91 | execsql { |
| 92 | ATTACH 'test.db' AS db2; |
| 93 | ATTACH 'test.db' AS db3; |
| 94 | ATTACH 'test.db' AS db4; |
| 95 | ATTACH 'test.db' AS db5; |
| 96 | ATTACH 'test.db' AS db6; |
| 97 | ATTACH 'test.db' AS db7; |
| 98 | ATTACH 'test.db' AS db8; |
| 99 | ATTACH 'test.db' AS db9; |
| 100 | } |
| 101 | } {} |
drh | b8ec209 | 2003-06-04 15:53:02 +0000 | [diff] [blame] | 102 | proc db_list {db} { |
| 103 | set list {} |
| 104 | foreach {idx name file} [execsql {PRAGMA database_list} $db] { |
| 105 | lappend list $idx $name |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 106 | } |
drh | b8ec209 | 2003-06-04 15:53:02 +0000 | [diff] [blame] | 107 | return $list |
| 108 | } |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 109 | ifcapable schema_pragmas { |
drh | b8ec209 | 2003-06-04 15:53:02 +0000 | [diff] [blame] | 110 | do_test attach-1.11b { |
| 111 | db_list db |
drh | 34f4732 | 2004-08-18 15:58:22 +0000 | [diff] [blame] | 112 | } {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9} |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 113 | } ;# ifcapable schema_pragmas |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 114 | do_test attach-1.12 { |
| 115 | catchsql { |
| 116 | ATTACH 'test.db' as db2; |
| 117 | } |
| 118 | } {1 {database db2 is already in use}} |
drh | 69544ec | 2008-02-06 14:11:34 +0000 | [diff] [blame] | 119 | do_test attach-1.12.2 { |
| 120 | db errorcode |
| 121 | } {1} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 122 | do_test attach-1.13 { |
| 123 | catchsql { |
| 124 | ATTACH 'test.db' as db5; |
| 125 | } |
| 126 | } {1 {database db5 is already in use}} |
| 127 | do_test attach-1.14 { |
| 128 | catchsql { |
| 129 | ATTACH 'test.db' as db9; |
| 130 | } |
| 131 | } {1 {database db9 is already in use}} |
| 132 | do_test attach-1.15 { |
| 133 | catchsql { |
| 134 | ATTACH 'test.db' as main; |
| 135 | } |
| 136 | } {1 {database main is already in use}} |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 137 | ifcapable tempdb { |
| 138 | do_test attach-1.16 { |
| 139 | catchsql { |
| 140 | ATTACH 'test.db' as temp; |
| 141 | } |
| 142 | } {1 {database temp is already in use}} |
| 143 | } |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 144 | do_test attach-1.17 { |
| 145 | catchsql { |
| 146 | ATTACH 'test.db' as MAIN; |
| 147 | } |
| 148 | } {1 {database MAIN is already in use}} |
| 149 | do_test attach-1.18 { |
| 150 | catchsql { |
| 151 | ATTACH 'test.db' as db10; |
| 152 | ATTACH 'test.db' as db11; |
| 153 | } |
| 154 | } {0 {}} |
drh | 01c7dc8 | 2011-03-23 18:22:34 +0000 | [diff] [blame] | 155 | if {$SQLITE_MAX_ATTACHED==10} { |
| 156 | do_test attach-1.19 { |
| 157 | catchsql { |
| 158 | ATTACH 'test.db' as db12; |
| 159 | } |
| 160 | } {1 {too many attached databases - max 10}} |
| 161 | do_test attach-1.19.1 { |
| 162 | db errorcode |
| 163 | } {1} |
| 164 | } |
drh | ed717fe | 2003-06-15 23:42:24 +0000 | [diff] [blame] | 165 | do_test attach-1.20.1 { |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 166 | execsql { |
| 167 | DETACH db5; |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 168 | } |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 169 | } {} |
| 170 | ifcapable schema_pragmas { |
| 171 | do_test attach-1.20.2 { |
drh | b8ec209 | 2003-06-04 15:53:02 +0000 | [diff] [blame] | 172 | db_list db |
drh | 34f4732 | 2004-08-18 15:58:22 +0000 | [diff] [blame] | 173 | } {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11} |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 174 | } ;# ifcapable schema_pragmas |
danielk1977 | a21c6b6 | 2005-01-24 10:25:59 +0000 | [diff] [blame] | 175 | integrity_check attach-1.20.3 |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 176 | ifcapable tempdb { |
| 177 | execsql {select * from sqlite_temp_master} |
| 178 | } |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 179 | do_test attach-1.21 { |
| 180 | catchsql { |
| 181 | ATTACH 'test.db' as db12; |
| 182 | } |
| 183 | } {0 {}} |
drh | 01c7dc8 | 2011-03-23 18:22:34 +0000 | [diff] [blame] | 184 | if {$SQLITE_MAX_ATTACHED==10} { |
| 185 | do_test attach-1.22 { |
| 186 | catchsql { |
| 187 | ATTACH 'test.db' as db13; |
| 188 | } |
| 189 | } {1 {too many attached databases - max 10}} |
| 190 | do_test attach-1.22.1 { |
| 191 | db errorcode |
| 192 | } {1} |
| 193 | } |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 194 | do_test attach-1.23 { |
| 195 | catchsql { |
drh | ae8b361 | 2005-03-15 02:04:12 +0000 | [diff] [blame] | 196 | DETACH "db14"; |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 197 | } |
| 198 | } {1 {no such database: db14}} |
| 199 | do_test attach-1.24 { |
| 200 | catchsql { |
| 201 | DETACH db12; |
| 202 | } |
| 203 | } {0 {}} |
| 204 | do_test attach-1.25 { |
| 205 | catchsql { |
| 206 | DETACH db12; |
| 207 | } |
| 208 | } {1 {no such database: db12}} |
| 209 | do_test attach-1.26 { |
| 210 | catchsql { |
| 211 | DETACH main; |
| 212 | } |
| 213 | } {1 {cannot detach database main}} |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 214 | |
| 215 | ifcapable tempdb { |
| 216 | do_test attach-1.27 { |
| 217 | catchsql { |
| 218 | DETACH Temp; |
| 219 | } |
| 220 | } {1 {cannot detach database Temp}} |
| 221 | } else { |
| 222 | do_test attach-1.27 { |
| 223 | catchsql { |
| 224 | DETACH Temp; |
| 225 | } |
| 226 | } {1 {no such database: Temp}} |
| 227 | } |
| 228 | |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 229 | do_test attach-1.28 { |
| 230 | catchsql { |
| 231 | DETACH db11; |
| 232 | DETACH db10; |
| 233 | DETACH db9; |
| 234 | DETACH db8; |
| 235 | DETACH db7; |
| 236 | DETACH db6; |
| 237 | DETACH db4; |
| 238 | DETACH db3; |
| 239 | DETACH db2; |
| 240 | } |
| 241 | } {0 {}} |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 242 | ifcapable schema_pragmas { |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 243 | ifcapable tempdb { |
| 244 | do_test attach-1.29 { |
| 245 | db_list db |
| 246 | } {0 main 1 temp} |
| 247 | } else { |
| 248 | do_test attach-1.29 { |
| 249 | db_list db |
| 250 | } {0 main} |
| 251 | } |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 252 | } ;# ifcapable schema_pragmas |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 253 | |
drh | b7f9164 | 2004-10-31 02:22:47 +0000 | [diff] [blame] | 254 | ifcapable {trigger} { # Only do the following tests if triggers are enabled |
drh | a69d916 | 2003-04-17 22:57:53 +0000 | [diff] [blame] | 255 | do_test attach-2.1 { |
| 256 | execsql { |
| 257 | CREATE TABLE tx(x1,x2,y1,y2); |
| 258 | CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN |
| 259 | INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y); |
| 260 | END; |
| 261 | SELECT * FROM tx; |
| 262 | } db2; |
| 263 | } {} |
| 264 | do_test attach-2.2 { |
| 265 | execsql { |
| 266 | UPDATE t2 SET x=x+10; |
| 267 | SELECT * FROM tx; |
| 268 | } db2; |
| 269 | } {1 11 x x 2 12 y y} |
| 270 | do_test attach-2.3 { |
| 271 | execsql { |
| 272 | CREATE TABLE tx(x1,x2,y1,y2); |
| 273 | SELECT * FROM tx; |
| 274 | } |
| 275 | } {} |
| 276 | do_test attach-2.4 { |
| 277 | execsql { |
| 278 | ATTACH 'test2.db' AS db2; |
| 279 | } |
| 280 | } {} |
| 281 | do_test attach-2.5 { |
| 282 | execsql { |
| 283 | UPDATE db2.t2 SET x=x+10; |
| 284 | SELECT * FROM db2.tx; |
| 285 | } |
| 286 | } {1 11 x x 2 12 y y 11 21 x x 12 22 y y} |
| 287 | do_test attach-2.6 { |
| 288 | execsql { |
| 289 | SELECT * FROM main.tx; |
| 290 | } |
| 291 | } {} |
drh | 8bf8dc9 | 2003-05-17 17:35:10 +0000 | [diff] [blame] | 292 | do_test attach-2.7 { |
| 293 | execsql { |
| 294 | SELECT type, name, tbl_name FROM db2.sqlite_master; |
| 295 | } |
| 296 | } {table t2 t2 table tx tx trigger r1 t2} |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 297 | |
| 298 | ifcapable schema_pragmas&&tempdb { |
| 299 | do_test attach-2.8 { |
| 300 | db_list db |
| 301 | } {0 main 1 temp 2 db2} |
| 302 | } ;# ifcapable schema_pragmas&&tempdb |
| 303 | ifcapable schema_pragmas&&!tempdb { |
| 304 | do_test attach-2.8 { |
| 305 | db_list db |
| 306 | } {0 main 2 db2} |
| 307 | } ;# ifcapable schema_pragmas&&!tempdb |
| 308 | |
drh | 8bf8dc9 | 2003-05-17 17:35:10 +0000 | [diff] [blame] | 309 | do_test attach-2.9 { |
| 310 | execsql { |
| 311 | CREATE INDEX i2 ON t2(x); |
| 312 | SELECT * FROM t2 WHERE x>5; |
| 313 | } db2 |
| 314 | } {21 x 22 y} |
| 315 | do_test attach-2.10 { |
| 316 | execsql { |
| 317 | SELECT type, name, tbl_name FROM sqlite_master; |
| 318 | } db2 |
| 319 | } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
drh | b5f70c2 | 2004-02-14 01:39:50 +0000 | [diff] [blame] | 320 | #do_test attach-2.11 { |
| 321 | # catchsql { |
| 322 | # SELECT * FROM t2 WHERE x>5; |
| 323 | # } |
| 324 | #} {1 {database schema has changed}} |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 325 | ifcapable schema_pragmas { |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 326 | ifcapable tempdb { |
| 327 | do_test attach-2.12 { |
| 328 | db_list db |
| 329 | } {0 main 1 temp 2 db2} |
| 330 | } else { |
| 331 | do_test attach-2.12 { |
| 332 | db_list db |
| 333 | } {0 main 2 db2} |
| 334 | } |
danielk1977 | 27188fb | 2004-11-23 10:13:03 +0000 | [diff] [blame] | 335 | } ;# ifcapable schema_pragmas |
drh | 8bf8dc9 | 2003-05-17 17:35:10 +0000 | [diff] [blame] | 336 | do_test attach-2.13 { |
| 337 | catchsql { |
| 338 | SELECT * FROM t2 WHERE x>5; |
| 339 | } |
| 340 | } {0 {21 x 22 y}} |
| 341 | do_test attach-2.14 { |
| 342 | execsql { |
| 343 | SELECT type, name, tbl_name FROM sqlite_master; |
| 344 | } |
| 345 | } {table t1 t1 table tx tx} |
| 346 | do_test attach-2.15 { |
| 347 | execsql { |
| 348 | SELECT type, name, tbl_name FROM db2.sqlite_master; |
| 349 | } |
| 350 | } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
| 351 | do_test attach-2.16 { |
| 352 | db close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 353 | sqlite3 db test.db |
drh | 8bf8dc9 | 2003-05-17 17:35:10 +0000 | [diff] [blame] | 354 | execsql { |
| 355 | ATTACH 'test2.db' AS db2; |
| 356 | SELECT type, name, tbl_name FROM db2.sqlite_master; |
| 357 | } |
| 358 | } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
drh | b7f9164 | 2004-10-31 02:22:47 +0000 | [diff] [blame] | 359 | } ;# End of ifcapable {trigger} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 360 | |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 361 | do_test attach-3.1 { |
| 362 | db close |
| 363 | db2 close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 364 | sqlite3 db test.db |
| 365 | sqlite3 db2 test2.db |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 366 | execsql { |
| 367 | SELECT * FROM t1 |
| 368 | } |
| 369 | } {1 2 3 4} |
drh | b7f9164 | 2004-10-31 02:22:47 +0000 | [diff] [blame] | 370 | |
| 371 | # If we are testing a version of the code that lacks trigger support, |
| 372 | # adjust the database contents so that they are the same if triggers |
| 373 | # had been enabled. |
| 374 | ifcapable {!trigger} { |
| 375 | db2 eval { |
| 376 | DELETE FROM t2; |
| 377 | INSERT INTO t2 VALUES(21, 'x'); |
| 378 | INSERT INTO t2 VALUES(22, 'y'); |
| 379 | CREATE TABLE tx(x1,x2,y1,y2); |
| 380 | INSERT INTO tx VALUES(1, 11, 'x', 'x'); |
| 381 | INSERT INTO tx VALUES(2, 12, 'y', 'y'); |
| 382 | INSERT INTO tx VALUES(11, 21, 'x', 'x'); |
| 383 | INSERT INTO tx VALUES(12, 22, 'y', 'y'); |
| 384 | CREATE INDEX i2 ON t2(x); |
| 385 | } |
| 386 | } |
| 387 | |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 388 | do_test attach-3.2 { |
| 389 | catchsql { |
| 390 | SELECT * FROM t2 |
| 391 | } |
| 392 | } {1 {no such table: t2}} |
| 393 | do_test attach-3.3 { |
| 394 | catchsql { |
| 395 | ATTACH DATABASE 'test2.db' AS db2; |
| 396 | SELECT * FROM t2 |
| 397 | } |
| 398 | } {0 {21 x 22 y}} |
| 399 | |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 400 | # Even though 'db' has started a transaction, it should not yet have |
| 401 | # a lock on test2.db so 'db2' should be readable. |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 402 | do_test attach-3.4 { |
| 403 | execsql BEGIN |
| 404 | catchsql { |
| 405 | SELECT * FROM t2; |
| 406 | } db2; |
| 407 | } {0 {21 x 22 y}} |
| 408 | |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 409 | # Reading from test2.db from db within a transaction should not |
| 410 | # prevent test2.db from being read by db2. |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 411 | do_test attach-3.5 { |
| 412 | execsql {SELECT * FROM t2} |
| 413 | catchsql { |
| 414 | SELECT * FROM t2; |
| 415 | } db2; |
| 416 | } {0 {21 x 22 y}} |
| 417 | |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 418 | # Making a change to test2.db through db causes test2.db to get |
| 419 | # a reserved lock. It should still be accessible through db2. |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 420 | do_test attach-3.6 { |
| 421 | execsql { |
| 422 | UPDATE t2 SET x=x+1 WHERE x=50; |
| 423 | } |
| 424 | catchsql { |
| 425 | SELECT * FROM t2; |
| 426 | } db2; |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 427 | } {0 {21 x 22 y}} |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 428 | |
| 429 | do_test attach-3.7 { |
| 430 | execsql ROLLBACK |
| 431 | execsql {SELECT * FROM t2} db2 |
| 432 | } {21 x 22 y} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 433 | |
| 434 | # Start transactions on both db and db2. Once again, just because |
| 435 | # we make a change to test2.db using db2, only a RESERVED lock is |
| 436 | # obtained, so test2.db should still be readable using db. |
| 437 | # |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 438 | do_test attach-3.8 { |
| 439 | execsql BEGIN |
| 440 | execsql BEGIN db2 |
danielk1977 | 1d850a7 | 2004-05-31 08:26:49 +0000 | [diff] [blame] | 441 | execsql {UPDATE t2 SET x=0 WHERE 0} db2 |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 442 | catchsql {SELECT * FROM t2} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 443 | } {0 {21 x 22 y}} |
| 444 | |
| 445 | # It is also still accessible from db2. |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 446 | do_test attach-3.9 { |
| 447 | catchsql {SELECT * FROM t2} db2 |
| 448 | } {0 {21 x 22 y}} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 449 | |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 450 | do_test attach-3.10 { |
| 451 | execsql {SELECT * FROM t1} |
| 452 | } {1 2 3 4} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 453 | |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 454 | do_test attach-3.11 { |
| 455 | catchsql {UPDATE t1 SET a=a+1} |
| 456 | } {0 {}} |
| 457 | do_test attach-3.12 { |
| 458 | execsql {SELECT * FROM t1} |
| 459 | } {2 2 4 4} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 460 | |
| 461 | # db2 has a RESERVED lock on test2.db, so db cannot write to any tables |
| 462 | # in test2.db. |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 463 | do_test attach-3.13 { |
| 464 | catchsql {UPDATE t2 SET x=x+1 WHERE x=50} |
| 465 | } {1 {database is locked}} |
danielk1977 | 1d850a7 | 2004-05-31 08:26:49 +0000 | [diff] [blame] | 466 | |
| 467 | # Change for version 3. Transaction is no longer rolled back |
| 468 | # for a locked database. |
| 469 | execsql {ROLLBACK} |
| 470 | |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 471 | # db is able to reread its schema because db2 still only holds a |
| 472 | # reserved lock. |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 473 | do_test attach-3.14 { |
drh | 8ef83ff | 2004-02-12 15:31:21 +0000 | [diff] [blame] | 474 | catchsql {SELECT * FROM t1} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 475 | } {0 {1 2 3 4}} |
drh | 8ef83ff | 2004-02-12 15:31:21 +0000 | [diff] [blame] | 476 | do_test attach-3.15 { |
| 477 | execsql COMMIT db2 |
drh | 6b86111 | 2003-05-17 19:23:51 +0000 | [diff] [blame] | 478 | execsql {SELECT * FROM t1} |
| 479 | } {1 2 3 4} |
| 480 | |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 481 | # Ticket #323 |
| 482 | do_test attach-4.1 { |
| 483 | execsql {DETACH db2} |
| 484 | db2 close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 485 | sqlite3 db2 test2.db |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 486 | execsql { |
| 487 | CREATE TABLE t3(x,y); |
| 488 | CREATE UNIQUE INDEX t3i1 ON t3(x); |
| 489 | INSERT INTO t3 VALUES(1,2); |
| 490 | SELECT * FROM t3; |
| 491 | } db2; |
| 492 | } {1 2} |
| 493 | do_test attach-4.2 { |
| 494 | execsql { |
| 495 | CREATE TABLE t3(a,b); |
| 496 | CREATE UNIQUE INDEX t3i1b ON t3(a); |
| 497 | INSERT INTO t3 VALUES(9,10); |
| 498 | SELECT * FROM t3; |
| 499 | } |
| 500 | } {9 10} |
| 501 | do_test attach-4.3 { |
| 502 | execsql { |
| 503 | ATTACH DATABASE 'test2.db' AS db2; |
| 504 | SELECT * FROM db2.t3; |
| 505 | } |
| 506 | } {1 2} |
| 507 | do_test attach-4.4 { |
| 508 | execsql { |
| 509 | SELECT * FROM main.t3; |
| 510 | } |
| 511 | } {9 10} |
| 512 | do_test attach-4.5 { |
| 513 | execsql { |
| 514 | INSERT INTO db2.t3 VALUES(9,10); |
| 515 | SELECT * FROM db2.t3; |
| 516 | } |
| 517 | } {1 2 9 10} |
drh | 798da52 | 2004-11-04 04:42:28 +0000 | [diff] [blame] | 518 | execsql { |
| 519 | DETACH db2; |
| 520 | } |
drh | b7f9164 | 2004-10-31 02:22:47 +0000 | [diff] [blame] | 521 | ifcapable {trigger} { |
| 522 | do_test attach-4.6 { |
| 523 | execsql { |
drh | b7f9164 | 2004-10-31 02:22:47 +0000 | [diff] [blame] | 524 | CREATE TABLE t4(x); |
| 525 | CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN |
| 526 | INSERT INTO t4 VALUES('db2.' || NEW.x); |
| 527 | END; |
| 528 | INSERT INTO t3 VALUES(6,7); |
| 529 | SELECT * FROM t4; |
| 530 | } db2 |
| 531 | } {db2.6} |
| 532 | do_test attach-4.7 { |
| 533 | execsql { |
| 534 | CREATE TABLE t4(y); |
| 535 | CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN |
| 536 | INSERT INTO t4 VALUES('main.' || NEW.a); |
| 537 | END; |
| 538 | INSERT INTO main.t3 VALUES(11,12); |
| 539 | SELECT * FROM main.t4; |
| 540 | } |
| 541 | } {main.11} |
| 542 | } |
drh | 798da52 | 2004-11-04 04:42:28 +0000 | [diff] [blame] | 543 | ifcapable {!trigger} { |
| 544 | # When we do not have trigger support, set up the table like they |
| 545 | # would have been had triggers been there. The tests that follow need |
| 546 | # this setup. |
| 547 | execsql { |
| 548 | CREATE TABLE t4(x); |
| 549 | INSERT INTO t3 VALUES(6,7); |
| 550 | INSERT INTO t4 VALUES('db2.6'); |
| 551 | INSERT INTO t4 VALUES('db2.13'); |
| 552 | } db2 |
| 553 | execsql { |
| 554 | CREATE TABLE t4(y); |
| 555 | INSERT INTO main.t3 VALUES(11,12); |
| 556 | INSERT INTO t4 VALUES('main.11'); |
| 557 | } |
| 558 | } |
| 559 | |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 560 | |
| 561 | # This one is tricky. On the UNION ALL select, we have to make sure |
| 562 | # the schema for both main and db2 is valid before starting to execute |
| 563 | # the first query of the UNION ALL. If we wait to test the validity of |
| 564 | # the schema for main until after the first query has run, that test will |
| 565 | # fail and the query will abort but we will have already output some |
| 566 | # results. When the query is retried, the results will be repeated. |
| 567 | # |
danielk1977 | 27c7743 | 2004-11-22 13:35:41 +0000 | [diff] [blame] | 568 | ifcapable compound { |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 569 | do_test attach-4.8 { |
| 570 | execsql { |
| 571 | ATTACH DATABASE 'test2.db' AS db2; |
| 572 | INSERT INTO db2.t3 VALUES(13,14); |
| 573 | SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; |
| 574 | } |
| 575 | } {db2.6 db2.13 main.11} |
drh | 8024205 | 2004-06-09 00:48:12 +0000 | [diff] [blame] | 576 | |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 577 | do_test attach-4.9 { |
drh | 798da52 | 2004-11-04 04:42:28 +0000 | [diff] [blame] | 578 | ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 579 | execsql { |
| 580 | INSERT INTO main.t3 VALUES(15,16); |
| 581 | SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; |
| 582 | } |
| 583 | } {db2.6 db2.13 main.11 main.15} |
danielk1977 | 27c7743 | 2004-11-22 13:35:41 +0000 | [diff] [blame] | 584 | } ;# ifcapable compound |
| 585 | |
| 586 | ifcapable !compound { |
| 587 | ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} |
| 588 | execsql { |
| 589 | ATTACH DATABASE 'test2.db' AS db2; |
| 590 | INSERT INTO db2.t3 VALUES(13,14); |
| 591 | INSERT INTO main.t3 VALUES(15,16); |
| 592 | } |
| 593 | } ;# ifcapable !compound |
danielk1977 | 0fa8ddb | 2004-11-22 08:43:32 +0000 | [diff] [blame] | 594 | |
| 595 | ifcapable view { |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 596 | do_test attach-4.10 { |
| 597 | execsql { |
| 598 | DETACH DATABASE db2; |
| 599 | } |
| 600 | execsql { |
| 601 | CREATE VIEW v3 AS SELECT x*100+y FROM t3; |
| 602 | SELECT * FROM v3; |
| 603 | } db2 |
| 604 | } {102 910 607 1314} |
| 605 | do_test attach-4.11 { |
| 606 | execsql { |
| 607 | CREATE VIEW v3 AS SELECT a*100+b FROM t3; |
| 608 | SELECT * FROM v3; |
| 609 | } |
| 610 | } {910 1112 1516} |
| 611 | do_test attach-4.12 { |
| 612 | execsql { |
| 613 | ATTACH DATABASE 'test2.db' AS db2; |
| 614 | SELECT * FROM db2.v3; |
| 615 | } |
| 616 | } {102 910 607 1314} |
| 617 | do_test attach-4.13 { |
| 618 | execsql { |
| 619 | SELECT * FROM main.v3; |
| 620 | } |
| 621 | } {910 1112 1516} |
danielk1977 | 0fa8ddb | 2004-11-22 08:43:32 +0000 | [diff] [blame] | 622 | } ;# ifcapable view |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 623 | |
drh | 4312db5 | 2003-06-03 01:47:11 +0000 | [diff] [blame] | 624 | # Tests for the sqliteFix...() routines in attach.c |
| 625 | # |
drh | 798da52 | 2004-11-04 04:42:28 +0000 | [diff] [blame] | 626 | ifcapable {trigger} { |
drh | 4312db5 | 2003-06-03 01:47:11 +0000 | [diff] [blame] | 627 | do_test attach-5.1 { |
| 628 | db close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 629 | sqlite3 db test.db |
drh | 9cb733c | 2003-07-18 01:25:34 +0000 | [diff] [blame] | 630 | db2 close |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 631 | forcedelete test2.db |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 632 | sqlite3 db2 test2.db |
drh | 4312db5 | 2003-06-03 01:47:11 +0000 | [diff] [blame] | 633 | catchsql { |
| 634 | ATTACH DATABASE 'test.db' AS orig; |
drh | 86dac2b | 2006-05-25 12:17:31 +0000 | [diff] [blame] | 635 | CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN |
drh | 4312db5 | 2003-06-03 01:47:11 +0000 | [diff] [blame] | 636 | SELECT 'no-op'; |
| 637 | END; |
| 638 | } db2 |
danielk1977 | ef2cb63 | 2004-05-29 02:37:19 +0000 | [diff] [blame] | 639 | } {1 {trigger r1 cannot reference objects in database orig}} |
drh | 4312db5 | 2003-06-03 01:47:11 +0000 | [diff] [blame] | 640 | do_test attach-5.2 { |
| 641 | catchsql { |
| 642 | CREATE TABLE t5(x,y); |
| 643 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 644 | SELECT 'no-op'; |
| 645 | END; |
| 646 | } db2 |
| 647 | } {0 {}} |
| 648 | do_test attach-5.3 { |
| 649 | catchsql { |
| 650 | DROP TRIGGER r5; |
| 651 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 652 | SELECT 'no-op' FROM orig.t1; |
| 653 | END; |
| 654 | } db2 |
| 655 | } {1 {trigger r5 cannot reference objects in database orig}} |
danielk1977 | 53c0f74 | 2005-03-29 03:10:59 +0000 | [diff] [blame] | 656 | ifcapable tempdb { |
| 657 | do_test attach-5.4 { |
| 658 | catchsql { |
| 659 | CREATE TEMP TABLE t6(p,q,r); |
| 660 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 661 | SELECT 'no-op' FROM temp.t6; |
| 662 | END; |
| 663 | } db2 |
| 664 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 665 | } |
danielk1977 | e61b9f4 | 2005-01-21 04:25:47 +0000 | [diff] [blame] | 666 | ifcapable subquery { |
| 667 | do_test attach-5.5 { |
| 668 | catchsql { |
| 669 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 670 | SELECT 'no-op' || (SELECT * FROM temp.t6); |
| 671 | END; |
| 672 | } db2 |
| 673 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 674 | do_test attach-5.6 { |
| 675 | catchsql { |
| 676 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 677 | SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); |
| 678 | END; |
| 679 | } db2 |
| 680 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 681 | do_test attach-5.7 { |
| 682 | catchsql { |
| 683 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 684 | SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6); |
| 685 | END; |
| 686 | } db2 |
| 687 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 688 | do_test attach-5.7 { |
| 689 | catchsql { |
| 690 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 691 | SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1; |
| 692 | END; |
| 693 | } db2 |
| 694 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 695 | do_test attach-5.8 { |
| 696 | catchsql { |
| 697 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 698 | INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5); |
| 699 | END; |
| 700 | } db2 |
| 701 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 702 | do_test attach-5.9 { |
| 703 | catchsql { |
| 704 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| 705 | DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); |
| 706 | END; |
| 707 | } db2 |
| 708 | } {1 {trigger r5 cannot reference objects in database temp}} |
| 709 | } ;# endif subquery |
drh | 798da52 | 2004-11-04 04:42:28 +0000 | [diff] [blame] | 710 | } ;# endif trigger |
drh | 4312db5 | 2003-06-03 01:47:11 +0000 | [diff] [blame] | 711 | |
| 712 | # Check to make sure we get a sensible error if unable to open |
| 713 | # the file that we are trying to attach. |
| 714 | # |
| 715 | do_test attach-6.1 { |
| 716 | catchsql { |
| 717 | ATTACH DATABASE 'no-such-file' AS nosuch; |
| 718 | } |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 719 | } {0 {}} |
drh | bc2bca0 | 2003-06-04 12:31:53 +0000 | [diff] [blame] | 720 | if {$tcl_platform(platform)=="unix"} { |
| 721 | do_test attach-6.2 { |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 722 | sqlite3 dbx cannot-read |
drh | bc2bca0 | 2003-06-04 12:31:53 +0000 | [diff] [blame] | 723 | dbx eval {CREATE TABLE t1(a,b,c)} |
| 724 | dbx close |
| 725 | file attributes cannot-read -permission 0000 |
drh | 97ba4c9 | 2005-03-02 05:18:57 +0000 | [diff] [blame] | 726 | if {[file writable cannot-read]} { |
| 727 | puts "\n**** Tests do not work when run as root ****" |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 728 | forcedelete cannot-read |
drh | 97ba4c9 | 2005-03-02 05:18:57 +0000 | [diff] [blame] | 729 | exit 1 |
| 730 | } |
drh | bc2bca0 | 2003-06-04 12:31:53 +0000 | [diff] [blame] | 731 | catchsql { |
| 732 | ATTACH DATABASE 'cannot-read' AS noread; |
| 733 | } |
| 734 | } {1 {unable to open database: cannot-read}} |
drh | 69544ec | 2008-02-06 14:11:34 +0000 | [diff] [blame] | 735 | do_test attach-6.2.2 { |
| 736 | db errorcode |
| 737 | } {14} |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 738 | forcedelete cannot-read |
drh | bc2bca0 | 2003-06-04 12:31:53 +0000 | [diff] [blame] | 739 | } |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 740 | |
danielk1977 | 576ec6b | 2005-01-21 11:55:25 +0000 | [diff] [blame] | 741 | # Check the error message if we try to access a database that has |
| 742 | # not been attached. |
| 743 | do_test attach-6.3 { |
| 744 | catchsql { |
| 745 | CREATE TABLE no_such_db.t1(a, b, c); |
| 746 | } |
| 747 | } {1 {unknown database no_such_db}} |
drh | a73af53 | 2003-04-05 16:56:28 +0000 | [diff] [blame] | 748 | for {set i 2} {$i<=15} {incr i} { |
| 749 | catch {db$i close} |
| 750 | } |
drh | 02f9f6b | 2004-05-31 18:21:54 +0000 | [diff] [blame] | 751 | db close |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 752 | forcedelete test2.db |
| 753 | forcedelete no-such-file |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 754 | |
danielk1977 | 4152e67 | 2007-09-12 17:01:45 +0000 | [diff] [blame] | 755 | ifcapable subquery { |
| 756 | do_test attach-7.1 { |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 757 | forcedelete test.db test.db-journal |
danielk1977 | 4152e67 | 2007-09-12 17:01:45 +0000 | [diff] [blame] | 758 | sqlite3 db test.db |
| 759 | catchsql { |
| 760 | DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY |
| 761 | REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL ) |
| 762 | } |
drh | fd773cf | 2009-05-29 14:39:07 +0000 | [diff] [blame] | 763 | } {1 {no such table: AAAAAA}} |
danielk1977 | 4152e67 | 2007-09-12 17:01:45 +0000 | [diff] [blame] | 764 | } |
drh | 69544ec | 2008-02-06 14:11:34 +0000 | [diff] [blame] | 765 | |
| 766 | # Create a malformed file (a file that is not a valid database) |
| 767 | # and try to attach it |
| 768 | # |
| 769 | do_test attach-8.1 { |
| 770 | set fd [open test2.db w] |
| 771 | puts $fd "This file is not a valid SQLite database" |
| 772 | close $fd |
| 773 | catchsql { |
| 774 | ATTACH 'test2.db' AS t2; |
| 775 | } |
| 776 | } {1 {file is encrypted or is not a database}} |
| 777 | do_test attach-8.2 { |
| 778 | db errorcode |
| 779 | } {26} |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 780 | forcedelete test2.db |
drh | 69544ec | 2008-02-06 14:11:34 +0000 | [diff] [blame] | 781 | do_test attach-8.3 { |
| 782 | sqlite3 db2 test2.db |
| 783 | db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE} |
| 784 | catchsql { |
| 785 | ATTACH 'test2.db' AS t2; |
| 786 | } |
| 787 | } {1 {database is locked}} |
| 788 | do_test attach-8.4 { |
| 789 | db errorcode |
| 790 | } {5} |
| 791 | db2 close |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 792 | forcedelete test2.db |
drh | 69544ec | 2008-02-06 14:11:34 +0000 | [diff] [blame] | 793 | |
dan | ac88760 | 2009-11-20 10:18:06 +0000 | [diff] [blame] | 794 | # Test that it is possible to attach the same database more than |
| 795 | # once when not in shared-cache mode. That this is not possible in |
| 796 | # shared-cache mode is tested in shared7.test. |
| 797 | do_test attach-9.1 { |
mistachkin | fda06be | 2011-08-02 00:57:34 +0000 | [diff] [blame] | 798 | forcedelete test4.db |
dan | ac88760 | 2009-11-20 10:18:06 +0000 | [diff] [blame] | 799 | execsql { |
| 800 | ATTACH 'test4.db' AS aux1; |
| 801 | CREATE TABLE aux1.t1(a, b); |
| 802 | INSERT INTO aux1.t1 VALUES(1, 2); |
| 803 | ATTACH 'test4.db' AS aux2; |
| 804 | SELECT * FROM aux2.t1; |
| 805 | } |
| 806 | } {1 2} |
| 807 | do_test attach-9.2 { |
dan | ac88760 | 2009-11-20 10:18:06 +0000 | [diff] [blame] | 808 | catchsql { |
| 809 | BEGIN; |
| 810 | INSERT INTO aux1.t1 VALUES(3, 4); |
| 811 | INSERT INTO aux2.t1 VALUES(5, 6); |
| 812 | } |
| 813 | } {1 {database is locked}} |
| 814 | do_test attach-9.3 { |
dan | ac88760 | 2009-11-20 10:18:06 +0000 | [diff] [blame] | 815 | execsql { |
| 816 | COMMIT; |
| 817 | SELECT * FROM aux2.t1; |
| 818 | } |
| 819 | } {1 2 3 4} |
| 820 | |
drh | 4413c43 | 2009-12-08 13:44:21 +0000 | [diff] [blame] | 821 | # Ticket [abe728bbc311d81334dae9762f0db87c07a98f79]. |
| 822 | # Multi-database commit on an attached TEMP database. |
| 823 | # |
| 824 | do_test attach-10.1 { |
| 825 | execsql { |
| 826 | ATTACH '' AS noname; |
| 827 | ATTACH ':memory:' AS inmem; |
| 828 | BEGIN; |
| 829 | CREATE TABLE noname.noname(x); |
| 830 | CREATE TABLE inmem.inmem(y); |
| 831 | CREATE TABLE main.main(z); |
| 832 | COMMIT; |
| 833 | SELECT name FROM noname.sqlite_master; |
| 834 | SELECT name FROM inmem.sqlite_master; |
| 835 | } |
| 836 | } {noname inmem} |
| 837 | do_test attach-10.2 { |
| 838 | lrange [execsql { |
| 839 | PRAGMA database_list; |
| 840 | }] 9 end |
| 841 | } {4 noname {} 5 inmem {}} |
dan | bdd9af0 | 2010-11-18 16:14:24 +0000 | [diff] [blame] | 842 | |
drh | 2d45834 | 2003-04-05 03:42:26 +0000 | [diff] [blame] | 843 | finish_test |