Index: third_party/sqlite/sqlite-src-3170000/ext/fts5/test/fts5vocab.test |
diff --git a/third_party/sqlite/sqlite-src-3170000/ext/fts5/test/fts5vocab.test b/third_party/sqlite/sqlite-src-3170000/ext/fts5/test/fts5vocab.test |
new file mode 100644 |
index 0000000000000000000000000000000000000000..5e0499fbf9479b0dc5ef31ba0b1e05c20a9134aa |
--- /dev/null |
+++ b/third_party/sqlite/sqlite-src-3170000/ext/fts5/test/fts5vocab.test |
@@ -0,0 +1,485 @@ |
+# 2015 Apr 24 |
+# |
+# 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. |
+# |
+#*********************************************************************** |
+# |
+# The tests in this file focus on testing the fts5vocab module. |
+# |
+ |
+source [file join [file dirname [info script]] fts5_common.tcl] |
+set testprefix fts5vocab |
+ |
+# If SQLITE_ENABLE_FTS5 is defined, omit this file. |
+ifcapable !fts5 { |
+ finish_test |
+ return |
+} |
+ |
+foreach_detail_mode $testprefix { |
+ |
+proc null_list_entries {iFirst nInterval L} { |
+ for {set i $iFirst} {$i < [llength $L]} {incr i $nInterval} { |
+ lset L $i {} |
+ } |
+ return $L |
+} |
+ |
+proc star_from_row {L} { |
+ if {[detail_is_full]==0} { |
+ set L [null_list_entries 2 3 $L] |
+ } |
+ return $L |
+} |
+ |
+proc star_from_col {L} { |
+ if {[detail_is_col]} { |
+ set L [null_list_entries 3 4 $L] |
+ } |
+ if {[detail_is_none]} { |
+ set L [null_list_entries 1 4 $L] |
+ set L [null_list_entries 3 4 $L] |
+ } |
+ return $L |
+} |
+ |
+proc row_to_col {L} { |
+ if {[detail_is_none]==0} { error "this is for detail=none mode" } |
+ set ret [list] |
+ foreach {a b c} $L { |
+ lappend ret $a {} $b {} |
+ } |
+ set ret |
+} |
+ |
+if 1 { |
+ |
+do_execsql_test 1.1.1 { |
+ CREATE VIRTUAL TABLE t1 USING fts5(one, prefix=1, detail=%DETAIL%); |
+ CREATE VIRTUAL TABLE v1 USING fts5vocab(t1, 'row'); |
+ PRAGMA table_info = v1; |
+} { |
+ 0 term {} 0 {} 0 |
+ 1 doc {} 0 {} 0 |
+ 2 cnt {} 0 {} 0 |
+} |
+ |
+do_execsql_test 1.1.2 { |
+ CREATE VIRTUAL TABLE v2 USING fts5vocab(t1, 'col'); |
+ PRAGMA table_info = v2; |
+} { |
+ 0 term {} 0 {} 0 |
+ 1 col {} 0 {} 0 |
+ 2 doc {} 0 {} 0 |
+ 3 cnt {} 0 {} 0 |
+} |
+ |
+do_execsql_test 1.2.1 { SELECT * FROM v1 } { } |
+do_execsql_test 1.2.2 { SELECT * FROM v2 } { } |
+ |
+do_execsql_test 1.3 { |
+ INSERT INTO t1 VALUES('x y z'); |
+ INSERT INTO t1 VALUES('x x x'); |
+} |
+ |
+do_execsql_test 1.4.1 { |
+ SELECT * FROM v1; |
+} [star_from_row {x 2 4 y 1 1 z 1 1}] |
+ |
+do_execsql_test 1.4.2 { |
+ SELECT * FROM v2; |
+} [star_from_col {x one 2 4 y one 1 1 z one 1 1}] |
+ |
+do_execsql_test 1.5.1 { |
+ BEGIN; |
+ INSERT INTO t1 VALUES('a b c'); |
+ SELECT * FROM v1 WHERE term<'d'; |
+} [star_from_row {a 1 1 b 1 1 c 1 1}] |
+ |
+do_execsql_test 1.5.2 { |
+ SELECT * FROM v2 WHERE term<'d'; |
+ COMMIT; |
+} [star_from_col {a one 1 1 b one 1 1 c one 1 1}] |
+ |
+do_execsql_test 1.6 { |
+ DELETE FROM t1 WHERE one = 'a b c'; |
+ SELECT * FROM v1; |
+} [star_from_row {x 2 4 y 1 1 z 1 1}] |
+ |
+#------------------------------------------------------------------------- |
+# |
+do_execsql_test 2.0 { |
+ CREATE VIRTUAL TABLE tt USING fts5(a, b, detail=%DETAIL%); |
+ INSERT INTO tt VALUES('d g b f d f', 'f c e c d a'); |
+ INSERT INTO tt VALUES('f a e a a b', 'e d c f d d'); |
+ INSERT INTO tt VALUES('b c a a a b', 'f f c c b c'); |
+ INSERT INTO tt VALUES('f d c a c e', 'd g d e g d'); |
+ INSERT INTO tt VALUES('g d e f a g x', 'f f d a a b'); |
+ INSERT INTO tt VALUES('g c f b c g', 'a g f d c b'); |
+ INSERT INTO tt VALUES('c e c f g b', 'f e d b g a'); |
+ INSERT INTO tt VALUES('g d e f d e', 'a c d b a g'); |
+ INSERT INTO tt VALUES('e f a c c b', 'b f e a f d y'); |
+ INSERT INTO tt VALUES('c c a a c f', 'd g a e b g'); |
+} |
+ |
+set res_row [star_from_row { |
+ a 10 20 b 9 14 c 9 20 d 9 19 |
+ e 8 13 f 10 20 g 7 14 x 1 1 |
+ y 1 1 |
+}] |
+set res_col [star_from_col { |
+ a a 6 11 a b 7 9 |
+ b a 6 7 b b 7 7 |
+ c a 6 12 c b 5 8 |
+ d a 4 6 d b 9 13 |
+ e a 6 7 e b 6 6 |
+ f a 9 10 f b 7 10 |
+ g a 5 7 g b 5 7 |
+ x a 1 1 y b 1 1 |
+}] |
+if {[detail_is_none]} { |
+ set res_col [row_to_col $res_row] |
+} |
+ |
+foreach {tn tbl resname} { |
+ 1 "fts5vocab(tt, 'col')" res_col |
+ 2 "fts5vocab(tt, 'row')" res_row |
+ 3 "fts5vocab(tt, \"row\")" res_row |
+ 4 "fts5vocab(tt, [row])" res_row |
+ 5 "fts5vocab(tt, `row`)" res_row |
+ |
+ 6 "fts5vocab('tt', 'row')" res_row |
+ 7 "fts5vocab(\"tt\", \"row\")" res_row |
+ 8 "fts5vocab([tt], [row])" res_row |
+ 9 "fts5vocab(`tt`, `row`)" res_row |
+} { |
+ do_execsql_test 2.$tn " |
+ DROP TABLE IF EXISTS tv; |
+ CREATE VIRTUAL TABLE tv USING $tbl; |
+ SELECT * FROM tv; |
+ " [set $resname] |
+} |
+ |
+#------------------------------------------------------------------------- |
+# Test errors in the CREATE VIRTUAL TABLE statement. |
+# |
+foreach {tn sql} { |
+ 1 { CREATE VIRTUAL TABLE aa USING fts5vocab() } |
+ 2 { CREATE VIRTUAL TABLE aa USING fts5vocab(x) } |
+ 3 { CREATE VIRTUAL TABLE aa USING fts5vocab(x,y,z) } |
+ 4 { CREATE VIRTUAL TABLE temp.aa USING fts5vocab(x,y,z,y) } |
+} { |
+ do_catchsql_test 3.$tn $sql {1 {wrong number of vtable arguments}} |
+} |
+ |
+do_catchsql_test 4.0 { |
+ CREATE VIRTUAL TABLE cc USING fts5vocab(tbl, unknown); |
+} {1 {fts5vocab: unknown table type: 'unknown'}} |
+ |
+do_catchsql_test 4.1 { |
+ ATTACH 'test.db' AS aux; |
+ CREATE VIRTUAL TABLE aux.cc USING fts5vocab(main, tbl, row); |
+} {1 {wrong number of vtable arguments}} |
+ |
+#------------------------------------------------------------------------- |
+# Test fts5vocab tables created in the temp schema. |
+# |
+reset_db |
+forcedelete test.db2 |
+do_execsql_test 5.0 { |
+ ATTACH 'test.db2' AS aux; |
+ CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL%); |
+ CREATE VIRTUAL TABLE temp.t1 USING fts5(x, detail=%DETAIL%); |
+ CREATE VIRTUAL TABLE aux.t1 USING fts5(x, detail=%DETAIL%); |
+ |
+ INSERT INTO main.t1 VALUES('a b c'); |
+ INSERT INTO main.t1 VALUES('d e f'); |
+ INSERT INTO main.t1 VALUES('a e c'); |
+ |
+ INSERT INTO temp.t1 VALUES('1 2 3'); |
+ INSERT INTO temp.t1 VALUES('4 5 6'); |
+ INSERT INTO temp.t1 VALUES('1 5 3'); |
+ |
+ INSERT INTO aux.t1 VALUES('x y z'); |
+ INSERT INTO aux.t1 VALUES('m n o'); |
+ INSERT INTO aux.t1 VALUES('x n z'); |
+} |
+ |
+breakpoint |
+do_execsql_test 5.1 { |
+ CREATE VIRTUAL TABLE temp.vm USING fts5vocab(main, t1, row); |
+ CREATE VIRTUAL TABLE temp.vt1 USING fts5vocab(t1, row); |
+ CREATE VIRTUAL TABLE temp.vt2 USING fts5vocab(temp, t1, row); |
+ CREATE VIRTUAL TABLE temp.va USING fts5vocab(aux, t1, row); |
+} |
+ |
+do_execsql_test 5.2 { SELECT * FROM vm } [star_from_row { |
+ a 2 2 b 1 1 c 2 2 d 1 1 e 2 2 f 1 1 |
+}] |
+do_execsql_test 5.3 { SELECT * FROM vt1 } [star_from_row { |
+ 1 2 2 2 1 1 3 2 2 4 1 1 5 2 2 6 1 1 |
+}] |
+do_execsql_test 5.4 { SELECT * FROM vt2 } [star_from_row { |
+ 1 2 2 2 1 1 3 2 2 4 1 1 5 2 2 6 1 1 |
+}] |
+do_execsql_test 5.5 { SELECT * FROM va } [star_from_row { |
+ m 1 1 n 2 2 o 1 1 x 2 2 y 1 1 z 2 2 |
+}] |
+ |
+#------------------------------------------------------------------------- |
+# |
+do_execsql_test 6.0 { |
+ CREATE TABLE iii(iii); |
+ CREATE TABLE jjj(x); |
+} |
+ |
+do_catchsql_test 6.1 { |
+ CREATE VIRTUAL TABLE vocab1 USING fts5vocab(iii, row); |
+ SELECT * FROM vocab1; |
+} {1 {no such fts5 table: main.iii}} |
+ |
+do_catchsql_test 6.2 { |
+ CREATE VIRTUAL TABLE vocab2 USING fts5vocab(jjj, row); |
+ SELECT * FROM vocab2; |
+} {1 {no such fts5 table: main.jjj}} |
+ |
+do_catchsql_test 6.2 { |
+ CREATE VIRTUAL TABLE vocab3 USING fts5vocab(lll, row); |
+ SELECT * FROM vocab3; |
+} {1 {no such fts5 table: main.lll}} |
+ |
+#------------------------------------------------------------------------- |
+# Test single term queries on fts5vocab tables (i.e. those with term=? |
+# constraints in the WHERE clause). |
+# |
+do_execsql_test 7.0 { |
+ CREATE VIRTUAL TABLE tx USING fts5(one, two, detail=%DETAIL%); |
+ INSERT INTO tx VALUES('g a ggg g a b eee', 'cc d aa ff g ee'); |
+ INSERT INTO tx VALUES('dd fff i a i jjj', 'f fff hh jj e f'); |
+ INSERT INTO tx VALUES('ggg a f f fff dd aa', 'd ggg f f j gg ddd'); |
+ INSERT INTO tx VALUES('e bb h jjj ii gg', 'e aa e f c fff'); |
+ INSERT INTO tx VALUES('j ff aa a h', 'h a j bbb bb'); |
+ INSERT INTO tx VALUES('cc i ff c d f', 'dd ii fff f c cc d'); |
+ INSERT INTO tx VALUES('jjj g i bb cc eee', 'hhh iii aaa b bbb aaa'); |
+ INSERT INTO tx VALUES('hhh hhh hhh bb fff f', 'fff gg aa ii h a'); |
+ INSERT INTO tx VALUES('b c cc aaa iii ggg f', 'iii ff ee a ff c cc'); |
+ INSERT INTO tx VALUES('hhh b hhh aaa j i i', 'dd ee ee aa bbb iii'); |
+ INSERT INTO tx VALUES('hh dd h b g ff i', 'ccc bb cc ccc f a d'); |
+ INSERT INTO tx VALUES('g d b ggg jj', 'fff jj ff jj g gg ee'); |
+ INSERT INTO tx VALUES('g ee ggg ggg cc bb eee', 'aa j jjj bbb dd eee ff'); |
+ INSERT INTO tx VALUES('c jjj hh ddd dd h', 'e aaa h jjj gg'); |
+ |
+ CREATE VIRTUAL TABLE txr USING fts5vocab(tx, row); |
+ CREATE VIRTUAL TABLE txc USING fts5vocab(tx, col); |
+} |
+ |
+proc cont {L elem} { |
+ set n 0 |
+ foreach e $L { if {$elem==$e} {incr n} } |
+ set n |
+} |
+db func cont cont |
+ |
+foreach {term} { |
+ a aa aaa |
+ b bb bbb |
+ c cc ccc |
+ d dd ddd |
+ e ee eee |
+ f ff fff |
+ g gg ggg |
+ h hh hhh |
+ i ii iii |
+ j jj jjj |
+} { |
+ set resr [db eval { |
+ SELECT $term, |
+ sum(cont(one || ' ' || two, $term) > 0), |
+ sum(cont(one || ' ' || two, $term)) |
+ FROM tx |
+ }] |
+ if {[lindex $resr 1]==0} {set resr [list]} |
+ |
+ set r1 [db eval { |
+ SELECT $term, 'one', sum(cont(one, $term)>0), sum(cont(one, $term)) FROM tx |
+ }] |
+ if {[lindex $r1 2]==0} {set r1 [list]} |
+ |
+ set r2 [db eval { |
+ SELECT $term, 'two', sum(cont(two, $term)>0), sum(cont(two, $term)) FROM tx |
+ }] |
+ if {[lindex $r2 2]==0} {set r2 [list]} |
+ |
+ set resc [concat $r1 $r2] |
+ |
+ set resc [star_from_col $resc] |
+ set resr [star_from_row $resr] |
+ if {[detail_is_none]} { set resc [row_to_col $resr] } |
+ do_execsql_test 7.$term.1 {SELECT * FROM txc WHERE term=$term} $resc |
+ do_execsql_test 7.$term.2 {SELECT * FROM txr WHERE term=$term} $resr |
+} |
+ |
+do_execsql_test 7.1 { |
+ CREATE TABLE txr_c AS SELECT * FROM txr; |
+ CREATE TABLE txc_c AS SELECT * FROM txc; |
+} |
+ |
+# Test range queries on the fts5vocab tables created above. |
+# |
+foreach {tn a b} { |
+ 1 a jjj |
+ 2 bb j |
+ 3 ccc ddd |
+ 4 dd xyz |
+ 5 xzy dd |
+ 6 h hh |
+} { |
+ do_execsql_test 7.2.$tn.1 { |
+ SELECT * FROM txr WHERE term>=$a |
+ } [db eval {SELECT * FROM txr_c WHERE term>=$a}] |
+ do_execsql_test 7.2.$tn.2 { |
+ SELECT * FROM txr WHERE term<=$b |
+ } [db eval {SELECT * FROM txr_c WHERE term <=$b}] |
+ do_execsql_test 7.2.$tn.3 { |
+ SELECT * FROM txr WHERE term>=$a AND term<=$b |
+ } [db eval {SELECT * FROM txr_c WHERE term>=$a AND term <=$b}] |
+ |
+ do_execsql_test 7.2.$tn.4 { |
+ SELECT * FROM txc WHERE term>=$a |
+ } [db eval {SELECT * FROM txc_c WHERE term>=$a}] |
+ do_execsql_test 7.2.$tn.5 { |
+ SELECT * FROM txc WHERE term<=$b |
+ } [db eval {SELECT * FROM txc_c WHERE term <=$b}] |
+ do_execsql_test 7.2.$tn.6 { |
+ SELECT * FROM txc WHERE term>=$a AND term<=$b |
+ } [db eval {SELECT * FROM txc_c WHERE term>=$a AND term <=$b}] |
+ |
+ do_execsql_test 7.2.$tn.7 { |
+ SELECT * FROM txr WHERE term>$a |
+ } [db eval {SELECT * FROM txr_c WHERE term>$a}] |
+ do_execsql_test 7.2.$tn.8 { |
+ SELECT * FROM txr WHERE term<$b |
+ } [db eval {SELECT * FROM txr_c WHERE term<$b}] |
+ do_execsql_test 7.2.$tn.9 { |
+ SELECT * FROM txr WHERE term>$a AND term<$b |
+ } [db eval {SELECT * FROM txr_c WHERE term>$a AND term <$b}] |
+ |
+ do_execsql_test 7.2.$tn.10 { |
+ SELECT * FROM txc WHERE term>$a |
+ } [db eval {SELECT * FROM txc_c WHERE term>$a}] |
+ do_execsql_test 7.2.$tn.11 { |
+ SELECT * FROM txc WHERE term<$b |
+ } [db eval {SELECT * FROM txc_c WHERE term<$b}] |
+ do_execsql_test 7.2.$tn.12 { |
+ SELECT * FROM txc WHERE term>$a AND term<$b |
+ } [db eval {SELECT * FROM txc_c WHERE term>$a AND term <$b}] |
+} |
+ |
+do_execsql_test 7.3.1 { |
+ SELECT count(*) FROM txr, txr_c WHERE txr.term = txr_c.term; |
+} {30} |
+ |
+if {![detail_is_none]} { |
+ do_execsql_test 7.3.2 { |
+ SELECT count(*) FROM txc, txc_c |
+ WHERE txc.term = txc_c.term AND txc.col=txc_c.col; |
+ } {57} |
+} |
+ |
+} |
+ |
+#------------------------------------------------------------------------- |
+# Test the fts5vocab tables response to a specific types of corruption: |
+# where the fts5 index contains hits for columns that do not exist. |
+# |
+do_execsql_test 8.0 { |
+ CREATE VIRTUAL TABLE x1 USING fts5(a, b, c, detail=%DETAIL%); |
+ INSERT INTO x1 VALUES('a b c', 'd e f', 'g h i'); |
+ INSERT INTO x1 VALUES('g h i', 'a b c', 'd e f'); |
+ INSERT INTO x1 VALUES('d e f', 'g h i', 'a b c'); |
+ CREATE VIRTUAL TABLE x1_r USING fts5vocab(x1, row); |
+ CREATE VIRTUAL TABLE x1_c USING fts5vocab(x1, col); |
+} |
+ |
+set resr [star_from_row {a 3 3 b 3 3 c 3 3 d 3 3 e 3 3 f 3 3 g 3 3 h 3 3 i 3 3}] |
+set resc [star_from_col { |
+ a a 1 1 a b 1 1 a c 1 1 b a 1 1 |
+ b b 1 1 b c 1 1 c a 1 1 c b 1 1 |
+ c c 1 1 d a 1 1 d b 1 1 d c 1 1 |
+ e a 1 1 e b 1 1 e c 1 1 f a 1 1 |
+ f b 1 1 f c 1 1 g a 1 1 g b 1 1 |
+ g c 1 1 h a 1 1 h b 1 1 h c 1 1 |
+ i a 1 1 i b 1 1 i c 1 1 |
+}] |
+if {[detail_is_none]} { set resc [row_to_col $resr] } |
+ |
+do_execsql_test 8.1.1 { SELECT * FROM x1_r; } $resr |
+do_execsql_test 8.1.2 { SELECT * FROM x1_c } $resc |
+ |
+do_execsql_test 8.2 { |
+ PRAGMA writable_schema = 1; |
+ UPDATE sqlite_master |
+ SET sql = 'CREATE VIRTUAL TABLE x1 USING fts5(a, detail=%DETAIL%)' |
+ WHERE name = 'x1'; |
+} |
+db close |
+sqlite3 db test.db |
+sqlite3_fts5_may_be_corrupt 1 |
+ |
+do_execsql_test 8.2.1 { SELECT * FROM x1_r } $resr |
+ |
+if {[detail_is_none]} { |
+ do_execsql_test 8.2.2 { SELECT * FROM x1_c } $resc |
+} else { |
+ do_catchsql_test 8.2.2 { |
+ SELECT * FROM x1_c |
+ } {1 {database disk image is malformed}} |
+} |
+ |
+sqlite3_fts5_may_be_corrupt 0 |
+} |
+ |
+#------------------------------------------------------------------------- |
+# Test that both "ORDER BY term" and "ORDER BY term DESC" work. |
+# |
+reset_db |
+do_execsql_test 9.1 { |
+ CREATE VIRTUAL TABLE x1 USING fts5(x); |
+ INSERT INTO x1 VALUES('def ABC ghi'); |
+ INSERT INTO x1 VALUES('DEF abc GHI'); |
+} |
+ |
+do_execsql_test 9.2 { |
+ CREATE VIRTUAL TABLE rrr USING fts5vocab(x1, row); |
+ SELECT * FROM rrr |
+} { |
+ abc 2 2 def 2 2 ghi 2 2 |
+} |
+do_execsql_test 9.3 { |
+ SELECT * FROM rrr ORDER BY term ASC |
+} { |
+ abc 2 2 def 2 2 ghi 2 2 |
+} |
+do_execsql_test 9.4 { |
+ SELECT * FROM rrr ORDER BY term DESC |
+} { |
+ ghi 2 2 def 2 2 abc 2 2 |
+} |
+do_test 9.5 { |
+ set e2 [db eval { EXPLAIN SELECT * FROM rrr ORDER BY term ASC }] |
+ expr [lsearch $e2 SorterSort]<0 |
+} 1 |
+do_test 9.6 { |
+ set e2 [db eval { EXPLAIN SELECT * FROM rrr ORDER BY term DESC }] |
+ expr [lsearch $e2 SorterSort]<0 |
+} 0 |
+ |
+ |
+ |
+finish_test |
+ |