SQLite4
Check-in [8161b13910]
Not logged in
HomeTimelineBranchesTagsWikiLogin

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 Unified Diffs Show Whitespace Changes Patch

Changes to src/delete.c. 

581
582
583
584
585
586
587

588
589
590
591
592
593

594
595









596
597
598
599
600
601
602
603
604
605
606
607
608
609

  int bCount,                     /* Non-zero to increment change counter */
  int baseCur,                    /* Cursor number for the table */
  int *aRegIdx                    /* Only delete if (aRegIdx && aRegIdx[i]>0) */
){
  Vdbe *v = pParse->pVdbe;
  Index *pPk;
  int iPk;

  int i;
  int regKey;
  Index *pIdx;

  regKey = sqlite4GetTempReg(pParse);
  pPk = sqlite4FindPrimaryKey(pTab, &iPk);


  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){









    if( pIdx!=pPk && (aRegIdx==0 || aRegIdx[i]>0) ){
      int addrNotFound;
      sqlite4EncodeIndexKey(pParse, pPk, baseCur+iPk, pIdx, baseCur+i,0,regKey);
      addrNotFound = sqlite4VdbeAddOp4(v,
          OP_NotFound, baseCur+i, 0, regKey, 0, P4_INT32
      );
      sqlite4VdbeAddOp1(v, OP_Delete, baseCur+i);
      sqlite4VdbeJumpHere(v, addrNotFound);
    }
  }

  sqlite4VdbeAddOp2(v, OP_Delete, baseCur+iPk, (bCount ? OPFLAG_NCHANGE: 0));
  sqlite4ReleaseTempReg(pParse, regKey);
}








>






>


>
>
>
>
>
>
>
>
>
|














581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620

  int bCount,                     /* Non-zero to increment change counter */
  int baseCur,                    /* Cursor number for the table */
  int *aRegIdx                    /* Only delete if (aRegIdx && aRegIdx[i]>0) */
){
  Vdbe *v = pParse->pVdbe;
  Index *pPk;
  int iPk;
  int iPkCsr;
  int i;
  int regKey;
  Index *pIdx;

  regKey = sqlite4GetTempReg(pParse);
  pPk = sqlite4FindPrimaryKey(pTab, &iPk);
  iPkCsr = baseCur+iPk;

  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iCol;
      int iReg = pParse->nMem+1;
      pParse->nMem += (1 + pTab->nCol);
      for(iCol=0; iCol<pTab->nCol; iCol++){
        sqlite4VdbeAddOp3(v, OP_Column, iPkCsr, iCol, iReg+iCol);
      }
      sqlite4VdbeAddOp2(v, OP_RowKey, iPkCsr, iReg+pTab->nCol);
      sqlite4Fts5CodeUpdate(pParse, pIdx, iReg+pTab->nCol, iReg, 1);
    }else if( pIdx!=pPk && (aRegIdx==0 || aRegIdx[i]>0) ){
      int addrNotFound;
      sqlite4EncodeIndexKey(pParse, pPk, baseCur+iPk,pIdx,baseCur+i,0,regKey);
      addrNotFound = sqlite4VdbeAddOp4(v,
          OP_NotFound, baseCur+i, 0, regKey, 0, P4_INT32
      );
      sqlite4VdbeAddOp1(v, OP_Delete, baseCur+i);
      sqlite4VdbeJumpHere(v, addrNotFound);
    }
  }

  sqlite4VdbeAddOp2(v, OP_Delete, baseCur+iPk, (bCount ? OPFLAG_NCHANGE: 0));
  sqlite4ReleaseTempReg(pParse, regKey);
}

Changes to src/fts5.c. 

1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
....
1131
1132
1133
1134
1135
1136
1137
1138

1139
1140
1141
1142
1143
1144
1145
1146
1147

1148
1149
1150
1151
1152
1153
1154

      nKey += nToken;
      aKey[nKey++] = 0x00;
      memcpy(&aKey[nKey], pPK, nPK);
      nKey += nPK;

      if( bDel ){
        /* delete key aKey/nKey... */
        assert( 0 );
      }else{
        const KVByteArray *aData = (const KVByteArray *)&pTerm[1];
        aData += pTerm->nToken;
        rc = sqlite4KVStoreReplace(pStore, aKey, nKey, aData, pTerm->nData);
      }
    }
    sqlite4DbFree(db, pTerm);
................................................................................
}


