SQLite4
Check-in [8161b13910]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fixes for updates and deletes on tables with fts5 indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8161b139108fa7ea07ad0075049b00f0052fac9b
User & Date: dan 2012-12-24 15:32:08
Context
2012-12-26
19:40
Modify where.c and so on to handle fts scans. Opcodes do not work yet. check-in: 58a5617da3 user: dan tags: trunk
2012-12-24
15:32
Fixes for updates and deletes on tables with fts5 indexes. check-in: 8161b13910 user: dan tags: trunk
2012-12-22
19:59
More work on updating an fts5 index. Add a pragma that checks if the index and table contents match. check-in: 4693eb7bcc user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   581    581     int bCount,                     /* Non-zero to increment change counter */
   582    582     int baseCur,                    /* Cursor number for the table */
   583    583     int *aRegIdx                    /* Only delete if (aRegIdx && aRegIdx[i]>0) */
   584    584   ){
   585    585     Vdbe *v = pParse->pVdbe;
   586    586     Index *pPk;
   587    587     int iPk;
          588  +  int iPkCsr;
   588    589     int i;
   589    590     int regKey;
   590    591     Index *pIdx;
   591    592   
   592    593     regKey = sqlite4GetTempReg(pParse);
   593    594     pPk = sqlite4FindPrimaryKey(pTab, &iPk);
          595  +  iPkCsr = baseCur+iPk;
   594    596   
   595    597     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
   596         -    if( pIdx!=pPk && (aRegIdx==0 || aRegIdx[i]>0) ){
          598  +    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
          599  +      int iCol;
          600  +      int iReg = pParse->nMem+1;
          601  +      pParse->nMem += (1 + pTab->nCol);
          602  +      for(iCol=0; iCol<pTab->nCol; iCol++){
          603  +        sqlite4VdbeAddOp3(v, OP_Column, iPkCsr, iCol, iReg+iCol);
          604  +      }
          605  +      sqlite4VdbeAddOp2(v, OP_RowKey, iPkCsr, iReg+pTab->nCol);
          606  +      sqlite4Fts5CodeUpdate(pParse, pIdx, iReg+pTab->nCol, iReg, 1);
          607  +    }else if( pIdx!=pPk && (aRegIdx==0 || aRegIdx[i]>0) ){
   597    608         int addrNotFound;
   598         -      sqlite4EncodeIndexKey(pParse, pPk, baseCur+iPk, pIdx, baseCur+i,0,regKey);
          609  +      sqlite4EncodeIndexKey(pParse, pPk, baseCur+iPk,pIdx,baseCur+i,0,regKey);
   599    610         addrNotFound = sqlite4VdbeAddOp4(v,
   600    611             OP_NotFound, baseCur+i, 0, regKey, 0, P4_INT32
   601    612         );
   602    613         sqlite4VdbeAddOp1(v, OP_Delete, baseCur+i);
   603    614         sqlite4VdbeJumpHere(v, addrNotFound);
   604    615       }
   605    616     }
   606    617   
   607    618     sqlite4VdbeAddOp2(v, OP_Delete, baseCur+iPk, (bCount ? OPFLAG_NCHANGE: 0));
   608    619     sqlite4ReleaseTempReg(pParse, regKey);
   609    620   }

Changes to src/fts5.c.

  1090   1090         nKey += nToken;
  1091   1091         aKey[nKey++] = 0x00;
  1092   1092         memcpy(&aKey[nKey], pPK, nPK);
  1093   1093         nKey += nPK;
  1094   1094   
  1095   1095         if( bDel ){
  1096   1096           /* delete key aKey/nKey... */
  1097         -        assert( 0 );
         1097  +        rc = sqlite4KVStoreReplace(pStore, aKey, nKey, 0, -1);
  1098   1098         }else{
  1099   1099           const KVByteArray *aData = (const KVByteArray *)&pTerm[1];
  1100   1100           aData += pTerm->nToken;
  1101   1101           rc = sqlite4KVStoreReplace(pStore, aKey, nKey, aData, pTerm->nData);
  1102   1102         }
  1103   1103       }
  1104   1104       sqlite4DbFree(db, pTerm);
