third_party/sqlite/sqlite-src-3270200/test/fts4content.test - chromium/src - Git at Google

 # 2011 October 03
 #
 # 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 content=xxx FTS4 option.
 #

 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 set ::testprefix fts4content

 # If SQLITE_ENABLE_FTS3 is defined, omit this file.
 ifcapable !fts3 {
   finish_test
   return
 }

 #-------------------------------------------------------------------------
 # Test organization:
 #
 #   1.* - Warm-body tests.
 #
 #   2.* - Querying a content=xxx FTS table.
 #
 #   3.* - Writing to a content=xxx FTS table.
 #
 #   4.* - The "INSERT INTO fts(fts) VALUES('rebuild')" command.
 #
 #   5.* - Check that CREATE TABLE, DROP TABLE and ALTER TABLE correctly
 #         ignore any %_content table when used with the content=xxx option.
 #
 #   6.* - Test the effects of messing with the schema of table xxx after
 #         creating a content=xxx FTS index.
 #
 #   7.* - Test that if content=xxx is specified and table xxx does not
 #         exist, the FTS table can still be used for INSERT and some
 #         SELECT statements.
 #
 #   8.* - Test that if the content=xxx and prefix options are used together,
 #         the 'rebuild' command still works.
 #
 #   9.* - Test using content=xxx where xxx is a virtual table.
 #
 #   11.* - Test that circular references (e.g. "t1(content=t1)") are
 #          detected.
 #

 do_execsql_test 1.1.1 {
   CREATE TABLE t1(a, b, c);
   INSERT INTO t1 VALUES('w x', 'x y', 'y z');
   CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
 }

 do_execsql_test 1.1.2 {
   PRAGMA table_info(ft1);
 } {
   0 a {} 0 {} 0
   1 b {} 0 {} 0
   2 c {} 0 {} 0
 }

 do_execsql_test 1.1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}
 do_execsql_test 1.1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}

 do_execsql_test 1.1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}
 do_execsql_test 1.1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}
 do_execsql_test 1.1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}

 do_execsql_test 1.2.1 {
   DROP TABLE ft1;
   CREATE VIRTUAL TABLE ft1 USING fts4(content=t1, b);
   PRAGMA table_info(ft1);
 } {
   0 b {} 0 {} 0
 }
 do_execsql_test 1.2.2 {
   SELECT *, rowid FROM ft1
 } {{x y} 1}

 #-------------------------------------------------------------------------
 # The following block of tests - 2.* - test that a content=xxx FTS table
 # can be queried. Also tested are cases where rows identified in the FTS
 # are missing from the content table, and cases where the index is
 # inconsistent with the content table.
 #
 do_execsql_test 2.0 {
   CREATE TABLE t2(x);
   INSERT INTO t2 VALUES('O S W W F U C R Q I C N P Z Y Y E Y Y E');  -- 1
   INSERT INTO t2 VALUES('Y X U V L B E H Y J C Y A I A P V F V K');  -- 2
   INSERT INTO t2 VALUES('P W I N J H I I N I F B K D U Q B Z S F');  -- 3
   INSERT INTO t2 VALUES('N R O R H J R H G M D I U U B O M P A U');  -- 4
   INSERT INTO t2 VALUES('Y O V O G T P N G T N F I V B U M J M G');  -- 5
   INSERT INTO t2 VALUES('J O B N K N E C H Z R K J O U G M K L S');  -- 6
   INSERT INTO t2 VALUES('S Z S R I Q U A P W R X H K C Z U L S P');  -- 7
   INSERT INTO t2 VALUES('J C H N R C K R V N M O F Z M Z A I H W');  -- 8
   INSERT INTO t2 VALUES('O Y G I S J U U W O D Z F J K N R P R L');  -- 9
   INSERT INTO t2 VALUES('B G L K U R U P V X Z I H V R W C Q A S');  -- 10
   INSERT INTO t2 VALUES('T F T J F F Y V F W N X K Q A Y L X W G');  -- 11
   INSERT INTO t2 VALUES('C J U H B Q X L C M M Y E G V F W V Z C');  -- 12
   INSERT INTO t2 VALUES('B W L T F S G X D P H N G M R I O A X I');  -- 13
   INSERT INTO t2 VALUES('N G Y O K Q K Z N M H U J E D H U W R K');  -- 14
   INSERT INTO t2 VALUES('U D T R U Y F J D S J X E H Q G V A S Z');  -- 15
   INSERT INTO t2 VALUES('M I W P J S H R J D Q I C G P C T P H R');  -- 16
   INSERT INTO t2 VALUES('J M N I S L X Q C A B F C B Y D H V R J');  -- 17
   INSERT INTO t2 VALUES('F V Z W J Q L P X Y E W B U Q N H X K T');  -- 18
   INSERT INTO t2 VALUES('R F S R Y O F Q E I E G H C B H R X Y N');  -- 19
   INSERT INTO t2 VALUES('U Q Q Q T E P D M F X P J G H X C Q D L');  -- 20
 }

 do_execsql_test 2.1 {
   CREATE VIRTUAL TABLE ft2 USING fts4(content=t2);
   INSERT INTO ft2(ft2) VALUES('rebuild');

   -- Modify the backing table a bit: Row 17 is missing and the contents
   -- of row 20 do not match the FTS index contents.
   DELETE FROM t2 WHERE rowid = 17;
   UPDATE t2 SET x = 'a b c d e f g h i j' WHERE rowid = 20;
 }

 foreach {tn match rowidlist} {
   1   {S}        {1 3 6 7 9 10 13 15 16 17 19}
   2   {"S R"}    {7 19}
   3   {"N K N"}  {6}
   4   {"Q Q"}    {20}
   5   {"B Y D"}  {17}
 } {
   do_execsql_test 2.2.1.$tn {
     SELECT rowid FROM ft2 WHERE ft2 MATCH $match
   } $rowidlist

   do_execsql_test 2.2.2.$tn {
     SELECT docid FROM ft2 WHERE ft2 MATCH $match
   } $rowidlist
 }

 foreach {tn match result} {
   1   {"N K N"}  {{J O B N K N E C H Z R K J O U G M K L S}}
   2   {"Q Q"}    {{a b c d e f g h i j}}
   3   {"B Y D"}  {{}}
 } {
   do_execsql_test 2.3.$tn {
     SELECT * FROM ft2 WHERE ft2 MATCH $match
   } $result
 }

 foreach {tn match result} {
   1   {"N K N"}  {{..O B [N] [K] [N] E..}}
   2   {"B Y D"}  {{}}
   3   {"Q Q"}    {{a [b] [c] [d] e f..}}
 } {
   do_execsql_test 2.4.$tn {
     SELECT snippet(ft2, '[', ']', '..', -1, 6) FROM ft2 WHERE ft2 MATCH $match
   } $result
 }

 foreach {tn match result} {
   1   {"N K N"}  {{0 0 6 1 0 1 8 1 0 2 10 1}}
   2   {"B Y D"}  {{}}
   3   {"Q Q"}    {{0 0 2 1 0 0 4 1 0 1 4 1 0 1 6 1}}
   4   {"Q D L"}  {{}}
 } {
   do_execsql_test 2.5.$tn {
     SELECT offsets(ft2) FROM ft2 WHERE ft2 MATCH $match
   } $result
 }

 #-------------------------------------------------------------------------
 # The following block of tests - 3.* - test that the FTS index can be
 # modified by writing to the table. But that this has no effect on the
 # content table.
 #

 do_execsql_test 3.1 {
   CREATE TABLE t3(x, y);
   CREATE VIRTUAL TABLE ft3 USING fts4(content=t3);
 }

 do_catchsql_test 3.1.1 {
   INSERT INTO ft3 VALUES('a b c', 'd e f');
 } {1 {constraint failed}}
 do_execsql_test 3.1.2 {
   INSERT INTO ft3(docid, x, y) VALUES(21, 'a b c', 'd e f');
   SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
 } {21}
 do_execsql_test 3.1.3 { SELECT * FROM t3 } {}

 # This DELETE does not work, since there is no row in [t3] to base the
 # DELETE on. So the SELECT on [ft3] still returns rowid 21.
 do_execsql_test 3.1.4 {
   DELETE FROM ft3;
   SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
 } {21}

 # If the row is added to [t3] before the DELETE on [ft3], it works.
 do_execsql_test 3.1.5 {
   INSERT INTO t3(rowid, x, y) VALUES(21, 'a b c', 'd e f');
   DELETE FROM ft3;
   SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
 } {}
 do_execsql_test 3.1.6 { SELECT rowid FROM t3 } {21}

 do_execsql_test 3.2.1 {
   INSERT INTO ft3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
   INSERT INTO ft3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
   INSERT INTO ft3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
   INSERT INTO ft3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
   INSERT INTO ft3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
   INSERT INTO ft3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
   INSERT INTO ft3(rowid, x, y) VALUES(6, 'I Q I S P', 'D R O Q B');
   INSERT INTO ft3(rowid, x, y) VALUES(7, 'T K T Z J', 'B W D G O');
   INSERT INTO ft3(rowid, x, y) VALUES(8, 'Y K F X T', 'D F G V G');
   INSERT INTO ft3(rowid, x, y) VALUES(9, 'E L E T L', 'P W N F Z');
   INSERT INTO ft3(rowid, x, y) VALUES(10, 'O G J G X', 'G J F E P');
   INSERT INTO ft3(rowid, x, y) VALUES(11, 'O L N N Z', 'K E Z F D');
   INSERT INTO ft3(rowid, x, y) VALUES(12, 'R Z M R J', 'X G I M Z');
   INSERT INTO ft3(rowid, x, y) VALUES(13, 'L X N N X', 'R R N S T');
   INSERT INTO ft3(rowid, x, y) VALUES(14, 'F L B J H', 'K W F L C');
   INSERT INTO ft3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
   INSERT INTO ft3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
   INSERT INTO ft3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
   INSERT INTO ft3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
   INSERT INTO ft3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
 }

 foreach {tn match rowidlist} {
   1   "N A"    {5 19}
   2   "x:O"    {1 2 10 11 17}
   3   "y:O"    {0 2 6 7 18 19}
 } {
   set res [list]
   foreach rowid $rowidlist { lappend res $rowid {} {} }

   do_execsql_test 3.2.2.$tn {
     SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
   } $res
   do_execsql_test 3.2.3.$tn {
     SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
   } $res
 }

 do_execsql_test 3.3.1 {
   INSERT INTO t3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
   INSERT INTO t3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
   INSERT INTO t3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
   INSERT INTO t3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
   INSERT INTO t3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
   INSERT INTO t3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
   UPDATE ft3 SET x = y, y = x;
   DELETE FROM t3;
 }

 foreach {tn match rowidlist} {
   1   "N A"    {5 19}
   2   "x:O"    {0 2 10 11 17}
   3   "y:O"    {1 2 6 7 18 19}
 } {
   set res [list]
   foreach rowid $rowidlist { lappend res $rowid {} {} }

   do_execsql_test 3.3.2.$tn {
     SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
   } $res
   do_execsql_test 3.3.3.$tn {
     SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
   } $res
 }

 do_execsql_test 3.3.1 {
   INSERT INTO t3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
   INSERT INTO t3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
   INSERT INTO t3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
   INSERT INTO t3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
   INSERT INTO t3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
   DELETE FROM ft3;
 }

 foreach {tn match rowidlist} {
   1   "N A"    {5}
   2   "x:O"    {0 2 10 11}
   3   "y:O"    {1 2 6 7}
 } {
   set res [list]
   foreach rowid $rowidlist { lappend res $rowid {} {} }

   do_execsql_test 3.3.2.$tn {
     SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
   } $res
   do_execsql_test 3.3.3.$tn {
     SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
   } $res
 }


 #-------------------------------------------------------------------------
 # Test cases 4.* test the 'rebuild' command. On content=xxx and regular
 # FTS tables.
 #
 do_execsql_test 4.0 {
   CREATE TABLE t4(x);
   CREATE VIRTUAL TABLE ft4 USING fts4(content=t4);
   CREATE VIRTUAL TABLE ft4x USING fts4(x);
 }

 do_execsql_test 4.1.1 {
   INSERT INTO ft4x(ft4x) VALUES('rebuild');
   INSERT INTO ft4(ft4) VALUES('rebuild');
 } {}
 do_execsql_test 4.1.2 {
   SELECT id, quote(value) FROM ft4_stat
 } {0 X'000000'}
 do_execsql_test 4.1.3 {
   SELECT id, quote(value) FROM ft4x_stat
 } {0 X'000000'}

 do_execsql_test 4.2.1 {
   INSERT INTO ft4x VALUES('M G M F T');
   INSERT INTO ft4x VALUES('Z Q C A U');
   INSERT INTO ft4x VALUES('N L L V');
   INSERT INTO ft4x VALUES('T F D X D');
   INSERT INTO ft4x VALUES('Z H I S D');

   SELECT id, quote(value) FROM ft4x_stat
 } {0 X'05182B'}

 do_execsql_test 4.2.2 {
   INSERT INTO ft4(rowid, x) SELECT rowid, * FROM ft4x;
   SELECT id, quote(value) FROM ft4_stat
 } {0 X'05182B'}

 do_execsql_test 4.2.3 {
   SELECT docid, quote(size) FROM ft4_docsize
 } {1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}

 do_execsql_test 4.2.4 {
   INSERT INTO ft4x(ft4x) VALUES('rebuild');
   SELECT id, quote(value) FROM ft4x_stat;
   SELECT docid, quote(size) FROM ft4x_docsize
 } {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}

 do_execsql_test 4.2.5 {
   INSERT INTO ft4(ft4) VALUES('rebuild');
   SELECT id, quote(value) FROM ft4_stat;
   SELECT docid, quote(size) FROM ft4_docsize
 } {0 X'000000'}

 do_execsql_test 4.2.6 {
   INSERT INTO t4(rowid, x) SELECT rowid, x FROM ft4x;
   INSERT INTO ft4(ft4) VALUES('rebuild');
   SELECT id, quote(value) FROM ft4_stat;
   SELECT docid, quote(size) FROM ft4_docsize
 } {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}


 #-------------------------------------------------------------------------
 # Test cases 5.* test that the following commands do not create/move or
 # delete a %_content table when used with a content=xxx FTS table.
 #
 do_execsql_test 5.1.1 {
   CREATE TABLE t5(a, b, c, d);
   CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
   SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
 } {
   t5 ft5 ft5_segments ft5_segdir
   sqlite_autoindex_ft5_segdir_1 ft5_docsize ft5_stat
 }
 do_execsql_test 5.1.2 {
   ALTER TABLE ft5 RENAME TO ft6;
   SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
 } {
   t5
 }
 do_execsql_test 5.1.3 {
   SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
 } {
   ft6 ft6_segments ft6_segdir
   sqlite_autoindex_ft6_segdir_1 ft6_docsize ft6_stat
 }
 do_execsql_test 5.1.4 {
   INSERT INTO t5 VALUES('a', 'b', 'c', 'd');
   INSERT INTO ft6(ft6) VALUES('rebuild');
   SELECT rowid FROM ft6 WHERE ft6 MATCH 'b';
 } {1}
 do_execsql_test 5.1.5 {
   DROP TABLE ft6;
   SELECT * FROM t5;
 } {a b c d}
 do_execsql_test 5.1.6 {
   SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
 } {
 }
 do_execsql_test 5.1.7 {
   CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
   CREATE TABLE t5_content(a, b);
   DROP TABLE ft5;
   SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
 } {
   t5 t5_content
 }

 #-------------------------------------------------------------------------
 # Test cases 6.* test
 #
 do_catchsql_test 6.1.1 {
   CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);
 } {1 {no such table: main.t7}}

 do_execsql_test 6.2.1 {
   CREATE TABLE t7(one, two);
   CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);
   INSERT INTO t7 VALUES('A B', 'B A');
   INSERT INTO t7 VALUES('C D', 'A A');
   SELECT * FROM ft7;
 } {
   {A B} {B A} {C D} {A A}
 }

 do_catchsql_test 6.2.2 {
   DROP TABLE t7;
   SELECT * FROM ft7;
 } {1 {SQL logic error}}

 db close
 sqlite3 db test.db
 do_execsql_test 6.2.3 {
   SELECT name FROM sqlite_master WHERE name LIKE '%t7%'
 } {
   ft7 ft7_segments ft7_segdir sqlite_autoindex_ft7_segdir_1
   ft7_docsize ft7_stat
 }
 do_catchsql_test 6.2.4 {
   SELECT * FROM ft7;
 } {1 {no such table: main.t7}}
 do_execsql_test 6.2.5 {
   CREATE TABLE t7(x, y);
   INSERT INTO t7 VALUES('A B', 'B A');
   INSERT INTO t7 VALUES('C D', 'A A');
   SELECT * FROM ft7;
 } {
   {A B} {B A} {C D} {A A}
 }

 do_execsql_test 6.2.6 {
   INSERT INTO ft7(ft7) VALUES('rebuild');
   SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
 } {2}

 do_execsql_test 6.2.7 {
   DROP TABLE t7;
   CREATE TABLE t7(x);
 }
 do_catchsql_test 6.2.8 {
   SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
 } {1 {SQL logic error}}
 do_catchsql_test 6.2.9 {
   SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
 } {1 {SQL logic error}}

 db close
 sqlite3 db test.db
 do_catchsql_test 6.2.10 {
   SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
 } {0 2}
 do_catchsql_test 6.2.11 {
   SELECT rowid, * FROM ft7 WHERE ft7 MATCH '"A A"';
 } {0 {2 {}}}

 #-------------------------------------------------------------------------
 # Test cases 7.*
 #
 do_execsql_test 7.1.1 {
   CREATE VIRTUAL TABLE ft8 USING fts4(content=nosuchtable, x);
   INSERT INTO ft8(docid, x) VALUES(13, 'U O N X G');
   INSERT INTO ft8(docid, x) VALUES(14, 'C J J U B');
   INSERT INTO ft8(docid, x) VALUES(15, 'N J Y G X');
   INSERT INTO ft8(docid, x) VALUES(16, 'R Y D O R');
   INSERT INTO ft8(docid, x) VALUES(17, 'I Y T Q O');
 }

 do_execsql_test 7.1.2 {
   SELECT docid FROM ft8 WHERE ft8 MATCH 'N';
 } {13 15}

 do_execsql_test 7.2.1 {
   CREATE VIRTUAL TABLE ft9 USING fts4(content=, x);
   INSERT INTO ft9(docid, x) VALUES(13, 'U O N X G');
   INSERT INTO ft9(docid, x) VALUES(14, 'C J J U B');
   INSERT INTO ft9(docid, x) VALUES(15, 'N J Y G X');
   INSERT INTO ft9(docid, x) VALUES(16, 'R Y D O R');
   INSERT INTO ft9(docid, x) VALUES(17, 'I Y T Q O');
 }
 do_execsql_test 7.2.2 {
   SELECT docid FROM ft9 WHERE ft9 MATCH 'N';
 } {13 15}
 do_execsql_test 7.2.3 {
   SELECT name FROM sqlite_master WHERE name LIKE 'ft9_%';
 } {ft9_segments ft9_segdir ft9_docsize ft9_stat}

 do_catchsql_test 7.2.4 {
   SELECT * FROM ft9 WHERE ft9 MATCH 'N';
 } {1 {SQL logic error}}

 #-------------------------------------------------------------------------
 # Test cases 8.*
 #
 do_execsql_test 8.1 {
   CREATE TABLE t10(a, b);
   INSERT INTO t10 VALUES(
       'abasia abasic abask', 'Abassin abastardize abatable');
   INSERT INTO t10 VALUES(
       'abate abatement abater', 'abatis abatised abaton');
   INSERT INTO t10 VALUES(
       'abator abattoir Abatua', 'abature abave abaxial');

   CREATE VIRTUAL TABLE ft10 USING fts4(content=t10, prefix="2,4", a, b);
 }

 do_execsql_test 8.2 { SELECT * FROM ft10 WHERE a MATCH 'ab*';          }
 do_execsql_test 8.3 { INSERT INTO ft10(ft10) VALUES('rebuild');        }
 do_execsql_test 8.4 { SELECT rowid FROM ft10 WHERE a MATCH 'ab*';      } {1 2 3}
 do_execsql_test 8.5 { SELECT rowid FROM ft10 WHERE b MATCH 'abav*';    } {3}
 do_execsql_test 8.6 { SELECT rowid FROM ft10 WHERE ft10 MATCH 'abas*'; } {1}

 #-------------------------------------------------------------------------
 # Test cases 9.*
 #
 reset_db
 register_echo_module [sqlite3_connection_pointer db]

 do_execsql_test 9.1 {
   CREATE TABLE tbl1(a, b);
   INSERT INTO tbl1 VALUES('a b', 'c d');
   INSERT INTO tbl1 VALUES('e f', 'a b');
   CREATE VIRTUAL TABLE e1 USING echo(tbl1);
   CREATE VIRTUAL TABLE ft1 USING fts4(content=e1);
   INSERT INTO ft1(ft1) VALUES('rebuild');
 }

 do_execsql_test 9.2 {
   SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'e'
 } {2 {e f} {a b}}

 do_execsql_test 9.3 {
   SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
 } {1 {a b} {c d} 2 {e f} {a b}}

 do_execsql_test 9.4 {
   DELETE FROM ft1 WHERE docid=1;
 }

 do_execsql_test 9.5 {
   SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
 } {2 {e f} {a b}}

 do_execsql_test 9.6 {
   INSERT INTO ft1(ft1) VALUES('rebuild');
   SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
 } {1 {a b} {c d} 2 {e f} {a b}}


 #-------------------------------------------------------------------------
 # Test cases 10.*
 #
 reset_db
 register_fs_module [sqlite3_connection_pointer db]

 proc write_file {path text} {
   set fd [open $path w]
   puts -nonewline $fd $text
   close $fd
 }

 write_file t1.txt {a b c d e f g h i j k l m n o p q r s t u v w x y z}
 write_file t2.txt {a b c d e f g h i j k l m a b c d e f g h i j k l m}
 write_file t3.txt {n o p q r s t u v w x y z n o p q r s t u v w x y z}

 do_execsql_test 10.1 {
   CREATE TABLE idx(id INTEGER PRIMARY KEY, path TEXT);
   INSERT INTO idx VALUES (1, 't1.txt');
   INSERT INTO idx VALUES (2, 't2.txt');
   INSERT INTO idx VALUES (3, 't3.txt');

   CREATE VIRTUAL TABLE vt USING fs(idx);
   SELECT path, data FROM vt;
 } {
   1 {a b c d e f g h i j k l m n o p q r s t u v w x y z}
   2 {a b c d e f g h i j k l m a b c d e f g h i j k l m}
   3 {n o p q r s t u v w x y z n o p q r s t u v w x y z}
 }

 do_execsql_test 10.2 {
   SELECT path, data FROM vt WHERE rowid = 2;
 } {
   2 {a b c d e f g h i j k l m a b c d e f g h i j k l m}
 }

 do_execsql_test 10.3 {
   CREATE VIRTUAL TABLE ft USING fts4(content=vt);
   INSERT INTO ft(ft) VALUES('rebuild');
 }

 do_execsql_test 10.4 {
   SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
 } {
   {...c d [e] f g...} {...c d [e] f g...}
 }

 do_execsql_test 10.5 {
   SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 't'
 } {
   {...r s [t] u v...} {...r s [t] u v...}
 }

 do_execsql_test 10.6 { DELETE FROM ft WHERE docid=2 }

 do_execsql_test 10.7 {
   SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
 } {
   {...c d [e] f g...}
 }

 #-------------------------------------------------------------------------
 # Test cases 11.*
 #
 reset_db

 do_catchsql_test 11.1 {
   CREATE VIRTUAL TABLE x1 USING fts4(content=x1);
 } {1 {vtable constructor called recursively: x1}}


 finish_test
	# 2011 October 03
	#
	# 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 content=xxx FTS4 option.
	#

	set testdir [file dirname $argv0]
	source $testdir/tester.tcl
	set ::testprefix fts4content

	# If SQLITE_ENABLE_FTS3 is defined, omit this file.
	ifcapable !fts3 {
	finish_test
	return
	}

	#-------------------------------------------------------------------------
	# Test organization:
	#
	# 1.* - Warm-body tests.
	#
	# 2.* - Querying a content=xxx FTS table.
	#
	# 3.* - Writing to a content=xxx FTS table.
	#
	# 4.* - The "INSERT INTO fts(fts) VALUES('rebuild')" command.
	#
	# 5.* - Check that CREATE TABLE, DROP TABLE and ALTER TABLE correctly
	# ignore any %_content table when used with the content=xxx option.
	#
	# 6.* - Test the effects of messing with the schema of table xxx after
	# creating a content=xxx FTS index.
	#
	# 7.* - Test that if content=xxx is specified and table xxx does not
	# exist, the FTS table can still be used for INSERT and some
	# SELECT statements.
	#
	# 8.* - Test that if the content=xxx and prefix options are used together,
	# the 'rebuild' command still works.
	#
	# 9.* - Test using content=xxx where xxx is a virtual table.
	#
	# 11.* - Test that circular references (e.g. "t1(content=t1)") are
	# detected.
	#

	do_execsql_test 1.1.1 {
	CREATE TABLE t1(a, b, c);
	INSERT INTO t1 VALUES('w x', 'x y', 'y z');
	CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
	}

	do_execsql_test 1.1.2 {
	PRAGMA table_info(ft1);
	} {
	0 a {} 0 {} 0
	1 b {} 0 {} 0
	2 c {} 0 {} 0
	}

	do_execsql_test 1.1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}
	do_execsql_test 1.1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}

	do_execsql_test 1.1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}
	do_execsql_test 1.1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}
	do_execsql_test 1.1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}

	do_execsql_test 1.2.1 {
	DROP TABLE ft1;
	CREATE VIRTUAL TABLE ft1 USING fts4(content=t1, b);
	PRAGMA table_info(ft1);
	} {
	0 b {} 0 {} 0
	}
	do_execsql_test 1.2.2 {
	SELECT *, rowid FROM ft1
	} {{x y} 1}

	#-------------------------------------------------------------------------
	# The following block of tests - 2.* - test that a content=xxx FTS table
	# can be queried. Also tested are cases where rows identified in the FTS
	# are missing from the content table, and cases where the index is
	# inconsistent with the content table.
	#
	do_execsql_test 2.0 {
	CREATE TABLE t2(x);
	INSERT INTO t2 VALUES('O S W W F U C R Q I C N P Z Y Y E Y Y E'); -- 1
	INSERT INTO t2 VALUES('Y X U V L B E H Y J C Y A I A P V F V K'); -- 2
	INSERT INTO t2 VALUES('P W I N J H I I N I F B K D U Q B Z S F'); -- 3
	INSERT INTO t2 VALUES('N R O R H J R H G M D I U U B O M P A U'); -- 4
	INSERT INTO t2 VALUES('Y O V O G T P N G T N F I V B U M J M G'); -- 5
	INSERT INTO t2 VALUES('J O B N K N E C H Z R K J O U G M K L S'); -- 6
	INSERT INTO t2 VALUES('S Z S R I Q U A P W R X H K C Z U L S P'); -- 7
	INSERT INTO t2 VALUES('J C H N R C K R V N M O F Z M Z A I H W'); -- 8
	INSERT INTO t2 VALUES('O Y G I S J U U W O D Z F J K N R P R L'); -- 9
	INSERT INTO t2 VALUES('B G L K U R U P V X Z I H V R W C Q A S'); -- 10
	INSERT INTO t2 VALUES('T F T J F F Y V F W N X K Q A Y L X W G'); -- 11
	INSERT INTO t2 VALUES('C J U H B Q X L C M M Y E G V F W V Z C'); -- 12
	INSERT INTO t2 VALUES('B W L T F S G X D P H N G M R I O A X I'); -- 13
	INSERT INTO t2 VALUES('N G Y O K Q K Z N M H U J E D H U W R K'); -- 14
	INSERT INTO t2 VALUES('U D T R U Y F J D S J X E H Q G V A S Z'); -- 15
	INSERT INTO t2 VALUES('M I W P J S H R J D Q I C G P C T P H R'); -- 16
	INSERT INTO t2 VALUES('J M N I S L X Q C A B F C B Y D H V R J'); -- 17
	INSERT INTO t2 VALUES('F V Z W J Q L P X Y E W B U Q N H X K T'); -- 18
	INSERT INTO t2 VALUES('R F S R Y O F Q E I E G H C B H R X Y N'); -- 19
	INSERT INTO t2 VALUES('U Q Q Q T E P D M F X P J G H X C Q D L'); -- 20
	}

	do_execsql_test 2.1 {
	CREATE VIRTUAL TABLE ft2 USING fts4(content=t2);
	INSERT INTO ft2(ft2) VALUES('rebuild');

	-- Modify the backing table a bit: Row 17 is missing and the contents
	-- of row 20 do not match the FTS index contents.
	DELETE FROM t2 WHERE rowid = 17;
	UPDATE t2 SET x = 'a b c d e f g h i j' WHERE rowid = 20;
	}

	foreach {tn match rowidlist} {
	1 {S} {1 3 6 7 9 10 13 15 16 17 19}
	2 {"S R"} {7 19}
	3 {"N K N"} {6}
	4 {"Q Q"} {20}
	5 {"B Y D"} {17}
	} {
	do_execsql_test 2.2.1.$tn {
	SELECT rowid FROM ft2 WHERE ft2 MATCH $match
	} $rowidlist

	do_execsql_test 2.2.2.$tn {
	SELECT docid FROM ft2 WHERE ft2 MATCH $match
	} $rowidlist
	}

	foreach {tn match result} {
	1 {"N K N"} {{J O B N K N E C H Z R K J O U G M K L S}}
	2 {"Q Q"} {{a b c d e f g h i j}}
	3 {"B Y D"} {{}}
	} {
	do_execsql_test 2.3.$tn {
	SELECT * FROM ft2 WHERE ft2 MATCH $match
	} $result
	}

	foreach {tn match result} {
	1 {"N K N"} {{..O B [N] [K] [N] E..}}
	2 {"B Y D"} {{}}
	3 {"Q Q"} {{a [b] [c] [d] e f..}}
	} {
	do_execsql_test 2.4.$tn {
	SELECT snippet(ft2, '[', ']', '..', -1, 6) FROM ft2 WHERE ft2 MATCH $match
	} $result
	}

	foreach {tn match result} {
	1 {"N K N"} {{0 0 6 1 0 1 8 1 0 2 10 1}}
	2 {"B Y D"} {{}}
	3 {"Q Q"} {{0 0 2 1 0 0 4 1 0 1 4 1 0 1 6 1}}
	4 {"Q D L"} {{}}
	} {
	do_execsql_test 2.5.$tn {
	SELECT offsets(ft2) FROM ft2 WHERE ft2 MATCH $match
	} $result
	}

	#-------------------------------------------------------------------------
	# The following block of tests - 3.* - test that the FTS index can be
	# modified by writing to the table. But that this has no effect on the
	# content table.
	#

	do_execsql_test 3.1 {
	CREATE TABLE t3(x, y);
	CREATE VIRTUAL TABLE ft3 USING fts4(content=t3);
	}

	do_catchsql_test 3.1.1 {
	INSERT INTO ft3 VALUES('a b c', 'd e f');
	} {1 {constraint failed}}
	do_execsql_test 3.1.2 {
	INSERT INTO ft3(docid, x, y) VALUES(21, 'a b c', 'd e f');
	SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
	} {21}
	do_execsql_test 3.1.3 { SELECT * FROM t3 } {}

	# This DELETE does not work, since there is no row in [t3] to base the
	# DELETE on. So the SELECT on [ft3] still returns rowid 21.
	do_execsql_test 3.1.4 {
	DELETE FROM ft3;
	SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
	} {21}

	# If the row is added to [t3] before the DELETE on [ft3], it works.
	do_execsql_test 3.1.5 {
	INSERT INTO t3(rowid, x, y) VALUES(21, 'a b c', 'd e f');
	DELETE FROM ft3;
	SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
	} {}
	do_execsql_test 3.1.6 { SELECT rowid FROM t3 } {21}

	do_execsql_test 3.2.1 {
	INSERT INTO ft3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
	INSERT INTO ft3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
	INSERT INTO ft3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
	INSERT INTO ft3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
	INSERT INTO ft3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
	INSERT INTO ft3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
	INSERT INTO ft3(rowid, x, y) VALUES(6, 'I Q I S P', 'D R O Q B');
	INSERT INTO ft3(rowid, x, y) VALUES(7, 'T K T Z J', 'B W D G O');
	INSERT INTO ft3(rowid, x, y) VALUES(8, 'Y K F X T', 'D F G V G');
	INSERT INTO ft3(rowid, x, y) VALUES(9, 'E L E T L', 'P W N F Z');
	INSERT INTO ft3(rowid, x, y) VALUES(10, 'O G J G X', 'G J F E P');
	INSERT INTO ft3(rowid, x, y) VALUES(11, 'O L N N Z', 'K E Z F D');
	INSERT INTO ft3(rowid, x, y) VALUES(12, 'R Z M R J', 'X G I M Z');
	INSERT INTO ft3(rowid, x, y) VALUES(13, 'L X N N X', 'R R N S T');
	INSERT INTO ft3(rowid, x, y) VALUES(14, 'F L B J H', 'K W F L C');
	INSERT INTO ft3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
	INSERT INTO ft3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
	INSERT INTO ft3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
	INSERT INTO ft3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
	INSERT INTO ft3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
	}

	foreach {tn match rowidlist} {
	1 "N A" {5 19}
	2 "x:O" {1 2 10 11 17}
	3 "y:O" {0 2 6 7 18 19}
	} {
	set res [list]
	foreach rowid $rowidlist { lappend res $rowid {} {} }

	do_execsql_test 3.2.2.$tn {
	SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
	} $res
	do_execsql_test 3.2.3.$tn {
	SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
	} $res
	}

	do_execsql_test 3.3.1 {
	INSERT INTO t3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
	INSERT INTO t3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
	INSERT INTO t3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
	INSERT INTO t3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
	INSERT INTO t3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
	INSERT INTO t3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
	UPDATE ft3 SET x = y, y = x;
	DELETE FROM t3;
	}

	foreach {tn match rowidlist} {
	1 "N A" {5 19}
	2 "x:O" {0 2 10 11 17}
	3 "y:O" {1 2 6 7 18 19}
	} {
	set res [list]
	foreach rowid $rowidlist { lappend res $rowid {} {} }

	do_execsql_test 3.3.2.$tn {
	SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
	} $res
	do_execsql_test 3.3.3.$tn {
	SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
	} $res
	}

	do_execsql_test 3.3.1 {
	INSERT INTO t3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
	INSERT INTO t3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
	INSERT INTO t3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
	INSERT INTO t3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
	INSERT INTO t3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
	DELETE FROM ft3;
	}

	foreach {tn match rowidlist} {
	1 "N A" {5}
	2 "x:O" {0 2 10 11}
	3 "y:O" {1 2 6 7}
	} {
	set res [list]
	foreach rowid $rowidlist { lappend res $rowid {} {} }

	do_execsql_test 3.3.2.$tn {
	SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
	} $res
	do_execsql_test 3.3.3.$tn {
	SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
	} $res
	}


	#-------------------------------------------------------------------------
	# Test cases 4.* test the 'rebuild' command. On content=xxx and regular
	# FTS tables.
	#
	do_execsql_test 4.0 {
	CREATE TABLE t4(x);
	CREATE VIRTUAL TABLE ft4 USING fts4(content=t4);
	CREATE VIRTUAL TABLE ft4x USING fts4(x);
	}

	do_execsql_test 4.1.1 {
	INSERT INTO ft4x(ft4x) VALUES('rebuild');
	INSERT INTO ft4(ft4) VALUES('rebuild');
	} {}
	do_execsql_test 4.1.2 {
	SELECT id, quote(value) FROM ft4_stat
	} {0 X'000000'}
	do_execsql_test 4.1.3 {
	SELECT id, quote(value) FROM ft4x_stat
	} {0 X'000000'}

	do_execsql_test 4.2.1 {
	INSERT INTO ft4x VALUES('M G M F T');
	INSERT INTO ft4x VALUES('Z Q C A U');
	INSERT INTO ft4x VALUES('N L L V');
	INSERT INTO ft4x VALUES('T F D X D');
	INSERT INTO ft4x VALUES('Z H I S D');

	SELECT id, quote(value) FROM ft4x_stat
	} {0 X'05182B'}

	do_execsql_test 4.2.2 {
	INSERT INTO ft4(rowid, x) SELECT rowid, * FROM ft4x;
	SELECT id, quote(value) FROM ft4_stat
	} {0 X'05182B'}

	do_execsql_test 4.2.3 {
	SELECT docid, quote(size) FROM ft4_docsize
	} {1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}

	do_execsql_test 4.2.4 {
	INSERT INTO ft4x(ft4x) VALUES('rebuild');
	SELECT id, quote(value) FROM ft4x_stat;
	SELECT docid, quote(size) FROM ft4x_docsize
	} {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}

	do_execsql_test 4.2.5 {
	INSERT INTO ft4(ft4) VALUES('rebuild');
	SELECT id, quote(value) FROM ft4_stat;
	SELECT docid, quote(size) FROM ft4_docsize
	} {0 X'000000'}

	do_execsql_test 4.2.6 {
	INSERT INTO t4(rowid, x) SELECT rowid, x FROM ft4x;
	INSERT INTO ft4(ft4) VALUES('rebuild');
	SELECT id, quote(value) FROM ft4_stat;
	SELECT docid, quote(size) FROM ft4_docsize
	} {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}


	#-------------------------------------------------------------------------
	# Test cases 5.* test that the following commands do not create/move or
	# delete a %_content table when used with a content=xxx FTS table.
	#
	do_execsql_test 5.1.1 {
	CREATE TABLE t5(a, b, c, d);
	CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
	SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
	} {
	t5 ft5 ft5_segments ft5_segdir
	sqlite_autoindex_ft5_segdir_1 ft5_docsize ft5_stat
	}
	do_execsql_test 5.1.2 {
	ALTER TABLE ft5 RENAME TO ft6;
	SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
	} {
	t5
	}
	do_execsql_test 5.1.3 {
	SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
	} {
	ft6 ft6_segments ft6_segdir
	sqlite_autoindex_ft6_segdir_1 ft6_docsize ft6_stat
	}
	do_execsql_test 5.1.4 {
	INSERT INTO t5 VALUES('a', 'b', 'c', 'd');
	INSERT INTO ft6(ft6) VALUES('rebuild');
	SELECT rowid FROM ft6 WHERE ft6 MATCH 'b';
	} {1}
	do_execsql_test 5.1.5 {
	DROP TABLE ft6;
	SELECT * FROM t5;
	} {a b c d}
	do_execsql_test 5.1.6 {
	SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
	} {
	}
	do_execsql_test 5.1.7 {
	CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
	CREATE TABLE t5_content(a, b);
	DROP TABLE ft5;
	SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
	} {
	t5 t5_content
	}

	#-------------------------------------------------------------------------
	# Test cases 6.* test
	#
	do_catchsql_test 6.1.1 {
	CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);
	} {1 {no such table: main.t7}}

	do_execsql_test 6.2.1 {
	CREATE TABLE t7(one, two);
	CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);
	INSERT INTO t7 VALUES('A B', 'B A');
	INSERT INTO t7 VALUES('C D', 'A A');
	SELECT * FROM ft7;
	} {
	{A B} {B A} {C D} {A A}
	}

	do_catchsql_test 6.2.2 {
	DROP TABLE t7;
	SELECT * FROM ft7;
	} {1 {SQL logic error}}

	db close
	sqlite3 db test.db
	do_execsql_test 6.2.3 {
	SELECT name FROM sqlite_master WHERE name LIKE '%t7%'
	} {
	ft7 ft7_segments ft7_segdir sqlite_autoindex_ft7_segdir_1
	ft7_docsize ft7_stat
	}
	do_catchsql_test 6.2.4 {
	SELECT * FROM ft7;
	} {1 {no such table: main.t7}}
	do_execsql_test 6.2.5 {
	CREATE TABLE t7(x, y);
	INSERT INTO t7 VALUES('A B', 'B A');
	INSERT INTO t7 VALUES('C D', 'A A');
	SELECT * FROM ft7;
	} {
	{A B} {B A} {C D} {A A}
	}

	do_execsql_test 6.2.6 {
	INSERT INTO ft7(ft7) VALUES('rebuild');
	SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
	} {2}

	do_execsql_test 6.2.7 {
	DROP TABLE t7;
	CREATE TABLE t7(x);
	}
	do_catchsql_test 6.2.8 {
	SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
	} {1 {SQL logic error}}
	do_catchsql_test 6.2.9 {
	SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
	} {1 {SQL logic error}}

	db close
	sqlite3 db test.db
	do_catchsql_test 6.2.10 {
	SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
	} {0 2}
	do_catchsql_test 6.2.11 {
	SELECT rowid, * FROM ft7 WHERE ft7 MATCH '"A A"';
	} {0 {2 {}}}

	#-------------------------------------------------------------------------
	# Test cases 7.*
	#
	do_execsql_test 7.1.1 {
	CREATE VIRTUAL TABLE ft8 USING fts4(content=nosuchtable, x);
	INSERT INTO ft8(docid, x) VALUES(13, 'U O N X G');
	INSERT INTO ft8(docid, x) VALUES(14, 'C J J U B');
	INSERT INTO ft8(docid, x) VALUES(15, 'N J Y G X');
	INSERT INTO ft8(docid, x) VALUES(16, 'R Y D O R');
	INSERT INTO ft8(docid, x) VALUES(17, 'I Y T Q O');
	}

	do_execsql_test 7.1.2 {
	SELECT docid FROM ft8 WHERE ft8 MATCH 'N';
	} {13 15}

	do_execsql_test 7.2.1 {
	CREATE VIRTUAL TABLE ft9 USING fts4(content=, x);
	INSERT INTO ft9(docid, x) VALUES(13, 'U O N X G');
	INSERT INTO ft9(docid, x) VALUES(14, 'C J J U B');
	INSERT INTO ft9(docid, x) VALUES(15, 'N J Y G X');
	INSERT INTO ft9(docid, x) VALUES(16, 'R Y D O R');
	INSERT INTO ft9(docid, x) VALUES(17, 'I Y T Q O');
	}
	do_execsql_test 7.2.2 {
	SELECT docid FROM ft9 WHERE ft9 MATCH 'N';
	} {13 15}
	do_execsql_test 7.2.3 {
	SELECT name FROM sqlite_master WHERE name LIKE 'ft9_%';
	} {ft9_segments ft9_segdir ft9_docsize ft9_stat}

	do_catchsql_test 7.2.4 {
	SELECT * FROM ft9 WHERE ft9 MATCH 'N';
	} {1 {SQL logic error}}

	#-------------------------------------------------------------------------
	# Test cases 8.*
	#
	do_execsql_test 8.1 {
	CREATE TABLE t10(a, b);
	INSERT INTO t10 VALUES(
	'abasia abasic abask', 'Abassin abastardize abatable');
	INSERT INTO t10 VALUES(
	'abate abatement abater', 'abatis abatised abaton');
	INSERT INTO t10 VALUES(
	'abator abattoir Abatua', 'abature abave abaxial');

	CREATE VIRTUAL TABLE ft10 USING fts4(content=t10, prefix="2,4", a, b);
	}

	do_execsql_test 8.2 { SELECT * FROM ft10 WHERE a MATCH 'ab*'; }
	do_execsql_test 8.3 { INSERT INTO ft10(ft10) VALUES('rebuild'); }
	do_execsql_test 8.4 { SELECT rowid FROM ft10 WHERE a MATCH 'ab*'; } {1 2 3}
	do_execsql_test 8.5 { SELECT rowid FROM ft10 WHERE b MATCH 'abav*'; } {3}
	do_execsql_test 8.6 { SELECT rowid FROM ft10 WHERE ft10 MATCH 'abas*'; } {1}

	#-------------------------------------------------------------------------
	# Test cases 9.*
	#
	reset_db
	register_echo_module [sqlite3_connection_pointer db]

	do_execsql_test 9.1 {
	CREATE TABLE tbl1(a, b);
	INSERT INTO tbl1 VALUES('a b', 'c d');
	INSERT INTO tbl1 VALUES('e f', 'a b');
	CREATE VIRTUAL TABLE e1 USING echo(tbl1);
	CREATE VIRTUAL TABLE ft1 USING fts4(content=e1);
	INSERT INTO ft1(ft1) VALUES('rebuild');
	}

	do_execsql_test 9.2 {
	SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'e'
	} {2 {e f} {a b}}

	do_execsql_test 9.3 {
	SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
	} {1 {a b} {c d} 2 {e f} {a b}}

	do_execsql_test 9.4 {
	DELETE FROM ft1 WHERE docid=1;
	}

	do_execsql_test 9.5 {
	SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
	} {2 {e f} {a b}}

	do_execsql_test 9.6 {
	INSERT INTO ft1(ft1) VALUES('rebuild');
	SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
	} {1 {a b} {c d} 2 {e f} {a b}}


	#-------------------------------------------------------------------------
	# Test cases 10.*
	#
	reset_db
	register_fs_module [sqlite3_connection_pointer db]

	proc write_file {path text} {
	set fd [open $path w]
	puts -nonewline $fd $text
	close $fd
	}

	write_file t1.txt {a b c d e f g h i j k l m n o p q r s t u v w x y z}
	write_file t2.txt {a b c d e f g h i j k l m a b c d e f g h i j k l m}
	write_file t3.txt {n o p q r s t u v w x y z n o p q r s t u v w x y z}

	do_execsql_test 10.1 {
	CREATE TABLE idx(id INTEGER PRIMARY KEY, path TEXT);
	INSERT INTO idx VALUES (1, 't1.txt');
	INSERT INTO idx VALUES (2, 't2.txt');
	INSERT INTO idx VALUES (3, 't3.txt');

	CREATE VIRTUAL TABLE vt USING fs(idx);
	SELECT path, data FROM vt;
	} {
	1 {a b c d e f g h i j k l m n o p q r s t u v w x y z}
	2 {a b c d e f g h i j k l m a b c d e f g h i j k l m}
	3 {n o p q r s t u v w x y z n o p q r s t u v w x y z}
	}

	do_execsql_test 10.2 {
	SELECT path, data FROM vt WHERE rowid = 2;
	} {
	2 {a b c d e f g h i j k l m a b c d e f g h i j k l m}
	}

	do_execsql_test 10.3 {
	CREATE VIRTUAL TABLE ft USING fts4(content=vt);
	INSERT INTO ft(ft) VALUES('rebuild');
	}

	do_execsql_test 10.4 {
	SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
	} {
	{...c d [e] f g...} {...c d [e] f g...}
	}

	do_execsql_test 10.5 {
	SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 't'
	} {
	{...r s [t] u v...} {...r s [t] u v...}
	}

	do_execsql_test 10.6 { DELETE FROM ft WHERE docid=2 }

	do_execsql_test 10.7 {
	SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
	} {
	{...c d [e] f g...}
	}

	#-------------------------------------------------------------------------
	# Test cases 11.*
	#
	reset_db

	do_catchsql_test 11.1 {
	CREATE VIRTUAL TABLE x1 USING fts4(content=x1);
	} {1 {vtable constructor called recursively: x1}}


	finish_test