| 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 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set ::testprefix fts4content |
| 18 |
|
| 19 |
# If SQLITE_ENABLE_FTS3 is defined, omit this file. |
| 20 |
ifcapable !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 |
|
| 55 |
do_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 |
|
| 61 |
do_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 |
|
| 69 |
do_execsql_test 1.1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1} |
| 70 |
do_execsql_test 1.1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}} |
| 71 |
|
| 72 |
do_execsql_test 1.1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {} |
| 73 |
do_execsql_test 1.1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1} |
| 74 |
do_execsql_test 1.1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {} |
| 75 |
|
| 76 |
do_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 |
} |
| 83 |
do_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 |
# |
| 93 |
do_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 |
|
| 117 |
do_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 |
|
| 127 |
foreach {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 |
|
| 143 |
foreach {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 |
|
| 153 |
foreach {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 |
|
| 163 |
foreach {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 |
|
| 180 |
do_execsql_test 3.1 { |
| 181 |
CREATE TABLE t3(x, y); |
| 182 |
CREATE VIRTUAL TABLE ft3 USING fts4(content=t3); |
| 183 |
} |
| 184 |
|
| 185 |
do_catchsql_test 3.1.1 { |
| 186 |
INSERT INTO ft3 VALUES('a b c', 'd e f'); |
| 187 |
} {1 {constraint failed}} |
| 188 |
do_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} |
| 192 |
do_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. |
| 196 |
do_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. |
| 202 |
do_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 |
} {} |
| 207 |
do_execsql_test 3.1.6 { SELECT rowid FROM t3 } {21} |
| 208 |
|
| 209 |
do_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 |
|
| 232 |
foreach {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 |
|
| 248 |
do_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 |
|
| 259 |
foreach {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 |
|
| 275 |
do_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 |
|
| 284 |
foreach {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 |
# |
| 305 |
do_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 |
|
| 311 |
do_execsql_test 4.1.1 { |
| 312 |
INSERT INTO ft4x(ft4x) VALUES('rebuild'); |
| 313 |
INSERT INTO ft4(ft4) VALUES('rebuild'); |
| 314 |
} {} |
| 315 |
do_execsql_test 4.1.2 { |
| 316 |
SELECT id, quote(value) FROM ft4_stat |
| 317 |
} {0 X'000000'} |
| 318 |
do_execsql_test 4.1.3 { |
| 319 |
SELECT id, quote(value) FROM ft4x_stat |
| 320 |
} {0 X'000000'} |
| 321 |
|
| 322 |
do_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 |
|
| 332 |
do_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 |
|
| 337 |
do_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 |
|
| 341 |
do_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 |
|
| 347 |
do_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 |
|
| 353 |
do_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 |
# |
| 365 |
do_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 |
} |
| 373 |
do_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 |
} |
| 379 |
do_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 |
} |
| 385 |
do_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} |
| 390 |
do_execsql_test 5.1.5 { |
| 391 |
DROP TABLE ft6; |
| 392 |
SELECT * FROM t5; |
| 393 |
} {a b c d} |
| 394 |
do_execsql_test 5.1.6 { |
| 395 |
SELECT name FROM sqlite_master WHERE name LIKE '%t6%'; |
| 396 |
} { |
| 397 |
} |
| 398 |
do_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 |
# |
| 410 |
do_catchsql_test 6.1.1 { |
| 411 |
CREATE VIRTUAL TABLE ft7 USING fts4(content=t7); |
| 412 |
} {1 {no such table: main.t7}} |
| 413 |
|
| 414 |
do_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 |
|
| 424 |
do_catchsql_test 6.2.2 { |
| 425 |
DROP TABLE t7; |
| 426 |
SELECT * FROM ft7; |
| 427 |
} {1 {SQL logic error}} |
| 428 |
|
| 429 |
db close |
| 430 |
sqlite3 db test.db |
| 431 |
do_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 |
} |
| 437 |
do_catchsql_test 6.2.4 { |
| 438 |
SELECT * FROM ft7; |
| 439 |
} {1 {no such table: main.t7}} |
| 440 |
do_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 |
|
| 449 |
do_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 |
|
| 454 |
do_execsql_test 6.2.7 { |
| 455 |
DROP TABLE t7; |
| 456 |
CREATE TABLE t7(x); |
| 457 |
} |
| 458 |
do_catchsql_test 6.2.8 { |
| 459 |
SELECT * FROM ft7 WHERE ft7 MATCH '"A A"'; |
| 460 |
} {1 {SQL logic error}} |
| 461 |
do_catchsql_test 6.2.9 { |
| 462 |
SELECT * FROM ft7 WHERE ft7 MATCH '"A A"'; |
| 463 |
} {1 {SQL logic error}} |
| 464 |
|
| 465 |
db close |
| 466 |
sqlite3 db test.db |
| 467 |
do_catchsql_test 6.2.10 { |
| 468 |
SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"'; |
| 469 |
} {0 2} |
| 470 |
do_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 |
# |
| 477 |
do_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 |
|
| 486 |
do_execsql_test 7.1.2 { |
| 487 |
SELECT docid FROM ft8 WHERE ft8 MATCH 'N'; |
| 488 |
} {13 15} |
| 489 |
|
| 490 |
do_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 |
} |
| 498 |
do_execsql_test 7.2.2 { |
| 499 |
SELECT docid FROM ft9 WHERE ft9 MATCH 'N'; |
| 500 |
} {13 15} |
| 501 |
do_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 |
|
| 505 |
do_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 |
# |
| 512 |
do_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 |
|
| 524 |
do_execsql_test 8.2 { SELECT * FROM ft10 WHERE a MATCH 'ab*'; } |
| 525 |
do_execsql_test 8.3 { INSERT INTO ft10(ft10) VALUES('rebuild'); } |
| 526 |
do_execsql_test 8.4 { SELECT rowid FROM ft10 WHERE a MATCH 'ab*'; } {1 2 3} |
| 527 |
do_execsql_test 8.5 { SELECT rowid FROM ft10 WHERE b MATCH 'abav*'; } {3} |
| 528 |
do_execsql_test 8.6 { SELECT rowid FROM ft10 WHERE ft10 MATCH 'abas*'; } {1} |
| 529 |
|
| 530 |
#------------------------------------------------------------------------- |
| 531 |
# Test cases 9.* |
| 532 |
# |
| 533 |
reset_db |
| 534 |
register_echo_module [sqlite3_connection_pointer db] |
| 535 |
|
| 536 |
do_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 |
|
| 545 |
do_execsql_test 9.2 { |
| 546 |
SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'e' |
| 547 |
} {2 {e f} {a b}} |
| 548 |
|
| 549 |
do_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 |
|
| 553 |
do_execsql_test 9.4 { |
| 554 |
DELETE FROM ft1 WHERE docid=1; |
| 555 |
} |
| 556 |
|
| 557 |
do_execsql_test 9.5 { |
| 558 |
SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a' |
| 559 |
} {2 {e f} {a b}} |
| 560 |
|
| 561 |
do_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 |
# |
| 570 |
reset_db |
| 571 |
register_fs_module [sqlite3_connection_pointer db] |
| 572 |
|
| 573 |
proc write_file {path text} { |
| 574 |
set fd [open $path w] |
| 575 |
puts -nonewline $fd $text |
| 576 |
close $fd |
| 577 |
} |
| 578 |
|
| 579 |
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} |
| 580 |
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} |
| 581 |
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} |
| 582 |
|
| 583 |
do_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 |
|
| 597 |
do_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 |
|
| 603 |
do_execsql_test 10.3 { |
| 604 |
CREATE VIRTUAL TABLE ft USING fts4(content=vt); |
| 605 |
INSERT INTO ft(ft) VALUES('rebuild'); |
| 606 |
} |
| 607 |
|
| 608 |
do_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 |
|
| 614 |
do_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 |
|
| 620 |
do_execsql_test 10.6 { DELETE FROM ft WHERE docid=2 } |
| 621 |
|
| 622 |
do_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 |
# |
| 631 |
reset_db |
| 632 |
|
| 633 |
do_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 |
# |
| 640 |
reset_db |
| 641 |
do_execsql_test 12.1.1 { |
| 642 |
CREATE VIRTUAL TABLE t1 USING fts4(a, content=t1 ); |
| 643 |
INSERT INTO t1(rowid, a) VALUES(1, 'abc'); |
| 644 |
} |
| 645 |
do_catchsql_test 12.1.2 { |
| 646 |
SELECT * FROM t1; |
| 647 |
} {1 {SQL logic error}} |
| 648 |
do_catchsql_test 12.1.3 { |
| 649 |
SELECT * FROM t1('abc'); |
| 650 |
} {1 {SQL logic error}} |
| 651 |
do_catchsql_test 12.1.4 { |
| 652 |
SELECT count(*) FROM t1; |
| 653 |
} {1 {SQL logic error}} |
| 654 |
|
| 655 |
reset_db |
| 656 |
do_execsql_test 12.2.1 { |
| 657 |
CREATE VIRTUAL TABLE t1 USING fts4(a, content=t2 ); |
| 658 |
CREATE VIRTUAL TABLE t2 USING fts4(a, content=t1 ); |
| 659 |
INSERT INTO t1(rowid, a) VALUES(1, 'abc'); |
| 660 |
} |
| 661 |
do_catchsql_test 12.2.2 { |
| 662 |
SELECT * FROM t1; |
| 663 |
} {1 {SQL logic error}} |
| 664 |
do_catchsql_test 12.2.3 { |
| 665 |
SELECT * FROM t1('abc'); |
| 666 |
} {1 {SQL logic error}} |
| 667 |
do_catchsql_test 12.2.4 { |
| 668 |
SELECT count(*) FROM t1; |
| 669 |
} {1 {SQL logic error}} |
| 670 |
|
| 671 |
#--------------------------------------------------------------------------- |
| 672 |
# Check that an fts4 table cannot read from an unsafe vtab in a non-trusted |
| 673 |
# schema. |
| 674 |
reset_db |
| 675 |
do_execsql_test 13.0 { |
| 676 |
PRAGMA trusted_schema = off; |
| 677 |
CREATE VIRTUAL TABLE t1 USING fts4(data, content=sqlite_dbpage); |
| 678 |
} |
| 679 |
|
| 680 |
do_catchsql_test 13.1 { |
| 681 |
INSERT INTO t1(t1) VALUES('rebuild'); |
| 682 |
} {1 {SQL logic error}} |
| 683 |
|
| 684 |
proc vtab_command {method args} { |
| 685 |
switch -- $method { |
| 686 |
xConnect { |
| 687 |
return "CREATE TABLE t1(a)" |
| 688 |
} |
| 689 |
|
| 690 |
xBestIndex { |
| 691 |
return "" |
| 692 |
} |
| 693 |
|
| 694 |
xFilter { |
| 695 |
return [list sql {SELECT 1, 123}] |
| 696 |
} |
| 697 |
|
| 698 |
xUpdate { |
| 699 |
return 123 |
| 700 |
} |
| 701 |
} |
| 702 |
|
| 703 |
return {} |
| 704 |
} |
| 705 |
|
| 706 |
register_tcl_module db xyz |
| 707 |
|
| 708 |
do_execsql_test 13.2.0 { |
| 709 |
CREATE VIRTUAL TABLE aa USING tcl(vtab_command); |
| 710 |
} |
| 711 |
|
| 712 |
do_execsql_test 13.2.1 { |
| 713 |
INSERT INTO aa VALUES('one two three'); |
| 714 |
} |
| 715 |
|
| 716 |
do_test 13.2.2 { |
| 717 |
set ::stmt [sqlite3_prepare_v3 db \ |
| 718 |
"INSERT INTO aa VALUES('one two three');" -1 0x00 |
| 719 |
] |
| 720 |
sqlite3_finalize $::stmt |
| 721 |
} {SQLITE_OK} |
| 722 |
|
| 723 |
do_test 13.2.2 { |
| 724 |
list [catch { |
| 725 |
set ::stmt [sqlite3_prepare_v3 db \ |
| 726 |
"INSERT INTO aa VALUES('one two three');" -1 0x20 |
| 727 |
] |
| 728 |
} msg] $msg |
| 729 |
} {1 {(1) unsafe use of virtual table "aa"}} |
| 730 |
|
| 731 |
finish_test |