# 2015 December 18
#
# 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 FTS5 module.
#
source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5detail
# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
ifcapable !fts5 {
finish_test
return
}
fts5_aux_test_functions db
#--------------------------------------------------------------------------
# Simple tests.
#
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE t1 USING fts5(a, b, c, detail=col);
INSERT INTO t1 VALUES('h d g', 'j b b g b', 'i e i d h g g'); -- 1
INSERT INTO t1 VALUES('h j d', 'j h d a h', 'f d d g g f b'); -- 2
INSERT INTO t1 VALUES('j c i', 'f f h e f', 'c j i j c h f'); -- 3
INSERT INTO t1 VALUES('e g g', 'g e d h i', 'e d b e g d c'); -- 4
INSERT INTO t1 VALUES('b c c', 'd i h a f', 'd i j f a b c'); -- 5
INSERT INTO t1 VALUES('e d e', 'b c j g d', 'a i f d h b d'); -- 6
INSERT INTO t1 VALUES('g h e', 'b c d i d', 'e f c i f i c'); -- 7
INSERT INTO t1 VALUES('c f j', 'j j i e a', 'h a c f d h e'); -- 8
INSERT INTO t1 VALUES('a h i', 'c i a f a', 'c f d h g d g'); -- 9
INSERT INTO t1 VALUES('j g g', 'e f e f f', 'h j b i c g e'); -- 10
}
do_execsql_test 1.1 {
INSERT INTO t1(t1) VALUES('integrity-check');
}
foreach {tn match res} {
1 "a:a" {9}
2 "b:g" {1 4 6}
3 "c:h" {1 3 6 8 9 10}
} {
do_execsql_test 1.2.$tn.1 {
SELECT rowid FROM t1($match);
} $res
do_execsql_test 1.2.$tn.2 {
SELECT rowid FROM t1($match || '*');
} $res
}
do_catchsql_test 1.3.1 {
SELECT rowid FROM t1('h + d');
} {1 {fts5: phrase queries are not supported (detail!=full)}}
do_catchsql_test 1.3.2 {
SELECT rowid FROM t1('NEAR(h d)');
} {1 {fts5: NEAR queries are not supported (detail!=full)}}
#-------------------------------------------------------------------------
# integrity-check with both detail= and prefix= options.
#
do_execsql_test 2.0 {
CREATE VIRTUAL TABLE t2 USING fts5(a, detail=col, prefix="1");
INSERT INTO t2(a) VALUES('aa ab');
}
#db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM t2_data} {puts $r}
do_execsql_test 2.1 {
INSERT INTO t2(t2) VALUES('integrity-check');
}
do_execsql_test 2.2 {
SELECT fts5_test_poslist(t2) FROM t2('aa');
} {0.0.0}
do_execsql_test 2.3 {
SELECT fts5_test_collist(t2) FROM t2('aa');
} {0.0}
set ::pc 0
#puts [nearset {{ax bx cx}} -pc ::pc -near 10 -- b*]
#exit
#-------------------------------------------------------------------------
# Check that the xInstCount, xInst, xPhraseFirst and xPhraseNext APIs
# work with detail=col tables.
#
set data {
1 {abb aca aca} {aba bab aab aac caa} {abc cbc ccb bcc bab ccb aca}
2 {bca aca acb} {ccb bcc bca aab bcc} {bab aaa aac cbb bba aca abc}
3 {cca abc cab} {aab aba bcc cac baa} {bab cbb acb aba aab ccc cca}
4 {ccb bcb aba} {aba bbb bcc cac bbb} {cbb aaa bca bcc aab cac aca}
5 {bca bbc cac} {aba cbb cac cca aca} {cab acb cbc ccb cac bbb bcb}
6 {acc bba cba} {bab bbc bbb bcb aca} {bca ccc cbb aca bac ccc ccb}
7 {aba bab aaa} {abb bca aac bcb bcc} {bcb bbc aba aaa cba abc acc}
8 {cab aba aaa} {ccb aca caa bbc bcc} {aaa abc ccb bbb cac cca abb}
9 {bcb bab bac} {bcb cba cac bbb abc} {aba aca cbb acb abb ccc ccb}
10 {aba aab ccc} {abc ccc bcc cab bbb} {aab bcc cbb ccc aaa bac baa}
11 {bab acb cba} {aac cab cab bca cbc} {aab cbc aac baa ccb acc cac}
12 {ccc cbb cbc} {aaa aab bcc aac bbc} {cbc cbc bac bac ccc bbc acc}
13 {cab bbc abc} {bbb bab bba aca bab} {baa bbb aab bbb ccb bbb ccc}
14 {bbc cab caa} {acb aac abb cba acc} {cba bba bba acb abc abb baa}
15 {aba cca bcc} {aaa acb abc aab ccb} {cca bcb acc aaa caa cca cbc}
16 {bcb bba aba} {cbc acb cab caa ccb} {aac aaa bbc cab cca cba abc}
17 {caa cbb acc} {ccb bcb bca aaa bcc} {bbb aca bcb bca cbc cbc cca}
18 {cbb bbc aac} {ccc bbc aaa aab baa} {cab cab cac cca bbc abc bbc}
19 {ccc acc aaa} {aab cbb bca cca caa} {bcb aca aca cab acc bac bcc}
20 {aab ccc bcb} {bbc cbb bbc aaa bcc} {cbc aab ccc aaa bcb bac cbc}
21 {aba cab ccc} {bbc cbc cba acc bbb} {acc aab aac acb aca bca acb}
22 {bcb bca baa} {cca bbc aca ccb cbb} {aab abc bbc aaa cab bcc bcc}
23 {cac cbb caa} {bbc aba bbb bcc ccb} {bbc bbb cab bbc cac abb acc}
24 {ccb acb caa} {cab bba cac bbc aac} {aac bca abc cab bca cab bcb}
25 {bbb aca bca} {bcb acc ccc cac aca} {ccc acb acc cac cac bba bbc}
26 {bab acc caa} {caa cab cac bac aca} {aba cac caa acc bac ccc aaa}
27 {bca bca aaa} {ccb aca bca aaa baa} {bab acc aaa cca cba cca bac}
28 {ccb cac cac} {bca abb bba bbc baa} {aca ccb aac cab ccc cab caa}
29 {abc bca cab} {cac cbc cbb ccc bcc} {bcc aaa aaa acc aac cac aac}
30 {aca acc acb} {aab aac cbb caa acb} {acb bbc bbc acc cbb bbc aac}
31 {aba aca baa} {aca bcc cab bab acb} {bcc acb baa bcb bbc acc aba}
32 {abb cbc caa} {cba abb bbb cbb aca} {bac aca caa cac caa ccb bbc}
33 {bcc bcb bcb} {cca cab cbc abb bab} {caa bbc aac bbb cab cba aaa}
34 {caa cab acc} {ccc ccc bcc acb bcc} {bac bba aca bcb bba bcb cac}
35 {bac bcb cba} {bcc acb bbc cba bab} {abb cbb abc abc bac acc cbb}
36 {cab bab ccb} {bca bba bab cca acc} {acc aab bcc bac acb cbb caa}
37 {aca cbc cab} {bba aac aca aac aaa} {baa cbb cba aba cab bca bcb}
38 {acb aab baa} {baa bab bca bbc bbb} {abc baa acc aba cab baa cac}
39 {bcb aac cba} {bcb baa caa cac bbc} {cbc ccc bab ccb bbb caa aba}
40 {cba ccb abc} {cbb caa cba aac bab} {cbb bbb bca bbb bac cac bca}
}
set data {
1 {abb aca aca} {aba bab aab aac caa} {abc cbc ccb bcc bab ccb aca}
}
proc matchdata {expr {bAsc 1}} {
set tclexpr [db one {
SELECT fts5_expr_tcl($expr, 'nearset $cols -pc ::pc', 'x', 'y', 'z')
}]
set res [list]
#puts "$expr -> $tclexpr"
foreach {id x y z} $::data {
set cols [list $x $y $z]
set ::pc 0
#set hits [lsort -command instcompare [eval $tclexpr]]
set hits [eval $tclexpr]
if {[llength $hits]>0} {
lappend res [list $id $hits]
}
}
if {$bAsc} {
set res [lsort -integer -increasing -index 0 $res]
} else {
set res [lsort -integer -decreasing -index 0 $res]
}
return [concat {*}$res]
}
foreach {tn tbl} {
1 { CREATE VIRTUAL TABLE t3 USING fts5(x, y, z, detail=col) }
2 { CREATE VIRTUAL TABLE t3 USING fts5(x, y, z, detail=none) }
} {
reset_db
fts5_aux_test_functions db
execsql $tbl
foreach {id x y z} $data {
execsql { INSERT INTO t3(rowid, x, y, z) VALUES($id, $x, $y, $z) }
}
foreach {tn2 expr} {
1 aaa 2 ccc 3 bab 4 aac
5 aa* 6 cc* 7 ba* 8 aa*
9 a* 10 b* 11 c*
} {
set res [matchdata $expr]
do_execsql_test 3.$tn.$tn2.1 {
SELECT rowid, fts5_test_poslist(t3) FROM t3($expr)
} $res
do_execsql_test 3.$tn.$tn2.2 {
SELECT rowid, fts5_test_poslist2(t3) FROM t3($expr)
} $res
}
}
#-------------------------------------------------------------------------
# Simple tests for detail=none tables.
#
do_execsql_test 4.0 {
CREATE VIRTUAL TABLE t4 USING fts5(a, b, c, detail=none);
INSERT INTO t4 VALUES('a b c', 'b c d', 'e f g');
INSERT INTO t4 VALUES('1 2 3', '4 5 6', '7 8 9');
}
do_catchsql_test 4.1 {
SELECT * FROM t4('a:a')
} {1 {fts5: column queries are not supported (detail=none)}}
#-------------------------------------------------------------------------
# Test that for the same content detail=none uses less space than
# detail=col, and that detail=col uses less space than detail=full
#
reset_db
do_test 5.1 {
foreach {tbl detail} {t1 none t2 col t3 full} {
execsql "CREATE VIRTUAL TABLE $tbl USING fts5(x, y, z, detail=$detail)"
foreach {rowid x y z} $::data {
execsql "INSERT INTO $tbl (rowid, x, y, z) VALUES(\$rowid, \$x, \$y, \$z)"
}
}
} {}
do_execsql_test 5.2 {
SELECT
(SELECT sum(length(block)) from t1_data) <
(SELECT sum(length(block)) from t2_data)
} {1}
do_execsql_test 5.3 {
SELECT
(SELECT sum(length(block)) from t2_data) <
(SELECT sum(length(block)) from t3_data)
} {1}
finish_test