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 fts5simple2 15 16# If SQLITE_ENABLE_FTS5 is defined, omit this file. 17ifcapable !fts5 { 18 finish_test 19 return 20} 21 22do_execsql_test 1.0 { 23 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 24 INSERT INTO t1 VALUES('a b c'); 25} 26do_execsql_test 1.1 { 27 SELECT rowid FROM t1('c a b') 28} {1} 29 30#------------------------------------------------------------------------- 31# 32reset_db 33do_execsql_test 2.0 { 34 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 35 BEGIN; 36 INSERT INTO t1 VALUES('b c d'); 37 INSERT INTO t1 VALUES('b c d'); 38 COMMIT; 39} 40do_execsql_test 2.1 { 41 SELECT rowid FROM t1('b c d') 42} {1 2} 43 44#------------------------------------------------------------------------- 45# 46reset_db 47do_execsql_test 3.0 { 48 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 49 BEGIN; 50 INSERT INTO t1 VALUES('b c d'); 51 INSERT INTO t1 VALUES('b c d'); 52} 53do_execsql_test 3.1 { 54 SELECT rowid FROM t1('b c d'); COMMIT; 55} {1 2} 56 57#------------------------------------------------------------------------- 58# 59reset_db 60do_execsql_test 4.0 { 61 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 62 BEGIN; 63 INSERT INTO t1 VALUES('a1 b1 c1'); 64 INSERT INTO t1 VALUES('a2 b2 c2'); 65 INSERT INTO t1 VALUES('a3 b3 c3'); 66 COMMIT; 67} 68do_execsql_test 4.1 { 69 SELECT rowid FROM t1('b*'); 70} {1 2 3} 71 72 73#------------------------------------------------------------------------- 74# 75reset_db 76do_execsql_test 5.0 { 77 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 78 BEGIN; 79 INSERT INTO t1 VALUES('a1 b1 c1'); 80 INSERT INTO t1 VALUES('a2 b2 c2'); 81 INSERT INTO t1 VALUES('a1 b1 c1'); 82 COMMIT; 83} 84do_execsql_test 5.1 { SELECT rowid FROM t1('b*') } {1 2 3} 85 86#------------------------------------------------------------------------- 87# 88reset_db 89do_execsql_test 6.0 { 90 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=full); 91 BEGIN; 92 INSERT INTO t1 VALUES('a1 b1 c1'); 93 INSERT INTO t1 VALUES('a1 b1 c1'); 94 INSERT INTO t1 VALUES('a1 b1 c1'); 95 COMMIT; 96} 97 98do_execsql_test 6.1 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {3 2 1} 99do_execsql_test 6.2 { SELECT rowid FROM t1('b1') ORDER BY rowid DESC } {3 2 1} 100do_execsql_test 6.3 { SELECT rowid FROM t1('c1') ORDER BY rowid DESC } {3 2 1} 101 102#------------------------------------------------------------------------- 103# 104reset_db 105do_execsql_test 7.0 { 106 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 107 BEGIN; 108 INSERT INTO t1 VALUES('a1 b1'); 109 INSERT INTO t1 VALUES('a1 b2'); 110 COMMIT; 111} 112do_execsql_test 7.1 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC } {2 1} 113do_execsql_test 7.2 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {2 1} 114 115#------------------------------------------------------------------------- 116# 117reset_db 118do_execsql_test 8.0 { 119 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 120 INSERT INTO t1 VALUES('a1 b1 c1'); 121 INSERT INTO t1 VALUES('a2 b2 c2'); 122 INSERT INTO t1 VALUES('a1 b1 c1'); 123} 124do_execsql_test 8.0.1 { SELECT rowid FROM t1('b*') } {1 2 3} 125do_execsql_test 8.0.2 { SELECT rowid FROM t1('a1') } {1 3} 126do_execsql_test 8.0.3 { SELECT rowid FROM t1('c2') } {2} 127 128do_execsql_test 8.0.4 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC } {3 2 1} 129do_execsql_test 8.0.5 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {3 1} 130do_execsql_test 8.0.8 { SELECT rowid FROM t1('c2') ORDER BY rowid DESC } {2} 131 132do_execsql_test 8.1.0 { INSERT INTO t1(t1) VALUES('optimize') } 133 134do_execsql_test 8.1.1 { SELECT rowid FROM t1('b*') } {1 2 3} 135do_execsql_test 8.1.2 { SELECT rowid FROM t1('a1') } {1 3} 136do_execsql_test 8.1.3 { SELECT rowid FROM t1('c2') } {2} 137 138do_execsql_test 8.2.1 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC} {3 2 1} 139do_execsql_test 8.2.2 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC} {3 1} 140do_execsql_test 8.2.3 { SELECT rowid FROM t1('c2') ORDER BY rowid DESC} {2} 141 142#-------------------------------------------------------------------------- 143# 144reset_db 145do_execsql_test 9.0.0 { 146 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 147 INSERT INTO t1 VALUES('a1 b1 c1'); 148 INSERT INTO t1 VALUES('a2 b2 c2'); 149 INSERT INTO t1 VALUES('a1 b1 c1'); 150} 151do_execsql_test 9.0.1 { 152 INSERT INTO t1(t1) VALUES('integrity-check'); 153} {} 154 155reset_db 156do_execsql_test 9.1.0 { 157 CREATE VIRTUAL TABLE t1 USING fts5(a, b, detail=none); 158 INSERT INTO t1 VALUES('a1 b1 c1', 'x y z'); 159 INSERT INTO t1 VALUES('a2 b2 c2', '1 2 3'); 160 INSERT INTO t1 VALUES('a1 b1 c1', 'x 2 z'); 161} 162do_execsql_test 9.2.1 { 163 INSERT INTO t1(t1) VALUES('integrity-check'); 164} {} 165 166#-------------------------------------------------------------------------- 167# 168reset_db 169do_execsql_test 10.0 { 170 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 171 INSERT INTO t1 VALUES('b1'); 172 INSERT INTO t1 VALUES('b1'); 173 DELETE FROM t1 WHERE rowid=1; 174} 175 176do_execsql_test 10.1 { 177 SELECT rowid FROM t1('b1'); 178} {2} 179 180do_execsql_test 10.2 { 181 SELECT rowid FROM t1('b1') ORDER BY rowid DESC; 182} {2} 183 184do_execsql_test 10.3 { 185 INSERT INTO t1(t1) VALUES('integrity-check'); 186} {} 187 188#-------------------------------------------------------------------------- 189# 190reset_db 191do_execsql_test 11.1 { 192 CREATE VIRTUAL TABLE t1 USING fts5(x, y, detail=none); 193 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); 194 WITH d(x,y) AS ( 195 SELECT NULL, 'xyz' UNION ALL SELECT NULL, 'xyz' FROM d 196 ) 197 INSERT INTO t1 SELECT * FROM d LIMIT 23; 198} 199 200#db eval { SELECT rowid AS r, quote(block) AS b FROM t1_data } { puts "$r: $b" } 201do_execsql_test 11.2 { 202 SELECT rowid FROM t1; 203} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23} 204 205do_execsql_test 11.3 { 206 SELECT rowid FROM t1('xyz'); 207} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23} 208 209do_execsql_test 11.4 { 210 INSERT INTO t1(t1) VALUES('integrity-check'); 211} 212 213#------------------------------------------------------------------------- 214# 215reset_db 216do_execsql_test 12.0 { 217 CREATE VIRTUAL TABLE yy USING fts5(x, detail=none); 218 INSERT INTO yy VALUES('in if'); 219 INSERT INTO yy VALUES('if'); 220} {} 221 222do_execsql_test 12.1 { 223 SELECT rowid FROM yy('i*'); 224} {1 2} 225 226#------------------------------------------------------------------------- 227# 228reset_db 229do_execsql_test 13.0 { 230 CREATE VIRTUAL TABLE t1 USING fts5(a, prefix=1, detail=none); 231} {} 232foreach {rowid a} { 233 0 {f} 234 1 {u} 235 2 {k} 236 3 {a} 237 4 {a} 238 5 {u} 239 6 {u} 240 7 {u} 241 8 {f} 242 9 {f} 243 10 {a} 244 11 {p} 245 12 {f} 246 13 {u} 247 14 {a} 248 15 {a} 249} { 250 do_execsql_test 13.1.$rowid { 251 INSERT INTO t1(rowid, a) VALUES($rowid, $a); 252 } 253} 254 255#------------------------------------------------------------------------- 256# 257reset_db 258fts5_aux_test_functions db 259do_execsql_test 14.0 { 260 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 261 INSERT INTO t1 VALUES('a b c d'); 262} {} 263 264do_execsql_test 14.1 { 265 SELECT fts5_test_poslist(t1) FROM t1('b') ORDER BY rank; 266} {0.0.1} 267 268#------------------------------------------------------------------------- 269# 270reset_db 271do_execsql_test 15.1 { 272 CREATE VIRTUAL TABLE t1 USING fts5(x, detail=none); 273 BEGIN; 274 INSERT INTO t1(rowid, x) VALUES(1, 'sqlite'); 275 INSERT INTO t1(rowid, x) VALUES(2, 'sqlite'); 276 COMMIT; 277} {} 278 279do_test 15.1 { 280 execsql { INSERT INTO t1(t1) VALUES('integrity-check') } 281} {} 282 283do_test 15.2 { 284 execsql { DELETE FROM t1 } 285} {} 286 287do_execsql_test 15.3.1 { 288 SELECT rowid FROM t1('sqlite'); 289} {} 290 291do_execsql_test 15.3.2 { 292 SELECT rowid FROM t1('sqlite') ORDER BY rowid DESC; 293} {} 294 295do_test 15.4 { 296 execsql { INSERT INTO t1(t1) VALUES('integrity-check') } 297} {} 298 299#------------------------------------------------------------------------- 300# 301reset_db 302do_execsql_test 16.0 { 303 CREATE VIRTUAL TABLE t2 USING fts5(x, detail=none); 304 BEGIN; 305 INSERT INTO t2(rowid, x) VALUES(1, 'a b c'); 306 INSERT INTO t2(rowid, x) VALUES(456, 'a b c'); 307 INSERT INTO t2(rowid, x) VALUES(1000, 'a b c'); 308 COMMIT; 309 UPDATE t2 SET x=x; 310} 311 312do_execsql_test 16.1 { 313 INSERT INTO t2(t2) VALUES('integrity-check'); 314} {} 315 316do_execsql_test 16.2 { 317 SELECT rowid FROM t2('b') ORDER BY rowid DESC 318} {1000 456 1} 319 320 321#------------------------------------------------------------------------- 322# 323reset_db 324do_execsql_test 16.0 { 325 CREATE VIRTUAL TABLE t2 USING fts5(x, detail=none); 326 BEGIN; 327 INSERT INTO t2(rowid, x) VALUES(1, 'a b c'); 328 INSERT INTO t2(rowid, x) VALUES(456, 'a b c'); 329 INSERT INTO t2(rowid, x) VALUES(1000, 'a b c'); 330 COMMIT; 331 UPDATE t2 SET x=x; 332 DELETE FROM t2; 333} 334 335#------------------------------------------------------------------------- 336# 337reset_db 338do_execsql_test 17.0 { 339 CREATE VIRTUAL TABLE t2 USING fts5(x, y); 340 BEGIN; 341 INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb'); 342 INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb'); 343 INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb'); 344 COMMIT; 345} 346do_execsql_test 17.1 { SELECT * FROM t2('y:a*') WHERE rowid BETWEEN 10 AND 20 } 347do_execsql_test 17.2 { 348 BEGIN; 349 INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb'); 350 SELECT * FROM t2('y:a*') WHERE rowid BETWEEN 10 AND 20 ; 351} 352do_execsql_test 17.3 { 353 COMMIT 354} 355 356reset_db 357do_execsql_test 17.4 { 358 CREATE VIRTUAL TABLE t2 USING fts5(x, y); 359 BEGIN; 360 INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb'); 361 INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb'); 362 SELECT * FROM t2('y:a*') WHERE rowid>66; 363} 364do_execsql_test 17.5 { SELECT * FROM t2('x:b* OR y:a*') } 365do_execsql_test 17.5 { COMMIT ; SELECT * FROM t2('x:b* OR y:a*') } 366do_execsql_test 17.6 { 367 SELECT * FROM t2('x:b* OR y:a*') WHERE rowid>55 368} 369 370#db eval {SELECT rowid, fts5_decode_none(rowid, block) aS r FROM t2_data} {puts $r} 371 372finish_test 373