SQLite

#      ... WHERE fts MATCH ? ORDER BY bm25(fts) [ASC|DESC]
#
# and
#
#      ... WHERE fts MATCH ? ORDER BY rank [ASC|DESC]
#

foreach_detail_mode $testprefix {

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(x, y, z, detail=%DETAIL%);
}

do_test 1.1 {
  foreach {x y z} {
    {j s m y m r n l u k} {z k f u z g h s w g} {r n o s s b v n w w}
    {m v g n d x q r r s} {q t d a q a v l h j} {s k l f s i n v q v}
    {m f f d h h s o h a} {y e v r q i u m h d} {b c k q m z l z h n}

foreach {tn expr} {
  2.1 a
  2.2 b
  2.3 c
  2.4 d




  3.0 {a AND b}
  3.1 {a OR b}
  3.2 {b OR c AND d}
} {
  do_fts5ag_test $tn $expr
}

if {[detail_is_full]} {
  foreach {tn expr} {
    4.1 {"m m"}
    4.2 {e + s}
    4.3 {NEAR(c d)}
  } {
    do_fts5ag_test $tn $expr
  }

}

} ;# foreach_detail_mode


finish_test

set testprefix fts5ah

# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

foreach_detail_mode $testprefix {

#-------------------------------------------------------------------------
# This file contains tests for very large doclists.
#

set Y [list]
set W [list]
do_test 1.0 {
  execsql { CREATE VIRTUAL TABLE t1 USING fts5(a, detail=%DETAIL%) }
  execsql { INSERT INTO t1(t1, rank) VALUES('pgsz', 128) }
  set v {w w w w w w w w w w w w w w w w w w w w}
  execsql { INSERT INTO t1(rowid, a) VALUES(0, $v) }
  for {set i 1} {$i <= 10000} {incr i} {
    set v {x x x x x x x x x x x x x x x x x x x x}
    if {($i % 2139)==0} {lset v 3 Y ; lappend Y $i}
    if {($i % 1577)==0} {lset v 5 W ; lappend W $i}

  expr [reads] - $nRead
}

do_test 1.4 {
  set nRead [reads]
  execsql { SELECT rowid FROM t1 WHERE t1 MATCH 'x' }
  set nReadX [expr [reads] - $nRead]
  #puts -nonewline "(nReadX=$nReadX)"
  if {[detail_is_full]} { set expect 1000 }
  if {[detail_is_col]}  { set expect 250 }
  if {[detail_is_none]} { set expect 80 }

  expr $nReadX>$expect
} {1}

do_test 1.5 {
  set fwd [execsql_reads {SELECT rowid FROM t1 WHERE t1 MATCH 'x' }]
  set bwd [execsql_reads {
    SELECT rowid FROM t1 WHERE t1 MATCH 'x' ORDER BY 1 ASC 
  }]
  expr {$bwd < $fwd + 12}
} {1}

foreach {tn q res} "
  1 { SELECT rowid FROM t1 WHERE t1 MATCH 'w + x'   }  [list $W]
  2 { SELECT rowid FROM t1 WHERE t1 MATCH 'x + w'   }  [list $W]
  3 { SELECT rowid FROM t1 WHERE t1 MATCH 'x AND w' }  [list $W]
  4 { SELECT rowid FROM t1 WHERE t1 MATCH 'y AND x' }  [list $Y]
" {
  if {[detail_is_full]==0 && ($tn==1 || $tn==2)} continue

  if {[detail_is_full]} { set ratio 8 }
  if {[detail_is_col]}  { set ratio 4 }
  if {[detail_is_none]} { set ratio 2 }

  do_test 1.6.$tn.1 {
    set n [execsql_reads $q]
    #puts -nonewline "(n=$n nReadX=$nReadX)"
    expr {$n < ($nReadX / $ratio)}
  } {1}

  do_test 1.6.$tn.2 {
    set n [execsql_reads "$q ORDER BY rowid DESC"]
    #puts -nonewline "(n=$n nReadX=$nReadX)"
    expr {$n < ($nReadX / $ratio)}
  } {1}

  do_execsql_test 1.6.$tn.3 $q [lsort -int -incr $res]
  do_execsql_test 1.6.$tn.4 "$q ORDER BY rowid DESC" [lsort -int -decr $res]
}

#-------------------------------------------------------------------------
# Now test that adding range constraints on the rowid field reduces the
# number of pages loaded from disk.
#
foreach {tn fraction tail cnt} {
  1  0.6 {rowid > 5000} 5000
  2  0.2 {rowid > 9000} 1000
  3  0.2 {rowid < 1000}  999
  4  0.2 {rowid BETWEEN 4000 AND 5000}  1001
  5  0.6 {rowid >= 5000} 5001
  6  0.2 {rowid >= 9000} 1001
  7  0.2 {rowid <= 1000} 1000
  8  0.6 {rowid > '5000'} 5000
  9  0.2 {rowid > '9000'} 1000
  10 0.1 {rowid = 444} 1
} {
  set q "SELECT rowid FROM t1 WHERE t1 MATCH 'x' AND $tail"
  set n [execsql_reads $q]
  set ret [llength [execsql $q]]

  # Because the position lists for 'x' are quite long in this db, the 
  # advantage is a bit smaller in detail=none mode. Update $fraction to 
  # reflect this.
  if {[detail_is_none] && $fraction<0.5} { set fraction [expr $fraction*2] }

  do_test "1.7.$tn.asc.(n=$n ret=$ret)" {
    expr {$n < ($fraction*$nReadX) && $ret==$cnt}
  } {1}

  set q "SELECT rowid FROM t1 WHERE t1 MATCH 'x' AND $tail ORDER BY rowid DESC"
  set n [execsql_reads $q]
  set ret [llength [execsql $q]]
  do_test "1.7.$tn.desc.(n=$n ret=$ret)" {
    expr {$n < 2*$fraction*$nReadX && $ret==$cnt}
  } {1}
}

do_execsql_test 1.8.1 {
  SELECT count(*) FROM t1 WHERE t1 MATCH 'x' AND +rowid < 'text';
} {10000}
do_execsql_test 1.8.2 {
  SELECT count(*) FROM t1 WHERE t1 MATCH 'x' AND rowid < 'text';
} {10000}

} ;# foreach_detail_mode

#db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM t1_data} {puts $r}

finish_test

# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

foreach_detail_mode $testprefix {

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(a, detail=%DETAIL%);
} {}

do_execsql_test 1.1 {
  BEGIN;
    INSERT INTO t1 VALUES('a b c');
    INSERT INTO t1 VALUES('d e f');
    SAVEPOINT one;

      INSERT INTO t1 VALUES('s t u');
    ROLLBACK TO one;
  COMMIT;
}

do_execsql_test 1.2 {
  INSERT INTO t1(t1) VALUES('integrity-check');
}
}


