SQLite

Check-in [1aa27d706d]
Login

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

Overview
Comment:Also allow UPDATE on virtual tables to use the onepass strategy.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | vtab-onepass
Files: files | file ages | folders
SHA1: 1aa27d706db9b2e21737ce4b94b47ecd12c2570f
User & Date: dan 2015-09-28 20:03:49.551
Context
2015-09-29
10:11
Add tests for fts3 and onepass update/delete operations. Also fix a problem with onepass updates that do not affect any rows. (check-in: 820c804468 user: dan tags: vtab-onepass)
2015-09-28
20:03
Also allow UPDATE on virtual tables to use the onepass strategy. (check-in: 1aa27d706d user: dan tags: vtab-onepass)
15:23
Update fts3 to use the onepass strategy for delete operations. (check-in: fffab4f70f user: dan tags: vtab-onepass)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/fts3/fts3_write.c.
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
  /* TODO(shess) Explore whether partially flushing the buffer on
  ** forced-flush would provide better performance.  I suspect that if
  ** we ordered the doclists by size and flushed the largest until the
  ** buffer was half empty, that would let the less frequent terms
  ** generate longer doclists.
  */
  if( iDocid<p->iPrevDocid 
   || (iDocid==p->iPrevLangid && p->bPrevDelete==0)
   || p->iPrevLangid!=iLangid
   || p->nPendingData>p->nMaxPendingData 
  ){
    int rc = sqlite3Fts3PendingTermsFlush(p);
    if( rc!=SQLITE_OK ) return rc;
  }
  p->iPrevDocid = iDocid;







|







870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
  /* TODO(shess) Explore whether partially flushing the buffer on
  ** forced-flush would provide better performance.  I suspect that if
  ** we ordered the doclists by size and flushed the largest until the
  ** buffer was half empty, that would let the less frequent terms
  ** generate longer doclists.
  */
  if( iDocid<p->iPrevDocid 
   || (iDocid==p->iPrevDocid && p->bPrevDelete==0)
   || p->iPrevLangid!=iLangid
   || p->nPendingData>p->nMaxPendingData 
  ){
    int rc = sqlite3Fts3PendingTermsFlush(p);
    if( rc!=SQLITE_OK ) return rc;
  }
  p->iPrevDocid = iDocid;
Changes to src/update.c.
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314

315
316
317
318
319
320
321
322
323
324
325

326
327
328
329
330
331
332

  /* Begin generating code. */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto update_cleanup;
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, 1, iDb);

#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Virtual tables must be handled separately */
  if( IsVirtual(pTab) ){
    updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
                       pWhere, onError);
    pWhere = 0;
    pTabList = 0;
    goto update_cleanup;
  }
