| 1 |
# 2015 December 18 |
| 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 FTS5 module. |
| 13 |
# |
| 14 |
|
| 15 |
source [file join [file dirname [info script]] fts5_common.tcl] |
| 16 |
set testprefix fts5detail |
| 17 |
|
| 18 |
# If SQLITE_ENABLE_FTS5 is not defined, omit this file. |
| 19 |
ifcapable !fts5 { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
fts5_aux_test_functions db |
| 25 |
|
| 26 |
#-------------------------------------------------------------------------- |
| 27 |
# Simple tests. |
| 28 |
# |
| 29 |
do_execsql_test 1.0 { |
| 30 |
CREATE VIRTUAL TABLE t1 USING fts5(a, b, c, detail=col); |
| 31 |
INSERT INTO t1 VALUES('h d g', 'j b b g b', 'i e i d h g g'); -- 1 |
| 32 |
INSERT INTO t1 VALUES('h j d', 'j h d a h', 'f d d g g f b'); -- 2 |
| 33 |
INSERT INTO t1 VALUES('j c i', 'f f h e f', 'c j i j c h f'); -- 3 |
| 34 |
INSERT INTO t1 VALUES('e g g', 'g e d h i', 'e d b e g d c'); -- 4 |
| 35 |
INSERT INTO t1 VALUES('b c c', 'd i h a f', 'd i j f a b c'); -- 5 |
| 36 |
INSERT INTO t1 VALUES('e d e', 'b c j g d', 'a i f d h b d'); -- 6 |
| 37 |
INSERT INTO t1 VALUES('g h e', 'b c d i d', 'e f c i f i c'); -- 7 |
| 38 |
INSERT INTO t1 VALUES('c f j', 'j j i e a', 'h a c f d h e'); -- 8 |
| 39 |
INSERT INTO t1 VALUES('a h i', 'c i a f a', 'c f d h g d g'); -- 9 |
| 40 |
INSERT INTO t1 VALUES('j g g', 'e f e f f', 'h j b i c g e'); -- 10 |
| 41 |
} |
| 42 |
|
| 43 |
do_execsql_test 1.1 { |
| 44 |
INSERT INTO t1(t1) VALUES('integrity-check'); |
| 45 |
} |
| 46 |
|
| 47 |
foreach {tn match res} { |
| 48 |
1 "a:a" {9} |
| 49 |
2 "b:g" {1 4 6} |
| 50 |
3 "c:h" {1 3 6 8 9 10} |
| 51 |
} { |
| 52 |
do_execsql_test 1.2.$tn.1 { |
| 53 |
SELECT rowid FROM t1($match); |
| 54 |
} $res |
| 55 |
|
| 56 |
do_execsql_test 1.2.$tn.2 { |
| 57 |
SELECT rowid FROM t1($match || '*'); |
| 58 |
} $res |
| 59 |
} |
| 60 |
|
| 61 |
do_catchsql_test 1.3.1 { |
| 62 |
SELECT rowid FROM t1('h + d'); |
| 63 |
} {1 {fts5: phrase queries are not supported (detail!=full)}} |
| 64 |
|
| 65 |
do_catchsql_test 1.3.2 { |
| 66 |
SELECT rowid FROM t1('NEAR(h d)'); |
| 67 |
} {1 {fts5: NEAR queries are not supported (detail!=full)}} |
| 68 |
|
| 69 |
|
| 70 |
#------------------------------------------------------------------------- |
| 71 |
# integrity-check with both detail= and prefix= options. |
| 72 |
# |
| 73 |
do_execsql_test 2.0 { |
| 74 |
CREATE VIRTUAL TABLE t2 USING fts5(a, detail=col, prefix="1"); |
| 75 |
INSERT INTO t2(a) VALUES('aa ab'); |
| 76 |
} |
| 77 |
|
| 78 |
#db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM t2_data} {puts $r} |
| 79 |
|
| 80 |
do_execsql_test 2.1 { |
| 81 |
INSERT INTO t2(t2) VALUES('integrity-check'); |
| 82 |
} |
| 83 |
|
| 84 |
do_execsql_test 2.2 { |
| 85 |
SELECT fts5_test_poslist(t2) FROM t2('aa'); |
| 86 |
} {0.0.0} |
| 87 |
|
| 88 |
do_execsql_test 2.3 { |
| 89 |
SELECT fts5_test_collist(t2) FROM t2('aa'); |
| 90 |
} {0.0} |
| 91 |
|
| 92 |
set ::pc 0 |
| 93 |
#puts [nearset {{ax bx cx}} -pc ::pc -near 10 -- b*] |
| 94 |
#exit |
| 95 |
|
| 96 |
#------------------------------------------------------------------------- |
| 97 |
# Check that the xInstCount, xInst, xPhraseFirst and xPhraseNext APIs |
| 98 |
# work with detail=col tables. |
| 99 |
# |
| 100 |
set data { |
| 101 |
1 {abb aca aca} {aba bab aab aac caa} {abc cbc ccb bcc bab ccb aca} |
| 102 |
2 {bca aca acb} {ccb bcc bca aab bcc} {bab aaa aac cbb bba aca abc} |
| 103 |
3 {cca abc cab} {aab aba bcc cac baa} {bab cbb acb aba aab ccc cca} |
| 104 |
4 {ccb bcb aba} {aba bbb bcc cac bbb} {cbb aaa bca bcc aab cac aca} |
| 105 |
5 {bca bbc cac} {aba cbb cac cca aca} {cab acb cbc ccb cac bbb bcb} |
| 106 |
6 {acc bba cba} {bab bbc bbb bcb aca} {bca ccc cbb aca bac ccc ccb} |
| 107 |
7 {aba bab aaa} {abb bca aac bcb bcc} {bcb bbc aba aaa cba abc acc} |
| 108 |
8 {cab aba aaa} {ccb aca caa bbc bcc} {aaa abc ccb bbb cac cca abb} |
| 109 |
9 {bcb bab bac} {bcb cba cac bbb abc} {aba aca cbb acb abb ccc ccb} |
| 110 |
10 {aba aab ccc} {abc ccc bcc cab bbb} {aab bcc cbb ccc aaa bac baa} |
| 111 |
11 {bab acb cba} {aac cab cab bca cbc} {aab cbc aac baa ccb acc cac} |
| 112 |
12 {ccc cbb cbc} {aaa aab bcc aac bbc} {cbc cbc bac bac ccc bbc acc} |
| 113 |
13 {cab bbc abc} {bbb bab bba aca bab} {baa bbb aab bbb ccb bbb ccc} |
| 114 |
14 {bbc cab caa} {acb aac abb cba acc} {cba bba bba acb abc abb baa} |
| 115 |
15 {aba cca bcc} {aaa acb abc aab ccb} {cca bcb acc aaa caa cca cbc} |
| 116 |
16 {bcb bba aba} {cbc acb cab caa ccb} {aac aaa bbc cab cca cba abc} |
| 117 |
17 {caa cbb acc} {ccb bcb bca aaa bcc} {bbb aca bcb bca cbc cbc cca} |
| 118 |
18 {cbb bbc aac} {ccc bbc aaa aab baa} {cab cab cac cca bbc abc bbc} |
| 119 |
19 {ccc acc aaa} {aab cbb bca cca caa} {bcb aca aca cab acc bac bcc} |
| 120 |
20 {aab ccc bcb} {bbc cbb bbc aaa bcc} {cbc aab ccc aaa bcb bac cbc} |
| 121 |
21 {aba cab ccc} {bbc cbc cba acc bbb} {acc aab aac acb aca bca acb} |
| 122 |
22 {bcb bca baa} {cca bbc aca ccb cbb} {aab abc bbc aaa cab bcc bcc} |
| 123 |
23 {cac cbb caa} {bbc aba bbb bcc ccb} {bbc bbb cab bbc cac abb acc} |
| 124 |
24 {ccb acb caa} {cab bba cac bbc aac} {aac bca abc cab bca cab bcb} |
| 125 |
25 {bbb aca bca} {bcb acc ccc cac aca} {ccc acb acc cac cac bba bbc} |
| 126 |
26 {bab acc caa} {caa cab cac bac aca} {aba cac caa acc bac ccc aaa} |
| 127 |
27 {bca bca aaa} {ccb aca bca aaa baa} {bab acc aaa cca cba cca bac} |
| 128 |
28 {ccb cac cac} {bca abb bba bbc baa} {aca ccb aac cab ccc cab caa} |
| 129 |
29 {abc bca cab} {cac cbc cbb ccc bcc} {bcc aaa aaa acc aac cac aac} |
| 130 |
30 {aca acc acb} {aab aac cbb caa acb} {acb bbc bbc acc cbb bbc aac} |
| 131 |
31 {aba aca baa} {aca bcc cab bab acb} {bcc acb baa bcb bbc acc aba} |
| 132 |
32 {abb cbc caa} {cba abb bbb cbb aca} {bac aca caa cac caa ccb bbc} |
| 133 |
33 {bcc bcb bcb} {cca cab cbc abb bab} {caa bbc aac bbb cab cba aaa} |
| 134 |
34 {caa cab acc} {ccc ccc bcc acb bcc} {bac bba aca bcb bba bcb cac} |
| 135 |
35 {bac bcb cba} {bcc acb bbc cba bab} {abb cbb abc abc bac acc cbb} |
| 136 |
36 {cab bab ccb} {bca bba bab cca acc} {acc aab bcc bac acb cbb caa} |
| 137 |
37 {aca cbc cab} {bba aac aca aac aaa} {baa cbb cba aba cab bca bcb} |
| 138 |
38 {acb aab baa} {baa bab bca bbc bbb} {abc baa acc aba cab baa cac} |
| 139 |
39 {bcb aac cba} {bcb baa caa cac bbc} {cbc ccc bab ccb bbb caa aba} |
| 140 |
40 {cba ccb abc} {cbb caa cba aac bab} {cbb bbb bca bbb bac cac bca} |
| 141 |
} |
| 142 |
|
| 143 |
set data { |
| 144 |
1 {abb aca aca} {aba bab aab aac caa} {abc cbc ccb bcc bab ccb aca} |
| 145 |
} |
| 146 |
|
| 147 |
proc matchdata {expr {bAsc 1}} { |
| 148 |
|
| 149 |
set tclexpr [db one { |
| 150 |
SELECT fts5_expr_tcl($expr, 'nearset $cols -pc ::pc', 'x', 'y', 'z') |
| 151 |
}] |
| 152 |
set res [list] |
| 153 |
|
| 154 |
#puts "$expr -> $tclexpr" |
| 155 |
foreach {id x y z} $::data { |
| 156 |
set cols [list $x $y $z] |
| 157 |
set ::pc 0 |
| 158 |
#set hits [lsort -command instcompare [eval $tclexpr]] |
| 159 |
set hits [eval $tclexpr] |
| 160 |
if {[llength $hits]>0} { |
| 161 |
lappend res [list $id $hits] |
| 162 |
} |
| 163 |
} |
| 164 |
|
| 165 |
if {$bAsc} { |
| 166 |
set res [lsort -integer -increasing -index 0 $res] |
| 167 |
} else { |
| 168 |
set res [lsort -integer -decreasing -index 0 $res] |
| 169 |
} |
| 170 |
|
| 171 |
return [concat {*}$res] |
| 172 |
} |
| 173 |
|
| 174 |
foreach {tn tbl} { |
| 175 |
1 { CREATE VIRTUAL TABLE t3 USING fts5(x, y, z, detail=col) } |
| 176 |
2 { CREATE VIRTUAL TABLE t3 USING fts5(x, y, z, detail=none) } |
| 177 |
} { |
| 178 |
reset_db |
| 179 |
fts5_aux_test_functions db |
| 180 |
execsql $tbl |
| 181 |
foreach {id x y z} $data { |
| 182 |
execsql { INSERT INTO t3(rowid, x, y, z) VALUES($id, $x, $y, $z) } |
| 183 |
} |
| 184 |
foreach {tn2 expr} { |
| 185 |
1 aaa 2 ccc 3 bab 4 aac |
| 186 |
5 aa* 6 cc* 7 ba* 8 aa* |
| 187 |
9 a* 10 b* 11 c* |
| 188 |
} { |
| 189 |
|
| 190 |
set res [matchdata $expr] |
| 191 |
|
| 192 |
do_execsql_test 3.$tn.$tn2.1 { |
| 193 |
SELECT rowid, fts5_test_poslist(t3) FROM t3($expr) |
| 194 |
} $res |
| 195 |
|
| 196 |
do_execsql_test 3.$tn.$tn2.2 { |
| 197 |
SELECT rowid, fts5_test_poslist2(t3) FROM t3($expr) |
| 198 |
} $res |
| 199 |
} |
| 200 |
} |
| 201 |
|
| 202 |
#------------------------------------------------------------------------- |
| 203 |
# Simple tests for detail=none tables. |
| 204 |
# |
| 205 |
do_execsql_test 4.0 { |
| 206 |
CREATE VIRTUAL TABLE t4 USING fts5(a, b, c, detail=none); |
| 207 |
INSERT INTO t4 VALUES('a b c', 'b c d', 'e f g'); |
| 208 |
INSERT INTO t4 VALUES('1 2 3', '4 5 6', '7 8 9'); |
| 209 |
} |
| 210 |
|
| 211 |
do_catchsql_test 4.1 { |
| 212 |
SELECT * FROM t4('a:a') |
| 213 |
} {1 {fts5: column queries are not supported (detail=none)}} |
| 214 |
|
| 215 |
do_catchsql_test 4.2 { |
| 216 |
SELECT * FROM t4('a:a &') |
| 217 |
} {1 {fts5: syntax error near "&"}} |
| 218 |
|
| 219 |
#------------------------------------------------------------------------- |
| 220 |
# Test that for the same content detail=none uses less space than |
| 221 |
# detail=col, and that detail=col uses less space than detail=full |
| 222 |
# |
| 223 |
reset_db |
| 224 |
do_test 5.1 { |
| 225 |
foreach {tbl detail} {t1 none t2 col t3 full} { |
| 226 |
execsql "CREATE VIRTUAL TABLE $tbl USING fts5(x, y, z, detail=$detail)" |
| 227 |
foreach {rowid x y z} $::data { |
| 228 |
execsql "INSERT INTO $tbl (rowid, x, y, z) VALUES(\$rowid, \$x, \$y, \$z)" |
| 229 |
} |
| 230 |
} |
| 231 |
} {} |
| 232 |
|
| 233 |
do_execsql_test 5.2 { |
| 234 |
SELECT |
| 235 |
(SELECT sum(length(block)) from t1_data) < |
| 236 |
(SELECT sum(length(block)) from t2_data) |
| 237 |
} {1} |
| 238 |
|
| 239 |
do_execsql_test 5.3 { |
| 240 |
SELECT |
| 241 |
(SELECT sum(length(block)) from t2_data) < |
| 242 |
(SELECT sum(length(block)) from t3_data) |
| 243 |
} {1} |
| 244 |
|
| 245 |
|
| 246 |
|
| 247 |
finish_test |