finish_test

# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

foreach_detail_mode $testprefix {

do_execsql_test 1.1 {
  CREATE VIRTUAL TABLE ft1 USING fts5(x, detail=%DETAIL%);
  INSERT INTO ft1 VALUES('i d d a g i b g d d');
  INSERT INTO ft1 VALUES('h d b j c c g a c a');
  INSERT INTO ft1 VALUES('e j a e f h b f h h');
  INSERT INTO ft1 VALUES('j f h d g h i b d f');
  INSERT INTO ft1 VALUES('d c j d c j b c g e');
  INSERT INTO ft1 VALUES('i a d e g j g d a a');
  INSERT INTO ft1 VALUES('j f c e d a h j d b');
  INSERT INTO ft1 VALUES('i c c f a d g h j e');
  INSERT INTO ft1 VALUES('i d i g c d c h b f');
  INSERT INTO ft1 VALUES('g d a e h a b c f j');

  CREATE VIRTUAL TABLE ft2 USING fts5(x, detail=%DETAIL%);
  INSERT INTO ft2 VALUES('a b c d e f g h i j');
}

do_execsql_test 1.2 {
  SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'e';
} {
  {[e] j a [e] f h b f h h}
  {d c j d c j b c g [e]}
  {i a d [e] g j g d a a}
  {j f c [e] d a h j d b}
  {i c c f a d g h j [e]}
  {g d a [e] h a b c f j}
}

do_execsql_test 1.3 {













  SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'e e e'
} {
  {[e] j a [e] f h b f h h}
  {d c j d c j b c g [e]}
  {i a d [e] g j g d a a}
  {j f c [e] d a h j d b}
  {i c c f a d g h j [e]}
  {g d a [e] h a b c f j}
}

do_execsql_test 1.4 {
  SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'f d'
} {
  {a b c [d] e [f] g h i j}
}

do_execsql_test 1.5 {
  SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'd f'
} {
  {a b c [d] e [f] g h i j}
}

#-------------------------------------------------------------------------
# Tests below this point require detail=full.
#-------------------------------------------------------------------------
if {[detail_is_full]==0} continue


do_execsql_test 2.1 {
  SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'h + d';
} {
  {[h d] b j c c g a c a}
  {j f [h d] g h i b d f} 
}

do_execsql_test 2.2 {
  SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'd + d';
} {
  {i [d d] a g i b g [d d]}
}

do_execsql_test 2.3 {


  SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'd + d d + d';
} {
  {i [d d] a g i b g [d d]}
}

do_execsql_test 2.4 {
  SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c+d c+d+e'
} {{a [b c d e] f g h i j}}

do_execsql_test 2.5 {
  SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c+d e+f+g'
} {
  {a [b c d] [e f g] h i j}
}

do_execsql_test 2.6 {
  SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c+d c'
} {
  {a [b c d] e f g h i j}
}

do_execsql_test 2.7 {
  SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c c+d+e'
} {
  {a [b c d e] f g h i j}
}













#-------------------------------------------------------------------------
# The example from the docs.
#
do_execsql_test 3.1 {
  -- Assuming this:
  CREATE VIRTUAL TABLE ft USING fts5(a, detail=%DETAIL%);
  INSERT INTO ft VALUES('a b c x c d e');
  INSERT INTO ft VALUES('a b c c d e');
  INSERT INTO ft VALUES('a b c d e');

  -- The following SELECT statement returns these three rows:
  --   '[a b c] x [c d e]'
  --   '[a b c] [c d e]'
  --   '[a b c d e]'
  SELECT highlight(ft, 0, '[', ']') FROM ft WHERE ft MATCH 'a+b+c AND c+d+e';
} {
  {[a b c] x [c d e]}
  {[a b c] [c d e]}
  {[a b c d e]}
}

}