#endif

  /* Allocate required registers. */

  regRowSet = ++pParse->nMem;
  regOldRowid = regNewRowid = ++pParse->nMem;
  if( chngPk || pTrigger || hasFK ){
    regOld = pParse->nMem + 1;
    pParse->nMem += pTab->nCol;
  }
  if( chngKey || pTrigger || hasFK ){
    regNewRowid = ++pParse->nMem;
  }
  regNew = pParse->nMem + 1;
  pParse->nMem += pTab->nCol;


  /* Start the view context. */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

  /* If we are trying to update a view, realize that view into







<
<
<
<
<
<
<
<
<
<
<

>
|
|
|
|
|
|
|
|
|
|
|
>







296
297
298
299
300
301
302











303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323

  /* Begin generating code. */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto update_cleanup;
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, 1, iDb);












  /* Allocate required registers. */
  if( !IsVirtual(pTab) ){
    regRowSet = ++pParse->nMem;
    regOldRowid = regNewRowid = ++pParse->nMem;
    if( chngPk || pTrigger || hasFK ){
      regOld = pParse->nMem + 1;
      pParse->nMem += pTab->nCol;
    }
    if( chngKey || pTrigger || hasFK ){
      regNewRowid = ++pParse->nMem;
    }
    regNew = pParse->nMem + 1;
    pParse->nMem += pTab->nCol;
  }

  /* Start the view context. */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

  /* If we are trying to update a view, realize that view into
340
341
342
343
344
345
346









347
348
349
350
351
352
353

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( sqlite3ResolveExprNames(&sNC, pWhere) ){
    goto update_cleanup;
  }










  /* Begin the database scan
  */
  if( HasRowid(pTab) ){
    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
    pWInfo = sqlite3WhereBegin(
        pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur







>
>
>
>
>
>
>
>
>







331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( sqlite3ResolveExprNames(&sNC, pWhere) ){
    goto update_cleanup;
  }

#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Virtual tables must be handled separately */
  if( IsVirtual(pTab) ){
    updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
                       pWhere, onError);
    goto update_cleanup;
  }
#endif

  /* Begin the database scan
  */
  if( HasRowid(pTab) ){
    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
    pWInfo = sqlite3WhereBegin(
        pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur
681
682
683
684
685
686
687




688
689
690
691
692
693
694
695
696
697
698
699

700
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
 #undef pTrigger
#endif

#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Generate code for an UPDATE of a virtual table.
**




** The strategy is that we create an ephemeral table that contains
** for each row to be changed:
**
**   (A)  The original rowid of that row.
**   (B)  The revised rowid for the row. (note1)
**   (C)  The content of every column in the row.
**
** Then we loop over this ephemeral table and for each row in
** the ephemeral table call VUpdate.
**
** When finished, drop the ephemeral table.
**

** (note1) Actually, if we know in advance that (A) is always the same
** as (B) we only store (A), then duplicate (A) when pulling
** it out of the ephemeral table before calling VUpdate.
*/
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
  SrcList *pSrc,       /* The virtual table to be modified */
  Table *pTab,         /* The virtual table */
  ExprList *pChanges,  /* The columns to change in the UPDATE statement */
  Expr *pRowid,        /* Expression used to recompute the rowid */
  int *aXRef,          /* Mapping from columns of pTab to entries in pChanges */
  Expr *pWhere,        /* WHERE clause of the UPDATE statement */
  int onError          /* ON CONFLICT strategy */
){
  Vdbe *v = pParse->pVdbe;  /* Virtual machine under construction */
  ExprList *pEList = 0;     /* The result set of the SELECT statement */
  Select *pSelect = 0;      /* The SELECT statement */
  Expr *pExpr;              /* Temporary expression */
  int ephemTab;             /* Table holding the result of the SELECT */
  int i;                    /* Loop counter */
  int addr;                 /* Address of top of loop */
  int iReg;                 /* First register in set passed to OP_VUpdate */
  sqlite3 *db = pParse->db; /* Database connection */
  const char *pVTab = (const char*)sqlite3GetVTable(db, pTab);
  SelectDest dest;

  /* Construct the SELECT statement that will find the new values for

  ** all updated rows. 
  */
  pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, "_rowid_"));

  if( pRowid ){
    pEList = sqlite3ExprListAppend(pParse, pEList,

                                   sqlite3ExprDup(db, pRowid, 0));
  }
  assert( pTab->iPKey<0 );
  for(i=0; i<pTab->nCol; i++){
    if( aXRef[i]>=0 ){
      pExpr = sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0);
    }else{


      pExpr = sqlite3Expr(db, TK_ID, pTab->aCol[i].zName);
    }
    pEList = sqlite3ExprListAppend(pParse, pEList, pExpr);
  }
  pSelect = sqlite3SelectNew(pParse, pEList, pSrc, pWhere, 0, 0, 0, 0, 0, 0);
  
  /* Create the ephemeral table into which the update results will
  ** be stored.
  */
  assert( v );
  ephemTab = pParse->nTab++;






  /* fill the ephemeral table 


  */














  sqlite3SelectDestInit(&dest, SRT_EphemTab, ephemTab);

  sqlite3Select(pParse, pSelect, &dest);










  /* Generate code to scan the ephemeral table and call VUpdate. */




  iReg = ++pParse->nMem;


  pParse->nMem += pTab->nCol+1;


  addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); VdbeCoverage(v);
  sqlite3VdbeAddOp3(v, OP_Column,  ephemTab, 0, iReg);
  sqlite3VdbeAddOp3(v, OP_Column, ephemTab, (pRowid?1:0), iReg+1);


  for(i=0; i<pTab->nCol; i++){
    sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i+1+(pRowid!=0), iReg+2+i);
  }

  sqlite3VtabMakeWritable(pParse, pTab);
  sqlite3VdbeAddOp4(v, OP_VUpdate, 0, pTab->nCol+2, iReg, pVTab, P4_VTAB);
  sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
  sqlite3MayAbort(pParse);



  sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
  sqlite3VdbeJumpHere(v, addr);
  sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);

  /* Cleanup */
  sqlite3SelectDelete(db, pSelect);  
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */







>
>
>
>
|



|


|
<
<
|

>
|
<
|












<
<
<


<
<


<
|
<
>
|
<
<
>
|
<
>
|
<
<
<
<
<
<
>
>
|
|
<
<
<
|
|
|
<


>
>
>
>
>

|
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|
>
>
>
>
>
>
>
|
>
>
|
>
>
>
>
|
>
>
|
>
>
|
|
<
>
>
|
|
|
>

|