void sqlite4Fts5CodeUpdate(
  Parse *pParse, 
  Index *pIdx, 
  int iRegPk, 
  int iRegData

){
  Vdbe *v;
  Fts5Info *pInfo;                /* p4 argument for FtsUpdate opcode */

  if( 0==(pInfo = fts5InfoCreate(pParse, pIdx)) ) return;

  v = sqlite4GetVdbe(pParse);
  sqlite4VdbeAddOp3(v, OP_FtsUpdate, iRegPk, 0, iRegData);
  sqlite4VdbeChangeP4(v, -1, (const char *)pInfo, P4_FTS5INFO);

}

void sqlite4Fts5FreeInfo(sqlite4 *db, Fts5Info *p){
  if( db->pnBytesFreed==0 ){
    if( p->p ) p->pTokenizer->xDestroy(p->p);
    sqlite4DbFree(db, p);
  }








|







 







|
>









>








1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
....
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156

      nKey += nToken;
      aKey[nKey++] = 0x00;
      memcpy(&aKey[nKey], pPK, nPK);
      nKey += nPK;

      if( bDel ){
        /* delete key aKey/nKey... */
        rc = sqlite4KVStoreReplace(pStore, aKey, nKey, 0, -1);
      }else{
        const KVByteArray *aData = (const KVByteArray *)&pTerm[1];
        aData += pTerm->nToken;
        rc = sqlite4KVStoreReplace(pStore, aKey, nKey, aData, pTerm->nData);
      }
    }
    sqlite4DbFree(db, pTerm);
................................................................................
}


void sqlite4Fts5CodeUpdate(
  Parse *pParse, 
  Index *pIdx, 
  int iRegPk, 
  int iRegData,
  int bDel
){
  Vdbe *v;
  Fts5Info *pInfo;                /* p4 argument for FtsUpdate opcode */

  if( 0==(pInfo = fts5InfoCreate(pParse, pIdx)) ) return;

  v = sqlite4GetVdbe(pParse);
  sqlite4VdbeAddOp3(v, OP_FtsUpdate, iRegPk, 0, iRegData);
  sqlite4VdbeChangeP4(v, -1, (const char *)pInfo, P4_FTS5INFO);
  sqlite4VdbeChangeP5(v, (u8)bDel);
}

