Annotation of embedaddon/sqlite3/test/fts3d.test, revision 1.1
1.1 ! misho 1: # 2008 June 26
! 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 focus
! 12: # of this script is testing the FTS3 module's optimize() function.
! 13: #
! 14:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17: source $testdir/fts3_common.tcl
! 18:
! 19: # If SQLITE_ENABLE_FTS3 is not defined, omit this file.
! 20: ifcapable !fts3 {
! 21: finish_test
! 22: return
! 23: }
! 24:
! 25: #*************************************************************************
! 26: # Utility function to check for the expected terms in the segment
! 27: # level/index. _all version does same but for entire index.
! 28: proc check_terms {test level index terms} {
! 29: set where "level = $level AND idx = $index"
! 30: do_test $test.terms [list fts3_terms t1 $where] $terms
! 31: }
! 32: proc check_terms_all {test terms} {
! 33: do_test $test.terms [list fts3_terms t1 1] $terms
! 34: }
! 35:
! 36: # Utility function to check for the expected doclist for the term in
! 37: # segment level/index. _all version does same for entire index.
! 38: proc check_doclist {test level index term doclist} {
! 39: set where "level = $level AND idx = $index"
! 40: do_test $test.doclist [list fts3_doclist t1 $term $where] $doclist
! 41: }
! 42: proc check_doclist_all {test term doclist} {
! 43: do_test $test.doclist [list fts3_doclist t1 $term 1] $doclist
! 44: }
! 45:
! 46: #*************************************************************************
! 47: # Test results when all rows are deleted and one is added back.
! 48: # Previously older segments would continue to exist, but now the index
! 49: # should be dropped when the table is empty. The results should look
! 50: # exactly like we never added the earlier rows in the first place.
! 51: db eval {
! 52: DROP TABLE IF EXISTS t1;
! 53: CREATE VIRTUAL TABLE t1 USING fts3(c);
! 54: INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');
! 55: INSERT INTO t1 (docid, c) VALUES (2, 'That was a test');
! 56: INSERT INTO t1 (docid, c) VALUES (3, 'This is a test');
! 57: DELETE FROM t1 WHERE 1=1; -- Delete each row rather than dropping table.
! 58: INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');
! 59: }
! 60:
! 61: # Should be a single initial segment.
! 62: do_test fts3d-1.segments {
! 63: execsql {
! 64: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 65: }
! 66: } {0 0}
! 67: do_test fts3d-1.matches {
! 68: execsql {
! 69: SELECT OFFSETS(t1) FROM t1
! 70: WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid;
! 71: }
! 72: } {{0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}}
! 73:
! 74: check_terms_all fts3d-1.1 {a is test this}
! 75: check_doclist_all fts3d-1.1.1 a {[1 0[2]]}
! 76: check_doclist_all fts3d-1.1.2 is {[1 0[1]]}
! 77: check_doclist_all fts3d-1.1.3 test {[1 0[3]]}
! 78: check_doclist_all fts3d-1.1.4 this {[1 0[0]]}
! 79:
! 80: check_terms fts3d-1.2 0 0 {a is test this}
! 81: check_doclist fts3d-1.2.1 0 0 a {[1 0[2]]}
! 82: check_doclist fts3d-1.2.2 0 0 is {[1 0[1]]}
! 83: check_doclist fts3d-1.2.3 0 0 test {[1 0[3]]}
! 84: check_doclist fts3d-1.2.4 0 0 this {[1 0[0]]}
! 85:
! 86: #*************************************************************************
! 87: # Test results when everything is optimized manually.
! 88: # NOTE(shess): This is a copy of fts3c-1.3. I've pulled a copy here
! 89: # because fts3d-2 and fts3d-3 should have identical results.
! 90: db eval {
! 91: DROP TABLE IF EXISTS t1;
! 92: CREATE VIRTUAL TABLE t1 USING fts3(c);
! 93: INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');
! 94: INSERT INTO t1 (docid, c) VALUES (2, 'That was a test');
! 95: INSERT INTO t1 (docid, c) VALUES (3, 'This is a test');
! 96: DELETE FROM t1 WHERE docid IN (1,3);
! 97: DROP TABLE IF EXISTS t1old;
! 98: ALTER TABLE t1 RENAME TO t1old;
! 99: CREATE VIRTUAL TABLE t1 USING fts3(c);
! 100: INSERT INTO t1 (docid, c) SELECT docid, c FROM t1old;
! 101: DROP TABLE t1old;
! 102: }
! 103:
! 104: # Should be a single optimal segment with the same logical results.
! 105: do_test fts3d-2.segments {
! 106: execsql {
! 107: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 108: }
! 109: } {0 0}
! 110: do_test fts3d-2.matches {
! 111: execsql {
! 112: SELECT OFFSETS(t1) FROM t1
! 113: WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid;
! 114: }
! 115: } {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}}
! 116:
! 117: check_terms_all fts3d-2.1 {a test that was}
! 118: check_doclist_all fts3d-2.1.1 a {[2 0[2]]}
! 119: check_doclist_all fts3d-2.1.2 test {[2 0[3]]}
! 120: check_doclist_all fts3d-2.1.3 that {[2 0[0]]}
! 121: check_doclist_all fts3d-2.1.4 was {[2 0[1]]}
! 122:
! 123: check_terms fts3d-2.2 0 0 {a test that was}
! 124: check_doclist fts3d-2.2.1 0 0 a {[2 0[2]]}
! 125: check_doclist fts3d-2.2.2 0 0 test {[2 0[3]]}
! 126: check_doclist fts3d-2.2.3 0 0 that {[2 0[0]]}
! 127: check_doclist fts3d-2.2.4 0 0 was {[2 0[1]]}
! 128:
! 129: #*************************************************************************
! 130: # Test results when everything is optimized via optimize().
! 131: db eval {
! 132: DROP TABLE IF EXISTS t1;
! 133: CREATE VIRTUAL TABLE t1 USING fts3(c);
! 134: INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');
! 135: INSERT INTO t1 (docid, c) VALUES (2, 'That was a test');
! 136: INSERT INTO t1 (docid, c) VALUES (3, 'This is a test');
! 137: DELETE FROM t1 WHERE docid IN (1,3);
! 138: SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
! 139: }
! 140:
! 141: # Should be a single optimal segment with the same logical results.
! 142: do_test fts3d-3.segments {
! 143: execsql {
! 144: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 145: }
! 146: } {0 0}
! 147: do_test fts3d-3.matches {
! 148: execsql {
! 149: SELECT OFFSETS(t1) FROM t1
! 150: WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid;
! 151: }
! 152: } {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}}
! 153:
! 154: check_terms_all fts3d-3.1 {a test that was}
! 155: check_doclist_all fts3d-3.1.1 a {[2 0[2]]}
! 156: check_doclist_all fts3d-3.1.2 test {[2 0[3]]}
! 157: check_doclist_all fts3d-3.1.3 that {[2 0[0]]}
! 158: check_doclist_all fts3d-3.1.4 was {[2 0[1]]}
! 159:
! 160: check_terms fts3d-3.2 0 0 {a test that was}
! 161: check_doclist fts3d-3.2.1 0 0 a {[2 0[2]]}
! 162: check_doclist fts3d-3.2.2 0 0 test {[2 0[3]]}
! 163: check_doclist fts3d-3.2.3 0 0 that {[2 0[0]]}
! 164: check_doclist fts3d-3.2.4 0 0 was {[2 0[1]]}
! 165:
! 166: #*************************************************************************
! 167: # Test optimize() against a table involving segment merges.
! 168: # NOTE(shess): Since there's no transaction, each of the INSERT/UPDATE
! 169: # statements generates a segment.
! 170: db eval {
! 171: DROP TABLE IF EXISTS t1;
! 172: CREATE VIRTUAL TABLE t1 USING fts3(c);
! 173:
! 174: INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
! 175: INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
! 176: INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
! 177:
! 178: UPDATE t1 SET c = 'This is a test one' WHERE rowid = 1;
! 179: UPDATE t1 SET c = 'That was a test one' WHERE rowid = 2;
! 180: UPDATE t1 SET c = 'This is a test one' WHERE rowid = 3;
! 181:
! 182: UPDATE t1 SET c = 'This is a test two' WHERE rowid = 1;
! 183: UPDATE t1 SET c = 'That was a test two' WHERE rowid = 2;
! 184: UPDATE t1 SET c = 'This is a test two' WHERE rowid = 3;
! 185:
! 186: UPDATE t1 SET c = 'This is a test three' WHERE rowid = 1;
! 187: UPDATE t1 SET c = 'That was a test three' WHERE rowid = 2;
! 188: UPDATE t1 SET c = 'This is a test three' WHERE rowid = 3;
! 189:
! 190: UPDATE t1 SET c = 'This is a test four' WHERE rowid = 1;
! 191: UPDATE t1 SET c = 'That was a test four' WHERE rowid = 2;
! 192: UPDATE t1 SET c = 'This is a test four' WHERE rowid = 3;
! 193:
! 194: UPDATE t1 SET c = 'This is a test' WHERE rowid = 1;
! 195: UPDATE t1 SET c = 'That was a test' WHERE rowid = 2;
! 196: UPDATE t1 SET c = 'This is a test' WHERE rowid = 3;
! 197: }
! 198:
! 199: # 2 segments in level 0, 1 in level 1 (18 segments created, 16
! 200: # merged).
! 201: do_test fts3d-4.segments {
! 202: execsql {
! 203: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 204: }
! 205: } {0 0 0 1 1 0}
! 206:
! 207: do_test fts3d-4.matches {
! 208: execsql {
! 209: SELECT OFFSETS(t1) FROM t1
! 210: WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid;
! 211: }
! 212: } [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \
! 213: {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \
! 214: {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}]
! 215:
! 216: check_terms_all fts3d-4.1 {a four is one test that this three two was}
! 217: check_doclist_all fts3d-4.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]}
! 218: check_doclist_all fts3d-4.1.2 four {}
! 219: check_doclist_all fts3d-4.1.3 is {[1 0[1]] [3 0[1]]}
! 220: check_doclist_all fts3d-4.1.4 one {}
! 221: check_doclist_all fts3d-4.1.5 test {[1 0[3]] [2 0[3]] [3 0[3]]}
! 222: check_doclist_all fts3d-4.1.6 that {[2 0[0]]}
! 223: check_doclist_all fts3d-4.1.7 this {[1 0[0]] [3 0[0]]}
! 224: check_doclist_all fts3d-4.1.8 three {}
! 225: check_doclist_all fts3d-4.1.9 two {}
! 226: check_doclist_all fts3d-4.1.10 was {[2 0[1]]}
! 227:
! 228: check_terms fts3d-4.2 0 0 {a four test that was}
! 229: check_doclist fts3d-4.2.1 0 0 a {[2 0[2]]}
! 230: check_doclist fts3d-4.2.2 0 0 four {[2]}
! 231: check_doclist fts3d-4.2.3 0 0 test {[2 0[3]]}
! 232: check_doclist fts3d-4.2.4 0 0 that {[2 0[0]]}
! 233: check_doclist fts3d-4.2.5 0 0 was {[2 0[1]]}
! 234:
! 235: check_terms fts3d-4.3 0 1 {a four is test this}
! 236: check_doclist fts3d-4.3.1 0 1 a {[3 0[2]]}
! 237: check_doclist fts3d-4.3.2 0 1 four {[3]}
! 238: check_doclist fts3d-4.3.3 0 1 is {[3 0[1]]}
! 239: check_doclist fts3d-4.3.4 0 1 test {[3 0[3]]}
! 240: check_doclist fts3d-4.3.5 0 1 this {[3 0[0]]}
! 241:
! 242: check_terms fts3d-4.4 1 0 {a four is one test that this three two was}
! 243: check_doclist fts3d-4.4.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]}
! 244: check_doclist fts3d-4.4.2 1 0 four {[1] [2 0[4]] [3 0[4]]}
! 245: check_doclist fts3d-4.4.3 1 0 is {[1 0[1]] [3 0[1]]}
! 246: check_doclist fts3d-4.4.4 1 0 one {[1] [2] [3]}
! 247: check_doclist fts3d-4.4.5 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]}
! 248: check_doclist fts3d-4.4.6 1 0 that {[2 0[0]]}
! 249: check_doclist fts3d-4.4.7 1 0 this {[1 0[0]] [3 0[0]]}
! 250: check_doclist fts3d-4.4.8 1 0 three {[1] [2] [3]}
! 251: check_doclist fts3d-4.4.9 1 0 two {[1] [2] [3]}
! 252: check_doclist fts3d-4.4.10 1 0 was {[2 0[1]]}
! 253:
! 254: # Optimize should leave the result in the level of the highest-level
! 255: # prior segment.
! 256: breakpoint
! 257: do_test fts3d-4.5 {
! 258: execsql {
! 259: SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
! 260: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 261: }
! 262: } {{Index optimized} 1 0}
! 263:
! 264: # Identical to fts3d-4.matches.
! 265: do_test fts3d-4.5.matches {
! 266: execsql {
! 267: SELECT OFFSETS(t1) FROM t1
! 268: WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid;
! 269: }
! 270: } [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \
! 271: {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \
! 272: {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}]
! 273:
! 274: check_terms_all fts3d-4.5.1 {a is test that this was}
! 275: check_doclist_all fts3d-4.5.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]}
! 276: check_doclist_all fts3d-4.5.1.2 is {[1 0[1]] [3 0[1]]}
! 277: check_doclist_all fts3d-4.5.1.3 test {[1 0[3]] [2 0[3]] [3 0[3]]}
! 278: check_doclist_all fts3d-4.5.1.4 that {[2 0[0]]}
! 279: check_doclist_all fts3d-4.5.1.5 this {[1 0[0]] [3 0[0]]}
! 280: check_doclist_all fts3d-4.5.1.6 was {[2 0[1]]}
! 281:
! 282: check_terms fts3d-4.5.2 1 0 {a is test that this was}
! 283: check_doclist fts3d-4.5.2.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]}
! 284: check_doclist fts3d-4.5.2.2 1 0 is {[1 0[1]] [3 0[1]]}
! 285: check_doclist fts3d-4.5.2.3 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]}
! 286: check_doclist fts3d-4.5.2.4 1 0 that {[2 0[0]]}
! 287: check_doclist fts3d-4.5.2.5 1 0 this {[1 0[0]] [3 0[0]]}
! 288: check_doclist fts3d-4.5.2.6 1 0 was {[2 0[1]]}
! 289:
! 290: # Re-optimizing does nothing.
! 291: do_test fts3d-5.0 {
! 292: execsql {
! 293: SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
! 294: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 295: }
! 296: } {{Index already optimal} 1 0}
! 297:
! 298: # Even if we move things around, still does nothing.
! 299: do_test fts3d-5.1 {
! 300: execsql {
! 301: UPDATE t1_segdir SET level = 2 WHERE level = 1 AND idx = 0;
! 302: SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
! 303: SELECT level, idx FROM t1_segdir ORDER BY level, idx;
! 304: }
! 305: } {{Index already optimal} 2 0}
! 306:
! 307:
! 308: # ALTER TABLE RENAME should work regardless of the database encoding.
! 309: #
! 310: do_test fts3d-6.0 {
! 311: db close
! 312: forcedelete test.db
! 313: sqlite3 db test.db
! 314: db eval {
! 315: PRAGMA encoding=UTF8;
! 316: CREATE VIRTUAL TABLE fts USING fts3(a,b,c);
! 317: SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
! 318: }
! 319: } {fts_content fts_segdir fts_segments}
! 320: do_test fts3d-6.1 {
! 321: db eval {
! 322: ALTER TABLE fts RENAME TO xyz;
! 323: SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
! 324: }
! 325: } {xyz_content xyz_segdir xyz_segments}
! 326: do_test fts3d-6.2 {
! 327: db close
! 328: forcedelete test.db
! 329: sqlite3 db test.db
! 330: db eval {
! 331: PRAGMA encoding=UTF16le;
! 332: CREATE VIRTUAL TABLE fts USING fts3(a,b,c);
! 333: SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
! 334: }
! 335: } {fts_content fts_segdir fts_segments}
! 336: do_test fts3d-6.3 {
! 337: db eval {
! 338: ALTER TABLE fts RENAME TO xyz;
! 339: SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
! 340: }
! 341: } {xyz_content xyz_segdir xyz_segments}
! 342: do_test fts3d-6.4 {
! 343: db close
! 344: forcedelete test.db
! 345: sqlite3 db test.db
! 346: db eval {
! 347: PRAGMA encoding=UTF16be;
! 348: CREATE VIRTUAL TABLE fts USING fts3(a,b,c);
! 349: SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
! 350: }
! 351: } {fts_content fts_segdir fts_segments}
! 352: do_test fts3d-6.5 {
! 353: db eval {
! 354: ALTER TABLE fts RENAME TO xyz;
! 355: SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
! 356: }
! 357: } {xyz_content xyz_segdir xyz_segments}
! 358:
! 359:
! 360: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fts3d.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