File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / fts3prefix.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    1: # 2011 May 04
    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 FTS3 module.
   13: #
   14: 
   15: set testdir [file dirname $argv0]
   16: source $testdir/tester.tcl
   17: set testprefix fts3prefix
   18: 
   19: ifcapable !fts3 {
   20:   finish_test
   21:   return
   22: }
   23: 
   24: # This proc tests that the prefixes index appears to represent the same content
   25: # as the terms index.
   26: #
   27: proc fts3_terms_and_prefixes {db tbl prefixlengths} {
   28: 
   29:   set iIndex 0
   30:   foreach len $prefixlengths {
   31:     incr iIndex
   32:     $db eval {
   33:       DROP TABLE IF EXISTS fts3check1;
   34:       DROP TABLE IF EXISTS fts3check2;
   35:     }
   36:     $db eval "CREATE VIRTUAL TABLE fts3check1 USING fts4term($tbl, 0);"
   37:     $db eval "CREATE VIRTUAL TABLE fts3check2 USING fts4term($tbl, $iIndex);"
   38: 
   39:     $db eval {
   40:       DROP TABLE IF EXISTS temp.terms;
   41:       DROP TABLE IF EXISTS temp.prefixes;
   42:       CREATE TEMP TABLE terms AS SELECT * FROM fts3check1;
   43:       CREATE TEMP TABLE prefixes AS SELECT * FROM fts3check2;
   44:       CREATE INDEX temp.idx ON prefixes(term);
   45:       DROP TABLE fts3check1;
   46:       DROP TABLE fts3check2;
   47:     }
   48: 
   49:     set nExpect 0
   50:     $db eval { SELECT term, docid, col, pos FROM temp.terms } a {
   51:       if {[string length $a(term)]<$len} continue
   52:       incr nExpect
   53:       set prefix [string range $a(term) 0 [expr $len-1]]
   54:       set r [$db one { 
   55:         SELECT count(*) FROM temp.prefixes WHERE 
   56:         term = $prefix AND docid = $a(docid) AND col = $a(col) AND pos = $a(pos)
   57:       }]
   58:       if {$r != 1} {
   59:         error "$t, $a(docid), $a(col), $a(pos)"
   60:       }
   61:     }
   62: 
   63:     set nCount [$db one {SELECT count(*) FROM temp.prefixes}]
   64:     if {$nCount != $nExpect} {
   65:       error "prefixes.count(*) is $nCount expected $nExpect"
   66:     }
   67:   
   68:     execsql { DROP TABLE temp.prefixes }
   69:     execsql { DROP TABLE temp.terms }
   70: 
   71:     set list [list]
   72:     $db eval "
   73:       SELECT sum( 1 << (16*(level%1024)) ) AS total, (level/1024) AS tree 
   74:       FROM ${tbl}_segdir GROUP BY tree
   75:     " {
   76:       lappend list [list $total $tree]
   77:     }
   78: 
   79:     if { [lsort -integer -index 0 $list] != [lsort -integer -index 1 $list] } {
   80:       error "inconsistent tree structures: $list"
   81:     }
   82:   }
   83: 
   84:   return ""
   85: }
   86: proc fts3_tap_test {tn db tbl lens} {
   87:   uplevel [list do_test $tn [list fts3_terms_and_prefixes $db $tbl $lens] ""]
   88: }
   89: 
   90: #-------------------------------------------------------------------------
   91: # Test cases 1.* are a sanity check. They test that the prefixes index is
   92: # being constructed correctly for the simplest possible case.
   93: #
   94: do_execsql_test 1.1 {
   95:   CREATE VIRTUAL TABLE t1 USING fts4(prefix='1,3,6');
   96: 
   97:   CREATE VIRTUAL TABLE p1 USING fts4term(t1, 1);
   98:   CREATE VIRTUAL TABLE p2 USING fts4term(t1, 2);
   99:   CREATE VIRTUAL TABLE p3 USING fts4term(t1, 3);
  100:   CREATE VIRTUAL TABLE terms USING fts4term(t1);
  101: }
  102: do_execsql_test 1.2 {
  103:   INSERT INTO t1 VALUES('sqlite mysql firebird');
  104: }
  105: do_execsql_test 1.3.1 { SELECT term FROM p1 } {f m s}
  106: do_execsql_test 1.3.2 { SELECT term FROM p2 } {fir mys sql}
  107: do_execsql_test 1.3.3 { SELECT term FROM p3 } {firebi sqlite}
  108: do_execsql_test 1.4 {
  109:   SELECT term FROM terms;
  110: } {firebird mysql sqlite}
  111: 
  112: fts3_tap_test 1.5 db t1 {1 3 6}
  113: 
  114: #-------------------------------------------------------------------------
  115: # A slightly more complicated dataset. This test also verifies that DELETE
  116: # operations do not corrupt the prefixes index.
  117: #
  118: do_execsql_test 2.1 {
  119:   INSERT INTO t1 VALUES('FTS3 and FTS4 are an SQLite virtual table modules');
  120:   INSERT INTO t1 VALUES('that allows users to perform full-text searches on');
  121:   INSERT INTO t1 VALUES('a set of documents. The most common (and');
  122:   INSERT INTO t1 VALUES('effective) way to describe full-text searches is');
  123:   INSERT INTO t1 VALUES('"what Google, Yahoo and Altavista do with');
  124:   INSERT INTO t1 VALUES('documents placed on the World Wide Web". Users');
  125:   INSERT INTO t1 VALUES('input a term, or series of terms, perhaps');
  126:   INSERT INTO t1 VALUES('connected by a binary operator or grouped together');
  127:   INSERT INTO t1 VALUES('into a phrase, and the full-text query system');
  128:   INSERT INTO t1 VALUES('finds the set of documents that best matches those');
  129:   INSERT INTO t1 VALUES('terms considering the operators and groupings the');
  130:   INSERT INTO t1 VALUES('user has specified. This article describes the');
  131:   INSERT INTO t1 VALUES('deployment and usage of FTS3 and FTS4.');
  132:   INSERT INTO t1 VALUES('FTS1 and FTS2 are obsolete full-text search');
  133:   INSERT INTO t1 VALUES('modules for SQLite. There are known issues with');
  134:   INSERT INTO t1 VALUES('these older modules and their use should be');
  135:   INSERT INTO t1 VALUES('avoided. Portions of the original FTS3 code were');
  136:   INSERT INTO t1 VALUES('contributed to the SQLite project by Scott Hess of');
  137:   INSERT INTO t1 VALUES('Google. It is now developed and maintained as part');
  138:   INSERT INTO t1 VALUES('of SQLite. ');
  139: }
  140: fts3_tap_test 2.2 db t1 {1 3 6}
  141: do_execsql_test 2.3 { DELETE FROM t1 WHERE docid%2; }
  142: fts3_tap_test 2.4 db t1 {1 3 6}
  143: 
  144: do_execsql_test 2.5 { INSERT INTO t1(t1) VALUES('optimize') }
  145: fts3_tap_test 2.6 db t1 {1 3 6}
  146: 
  147: do_execsql_test 3.1 {
  148:   CREATE VIRTUAL TABLE t2 USING fts4(prefix='1,2,3');
  149:   INSERT INTO t2 VALUES('On 12 September the wind direction turned and');
  150:   INSERT INTO t2 VALUES('William''s fleet sailed. A storm blew up and the');
  151:   INSERT INTO t2 VALUES('fleet was forced to take shelter at');
  152:   INSERT INTO t2 VALUES('Saint-Valery-sur-Somme and again wait for the wind');
  153:   INSERT INTO t2 VALUES('to change. On 27 September the Norman fleet');
  154:   INSERT INTO t2 VALUES('finally set sail, landing in England at Pevensey');
  155:   INSERT INTO t2 VALUES('Bay (Sussex) on 28 September. William then moved');
  156:   INSERT INTO t2 VALUES('to Hastings, a few miles to the east, where he');
  157:   INSERT INTO t2 VALUES('built a prefabricated wooden castle for a base of');
  158:   INSERT INTO t2 VALUES('operations. From there, he ravaged the hinterland');
  159:   INSERT INTO t2 VALUES('and waited for Harold''s return from the north.');
  160:   INSERT INTO t2 VALUES('On 12 September the wind direction turned and');
  161:   INSERT INTO t2 VALUES('William''s fleet sailed. A storm blew up and the');
  162:   INSERT INTO t2 VALUES('fleet was forced to take shelter at');
  163:   INSERT INTO t2 VALUES('Saint-Valery-sur-Somme and again wait for the wind');
  164:   INSERT INTO t2 VALUES('to change. On 27 September the Norman fleet');
  165:   INSERT INTO t2 VALUES('finally set sail, landing in England at Pevensey');
  166:   INSERT INTO t2 VALUES('Bay (Sussex) on 28 September. William then moved');
  167:   INSERT INTO t2 VALUES('to Hastings, a few miles to the east, where he');
  168:   INSERT INTO t2 VALUES('built a prefabricated wooden castle for a base of');
  169:   INSERT INTO t2 VALUES('operations. From there, he ravaged the hinterland');
  170:   INSERT INTO t2 VALUES('and waited for Harold''s return from the north.');
  171: }
  172: 
  173: fts3_tap_test 3.2 db t2 {1 2 3}
  174: do_execsql_test 3.3 { SELECT optimize(t2) FROM t2 LIMIT 1 } {{Index optimized}}
  175: fts3_tap_test 3.4 db t2 {1 2 3}
  176: 
  177: 
  178: #-------------------------------------------------------------------------
  179: # Simple tests for reading the prefix-index.
  180: #
  181: do_execsql_test 4.1 {
  182:   CREATE VIRTUAL TABLE t3 USING fts4(prefix="1,4");
  183:   INSERT INTO t3 VALUES('one two three');
  184:   INSERT INTO t3 VALUES('four five six');
  185:   INSERT INTO t3 VALUES('seven eight nine');
  186: }
  187: do_execsql_test 4.2 {
  188:   SELECT * FROM t3 WHERE t3 MATCH 'f*'
  189: } {{four five six}}
  190: do_execsql_test 4.3 {
  191:   SELECT * FROM t3 WHERE t3 MATCH 'four*'
  192: } {{four five six}}
  193: do_execsql_test 4.4 {
  194:   SELECT * FROM t3 WHERE t3 MATCH 's*'
  195: } {{four five six} {seven eight nine}}
  196: do_execsql_test 4.5 {
  197:   SELECT * FROM t3 WHERE t3 MATCH 'sev*'
  198: } {{seven eight nine}}
  199: do_execsql_test 4.6 {
  200:   SELECT * FROM t3 WHERE t3 MATCH 'one*'
  201: } {{one two three}}
  202: 
  203: #-------------------------------------------------------------------------
  204: # Syntax tests.
  205: #
  206: do_catchsql_test 5.1 {
  207:   CREATE VIRTUAL TABLE t4 USING fts4(prefix="abc");
  208: } {1 {error parsing prefix parameter: abc}}
  209: do_catchsql_test 5.2 {
  210:   CREATE VIRTUAL TABLE t4 USING fts4(prefix="");
  211: } {0 {}}
  212: 
  213: finish_test

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