modernc.org/cc@v1.0.1/v2/testdata/_sqlite/ext/fts5/test/fts5simple2.test (about) 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 13 source [file join [file dirname [info script]] fts5_common.tcl] 14 set testprefix fts5simple2 15 16 # If SQLITE_ENABLE_FTS5 is defined, omit this file. 17 ifcapable !fts5 { 18 finish_test 19 return 20 } 21 22 do_execsql_test 1.0 { 23 CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none); 24 INSERT INTO t1 VALUES('a b c'); 25 } 26 do_execsql_test 1.1 { 27 SELECT rowid FROM t1('c a b') 28 } {1} 29 30 #------------------------------------------------------------------------- 31 # 32 reset_db 33 do_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 } 40 do_execsql_test 2.1 { 41 SELECT rowid FROM t1('b c d') 42 } {1 2} 43 44 #------------------------------------------------------------------------- 45 # 46 reset_db 47 do_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 } 53 do_execsql_test 3.1 { 54 SELECT rowid FROM t1('b c d'); COMMIT; 55 } {1 2} 56 57 #------------------------------------------------------------------------- 58 # 59 reset_db 60 do_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 } 68 do_execsql_test 4.1 { 69 SELECT rowid FROM t1('b*'); 70 } {1 2 3} 71 72 73 #------------------------------------------------------------------------- 74 # 75 reset_db 76 do_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 } 84 do_execsql_test 5.1 { SELECT rowid FROM t1('b*') } {1 2 3} 85 86 #------------------------------------------------------------------------- 87 # 88 reset_db 89 do_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 98 do_execsql_test 6.1 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {3 2 1} 99 do_execsql_test 6.2 { SELECT rowid FROM t1('b1') ORDER BY rowid DESC } {3 2 1} 100 do_execsql_test 6.3 { SELECT rowid FROM t1('c1') ORDER BY rowid DESC } {3 2 1} 101 102 #------------------------------------------------------------------------- 103 # 104 reset_db 105 do_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 } 112 do_execsql_test 7.1 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC } {2 1} 113 do_execsql_test 7.2 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {2 1} 114 115 #------------------------------------------------------------------------- 116 # 117 reset_db 118 do_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 } 124 do_execsql_test 8.0.1 { SELECT rowid FROM t1('b*') } {1 2 3} 125 do_execsql_test 8.0.2 { SELECT rowid FROM t1('a1') } {1 3} 126 do_execsql_test 8.0.3 { SELECT rowid FROM t1('c2') } {2} 127 128 do_execsql_test 8.0.4 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC } {3 2 1} 129 do_execsql_test 8.0.5 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {3 1} 130 do_execsql_test 8.0.8 { SELECT rowid FROM t1('c2') ORDER BY rowid DESC } {2} 131 132 do_execsql_test 8.1.0 { INSERT INTO t1(t1) VALUES('optimize') } 133 134 do_execsql_test 8.1.1 { SELECT rowid FROM t1('b*') } {1 2 3} 135 do_execsql_test 8.1.2 { SELECT rowid FROM t1('a1') } {1 3} 136 do_execsql_test 8.1.3 { SELECT rowid FROM t1('c2') } {2} 137 138 do_execsql_test 8.2.1 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC} {3 2 1} 139 do_execsql_test 8.2.2 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC} {3 1} 140 do_execsql_test 8.2.3 { SELECT rowid FROM t1('c2') ORDER BY rowid DESC} {2} 141 142 #-------------------------------------------------------------------------- 143 # 144 reset_db 145 do_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 } 151 do_execsql_test 9.0.1 { 152 INSERT INTO t1(t1) VALUES('integrity-check'); 153 } {} 154 155 reset_db 156 do_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 } 162 do_execsql_test 9.2.1 { 163 INSERT INTO t1(t1) VALUES('integrity-check'); 164 } {} 165 166 #-------------------------------------------------------------------------- 167 # 168 reset_db 169 do_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 176 do_execsql_test 10.1 { 177 SELECT rowid FROM t1('b1'); 178 } {2} 179 180 do_execsql_test 10.2 { 181 SELECT rowid FROM t1('b1') ORDER BY rowid DESC; 182 } {2} 183 184 do_execsql_test 10.3 { 185 INSERT INTO t1(t1) VALUES('integrity-check'); 186 } {} 187 188 #-------------------------------------------------------------------------- 189 # 190 reset_db 191 do_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" } 201 do_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 205 do_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 209 do_execsql_test 11.4 { 210 INSERT INTO t1(t1) VALUES('integrity-check'); 211 } 212 213 #------------------------------------------------------------------------- 214 # 215 reset_db 216 do_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 222 do_execsql_test 12.1 { 223 SELECT rowid FROM yy('i*'); 224 } {1 2} 225 226 #------------------------------------------------------------------------- 227 # 228 reset_db 229 do_execsql_test 13.0 { 230 CREATE VIRTUAL TABLE t1 USING fts5(a, prefix=1, detail=none); 231 } {} 232 foreach {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 # 257 reset_db 258 fts5_aux_test_functions db 259 do_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 264 do_execsql_test 14.1 { 265 SELECT fts5_test_poslist(t1) FROM t1('b') ORDER BY rank; 266 } {0.0.1} 267 268 #------------------------------------------------------------------------- 269 # 270 reset_db 271 do_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 279 do_test 15.1 { 280 execsql { INSERT INTO t1(t1) VALUES('integrity-check') } 281 } {} 282 283 do_test 15.2 { 284 execsql { DELETE FROM t1 } 285 } {} 286 287 do_execsql_test 15.3.1 { 288 SELECT rowid FROM t1('sqlite'); 289 } {} 290 291 do_execsql_test 15.3.2 { 292 SELECT rowid FROM t1('sqlite') ORDER BY rowid DESC; 293 } {} 294 295 do_test 15.4 { 296 execsql { INSERT INTO t1(t1) VALUES('integrity-check') } 297 } {} 298 299 #------------------------------------------------------------------------- 300 # 301 reset_db 302 do_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 312 do_execsql_test 16.1 { 313 INSERT INTO t2(t2) VALUES('integrity-check'); 314 } {} 315 316 do_execsql_test 16.2 { 317 SELECT rowid FROM t2('b') ORDER BY rowid DESC 318 } {1000 456 1} 319 320 321 #------------------------------------------------------------------------- 322 # 323 reset_db 324 do_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 # 337 reset_db 338 do_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 } 346 do_execsql_test 17.1 { SELECT * FROM t2('y:a*') WHERE rowid BETWEEN 10 AND 20 } 347 do_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 } 352 do_execsql_test 17.3 { 353 COMMIT 354 } 355 356 reset_db 357 do_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 } 364 do_execsql_test 17.5 { SELECT * FROM t2('x:b* OR y:a*') } 365 do_execsql_test 17.5 { COMMIT ; SELECT * FROM t2('x:b* OR y:a*') } 366 do_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 372 finish_test