>
>
>
|
|
|
|
<
<


681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699


700
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
798
799
800
801
802
803


804
805
 #undef pTrigger
#endif

#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Generate code for an UPDATE of a virtual table.
**
** There are two possible strategies - the default and the special 
** "onepass" strategy. Onepass is only used if the virtual table 
** implementation indicates that pWhere may match at most one row.
**
** The default strategy is to create an ephemeral table that contains
** for each row to be changed:
**
**   (A)  The original rowid of that row.
**   (B)  The revised rowid for the row.
**   (C)  The content of every column in the row.
**
** Then loop through the contents of this ephemeral table executing a


** VUpdate for each row. When finished, drop the ephemeral table.
**
** The "onepass" strategy does not use an ephemeral table. Instead, it
** stores the same values (A, B and C above) in a register array and

** makes a single invocation of VUpdate.
*/
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
  SrcList *pSrc,       /* The virtual table to be modified */
  Table *pTab,         /* The virtual table */
  ExprList *pChanges,  /* The columns to change in the UPDATE statement */
  Expr *pRowid,        /* Expression used to recompute the rowid */
  int *aXRef,          /* Mapping from columns of pTab to entries in pChanges */
  Expr *pWhere,        /* WHERE clause of the UPDATE statement */
  int onError          /* ON CONFLICT strategy */
){
  Vdbe *v = pParse->pVdbe;  /* Virtual machine under construction */



  int ephemTab;             /* Table holding the result of the SELECT */
  int i;                    /* Loop counter */


  sqlite3 *db = pParse->db; /* Database connection */
  const char *pVTab = (const char*)sqlite3GetVTable(db, pTab);

  WhereInfo *pWInfo;

  int nArg = 2 + pTab->nCol;      /* Number of arguments to VUpdate */
  int regArg;                     /* First register in VUpdate arg array */


  int regRec;                     /* Register in which to assemble record */
  int regRowid;                   /* Register for ephem table rowid */

  int iCsr = pSrc->a[0].iCursor;  /* Cursor used for virtual table scan */
  int aDummy[2];                  /* Unused arg for sqlite3WhereOkOnePass() */






  int bOnePass;                   /* True to use onepass strategy */
  int addr;                       /* Address of OP_OpenEphemeral */
  NameContext sNC;




  /* Allocate nArg registers to martial the arguments to VUpdate. Then
  ** create and open the ephemeral table in which the records created from
  ** these arguments will be temporarily stored. */

  assert( v );
  ephemTab = pParse->nTab++;
  addr= sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, nArg);
  regArg = pParse->nMem + 1;
  pParse->nMem += nArg;
  regRec = ++pParse->nMem;
  regRowid = ++pParse->nMem;

  /* Start scanning the virtual table */
  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0,0,WHERE_ONEPASS_DESIRED,0);
  if( pWInfo==0 ) return;

  /* Populate the argument registers. */
  sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg);
  if( pRowid ){
    sqlite3ExprCode(pParse, pRowid, regArg+1);
  }else{
    sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg+1);
  }
  for(i=0; i<pTab->nCol; i++){
    if( aXRef[i]>=0 ){
      sqlite3ExprCode(pParse, pChanges->a[aXRef[i]].pExpr, regArg+2+i);
    }else{
      sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, i, regArg+2+i);
    }
  }

  bOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy);

  if( bOnePass ){
    /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded
    ** above. Also, if this is a top-level parse (not a trigger), clear the
    ** multi-write flag so that the VM does not open a statement journal */
    sqlite3VdbeChangeToNoop(v, addr);
    if( sqlite3ParseToplevel(pParse)==pParse ){
      pParse->isMultiWrite = 0;
    }
  }else{
    /* Create a record from the argument register contents and insert it into
    ** the ephemeral table. */
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
    sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);
    sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid);
  }

  /* End the virtual table scan */
  sqlite3WhereEnd(pWInfo);

  if( bOnePass==0 ){
    /* Begin scannning through the ephemeral table. */
    addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); VdbeCoverage(v);


    /* Extract arguments from the current row of the ephemeral table and 
    ** invoke the VUpdate method.  */
    for(i=0; i<nArg; i++){
      sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
    }
  }
  sqlite3VtabMakeWritable(pParse, pTab);
  sqlite3VdbeAddOp4(v, OP_VUpdate, 0, nArg, regArg, pVTab, P4_VTAB);
  sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
  sqlite3MayAbort(pParse);

  /* End of the ephemeral table scan */
  if( bOnePass==0 ){
    sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
    sqlite3VdbeJumpHere(v, addr);
    sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
  }


}
#endif /* SQLITE_OMIT_VIRTUALTABLE */
Changes to test/fts3conf.test.
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

  6    "INSERT OR ROLLBACK $T2"   1 1 {{a b c d} {e f g h}}
  7    "INSERT OR ABORT    $T2"   1 1 {{a b c d} {e f g h} {i j k l}}
  8    "INSERT OR FAIL     $T2"   1 1 {{a b c d} {e f g h} {i j k l} z}
  9    "INSERT OR IGNORE   $T2"   1 0 {{a b c d} {e f g h} {i j k l} z}
  10   "INSERT OR REPLACE  $T2"   1 0 {{a b c d} y {i j k l} z}

  11   "UPDATE OR ROLLBACK $T3"   1 1 {{a b c d} {e f g h}}
  12   "UPDATE OR ABORT    $T3"   1 1 {{a b c d} {e f g h} {i j k l}}
  13   "UPDATE OR FAIL     $T3"   1 1 {{a b c d} {e f g h} {i j k l}}
  14   "UPDATE OR IGNORE   $T3"   1 0 {{a b c d} {e f g h} {i j k l}}
  15   "UPDATE OR REPLACE  $T3"   1 0 {{a b c d} {i j k l}}

  16   "UPDATE OR ROLLBACK $T4"   1 1 {{a b c d} {e f g h}}
  17   "UPDATE OR ABORT    $T4"   1 1 {{a b c d} {e f g h} {i j k l}}
  18   "UPDATE OR FAIL     $T4"   1 1 {{e f g h} {i j k l} {a b c d}}
  19   "UPDATE OR IGNORE   $T4"   1 0 {{e f g h} {i j k l} {a b c d}}
  20   "UPDATE OR REPLACE  $T4"   1 0 {{e f g h} {a b c d}}
}] {







|
|
|
|
|







82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

  6    "INSERT OR ROLLBACK $T2"   1 1 {{a b c d} {e f g h}}
  7    "INSERT OR ABORT    $T2"   1 1 {{a b c d} {e f g h} {i j k l}}
  8    "INSERT OR FAIL     $T2"   1 1 {{a b c d} {e f g h} {i j k l} z}
  9    "INSERT OR IGNORE   $T2"   1 0 {{a b c d} {e f g h} {i j k l} z}
  10   "INSERT OR REPLACE  $T2"   1 0 {{a b c d} y {i j k l} z}

  11   "UPDATE OR ROLLBACK $T3"   0 1 {{a b c d} {e f g h}}
  12   "UPDATE OR ABORT    $T3"   0 1 {{a b c d} {e f g h} {i j k l}}
  13   "UPDATE OR FAIL     $T3"   0 1 {{a b c d} {e f g h} {i j k l}}
  14   "UPDATE OR IGNORE   $T3"   0 0 {{a b c d} {e f g h} {i j k l}}
  15   "UPDATE OR REPLACE  $T3"   0 0 {{a b c d} {i j k l}}

  16   "UPDATE OR ROLLBACK $T4"   1 1 {{a b c d} {e f g h}}
  17   "UPDATE OR ABORT    $T4"   1 1 {{a b c d} {e f g h} {i j k l}}
  18   "UPDATE OR FAIL     $T4"   1 1 {{e f g h} {i j k l} {a b c d}}
  19   "UPDATE OR IGNORE   $T4"   1 0 {{e f g h} {i j k l} {a b c d}}
  20   "UPDATE OR REPLACE  $T4"   1 0 {{e f g h} {a b c d}}
}] {
Changes to test/fts4growth.test.
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
} {0}
do_test 3.1.3 {
  db transaction { 
    insert_doc 1 2 3 4 5 6 7 8 9
    delete_doc 9 8 7
  }
  execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 591 0 1 72 0 2 76}
do_test 3.1.4 {
  execsql { INSERT INTO x3(x3) VALUES('optimize') }
  execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 463}

do_test 3.2.1 {
  execsql { DELETE FROM x3 }
  insert_doc 8 7 6 5 4 3 2 1
  delete_doc 7 8
  execsql { SELECT count(*) FROM x3_segdir }
} {10}







|



|







198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
} {0}
do_test 3.1.3 {
  db transaction { 
    insert_doc 1 2 3 4 5 6 7 8 9
    delete_doc 9 8 7
  }
  execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 591 0 1 65 0 2 72 0 3 76}
do_test 3.1.4 {
  execsql { INSERT INTO x3(x3) VALUES('optimize') }
  execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 412}

do_test 3.2.1 {
  execsql { DELETE FROM x3 }
  insert_doc 8 7 6 5 4 3 2 1
  delete_doc 7 8
  execsql { SELECT count(*) FROM x3_segdir }
} {10}