| OLD | NEW |
|---|
| (Empty) |
| 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 or missing database}} | |
| 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 or missing database}} | |
| 461 do_catchsql_test 6.2.9 { | |
| 462 SELECT * FROM ft7 WHERE ft7 MATCH '"A A"'; | |
| 463 } {1 {SQL logic error or missing database}} | |
| 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 or missing database}} | |
| 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 finish_test | |
| 639 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