................................................................................
  1131   1131   }
  1132   1132   
  1133   1133   
  1134   1134   void sqlite4Fts5CodeUpdate(
  1135   1135     Parse *pParse, 
  1136   1136     Index *pIdx, 
  1137   1137     int iRegPk, 
  1138         -  int iRegData
         1138  +  int iRegData,
         1139  +  int bDel
  1139   1140   ){
  1140   1141     Vdbe *v;
  1141   1142     Fts5Info *pInfo;                /* p4 argument for FtsUpdate opcode */
  1142   1143   
  1143   1144     if( 0==(pInfo = fts5InfoCreate(pParse, pIdx)) ) return;
  1144   1145   
  1145   1146     v = sqlite4GetVdbe(pParse);
  1146   1147     sqlite4VdbeAddOp3(v, OP_FtsUpdate, iRegPk, 0, iRegData);
  1147   1148     sqlite4VdbeChangeP4(v, -1, (const char *)pInfo, P4_FTS5INFO);
         1149  +  sqlite4VdbeChangeP5(v, (u8)bDel);
  1148   1150   }
  1149   1151   
  1150   1152   void sqlite4Fts5FreeInfo(sqlite4 *db, Fts5Info *p){
  1151   1153     if( db->pnBytesFreed==0 ){
  1152   1154       if( p->p ) p->pTokenizer->xDestroy(p->p);
  1153   1155       sqlite4DbFree(db, p);
  1154   1156     }

Changes to src/insert.c.

  1418   1418   
  1419   1419     /* Write the entry to each index. */
  1420   1420     for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
  1421   1421       assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
  1422   1422       if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
  1423   1423         int iPK;
  1424   1424         sqlite4FindPrimaryKey(pTab, &iPK);
  1425         -      sqlite4Fts5CodeUpdate(pParse, pIdx, aRegIdx[iPK], regContent);
         1425  +      sqlite4Fts5CodeUpdate(pParse, pIdx, aRegIdx[iPK], regContent, 0);
  1426   1426       }
  1427   1427       else if( aRegIdx[i] ){
  1428   1428         int regData = 0;
  1429   1429         int flags = 0;
  1430   1430         if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
  1431   1431           regData = regRec;
  1432   1432           flags = pik_flags;

Changes to src/sqliteInt.h.

  3267   3267   
  3268   3268   int sqlite4Fts5IndexSz(void);
  3269   3269   void sqlite4Fts5IndexInit(Parse *, Index *, ExprList *);
  3270   3270   void sqlite4Fts5IndexFree(sqlite4 *, Index *);
  3271   3271   
  3272   3272   int sqlite4Fts5Update(sqlite4 *, Fts5Info *, Mem *pPk, Mem *aArg, int, char **);
  3273   3273   void sqlite4Fts5FreeInfo(sqlite4 *db, Fts5Info *);
  3274         -void sqlite4Fts5CodeUpdate(Parse *, Index *pIdx, int iRegPk, int iRegData);
         3274  +void sqlite4Fts5CodeUpdate(Parse *, Index *pIdx, int iRegPk, int iRegData, int);
  3275   3275   void sqlite4Fts5CodeCksum(Parse *, Index *, int, int, int);
  3276   3276   
  3277   3277   #endif /* _SQLITEINT_H_ */

Changes to src/update.c.

   504    504   
   505    505       /* Delete the index entries associated with the current record.  */
   506    506       j1 = sqlite4VdbeAddOp4(v, OP_NotFound, iCur+iPk, 0, regOldKey, 0, P4_INT32);
   507    507       sqlite4GenerateRowIndexDelete(pParse, pTab, 0, iCur, aRegIdx);
   508    508     
   509    509       /* Delete the old record */
   510    510       if( hasFK || bChngPk ){
   511         -      sqlite4VdbeAddOp2(v, OP_Delete, iCur, 0);
          511  +      sqlite4VdbeAddOp2(v, OP_Delete, iCur+iPk, 0);
   512    512       }
   513    513       sqlite4VdbeJumpHere(v, j1);
   514    514   
   515    515       if( hasFK ){
   516    516         sqlite4FkCheck(pParse, pTab, 0, regNew);
   517    517       }
   518    518     

Changes to src/where.c.

  3020   3020       int bDist = !!pDistinct;      /* True if index cannot help with DISTINCT */
  3021   3021       int bLookup = 0;              /* True if not the PK index */
  3022   3022       WhereTerm *pTerm;             /* A single term of the WHERE clause */
  3023   3023   #ifdef SQLITE4_ENABLE_STAT3
  3024   3024       WhereTerm *pFirstTerm = 0;    /* First term matching the index */
  3025   3025   #endif
  3026   3026       int nCol = pProbe->nColumn;   /* Total columns in index record */
         3027  +
         3028  +    if( pProbe->eIndexType==SQLITE4_INDEX_FTS5 ) continue;
  3027   3029   
  3028   3030       /* Unless pProbe is the primary key index, then the encoded PK column 
  3029   3031       ** values are at the end of each record. Set variable nCol to the total
  3030   3032       ** number of columns encoded into each index record, including the PK  
  3031   3033       ** columns.  */
  3032   3034       if( pProbe!=pPk ) nCol += pPk->nColumn;
  3033   3035   

Added test/fts5query1.test.

            1  +# 2012 December 17
            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  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix fts5query1
           17  +
           18  +do_execsql_test 1.0 {
           19  +  CREATE TABLE t1(a PRIMARY KEY, b, c);
           20  +  CREATE INDEX i1 ON t1 USING fts5();
           21  +}
           22  +
           23  +foreach {tn stmt} {
           24  +  1 "INSERT INTO t1 VALUES(1, 'a b c', 'd e f')"
           25  +  2 "INSERT INTO t1 VALUES(2, 'b c e', 'A A a')"
           26  +  3 "INSERT INTO t1 VALUES(3, 'd A A', 'e c a')"
           27  +  4 "DELETE FROM t1 WHERE a=1"
           28  +  5 "DELETE FROM t1"
           29  +  6 "INSERT INTO t1 VALUES(1, 'May you do', 'good and not evil')"
           30  +  7 "INSERT INTO t1 VALUES(2, 'May you find', 'forgiveness for yourself')"
           31  +  8 "UPDATE t1 SET b = 'and forgive others' WHERE a = 2"
           32  +  9 "UPDATE t1 SET a = 4 AND c = 'a b c d' WHERE a = 2"
           33  +} {
           34  +  do_execsql_test 1.$tn.1 $stmt
           35  +  do_execsql_test 1.$tn.2 {PRAGMA fts_check(i1)} ok
           36  +}
           37  +
           38  +finish_test