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