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 FTS2 module. 13# 14# $Id: fts2b.test,v 1.1 2006/10/19 23:36:26 shess Exp $ 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# If SQLITE_ENABLE_FTS2 is defined, omit this file. 21ifcapable !fts2 { 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# 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 $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# 57db eval { 58 CREATE VIRTUAL TABLE t1 USING fts2(english,spanish,german); 59} 60fill_multilanguage_fulltext_t1 61 62do_test fts2b-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 fts2b-1.2 { 66 execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'} 67} {} 68do_test fts2b-1.3 { 69 execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'} 70} {} 71do_test fts2b-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 fts2b-1.5 { 75 execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'} 76} {7 15 23 31} 77do_test fts2b-1.6 { 78 execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1} 79} {one un eine} 80do_test fts2b-1.7 { 81 execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'} 82} {} 83 84do_test fts2b-2.1 { 85 execsql { 86 CREATE VIRTUAL TABLE t2 USING fts2(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# 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 rowid 107# plusone: words for the bits of rowid+1 108# invert: words for the bits of ~rowid 109# 110db eval { 111 CREATE VIRTUAL TABLE t4 USING fts2([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 fts2b-4.1 { 119 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one'} 120} {1 3 5 7 9 11 13 15} 121do_test fts2b-4.2 { 122 execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'} 123} {1 3 5 7 9 11 13 15} 124do_test fts2b-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 fts2b-4.4 { 128 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'} 129} {2 4 6 8 10 12 14} 130do_test fts2b-4.5 { 131 execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'} 132} {2 4 6 8 10 12 14} 133do_test fts2b-4.6 { 134 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'} 135} {1 5 9 13} 136do_test fts2b-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 fts2b-4.8 { 140 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'} 141} {1 5 9 13} 142do_test fts2b-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_test 148