Annotation of embedaddon/sqlite3/test/fts3query.test, revision 1.1.1.1

1.1       misho       1: # 2009 December 20
                      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 contains tests of fts3 queries that have been useful during
                     13: # the development process as well as some that have been useful in tracking
                     14: # down bugs. They are not focused on any particular functionality.
                     15: #
                     16: 
                     17: set testdir [file dirname $argv0]
                     18: source $testdir/tester.tcl
                     19: 
                     20: # If this build does not include FTS3, skip the tests in this file.
                     21: #
                     22: ifcapable !fts3 { finish_test ; return }
                     23: source $testdir/malloc_common.tcl
                     24: source $testdir/fts3_common.tcl
                     25: set DO_MALLOC_TEST 0
                     26: 
                     27: set testprefix fts3query
                     28: 
                     29: do_test fts3query-1.1 {
                     30:   execsql {
                     31:     CREATE VIRTUAL TABLE t1 USING fts3(x);
                     32:     BEGIN;
                     33:       INSERT INTO t1 VALUES('The source code for SQLite is in the public');
                     34:   }
                     35: } {}
                     36: 
                     37: do_select_test fts3query-1.2 {
                     38:   SELECT * FROM t1;
                     39: } {{The source code for SQLite is in the public}}
                     40: do_select_test fts3query-1.3 {
                     41:   SELECT * FROM t1 WHERE t1 MATCH 'sqlite'
                     42: } {{The source code for SQLite is in the public}}
                     43: 
                     44: do_test fts3query-1.4 { execsql {COMMIT} } {}
                     45: 
                     46: do_select_test fts3query-1.5 {
                     47:   SELECT * FROM t1;
                     48: } {{The source code for SQLite is in the public}}
                     49: do_select_test fts3query-1.6 {
                     50:   SELECT * FROM t1 WHERE t1 MATCH 'sqlite'
                     51: } {{The source code for SQLite is in the public}}
                     52: 
                     53: 
                     54: set sqlite_fts3_enable_parentheses 1
                     55: do_test fts3query-2.1 {
                     56:   execsql {
                     57:     CREATE VIRTUAL TABLE zoink USING fts3;
                     58:     INSERT INTO zoink VALUES('The apple falls far from the tree');
                     59:   }
                     60: } {}
                     61: do_test fts3query-2.2 {
                     62:   execsql {
                     63:     SELECT docid FROM zoink WHERE zoink MATCH '(apple oranges) AND apple'
                     64:   }
                     65: } {}
                     66: do_test fts3query-2.3 {
                     67:   execsql {
                     68:     SELECT docid FROM zoink WHERE zoink MATCH 'apple AND (oranges apple)'
                     69:   }
                     70: } {}
                     71: set sqlite_fts3_enable_parentheses 0
                     72: 
                     73: do_test fts3query-3.1 {
                     74:   execsql {
                     75:     CREATE VIRTUAL TABLE foobar using FTS3(description, tokenize porter);
                     76:     INSERT INTO foobar (description) values ('
                     77:       Filed under: Emerging Technologies, EV/Plug-in, Hybrid, Chevrolet, GM, 
                     78:       ZENN 2011 Chevy Volt - Click above for high-res image gallery There are 
                     79:       16 days left in the month of December. Besides being time for most 
                     80:       Americans to kick their Christmas shopping sessions into high gear and
                     81:       start planning their resolutions for 2010, it also means that there''s
                     82:       precious little time for EEStor to "deliver functional technology" to
                     83:       Zenn Motors as promised. Still, the promises held out by the secretive
                     84:       company are too great for us to forget about entirely. We''d love for
                     85:       EEStor''s claims to be independently verified and proven accurate, as
                     86:       would just about anyone else looking to break free of petroleum in fav
                     87:     '); 
                     88:   }
                     89: } {}
                     90: 
                     91: do_test fts3query-3.2 {
                     92:   execsql { SELECT docid FROM foobar WHERE description MATCH '"high sp d"' }
                     93: } {}
                     94: 
                     95: proc mit {blob} {
                     96:   set scan(littleEndian) i*
                     97:   set scan(bigEndian) I*
                     98:   binary scan $blob $scan($::tcl_platform(byteOrder)) r
                     99:   return $r
                    100: }
                    101: db func mit mit
                    102: 
                    103: do_test fts3query-3.3 {
                    104:   execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }
                    105: } {{1 1 3 3 1}}
                    106: 
                    107: # The following tests check that ticket 775b39dd3c has been fixed.
                    108: #
                    109: do_test fts3query-4.1 {
                    110:   execsql {
                    111:     DROP TABLE IF EXISTS t1;
                    112:     CREATE TABLE t1(number INTEGER PRIMARY KEY, date);
                    113:     CREATE INDEX i1 ON t1(date);
                    114:     CREATE VIRTUAL TABLE ft USING fts3(title);
                    115:     CREATE TABLE bt(title);
                    116:   }
                    117: } {}
                    118: do_eqp_test fts3query-4.2 {
                    119:   SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date
                    120: } {
                    121:   0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
                    122:   0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~0 rows)}
                    123: }
                    124: do_eqp_test fts3query-4.3 {
                    125:   SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date
                    126: } {
                    127:   0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
                    128:   0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~0 rows)}
                    129: }
                    130: do_eqp_test fts3query-4.4 {
                    131:   SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date
                    132: } {
                    133:   0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
                    134:   0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
                    135: }
                    136: do_eqp_test fts3query-4.5 {
                    137:   SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date
                    138: } {
                    139:   0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
                    140:   0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
                    141: }
                    142: 
                    143: 
                    144: # Test that calling matchinfo() with the wrong number of arguments, or with
                    145: # an invalid argument returns an error.
                    146: #
                    147: do_execsql_test 5.1 {
                    148:   CREATE VIRTUAL TABLE t2 USING FTS4;
                    149:   INSERT INTO t2 VALUES('it was the first time in history');
                    150: }
                    151: do_select_tests 5.2 -errorformat {
                    152:   wrong number of arguments to function %s()
                    153: } {
                    154:   1 "SELECT matchinfo() FROM t2 WHERE t2 MATCH 'history'"       matchinfo
                    155:   3 "SELECT snippet(t2, 1, 2, 3, 4, 5, 6) FROM t2 WHERE t2 MATCH 'history'" 
                    156:     snippet
                    157: }
                    158: do_select_tests 5.3 -errorformat {
                    159:   illegal first argument to %s
                    160: } {
                    161:   1 "SELECT matchinfo(content) FROM t2 WHERE t2 MATCH 'history'" matchinfo
                    162:   2 "SELECT offsets(content) FROM t2 WHERE t2 MATCH 'history'"   offsets
                    163:   3 "SELECT snippet(content) FROM t2 WHERE t2 MATCH 'history'"   snippet
                    164:   4 "SELECT optimize(content) FROM t2 WHERE t2 MATCH 'history'"  optimize
                    165: }
                    166: do_execsql_test 5.4.0 { UPDATE t2_content SET c0content = X'1234' }
                    167: do_select_tests 5.4 -errorformat {
                    168:   illegal first argument to %s
                    169: } {
                    170:   1 "SELECT matchinfo(content) FROM t2 WHERE t2 MATCH 'history'" matchinfo
                    171:   2 "SELECT offsets(content) FROM t2 WHERE t2 MATCH 'history'"   offsets
                    172:   3 "SELECT snippet(content) FROM t2 WHERE t2 MATCH 'history'"   snippet
                    173:   4 "SELECT optimize(content) FROM t2 WHERE t2 MATCH 'history'"  optimize
                    174: }
                    175: do_catchsql_test 5.5.1 {
                    176:   SELECT matchinfo(t2, 'abc') FROM t2 WHERE t2 MATCH 'history'
                    177: } {1 {unrecognized matchinfo request: b}}
                    178: 
                    179: do_execsql_test 5.5 { DROP TABLE t2 }
                    180: 
                    181: 
                    182: # Test the snippet() function with 1 to 6 arguments.
                    183: # 
                    184: do_execsql_test 6.1 {
                    185:   CREATE VIRTUAL TABLE t3 USING FTS4(a, b);
                    186:   INSERT INTO t3 VALUES('no gestures', 'another intriguing discovery by observing the hand gestures (called beats) people make while speaking. Research has shown that such gestures do more than add visual emphasis to our words (many people gesture while they''re on the telephone, for example); it seems they actually help our brains find words');
                    187: }
                    188: do_select_tests 6.2 {
                    189:   1 "SELECT snippet(t3) FROM t3 WHERE t3 MATCH 'gestures'"
                    190:   {{<b>...</b>hand <b>gestures</b> (called beats) people make while speaking. Research has shown that such <b>gestures</b> do<b>...</b>}}
                    191: 
                    192:   2 "SELECT snippet(t3, 'XXX') FROM t3 WHERE t3 MATCH 'gestures'" 
                    193:   {{<b>...</b>hand XXXgestures</b> (called beats) people make while speaking. Research has shown that such XXXgestures</b> do<b>...</b>}}
                    194: 
                    195:   3 "SELECT snippet(t3, 'XXX', 'YYY') FROM t3 WHERE t3 MATCH 'gestures'" 
                    196:   {{<b>...</b>hand XXXgesturesYYY (called beats) people make while speaking. Research has shown that such XXXgesturesYYY do<b>...</b>}}
                    197: 
                    198:   4 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ') FROM t3 WHERE t3 MATCH 'gestures'" 
                    199:   {{ZZZhand XXXgesturesYYY (called beats) people make while speaking. Research has shown that such XXXgesturesYYY doZZZ}}
                    200: 
                    201:   5 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1) FROM t3 WHERE t3 MATCH 'gestures'" 
                    202:   {{ZZZhand XXXgesturesYYY (called beats) people make while speaking. Research has shown that such XXXgesturesYYY doZZZ}}
                    203: 
                    204:   6 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 0) FROM t3 WHERE t3 MATCH 'gestures'" 
                    205:   {{no XXXgesturesYYY}}
                    206: 
                    207:   7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'" 
                    208:   {{ZZZthe hand XXXgesturesYYY (called beatsZZZ}}
                    209: }
                    210: 
                    211: 
                    212: finish_test
                    213: 

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>