# 2006 September 13

#

# The author disclaims copyright to this source code.  In place of

# a legal notice, here is a blessing:

#

#    May you do good and not evil.

#    May you find forgiveness for yourself and forgive others.

#    May you share freely, never taking more than you give.

#

#*************************************************************************

# This file implements regression tests for SQLite library.  The

# focus of this script is testing the FTS2 module.

#

# $Id: fts2b.test,v 1.1 2006/10/19 23:36:26 shess Exp $

#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# If SQLITE_ENABLE_FTS2 is defined, omit this file.

ifcapable !fts2 {

  finish_test

  return

}

# Fill the full-text index "t1" with phrases in english, spanish,

# and german.  For the i-th row, fill in the names for the bits

# that are set in the value of i.  The least significant bit is

# 1.  For example,  the value 5 is 101 in binary which will be

# converted to "one three" in english.

#

proc fill_multilanguage_fulltext_t1 {} {

  set english {one two three four five}

  set spanish {un dos tres cuatro cinco}

  set german {eine zwei drei vier funf}

  for {set i 1} {$i<=31} {incr i} {

    set cmd "INSERT INTO t1 VALUES"

    set vset {}

    foreach lang {english spanish german} {

      set words {}

      for {set j 0; set k 1} {$j<5} {incr j; incr k $k} {

        if {$k&$i} {lappend words [lindex [set $lang] $j]}

      }

      lappend vset "'$words'"

    }

    set sql "INSERT INTO t1(english,spanish,german) VALUES([join $vset ,])"

    # puts $sql

    db eval $sql

  }

}

# Construct a full-text search table containing five keywords:

# one, two, three, four, and five, in various combinations.  The

# rowid for each will be a bitmask for the elements it contains.

#

db eval {

  CREATE VIRTUAL TABLE t1 USING fts2(english,spanish,german);

}

fill_multilanguage_fulltext_t1

do_test fts2b-1.1 {

  execsql {SELECT rowid FROM t1 WHERE english MATCH 'one'}

} {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}

do_test fts2b-1.2 {

  execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'}

} {}

do_test fts2b-1.3 {

  execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'}

} {}

do_test fts2b-1.4 {

  execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one'}

} {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}

do_test fts2b-1.5 {

  execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'}

} {7 15 23 31}

do_test fts2b-1.6 {

  execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1}

} {one un eine}

do_test fts2b-1.7 {

  execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'}

} {}

do_test fts2b-2.1 {

  execsql {

    CREATE VIRTUAL TABLE t2 USING fts2(from,to);

    INSERT INTO t2([from],[to]) VALUES ('one two three', 'four five six');

    SELECT [from], [to] FROM t2

  }

} {{one two three} {four five six}}

# Compute an SQL string that contains the words one, two, three,... to

# describe bits set in the value $i.  Only the lower 5 bits are examined.

#

proc wordset {i} {

  set x {}

  for {set j 0; set k 1} {$j<5} {incr j; incr k $k} {

    if {$k&$i} {lappend x [lindex {one two three four five} $j]}

  }

  return '$x'

}

# Create a new FTS table with three columns:

#

#    norm:      words for the bits of rowid

#    plusone:   words for the bits of rowid+1

#    invert:    words for the bits of ~rowid

#

db eval {

   CREATE VIRTUAL TABLE t4 USING fts2([norm],'plusone',"invert");

}

for {set i 1} {$i<=15} {incr i} {

  set vset [list [wordset $i] [wordset [expr {$i+1}]] [wordset [expr {~$i}]]]

  db eval "INSERT INTO t4(norm,plusone,invert) VALUES([join $vset ,]);"

}

do_test fts2b-4.1 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one'}

} {1 3 5 7 9 11 13 15}

do_test fts2b-4.2 {

  execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'}

} {1 3 5 7 9 11 13 15}

do_test fts2b-4.3 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'one'}

} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}

do_test fts2b-4.4 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'}

} {2 4 6 8 10 12 14}

do_test fts2b-4.5 {

  execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'}

} {2 4 6 8 10 12 14}

do_test fts2b-4.6 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'}

} {1 5 9 13}

do_test fts2b-4.7 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one two'}

} {1 3 5 7 9 11 13 15}

do_test fts2b-4.8 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'}

} {1 5 9 13}

do_test fts2b-4.9 {

  execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'two norm:one'}

} {1 3 5 7 9 11 13 15}

finish_test