1# 2015 September 05 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# 12 13source [file join [file dirname [info script]] fts5_common.tcl] 14set testprefix fts5simple 15 16# If SQLITE_ENABLE_FTS5 is defined, omit this file. 17ifcapable !fts5 { 18 finish_test 19 return 20} 21 22if 1 { 23 24#------------------------------------------------------------------------- 25# 26set doc "x x [string repeat {y } 50]z z" 27do_execsql_test 1.0 { 28 CREATE VIRTUAL TABLE t1 USING fts5(x); 29 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); 30 BEGIN; 31 INSERT INTO t1 VALUES($doc); 32 COMMIT; 33} 34 35do_execsql_test 1.1 { 36 INSERT INTO t1(t1) VALUES('integrity-check'); 37} 38 39#------------------------------------------------------------------------- 40# 41reset_db 42do_execsql_test 2.0 { 43 CREATE VIRTUAL TABLE t1 USING fts5(x); 44 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); 45 INSERT INTO t1 VALUES('a b c'); 46 INSERT INTO t1 VALUES('d e f'); 47 INSERT INTO t1(t1) VALUES('optimize'); 48} 49 50do_execsql_test 2.1 { 51 INSERT INTO t1(t1) VALUES('integrity-check'); 52} {} 53 54 55#------------------------------------------------------------------------- 56# 57reset_db 58do_execsql_test 3.0 { 59 CREATE VIRTUAL TABLE t1 USING fts5(x, prefix='1,2'); 60 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); 61 BEGIN; 62 INSERT INTO t1 VALUES('one'); 63 SELECT * FROM t1 WHERE t1 MATCH 'o*'; 64} {one} 65 66do_execsql_test 3.1 { 67 INSERT INTO t1(t1) VALUES('integrity-check'); 68} {} 69 70#------------------------------------------------------------------------- 71reset_db 72do_execsql_test 4.1 { 73 CREATE VIRTUAL TABLE t11 USING fts5(content); 74 INSERT INTO t11(t11, rank) VALUES('pgsz', 32); 75 INSERT INTO t11 VALUES('another'); 76 INSERT INTO t11 VALUES('string'); 77 INSERT INTO t11 VALUES('of'); 78 INSERT INTO t11 VALUES('text'); 79} 80do_test 4.2 { 81 execsql { INSERT INTO t11(t11) VALUES('optimize') } 82} {} 83do_execsql_test 4.3 { 84 INSERT INTO t11(t11) VALUES('integrity-check'); 85} {} 86 87#db eval { SELECT fts5_decode(rowid, block) as x FROM t11_data } { puts $x } 88 89#------------------------------------------------------------------------- 90reset_db 91set doc [string repeat "x y " 5] 92do_execsql_test 5.1 { 93 CREATE VIRTUAL TABLE yy USING fts5(content); 94 INSERT INTO yy(yy, rank) VALUES('pgsz', 32); 95 BEGIN; 96 INSERT INTO yy VALUES($doc); 97 INSERT INTO yy VALUES($doc); 98 INSERT INTO yy VALUES($doc); 99 INSERT INTO yy VALUES($doc); 100 INSERT INTO yy VALUES($doc); 101 INSERT INTO yy VALUES($doc); 102 INSERT INTO yy VALUES($doc); 103 INSERT INTO yy VALUES($doc); 104 COMMIT; 105} 106 107do_execsql_test 5.2 { 108 SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid ASC 109} {1 2 3 4 5 6 7 8} 110 111do_execsql_test 5.3 { 112 SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid DESC 113} {8 7 6 5 4 3 2 1} 114 115#db eval { SELECT fts5_decode(rowid, block) as x FROM yy_data } { puts $x } 116 117#------------------------------------------------------------------------- 118reset_db 119do_execsql_test 5.1 { 120 CREATE VIRTUAL TABLE tt USING fts5(content); 121 INSERT INTO tt(tt, rank) VALUES('pgsz', 32); 122 INSERT INTO tt VALUES('aa'); 123} 124 125do_execsql_test 5.2 { 126 SELECT rowid FROM tt WHERE tt MATCH 'a*'; 127} {1} 128 129do_execsql_test 5.3 { 130 DELETE FROM tt; 131 BEGIN; 132 INSERT INTO tt VALUES('aa'); 133 INSERT INTO tt VALUES('ab'); 134 COMMIT; 135} {} 136 137do_execsql_test 5.4 { 138 SELECT rowid FROM tt WHERE tt MATCH 'a*'; 139} {1 2} 140 141do_execsql_test 5.5 { 142 DELETE FROM tt; 143 BEGIN; 144 INSERT INTO tt VALUES('aa'); 145 INSERT INTO tt VALUES('ab'); 146 INSERT INTO tt VALUES('aa'); 147 INSERT INTO tt VALUES('ab'); 148 INSERT INTO tt VALUES('aa'); 149 INSERT INTO tt VALUES('ab'); 150 INSERT INTO tt VALUES('aa'); 151 INSERT INTO tt VALUES('ab'); 152 COMMIT; 153 SELECT rowid FROM tt WHERE tt MATCH 'a*'; 154} {1 2 3 4 5 6 7 8} 155 156do_execsql_test 5.6 { 157 INSERT INTO tt(tt) VALUES('integrity-check'); 158} 159 160reset_db 161do_execsql_test 5.7 { 162 CREATE VIRTUAL TABLE tt USING fts5(content); 163 INSERT INTO tt(tt, rank) VALUES('pgsz', 32); 164 INSERT INTO tt VALUES('aa ab ac ad ae af'); 165} 166 167do_execsql_test 5.8 { 168 SELECT rowid FROM tt WHERE tt MATCH 'a*'; 169} {1} 170 171#------------------------------------------------------------------------- 172 173reset_db 174do_execsql_test 6.1 { 175 CREATE VIRTUAL TABLE xyz USING fts5(x, y, z); 176 INSERT INTO xyz VALUES('x', 'y', 'z'); 177} 178 179do_catchsql_test 6.2 { 180 SELECT * FROM xyz WHERE xyz MATCH '' 181} {1 {fts5: syntax error near ""}} 182do_catchsql_test 6.3 { 183 SELECT * FROM xyz WHERE xyz MATCH NULL 184} {1 {fts5: syntax error near ""}} 185 186#------------------------------------------------------------------------- 187 188do_execsql_test 7.1 { 189 CREATE VIRTUAL TABLE ft2 USING fts5(content); 190 INSERT INTO ft2(rowid, content) VALUES(1, 'a b c'); 191 INSERT INTO ft2(rowid, content) VALUES(2, 'a b d'); 192} 193 194do_catchsql_test 7.2 { 195 BEGIN; 196 UPDATE ft2 SET rowid=2 WHERE rowid=1; 197} {1 {constraint failed}} 198 199do_execsql_test 7.3 { 200 COMMIT; 201 INSERT INTO ft2(ft2) VALUES('integrity-check'); 202} {} 203 204do_execsql_test 7.4 { 205 SELECT * FROM ft2; 206} {{a b c} {a b d}} 207 208#------------------------------------------------------------------------- 209# 210reset_db 211do_execsql_test 8.1 { 212 CREATE VIRTUAL TABLE ft2 USING fts5(content); 213 INSERT INTO ft2(rowid, content) VALUES(1, 'a b'); 214} 215 216do_execsql_test 8.2 { 217 BEGIN; 218 INSERT INTO ft2(rowid, content) VALUES(4, 'a x'); 219} 220 221do_execsql_test 8.3 { 222 INSERT INTO ft2(ft2) VALUES('integrity-check'); 223} 224 225#------------------------------------------------------------------------- 226# Check that the "table function" syntax works. 227# 228reset_db 229do_execsql_test 9.1 { 230 CREATE VIRTUAL TABLE ft2 USING fts5(content); 231 INSERT INTO ft2(rowid, content) VALUES(1, 'a b'); 232 INSERT INTO ft2(rowid, content) VALUES(2, 'a b c d'); 233 INSERT INTO ft2(rowid, content) VALUES(3, 'c d e f'); 234} 235 236do_execsql_test 9.2 { 237 SELECT rowid FROM ft2('a'); 238} {1 2} 239 240do_execsql_test 9.3 { 241 SELECT rowid FROM ft2('b AND c'); 242} {2} 243 244#------------------------------------------------------------------------- 245# 246do_execsql_test 10.0 { 247 CREATE VIRTUAL TABLE t3 USING fts5(a, b, c); 248 INSERT INTO t3 VALUES('bac aab bab', 'c bac c', 'acb aba abb'); -- 1 249 INSERT INTO t3 VALUES('bab abc c', 'acb c abb', 'c aaa c'); -- 2 250} 251 252do_execsql_test 10.1 { 253 SELECT rowid FROM t3('c: c*'); 254} {2} 255 256do_execsql_test 10.2 { 257 SELECT rowid FROM t3('b: acb'); 258} {2} 259 260#------------------------------------------------------------------------- 261# Test that character 0x1A is allowed in fts5 barewords. 262# 263do_test 11.0 { 264 execsql "CREATE VIRTUAL TABLE t4 USING fts5(x, tokenize=\"ascii tokenchars '\x1A'\")" 265 execsql " 266 INSERT INTO t4 VALUES('a b c \x1A'); 267 INSERT INTO t4 VALUES('a b c d\x1A'); 268 INSERT INTO t4 VALUES('a b c \x1Ag'); 269 INSERT INTO t4 VALUES('a b c d'); 270 " 271} {} 272 273do_test 11.1 { 274 execsql "SELECT rowid FROM t4('\x1A')" 275} {1} 276do_test 11.2 { 277 execsql "SELECT rowid FROM t4('\x1A*')" 278} {1 3} 279do_test 11.3 { 280 execsql "SELECT rowid FROM t4('d\x1A')" 281} {2} 282 283do_test 11.4 { 284 catchsql "SELECT rowid FROM t4('d\x1B')" 285} {/fts5: syntax error/} 286do_test 11.5 { 287 catchsql "SELECT rowid FROM t4('d\x19')" 288} {/fts5: syntax error/} 289 290#------------------------------------------------------------------------- 291# 292do_test 12.1 { 293 execsql { 294 CREATE VIRTUAL TABLE xx USING fts5(x,y); 295 BEGIN; 296 INSERT INTO xx VALUES('1 2 3', 'a b c'); 297 } 298} {} 299 300do_execsql_test 12.2 { 301 SELECT rowid FROM xx('x:a'); 302 COMMIT; 303} {} 304 305#------------------------------------------------------------------------- 306# Try an UPDATE OR REPLACE query. 307# 308do_execsql_test 13.1 { 309 CREATE VIRTUAL TABLE xy USING fts5(x); 310 INSERT INTO xy(rowid, x) VALUES(1, '1 2 3'); 311 INSERT INTO xy(rowid, x) VALUES(2, '2 3 4'); 312 INSERT INTO xy(rowid, x) VALUES(3, '3 4 5'); 313} 314 315do_execsql_test 13.2 { 316 UPDATE OR REPLACE xy SET rowid=3 WHERE rowid = 2; 317 SELECT rowid, x FROM xy; 318} { 319 1 {1 2 3} 320 3 {2 3 4} 321} 322 323do_execsql_test 13.3 { 324 INSERT INTO xy(xy) VALUES('integrity-check'); 325} 326 327#------------------------------------------------------------------------- 328# 329do_execsql_test 14.1 { 330 CREATE VIRTUAL TABLE ttt USING fts5(x); 331 BEGIN; 332 INSERT INTO ttt(rowid, x) VALUES(1, 'a b c'); 333 INSERT INTO ttt(rowid, x) VALUES(2, 'a b c'); 334 INSERT INTO ttt(rowid, x) VALUES(3, 'a b c'); 335 COMMIT; 336} 337do_test 14.2 { 338 fts5_level_segs ttt 339} {1} 340 341#------------------------------------------------------------------------- 342db func rnddoc fts5_rnddoc 343do_execsql_test 14.3 { 344 CREATE VIRTUAL TABLE x1 USING fts5(x); 345 INSERT INTO x1(x1, rank) VALUES('pgsz', 32); 346 347 WITH ii(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM ii WHERE i<10 ) 348 INSERT INTO x1 SELECT rnddoc(5) FROM ii; 349} 350 351do_execsql_test 14.4 { 352 SELECT rowid, x, x1 FROM x1 WHERE x1 MATCH '*reads' 353} {0 {} 3} 354 355#------------------------------------------------------------------------- 356reset_db 357do_execsql_test 15.0 { 358 CREATE VIRTUAL TABLE x2 USING fts5(x, prefix=1); 359 INSERT INTO x2 VALUES('ab'); 360} 361 362do_execsql_test 15.1 { 363 INSERT INTO x2(x2) VALUES('integrity-check'); 364} 365 366#------------------------------------------------------------------------- 367foreach_detail_mode $testprefix { 368 reset_db 369 fts5_aux_test_functions db 370 do_execsql_test 16.0 { 371 CREATE VIRTUAL TABLE x3 USING fts5(x, detail=%DETAIL%); 372 INSERT INTO x3 VALUES('a b c d e f'); 373 } 374 do_execsql_test 16.1 { 375 SELECT fts5_test_poslist(x3) FROM x3('(a NOT b) OR c'); 376 } {2.0.2} 377 378 do_execsql_test 16.1 { 379 SELECT fts5_test_poslist(x3) FROM x3('a OR c'); 380 } {{0.0.0 1.0.2}} 381} 382 383} 384 385#------------------------------------------------------------------------- 386reset_db 387do_execsql_test 17.0 { 388 CREATE VIRTUAL TABLE x3 USING fts5(x); 389 INSERT INTO x3 VALUES('a b c'); 390} 391 392do_execsql_test 17.1 { 393 SELECT rowid FROM x3('b AND d'); 394} 395 396#------------------------------------------------------------------------- 397do_execsql_test 18.1 { 398 CREATE VIRTUAL TABLE x4 USING fts5(x); 399 SELECT rowid FROM x4('""'); 400} 401 402#------------------------------------------------------------------------- 403reset_db 404do_execsql_test 19.1 { 405 CREATE VIRTUAL TABLE x1 USING fts5(a,b,c); 406} 407 408do_catchsql_test 19.2 { 409 SELECT * FROM x1 WHERE x1 MATCH 'c0 AND (c1 AND (c2 AND (c3 AND (c4 AND (c5 AND (c6 AND (c7 AND (c8 AND (c9 AND (c10 AND (c11 AND (c12 AND (c13 AND (c14 AND (c15 AND (c16 AND (c17 AND (c18 AND (c19 AND (c20 AND (c21 AND (c22 AND (c23 AND (c24 AND (c25 AND (c26 AND (c27 AND (c28 AND (c29 AND (c30 AND (c31 AND (c32 AND (c33 AND (c34 AND (c35 AND (c36 AND (c37 AND (c38 AND (c39 AND (c40 AND (c41 AND (c42 AND (c43 AND (c44 AND (c45 AND (c46 AND (c47 AND (c48 AND (c49 AND (c50 AND (c51 AND (c52 AND (c53 AND (c54 AND (c55 AND (c56 AND (c57 AND (c58 AND (c59 AND (c60 AND (c61 AND (c62 AND (c63 AND (c64 AND (c65 AND (c66 AND (c67 AND (c68 AND (c69 AND (c70 AND (c71 AND (c72 AND (c73 AND (c74 AND (c75 AND (c76 AND (c77 AND (c78 AND (c79 AND (c80 AND (c81 AND (c82 AND (c83 AND (c84 AND (c85 AND (c86 AND (c87 AND (c88 AND (c89 AND (c90 AND (c91 AND (c92 AND (c93 AND (c94 AND (c95 AND (c96 AND (c97 AND (c98 AND (c99 AND (c100 AND (c101 AND (c102 AND (c103 AND (c104 AND (c105 AND (c106 AND (c107 AND (c108 AND (c109 AND (c110 AND (c111 AND (c112 AND (c113 AND (c114 AND (c115 AND (c116 AND (c117 AND (c118 AND (c119 AND (c120 AND (c121 AND (c122 AND (c123 AND (c124 AND (c125 AND (c126 AND (c127 AND (c128 AND (c129 AND (c130 AND (c131 AND (c132 AND (c133 AND (c134 AND (c135 AND (c136 AND (c137 AND (c138 AND (c139 AND (c140 AND (c141 AND (c142 AND (c143 AND (c144 AND (c145 AND (c146 AND (c147 AND (c148 AND (c149 AND (c150 AND (c151 AND (c152 AND (c153 AND (c154 AND (c155 AND (c156 AND (c157 AND (c158 AND (c159 AND (c160 AND (c161 AND (c162 AND (c163 AND (c164 AND (c165 AND (c166 AND (c167 AND (c168 AND (c169 AND (c170 AND (c171 AND (c172 AND (c173 AND (c174 AND (c175 AND (c176 AND (c177 AND (c178 AND (c179 AND (c180 AND (c181 AND (c182 AND (c183 AND (c184 AND (c185 AND (c186 AND (c187 AND (c188 AND (c189 AND (c190 AND (c191 AND (c192 AND (c193 AND (c194 AND (c195 AND (c196 AND (c197 AND (c198 AND (c199 AND c200)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))'; 410} {1 {fts5: parser stack overflow}} 411 412#------------------------------------------------------------------------- 413reset_db 414do_execsql_test 20.0 { 415 CREATE VIRTUAL TABLE x1 USING fts5(x); 416 INSERT INTO x1(x1, rank) VALUES('pgsz', 32); 417 INSERT INTO x1(rowid, x) VALUES(11111, 'onetwothree'); 418} 419do_test 20.1 { 420 for {set i 1} {$i <= 200} {incr i} { 421 execsql { INSERT INTO x1(rowid, x) VALUES($i, 'one two three'); } 422 } 423 execsql { INSERT INTO x1(x1) VALUES('optimize'); } 424 execsql { DELETE FROM x1 WHERE rowid = 4; } 425} {} 426do_execsql_test 20.2 { 427 INSERT INTO x1(x1) VALUES('optimize'); 428 INSERT INTO x1(x1) VALUES('integrity-check'); 429} {} 430 431#------------------------------------------------------------------------- 432reset_db 433do_execsql_test 20.0 { 434 CREATE VIRTUAL TABLE x1 USING fts5(x); 435 INSERT INTO x1(x1, rank) VALUES('pgsz', 32); 436 INSERT INTO x1(rowid, x) VALUES(11111, 'onetwothree'); 437} 438do_test 20.1 { 439 for {set i 1} {$i <= 200} {incr i} { 440 execsql { INSERT INTO x1(rowid, x) VALUES($i, 'one two three'); } 441 } 442 execsql { INSERT INTO x1(x1) VALUES('optimize'); } 443 execsql { DELETE FROM x1 WHERE rowid = 4; } 444} {} 445do_execsql_test 20.2 { 446 INSERT INTO x1(x1) VALUES('optimize'); 447 INSERT INTO x1(x1) VALUES('integrity-check'); 448} {} 449 450#------------------------------------------------------------------------- 451reset_db 452set doc "a b [string repeat x 100000]" 453do_execsql_test 21.0 { 454 CREATE VIRTUAL TABLE x1 USING fts5(x); 455 INSERT INTO x1(rowid, x) VALUES(11111, $doc); 456 INSERT INTO x1(rowid, x) VALUES(11112, $doc); 457} 458do_execsql_test 21.1 { 459 INSERT INTO x1(x1) VALUES('integrity-check'); 460} 461do_execsql_test 21.2 { 462 SELECT rowid FROM x1($doc); 463} {11111 11112} 464do_execsql_test 21.3 { 465 DELETE FROM x1 WHERE rowid=11111; 466 INSERT INTO x1(x1) VALUES('integrity-check'); 467 SELECT rowid FROM x1($doc); 468} {11112} 469 470#------------------------------------------------------------------------- 471reset_db 472do_execsql_test 22.0 { 473 CREATE VIRTUAL TABLE x1 USING fts5(x); 474 INSERT INTO x1(x) VALUES('a b c'); 475 INSERT INTO x1(x) VALUES('x y z'); 476 INSERT INTO x1(x) VALUES('c b a'); 477 INSERT INTO x1(x) VALUES('z y x'); 478} 479 480do_catchsql_test 22.1 {SELECT * FROM x1('')} {1 {fts5: syntax error near ""}} 481do_catchsql_test 22.2 {SELECT * FROM x1(NULL)} {1 {fts5: syntax error near ""}} 482 483finish_test 484