Annotation of embedaddon/sqlite3/test/temptable.test, revision 1.1
1.1 ! misho 1: # 2001 October 7
! 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.
! 12: #
! 13: # This file implements tests for temporary tables and indices.
! 14: #
! 15: # $Id: temptable.test,v 1.21 2009/06/16 17:49:36 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: ifcapable !tempdb {
! 21: finish_test
! 22: return
! 23: }
! 24:
! 25: # Create an alternative connection to the database
! 26: #
! 27: do_test temptable-1.0 {
! 28: sqlite3 db2 ./test.db
! 29: set dummy {}
! 30: } {}
! 31:
! 32: # Create a permanent table.
! 33: #
! 34: do_test temptable-1.1 {
! 35: execsql {CREATE TABLE t1(a,b,c);}
! 36: execsql {INSERT INTO t1 VALUES(1,2,3);}
! 37: execsql {SELECT * FROM t1}
! 38: } {1 2 3}
! 39: do_test temptable-1.2 {
! 40: catch {db2 eval {SELECT * FROM sqlite_master}}
! 41: db2 eval {SELECT * FROM t1}
! 42: } {1 2 3}
! 43: do_test temptable-1.3 {
! 44: execsql {SELECT name FROM sqlite_master}
! 45: } {t1}
! 46: do_test temptable-1.4 {
! 47: db2 eval {SELECT name FROM sqlite_master}
! 48: } {t1}
! 49:
! 50: # Create a temporary table. Verify that only one of the two
! 51: # processes can see it.
! 52: #
! 53: do_test temptable-1.5 {
! 54: db2 eval {
! 55: CREATE TEMP TABLE t2(x,y,z);
! 56: INSERT INTO t2 VALUES(4,5,6);
! 57: }
! 58: db2 eval {SELECT * FROM t2}
! 59: } {4 5 6}
! 60: do_test temptable-1.6 {
! 61: catch {execsql {SELECT * FROM sqlite_master}}
! 62: catchsql {SELECT * FROM t2}
! 63: } {1 {no such table: t2}}
! 64: do_test temptable-1.7 {
! 65: catchsql {INSERT INTO t2 VALUES(8,9,0);}
! 66: } {1 {no such table: t2}}
! 67: do_test temptable-1.8 {
! 68: db2 eval {INSERT INTO t2 VALUES(8,9,0);}
! 69: db2 eval {SELECT * FROM t2 ORDER BY x}
! 70: } {4 5 6 8 9 0}
! 71: do_test temptable-1.9 {
! 72: db2 eval {DELETE FROM t2 WHERE x==8}
! 73: db2 eval {SELECT * FROM t2 ORDER BY x}
! 74: } {4 5 6}
! 75: do_test temptable-1.10 {
! 76: db2 eval {DELETE FROM t2}
! 77: db2 eval {SELECT * FROM t2}
! 78: } {}
! 79: do_test temptable-1.11 {
! 80: db2 eval {
! 81: INSERT INTO t2 VALUES(7,6,5);
! 82: INSERT INTO t2 VALUES(4,3,2);
! 83: SELECT * FROM t2 ORDER BY x;
! 84: }
! 85: } {4 3 2 7 6 5}
! 86: do_test temptable-1.12 {
! 87: db2 eval {DROP TABLE t2;}
! 88: set r [catch {db2 eval {SELECT * FROM t2}} msg]
! 89: lappend r $msg
! 90: } {1 {no such table: t2}}
! 91:
! 92: # Make sure temporary tables work with transactions
! 93: #
! 94: do_test temptable-2.1 {
! 95: execsql {
! 96: BEGIN TRANSACTION;
! 97: CREATE TEMPORARY TABLE t2(x,y);
! 98: INSERT INTO t2 VALUES(1,2);
! 99: SELECT * FROM t2;
! 100: }
! 101: } {1 2}
! 102: do_test temptable-2.2 {
! 103: execsql {ROLLBACK}
! 104: catchsql {SELECT * FROM t2}
! 105: } {1 {no such table: t2}}
! 106: do_test temptable-2.3 {
! 107: execsql {
! 108: BEGIN TRANSACTION;
! 109: CREATE TEMPORARY TABLE t2(x,y);
! 110: INSERT INTO t2 VALUES(1,2);
! 111: SELECT * FROM t2;
! 112: }
! 113: } {1 2}
! 114: do_test temptable-2.4 {
! 115: execsql {COMMIT}
! 116: catchsql {SELECT * FROM t2}
! 117: } {0 {1 2}}
! 118: do_test temptable-2.5 {
! 119: set r [catch {db2 eval {SELECT * FROM t2}} msg]
! 120: lappend r $msg
! 121: } {1 {no such table: t2}}
! 122:
! 123: # Make sure indices on temporary tables are also temporary.
! 124: #
! 125: do_test temptable-3.1 {
! 126: execsql {
! 127: CREATE INDEX i2 ON t2(x);
! 128: SELECT name FROM sqlite_master WHERE type='index';
! 129: }
! 130: } {}
! 131: do_test temptable-3.2 {
! 132: execsql {
! 133: SELECT y FROM t2 WHERE x=1;
! 134: }
! 135: } {2}
! 136: do_test temptable-3.3 {
! 137: execsql {
! 138: DROP INDEX i2;
! 139: SELECT y FROM t2 WHERE x=1;
! 140: }
! 141: } {2}
! 142: do_test temptable-3.4 {
! 143: execsql {
! 144: CREATE INDEX i2 ON t2(x);
! 145: DROP TABLE t2;
! 146: }
! 147: catchsql {DROP INDEX i2}
! 148: } {1 {no such index: i2}}
! 149:
! 150: # Check for correct name collision processing. A name collision can
! 151: # occur when process A creates a temporary table T then process B
! 152: # creates a permanent table also named T. The temp table in process A
! 153: # hides the existance of the permanent table.
! 154: #
! 155: do_test temptable-4.1 {
! 156: execsql {
! 157: CREATE TEMP TABLE t2(x,y);
! 158: INSERT INTO t2 VALUES(10,20);
! 159: SELECT * FROM t2;
! 160: } db2
! 161: } {10 20}
! 162: do_test temptable-4.2 {
! 163: execsql {
! 164: CREATE TABLE t2(x,y,z);
! 165: INSERT INTO t2 VALUES(9,8,7);
! 166: SELECT * FROM t2;
! 167: }
! 168: } {9 8 7}
! 169: do_test temptable-4.3 {
! 170: catchsql {
! 171: SELECT * FROM t2;
! 172: } db2
! 173: } {0 {10 20}}
! 174: do_test temptable-4.4.1 {
! 175: catchsql {
! 176: SELECT * FROM temp.t2;
! 177: } db2
! 178: } {0 {10 20}}
! 179: do_test temptable-4.4.2 {
! 180: catchsql {
! 181: SELECT * FROM main.t2;
! 182: } db2
! 183: } {0 {9 8 7}}
! 184: #do_test temptable-4.4.3 {
! 185: # catchsql {
! 186: # SELECT name FROM main.sqlite_master WHERE type='table';
! 187: # } db2
! 188: #} {1 {database schema has changed}}
! 189: do_test temptable-4.4.4 {
! 190: catchsql {
! 191: SELECT name FROM main.sqlite_master WHERE type='table';
! 192: } db2
! 193: } {0 {t1 t2}}
! 194: do_test temptable-4.4.5 {
! 195: catchsql {
! 196: SELECT * FROM main.t2;
! 197: } db2
! 198: } {0 {9 8 7}}
! 199: do_test temptable-4.4.6 {
! 200: # TEMP takes precedence over MAIN
! 201: catchsql {
! 202: SELECT * FROM t2;
! 203: } db2
! 204: } {0 {10 20}}
! 205: do_test temptable-4.5 {
! 206: catchsql {
! 207: DROP TABLE t2; -- should drop TEMP
! 208: SELECT * FROM t2; -- data should be from MAIN
! 209: } db2
! 210: } {0 {9 8 7}}
! 211: do_test temptable-4.6 {
! 212: db2 close
! 213: sqlite3 db2 ./test.db
! 214: catchsql {
! 215: SELECT * FROM t2;
! 216: } db2
! 217: } {0 {9 8 7}}
! 218: do_test temptable-4.7 {
! 219: catchsql {
! 220: DROP TABLE t2;
! 221: SELECT * FROM t2;
! 222: }
! 223: } {1 {no such table: t2}}
! 224: do_test temptable-4.8 {
! 225: db2 close
! 226: sqlite3 db2 ./test.db
! 227: execsql {
! 228: CREATE TEMP TABLE t2(x unique,y);
! 229: INSERT INTO t2 VALUES(1,2);
! 230: SELECT * FROM t2;
! 231: } db2
! 232: } {1 2}
! 233: do_test temptable-4.9 {
! 234: execsql {
! 235: CREATE TABLE t2(x unique, y);
! 236: INSERT INTO t2 VALUES(3,4);
! 237: SELECT * FROM t2;
! 238: }
! 239: } {3 4}
! 240: do_test temptable-4.10.1 {
! 241: catchsql {
! 242: SELECT * FROM t2;
! 243: } db2
! 244: } {0 {1 2}}
! 245: # Update: The schema is reloaded in test temptable-4.10.1. And tclsqlite.c
! 246: # handles it and retries the query anyway.
! 247: # do_test temptable-4.10.2 {
! 248: # catchsql {
! 249: # SELECT name FROM sqlite_master WHERE type='table'
! 250: # } db2
! 251: # } {1 {database schema has changed}}
! 252: do_test temptable-4.10.3 {
! 253: catchsql {
! 254: SELECT name FROM sqlite_master WHERE type='table'
! 255: } db2
! 256: } {0 {t1 t2}}
! 257: do_test temptable-4.11 {
! 258: execsql {
! 259: SELECT * FROM t2;
! 260: } db2
! 261: } {1 2}
! 262: do_test temptable-4.12 {
! 263: execsql {
! 264: SELECT * FROM t2;
! 265: }
! 266: } {3 4}
! 267: do_test temptable-4.13 {
! 268: catchsql {
! 269: DROP TABLE t2; -- drops TEMP.T2
! 270: SELECT * FROM t2; -- uses MAIN.T2
! 271: } db2
! 272: } {0 {3 4}}
! 273: do_test temptable-4.14 {
! 274: execsql {
! 275: SELECT * FROM t2;
! 276: }
! 277: } {3 4}
! 278: do_test temptable-4.15 {
! 279: db2 close
! 280: sqlite3 db2 ./test.db
! 281: execsql {
! 282: SELECT * FROM t2;
! 283: } db2
! 284: } {3 4}
! 285:
! 286: # Now create a temporary table in db2 and a permanent index in db. The
! 287: # temporary table in db2 should mask the name of the permanent index,
! 288: # but the permanent index should still be accessible and should still
! 289: # be updated when its corresponding table changes.
! 290: #
! 291: do_test temptable-5.1 {
! 292: execsql {
! 293: CREATE TEMP TABLE mask(a,b,c)
! 294: } db2
! 295: if {[permutation]=="prepare"} { db2 cache flush }
! 296: execsql {
! 297: CREATE INDEX mask ON t2(x);
! 298: SELECT * FROM t2;
! 299: }
! 300: } {3 4}
! 301: #do_test temptable-5.2 {
! 302: # catchsql {
! 303: # SELECT * FROM t2;
! 304: # } db2
! 305: #} {1 {database schema has changed}}
! 306: do_test temptable-5.3 {
! 307: catchsql {
! 308: SELECT * FROM t2;
! 309: } db2
! 310: } {0 {3 4}}
! 311: do_test temptable-5.4 {
! 312: execsql {
! 313: SELECT y FROM t2 WHERE x=3
! 314: }
! 315: } {4}
! 316: do_test temptable-5.5 {
! 317: execsql {
! 318: SELECT y FROM t2 WHERE x=3
! 319: } db2
! 320: } {4}
! 321: do_test temptable-5.6 {
! 322: execsql {
! 323: INSERT INTO t2 VALUES(1,2);
! 324: SELECT y FROM t2 WHERE x=1;
! 325: } db2
! 326: } {2}
! 327: do_test temptable-5.7 {
! 328: execsql {
! 329: SELECT y FROM t2 WHERE x=3
! 330: } db2
! 331: } {4}
! 332: do_test temptable-5.8 {
! 333: execsql {
! 334: SELECT y FROM t2 WHERE x=1;
! 335: }
! 336: } {2}
! 337: do_test temptable-5.9 {
! 338: execsql {
! 339: SELECT y FROM t2 WHERE x=3
! 340: }
! 341: } {4}
! 342:
! 343: db2 close
! 344:
! 345: # Test for correct operation of read-only databases
! 346: #
! 347: do_test temptable-6.1 {
! 348: execsql {
! 349: CREATE TABLE t8(x);
! 350: INSERT INTO t8 VALUES('xyzzy');
! 351: SELECT * FROM t8;
! 352: }
! 353: } {xyzzy}
! 354: do_test temptable-6.2 {
! 355: db close
! 356: catch {file attributes test.db -permissions 0444}
! 357: catch {file attributes test.db -readonly 1}
! 358: sqlite3 db test.db
! 359: if {[file writable test.db]} {
! 360: error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
! 361: }
! 362: execsql {
! 363: SELECT * FROM t8;
! 364: }
! 365: } {xyzzy}
! 366: do_test temptable-6.3 {
! 367: if {[file writable test.db]} {
! 368: error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
! 369: }
! 370: catchsql {
! 371: CREATE TABLE t9(x,y);
! 372: }
! 373: } {1 {attempt to write a readonly database}}
! 374: do_test temptable-6.4 {
! 375: catchsql {
! 376: CREATE TEMP TABLE t9(x,y);
! 377: }
! 378: } {0 {}}
! 379: do_test temptable-6.5 {
! 380: catchsql {
! 381: INSERT INTO t9 VALUES(1,2);
! 382: SELECT * FROM t9;
! 383: }
! 384: } {0 {1 2}}
! 385: do_test temptable-6.6 {
! 386: if {[file writable test.db]} {
! 387: error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
! 388: }
! 389: catchsql {
! 390: INSERT INTO t8 VALUES('hello');
! 391: SELECT * FROM t8;
! 392: }
! 393: } {1 {attempt to write a readonly database}}
! 394: do_test temptable-6.7 {
! 395: catchsql {
! 396: SELECT * FROM t8,t9;
! 397: }
! 398: } {0 {xyzzy 1 2}}
! 399: do_test temptable-6.8 {
! 400: db close
! 401: sqlite3 db test.db
! 402: catchsql {
! 403: SELECT * FROM t8,t9;
! 404: }
! 405: } {1 {no such table: t9}}
! 406:
! 407: forcedelete test2.db test2.db-journal
! 408: ifcapable attach {
! 409: do_test temptable-7.1 {
! 410: catchsql {
! 411: ATTACH 'test2.db' AS two;
! 412: CREATE TEMP TABLE two.abc(x,y);
! 413: }
! 414: } {1 {temporary table name must be unqualified}}
! 415: }
! 416:
! 417: # Need to do the following for tcl 8.5 on mac. On that configuration, the
! 418: # -readonly flag is taken so seriously that a subsequent [forcedelete]
! 419: # (required before the next test file can be executed) will fail.
! 420: #
! 421: catch {file attributes test.db -readonly 0}
! 422:
! 423: do_test temptable-8.0 {
! 424: db close
! 425: catch {forcedelete test.db}
! 426: sqlite3 db test.db
! 427: } {}
! 428: do_test temptable-8.1 {
! 429: execsql { CREATE TEMP TABLE tbl2(a, b); }
! 430: execsql {
! 431: CREATE TABLE tbl(a, b);
! 432: INSERT INTO tbl VALUES(1, 2);
! 433: }
! 434: execsql {SELECT * FROM tbl}
! 435: } {1 2}
! 436: do_test temptable-8.2 {
! 437: execsql { CREATE TEMP TABLE tbl(a, b); }
! 438: execsql {SELECT * FROM tbl}
! 439: } {}
! 440:
! 441: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to temptable.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