[sql] Import SQLite 3.17.0.

third_party/sqlite/src/ext/fts5/test/fts5detail.test

+# 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
+