Annotation of embedaddon/sqlite3/test/e_fts3.test, revision 1.1
1.1 ! misho 1: # 2009 November 28
! 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: #
! 12: # This file implements tests to verify the "testable statements" in the
! 13: # fts3.in document.
! 14: #
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # If this build does not include FTS3, skip the tests in this file.
! 20: #
! 21: ifcapable !fts3 { finish_test ; return }
! 22: source $testdir/fts3_common.tcl
! 23: source $testdir/malloc_common.tcl
! 24:
! 25: # Procs used to make the tests in this file easier to read.
! 26: #
! 27: proc ddl_test {tn ddl} {
! 28: uplevel [list do_write_test e_fts3-$tn sqlite_master $ddl]
! 29: }
! 30: proc write_test {tn tbl sql} {
! 31: uplevel [list do_write_test e_fts3-$tn $tbl $sql]
! 32: }
! 33: proc read_test {tn sql result} {
! 34: uplevel [list do_select_test e_fts3-$tn $sql $result]
! 35: }
! 36: proc error_test {tn sql result} {
! 37: uplevel [list do_error_test e_fts3-$tn $sql $result]
! 38: }
! 39:
! 40:
! 41: #-------------------------------------------------------------------------
! 42: # The body of the following [foreach] block contains test cases to verify
! 43: # that the example code in fts3.html works as expected. The tests run three
! 44: # times, with different values for DO_MALLOC_TEST.
! 45: #
! 46: # DO_MALLOC_TEST=0: Run tests with no OOM errors.
! 47: # DO_MALLOC_TEST=1: Run tests with transient OOM errors.
! 48: # DO_MALLOC_TEST=2: Run tests with persistent OOM errors.
! 49: #
! 50: foreach {DO_MALLOC_TEST enc} {
! 51: 0 utf-8
! 52: 1 utf-8
! 53: 2 utf-8
! 54: 1 utf-16
! 55: } {
! 56:
! 57: #if {$DO_MALLOC_TEST} break
! 58:
! 59: # Reset the database and database connection. If this iteration of the
! 60: # [foreach] loop is testing with OOM errors, disable the lookaside buffer.
! 61: #
! 62: db close
! 63: forcedelete test.db test.db-journal
! 64: sqlite3 db test.db
! 65: if {$DO_MALLOC_TEST} { sqlite3_db_config_lookaside db 0 0 0 }
! 66: db eval "PRAGMA encoding = '$enc'"
! 67:
! 68: proc mit {blob} {
! 69: set scan(littleEndian) i*
! 70: set scan(bigEndian) I*
! 71: binary scan $blob $scan($::tcl_platform(byteOrder)) r
! 72: return $r
! 73: }
! 74: db func mit mit
! 75:
! 76: ##########################################################################
! 77: # Test the example CREATE VIRTUAL TABLE statements in section 1.1
! 78: # of fts3.in.
! 79: #
! 80: ddl_test 1.1.1.1 {CREATE VIRTUAL TABLE data USING fts3()}
! 81: read_test 1.1.1.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0}
! 82:
! 83: ddl_test 1.1.2.1 {
! 84: CREATE VIRTUAL TABLE pages USING fts3(title, keywords, body)
! 85: }
! 86: read_test 1.1.2.2 {
! 87: PRAGMA table_info(pages)
! 88: } {0 title {} 0 {} 0 1 keywords {} 0 {} 0 2 body {} 0 {} 0}
! 89:
! 90: ddl_test 1.1.3.1 {
! 91: CREATE VIRTUAL TABLE mail USING fts3(
! 92: subject VARCHAR(256) NOT NULL,
! 93: body TEXT CHECK(length(body)<10240)
! 94: )
! 95: }
! 96: read_test 1.1.3.2 {
! 97: PRAGMA table_info(mail)
! 98: } {0 subject {} 0 {} 0 1 body {} 0 {} 0}
! 99:
! 100: # A very large string. Used to test if the constraint on column "body" of
! 101: # table "mail" is enforced (it should not be - FTS3 tables do not support
! 102: # constraints).
! 103: set largetext [string repeat "the quick brown fox " 5000]
! 104: write_test 1.1.3.3 mail_content { INSERT INTO mail VALUES(NULL, $largetext) }
! 105: read_test 1.1.3.4 {
! 106: SELECT subject IS NULL, length(body) FROM mail
! 107: } [list 1 100000]
! 108:
! 109: ddl_test 1.1.4.1 {
! 110: CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter)
! 111: }
! 112: read_test 1.1.4.2 {
! 113: PRAGMA table_info(papers)
! 114: } {0 author {} 0 {} 0 1 document {} 0 {} 0}
! 115:
! 116: ddl_test 1.1.5.1 {
! 117: CREATE VIRTUAL TABLE simpledata USING fts3(tokenize=simple)
! 118: }
! 119: read_test 1.1.5.2 {
! 120: PRAGMA table_info(simpledata)
! 121: } {0 content {} 0 {} 0}
! 122:
! 123: ifcapable icu {
! 124: ddl_test 1.1.6.1 {
! 125: CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU)
! 126: }
! 127: read_test 1.1.6.2 {
! 128: PRAGMA table_info(names)
! 129: } {0 a {} 0 {} 0 1 b {} 0 {} 0}
! 130: }
! 131:
! 132: ddl_test 1.1.7.1 {DROP TABLE data}
! 133: ddl_test 1.1.7.2 {DROP TABLE pages}
! 134: ddl_test 1.1.7.3 {DROP TABLE mail}
! 135: ddl_test 1.1.7.4 {DROP TABLE papers}
! 136: ddl_test 1.1.7.5 {DROP TABLE simpledata}
! 137: read_test 1.1.7.6 {SELECT * FROM sqlite_master} {}
! 138:
! 139: # The following is not one of the examples in section 1.1. It tests
! 140: # specifying an FTS3 table with no module arguments using a slightly
! 141: # different syntax.
! 142: ddl_test 1.1.8.1 {CREATE VIRTUAL TABLE data USING fts3;}
! 143: read_test 1.1.8.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0}
! 144: ddl_test 1.1.8.3 {DROP TABLE data}
! 145:
! 146: ##########################################################################
! 147: # Test the examples in section 1.2 (populating fts3 tables)
! 148: #
! 149: ddl_test 1.2.1.1 {
! 150: CREATE VIRTUAL TABLE pages USING fts3(title, body);
! 151: }
! 152: write_test 1.2.1.2 pages_content {
! 153: INSERT INTO pages(docid, title, body)
! 154: VALUES(53, 'Home Page', 'SQLite is a software...');
! 155: }
! 156: read_test 1.2.1.3 {
! 157: SELECT docid, * FROM pages
! 158: } {53 {Home Page} {SQLite is a software...}}
! 159:
! 160: write_test 1.2.1.4 pages_content {
! 161: INSERT INTO pages(title, body)
! 162: VALUES('Download', 'All SQLite source code...');
! 163: }
! 164: read_test 1.2.1.5 {
! 165: SELECT docid, * FROM pages
! 166: } {53 {Home Page} {SQLite is a software...} 54 Download {All SQLite source code...}}
! 167:
! 168: write_test 1.2.1.6 pages_content {
! 169: UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54
! 170: }
! 171: read_test 1.2.1.7 {
! 172: SELECT docid, * FROM pages
! 173: } {53 {Home Page} {SQLite is a software...} 54 {Download SQLite} {All SQLite source code...}}
! 174:
! 175: write_test 1.2.1.8 pages_content { DELETE FROM pages }
! 176: read_test 1.2.1.9 { SELECT docid, * FROM pages } {}
! 177:
! 178: do_error_test fts3-1.2.1.10 {
! 179: INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');
! 180: } {SQL logic error or missing database}
! 181:
! 182: # Test the optimize() function example:
! 183: ddl_test 1.2.2.1 { CREATE VIRTUAL TABLE docs USING fts3 }
! 184: write_test 1.2.2.2 docs_content {
! 185: INSERT INTO docs VALUES('Others translate the first clause as');
! 186: }
! 187: write_test 1.2.2.3 docs_content {
! 188: INSERT INTO docs VALUES('"which is for Solomon," meaning that');
! 189: }
! 190: write_test 1.2.2.4 docs_content {
! 191: INSERT INTO docs VALUES('the book is dedicated to Solomon.');
! 192: }
! 193: read_test 1.2.2.5 { SELECT count(*) FROM docs_segdir } {3}
! 194: write_test 1.2.2.6 docs_segdir {
! 195: INSERT INTO docs(docs) VALUES('optimize');
! 196: }
! 197: read_test 1.2.2.7 { SELECT count(*) FROM docs_segdir } {1}
! 198: ddl_test 1.2.2.8 { DROP TABLE docs }
! 199:
! 200: ##########################################################################
! 201: # Test the examples in section 1.3 (querying FTS3 tables)
! 202: #
! 203: ddl_test 1.3.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) }
! 204: read_test 1.3.1.2 {
! 205: SELECT * FROM mail WHERE rowid = 15; -- Fast. Rowid lookup.
! 206: SELECT * FROM mail WHERE body MATCH 'sqlite'; -- Fast. Full-text query.
! 207: SELECT * FROM mail WHERE mail MATCH 'search'; -- Fast. Full-text query.
! 208: SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; -- Slow. Linear scan.
! 209: SELECT * FROM mail WHERE subject = 'database'; -- Slow. Linear scan.
! 210: SELECT * FROM mail WHERE subject MATCH 'database'; -- Fast. Full-text query.
! 211: } {}
! 212: ddl_test 1.3.1.3 { DROP TABLE mail }
! 213:
! 214: ddl_test 1.3.2.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) }
! 215:
! 216: write_test 1.3.2.2 mail_content {
! 217: INSERT INTO mail(docid, subject, body)
! 218: VALUES(1, 'software feedback', 'found it too slow')
! 219: }
! 220: write_test 1.3.2.3 mail_content {
! 221: INSERT INTO mail(docid, subject, body)
! 222: VALUES(2, 'software feedback', 'no feedback')
! 223: }
! 224: write_test 1.3.2.4 mail_content {
! 225: INSERT INTO mail(docid, subject, body)
! 226: VALUES(3, 'slow lunch order', 'was a software problem')
! 227: }
! 228: read_test 1.3.2.5 {
! 229: SELECT * FROM mail WHERE subject MATCH 'software'
! 230: } {{software feedback} {found it too slow} {software feedback} {no feedback}}
! 231: read_test 1.3.2.6 {
! 232: SELECT * FROM mail WHERE body MATCH 'feedback'
! 233: } {{software feedback} {no feedback}}
! 234: read_test 1.3.2.7 {
! 235: SELECT * FROM mail WHERE mail MATCH 'software'
! 236: } {{software feedback} {found it too slow} {software feedback} {no feedback} {slow lunch order} {was a software problem}}
! 237: read_test 1.3.2.7 {
! 238: SELECT * FROM mail WHERE mail MATCH 'slow'
! 239: } {{software feedback} {found it too slow} {slow lunch order} {was a software problem}}
! 240: ddl_test 1.3.2.8 { DROP TABLE mail }
! 241:
! 242: ddl_test 1.3.3.1 { CREATE VIRTUAL TABLE docs USING fts3(content) }
! 243: read_test 1.3.3.2 { SELECT * FROM docs WHERE docs MATCH 'sqlite' } {}
! 244: read_test 1.3.3.3 { SELECT * FROM docs WHERE docs.docs MATCH 'sqlite' } {}
! 245: read_test 1.3.3.4 { SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite' } {}
! 246: do_error_test e_fts3-1.3.3.5 {
! 247: SELECT * FROM docs WHERE main.docs MATCH 'sqlite'
! 248: } {no such column: main.docs}
! 249: ddl_test 1.3.2.8 { DROP TABLE docs }
! 250:
! 251: ##########################################################################
! 252: # Test the examples in section 3 (full-text index queries).
! 253: #
! 254: ddl_test 1.4.1.1 { CREATE VIRTUAL TABLE docs USING fts3(title, body) }
! 255: unset -nocomplain R
! 256: foreach {tn title body} {
! 257: 2 "linux driver" "a device"
! 258: 3 "driver" "linguistic trick"
! 259: 4 "problems" "linux problems"
! 260: 5 "linux" "big problems"
! 261: 6 "linux driver" "a device driver problem"
! 262: 7 "good times" "applications for linux"
! 263: 8 "not so good" "linux applications"
! 264: 9 "alternative" "linoleum appliances"
! 265: 10 "no L I N" "to be seen"
! 266: } {
! 267: write_test 1.4.1.$tn docs_content { INSERT INTO docs VALUES($title,$body) }
! 268: set R($tn) [list $title $body]
! 269: }
! 270:
! 271: read_test 1.4.1.11 {
! 272: SELECT * FROM docs WHERE docs MATCH 'linux'
! 273: } [concat $R(2) $R(4) $R(5) $R(6) $R(7) $R(8)]
! 274: read_test 1.4.1.12 {
! 275: SELECT * FROM docs WHERE docs MATCH 'lin*'
! 276: } [concat $R(2) $R(3) $R(4) $R(5) $R(6) $R(7) $R(8) $R(9)]
! 277: read_test 1.4.1.13 {
! 278: SELECT * FROM docs WHERE docs MATCH 'title:linux problems'
! 279: } [concat $R(5)]
! 280: read_test 1.4.1.14 {
! 281: SELECT * FROM docs WHERE body MATCH 'title:linux driver'
! 282: } [concat $R(6)]
! 283: read_test 1.4.1.15 {
! 284: SELECT * FROM docs WHERE docs MATCH '"linux applications"'
! 285: } [concat $R(8)]
! 286: read_test 1.4.1.16 {
! 287: SELECT * FROM docs WHERE docs MATCH '"lin* app*"'
! 288: } [concat $R(8) $R(9)]
! 289: ddl_test 1.4.1.17 { DROP TABLE docs }
! 290: unset R
! 291:
! 292: ddl_test 1.4.2.1 { CREATE VIRTUAL TABLE docs USING fts3() }
! 293: write_test 1.4.2.2 docs_content {
! 294: INSERT INTO docs VALUES(
! 295: 'SQLite is an ACID compliant embedded relational database management system')
! 296: }
! 297: foreach {tn query hit} {
! 298: 3 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database'} 1
! 299: 4 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite'} 1
! 300: 5 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite'} 0
! 301: 6 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"'} 1
! 302: 7 {SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite'} 1
! 303: 8 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational'} 1
! 304: 9 {SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational'} 0
! 305: } {
! 306: set res [db eval {SELECT * FROM docs WHERE $hit}]
! 307: read_test 1.4.2.$tn $query $res
! 308: }
! 309: ddl_test 1.4.2.10 { DROP TABLE docs }
! 310:
! 311: ##########################################################################
! 312: # Test the example in section 3.1 (set operators with enhanced syntax).
! 313: #
! 314: set sqlite_fts3_enable_parentheses 1
! 315: ddl_test 1.5.1.1 { CREATE VIRTUAL TABLE docs USING fts3() }
! 316: foreach {tn docid content} {
! 317: 2 1 "a database is a software system"
! 318: 3 2 "sqlite is a software system"
! 319: 4 3 "sqlite is a database"
! 320: } {
! 321: set R($docid) $content
! 322: write_test 1.5.1.$tn docs_content {
! 323: INSERT INTO docs(docid, content) VALUES($docid, $content)
! 324: }
! 325: }
! 326: read_test 1.5.1.4 {
! 327: SELECT * FROM docs WHERE docs MATCH 'sqlite AND database'
! 328: } [list $R(3)]
! 329: read_test 1.5.1.5 {
! 330: SELECT * FROM docs WHERE docs MATCH 'database sqlite'
! 331: } [list $R(3)]
! 332: read_test 1.5.1.6 {
! 333: SELECT * FROM docs WHERE docs MATCH 'sqlite OR database'
! 334: } [list $R(1) $R(2) $R(3)]
! 335: read_test 1.5.1.7 {
! 336: SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite'
! 337: } [list $R(1)]
! 338: read_test 1.5.1.8 {
! 339: SELECT * FROM docs WHERE docs MATCH 'database and sqlite'
! 340: } {}
! 341:
! 342: write_test 1.5.2.1 docs_content {
! 343: INSERT INTO docs
! 344: SELECT 'sqlite is also a library' UNION ALL
! 345: SELECT 'library software'
! 346: }
! 347: read_test 1.5.2.2 {
! 348: SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library'
! 349: } {3 4 5}
! 350: read_test 1.5.2.3 {
! 351: SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'
! 352: UNION
! 353: SELECT docid FROM docs WHERE docs MATCH 'library'
! 354: } {3 4 5}
! 355: write_test 1.5.2.4 docs_content {
! 356: INSERT INTO docs
! 357: SELECT 'the sqlite library runs on linux' UNION ALL
! 358: SELECT 'as does the sqlite database (on linux)' UNION ALL
! 359: SELECT 'the sqlite database is accessed by the sqlite library'
! 360: }
! 361: read_test 1.5.2.2 {
! 362: SELECT docid FROM docs
! 363: WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';
! 364: } {6 7}
! 365: read_test 1.5.2.3 {
! 366: SELECT docid FROM docs WHERE docs MATCH 'linux'
! 367: INTERSECT
! 368: SELECT docid FROM (
! 369: SELECT docid FROM docs WHERE docs MATCH '"sqlite library"'
! 370: UNION
! 371: SELECT docid FROM docs WHERE docs MATCH '"sqlite database"'
! 372: );
! 373: } {6 7}
! 374:
! 375: ##########################################################################
! 376: # Test the examples in section 3.2 (set operators with standard syntax).
! 377: # These tests reuse the table populated by the block above.
! 378: #
! 379: set sqlite_fts3_enable_parentheses 0
! 380: read_test 1.6.1.1 {
! 381: SELECT * FROM docs WHERE docs MATCH 'sqlite -database'
! 382: } {{sqlite is a software system} {sqlite is also a library} {the sqlite library runs on linux}}
! 383: read_test 1.6.1.2 {
! 384: SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library'
! 385: } {{sqlite is also a library} {the sqlite library runs on linux} {the sqlite database is accessed by the sqlite library}}
! 386:
! 387: set sqlite_fts3_enable_parentheses 1
! 388: read_test 1.6.1.3 {
! 389: SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library'
! 390: } {{sqlite is a software system} {sqlite is a database} {sqlite is also a library} {the sqlite library runs on linux} {as does the sqlite database (on linux)} {the sqlite database is accessed by the sqlite library}}
! 391: read_test 1.6.1.4 {
! 392: SELECT * FROM docs WHERE docs MATCH '(sqlite OR database) library'
! 393: } {{sqlite is also a library} {the sqlite library runs on linux} {the sqlite database is accessed by the sqlite library}}
! 394: set sqlite_fts3_enable_parentheses 0
! 395: ddl_test 1.6.1.5 { DROP TABLE docs }
! 396:
! 397: ##########################################################################
! 398: # Test the examples in section 4 (auxillary functions).
! 399: #
! 400: ddl_test 1.7.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) }
! 401:
! 402: write_test 1.7.1.2 mail_content {
! 403: INSERT INTO mail VALUES(
! 404: 'hello world', 'This message is a hello world message.');
! 405: }
! 406: write_test 1.7.1.3 mail_content {
! 407: INSERT INTO mail VALUES(
! 408: 'urgent: serious', 'This mail is seen as a more serious mail');
! 409: }
! 410:
! 411: read_test 1.7.1.4 {
! 412: SELECT offsets(mail) FROM mail WHERE mail MATCH 'world';
! 413: } {{0 0 6 5 1 0 24 5}}
! 414: read_test 1.7.1.5 {
! 415: SELECT offsets(mail) FROM mail WHERE mail MATCH 'message'
! 416: } {{1 0 5 7 1 0 30 7}}
! 417: read_test 1.7.1.6 {
! 418: SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"'
! 419: } {{1 0 28 7 1 1 36 4}}
! 420:
! 421: ddl_test 1.7.2.1 { CREATE VIRTUAL TABLE text USING fts3() }
! 422:
! 423: write_test 1.7.2.2 text_content {
! 424: INSERT INTO text VALUES('
! 425: During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops, minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature increases. Northeasterly winds 15-30 km/hr.
! 426: ');
! 427: }
! 428:
! 429: read_test 1.7.2.3 {
! 430: SELECT snippet(text) FROM text WHERE text MATCH 'cold'
! 431: } {{<b>...</b>cool elsewhere, minimum temperature 17-20oC. <b>Cold</b> to very <b>cold</b> on mountaintops, minimum temperature 6<b>...</b>}}
! 432:
! 433: read_test 1.7.2.4 {
! 434: SELECT snippet(text, '[', ']', '...') FROM text WHERE text MATCH '"min* tem*"'
! 435: } {{...the upper portion, [minimum] [temperature] 14-16oC and cool elsewhere, [minimum] [temperature] 17-20oC. Cold...}}
! 436:
! 437: ddl_test 1.7.3.1 { DROP TABLE IF EXISTS t1 }
! 438: ddl_test 1.7.3.2 { CREATE VIRTUAL TABLE t1 USING fts3(a, b) }
! 439: write_test 1.7.3.3 t1_content {
! 440: INSERT INTO t1 VALUES(
! 441: 'transaction default models default', 'Non transaction reads');
! 442: }
! 443: write_test 1.7.3.4 t1_content {
! 444: INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
! 445: }
! 446: write_test 1.7.3.5 t1_content {
! 447: INSERT INTO t1 VALUES('single request', 'default data');
! 448: }
! 449: read_test 1.7.3.6 {
! 450: SELECT mit(matchinfo(t1)) FROM t1
! 451: WHERE t1 MATCH 'default transaction "these semantics"';
! 452: } {{3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1}}
! 453:
! 454: ##########################################################################
! 455: # Test the example in section 5 (custom tokenizers).
! 456: #
! 457: ddl_test 1.8.1.1 { CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple) }
! 458: write_test 1.8.1.2 simple_content {
! 459: INSERT INTO simple VALUES('Right now they''re very frustrated')
! 460: }
! 461: read_test 1.8.1.3 {SELECT docid FROM simple WHERE simple MATCH 'Frustrated'} {1}
! 462: read_test 1.8.1.4 {SELECT docid FROM simple WHERE simple MATCH 'Frustration'} {}
! 463:
! 464: ddl_test 1.8.2.1 { CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter) }
! 465: write_test 1.8.2.2 porter_content {
! 466: INSERT INTO porter VALUES('Right now they''re very frustrated')
! 467: }
! 468: read_test 1.8.2.4 {
! 469: SELECT docid FROM porter WHERE porter MATCH 'Frustration'
! 470: } {1}
! 471:
! 472: }
! 473: # End of tests of example code in fts3.html
! 474: #-------------------------------------------------------------------------
! 475:
! 476: #-------------------------------------------------------------------------
! 477: # Test that errors in the arguments passed to the snippet and offsets
! 478: # functions are handled correctly.
! 479: #
! 480: set DO_MALLOC_TEST 0
! 481: ddl_test 2.1.0 { DROP TABLE IF EXISTS t1 }
! 482: ddl_test 2.1.1 { CREATE VIRTUAL TABLE t1 USING fts3(a, b) }
! 483: write_test 2.1.2 t1_content {
! 484: INSERT INTO t1 VALUES('one two three', x'A1B2C3D4E5F6');
! 485: }
! 486: error_test 2.1.3 {
! 487: SELECT offsets(a) FROM t1 WHERE a MATCH 'one'
! 488: } {illegal first argument to offsets}
! 489: error_test 2.1.4 {
! 490: SELECT offsets(b) FROM t1 WHERE a MATCH 'one'
! 491: } {illegal first argument to offsets}
! 492: error_test 2.1.5 {
! 493: SELECT optimize(a) FROM t1 LIMIT 1
! 494: } {illegal first argument to optimize}
! 495: error_test 2.1.6 {
! 496: SELECT snippet(a) FROM t1 WHERE a MATCH 'one'
! 497: } {illegal first argument to snippet}
! 498: error_test 2.1.7 {
! 499: SELECT snippet() FROM t1 WHERE a MATCH 'one'
! 500: } {unable to use function snippet in the requested context}
! 501: error_test 2.1.8 {
! 502: SELECT snippet(a, b, 'A', 'B', 'C', 'D', 'E') FROM t1 WHERE a MATCH 'one'
! 503: } {wrong number of arguments to function snippet()}
! 504: #-------------------------------------------------------------------------
! 505:
! 506: #-------------------------------------------------------------------------
! 507: # Test the effect of an OOM error while installing the FTS3 module (i.e.
! 508: # opening a database handle). This case was not tested by the OOM testing
! 509: # of the document examples above.
! 510: #
! 511: do_malloc_test e_fts3-3 -tclbody {
! 512: if {[catch {sqlite3 db test.db}]} { error "out of memory" }
! 513: }
! 514: #-------------------------------------------------------------------------
! 515:
! 516: #-------------------------------------------------------------------------
! 517: # Verify the return values of the optimize() function. If no error occurs,
! 518: # the returned value should be "Index optimized" if the data structure
! 519: # was modified, or "Index already optimal" if it were not.
! 520: #
! 521: set DO_MALLOC_TEST 0
! 522: ddl_test 4.1 { CREATE VIRTUAL TABLE t4 USING fts3(a, b) }
! 523: write_test 4.2 t4_content {
! 524: INSERT INTO t4 VALUES('In Xanadu', 'did Kubla Khan');
! 525: }
! 526: write_test 4.3 t4_content {
! 527: INSERT INTO t4 VALUES('a stately pleasure', 'dome decree');
! 528: }
! 529: do_test e_fts3-4.4 {
! 530: execsql { SELECT optimize(t4) FROM t4 LIMIT 1 }
! 531: } {{Index optimized}}
! 532: do_test e_fts3-4.5 {
! 533: execsql { SELECT optimize(t4) FROM t4 LIMIT 1 }
! 534: } {{Index already optimal}}
! 535: #-------------------------------------------------------------------------
! 536:
! 537: #-------------------------------------------------------------------------
! 538: # Test that the snippet function appears to work correctly with 1, 2, 3
! 539: # or 4 arguments passed to it.
! 540: #
! 541: set DO_MALLOC_TEST 0
! 542: ddl_test 5.1 { CREATE VIRTUAL TABLE t5 USING fts3(x) }
! 543: write_test 5.2 t5_content {
! 544: INSERT INTO t5 VALUES('In Xanadu did Kubla Khan A stately pleasure-dome decree Where Alph, the sacred river, ran Through caverns measureless to man Down to a sunless sea. So twice five miles of fertile ground With walls and towers were girdled round : And there were gardens bright with sinuous rills, Where blossomed many an incense-bearing tree ; And here were forests ancient as the hills, Enfolding sunny spots of greenery.');
! 545: }
! 546: read_test 5.3 {
! 547: SELECT snippet(t5) FROM t5 WHERE t5 MATCH 'miles'
! 548: } {{<b>...</b>to a sunless sea. So twice five <b>miles</b> of fertile ground With walls and towers<b>...</b>}}
! 549: read_test 5.4 {
! 550: SELECT snippet(t5, '<i>') FROM t5 WHERE t5 MATCH 'miles'
! 551: } {{<b>...</b>to a sunless sea. So twice five <i>miles</b> of fertile ground With walls and towers<b>...</b>}}
! 552: read_test 5.5 {
! 553: SELECT snippet(t5, '<i>', '</i>') FROM t5 WHERE t5 MATCH 'miles'
! 554: } {{<b>...</b>to a sunless sea. So twice five <i>miles</i> of fertile ground With walls and towers<b>...</b>}}
! 555: read_test 5.6 {
! 556: SELECT snippet(t5, '<i>', '</i>', 'XXX') FROM t5 WHERE t5 MATCH 'miles'
! 557: } {{XXXto a sunless sea. So twice five <i>miles</i> of fertile ground With walls and towersXXX}}
! 558: #-------------------------------------------------------------------------
! 559:
! 560: #-------------------------------------------------------------------------
! 561: # Test that an empty MATCH expression returns an empty result set. As
! 562: # does passing a NULL value as a MATCH expression.
! 563: #
! 564: set DO_MALLOC_TEST 0
! 565: ddl_test 6.1 { CREATE VIRTUAL TABLE t6 USING fts3(x) }
! 566: write_test 6.2 t5_content { INSERT INTO t6 VALUES('a'); }
! 567: write_test 6.3 t5_content { INSERT INTO t6 VALUES('b'); }
! 568: write_test 6.4 t5_content { INSERT INTO t6 VALUES('c'); }
! 569: read_test 6.5 { SELECT * FROM t6 WHERE t6 MATCH '' } {}
! 570: read_test 6.6 { SELECT * FROM t6 WHERE x MATCH '' } {}
! 571: read_test 6.7 { SELECT * FROM t6 WHERE t6 MATCH NULL } {}
! 572: read_test 6.8 { SELECT * FROM t6 WHERE x MATCH NULL } {}
! 573: #-------------------------------------------------------------------------
! 574:
! 575: #-------------------------------------------------------------------------
! 576: # Test a few facets of the FTS3 xFilter() callback implementation:
! 577: #
! 578: # 1. That the sqlite3_index_constraint.usable flag is respected.
! 579: #
! 580: # 2. That it is an error to use the "docid" or "rowid" column of
! 581: # an FTS3 table as the LHS of a MATCH operator.
! 582: #
! 583: # 3. That it is an error to AND together two MATCH expressions in
! 584: # that refer to a single FTS3 table in a WHERE clause.
! 585: #
! 586: #
! 587: set DO_MALLOC_TEST 0
! 588: ddl_test 7.1.1 { CREATE VIRTUAL TABLE t7 USING fts3(a) }
! 589: ddl_test 7.1.2 { CREATE VIRTUAL TABLE t8 USING fts3(b) }
! 590: write_test 7.1.3 t7_content { INSERT INTO t7(docid, a) VALUES(4,'number four') }
! 591: write_test 7.1.4 t7_content { INSERT INTO t7(docid, a) VALUES(5,'number five') }
! 592: write_test 7.1.5 t8_content { INSERT INTO t8(docid, b) VALUES(4,'letter D') }
! 593: write_test 7.1.6 t8_content { INSERT INTO t8(docid, b) VALUES(5,'letter E') }
! 594: read_test 7.1.7 {
! 595: SELECT a || ':' || b FROM t7 JOIN t8 USING(docid)
! 596: } {{number four:letter D} {number five:letter E}}
! 597:
! 598: error_test 7.2.1 {
! 599: SELECT * FROM t7 WHERE docid MATCH 'number'
! 600: } {unable to use function MATCH in the requested context}
! 601: error_test 7.2.2 {
! 602: SELECT * FROM t7 WHERE rowid MATCH 'number'
! 603: } {unable to use function MATCH in the requested context}
! 604:
! 605: error_test 7.3.1 {
! 606: SELECT * FROM t7 WHERE a MATCH 'number' AND a MATCH 'four'
! 607: } {unable to use function MATCH in the requested context}
! 608: error_test 7.3.2 {
! 609: SELECT * FROM t7, t8 WHERE a MATCH 'number' AND a MATCH 'four'
! 610: } {unable to use function MATCH in the requested context}
! 611: error_test 7.3.3 {
! 612: SELECT * FROM t7, t8 WHERE b MATCH 'letter' AND b MATCH 'd'
! 613: } {unable to use function MATCH in the requested context}
! 614: read_test 7.3.4 {
! 615: SELECT * FROM t7, t8 WHERE a MATCH 'number' AND b MATCH 'letter'
! 616: } {{number four} {letter D} {number four} {letter E} {number five} {letter D} {number five} {letter E}}
! 617: read_test 7.3.5 {
! 618: SELECT * FROM t7 WHERE a MATCH 'number' AND docid = 4
! 619: } {{number four}}
! 620:
! 621: #-------------------------------------------------------------------------
! 622: # Test the quoting of FTS3 table column names. Names may be quoted using
! 623: # any of "", '', ``` or [].
! 624: #
! 625: set DO_MALLOC_TEST 0
! 626: ddl_test 8.1.1 { CREATE VIRTUAL TABLE t9a USING fts3("c1", [c2]) }
! 627: ddl_test 8.1.2 { CREATE VIRTUAL TABLE t9b USING fts3('c1', `c2`) }
! 628: read_test 8.1.3 { PRAGMA table_info(t9a) } {0 c1 {} 0 {} 0 1 c2 {} 0 {} 0}
! 629: read_test 8.1.4 { PRAGMA table_info(t9b) } {0 c1 {} 0 {} 0 1 c2 {} 0 {} 0}
! 630: ddl_test 8.2.1 { CREATE VIRTUAL TABLE t9c USING fts3("c""1", 'c''2') }
! 631: read_test 8.2.2 { PRAGMA table_info(t9c) } {0 c\"1 {} 0 {} 0 1 c'2 {} 0 {} 0}
! 632: #-------------------------------------------------------------------------
! 633:
! 634: #-------------------------------------------------------------------------
! 635: # Test that FTS3 tables can be renamed using the ALTER RENAME command.
! 636: # OOM errors are tested during ALTER RENAME commands also.
! 637: #
! 638: foreach DO_MALLOC_TEST {0 1 2} {
! 639: db close
! 640: forcedelete test.db test.db-journal
! 641: sqlite3 db test.db
! 642: if {$DO_MALLOC_TEST} { sqlite3_db_config_lookaside db 0 0 0 }
! 643:
! 644: ddl_test 9.1.1 { CREATE VIRTUAL TABLE t10 USING fts3(x) }
! 645: write_test 9.1.2 t10_content { INSERT INTO t10 VALUES('fts3 tables') }
! 646: write_test 9.1.3 t10_content { INSERT INTO t10 VALUES('are renameable') }
! 647:
! 648: read_test 9.1.4 {
! 649: SELECT * FROM t10 WHERE t10 MATCH 'table*'
! 650: } {{fts3 tables}}
! 651: read_test 9.1.5 {
! 652: SELECT * FROM t10 WHERE x MATCH 'rename*'
! 653: } {{are renameable}}
! 654:
! 655: ddl_test 9.1.6 { ALTER TABLE t10 RENAME TO t11 }
! 656:
! 657: read_test 9.1.7 {
! 658: SELECT * FROM t11 WHERE t11 MATCH 'table*'
! 659: } {{fts3 tables}}
! 660: read_test 9.1.8 {
! 661: SELECT * FROM t11 WHERE x MATCH 'rename*'
! 662: } {{are renameable}}
! 663: }
! 664: #-------------------------------------------------------------------------
! 665:
! 666: #-------------------------------------------------------------------------
! 667: # Test a couple of cases involving corrupt data structures:
! 668: #
! 669: # 1) A case where a document referenced by the full-text index is
! 670: # not present in the %_content table.
! 671: #
! 672: # 2) A badly formatted b-tree segment node.
! 673: #
! 674: set DO_MALLOC_TEST 0
! 675: ddl_test 10.1.1 { CREATE VIRTUAL TABLE ta USING fts3 }
! 676: write_test 10.1.2 ta_content {
! 677: INSERT INTO ta VALUES('During a summer vacation in 1790') }
! 678: write_test 10.1.3 ta_content {
! 679: INSERT INTO ta VALUES('Wordsworth went on a walking tour') }
! 680: write_test 10.1.4 ta_content { DELETE FROM ta_content WHERE rowid = 2 }
! 681: read_test 10.1.5 {
! 682: SELECT * FROM ta WHERE ta MATCH 'summer'
! 683: } {{During a summer vacation in 1790}}
! 684: error_test 10.1.6 {
! 685: SELECT * FROM ta WHERE ta MATCH 'walking'
! 686: } {database disk image is malformed}
! 687:
! 688: write_test 10.2.1 ta_content { DELETE FROM ta }
! 689: write_test 10.2.2 ta_content {
! 690: INSERT INTO ta VALUES('debate demonstrated the rising difficulty') }
! 691: write_test 10.2.3 ta_content {
! 692: INSERT INTO ta VALUES('Google released its browser beta') }
! 693:
! 694: set blob [db one {SELECT root FROM ta_segdir WHERE rowid = 2}]
! 695: binary scan $blob "a6 a3 a*" start middle end
! 696: set middle "\x0E\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\x06\x06"
! 697: set blob [binary format "a6 a* a*" $start $middle $end]
! 698: write_test 10.2.4 ta_segdir {
! 699: UPDATE ta_segdir SET root = $blob WHERE rowid = 2
! 700: }
! 701: error_test 10.2.5 {
! 702: SELECT * FROM ta WHERE ta MATCH 'beta'
! 703: } {database disk image is malformed}
! 704:
! 705:
! 706: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_fts3.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