AryaWu/sqlite

1# 2006 September 132#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is testing the FTS3 module.13#14# $Id: fts3ab.test,v 1.1 2007/08/20 17:38:42 shess Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# If SQLITE_ENABLE_FTS3 is defined, omit this file.21ifcapable !fts3 {22  finish_test23  return24}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 bits28# that are set in the value of i.  The least significant bit is29# 1.  For example,  the value 5 is 101 in binary which will be30# converted to "one three" in english.31#32proc 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 $sql49    db eval $sql50  }51}52 53# Construct a full-text search table containing five keywords:54# one, two, three, four, and five, in various combinations.  The55# rowid for each will be a bitmask for the elements it contains.56#57db eval {58  CREATE VIRTUAL TABLE t1 USING fts3(english,spanish,german);59}60fill_multilanguage_fulltext_t161 62do_test fts3ab-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}65do_test fts3ab-1.2 {66  execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'}67} {}68do_test fts3ab-1.3 {69  execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'}70} {}71do_test fts3ab-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}74do_test fts3ab-1.5 {75  execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'}76} {7 15 23 31}77do_test fts3ab-1.6 {78  execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1}79} {one un eine}80do_test fts3ab-1.7 {81  execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'}82} {}83 84do_test fts3ab-2.1 {85  execsql {86    CREATE VIRTUAL TABLE t2 USING fts3(from,to);87    INSERT INTO t2([from],[to]) VALUES ('one two three', 'four five six');88    SELECT [from], [to] FROM t289  }90} {{one two three} {four five six}}91 92 93# Compute an SQL string that contains the words one, two, three,... to94# describe bits set in the value $i.  Only the lower 5 bits are examined.95#96proc 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 rowid107#    plusone:   words for the bits of rowid+1108#    invert:    words for the bits of ~rowid109#110db eval {111   CREATE VIRTUAL TABLE t4 USING fts3([norm],'plusone',"invert");112}113for {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 118do_test fts3ab-4.1 {119  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one'}120} {1 3 5 7 9 11 13 15}121do_test fts3ab-4.2 {122  execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'}123} {1 3 5 7 9 11 13 15}124do_test fts3ab-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}127do_test fts3ab-4.4 {128  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'}129} {2 4 6 8 10 12 14}130do_test fts3ab-4.5 {131  execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'}132} {2 4 6 8 10 12 14}133do_test fts3ab-4.6 {134  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'}135} {1 5 9 13}136do_test fts3ab-4.7 {137  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one two'}138} {1 3 5 7 9 11 13 15}139do_test fts3ab-4.8 {140  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'}141} {1 5 9 13}142do_test fts3ab-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 147finish_test148