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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # If SQLITE_ENABLE_FTS2 is defined, omit this file. 21 ifcapable !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 # 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 fts2(english,spanish,german); 59 } 60 fill_multilanguage_fulltext_t1 61 62 do_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} 65 do_test fts2b-1.2 { 66 execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'} 67 } {} 68 do_test fts2b-1.3 { 69 execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'} 70 } {} 71 do_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} 74 do_test fts2b-1.5 { 75 execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'} 76 } {7 15 23 31} 77 do_test fts2b-1.6 { 78 execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1} 79 } {one un eine} 80 do_test fts2b-1.7 { 81 execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'} 82 } {} 83 84 do_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 # 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 fts2([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 fts2b-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 fts2b-4.2 { 122 execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'} 123 } {1 3 5 7 9 11 13 15} 124 do_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} 127 do_test fts2b-4.4 { 128 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'} 129 } {2 4 6 8 10 12 14} 130 do_test fts2b-4.5 { 131 execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'} 132 } {2 4 6 8 10 12 14} 133 do_test fts2b-4.6 { 134 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'} 135 } {1 5 9 13} 136 do_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} 139 do_test fts2b-4.8 { 140 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'} 141 } {1 5 9 13} 142 do_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 147 finish_test 148
