[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/ext/fts5/test/fts5ab.test

+# 2014 June 17
+#
+# 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 fts5ab
+
+# 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, b, detail=%DETAIL%);
+  INSERT INTO t1 VALUES('hello', 'world');
+  INSERT INTO t1 VALUES('one two', 'three four');
+  INSERT INTO t1(rowid, a, b) VALUES(45, 'forty', 'five');
+}
+
+do_execsql_test 1.1 {
+  SELECT * FROM t1 ORDER BY rowid DESC;
+} { forty five {one two} {three four} hello world }
+
+do_execsql_test 1.2 {
+  SELECT rowid FROM t1 ORDER BY rowid DESC;
+} {45 2 1}
+
+do_execsql_test 1.3 {
+  SELECT rowid FROM t1 ORDER BY rowid ASC;
+} {1 2 45}
+
+do_execsql_test 1.4 {
+  SELECT * FROM t1 WHERE rowid=2;
+} {{one two} {three four}}
+
+do_execsql_test 1.5 {
+  SELECT * FROM t1 WHERE rowid=2.01;
+} {}
+
+do_execsql_test 1.6 {
+  SELECT * FROM t1 WHERE rowid=1.99;
+} {}
+
+#-------------------------------------------------------------------------
+
+reset_db
+do_execsql_test 2.1 {
+  CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL%);
+  INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
+  INSERT INTO t1 VALUES('one');
+  INSERT INTO t1 VALUES('two');
+  INSERT INTO t1 VALUES('three');
+}
+
+do_catchsql_test 2.2 {
+  SELECT rowid, * FROM t1 WHERE t1 MATCH 'AND AND'
+} {1 {fts5: syntax error near "AND"}}
+
+do_execsql_test 2.3 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'two' } {2 two}
+do_execsql_test 2.4 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'three' } {3 three}
+do_execsql_test 2.5 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'one' } {1 one}
+
+do_execsql_test 2.6 {
+  INSERT INTO t1 VALUES('a b c d e f g');
+  INSERT INTO t1 VALUES('b d e a a a i');
+  INSERT INTO t1 VALUES('x y z b c c c');
+}
+
+foreach {tn expr res} {
+  1  a    {5 4}
+  2  b    {6 5 4}
+  3  c    {6 4}
+  4  d    {5 4}
+  5  e    {5 4}
+  6  f    {4}
+  7  g    {4}
+  8  x    {6}
+  9  y    {6}
+  10 z    {6}
+} {
+  do_execsql_test 2.7.$tn.1 { 
+    SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid DESC
+  } $res
+  do_execsql_test 2.7.$tn.2 { 
+    SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid ASC
+  } [lsort -integer $res]
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 3.0 {
+  CREATE VIRTUAL TABLE t1 USING fts5(a,b);
+  INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
+}
+
+foreach {tn a b} {
+   1 {abashed abandons abase abash abaft} {abases abased}
+   2 {abasing abases abaft abated abandons} {abases abandoned}
+   3 {abatement abash abash abated abase} {abasements abashing}
+   4 {abaft abasements abase abasement abasing} {abasement abases}
+   5 {abaft abashing abatement abash abasements} {abandons abandoning}
+   6 {aback abate abasements abashes abandoned} {abasement abased}
+   7 {abandons abated abased aback abandoning} {abases abandoned}
+   8 {abashing abases abasement abaft abashing} {abashed abate}
+   9 {abash abase abate abashing abashed} {abandon abandoned}
+   10 {abate abandoning abandons abasement aback} {abandon abandoning}
+} {
+  do_execsql_test 3.1.$tn.1 { INSERT INTO t1 VALUES($a, $b) } 
+  do_execsql_test 3.1.$tn.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
+}
+
+foreach {tn expr res} {
+  1 {abash} {9 5 3 1}
+  2 {abase} {9 4 3 1}
+  3 {abase + abash} {1}
+  4 {abash + abase} {9}
+  5 {abaft + abashing} {8 5}
+  6 {abandon + abandoning} {10}
+  7 {"abashing abases abasement abaft abashing"} {8}
+} {
+  do_execsql_test 3.2.$tn {
+    SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid DESC
+  } $res
+}
+
+do_execsql_test 3.3 {
+  SELECT rowid FROM t1 WHERE t1 MATCH 'NEAR(aback abate, 2)'
+} {6}
+
+foreach {tn expr res} {
+  1 {abash} {1 3 5 9}
+  2 {abase} {1 3 4 9}
+  3 {abase + abash} {1}
+  4 {abash + abase} {9}
+  5 {abaft + abashing} {5 8}
+  6 {abandon + abandoning} {10}
+  7 {"abashing abases abasement abaft abashing"} {8}
+} {
+  do_execsql_test 3.4.$tn {
+    SELECT rowid FROM t1 WHERE t1 MATCH $expr
+  } $res
+}
+
+#-------------------------------------------------------------------------
+# Documents with more than 2M tokens.
+#
+
+do_execsql_test 4.0 {
+  CREATE VIRTUAL TABLE s1 USING fts5(x, detail=%DETAIL%);
+}
+foreach {tn doc} [list \
+  1 [string repeat {a x } 1500000]       \
+  2 "[string repeat {a a } 1500000] x"   \
+] {
+  do_execsql_test 4.$tn { INSERT INTO s1 VALUES($doc) }
+}
+
+do_execsql_test 4.3 {
+  SELECT rowid FROM s1 WHERE s1 MATCH 'x'
+} {1 2}
+
+if {[detail_is_full]} {
+  do_execsql_test 4.4 {
+    SELECT rowid FROM s1 WHERE s1 MATCH '"a x"'
+  } {1 2}
+}
+
+do_execsql_test 4.5 {
+  SELECT rowid FROM s1 WHERE s1 MATCH 'a x'
+} {1 2}
+
+#-------------------------------------------------------------------------
+# Check that a special case of segment promotion works. The case is where
+# a new segment is written to level L, but the oldest segment within level
+# (L-2) is larger than it.
+#
+do_execsql_test 5.0 {
+  CREATE VIRTUAL TABLE s2 USING fts5(x, detail=%DETAIL%);
+  INSERT INTO s2(s2, rank) VALUES('pgsz', 32);
+  INSERT INTO s2(s2, rank) VALUES('automerge', 0);
+}
+
+proc rnddoc {n} {
+  set map [list 0 a  1 b  2 c  3 d  4 e  5 f  6 g  7 h  8 i  9 j]
+  set doc [list]
+  for {set i 0} {$i < $n} {incr i} {
+    lappend doc [string map $map [format %.3d [expr int(rand()*1000)]]]
+  }
+  set doc
+}
+db func rnddoc rnddoc
+
+do_test 5.1 {
+  for {set i 1} {$i <= 65} {incr i} {
+    execsql { INSERT INTO s2 VALUES(rnddoc(10)) }
+  }
+  for {set i 1} {$i <= 63} {incr i} {
+    execsql { DELETE FROM s2 WHERE rowid = $i }
+  }
+  fts5_level_segs s2
+} {0 8}
+
+do_test 5.2 {
+  execsql {
+    INSERT INTO s2(s2, rank) VALUES('automerge', 8);
+  }
+  for {set i 0} {$i < 7} {incr i} {
+    execsql { INSERT INTO s2 VALUES(rnddoc(50)) }
+  }
+  fts5_level_segs s2
+} {8 0 0}
+
+# Test also the other type of segment promotion - when a new segment is written
+# that is larger than segments immediately following it.
+do_test 5.3 {
+  execsql {
+    DROP TABLE s2;
+    CREATE VIRTUAL TABLE s2 USING fts5(x, detail=%DETAIL%);
+    INSERT INTO s2(s2, rank) VALUES('pgsz', 32);
+    INSERT INTO s2(s2, rank) VALUES('automerge', 0);
+  }
+
+  for {set i 1} {$i <= 16} {incr i} {
+    execsql { INSERT INTO s2 VALUES(rnddoc(5)) }
+  }
+  fts5_level_segs s2
+} {0 1}
+
+do_test 5.4 {
+  execsql { INSERT INTO s2 VALUES(rnddoc(160)) }
+  fts5_level_segs s2
+} {2 0}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 6.0 {
+  CREATE VIRTUAL TABLE s3 USING fts5(x, detail=%DETAIL%);
+  BEGIN;
+    INSERT INTO s3 VALUES('a b c');
+    INSERT INTO s3 VALUES('A B C');
+}
+
+do_execsql_test 6.1.1 {
+  SELECT rowid FROM s3 WHERE s3 MATCH 'a'
+} {1 2}
+
+do_execsql_test 6.1.2 {
+  SELECT rowid FROM s3 WHERE s3 MATCH 'a' ORDER BY rowid DESC
+} {2 1}
+
+do_execsql_test 6.2 {
+  COMMIT;
+}
+
+do_execsql_test 6.3 {
+  SELECT rowid FROM s3 WHERE s3 MATCH 'a'
+} {1 2}
+
+do_test 6.4 {
+  db close
+  sqlite3 db test.db
+  execsql {
+    BEGIN;
+      INSERT INTO s3(s3) VALUES('optimize');
+    ROLLBACK;
+  }
+} {}
+
+#-------------------------------------------------------------------------
+#
+set doc [string repeat "a b c " 500]
+do_execsql_test 7.0 {
+  CREATE VIRTUAL TABLE x1 USING fts5(x, detail=%DETAIL%);
+  INSERT INTO x1(x1, rank) VALUES('pgsz', 32);
+  INSERT INTO x1 VALUES($doc);
+}
+
+} ;# foreach_detail_mode...
+
+
+finish_test
+