Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Change fts4aux to (additionally) report on term frequency in individual columns of an fts table. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3996f92a9aca9ac2628be003eca83c8f |
User & Date: | dan 2011-02-04 18:56:25.115 |
Context
2011-02-05
| ||
14:37 | Add test cases for fts4aux. Fix a bug affecting fts3 tables with multiple columns. (check-in: dc511e60a6 user: dan tags: trunk) | |
2011-02-04
| ||
18:56 | Change fts4aux to (additionally) report on term frequency in individual columns of an fts table. (check-in: 3996f92a9a user: dan tags: trunk) | |
14:28 | Significant changes have occurred since the 3.7.5 release so go ahead and increase the version number to 3.7.6. (check-in: 999c915a4a user: drh tags: trunk) | |
Changes
Changes to ext/fts3/fts3_aux.c.
︙ | ︙ | |||
27 28 29 30 31 32 33 | }; struct Fts3auxCursor { sqlite3_vtab_cursor base; /* Base class used by SQLite core */ Fts3SegReaderCursor csr; /* Must be right after "base" */ Fts3SegFilter filter; char *zStop; | | | | > > > > | | > | | 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | }; struct Fts3auxCursor { sqlite3_vtab_cursor base; /* Base class used by SQLite core */ Fts3SegReaderCursor csr; /* Must be right after "base" */ Fts3SegFilter filter; char *zStop; int nStop; /* Byte-length of string zStop */ int isEof; /* True if cursor is at EOF */ sqlite3_int64 iRowid; /* Current rowid */ int iCol; /* Current value of 'col' column */ int nStat; /* Size of aStat[] array */ struct Fts3auxColstats { sqlite3_int64 nDoc; /* 'documents' values for current csr row */ sqlite3_int64 nOcc; /* 'occurrences' values for current csr row */ } *aStat; }; /* ** Schema of the terms table. */ #define FTS3_TERMS_SCHEMA "CREATE TABLE x(term, col, documents, occurrences)" /* ** This function does all the work for both the xConnect and xCreate methods. ** These tables have no persistent representation of their own, so xConnect ** and xCreate are identical operations. */ static int fts3auxConnectMethod( |
︙ | ︙ | |||
195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab; Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor; sqlite3Fts3SegmentsClose(pFts3); sqlite3Fts3SegReaderFinish(&pCsr->csr); sqlite3_free((void *)pCsr->filter.zTerm); sqlite3_free(pCsr->zStop); sqlite3_free(pCsr); return SQLITE_OK; } /* ** xNext - Advance the cursor to the next row, if any. */ static int fts3auxNextMethod(sqlite3_vtab_cursor *pCursor){ Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor; Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab; int rc; rc = sqlite3Fts3SegReaderStep(pFts3, &pCsr->csr); if( rc==SQLITE_ROW ){ | > > > > > > > > > > > > > > > > > > > > > > > > | < > > > < < < | | | > > > > | > > | > > > > > > > > > > > > | | > > > | > > > > > > > > > > | > > > > > | > > < | 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 | Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab; Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor; sqlite3Fts3SegmentsClose(pFts3); sqlite3Fts3SegReaderFinish(&pCsr->csr); sqlite3_free((void *)pCsr->filter.zTerm); sqlite3_free(pCsr->zStop); sqlite3_free(pCsr->aStat); sqlite3_free(pCsr); return SQLITE_OK; } static int fts3auxGrowStatArray(Fts3auxCursor *pCsr, int nSize){ if( nSize>pCsr->nStat ){ struct Fts3auxColstats *aNew; aNew = (struct Fts3auxColstats *)sqlite3_realloc(pCsr->aStat, sizeof(struct Fts3auxColstats) * nSize ); if( aNew==0 ) return SQLITE_NOMEM; memset(&aNew[pCsr->nStat], 0, sizeof(struct Fts3auxColstats) * (nSize - pCsr->nStat) ); pCsr->aStat = aNew; pCsr->nStat = nSize; } return SQLITE_OK; } /* ** xNext - Advance the cursor to the next row, if any. */ static int fts3auxNextMethod(sqlite3_vtab_cursor *pCursor){ Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor; Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab; int rc; /* Increment our pretend rowid value. */ pCsr->iRowid++; for(pCsr->iCol++; pCsr->iCol<pCsr->nStat; pCsr->iCol++){ if( pCsr->aStat[pCsr->iCol].nDoc>0 ) return SQLITE_OK; } rc = sqlite3Fts3SegReaderStep(pFts3, &pCsr->csr); if( rc==SQLITE_ROW ){ int i = 0; int nDoclist = pCsr->csr.nDoclist; char *aDoclist = pCsr->csr.aDoclist; int iCol; int eState = 0; if( pCsr->zStop ){ int n = (pCsr->nStop<pCsr->csr.nTerm) ? pCsr->nStop : pCsr->csr.nTerm; int mc = memcmp(pCsr->zStop, pCsr->csr.zTerm, n); if( mc<0 || (mc==0 && pCsr->csr.nTerm>pCsr->nStop) ){ pCsr->isEof = 1; return SQLITE_OK; } } if( fts3auxGrowStatArray(pCsr, 2) ) return SQLITE_NOMEM; memset(pCsr->aStat, 0, sizeof(struct Fts3auxColstats) * pCsr->nStat); iCol = 0; while( i<nDoclist ){ sqlite3_int64 v = 0; i += sqlite3Fts3GetVarint(&aDoclist[i], &v); switch( eState ){ /* State 0. In this state the integer just read was a docid. */ case 0: pCsr->aStat[0].nDoc++; eState = 1; iCol = 0; break; /* State 1. In this state we are expecting either a 1, indicating ** that the following integer will be a column number, or the ** start of a position list for column 0. ** ** The only difference between state 1 and state 2 is that if the ** integer encountered in state 1 is not 0 or 1, then we need to ** increment the column 0 "nDoc" count for this term. */ case 1: assert( iCol==0 ); if( v>1 ){ pCsr->aStat[1].nDoc++; } eState = 2; /* fall through */ case 2: if( v==0 ){ /* 0x00. Next integer will be a docid. */ eState = 0; }else if( v==1 ){ /* 0x01. Next integer will be a column number. */ eState = 3; }else{ /* 2 or greater. A position. */ pCsr->aStat[iCol+1].nOcc++; pCsr->aStat[0].nOcc++; } break; /* State 3. The integer just read is a column number. */ case 3: iCol = (int)v; if( fts3auxGrowStatArray(pCsr, iCol+2) ) return SQLITE_NOMEM; pCsr->aStat[iCol+1].nDoc++; eState = 2; break; } } pCsr->iCol = 0; rc = SQLITE_OK; }else{ pCsr->isEof = 1; } return rc; } /* |
︙ | ︙ | |||
276 277 278 279 280 281 282 283 284 285 286 287 288 289 | ); isScan = (idxNum!=FTS4AUX_EQ_CONSTRAINT); /* In case this cursor is being reused, close and zero it. */ testcase(pCsr->filter.zTerm); sqlite3Fts3SegReaderFinish(&pCsr->csr); sqlite3_free((void *)pCsr->filter.zTerm); memset(&pCsr->csr, 0, ((u8*)&pCsr[1]) - (u8*)&pCsr->csr); pCsr->filter.flags = FTS3_SEGMENT_REQUIRE_POS|FTS3_SEGMENT_IGNORE_EMPTY; if( isScan ) pCsr->filter.flags |= FTS3_SEGMENT_SCAN; if( idxNum&(FTS4AUX_EQ_CONSTRAINT|FTS4AUX_GE_CONSTRAINT) ){ const unsigned char *zStr = sqlite3_value_text(apVal[0]); | > | 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 | ); isScan = (idxNum!=FTS4AUX_EQ_CONSTRAINT); /* In case this cursor is being reused, close and zero it. */ testcase(pCsr->filter.zTerm); sqlite3Fts3SegReaderFinish(&pCsr->csr); sqlite3_free((void *)pCsr->filter.zTerm); sqlite3_free(pCsr->aStat); memset(&pCsr->csr, 0, ((u8*)&pCsr[1]) - (u8*)&pCsr->csr); pCsr->filter.flags = FTS3_SEGMENT_REQUIRE_POS|FTS3_SEGMENT_IGNORE_EMPTY; if( isScan ) pCsr->filter.flags |= FTS3_SEGMENT_SCAN; if( idxNum&(FTS4AUX_EQ_CONSTRAINT|FTS4AUX_GE_CONSTRAINT) ){ const unsigned char *zStr = sqlite3_value_text(apVal[0]); |
︙ | ︙ | |||
328 329 330 331 332 333 334 | int iCol /* Index of column to read value from */ ){ Fts3auxCursor *p = (Fts3auxCursor *)pCursor; assert( p->isEof==0 ); if( iCol==0 ){ /* Column "term" */ sqlite3_result_text(pContext, p->csr.zTerm, p->csr.nTerm, SQLITE_TRANSIENT); | | > > > > > > | | | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 | int iCol /* Index of column to read value from */ ){ Fts3auxCursor *p = (Fts3auxCursor *)pCursor; assert( p->isEof==0 ); if( iCol==0 ){ /* Column "term" */ sqlite3_result_text(pContext, p->csr.zTerm, p->csr.nTerm, SQLITE_TRANSIENT); }else if( iCol==1 ){ /* Column "col" */ if( p->iCol ){ sqlite3_result_int(pContext, p->iCol-1); }else{ sqlite3_result_text(pContext, "*", -1, SQLITE_STATIC); } }else if( iCol==2 ){ /* Column "documents" */ sqlite3_result_int64(pContext, p->aStat[p->iCol].nDoc); }else{ /* Column "occurrences" */ sqlite3_result_int64(pContext, p->aStat[p->iCol].nOcc); } return SQLITE_OK; } /* ** xRowid - Return the current rowid for the cursor. |
︙ | ︙ |
Changes to ext/fts3/fts3_write.c.
︙ | ︙ | |||
2219 2220 2221 2222 2223 2224 2225 | ** If this function is called with iLevel<0, but there is only one ** segment in the database, SQLITE_DONE is returned immediately. ** Otherwise, if successful, SQLITE_OK is returned. If an error occurs, ** an SQLite error code is returned. */ static int fts3SegmentMerge(Fts3Table *p, int iLevel){ int rc; /* Return code */ | | < | 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 | ** If this function is called with iLevel<0, but there is only one ** segment in the database, SQLITE_DONE is returned immediately. ** Otherwise, if successful, SQLITE_OK is returned. If an error occurs, ** an SQLite error code is returned. */ static int fts3SegmentMerge(Fts3Table *p, int iLevel){ int rc; /* Return code */ int iIdx = 0; /* Index of new segment */ int iNewLevel = 0; /* Level to create new segment at */ SegmentWriter *pWriter = 0; /* Used to write the new, merged, segment */ Fts3SegFilter filter; /* Segment term filter condition */ Fts3SegReaderCursor csr; /* Cursor to iterate through level(s) */ rc = sqlite3Fts3SegReaderCursor(p, iLevel, 0, 0, 1, 0, &csr); if( rc!=SQLITE_OK || csr.nSegment==0 ) goto finished; if( iLevel==FTS3_SEGCURSOR_ALL ){ /* This call is to merge all segments in the database to a single ** segment. The level of the new segment is equal to the the numerically ** greatest segment level currently present in the database. The index ** of the new segment is always 0. */ int nDummy; /* TODO: Remove this */ if( csr.nSegment==1 ){ rc = SQLITE_DONE; goto finished; } rc = fts3SegmentCountMax(p, &nDummy, &iNewLevel); }else{ /* This call is to merge all segments at level iLevel. Find the next ** available segment index at level iLevel+1. The call to ** fts3AllocateSegdirIdx() will merge the segments at level iLevel+1 to ** a single iLevel+2 segment if necessary. */ iNewLevel = iLevel+1; |
︙ | ︙ |
Changes to test/fts3aux1.test.
︙ | ︙ | |||
20 21 22 23 24 25 26 | do_execsql_test 1.1 { CREATE VIRTUAL TABLE t1 USING fts4; INSERT INTO t1 VALUES('one two three four'); INSERT INTO t1 VALUES('three four five six'); INSERT INTO t1 VALUES('one three five seven'); CREATE VIRTUAL TABLE terms USING fts4aux(t1); | | | | | | 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | do_execsql_test 1.1 { CREATE VIRTUAL TABLE t1 USING fts4; INSERT INTO t1 VALUES('one two three four'); INSERT INTO t1 VALUES('three four five six'); INSERT INTO t1 VALUES('one three five seven'); CREATE VIRTUAL TABLE terms USING fts4aux(t1); SELECT term, documents, occurrences FROM terms WHERE col = '*'; } { five 2 2 four 2 2 one 2 2 seven 1 1 six 1 1 three 3 3 two 1 1 } do_execsql_test 1.2 { INSERT INTO t1 VALUES('one one one three three three'); SELECT term, documents, occurrences FROM terms WHERE col = '*'; } { five 2 2 four 2 2 one 3 5 seven 1 1 six 1 1 three 4 6 two 1 1 } do_execsql_test 1.3 { DELETE FROM t1; SELECT term, documents, occurrences FROM terms WHERE col = '*'; } {} do_execsql_test 1.4 { INSERT INTO t1 VALUES('a b a b a b a'); INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; SELECT term, documents, occurrences FROM terms WHERE col = '*'; } {a 256 1024 b 256 768} #------------------------------------------------------------------------- # The following tests verify that the fts4aux module uses the full-text # index to reduce the number of rows scanned in the following circumstances: # # * when there is equality comparison against the term column using the |
︙ | ︙ | |||
80 81 82 83 84 85 86 87 88 89 90 91 92 93 | DROP TABLE t1; DROP TABLE terms; CREATE VIRTUAL TABLE x1 USING fts4(x); INSERT INTO x1(x1) VALUES('nodesize=24'); CREATE VIRTUAL TABLE terms USING fts4aux(x1); INSERT INTO x1 VALUES('braes brag bragged bragger bragging'); INSERT INTO x1 VALUES('brags braid braided braiding braids'); INSERT INTO x1 VALUES('brain brainchild brained braining brains'); INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); } proc rec {varname x} { | > > > | 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | DROP TABLE t1; DROP TABLE terms; CREATE VIRTUAL TABLE x1 USING fts4(x); INSERT INTO x1(x1) VALUES('nodesize=24'); CREATE VIRTUAL TABLE terms USING fts4aux(x1); CREATE VIEW terms_v AS SELECT term, documents, occurrences FROM terms WHERE col = '*'; INSERT INTO x1 VALUES('braes brag bragged bragger bragging'); INSERT INTO x1 VALUES('brags braid braided braiding braids'); INSERT INTO x1 VALUES('brain brainchild brained braining brains'); INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); } proc rec {varname x} { |
︙ | ︙ | |||
108 109 110 111 112 113 114 | } {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}} # Now show that using "term='braid'" means the virtual table returns # only 1 row to SQLite, but "+term='braid'" means all 19 are returned. # do_test 2.1.2.1 { set cnt 0 | | | | | | | | | | | | | | | | | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | } {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}} # Now show that using "term='braid'" means the virtual table returns # only 1 row to SQLite, but "+term='braid'" means all 19 are returned. # do_test 2.1.2.1 { set cnt 0 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' } set cnt } {2} do_test 2.1.2.2 { set cnt 0 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' } set cnt } {38} # Similar to the test immediately above, but using a term ("breakfast") that # is not featured in the dataset. # do_test 2.1.3.1 { set cnt 0 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' } set cnt } {0} do_test 2.1.3.2 { set cnt 0 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' } set cnt } {38} do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1} do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1} do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {} do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {} do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {} do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {} do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {} do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {} # Special case: term=NULL # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' |
︙ | ︙ | |||
170 171 172 173 174 175 176 | EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} } do_test 2.2.2.1 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } set cnt | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 | EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} } do_test 2.2.2.1 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } set cnt } {18} do_test 2.2.2.2 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' } set cnt } {38} do_execsql_test 2.2.2.3 { SELECT term, documents, occurrences FROM terms_v WHERE term>'brain' } { brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 } do_execsql_test 2.2.2.4 { SELECT term, documents, occurrences FROM terms_v WHERE +term>'brain' } { brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 } do_execsql_test 2.2.2.5 { SELECT term, documents, occurrences FROM terms_v WHERE term>='brain' } { brain 1 1 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 } do_execsql_test 2.2.2.6 { SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain' } { brain 1 1 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 } do_execsql_test 2.2.2.7 { SELECT term, documents, occurrences FROM terms_v WHERE term>='abc' } { braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 } do_execsql_test 2.2.2.8 { SELECT term, documents, occurrences FROM terms_v WHERE +term>='abc' } { braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 } do_execsql_test 2.2.2.9 { SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms' } {brainstorms 1 1} do_execsql_test 2.2.2.10 { SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms' } {brainstorms 1 1} do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {} do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {} do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {} do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {} do_test 2.2.3.1 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' } set cnt } {22} do_test 2.2.3.2 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' } set cnt } {38} do_execsql_test 2.2.3.3 { SELECT term, documents, occurrences FROM terms_v WHERE term<'brain' } { braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 } do_execsql_test 2.2.3.4 { SELECT term, documents, occurrences FROM terms_v WHERE +term<'brain' } { braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 } do_execsql_test 2.2.3.5 { SELECT term, documents, occurrences FROM terms_v WHERE term<='brain' } { braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 } do_execsql_test 2.2.3.6 { SELECT term, documents, occurrences FROM terms_v WHERE +term<='brain' } { braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 } do_test 2.2.4.1 { set cnt 0 execsql { SELECT term, documents, occurrences FROM terms WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' } set cnt } {12} do_test 2.2.4.2 { set cnt 0 execsql { SELECT term, documents, occurrences FROM terms WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' } set cnt } {38} do_execsql_test 2.2.4.3 { SELECT term, documents, occurrences FROM terms_v WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' } { brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 } do_execsql_test 2.2.4.4 { SELECT term, documents, occurrences FROM terms_v WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' } { brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 } do_execsql_test 2.2.4.5 { SELECT term, documents, occurrences FROM terms_v WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain' } { braid 1 1 braided 1 1 braiding 1 1 braids 1 1 } do_execsql_test 2.2.4.6 { SELECT term, documents, occurrences FROM terms_v WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' } { braid 1 1 braided 1 1 braiding 1 1 braids 1 1 } # Check that "ORDER BY term ASC" and equivalents are sorted by the # virtual table implementation. Any other ORDER BY clause requires |
︙ | ︙ | |||
389 390 391 392 393 394 395 | CREATE TABLE x3(y); CREATE INDEX i1 ON x3(y); INSERT INTO x1 VALUES('a b c d e'); INSERT INTO x1 VALUES('f g h i j'); INSERT INTO x1 VALUES('k k l l a'); | | | | | | | | 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 | CREATE TABLE x3(y); CREATE INDEX i1 ON x3(y); INSERT INTO x1 VALUES('a b c d e'); INSERT INTO x1 VALUES('f g h i j'); INSERT INTO x1 VALUES('k k l l a'); INSERT INTO x2 SELECT term FROM terms WHERE col = '*'; INSERT INTO x3 SELECT term FROM terms WHERE col = '*'; } proc do_plansql_test {tn sql r} { uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r] } do_plansql_test 4.2 { SELECT y FROM x2, terms WHERE y = term AND col = '*' } { 0 0 0 {SCAN TABLE x2 (~1000000 rows)} 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} a b c d e f g h i j k l } do_plansql_test 4.3 { SELECT y FROM terms, x2 WHERE y = term AND col = '*' } { 0 0 1 {SCAN TABLE x2 (~1000000 rows)} 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} a b c d e f g h i j k l } do_plansql_test 4.4 { SELECT y FROM x3, terms WHERE y = term AND col = '*' } { 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} a b c d e f g h i j k l } do_plansql_test 4.5 { SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} a k l } finish_test |
Changes to test/fts3comp1.test.
︙ | ︙ | |||
84 85 86 87 88 89 90 | } {{one two three} {two four six}} # Delete a row and check that the full-text index is correctly updated. # Inspect the full-text index using an fts4aux table. # do_execsql_test 1.$tn.7 { CREATE VIRTUAL TABLE terms USING fts4aux(t1); | | | | 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | } {{one two three} {two four six}} # Delete a row and check that the full-text index is correctly updated. # Inspect the full-text index using an fts4aux table. # do_execsql_test 1.$tn.7 { CREATE VIRTUAL TABLE terms USING fts4aux(t1); SELECT term, documents, occurrences FROM terms WHERE col = '*'; } { eight 1 1 four 2 2 nine 1 1 one 1 1 six 2 2 three 2 2 twelve 1 1 two 1 2 } do_execsql_test 1.$tn.8 { DELETE FROM t1 WHERE docid = 1; SELECT term, documents, occurrences FROM terms WHERE col = '*'; } { eight 1 1 four 1 1 nine 1 1 six 1 1 three 1 1 twelve 1 1 } do_execsql_test 1.$tn.9 { SELECT c0a, c1b FROM t1_content } {3 4} } |
︙ | ︙ |