1# 2011 October 03 2# 3# The author disclaims copyright to this source code. In place of 4# 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. The 12# focus of this script is testing the content=xxx FTS4 option. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set ::testprefix fts4content 18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file. 20ifcapable !fts3 { 21 finish_test 22 return 23} 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 correctly 37# 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 after 40# creating a content=xxx FTS index. 41# 42# 7.* - Test that if content=xxx is specified and table xxx does not 43# exist, the FTS table can still be used for INSERT and some 44# 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)") are 52# 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 {} 0 67} 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 table 89# can be queried. Also tested are cases where rows identified in the FTS 90# 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'); -- 1 96 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 97 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 98 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 99 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 100 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 101 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 102 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 103 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 104 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 105 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 106 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 107 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 108 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 109 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 110 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 111 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 112 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 113 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 114 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 115} 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 $match 136 } $rowidlist 137 138 do_execsql_test 2.2.2.$tn { 139 SELECT docid FROM ft2 WHERE ft2 MATCH $match 140 } $rowidlist 141} 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 $match 150 } $result 151} 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 $match 160 } $result 161} 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 $match 171 } $result 172} 173 174#------------------------------------------------------------------------- 175# The following block of tests - 3.* - test that the FTS index can be 176# 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 the 195# 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 $match 242 } $res 243 do_execsql_test 3.2.3.$tn { 244 SELECT docid, * FROM ft3 WHERE ft3 MATCH $match 245 } $res 246} 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 $match 269 } $res 270 do_execsql_test 3.3.3.$tn { 271 SELECT docid, * FROM ft3 WHERE ft3 MATCH $match 272 } $res 273} 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 $match 294 } $res 295 do_execsql_test 3.3.3.$tn { 296 SELECT docid, * FROM ft3 WHERE ft3 MATCH $match 297 } $res 298} 299 300 301#------------------------------------------------------------------------- 302# Test cases 4.* test the 'rebuild' command. On content=xxx and regular 303# 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_stat 317} {0 X'000000'} 318do_execsql_test 4.1.3 { 319 SELECT id, quote(value) FROM ft4x_stat 320} {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_stat 330} {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_stat 335} {0 X'05182B'} 336 337do_execsql_test 4.2.3 { 338 SELECT docid, quote(size) FROM ft4_docsize 339} {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_docsize 345} {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_docsize 351} {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_docsize 358} {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 or 363# 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_stat 372} 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 t5 378} 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_stat 384} 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_content 405} 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 close 430sqlite3 db test.db 431do_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_stat 436} 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 close 466sqlite3 db test.db 467do_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_db 534register_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_db 571register_fs_module [sqlite3_connection_pointer db] 572 573proc write_file {path text} { 574 set fd [open $path w] 575 puts -nonewline $fd $text 576 close $fd 577} 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_db 632 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_db 641breakpoint 642do_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_db 657do_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_test 675