void sqlite4Fts5FreeInfo(sqlite4 *db, Fts5Info *p){
  if( db->pnBytesFreed==0 ){
    if( p->p ) p->pTokenizer->xDestroy(p->p);
    sqlite4DbFree(db, p);
  }

Changes to src/insert.c. 

1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432


  /* Write the entry to each index. */
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iPK;
      sqlite4FindPrimaryKey(pTab, &iPK);
      sqlite4Fts5CodeUpdate(pParse, pIdx, aRegIdx[iPK], regContent);
    }
    else if( aRegIdx[i] ){
      int regData = 0;
      int flags = 0;
      if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
        regData = regRec;
        flags = pik_flags;








|








1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432


  /* Write the entry to each index. */
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iPK;
      sqlite4FindPrimaryKey(pTab, &iPK);
      sqlite4Fts5CodeUpdate(pParse, pIdx, aRegIdx[iPK], regContent, 0);
    }
    else if( aRegIdx[i] ){
      int regData = 0;
      int flags = 0;
      if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
        regData = regRec;
        flags = pik_flags;

Changes to src/sqliteInt.h. 

3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277


int sqlite4Fts5IndexSz(void);
void sqlite4Fts5IndexInit(Parse *, Index *, ExprList *);
void sqlite4Fts5IndexFree(sqlite4 *, Index *);

int sqlite4Fts5Update(sqlite4 *, Fts5Info *, Mem *pPk, Mem *aArg, int, char **);
void sqlite4Fts5FreeInfo(sqlite4 *db, Fts5Info *);
void sqlite4Fts5CodeUpdate(Parse *, Index *pIdx, int iRegPk, int iRegData);
void sqlite4Fts5CodeCksum(Parse *, Index *, int, int, int);

#endif /* _SQLITEINT_H_ */








|




3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277


int sqlite4Fts5IndexSz(void);
void sqlite4Fts5IndexInit(Parse *, Index *, ExprList *);
void sqlite4Fts5IndexFree(sqlite4 *, Index *);

int sqlite4Fts5Update(sqlite4 *, Fts5Info *, Mem *pPk, Mem *aArg, int, char **);
void sqlite4Fts5FreeInfo(sqlite4 *db, Fts5Info *);
void sqlite4Fts5CodeUpdate(Parse *, Index *pIdx, int iRegPk, int iRegData, int);
void sqlite4Fts5CodeCksum(Parse *, Index *, int, int, int);

#endif /* _SQLITEINT_H_ */

Changes to src/update.c. 

504
505
506
507
508
509
510
511
512
513
514
515
516
517
518


    /* Delete the index entries associated with the current record.  */
    j1 = sqlite4VdbeAddOp4(v, OP_NotFound, iCur+iPk, 0, regOldKey, 0, P4_INT32);
    sqlite4GenerateRowIndexDelete(pParse, pTab, 0, iCur, aRegIdx);
  
    /* Delete the old record */
    if( hasFK || bChngPk ){
      sqlite4VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite4VdbeJumpHere(v, j1);

    if( hasFK ){
      sqlite4FkCheck(pParse, pTab, 0, regNew);
    }
  








|








504
505
506
507
508
509
510
511
512
513
514
515
516
517
518


    /* Delete the index entries associated with the current record.  */
    j1 = sqlite4VdbeAddOp4(v, OP_NotFound, iCur+iPk, 0, regOldKey, 0, P4_INT32);
    sqlite4GenerateRowIndexDelete(pParse, pTab, 0, iCur, aRegIdx);
  
    /* Delete the old record */
    if( hasFK || bChngPk ){
      sqlite4VdbeAddOp2(v, OP_Delete, iCur+iPk, 0);
    }
    sqlite4VdbeJumpHere(v, j1);

    if( hasFK ){
      sqlite4FkCheck(pParse, pTab, 0, regNew);
    }

Changes to src/where.c. 

3020
3021
3022
3023
3024
3025
3026


3027
3028
3029
3030
3031
3032
3033

    int bDist = !!pDistinct;      /* True if index cannot help with DISTINCT */
    int bLookup = 0;              /* True if not the PK index */
    WhereTerm *pTerm;             /* A single term of the WHERE clause */
#ifdef SQLITE4_ENABLE_STAT3
    WhereTerm *pFirstTerm = 0;    /* First term matching the index */
#endif
    int nCol = pProbe->nColumn;   /* Total columns in index record */



    /* Unless pProbe is the primary key index, then the encoded PK column 
    ** values are at the end of each record. Set variable nCol to the total
    ** number of columns encoded into each index record, including the PK  
    ** columns.  */
    if( pProbe!=pPk ) nCol += pPk->nColumn;









>
>








3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035

    int bDist = !!pDistinct;      /* True if index cannot help with DISTINCT */
    int bLookup = 0;              /* True if not the PK index */
    WhereTerm *pTerm;             /* A single term of the WHERE clause */
#ifdef SQLITE4_ENABLE_STAT3
    WhereTerm *pFirstTerm = 0;    /* First term matching the index */
#endif
    int nCol = pProbe->nColumn;   /* Total columns in index record */

    if( pProbe->eIndexType==SQLITE4_INDEX_FTS5 ) continue;

    /* Unless pProbe is the primary key index, then the encoded PK column 
    ** values are at the end of each record. Set variable nCol to the total
    ** number of columns encoded into each index record, including the PK  
    ** columns.  */
    if( pProbe!=pPk ) nCol += pPk->nColumn;

Added test/fts5query1.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

# 2012 December 17
#
# 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.
#
#*************************************************************************
#
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix fts5query1

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c);
  CREATE INDEX i1 ON t1 USING fts5();
}

foreach {tn stmt} {
  1 "INSERT INTO t1 VALUES(1, 'a b c', 'd e f')"
  2 "INSERT INTO t1 VALUES(2, 'b c e', 'A A a')"
  3 "INSERT INTO t1 VALUES(3, 'd A A', 'e c a')"
  4 "DELETE FROM t1 WHERE a=1"
  5 "DELETE FROM t1"
  6 "INSERT INTO t1 VALUES(1, 'May you do', 'good and not evil')"
  7 "INSERT INTO t1 VALUES(2, 'May you find', 'forgiveness for yourself')"
  8 "UPDATE t1 SET b = 'and forgive others' WHERE a = 2"
  9 "UPDATE t1 SET a = 4 AND c = 'a b c d' WHERE a = 2"
} {
  do_execsql_test 1.$tn.1 $stmt
  do_execsql_test 1.$tn.2 {PRAGMA fts_check(i1)} ok
}

finish_test

This page was generated in about 0.028s by Fossil 2.13 [7de85417de] 2020-09-21 14:21:35