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
Side-by-Side 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
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)
   || (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
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);

#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. */
  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;
    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
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
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 strategy is that we create an ephemeral table that contains
** 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. (note1)
**   (B)  The revised rowid for the row.
**   (C)  The content of every column in the row.
**
** Then we loop over this ephemeral table and for each row in
** Then loop through the contents of this ephemeral table executing a
** the ephemeral table call VUpdate.
**
** When finished, drop the ephemeral table.
** VUpdate for each row. When finished, drop the ephemeral table.
**
** The "onepass" strategy does not use an ephemeral table. Instead, it
** (note1) Actually, if we know in advance that (A) is always the same
** stores the same values (A, B and C above) in a register array and
** as (B) we only store (A), then duplicate (A) when pulling
** it out of the ephemeral table before calling VUpdate.
** 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 */
  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;

  WhereInfo *pWInfo;
  /* Construct the SELECT statement that will find the new values for
  ** all updated rows. 
  int nArg = 2 + pTab->nCol;      /* Number of arguments to VUpdate */
  int regArg;                     /* First register in VUpdate arg array */
  */
  pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, "_rowid_"));
  if( pRowid ){
  int regRec;                     /* Register in which to assemble record */
  int regRowid;                   /* Register for ephem table rowid */
    pEList = sqlite3ExprListAppend(pParse, pEList,
                                   sqlite3ExprDup(db, pRowid, 0));
  int iCsr = pSrc->a[0].iCursor;  /* Cursor used for virtual table scan */
  int aDummy[2];                  /* Unused arg for sqlite3WhereOkOnePass() */
  }
  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);
    }
  int bOnePass;                   /* True to use onepass strategy */
  int addr;                       /* Address of OP_OpenEphemeral */
  NameContext sNC;

    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.
  /* 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;

  /* 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);
  /* 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);

  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);
  }
    /* 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, pTab->nCol+2, iReg, pVTab, P4_VTAB);
  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);

    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 */
Changes to test/fts3conf.test.
82
83
84
85
86
87
88
89
90
91
92
93





94
95
96
97
98
99
100
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}}
  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
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}
} {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 463}
} {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}