Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Update fts5 to avoid using a statement journal for UPDATE and DELETE operations that affect at most a single row. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
5c83b9db46d61cfa76a1abed50467e2f |
User & Date: | dan 2015-10-02 20:04:30.384 |
Context
2015-10-03
| ||
12:23 | Add tests for the rtree module to verify that attempts to insert non-integer primary key values or non-numeric dimensions into an rtree table are handled correctly. (check-in: f653fce908 user: dan tags: trunk) | |
2015-10-02
| ||
20:04 | Update fts5 to avoid using a statement journal for UPDATE and DELETE operations that affect at most a single row. (check-in: 5c83b9db46 user: dan tags: trunk) | |
2015-10-01
| ||
18:31 | Fix an fts3 bug causing NEAR queries on uncommitted data to malfunction. (check-in: 6f90839e91 user: dan tags: trunk) | |
Changes
Changes to ext/fts5/fts5Int.h.
︙ | ︙ | |||
366 367 368 369 370 371 372 373 374 375 376 377 378 379 | /* ** Indicate that subsequent calls to sqlite3Fts5IndexWrite() pertain to ** document iDocid. */ int sqlite3Fts5IndexBeginWrite( Fts5Index *p, /* Index to write to */ i64 iDocid /* Docid to add or remove data from */ ); /* ** Flush any data stored in the in-memory hash tables to the database. ** If the bCommit flag is true, also close any open blob handles. */ | > | 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 | /* ** Indicate that subsequent calls to sqlite3Fts5IndexWrite() pertain to ** document iDocid. */ int sqlite3Fts5IndexBeginWrite( Fts5Index *p, /* Index to write to */ int bDelete, /* True if current operation is a delete */ i64 iDocid /* Docid to add or remove data from */ ); /* ** Flush any data stored in the in-memory hash tables to the database. ** If the bCommit flag is true, also close any open blob handles. */ |
︙ | ︙ | |||
522 523 524 525 526 527 528 | int sqlite3Fts5StorageClose(Fts5Storage *p); int sqlite3Fts5StorageRename(Fts5Storage*, const char *zName); int sqlite3Fts5DropAll(Fts5Config*); int sqlite3Fts5CreateTable(Fts5Config*, const char*, const char*, int, char **); int sqlite3Fts5StorageDelete(Fts5Storage *p, i64); | | > | 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 | int sqlite3Fts5StorageClose(Fts5Storage *p); int sqlite3Fts5StorageRename(Fts5Storage*, const char *zName); int sqlite3Fts5DropAll(Fts5Config*); int sqlite3Fts5CreateTable(Fts5Config*, const char*, const char*, int, char **); int sqlite3Fts5StorageDelete(Fts5Storage *p, i64); int sqlite3Fts5StorageContentInsert(Fts5Storage *p, sqlite3_value**, i64*); int sqlite3Fts5StorageIndexInsert(Fts5Storage *p, sqlite3_value**, i64); int sqlite3Fts5StorageIntegrity(Fts5Storage *p); int sqlite3Fts5StorageStmt(Fts5Storage *p, int eStmt, sqlite3_stmt**, char**); void sqlite3Fts5StorageStmtRelease(Fts5Storage *p, int eStmt, sqlite3_stmt*); int sqlite3Fts5StorageDocsize(Fts5Storage *p, i64 iRowid, int *aCol); |
︙ | ︙ |
Changes to ext/fts5/fts5_index.c.
︙ | ︙ | |||
287 288 289 290 291 292 293 | ** Variables related to the accumulation of tokens and doclists within the ** in-memory hash tables before they are flushed to disk. */ Fts5Hash *pHash; /* Hash table for in-memory data */ int nMaxPendingData; /* Max pending data before flush to disk */ int nPendingData; /* Current bytes of pending data */ i64 iWriteRowid; /* Rowid for current doc being written */ | | | 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 | ** Variables related to the accumulation of tokens and doclists within the ** in-memory hash tables before they are flushed to disk. */ Fts5Hash *pHash; /* Hash table for in-memory data */ int nMaxPendingData; /* Max pending data before flush to disk */ int nPendingData; /* Current bytes of pending data */ i64 iWriteRowid; /* Rowid for current doc being written */ int bDelete; /* Current write is a delete */ /* Error state. */ int rc; /* Current error code */ /* State used by the fts5DataXXX() functions. */ sqlite3_blob *pReader; /* RO incr-blob open on %_data table */ sqlite3_stmt *pWriter; /* "INSERT ... %_data VALUES(?,?)" */ |
︙ | ︙ | |||
1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 | }else{ pIter->pLeaf->p = (u8*)pList; pIter->pLeaf->nn = nList; pIter->pLeaf->szLeaf = nList; pIter->iEndofDoclist = nList+1; sqlite3Fts5BufferSet(&p->rc, &pIter->term, strlen(zTerm), (u8*)zTerm); pIter->iLeafOffset = fts5GetVarint(pList, (u64*)&pIter->iRowid); } }else{ iOff = 0; /* Next entry is not on the current page */ while( iOff==0 ){ fts5SegIterNextPage(p, pIter); pLeaf = pIter->pLeaf; | > | 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 | }else{ pIter->pLeaf->p = (u8*)pList; pIter->pLeaf->nn = nList; pIter->pLeaf->szLeaf = nList; pIter->iEndofDoclist = nList+1; sqlite3Fts5BufferSet(&p->rc, &pIter->term, strlen(zTerm), (u8*)zTerm); pIter->iLeafOffset = fts5GetVarint(pList, (u64*)&pIter->iRowid); if( pbNewTerm ) *pbNewTerm = 1; } }else{ iOff = 0; /* Next entry is not on the current page */ while( iOff==0 ){ fts5SegIterNextPage(p, pIter); pLeaf = pIter->pLeaf; |
︙ | ︙ | |||
4191 4192 4193 4194 4195 4196 4197 | } /* ** Indicate that all subsequent calls to sqlite3Fts5IndexWrite() pertain ** to the document with rowid iRowid. */ | | > > | > > > | 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 | } /* ** Indicate that all subsequent calls to sqlite3Fts5IndexWrite() pertain ** to the document with rowid iRowid. */ int sqlite3Fts5IndexBeginWrite(Fts5Index *p, int bDelete, i64 iRowid){ assert( p->rc==SQLITE_OK ); /* Allocate the hash table if it has not already been allocated */ if( p->pHash==0 ){ p->rc = sqlite3Fts5HashNew(&p->pHash, &p->nPendingData); } /* Flush the hash table to disk if required */ if( iRowid<p->iWriteRowid || (iRowid==p->iWriteRowid && p->bDelete==0) || (p->nPendingData > p->nMaxPendingData) ){ fts5IndexFlush(p); } p->iWriteRowid = iRowid; p->bDelete = bDelete; return fts5IndexReturn(p); } /* ** Commit data to disk. */ int sqlite3Fts5IndexSync(Fts5Index *p, int bCommit){ |
︙ | ︙ | |||
4302 4303 4304 4305 4306 4307 4308 | assert( p->pReader==0 ); sqlite3_finalize(p->pWriter); sqlite3_finalize(p->pDeleter); sqlite3_finalize(p->pIdxWriter); sqlite3_finalize(p->pIdxDeleter); sqlite3_finalize(p->pIdxSelect); sqlite3Fts5HashFree(p->pHash); | < | 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 | assert( p->pReader==0 ); sqlite3_finalize(p->pWriter); sqlite3_finalize(p->pDeleter); sqlite3_finalize(p->pIdxWriter); sqlite3_finalize(p->pIdxDeleter); sqlite3_finalize(p->pIdxSelect); sqlite3Fts5HashFree(p->pHash); sqlite3_free(p->zDataTbl); sqlite3_free(p); } return rc; } /* |
︙ | ︙ | |||
4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 | const char *pToken, int nToken /* Token to add or remove to or from index */ ){ int i; /* Used to iterate through indexes */ int rc = SQLITE_OK; /* Return code */ Fts5Config *pConfig = p->pConfig; assert( p->rc==SQLITE_OK ); /* Add the entry to the main terms index. */ rc = sqlite3Fts5HashWrite( p->pHash, p->iWriteRowid, iCol, iPos, FTS5_MAIN_PREFIX, pToken, nToken ); for(i=0; i<pConfig->nPrefix && rc==SQLITE_OK; i++){ | > | 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 4378 4379 4380 4381 4382 | const char *pToken, int nToken /* Token to add or remove to or from index */ ){ int i; /* Used to iterate through indexes */ int rc = SQLITE_OK; /* Return code */ Fts5Config *pConfig = p->pConfig; assert( p->rc==SQLITE_OK ); assert( (iCol<0)==p->bDelete ); /* Add the entry to the main terms index. */ rc = sqlite3Fts5HashWrite( p->pHash, p->iWriteRowid, iCol, iPos, FTS5_MAIN_PREFIX, pToken, nToken ); for(i=0; i<pConfig->nPrefix && rc==SQLITE_OK; i++){ |
︙ | ︙ | |||
5238 5239 5240 5241 5242 5243 5244 5245 5246 5247 5248 5249 5250 5251 | *pRc = rc; return; } fts5DebugStructure(pRc, pBuf, p); fts5StructureRelease(p); } /* ** Buffer (a/n) is assumed to contain a list of serialized varints. Read ** each varint and append its string representation to buffer pBuf. Return ** after either the input buffer is exhausted or a 0 value is read. ** ** The return value is the number of bytes read from the input buffer. | > > > > > > > > > > > > > > > > > > > > > > > | 5244 5245 5246 5247 5248 5249 5250 5251 5252 5253 5254 5255 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 5278 5279 5280 | *pRc = rc; return; } fts5DebugStructure(pRc, pBuf, p); fts5StructureRelease(p); } /* ** This is part of the fts5_decode() debugging aid. ** ** Arguments pBlob/nBlob contain an "averages" record. This function ** appends a human-readable representation of record to the buffer passed ** as the second argument. */ static void fts5DecodeAverages( int *pRc, /* IN/OUT: error code */ Fts5Buffer *pBuf, const u8 *pBlob, int nBlob ){ int i = 0; const char *zSpace = ""; while( i<nBlob ){ u64 iVal; i += sqlite3Fts5GetVarint(&pBlob[i], &iVal); sqlite3Fts5BufferAppendPrintf(pRc, pBuf, "%s%d", zSpace, (int)iVal); zSpace = " "; } } /* ** Buffer (a/n) is assumed to contain a list of serialized varints. Read ** each varint and append its string representation to buffer pBuf. Return ** after either the input buffer is exhausted or a 0 value is read. ** ** The return value is the number of bytes read from the input buffer. |
︙ | ︙ | |||
5340 5341 5342 5343 5344 5345 5346 | for(fts5DlidxLvlNext(&lvl); lvl.bEof==0; fts5DlidxLvlNext(&lvl)){ sqlite3Fts5BufferAppendPrintf(&rc, &s, " %d(%lld)", lvl.iLeafPgno, lvl.iRowid ); } }else if( iSegid==0 ){ if( iRowid==FTS5_AVERAGES_ROWID ){ | | | 5369 5370 5371 5372 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 | for(fts5DlidxLvlNext(&lvl); lvl.bEof==0; fts5DlidxLvlNext(&lvl)){ sqlite3Fts5BufferAppendPrintf(&rc, &s, " %d(%lld)", lvl.iLeafPgno, lvl.iRowid ); } }else if( iSegid==0 ){ if( iRowid==FTS5_AVERAGES_ROWID ){ fts5DecodeAverages(&rc, &s, a, n); }else{ fts5DecodeStructure(&rc, &s, a, n); } }else{ Fts5Buffer term; /* Current term read from page */ int szLeaf; /* Offset of pgidx in a[] */ int iPgidxOff; |
︙ | ︙ |
Changes to ext/fts5/fts5_main.c.
︙ | ︙ | |||
435 436 437 438 439 440 441 442 443 444 445 446 447 448 | */ #define FTS5_PLAN_MATCH 1 /* (<tbl> MATCH ?) */ #define FTS5_PLAN_SOURCE 2 /* A source cursor for SORTED_MATCH */ #define FTS5_PLAN_SPECIAL 3 /* An internal query */ #define FTS5_PLAN_SORTED_MATCH 4 /* (<tbl> MATCH ? ORDER BY rank) */ #define FTS5_PLAN_SCAN 5 /* No usable constraint */ #define FTS5_PLAN_ROWID 6 /* (rowid = ?) */ /* ** Implementation of the xBestIndex method for FTS5 tables. Within the ** WHERE constraint, it searches for the following: ** ** 1. A MATCH constraint against the special column. ** 2. A MATCH constraint against the "rank" column. | > > > > > > > > > > > > > | 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 | */ #define FTS5_PLAN_MATCH 1 /* (<tbl> MATCH ?) */ #define FTS5_PLAN_SOURCE 2 /* A source cursor for SORTED_MATCH */ #define FTS5_PLAN_SPECIAL 3 /* An internal query */ #define FTS5_PLAN_SORTED_MATCH 4 /* (<tbl> MATCH ? ORDER BY rank) */ #define FTS5_PLAN_SCAN 5 /* No usable constraint */ #define FTS5_PLAN_ROWID 6 /* (rowid = ?) */ /* ** Set the SQLITE_INDEX_SCAN_UNIQUE flag in pIdxInfo->flags. Unless this ** extension is currently being used by a version of SQLite too old to ** support index-info flags. In that case this function is a no-op. */ static void fts5SetUniqueFlag(sqlite3_index_info *pIdxInfo){ #if SQLITE_VERSION_NUMBER>=3008012 if( sqlite3_libversion_number()>=3008012 ){ pIdxInfo->idxFlags |= SQLITE_INDEX_SCAN_UNIQUE; } #endif } /* ** Implementation of the xBestIndex method for FTS5 tables. Within the ** WHERE constraint, it searches for the following: ** ** 1. A MATCH constraint against the special column. ** 2. A MATCH constraint against the "rank" column. |
︙ | ︙ | |||
542 543 544 545 546 547 548 549 550 551 552 553 554 555 | } } /* Calculate the estimated cost based on the flags set in idxFlags. */ bHasMatch = BitFlagTest(idxFlags, FTS5_BI_MATCH); if( BitFlagTest(idxFlags, FTS5_BI_ROWID_EQ) ){ pInfo->estimatedCost = bHasMatch ? 100.0 : 10.0; }else if( BitFlagAllTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){ pInfo->estimatedCost = bHasMatch ? 500.0 : 250000.0; }else if( BitFlagTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){ pInfo->estimatedCost = bHasMatch ? 750.0 : 750000.0; }else{ pInfo->estimatedCost = bHasMatch ? 1000.0 : 1000000.0; } | > | 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 | } } /* Calculate the estimated cost based on the flags set in idxFlags. */ bHasMatch = BitFlagTest(idxFlags, FTS5_BI_MATCH); if( BitFlagTest(idxFlags, FTS5_BI_ROWID_EQ) ){ pInfo->estimatedCost = bHasMatch ? 100.0 : 10.0; if( bHasMatch==0 ) fts5SetUniqueFlag(pInfo); }else if( BitFlagAllTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){ pInfo->estimatedCost = bHasMatch ? 500.0 : 250000.0; }else if( BitFlagTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){ pInfo->estimatedCost = bHasMatch ? 750.0 : 750000.0; }else{ pInfo->estimatedCost = bHasMatch ? 1000.0 : 1000000.0; } |
︙ | ︙ | |||
1280 1281 1282 1283 1284 1285 1286 | ** ** The commands implemented by this function are documented in the "Special ** INSERT Directives" section of the documentation. It should be updated if ** more commands are added to this function. */ static int fts5SpecialInsert( Fts5Table *pTab, /* Fts5 table object */ | | < | | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > > < > > > > > > > > | > > > > > > > > > > > > > > > > | | | | | | > > | < < | | < | < > > | < > | > > | | | > | | < < > > > | > > > > > > > > > > > > > | | > > | < > > | | > > > | | < < | 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 | ** ** The commands implemented by this function are documented in the "Special ** INSERT Directives" section of the documentation. It should be updated if ** more commands are added to this function. */ static int fts5SpecialInsert( Fts5Table *pTab, /* Fts5 table object */ const char *zCmd, /* Text inserted into table-name column */ sqlite3_value *pVal /* Value inserted into rank column */ ){ Fts5Config *pConfig = pTab->pConfig; int rc = SQLITE_OK; int bError = 0; if( 0==sqlite3_stricmp("delete-all", zCmd) ){ if( pConfig->eContent==FTS5_CONTENT_NORMAL ){ fts5SetVtabError(pTab, "'delete-all' may only be used with a " "contentless or external content fts5 table" ); rc = SQLITE_ERROR; }else{ rc = sqlite3Fts5StorageDeleteAll(pTab->pStorage); } }else if( 0==sqlite3_stricmp("rebuild", zCmd) ){ if( pConfig->eContent==FTS5_CONTENT_NONE ){ fts5SetVtabError(pTab, "'rebuild' may not be used with a contentless fts5 table" ); rc = SQLITE_ERROR; }else{ rc = sqlite3Fts5StorageRebuild(pTab->pStorage); } }else if( 0==sqlite3_stricmp("optimize", zCmd) ){ rc = sqlite3Fts5StorageOptimize(pTab->pStorage); }else if( 0==sqlite3_stricmp("merge", zCmd) ){ int nMerge = sqlite3_value_int(pVal); rc = sqlite3Fts5StorageMerge(pTab->pStorage, nMerge); }else if( 0==sqlite3_stricmp("integrity-check", zCmd) ){ rc = sqlite3Fts5StorageIntegrity(pTab->pStorage); #ifdef SQLITE_DEBUG }else if( 0==sqlite3_stricmp("prefix-index", zCmd) ){ pConfig->bPrefixIndex = sqlite3_value_int(pVal); #endif }else{ rc = sqlite3Fts5IndexLoadConfig(pTab->pIndex); if( rc==SQLITE_OK ){ rc = sqlite3Fts5ConfigSetValue(pTab->pConfig, zCmd, pVal, &bError); } if( rc==SQLITE_OK ){ if( bError ){ rc = SQLITE_ERROR; }else{ rc = sqlite3Fts5StorageConfigValue(pTab->pStorage, zCmd, pVal, 0); } } } return rc; } static int fts5SpecialDelete( Fts5Table *pTab, sqlite3_value **apVal, sqlite3_int64 *piRowid ){ int rc = SQLITE_OK; int eType1 = sqlite3_value_type(apVal[1]); if( eType1==SQLITE_INTEGER ){ sqlite3_int64 iDel = sqlite3_value_int64(apVal[1]); rc = sqlite3Fts5StorageSpecialDelete(pTab->pStorage, iDel, &apVal[2]); } return rc; } static void fts5StorageInsert( int *pRc, Fts5Table *pTab, sqlite3_value **apVal, i64 *piRowid ){ int rc = *pRc; if( rc==SQLITE_OK ){ rc = sqlite3Fts5StorageContentInsert(pTab->pStorage, apVal, piRowid); } if( rc==SQLITE_OK ){ rc = sqlite3Fts5StorageIndexInsert(pTab->pStorage, apVal, *piRowid); } *pRc = rc; } /* ** This function is the implementation of the xUpdate callback used by ** FTS3 virtual tables. It is invoked by SQLite each time a row is to be ** inserted, updated or deleted. ** ** A delete specifies a single argument - the rowid of the row to remove. ** ** Update and insert operations pass: ** ** 1. The "old" rowid, or NULL. ** 2. The "new" rowid. ** 3. Values for each of the nCol matchable columns. ** 4. Values for the two hidden columns (<tablename> and "rank"). */ static int fts5UpdateMethod( sqlite3_vtab *pVtab, /* Virtual table handle */ int nArg, /* Size of argument array */ sqlite3_value **apVal, /* Array of arguments */ sqlite_int64 *pRowid /* OUT: The affected (or effected) rowid */ ){ Fts5Table *pTab = (Fts5Table*)pVtab; Fts5Config *pConfig = pTab->pConfig; int eType0; /* value_type() of apVal[0] */ int rc = SQLITE_OK; /* Return code */ /* A transaction must be open when this is called. */ assert( pTab->ts.eState==1 ); assert( pVtab->zErrMsg==0 ); assert( nArg==1 || nArg==(2+pConfig->nCol+2) ); assert( nArg==1 || sqlite3_value_type(apVal[1])==SQLITE_INTEGER || sqlite3_value_type(apVal[1])==SQLITE_NULL ); assert( pTab->pConfig->pzErrmsg==0 ); pTab->pConfig->pzErrmsg = &pTab->base.zErrMsg; /* Put any active cursors into REQUIRE_SEEK state. */ fts5TripCursors(pTab); eType0 = sqlite3_value_type(apVal[0]); if( eType0==SQLITE_NULL && sqlite3_value_type(apVal[2+pConfig->nCol])!=SQLITE_NULL ){ /* A "special" INSERT op. These are handled separately. */ const char *z = (const char*)sqlite3_value_text(apVal[2+pConfig->nCol]); if( pConfig->eContent!=FTS5_CONTENT_NORMAL && 0==sqlite3_stricmp("delete", z) ){ rc = fts5SpecialDelete(pTab, apVal, pRowid); }else{ rc = fts5SpecialInsert(pTab, z, apVal[2 + pConfig->nCol + 1]); } }else{ /* A regular INSERT, UPDATE or DELETE statement. The trick here is that ** any conflict on the rowid value must be detected before any ** modifications are made to the database file. There are 4 cases: ** ** 1) DELETE ** 2) UPDATE (rowid not modified) ** 3) UPDATE (rowid modified) ** 4) INSERT ** ** Cases 3 and 4 may violate the rowid constraint. */ int eConflict = sqlite3_vtab_on_conflict(pConfig->db); assert( eType0==SQLITE_INTEGER || eType0==SQLITE_NULL ); assert( nArg!=1 || eType0==SQLITE_INTEGER ); /* Filter out attempts to run UPDATE or DELETE on contentless tables. ** This is not suported. */ if( eType0==SQLITE_INTEGER && fts5IsContentless(pTab) ){ pTab->base.zErrMsg = sqlite3_mprintf( "cannot %s contentless fts5 table: %s", (nArg>1 ? "UPDATE" : "DELETE from"), pConfig->zName ); rc = SQLITE_ERROR; } /* Case 1: DELETE */ else if( nArg==1 ){ i64 iDel = sqlite3_value_int64(apVal[0]); /* Rowid to delete */ rc = sqlite3Fts5StorageDelete(pTab->pStorage, iDel); } /* Case 2: INSERT */ else if( eType0!=SQLITE_INTEGER ){ /* If this is a REPLACE, first remove the current entry (if any) */ if( eConflict==SQLITE_REPLACE && sqlite3_value_type(apVal[1])==SQLITE_INTEGER ){ i64 iNew = sqlite3_value_int64(apVal[1]); /* Rowid to delete */ rc = sqlite3Fts5StorageDelete(pTab->pStorage, iNew); } fts5StorageInsert(&rc, pTab, apVal, pRowid); } /* Case 2: UPDATE */ else{ i64 iOld = sqlite3_value_int64(apVal[0]); /* Old rowid */ i64 iNew = sqlite3_value_int64(apVal[1]); /* New rowid */ if( iOld!=iNew ){ if( eConflict==SQLITE_REPLACE ){ rc = sqlite3Fts5StorageDelete(pTab->pStorage, iOld); if( rc==SQLITE_OK ){ rc = sqlite3Fts5StorageDelete(pTab->pStorage, iNew); } fts5StorageInsert(&rc, pTab, apVal, pRowid); }else{ rc = sqlite3Fts5StorageContentInsert(pTab->pStorage, apVal, pRowid); if( rc==SQLITE_OK ){ rc = sqlite3Fts5StorageDelete(pTab->pStorage, iOld); } if( rc==SQLITE_OK ){ rc = sqlite3Fts5StorageIndexInsert(pTab->pStorage, apVal, *pRowid); } } }else{ rc = sqlite3Fts5StorageDelete(pTab->pStorage, iOld); fts5StorageInsert(&rc, pTab, apVal, pRowid); } } } update_method_out: pTab->pConfig->pzErrmsg = 0; return rc; } |
︙ | ︙ |
Changes to ext/fts5/fts5_storage.c.
︙ | ︙ | |||
388 389 390 391 392 393 394 | int rc2; sqlite3_bind_int64(pSeek, 1, iDel); if( sqlite3_step(pSeek)==SQLITE_ROW ){ int iCol; Fts5InsertCtx ctx; ctx.pStorage = p; ctx.iCol = -1; | | | 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 | int rc2; sqlite3_bind_int64(pSeek, 1, iDel); if( sqlite3_step(pSeek)==SQLITE_ROW ){ int iCol; Fts5InsertCtx ctx; ctx.pStorage = p; ctx.iCol = -1; rc = sqlite3Fts5IndexBeginWrite(p->pIndex, 1, iDel); for(iCol=1; rc==SQLITE_OK && iCol<=pConfig->nCol; iCol++){ if( pConfig->abUnindexed[iCol-1] ) continue; ctx.szCol = 0; rc = sqlite3Fts5Tokenize(pConfig, FTS5_TOKENIZE_DOCUMENT, (const char*)sqlite3_column_text(pSeek, iCol), sqlite3_column_bytes(pSeek, iCol), |
︙ | ︙ | |||
545 546 547 548 549 550 551 | /* Delete the index records */ if( rc==SQLITE_OK ){ int iCol; Fts5InsertCtx ctx; ctx.pStorage = p; ctx.iCol = -1; | | | 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 | /* Delete the index records */ if( rc==SQLITE_OK ){ int iCol; Fts5InsertCtx ctx; ctx.pStorage = p; ctx.iCol = -1; rc = sqlite3Fts5IndexBeginWrite(p->pIndex, 1, iDel); for(iCol=0; rc==SQLITE_OK && iCol<pConfig->nCol; iCol++){ if( pConfig->abUnindexed[iCol] ) continue; ctx.szCol = 0; rc = sqlite3Fts5Tokenize(pConfig, FTS5_TOKENIZE_DOCUMENT, (const char*)sqlite3_value_text(apVal[iCol]), sqlite3_value_bytes(apVal[iCol]), |
︙ | ︙ | |||
635 636 637 638 639 640 641 | rc = fts5StorageGetStmt(p, FTS5_STMT_SCAN, &pScan, 0); } while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pScan) ){ i64 iRowid = sqlite3_column_int64(pScan, 0); sqlite3Fts5BufferZero(&buf); | | | 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 | rc = fts5StorageGetStmt(p, FTS5_STMT_SCAN, &pScan, 0); } while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pScan) ){ i64 iRowid = sqlite3_column_int64(pScan, 0); sqlite3Fts5BufferZero(&buf); rc = sqlite3Fts5IndexBeginWrite(p->pIndex, 0, iRowid); for(ctx.iCol=0; rc==SQLITE_OK && ctx.iCol<pConfig->nCol; ctx.iCol++){ ctx.szCol = 0; if( pConfig->abUnindexed[ctx.iCol]==0 ){ rc = sqlite3Fts5Tokenize(pConfig, FTS5_TOKENIZE_DOCUMENT, (const char*)sqlite3_column_text(pScan, ctx.iCol+1), sqlite3_column_bytes(pScan, ctx.iCol+1), |
︙ | ︙ | |||
701 702 703 704 705 706 707 | *piRowid = sqlite3_last_insert_rowid(p->pConfig->db); } } return rc; } /* | | | | | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > < < < > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | < | | 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 | *piRowid = sqlite3_last_insert_rowid(p->pConfig->db); } } return rc; } /* ** Insert a new row into the FTS content table. */ int sqlite3Fts5StorageContentInsert( Fts5Storage *p, sqlite3_value **apVal, i64 *piRowid ){ Fts5Config *pConfig = p->pConfig; int rc = SQLITE_OK; /* Insert the new row into the %_content table. */ if( pConfig->eContent!=FTS5_CONTENT_NORMAL ){ if( sqlite3_value_type(apVal[1])==SQLITE_INTEGER ){ *piRowid = sqlite3_value_int64(apVal[1]); }else{ rc = fts5StorageNewRowid(p, piRowid); } }else{ sqlite3_stmt *pInsert = 0; /* Statement to write %_content table */ int i; /* Counter variable */ #if 0 if( eConflict==SQLITE_REPLACE ){ eStmt = FTS5_STMT_REPLACE_CONTENT; rc = fts5StorageDeleteFromIndex(p, sqlite3_value_int64(apVal[1])); }else{ eStmt = FTS5_STMT_INSERT_CONTENT; } #endif if( rc==SQLITE_OK ){ rc = fts5StorageGetStmt(p, FTS5_STMT_INSERT_CONTENT, &pInsert, 0); } for(i=1; rc==SQLITE_OK && i<=pConfig->nCol+1; i++){ rc = sqlite3_bind_value(pInsert, i, apVal[i]); } if( rc==SQLITE_OK ){ sqlite3_step(pInsert); rc = sqlite3_reset(pInsert); } *piRowid = sqlite3_last_insert_rowid(pConfig->db); } return rc; } /* ** Insert new entries into the FTS index and %_docsize table. */ int sqlite3Fts5StorageIndexInsert( Fts5Storage *p, sqlite3_value **apVal, i64 iRowid ){ Fts5Config *pConfig = p->pConfig; int rc = SQLITE_OK; /* Return code */ Fts5InsertCtx ctx; /* Tokenization callback context object */ Fts5Buffer buf; /* Buffer used to build up %_docsize blob */ memset(&buf, 0, sizeof(Fts5Buffer)); ctx.pStorage = p; rc = fts5StorageLoadTotals(p, 1); if( rc==SQLITE_OK ){ rc = sqlite3Fts5IndexBeginWrite(p->pIndex, 0, iRowid); } for(ctx.iCol=0; rc==SQLITE_OK && ctx.iCol<pConfig->nCol; ctx.iCol++){ ctx.szCol = 0; if( pConfig->abUnindexed[ctx.iCol]==0 ){ rc = sqlite3Fts5Tokenize(pConfig, FTS5_TOKENIZE_DOCUMENT, (const char*)sqlite3_value_text(apVal[ctx.iCol+2]), sqlite3_value_bytes(apVal[ctx.iCol+2]), (void*)&ctx, fts5StorageInsertCallback ); } sqlite3Fts5BufferAppendVarint(&rc, &buf, ctx.szCol); p->aTotalSize[ctx.iCol] += (i64)ctx.szCol; } p->nTotalRow++; /* Write the %_docsize record */ if( rc==SQLITE_OK ){ rc = fts5StorageInsertDocsize(p, iRowid, &buf); } sqlite3_free(buf.p); /* Write the averages record */ if( rc==SQLITE_OK ){ rc = fts5StorageSaveTotals(p); } |
︙ | ︙ |
Added ext/fts5/test/fts5onepass.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 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 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | # 2015 Sep 27 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # source [file join [file dirname [info script]] fts5_common.tcl] set testprefix fts5onepass # If SQLITE_ENABLE_FTS3 is defined, omit this file. ifcapable !fts5 { finish_test return } do_execsql_test 1.0 { CREATE VIRTUAL TABLE ft USING fts5(content); INSERT INTO ft(rowid, content) VALUES(1, '1 2 3'); INSERT INTO ft(rowid, content) VALUES(2, '4 5 6'); INSERT INTO ft(rowid, content) VALUES(3, '7 8 9'); } #------------------------------------------------------------------------- # Check that UPDATE and DELETE statements that feature "WHERE rowid=?" or # or "WHERE rowid=?" clauses do not use statement journals. But that other # DELETE and UPDATE statements do. # # Note: "MATCH ? AND rowid=?" does use a statement journal. # foreach {tn sql uses} { 1.1 { DELETE FROM ft } 1 1.2 { DELETE FROM ft WHERE rowid=? } 0 1.3 { DELETE FROM ft WHERE rowid=? } 0 1.4 { DELETE FROM ft WHERE ft MATCH '1' } 1 1.5 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1 1.6 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1 2.1 { UPDATE ft SET content='a b c' } 1 2.2 { UPDATE ft SET content='a b c' WHERE rowid=? } 0 2.3 { UPDATE ft SET content='a b c' WHERE rowid=? } 0 2.4 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' } 1 2.5 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1 2.6 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1 } { do_test 1.$tn { sql_uses_stmt db $sql } $uses } #------------------------------------------------------------------------- # Check that putting a "DELETE/UPDATE ... WHERE rowid=?" statement in a # trigger program does not prevent the VM from using a statement # transaction. Even if the calling statement cannot hit a constraint. # do_execsql_test 2.0 { CREATE TABLE t1(x); CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN DELETE FROM ft WHERE rowid=new.x; END; CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN UPDATE ft SET content = 'a b c' WHERE rowid=old.x; END; CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN DELETE FROM ft WHERE rowid=old.x; END; } foreach {tn sql uses} { 1 { INSERT INTO t1 VALUES(1) } 1 2 { DELETE FROM t1 WHERE x=4 } 1 3 { UPDATE t1 SET x=10 WHERE x=11 } 1 } { do_test 2.$tn { sql_uses_stmt db $sql } $uses } #------------------------------------------------------------------------- # Test that an "UPDATE ... WHERE rowid=?" works and does not corrupt the # index when it strikes a constraint. Both inside and outside a # transaction. # foreach {tn tcl1 tcl2} { 1 {} {} 2 { execsql BEGIN } { if {[sqlite3_get_autocommit db]==1} { error "transaction rolled back!" } execsql COMMIT } } { do_execsql_test 3.$tn.0 { DROP TABLE IF EXISTS ft2; CREATE VIRTUAL TABLE ft2 USING fts5(content); INSERT INTO ft2(rowid, content) VALUES(1, 'a b c'); INSERT INTO ft2(rowid, content) VALUES(2, 'a b d'); INSERT INTO ft2(rowid, content) VALUES(3, 'a b e'); } eval $tcl1 foreach {tn2 sql content} { 1 { UPDATE ft2 SET rowid=2 WHERE rowid=1 } { 1 {a b c} 2 {a b d} 3 {a b e} } 2 { INSERT INTO ft2(rowid, content) VALUES(4, 'a b f'); UPDATE ft2 SET rowid=5 WHERE rowid=4; UPDATE ft2 SET rowid=3 WHERE rowid=5; } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } 3 { UPDATE ft2 SET rowid=3 WHERE rowid=4; -- matches 0 rows UPDATE ft2 SET rowid=2 WHERE rowid=3; } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } 4 { INSERT INTO ft2(rowid, content) VALUES(4, 'a b g'); UPDATE ft2 SET rowid=-1 WHERE rowid=4; UPDATE ft2 SET rowid=3 WHERE rowid=-1; } {-1 {a b g} 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } 5 { DELETE FROM ft2 WHERE rowid=451; DELETE FROM ft2 WHERE rowid=-1; UPDATE ft2 SET rowid = 2 WHERE rowid = 1; } {1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } } { do_catchsql_test 3.$tn.$tn2.a $sql {1 {constraint failed}} do_execsql_test 3.$tn.$tn2.b { SELECT rowid, content FROM ft2 } $content do_execsql_test 3.$tn.$tn2.c { INSERT INTO ft2(ft2) VALUES('integrity-check'); } } eval $tcl2 } #------------------------------------------------------------------------- # Check that DELETE and UPDATE operations can be done without flushing # the in-memory hash table to disk. # reset_db do_execsql_test 4.1.1 { CREATE VIRTUAL TABLE ttt USING fts5(x); BEGIN; INSERT INTO ttt(rowid, x) VALUES(1, 'a b c'); INSERT INTO ttt(rowid, x) VALUES(2, 'a b c'); INSERT INTO ttt(rowid, x) VALUES(3, 'a b c'); COMMIT } do_test 4.1.2 { fts5_level_segs ttt } {1} do_execsql_test 4.2.1 { BEGIN; DELETE FROM ttt WHERE rowid=1; DELETE FROM ttt WHERE rowid=3; INSERT INTO ttt(rowid, x) VALUES(4, 'd e f'); INSERT INTO ttt(rowid, x) VALUES(5, 'd e f'); COMMIT; } {} do_test 4.2.2 { fts5_level_segs ttt } {2} do_execsql_test 4.3.1 { BEGIN; UPDATE ttt SET x = 'd e f' WHERE rowid = 2; UPDATE ttt SET x = 'A B C' WHERE rowid = 4; INSERT INTO ttt(rowid, x) VALUES(6, 'd e f'); COMMIT; } {} do_test 4.2.2 { fts5_level_segs ttt } {3} finish_test |
Changes to ext/fts5/test/fts5simple.test.
︙ | ︙ | |||
15 16 17 18 19 20 21 | # If SQLITE_ENABLE_FTS5 is defined, omit this file. ifcapable !fts5 { finish_test return } | < | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | # If SQLITE_ENABLE_FTS5 is defined, omit this file. ifcapable !fts5 { finish_test return } #------------------------------------------------------------------------- # set doc "x x [string repeat {y } 50]z z" do_execsql_test 1.0 { CREATE VIRTUAL TABLE t1 USING fts5(x); INSERT INTO t1(t1, rank) VALUES('pgsz', 32); BEGIN; |
︙ | ︙ | |||
133 134 135 136 137 138 139 | COMMIT; } {} do_execsql_test 5.4 { SELECT rowid FROM tt WHERE tt MATCH 'a*'; } {1 2} | < < | 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | COMMIT; } {} do_execsql_test 5.4 { SELECT rowid FROM tt WHERE tt MATCH 'a*'; } {1 2} do_execsql_test 5.5 { DELETE FROM tt; BEGIN; INSERT INTO tt VALUES('aa'); INSERT INTO tt VALUES('ab'); INSERT INTO tt VALUES('aa'); INSERT INTO tt VALUES('ab'); |
︙ | ︙ | |||
180 181 182 183 184 185 186 187 188 189 | do_catchsql_test 6.2 { SELECT * FROM xyz WHERE xyz MATCH '' } {1 {fts5: syntax error near ""}} do_catchsql_test 6.3 { SELECT * FROM xyz WHERE xyz MATCH NULL } {1 {fts5: syntax error near ""}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | do_catchsql_test 6.2 { SELECT * FROM xyz WHERE xyz MATCH '' } {1 {fts5: syntax error near ""}} do_catchsql_test 6.3 { SELECT * FROM xyz WHERE xyz MATCH NULL } {1 {fts5: syntax error near ""}} #------------------------------------------------------------------------- do_execsql_test 7.1 { CREATE VIRTUAL TABLE ft2 USING fts5(content); INSERT INTO ft2(rowid, content) VALUES(1, 'a b c'); INSERT INTO ft2(rowid, content) VALUES(2, 'a b d'); } do_catchsql_test 7.2 { BEGIN; UPDATE ft2 SET rowid=2 WHERE rowid=1; } {1 {constraint failed}} do_execsql_test 7.3 { COMMIT; INSERT INTO ft2(ft2) VALUES('integrity-check'); } {} do_execsql_test 7.4 { SELECT * FROM ft2; } {{a b c} {a b d}} #------------------------------------------------------------------------- # reset_db do_execsql_test 8.1 { CREATE VIRTUAL TABLE ft2 USING fts5(content); INSERT INTO ft2(rowid, content) VALUES(1, 'a b'); } do_execsql_test 8.2 { BEGIN; INSERT INTO ft2(rowid, content) VALUES(4, 'a x'); } do_execsql_test 8.3 { INSERT INTO ft2(ft2) VALUES('integrity-check'); } finish_test |