| OLD | NEW |
|---|
| (Empty) |
| 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 fts5simple | |
| 15 | |
| 16 # If SQLITE_ENABLE_FTS5 is defined, omit this file. | |
| 17 ifcapable !fts5 { | |
| 18 finish_test | |
| 19 return | |
| 20 } | |
| 21 | |
| 22 #------------------------------------------------------------------------- | |
| 23 # | |
| 24 set doc "x x [string repeat {y } 50]z z" | |
| 25 do_execsql_test 1.0 { | |
| 26 CREATE VIRTUAL TABLE t1 USING fts5(x); | |
| 27 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); | |
| 28 BEGIN; | |
| 29 INSERT INTO t1 VALUES($doc); | |
| 30 COMMIT; | |
| 31 } | |
| 32 | |
| 33 do_execsql_test 1.1 { | |
| 34 INSERT INTO t1(t1) VALUES('integrity-check'); | |
| 35 } | |
| 36 | |
| 37 #------------------------------------------------------------------------- | |
| 38 # | |
| 39 reset_db | |
| 40 do_execsql_test 2.0 { | |
| 41 CREATE VIRTUAL TABLE t1 USING fts5(x); | |
| 42 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); | |
| 43 INSERT INTO t1 VALUES('a b c'); | |
| 44 INSERT INTO t1 VALUES('d e f'); | |
| 45 INSERT INTO t1(t1) VALUES('optimize'); | |
| 46 } | |
| 47 | |
| 48 do_execsql_test 2.1 { | |
| 49 INSERT INTO t1(t1) VALUES('integrity-check'); | |
| 50 } {} | |
| 51 | |
| 52 | |
| 53 #------------------------------------------------------------------------- | |
| 54 # | |
| 55 reset_db | |
| 56 do_execsql_test 3.0 { | |
| 57 CREATE VIRTUAL TABLE t1 USING fts5(x, prefix='1,2'); | |
| 58 INSERT INTO t1(t1, rank) VALUES('pgsz', 32); | |
| 59 BEGIN; | |
| 60 INSERT INTO t1 VALUES('one'); | |
| 61 SELECT * FROM t1 WHERE t1 MATCH 'o*'; | |
| 62 } {one} | |
| 63 | |
| 64 do_execsql_test 3.1 { | |
| 65 INSERT INTO t1(t1) VALUES('integrity-check'); | |
| 66 } {} | |
| 67 | |
| 68 #------------------------------------------------------------------------- | |
| 69 reset_db | |
| 70 do_execsql_test 4.1 { | |
| 71 CREATE VIRTUAL TABLE t11 USING fts5(content); | |
| 72 INSERT INTO t11(t11, rank) VALUES('pgsz', 32); | |
| 73 INSERT INTO t11 VALUES('another'); | |
| 74 INSERT INTO t11 VALUES('string'); | |
| 75 INSERT INTO t11 VALUES('of'); | |
| 76 INSERT INTO t11 VALUES('text'); | |
| 77 } | |
| 78 do_test 4.2 { | |
| 79 execsql { INSERT INTO t11(t11) VALUES('optimize') } | |
| 80 } {} | |
| 81 do_execsql_test 4.3 { | |
| 82 INSERT INTO t11(t11) VALUES('integrity-check'); | |
| 83 } {} | |
| 84 | |
| 85 #db eval { SELECT fts5_decode(rowid, block) as x FROM t11_data } { puts $x } | |
| 86 | |
| 87 #------------------------------------------------------------------------- | |
| 88 reset_db | |
| 89 set doc [string repeat "x y " 5] | |
| 90 do_execsql_test 5.1 { | |
| 91 CREATE VIRTUAL TABLE yy USING fts5(content); | |
| 92 INSERT INTO yy(yy, rank) VALUES('pgsz', 32); | |
| 93 BEGIN; | |
| 94 INSERT INTO yy VALUES($doc); | |
| 95 INSERT INTO yy VALUES($doc); | |
| 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 COMMIT; | |
| 103 } | |
| 104 | |
| 105 do_execsql_test 5.2 { | |
| 106 SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid ASC | |
| 107 } {1 2 3 4 5 6 7 8} | |
| 108 | |
| 109 do_execsql_test 5.3 { | |
| 110 SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid DESC | |
| 111 } {8 7 6 5 4 3 2 1} | |
| 112 | |
| 113 #db eval { SELECT fts5_decode(rowid, block) as x FROM yy_data } { puts $x } | |
| 114 | |
| 115 #------------------------------------------------------------------------- | |
| 116 reset_db | |
| 117 do_execsql_test 5.1 { | |
| 118 CREATE VIRTUAL TABLE tt USING fts5(content); | |
| 119 INSERT INTO tt(tt, rank) VALUES('pgsz', 32); | |
| 120 INSERT INTO tt VALUES('aa'); | |
| 121 } | |
| 122 | |
| 123 do_execsql_test 5.2 { | |
| 124 SELECT rowid FROM tt WHERE tt MATCH 'a*'; | |
| 125 } {1} | |
| 126 | |
| 127 do_execsql_test 5.3 { | |
| 128 DELETE FROM tt; | |
| 129 BEGIN; | |
| 130 INSERT INTO tt VALUES('aa'); | |
| 131 INSERT INTO tt VALUES('ab'); | |
| 132 COMMIT; | |
| 133 } {} | |
| 134 | |
| 135 do_execsql_test 5.4 { | |
| 136 SELECT rowid FROM tt WHERE tt MATCH 'a*'; | |
| 137 } {1 2} | |
| 138 | |
| 139 do_execsql_test 5.5 { | |
| 140 DELETE FROM tt; | |
| 141 BEGIN; | |
| 142 INSERT INTO tt VALUES('aa'); | |
| 143 INSERT INTO tt VALUES('ab'); | |
| 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 COMMIT; | |
| 151 SELECT rowid FROM tt WHERE tt MATCH 'a*'; | |
| 152 } {1 2 3 4 5 6 7 8} | |
| 153 | |
| 154 do_execsql_test 5.6 { | |
| 155 INSERT INTO tt(tt) VALUES('integrity-check'); | |
| 156 } | |
| 157 | |
| 158 reset_db | |
| 159 do_execsql_test 5.7 { | |
| 160 CREATE VIRTUAL TABLE tt USING fts5(content); | |
| 161 INSERT INTO tt(tt, rank) VALUES('pgsz', 32); | |
| 162 INSERT INTO tt VALUES('aa ab ac ad ae af'); | |
| 163 } | |
| 164 | |
| 165 do_execsql_test 5.8 { | |
| 166 SELECT rowid FROM tt WHERE tt MATCH 'a*'; | |
| 167 } {1} | |
| 168 | |
| 169 #------------------------------------------------------------------------- | |
| 170 | |
| 171 reset_db | |
| 172 do_execsql_test 6.1 { | |
| 173 CREATE VIRTUAL TABLE xyz USING fts5(x, y, z); | |
| 174 INSERT INTO xyz VALUES('x', 'y', 'z'); | |
| 175 } | |
| 176 | |
| 177 do_catchsql_test 6.2 { | |
| 178 SELECT * FROM xyz WHERE xyz MATCH '' | |
| 179 } {1 {fts5: syntax error near ""}} | |
| 180 do_catchsql_test 6.3 { | |
| 181 SELECT * FROM xyz WHERE xyz MATCH NULL | |
| 182 } {1 {fts5: syntax error near ""}} | |
| 183 | |
| 184 #------------------------------------------------------------------------- | |
| 185 | |
| 186 do_execsql_test 7.1 { | |
| 187 CREATE VIRTUAL TABLE ft2 USING fts5(content); | |
| 188 INSERT INTO ft2(rowid, content) VALUES(1, 'a b c'); | |
| 189 INSERT INTO ft2(rowid, content) VALUES(2, 'a b d'); | |
| 190 } | |
| 191 | |
| 192 do_catchsql_test 7.2 { | |
| 193 BEGIN; | |
| 194 UPDATE ft2 SET rowid=2 WHERE rowid=1; | |
| 195 } {1 {constraint failed}} | |
| 196 | |
| 197 do_execsql_test 7.3 { | |
| 198 COMMIT; | |
| 199 INSERT INTO ft2(ft2) VALUES('integrity-check'); | |
| 200 } {} | |
| 201 | |
| 202 do_execsql_test 7.4 { | |
| 203 SELECT * FROM ft2; | |
| 204 } {{a b c} {a b d}} | |
| 205 | |
| 206 #------------------------------------------------------------------------- | |
| 207 # | |
| 208 reset_db | |
| 209 do_execsql_test 8.1 { | |
| 210 CREATE VIRTUAL TABLE ft2 USING fts5(content); | |
| 211 INSERT INTO ft2(rowid, content) VALUES(1, 'a b'); | |
| 212 } | |
| 213 | |
| 214 do_execsql_test 8.2 { | |
| 215 BEGIN; | |
| 216 INSERT INTO ft2(rowid, content) VALUES(4, 'a x'); | |
| 217 } | |
| 218 | |
| 219 do_execsql_test 8.3 { | |
| 220 INSERT INTO ft2(ft2) VALUES('integrity-check'); | |
| 221 } | |
| 222 | |
| 223 #------------------------------------------------------------------------- | |
| 224 # Check that the "table function" syntax works. | |
| 225 # | |
| 226 reset_db | |
| 227 do_execsql_test 9.1 { | |
| 228 CREATE VIRTUAL TABLE ft2 USING fts5(content); | |
| 229 INSERT INTO ft2(rowid, content) VALUES(1, 'a b'); | |
| 230 INSERT INTO ft2(rowid, content) VALUES(2, 'a b c d'); | |
| 231 INSERT INTO ft2(rowid, content) VALUES(3, 'c d e f'); | |
| 232 } | |
| 233 | |
| 234 do_execsql_test 9.2 { | |
| 235 SELECT rowid FROM ft2('a'); | |
| 236 } {1 2} | |
| 237 | |
| 238 do_execsql_test 9.3 { | |
| 239 SELECT rowid FROM ft2('b AND c'); | |
| 240 } {2} | |
| 241 | |
| 242 #------------------------------------------------------------------------- | |
| 243 # | |
| 244 do_execsql_test 10.0 { | |
| 245 CREATE VIRTUAL TABLE t3 USING fts5(a, b, c); | |
| 246 INSERT INTO t3 VALUES('bac aab bab', 'c bac c', 'acb aba abb'); -- 1 | |
| 247 INSERT INTO t3 VALUES('bab abc c', 'acb c abb', 'c aaa c'); -- 2 | |
| 248 } | |
| 249 | |
| 250 do_execsql_test 10.1 { | |
| 251 SELECT rowid FROM t3('c: c*'); | |
| 252 } {2} | |
| 253 | |
| 254 do_execsql_test 10.2 { | |
| 255 SELECT rowid FROM t3('b: acb'); | |
| 256 } {2} | |
| 257 | |
| 258 #------------------------------------------------------------------------- | |
| 259 # Test that character 0x1A is allowed in fts5 barewords. | |
| 260 # | |
| 261 do_test 11.0 { | |
| 262 execsql "CREATE VIRTUAL TABLE t4 USING fts5(x, tokenize=\"ascii tokenchars '\x
1A'\")" | |
| 263 execsql " | |
| 264 INSERT INTO t4 VALUES('a b c \x1A'); | |
| 265 INSERT INTO t4 VALUES('a b c d\x1A'); | |
| 266 INSERT INTO t4 VALUES('a b c \x1Ad'); | |
| 267 INSERT INTO t4 VALUES('a b c d'); | |
| 268 " | |
| 269 } {} | |
| 270 | |
| 271 do_test 11.1 { | |
| 272 execsql "SELECT rowid FROM t4('\x1A')" | |
| 273 } {1} | |
| 274 do_test 11.2 { | |
| 275 execsql "SELECT rowid FROM t4('\x1A*')" | |
| 276 } {1 3} | |
| 277 do_test 11.3 { | |
| 278 execsql "SELECT rowid FROM t4('d\x1A')" | |
| 279 } {2} | |
| 280 | |
| 281 do_test 11.4 { | |
| 282 catchsql "SELECT rowid FROM t4('d\x1B')" | |
| 283 } {/fts5: syntax error/} | |
| 284 do_test 11.5 { | |
| 285 catchsql "SELECT rowid FROM t4('d\x19')" | |
| 286 } {/fts5: syntax error/} | |
| 287 | |
| 288 #------------------------------------------------------------------------- | |
| 289 # | |
| 290 do_test 12.1 { | |
| 291 execsql { | |
| 292 CREATE VIRTUAL TABLE xx USING fts5(x,y); | |
| 293 BEGIN; | |
| 294 INSERT INTO xx VALUES('1 2 3', 'a b c'); | |
| 295 } | |
| 296 } {} | |
| 297 | |
| 298 do_execsql_test 12.2 { | |
| 299 SELECT rowid FROM xx('x:a'); | |
| 300 COMMIT; | |
| 301 } {} | |
| 302 | |
| 303 #------------------------------------------------------------------------- | |
| 304 # Try an UPDATE OR REPLACE query. | |
| 305 # | |
| 306 do_execsql_test 13.1 { | |
| 307 CREATE VIRTUAL TABLE xy USING fts5(x); | |
| 308 INSERT INTO xy(rowid, x) VALUES(1, '1 2 3'); | |
| 309 INSERT INTO xy(rowid, x) VALUES(2, '2 3 4'); | |
| 310 INSERT INTO xy(rowid, x) VALUES(3, '3 4 5'); | |
| 311 } | |
| 312 | |
| 313 do_execsql_test 13.2 { | |
| 314 UPDATE OR REPLACE xy SET rowid=3 WHERE rowid = 2; | |
| 315 SELECT rowid, x FROM xy; | |
| 316 } { | |
| 317 1 {1 2 3} | |
| 318 3 {2 3 4} | |
| 319 } | |
| 320 | |
| 321 do_execsql_test 13.3 { | |
| 322 INSERT INTO xy(xy) VALUES('integrity-check'); | |
| 323 } | |
| 324 | |
| 325 #------------------------------------------------------------------------- | |
| 326 # | |
| 327 do_execsql_test 14.1 { | |
| 328 CREATE VIRTUAL TABLE ttt USING fts5(x); | |
| 329 BEGIN; | |
| 330 INSERT INTO ttt(rowid, x) VALUES(1, 'a b c'); | |
| 331 INSERT INTO ttt(rowid, x) VALUES(2, 'a b c'); | |
| 332 INSERT INTO ttt(rowid, x) VALUES(3, 'a b c'); | |
| 333 COMMIT; | |
| 334 } | |
| 335 do_test 14.2 { | |
| 336 fts5_level_segs ttt | |
| 337 } {1} | |
| 338 | |
| 339 #------------------------------------------------------------------------- | |
| 340 db func rnddoc fts5_rnddoc | |
| 341 do_execsql_test 4.0 { | |
| 342 CREATE VIRTUAL TABLE x1 USING fts5(x); | |
| 343 INSERT INTO x1(x1, rank) VALUES('pgsz', 32); | |
| 344 | |
| 345 WITH ii(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM ii WHERE i<10 ) | |
| 346 INSERT INTO x1 SELECT rnddoc(5) FROM ii; | |
| 347 } | |
| 348 | |
| 349 do_execsql_test 4.1 { | |
| 350 SELECT rowid, x, x1 FROM x1 WHERE x1 MATCH '*reads' | |
| 351 } {0 {} 4} | |
| 352 | |
| 353 | |
| 354 finish_test | |
| 355 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
