AryaWu/sqlite

1# 2011 October 032#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#*************************************************************************11# This file implements regression tests for SQLite library.  The12# focus of this script is testing the content=xxx FTS4 option.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set ::testprefix fts4content18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21  finish_test22  return23}24 25#-------------------------------------------------------------------------26# Test organization:27#   28#   1.* - Warm-body tests.29#30#   2.* - Querying a content=xxx FTS table.31#32#   3.* - Writing to a content=xxx FTS table.33#34#   4.* - The "INSERT INTO fts(fts) VALUES('rebuild')" command.35#36#   5.* - Check that CREATE TABLE, DROP TABLE and ALTER TABLE correctly37#         ignore any %_content table when used with the content=xxx option.38#39#   6.* - Test the effects of messing with the schema of table xxx after40#         creating a content=xxx FTS index.41#   42#   7.* - Test that if content=xxx is specified and table xxx does not43#         exist, the FTS table can still be used for INSERT and some44#         SELECT statements.45#46#   8.* - Test that if the content=xxx and prefix options are used together,47#         the 'rebuild' command still works.48#49#   9.* - Test using content=xxx where xxx is a virtual table.50#51#   11.* - Test that circular references (e.g. "t1(content=t1)") are52#          detected.53#54 55do_execsql_test 1.1.1 {56  CREATE TABLE t1(a, b, c);57  INSERT INTO t1 VALUES('w x', 'x y', 'y z');58  CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);59}60 61do_execsql_test 1.1.2 {62  PRAGMA table_info(ft1);63} {64  0 a {} 0 {} 0 65  1 b {} 0 {} 0 66  2 c {} 0 {} 067}68 69do_execsql_test 1.1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}70do_execsql_test 1.1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}71 72do_execsql_test 1.1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}73do_execsql_test 1.1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}74do_execsql_test 1.1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}75 76do_execsql_test 1.2.1 {77  DROP TABLE ft1;78  CREATE VIRTUAL TABLE ft1 USING fts4(content=t1, b);79  PRAGMA table_info(ft1);80} {81  0 b {} 0 {} 0 82}83do_execsql_test 1.2.2 { 84  SELECT *, rowid FROM ft1 85} {{x y} 1}86 87#-------------------------------------------------------------------------88# The following block of tests - 2.* - test that a content=xxx FTS table89# can be queried. Also tested are cases where rows identified in the FTS90# are missing from the content table, and cases where the index is 91# inconsistent with the content table.92# 93do_execsql_test 2.0 {94  CREATE TABLE t2(x);95  INSERT INTO t2 VALUES('O S W W F U C R Q I C N P Z Y Y E Y Y E');  -- 196  INSERT INTO t2 VALUES('Y X U V L B E H Y J C Y A I A P V F V K');  -- 297  INSERT INTO t2 VALUES('P W I N J H I I N I F B K D U Q B Z S F');  -- 398  INSERT INTO t2 VALUES('N R O R H J R H G M D I U U B O M P A U');  -- 499  INSERT INTO t2 VALUES('Y O V O G T P N G T N F I V B U M J M G');  -- 5100  INSERT INTO t2 VALUES('J O B N K N E C H Z R K J O U G M K L S');  -- 6101  INSERT INTO t2 VALUES('S Z S R I Q U A P W R X H K C Z U L S P');  -- 7102  INSERT INTO t2 VALUES('J C H N R C K R V N M O F Z M Z A I H W');  -- 8103  INSERT INTO t2 VALUES('O Y G I S J U U W O D Z F J K N R P R L');  -- 9104  INSERT INTO t2 VALUES('B G L K U R U P V X Z I H V R W C Q A S');  -- 10105  INSERT INTO t2 VALUES('T F T J F F Y V F W N X K Q A Y L X W G');  -- 11106  INSERT INTO t2 VALUES('C J U H B Q X L C M M Y E G V F W V Z C');  -- 12107  INSERT INTO t2 VALUES('B W L T F S G X D P H N G M R I O A X I');  -- 13108  INSERT INTO t2 VALUES('N G Y O K Q K Z N M H U J E D H U W R K');  -- 14109  INSERT INTO t2 VALUES('U D T R U Y F J D S J X E H Q G V A S Z');  -- 15110  INSERT INTO t2 VALUES('M I W P J S H R J D Q I C G P C T P H R');  -- 16111  INSERT INTO t2 VALUES('J M N I S L X Q C A B F C B Y D H V R J');  -- 17112  INSERT INTO t2 VALUES('F V Z W J Q L P X Y E W B U Q N H X K T');  -- 18113  INSERT INTO t2 VALUES('R F S R Y O F Q E I E G H C B H R X Y N');  -- 19114  INSERT INTO t2 VALUES('U Q Q Q T E P D M F X P J G H X C Q D L');  -- 20115}116 117do_execsql_test 2.1 {118  CREATE VIRTUAL TABLE ft2 USING fts4(content=t2);119  INSERT INTO ft2(ft2) VALUES('rebuild');120 121  -- Modify the backing table a bit: Row 17 is missing and the contents 122  -- of row 20 do not match the FTS index contents. 123  DELETE FROM t2 WHERE rowid = 17;124  UPDATE t2 SET x = 'a b c d e f g h i j' WHERE rowid = 20;125}126 127foreach {tn match rowidlist} {128  1   {S}        {1 3 6 7 9 10 13 15 16 17 19}129  2   {"S R"}    {7 19}130  3   {"N K N"}  {6}131  4   {"Q Q"}    {20}132  5   {"B Y D"}  {17}133} {134  do_execsql_test 2.2.1.$tn {135    SELECT rowid FROM ft2 WHERE ft2 MATCH $match136  } $rowidlist137 138  do_execsql_test 2.2.2.$tn {139    SELECT docid FROM ft2 WHERE ft2 MATCH $match140  } $rowidlist141}142 143foreach {tn match result} {144  1   {"N K N"}  {{J O B N K N E C H Z R K J O U G M K L S}}145  2   {"Q Q"}    {{a b c d e f g h i j}}146  3   {"B Y D"}  {{}}147} {148  do_execsql_test 2.3.$tn {149    SELECT * FROM ft2 WHERE ft2 MATCH $match150  } $result151}152 153foreach {tn match result} {154  1   {"N K N"}  {{..O B [N] [K] [N] E..}}155  2   {"B Y D"}  {{}}156  3   {"Q Q"}    {{a [b] [c] [d] e f..}}157} {158  do_execsql_test 2.4.$tn {159    SELECT snippet(ft2, '[', ']', '..', -1, 6) FROM ft2 WHERE ft2 MATCH $match160  } $result161}162 163foreach {tn match result} {164  1   {"N K N"}  {{0 0 6 1 0 1 8 1 0 2 10 1}}165  2   {"B Y D"}  {{}}166  3   {"Q Q"}    {{0 0 2 1 0 0 4 1 0 1 4 1 0 1 6 1}}167  4   {"Q D L"}  {{}}168} {169  do_execsql_test 2.5.$tn {170    SELECT offsets(ft2) FROM ft2 WHERE ft2 MATCH $match171  } $result172}173 174#-------------------------------------------------------------------------175# The following block of tests - 3.* - test that the FTS index can be176# modified by writing to the table. But that this has no effect on the 177# content table.178# 179 180do_execsql_test 3.1 {181  CREATE TABLE t3(x, y);182  CREATE VIRTUAL TABLE ft3 USING fts4(content=t3);183}184 185do_catchsql_test 3.1.1 {186  INSERT INTO ft3 VALUES('a b c', 'd e f');187} {1 {constraint failed}}188do_execsql_test 3.1.2 {189  INSERT INTO ft3(docid, x, y) VALUES(21, 'a b c', 'd e f');190  SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';191} {21}192do_execsql_test 3.1.3 { SELECT * FROM t3 } {}193 194# This DELETE does not work, since there is no row in [t3] to base the195# DELETE on. So the SELECT on [ft3] still returns rowid 21.196do_execsql_test 3.1.4 { 197  DELETE FROM ft3;198  SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';199} {21}200 201# If the row is added to [t3] before the DELETE on [ft3], it works.202do_execsql_test 3.1.5 {203  INSERT INTO t3(rowid, x, y) VALUES(21, 'a b c', 'd e f');204  DELETE FROM ft3;205  SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';206} {}207do_execsql_test 3.1.6 { SELECT rowid FROM t3 } {21}208 209do_execsql_test 3.2.1 {210  INSERT INTO ft3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');211  INSERT INTO ft3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');212  INSERT INTO ft3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');213  INSERT INTO ft3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');214  INSERT INTO ft3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');215  INSERT INTO ft3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');216  INSERT INTO ft3(rowid, x, y) VALUES(6, 'I Q I S P', 'D R O Q B');217  INSERT INTO ft3(rowid, x, y) VALUES(7, 'T K T Z J', 'B W D G O');218  INSERT INTO ft3(rowid, x, y) VALUES(8, 'Y K F X T', 'D F G V G');219  INSERT INTO ft3(rowid, x, y) VALUES(9, 'E L E T L', 'P W N F Z');220  INSERT INTO ft3(rowid, x, y) VALUES(10, 'O G J G X', 'G J F E P');221  INSERT INTO ft3(rowid, x, y) VALUES(11, 'O L N N Z', 'K E Z F D');222  INSERT INTO ft3(rowid, x, y) VALUES(12, 'R Z M R J', 'X G I M Z');223  INSERT INTO ft3(rowid, x, y) VALUES(13, 'L X N N X', 'R R N S T');224  INSERT INTO ft3(rowid, x, y) VALUES(14, 'F L B J H', 'K W F L C');225  INSERT INTO ft3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');226  INSERT INTO ft3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');227  INSERT INTO ft3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');228  INSERT INTO ft3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');229  INSERT INTO ft3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');230}231 232foreach {tn match rowidlist} {233  1   "N A"    {5 19}234  2   "x:O"    {1 2 10 11 17}235  3   "y:O"    {0 2 6 7 18 19}236} {237  set res [list]238  foreach rowid $rowidlist { lappend res $rowid {} {} }239 240  do_execsql_test 3.2.2.$tn {241    SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match242  } $res243  do_execsql_test 3.2.3.$tn {244    SELECT docid, * FROM ft3 WHERE ft3 MATCH $match245  } $res246}247 248do_execsql_test 3.3.1 {249  INSERT INTO t3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');250  INSERT INTO t3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');251  INSERT INTO t3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');252  INSERT INTO t3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');253  INSERT INTO t3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');254  INSERT INTO t3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');255  UPDATE ft3 SET x = y, y = x;256  DELETE FROM t3;257}258 259foreach {tn match rowidlist} {260  1   "N A"    {5 19}261  2   "x:O"    {0 2 10 11 17}262  3   "y:O"    {1 2 6 7 18 19}263} {264  set res [list]265  foreach rowid $rowidlist { lappend res $rowid {} {} }266 267  do_execsql_test 3.3.2.$tn {268    SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match269  } $res270  do_execsql_test 3.3.3.$tn {271    SELECT docid, * FROM ft3 WHERE ft3 MATCH $match272  } $res273}274 275do_execsql_test 3.3.1 {276  INSERT INTO t3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');277  INSERT INTO t3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');278  INSERT INTO t3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');279  INSERT INTO t3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');280  INSERT INTO t3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');281  DELETE FROM ft3;282}283 284foreach {tn match rowidlist} {285  1   "N A"    {5}286  2   "x:O"    {0 2 10 11}287  3   "y:O"    {1 2 6 7}288} {289  set res [list]290  foreach rowid $rowidlist { lappend res $rowid {} {} }291 292  do_execsql_test 3.3.2.$tn {293    SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match294  } $res295  do_execsql_test 3.3.3.$tn {296    SELECT docid, * FROM ft3 WHERE ft3 MATCH $match297  } $res298}299 300 301#-------------------------------------------------------------------------302# Test cases 4.* test the 'rebuild' command. On content=xxx and regular303# FTS tables.304# 305do_execsql_test 4.0 {306  CREATE TABLE t4(x);307  CREATE VIRTUAL TABLE ft4 USING fts4(content=t4);308  CREATE VIRTUAL TABLE ft4x USING fts4(x);309}310 311do_execsql_test 4.1.1 {312  INSERT INTO ft4x(ft4x) VALUES('rebuild');313  INSERT INTO ft4(ft4) VALUES('rebuild');314} {}315do_execsql_test 4.1.2 {316  SELECT id, quote(value) FROM ft4_stat317} {0 X'000000'}318do_execsql_test 4.1.3 {319  SELECT id, quote(value) FROM ft4x_stat320} {0 X'000000'}321 322do_execsql_test 4.2.1 {323  INSERT INTO ft4x VALUES('M G M F T');324  INSERT INTO ft4x VALUES('Z Q C A U');325  INSERT INTO ft4x VALUES('N L L V');326  INSERT INTO ft4x VALUES('T F D X D');327  INSERT INTO ft4x VALUES('Z H I S D');328 329  SELECT id, quote(value) FROM ft4x_stat330} {0 X'05182B'}331 332do_execsql_test 4.2.2 {333  INSERT INTO ft4(rowid, x) SELECT rowid, * FROM ft4x;334  SELECT id, quote(value) FROM ft4_stat335} {0 X'05182B'}336 337do_execsql_test 4.2.3 {338  SELECT docid, quote(size) FROM ft4_docsize339} {1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}340 341do_execsql_test 4.2.4 {342  INSERT INTO ft4x(ft4x) VALUES('rebuild');343  SELECT id, quote(value) FROM ft4x_stat;344  SELECT docid, quote(size) FROM ft4x_docsize345} {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}346 347do_execsql_test 4.2.5 {348  INSERT INTO ft4(ft4) VALUES('rebuild');349  SELECT id, quote(value) FROM ft4_stat;350  SELECT docid, quote(size) FROM ft4_docsize351} {0 X'000000'}352 353do_execsql_test 4.2.6 {354  INSERT INTO t4(rowid, x) SELECT rowid, x FROM ft4x;355  INSERT INTO ft4(ft4) VALUES('rebuild');356  SELECT id, quote(value) FROM ft4_stat;357  SELECT docid, quote(size) FROM ft4_docsize358} {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}359 360 361#-------------------------------------------------------------------------362# Test cases 5.* test that the following commands do not create/move or363# delete a %_content table when used with a content=xxx FTS table.364# 365do_execsql_test 5.1.1 {366  CREATE TABLE t5(a, b, c, d);367  CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);368  SELECT name FROM sqlite_master WHERE name LIKE '%t5%';369} {370  t5 ft5 ft5_segments ft5_segdir 371  sqlite_autoindex_ft5_segdir_1 ft5_docsize ft5_stat372}373do_execsql_test 5.1.2 {374  ALTER TABLE ft5 RENAME TO ft6;375  SELECT name FROM sqlite_master WHERE name LIKE '%t5%';376} {377  t5378}379do_execsql_test 5.1.3 {380  SELECT name FROM sqlite_master WHERE name LIKE '%t6%';381} {382  ft6 ft6_segments ft6_segdir 383  sqlite_autoindex_ft6_segdir_1 ft6_docsize ft6_stat384}385do_execsql_test 5.1.4 {386  INSERT INTO t5 VALUES('a', 'b', 'c', 'd');387  INSERT INTO ft6(ft6) VALUES('rebuild');388  SELECT rowid FROM ft6 WHERE ft6 MATCH 'b';389} {1}390do_execsql_test 5.1.5 {391  DROP TABLE ft6;392  SELECT * FROM t5;393} {a b c d}394do_execsql_test 5.1.6 {395  SELECT name FROM sqlite_master WHERE name LIKE '%t6%';396} {397}398do_execsql_test 5.1.7 {399  CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);400  CREATE TABLE t5_content(a, b);401  DROP TABLE ft5;402  SELECT name FROM sqlite_master WHERE name LIKE '%t5%';403} {404  t5 t5_content405}406 407#-------------------------------------------------------------------------408# Test cases 6.* test 409# 410do_catchsql_test 6.1.1 {411  CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);412} {1 {no such table: main.t7}}413 414do_execsql_test 6.2.1 {415  CREATE TABLE t7(one, two);416  CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);417  INSERT INTO t7 VALUES('A B', 'B A');418  INSERT INTO t7 VALUES('C D', 'A A');419  SELECT * FROM ft7;420} {421  {A B} {B A} {C D} {A A}422}423 424do_catchsql_test 6.2.2 {425  DROP TABLE t7;426  SELECT * FROM ft7;427} {1 {SQL logic error}}428 429db close430sqlite3 db test.db431do_execsql_test 6.2.3 {432  SELECT name FROM sqlite_master WHERE name LIKE '%t7%'433} {434  ft7 ft7_segments ft7_segdir sqlite_autoindex_ft7_segdir_1 435  ft7_docsize ft7_stat436}437do_catchsql_test 6.2.4 {438  SELECT * FROM ft7;439} {1 {no such table: main.t7}}440do_execsql_test 6.2.5 {441  CREATE TABLE t7(x, y);442  INSERT INTO t7 VALUES('A B', 'B A');443  INSERT INTO t7 VALUES('C D', 'A A');444  SELECT * FROM ft7;445} {446  {A B} {B A} {C D} {A A}447}448 449do_execsql_test 6.2.6 {450  INSERT INTO ft7(ft7) VALUES('rebuild');451  SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';452} {2}453 454do_execsql_test 6.2.7 {455  DROP TABLE t7;456  CREATE TABLE t7(x);457}458do_catchsql_test 6.2.8 {459  SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';460} {1 {SQL logic error}}461do_catchsql_test 6.2.9 {462  SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';463} {1 {SQL logic error}}464 465db close466sqlite3 db test.db467do_catchsql_test 6.2.10 {468  SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';469} {0 2}470do_catchsql_test 6.2.11 {471  SELECT rowid, * FROM ft7 WHERE ft7 MATCH '"A A"';472} {0 {2 {}}}473 474#-------------------------------------------------------------------------475# Test cases 7.*476# 477do_execsql_test 7.1.1 {478  CREATE VIRTUAL TABLE ft8 USING fts4(content=nosuchtable, x);479  INSERT INTO ft8(docid, x) VALUES(13, 'U O N X G');480  INSERT INTO ft8(docid, x) VALUES(14, 'C J J U B');481  INSERT INTO ft8(docid, x) VALUES(15, 'N J Y G X');482  INSERT INTO ft8(docid, x) VALUES(16, 'R Y D O R');483  INSERT INTO ft8(docid, x) VALUES(17, 'I Y T Q O');484}485 486do_execsql_test 7.1.2 {487  SELECT docid FROM ft8 WHERE ft8 MATCH 'N';488} {13 15}489 490do_execsql_test 7.2.1 {491  CREATE VIRTUAL TABLE ft9 USING fts4(content=, x);492  INSERT INTO ft9(docid, x) VALUES(13, 'U O N X G');493  INSERT INTO ft9(docid, x) VALUES(14, 'C J J U B');494  INSERT INTO ft9(docid, x) VALUES(15, 'N J Y G X');495  INSERT INTO ft9(docid, x) VALUES(16, 'R Y D O R');496  INSERT INTO ft9(docid, x) VALUES(17, 'I Y T Q O');497}498do_execsql_test 7.2.2 {499  SELECT docid FROM ft9 WHERE ft9 MATCH 'N';500} {13 15}501do_execsql_test 7.2.3 {502  SELECT name FROM sqlite_master WHERE name LIKE 'ft9_%';503} {ft9_segments ft9_segdir ft9_docsize ft9_stat}504 505do_catchsql_test 7.2.4 {506  SELECT * FROM ft9 WHERE ft9 MATCH 'N';507} {1 {SQL logic error}}508 509#-------------------------------------------------------------------------510# Test cases 8.*511# 512do_execsql_test 8.1 {513  CREATE TABLE t10(a, b);514  INSERT INTO t10 VALUES(515      'abasia abasic abask', 'Abassin abastardize abatable');516  INSERT INTO t10 VALUES(517      'abate abatement abater', 'abatis abatised abaton');518  INSERT INTO t10 VALUES(519      'abator abattoir Abatua', 'abature abave abaxial');520 521  CREATE VIRTUAL TABLE ft10 USING fts4(content=t10, prefix="2,4", a, b);522}523 524do_execsql_test 8.2 { SELECT * FROM ft10 WHERE a MATCH 'ab*';          }525do_execsql_test 8.3 { INSERT INTO ft10(ft10) VALUES('rebuild');        }526do_execsql_test 8.4 { SELECT rowid FROM ft10 WHERE a MATCH 'ab*';      } {1 2 3}527do_execsql_test 8.5 { SELECT rowid FROM ft10 WHERE b MATCH 'abav*';    } {3}528do_execsql_test 8.6 { SELECT rowid FROM ft10 WHERE ft10 MATCH 'abas*'; } {1}529 530#-------------------------------------------------------------------------531# Test cases 9.*532# 533reset_db534register_echo_module [sqlite3_connection_pointer db]535 536do_execsql_test 9.1 {537  CREATE TABLE tbl1(a, b);538  INSERT INTO tbl1 VALUES('a b', 'c d');539  INSERT INTO tbl1 VALUES('e f', 'a b');540  CREATE VIRTUAL TABLE e1 USING echo(tbl1);541  CREATE VIRTUAL TABLE ft1 USING fts4(content=e1);542  INSERT INTO ft1(ft1) VALUES('rebuild');543}544 545do_execsql_test 9.2 {546  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'e'547} {2 {e f} {a b}}548 549do_execsql_test 9.3 {550  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'551} {1 {a b} {c d} 2 {e f} {a b}}552 553do_execsql_test 9.4 { 554  DELETE FROM ft1 WHERE docid=1;555}556 557do_execsql_test 9.5 {558  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'559} {2 {e f} {a b}}560 561do_execsql_test 9.6 {562  INSERT INTO ft1(ft1) VALUES('rebuild');563  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'564} {1 {a b} {c d} 2 {e f} {a b}}565 566 567#-------------------------------------------------------------------------568# Test cases 10.*569# 570reset_db571register_fs_module [sqlite3_connection_pointer db]572 573proc write_file {path text} {574  set fd [open $path w]575  puts -nonewline $fd $text576  close $fd577}578 579write_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}580write_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}581write_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}582 583do_execsql_test 10.1 {584  CREATE TABLE idx(id INTEGER PRIMARY KEY, path TEXT);585  INSERT INTO idx VALUES (1, 't1.txt');586  INSERT INTO idx VALUES (2, 't2.txt');587  INSERT INTO idx VALUES (3, 't3.txt');588 589  CREATE VIRTUAL TABLE vt USING fs(idx);590  SELECT path, data FROM vt;591} {592  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} 593  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}594  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}595}596 597do_execsql_test 10.2 {598  SELECT path, data FROM vt WHERE rowid = 2;599} {600  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}601}602 603do_execsql_test 10.3 {604  CREATE VIRTUAL TABLE ft USING fts4(content=vt);605  INSERT INTO ft(ft) VALUES('rebuild');606}607 608do_execsql_test 10.4 {609  SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'610} {611  {...c d [e] f g...} {...c d [e] f g...}612}613 614do_execsql_test 10.5 {615  SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 't'616} {617  {...r s [t] u v...} {...r s [t] u v...}618}619 620do_execsql_test 10.6 { DELETE FROM ft WHERE docid=2 }621 622do_execsql_test 10.7 {623  SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'624} {625  {...c d [e] f g...}626}627 628#-------------------------------------------------------------------------629# Test cases 11.*630# 631reset_db632 633do_catchsql_test 11.1 {634  CREATE VIRTUAL TABLE x1 USING fts4(content=x1);635} {1 {vtable constructor called recursively: x1}}636 637#---------------------------------------------------------------------------638# Check that an fts4 table cannot be its own content table.639#640reset_db641breakpoint642do_execsql_test 12.1.1 {643  CREATE VIRTUAL TABLE t1 USING fts4(a, content=t1 );644  INSERT INTO t1(rowid, a) VALUES(1, 'abc');645}646do_catchsql_test 12.1.2 { 647  SELECT * FROM t1; 648} {1 {SQL logic error}}649do_catchsql_test 12.1.3 { 650  SELECT * FROM t1('abc'); 651} {1 {SQL logic error}}652do_catchsql_test 12.1.4 { 653  SELECT count(*) FROM t1;654} {1 {SQL logic error}}655 656reset_db657do_execsql_test 12.2.1 {658  CREATE VIRTUAL TABLE t1 USING fts4(a, content=t2 );659  CREATE VIRTUAL TABLE t2 USING fts4(a, content=t1 );660  INSERT INTO t1(rowid, a) VALUES(1, 'abc');661}662do_catchsql_test 12.2.2 { 663  SELECT * FROM t1; 664} {1 {SQL logic error}}665do_catchsql_test 12.2.3 { 666  SELECT * FROM t1('abc'); 667} {1 {SQL logic error}}668do_catchsql_test 12.2.4 { 669  SELECT count(*) FROM t1;670} {1 {SQL logic error}}671 672 673 674finish_test675