Home | History | Annotate | Download | only in test 
      1 # 2006 September 13
      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 FTS1 module.
     13 #
     14 # $Id: fts1b.test,v 1.4 2006/09/18 02:12:48 drh Exp $
     15 #
     16 
     17 set testdir [file dirname $argv0]
     18 source $testdir/tester.tcl
     19 
     20 # If SQLITE_ENABLE_FTS1 is defined, omit this file.
     21 ifcapable !fts1 {
     22   finish_test
     23   return
     24 }
     25 
     26 # Fill the full-text index "t1" with phrases in english, spanish,
     27 # and german.  For the i-th row, fill in the names for the bits
     28 # that are set in the value of i.  The least significant bit is
     29 # 1.  For example,  the value 5 is 101 in binary which will be
     30 # converted to "one three" in english.
     31 #
     32 proc fill_multilanguage_fulltext_t1 {} {
     33   set english {one two three four five}
     34   set spanish {un dos tres cuatro cinco}
     35   set german {eine zwei drei vier funf}
     36   
     37   for {set i 1} {$i<=31} {incr i} {
     38     set cmd "INSERT INTO t1 VALUES"
     39     set vset {}
     40     foreach lang {english spanish german} {
     41       set words {}
     42       for {set j 0; set k 1} {$j<5} {incr j; incr k $k} {
     43         if {$k&$i} {lappend words [lindex [set $lang] $j]}
     44       }
     45       lappend vset "'$words'"
     46     }
     47     set sql "INSERT INTO t1(english,spanish,german) VALUES([join $vset ,])"
     48     # puts $sql
     49     db eval $sql
     50   }
     51 }
     52 
     53 # Construct a full-text search table containing five keywords:
     54 # one, two, three, four, and five, in various combinations.  The
     55 # rowid for each will be a bitmask for the elements it contains.
     56 #
     57 db eval {
     58   CREATE VIRTUAL TABLE t1 USING fts1(english,spanish,german);
     59 }
     60 fill_multilanguage_fulltext_t1
     61 
     62 do_test fts1b-1.1 {
     63   execsql {SELECT rowid FROM t1 WHERE english MATCH 'one'}
     64 } {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
     65 do_test fts1b-1.2 {
     66   execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'}
     67 } {}
     68 do_test fts1b-1.3 {
     69   execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'}
     70 } {}
     71 do_test fts1b-1.4 {
     72   execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one'}
     73 } {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
     74 do_test fts1b-1.5 {
     75   execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'}
     76 } {7 15 23 31}
     77 do_test fts1b-1.6 {
     78   execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1}
     79 } {one un eine}
     80 do_test fts1b-1.7 {
     81   execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'}
     82 } {}
     83 
     84 do_test fts1b-2.1 {
     85   execsql {
     86     CREATE VIRTUAL TABLE t2 USING fts1(from,to);
     87     INSERT INTO t2([from],[to]) VALUES ('one two three', 'four five six');
     88     SELECT [from], [to] FROM t2
     89   }
     90 } {{one two three} {four five six}}
     91 
     92 
     93 # Compute an SQL string that contains the words one, two, three,... to
     94 # describe bits set in the value $i.  Only the lower 5 bits are examined.
     95 #
     96 proc wordset {i} {
     97   set x {}
     98   for {set j 0; set k 1} {$j<5} {incr j; incr k $k} {
     99     if {$k&$i} {lappend x [lindex {one two three four five} $j]}
    100   }
    101   return '$x'
    102 }
    103 
    104 # Create a new FTS table with three columns:
    105 #
    106 #    norm:      words for the bits of rowid
    107 #    plusone:   words for the bits of rowid+1
    108 #    invert:    words for the bits of ~rowid
    109 #
    110 db eval {
    111    CREATE VIRTUAL TABLE t4 USING fts1([norm],'plusone',"invert");
    112 }
    113 for {set i 1} {$i<=15} {incr i} {
    114   set vset [list [wordset $i] [wordset [expr {$i+1}]] [wordset [expr {~$i}]]]
    115   db eval "INSERT INTO t4(norm,plusone,invert) VALUES([join $vset ,]);"
    116 }
    117 
    118 do_test fts1b-4.1 {
    119   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one'}
    120 } {1 3 5 7 9 11 13 15}
    121 do_test fts1b-4.2 {
    122   execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'}
    123 } {1 3 5 7 9 11 13 15}
    124 do_test fts1b-4.3 {
    125   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'one'}
    126 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}
    127 do_test fts1b-4.4 {
    128   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'}
    129 } {2 4 6 8 10 12 14}
    130 do_test fts1b-4.5 {
    131   execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'}
    132 } {2 4 6 8 10 12 14}
    133 do_test fts1b-4.6 {
    134   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'}
    135 } {1 5 9 13}
    136 do_test fts1b-4.7 {
    137   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one two'}
    138 } {1 3 5 7 9 11 13 15}
    139 do_test fts1b-4.8 {
    140   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'}
    141 } {1 5 9 13}
    142 do_test fts1b-4.9 {
    143   execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'two norm:one'}
    144 } {1 3 5 7 9 11 13 15}
    145 
    146 
    147 finish_test
    148