[sql] Import reference version of SQLite 3.8.7.4.

third_party/sqlite/sqlite-src-3080704/test/fts4aa.test

+# 2010 February 02
+#
+# 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 FTS4 module.
+#
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# If SQLITE_ENABLE_FTS3 is defined, omit this file.
+ifcapable !fts3 {
+  finish_test
+  return
+}
+
+# Create the fts_kjv_genesis procedure which fills and FTS3/4 table with
+# the complete text of the Book of Genesis.
+#
+source $testdir/genesis.tcl
+
+# The following is a list of queries to perform against the above
+# FTS3/FTS4 database.  We will be trying these queries in various
+# configurations to ensure that they always return the same answers.
+#
+set fts4aa_queries {
+  {abraham}
+  {the king}
+  {"the king"}
+  {abraham OR joseph}
+  {ab* OR jos*}
+  {lived t*}
+  {spake hebrew}
+  {melchizedek}
+  {t* melchizedek}
+  {melchizedek t*}
+}
+unset -nocomplain fts4aa_res
+
+# Set up the baseline results
+#
+do_test fts4aa-1.0 {
+  db eval {
+    CREATE VIRTUAL TABLE t1 USING fts4(words, tokenize porter);
+  }
+  fts_kjv_genesis
+  foreach q $::fts4aa_queries {
+    set r [db eval {SELECT docid FROM t1 WHERE words MATCH $q ORDER BY docid}]
+    set ::fts4aa_res($q) $r
+  }
+} {}
+
+# Legacy test cases
+#
+do_test fts4aa-1.1 {
+  db eval {
+    SELECT docid FROM t1 EXCEPT SELECT docid FROM t1_docsize
+  }
+} {}
+do_test fts4aa-1.2 {
+  db eval {
+    SELECT docid FROM t1_docsize EXCEPT SELECT docid FROM t1
+  }
+} {}
+
+proc mit {blob} {
+  set scan(littleEndian) i*
+  set scan(bigEndian) I*
+  binary scan $blob $scan($::tcl_platform(byteOrder)) r
+  return $r
+}
+db func mit mit
+
+do_test fts4aa-1.3 {
+  db eval {
+    SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t1 WHERE t1 MATCH 'melchizedek';
+  }
+} {1014018 {1 1 1 1 1 1533 25 20}}
+do_test fts4aa-1.4 {
+  db eval {
+    SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t1
+     WHERE t1 MATCH 'spake hebrew'
+     ORDER BY docid;
+  }
+} {1039014 {2 1 1 40 40 1 6 6 1533 25 42} 1039017 {2 1 1 40 40 1 6 6 1533 25 26}}
+do_test fts4aa-1.5 {
+  db eval {
+    SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t1
+     WHERE t1 MATCH 'laban overtook jacob'
+     ORDER BY docid;
+  }
+} {1031025 {3 1 2 54 46 1 3 3 2 181 160 1533 25 24}}
+
+do_test fts4aa-1.6 {
+  db eval {
+    DELETE FROM t1 WHERE docid!=1050026;
+    SELECT hex(size) FROM t1_docsize;
+    SELECT hex(value) FROM t1_stat;
+  }
+} {17 01176F}
+
+do_test fts4aa-1.7 {
+  db eval {
+    SELECT docid FROM t1 EXCEPT SELECT docid FROM t1_docsize
+  }
+} {}
+do_test fts4aa-1.8 {
+  db eval {
+    SELECT docid FROM t1_docsize EXCEPT SELECT docid FROM t1
+  }
+} {}
+ifcapable fts4_deferred {
+  do_test fts4aa-1.9 {
+    # Note: Token 'in' is being deferred in the following query. 
+    db eval {
+      SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t1
+       WHERE t1 MATCH 'joseph died in egypt'
+       ORDER BY docid;
+    }
+  } {1050026 {4 1 1 1 1 1 1 1 2 1 1 1 1 1 1 23 23}}
+}
+
+# Should get the same search results from FTS3
+#
+do_test fts4aa-2.0 {
+  db eval {
+    DROP TABLE t1;
+    CREATE VIRTUAL TABLE t1 USING fts3(words, tokenize porter);
+  }
+  fts_kjv_genesis
+} {}
+unset -nocomplain ii
+set ii 0
+foreach {q r} [array get fts4aa_res] {
+  incr ii
+  do_test fts4aa-2.$ii {
+    db eval {SELECT docid FROM t1 WHERE words MATCH $::q ORDER BY docid}
+  } $r
+}
+
+# Should get the same search results when the page size is very large
+#
+do_test fts4aa-3.0 {
+  db close
+  forcedelete test.db
+  sqlite3 db test.db
+  db eval {
+    PRAGMA page_size=65536;
+    CREATE VIRTUAL TABLE t1 USING fts4(words, tokenize porter);
+  }
+  fts_kjv_genesis
+} {}
+unset -nocomplain ii
+set ii 0
+foreach {q r} [array get fts4aa_res] {
+  incr ii
+  do_test fts4aa-3.$ii {
+    db eval {SELECT docid FROM t1 WHERE words MATCH $::q ORDER BY docid}
+  } $r
+}
+
+# Should get the same search results when an authorizer prevents
+# all PRAGMA statements.
+#
+proc no_pragma_auth {code arg1 arg2 arg3 arg4 args} {
+  if {$code=="SQLITE_PRAGMA"} {return SQLITE_DENY}
+  return SQLITE_OK;
+}
+do_test fts4aa-4.0 {
+  db auth ::no_pragma_auth
+  db eval {
+    DROP TABLE t1;
+    CREATE VIRTUAL TABLE t1 USING fts4(words, tokenize porter);
+  }
+  fts_kjv_genesis
+} {}
+unset -nocomplain ii
+set ii 0
+foreach {q r} [array get fts4aa_res] {
+  incr ii
+  do_test fts4aa-4.$ii {
+    db eval {SELECT docid FROM t1 WHERE words MATCH $::q ORDER BY docid}
+  } $r
+}
+
+finish_test