Annotation of embedaddon/sqlite3/test/attach3.test, revision 1.1
1.1 ! misho 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:
! 18: set testdir [file dirname $argv0]
! 19: source $testdir/tester.tcl
! 20:
! 21: ifcapable !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.
! 29: catchsql { pragma recursive_triggers = off }
! 30:
! 31: # Create tables t1 and t2 in the main database
! 32: execsql {
! 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
! 38: forcedelete test2.db
! 39: forcedelete test2.db-journal
! 40: sqlite3 db2 test2.db
! 41: execsql {
! 42: CREATE TABLE t1(a, b);
! 43: CREATE TABLE t2(c, d);
! 44: } db2
! 45: db2 close
! 46:
! 47: # Create a table in the auxilary database.
! 48: do_test attach3-1.1 {
! 49: execsql {
! 50: ATTACH 'test2.db' AS aux;
! 51: }
! 52: } {}
! 53: do_test attach3-1.2 {
! 54: execsql {
! 55: CREATE TABLE aux.t3(e, f);
! 56: }
! 57: } {}
! 58: do_test attach3-1.3 {
! 59: execsql {
! 60: SELECT * FROM sqlite_master WHERE name = 't3';
! 61: }
! 62: } {}
! 63: do_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)}"
! 68: do_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.
! 76: do_test attach3-2.1 {
! 77: execsql {
! 78: CREATE INDEX aux.i1 on t3(e);
! 79: }
! 80: } {}
! 81: do_test attach3-2.2 {
! 82: execsql {
! 83: SELECT * FROM sqlite_master WHERE name = 'i1';
! 84: }
! 85: } {}
! 86: do_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.
! 93: do_test attach3-3.1 {
! 94: execsql {
! 95: DROP INDEX aux.i1;
! 96: SELECT * FROM aux.sqlite_master WHERE name = 'i1';
! 97: }
! 98: } {}
! 99: do_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)}"
! 105: do_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.
! 113: do_test attach3-4.1 {
! 114: execsql {
! 115: DROP TABLE aux.t1;
! 116: SELECT name FROM aux.sqlite_master;
! 117: }
! 118: } {t2 t3}
! 119: do_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}
! 126: do_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.
! 134: ifcapable view {
! 135: do_test attach3-5.1 {
! 136: execsql {
! 137: CREATE VIEW aux.v1 AS SELECT * FROM t3;
! 138: }
! 139: } {}
! 140: do_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}}
! 145: do_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
! 153: do_test attach3-6.1 {
! 154: execsql {
! 155: DROP VIEW aux.v1;
! 156: }
! 157: } {}
! 158: do_test attach3-6.2 {
! 159: execsql {
! 160: SELECT * FROM aux.sqlite_master WHERE name = 'v1';
! 161: }
! 162: } {}
! 163: } ;# ifcapable view
! 164:
! 165: ifcapable {trigger} {
! 166: # Create a trigger in the auxilary database.
! 167: do_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: } {}
! 174: do_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}
! 181: do_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
! 190: do_test attach3-8.1 {
! 191: execsql {
! 192: DROP TRIGGER aux.tr1;
! 193: }
! 194: } {}
! 195: do_test attach3-8.2 {
! 196: execsql {
! 197: SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
! 198: }
! 199: } {}
! 200:
! 201: ifcapable 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
! 229: do_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: #
! 238: do_test attach3-11.0 {
! 239: catchsql {
! 240: ATTACH DATABASE '/nodir/nofile.x' AS notadb;
! 241: }
! 242: } {1 {unable to open database: /nodir/nofile.x}}
! 243: do_test attach3-11.1 {
! 244: catchsql {
! 245: ATTACH DATABASE ':memory:' AS notadb;
! 246: }
! 247: } {0 {}}
! 248: do_test attach3-11.2 {
! 249: catchsql {
! 250: DETACH DATABASE notadb;
! 251: }
! 252: } {0 {}}
! 253:
! 254: # Return a list of attached databases
! 255: #
! 256: proc 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:
! 265: ifcapable schema_pragmas&&tempdb {
! 266:
! 267: ifcapable !trigger {
! 268: execsql {create temp table dummy(dummy)}
! 269: }
! 270:
! 271: # Ticket #1825
! 272: #
! 273: do_test attach3-12.1 {
! 274: db_list
! 275: } {main temp aux}
! 276: do_test attach3-12.2 {
! 277: execsql {
! 278: ATTACH DATABASE ? AS ?
! 279: }
! 280: db_list
! 281: } {main temp aux {}}
! 282: do_test attach3-12.3 {
! 283: execsql {
! 284: DETACH aux
! 285: }
! 286: db_list
! 287: } {main temp {}}
! 288: do_test attach3-12.4 {
! 289: execsql {
! 290: DETACH ?
! 291: }
! 292: db_list
! 293: } {main temp}
! 294: do_test attach3-12.5 {
! 295: execsql {
! 296: ATTACH DATABASE '' AS ''
! 297: }
! 298: db_list
! 299: } {main temp {}}
! 300: do_test attach3-12.6 {
! 301: execsql {
! 302: DETACH ''
! 303: }
! 304: db_list
! 305: } {main temp}
! 306: do_test attach3-12.7 {
! 307: execsql {
! 308: ATTACH DATABASE '' AS ?
! 309: }
! 310: db_list
! 311: } {main temp {}}
! 312: do_test attach3-12.8 {
! 313: execsql {
! 314: DETACH ''
! 315: }
! 316: db_list
! 317: } {main temp}
! 318: do_test attach3-12.9 {
! 319: execsql {
! 320: ATTACH DATABASE '' AS NULL
! 321: }
! 322: db_list
! 323: } {main temp {}}
! 324: do_test attach3-12.10 {
! 325: breakpoint
! 326: execsql {
! 327: DETACH ?
! 328: }
! 329: db_list
! 330: } {main temp}
! 331: do_test attach3-12.11 {
! 332: catchsql {
! 333: DETACH NULL
! 334: }
! 335: } {1 {no such database: }}
! 336: do_test attach3-12.12 {
! 337: catchsql {
! 338: ATTACH null AS null;
! 339: ATTACH '' AS '';
! 340: }
! 341: } {1 {database is already in use}}
! 342: do_test attach3-12.13 {
! 343: db_list
! 344: } {main temp {}}
! 345: do_test attach3-12.14 {
! 346: execsql {
! 347: DETACH '';
! 348: }
! 349: db_list
! 350: } {main temp}
! 351:
! 352: } ;# ifcapable pragma
! 353:
! 354: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to attach3.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