finish_test

# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

foreach_detail_mode $testprefix {

do_execsql_test 1.1 {
  CREATE VIRTUAL TABLE ft1 USING fts5(x, detail=%DETAIL%);
  SELECT * FROM ft1_config;
} {version 4}

do_execsql_test 1.2 {
  INSERT INTO ft1(ft1, rank) VALUES('pgsz', 32);
  SELECT * FROM ft1_config;
} {pgsz 32 version 4}

}

#-------------------------------------------------------------------------
# Assorted tests of the tcl interface for creating extension functions.
#

do_execsql_test 3.1 {
  CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL%);
  INSERT INTO t1 VALUES('q w e r t y');
  INSERT INTO t1 VALUES('y t r e w q');
}

proc argtest {cmd args} { return $args }
sqlite3_fts5_create_function db argtest argtest

do_execsql_test 3.4.1 {
  SELECT insttest(t1) FROM t1 WHERE t1 MATCH 'q'
} {
  {{0 0 0}}
  {{0 0 5}} 
}

if {[detail_is_full]} {
  do_execsql_test 3.4.2 {
    SELECT insttest(t1) FROM t1 WHERE t1 MATCH 'r+e OR w'
  } {
    {{1 0 1}}
    {{0 0 2} {1 0 4}} 
  }
}

proc coltest {cmd} {
  list [$cmd xColumnSize 0] [$cmd xColumnText 0]
}
sqlite3_fts5_create_function db coltest coltest

# Tests for remapping the "rank" column.
#
#   4.1.*: Mapped to a function with no arguments.
#   4.2.*: Mapped to a function with one or more arguments.
#

do_execsql_test 4.0 {
  CREATE VIRTUAL TABLE t2 USING fts5(a, b, detail=%DETAIL%);
  INSERT INTO t2 VALUES('a s h g s b j m r h', 's b p a d b b a o e');
  INSERT INTO t2 VALUES('r h n t a g r d d i', 'l d n j r c f t o q');
  INSERT INTO t2 VALUES('q k n i k c a a e m', 'c h n j p g s c i t');
  INSERT INTO t2 VALUES('h j g t r e l s g s', 'k q k c i i c k n s');
  INSERT INTO t2 VALUES('b l k h d n n n m i', 'p t i a r b t q o l');
  INSERT INTO t2 VALUES('k r i l j b g i p a', 't q c h a i m g n l');
  INSERT INTO t2 VALUES('a e c q n m o m d g', 'l c t g i s q g q e');

}

proc rowidplus {cmd ival} { 
  expr [$cmd xRowid] + $ival
}
sqlite3_fts5_create_function db rowidplus rowidplus

if {[detail_is_full]} {
  do_execsql_test 4.2.1 {
    INSERT INTO t2(t2, rank) VALUES('rank', 'rowidplus(100) ');
    SELECT rowid, rank FROM t2 WHERE t2 MATCH 'o + q + g'
  } {
    10 110
  }
  do_execsql_test 4.2.2 {
    INSERT INTO t2(t2, rank) VALUES('rank', 'rowidplus(111) ');
    SELECT rowid, rank FROM t2 WHERE t2 MATCH 'o + q + g'
  } {
    10 121
  }

  do_execsql_test 4.2.3 {
    SELECT rowid, rank FROM t2 
      WHERE t2 MATCH 'o + q + g' AND rank MATCH 'rowidplus(112)'
  } {
    10 122
  }
}

proc rowidmod {cmd imod} { 
  expr [$cmd xRowid] % $imod
}
sqlite3_fts5_create_function db rowidmod rowidmod
do_execsql_test 4.3.1 {
  CREATE VIRTUAL TABLE t3 USING fts5(x, detail=%DETAIL%);
  INSERT INTO t3 VALUES('a one');
  INSERT INTO t3 VALUES('a two');
  INSERT INTO t3 VALUES('a three');
  INSERT INTO t3 VALUES('a four');
  INSERT INTO t3 VALUES('a five');
  INSERT INTO t3(t3, rank) VALUES('rank', 'bm25()');
}

do_catchsql_test 4.4.3 {
  SELECT *, rank FROM t3 WHERE t3 MATCH 'a' AND rank MATCH 'xyz(3)' 
} {1 {no such function: xyz}}
do_catchsql_test 4.4.4 {
  SELECT *, rank FROM t3 WHERE t3 MATCH 'a' AND rank MATCH NULL
} {1 {parse error in rank function: }}

} ;# foreach_detail_mode


finish_test