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