/ Check-in [e73f919f]
Login

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

Overview
Comment:Changes to allow DELETE operations on virtual tables to use the onepass strategy under some circumstances.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-onepass
Files: files | file ages | folders
SHA1: e73f919fae1833c6ffb36eddbc76d9a8d9324214
User & Date: dan 2015-09-28 15:20:58
Context
2015-09-28
15:23
Update fts3 to use the onepass strategy for delete operations. check-in: fffab4f7 user: dan tags: vtab-onepass
15:20
Changes to allow DELETE operations on virtual tables to use the onepass strategy under some circumstances. check-in: e73f919f user: dan tags: vtab-onepass
15:08
Add test cases to the ONEPASS optimization corruption problem fixed by the previous check-in. check-in: 5c14d447 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   188    188             OP_Transaction,                    /* Opcode */
   189    189             iDb,                               /* P1 */
   190    190             DbMaskTest(pParse->writeMask,iDb), /* P2 */
   191    191             pParse->cookieValue[iDb],          /* P3 */
   192    192             db->aDb[iDb].pSchema->iGeneration  /* P4 */
   193    193           );
   194    194           if( db->init.busy==0 ) sqlite3VdbeChangeP5(v, 1);
          195  +        VdbeComment((v,
          196  +              "usesStmtJournal=%d", pParse->mayAbort && pParse->isMultiWrite));
   195    197         }
   196    198   #ifndef SQLITE_OMIT_VIRTUALTABLE
   197    199         for(i=0; i<pParse->nVtabLock; i++){
   198    200           char *vtab = (char *)sqlite3GetVTable(db, pParse->apVtabLock[i]);
   199    201           sqlite3VdbeAddOp4(v, OP_VBegin, 0, 0, 0, vtab, P4_VTAB);
   200    202         }
   201    203         pParse->nVtabLock = 0;

Changes to src/delete.c.

   407    407       **  ONEPASS_OFF:    Two-pass approach - use a FIFO for rowids/PK values.
   408    408       **  ONEPASS_SINGLE: One-pass approach - at most one row deleted.
   409    409       **  ONEPASS_MULTI:  One-pass approach - any number of rows may be deleted.
   410    410       */
   411    411       pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1);
   412    412       if( pWInfo==0 ) goto delete_from_cleanup;
   413    413       eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   414         -    assert( IsVirtual(pTab)==0 || eOnePass==ONEPASS_OFF );
          414  +    assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI );
   415    415       assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF );
   416    416     
   417    417       /* Keep track of the number of rows to be deleted */
   418    418       if( db->flags & SQLITE_CountRows ){
   419    419         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   420    420       }
   421    421     
................................................................................
   461    461           nKey = 1;  /* OP_Seek always uses a single rowid */
   462    462           sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey);
   463    463         }
   464    464       }
   465    465     
   466    466       /* If this DELETE cannot use the ONEPASS strategy, this is the 
   467    467       ** end of the WHERE loop */
   468         -    if( eOnePass!=ONEPASS_OFF ){
          468  +    if( eOnePass!=ONEPASS_OFF && !IsVirtual(pTab) ){
   469    469         addrBypass = sqlite3VdbeMakeLabel(v);
   470    470       }else{
   471    471         sqlite3WhereEnd(pWInfo);
   472    472       }
   473    473     
   474    474       /* Unless this is a view, open cursors for the table we are 
   475    475       ** deleting from and all its indices. If this is a view, then the
................................................................................
   490    490       }
   491    491     
   492    492       /* Set up a loop over the rowids/primary-keys that were found in the
   493    493       ** where-clause loop above.
   494    494       */
   495    495       if( eOnePass!=ONEPASS_OFF ){
   496    496         assert( nKey==nPk );  /* OP_Found will use an unpacked key */
   497         -      if( aToOpen[iDataCur-iTabCur] ){
          497  +      if( !IsVirtual(pTab) && aToOpen[iDataCur-iTabCur] ){
   498    498           assert( pPk!=0 || pTab->pSelect!=0 );
   499    499           sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey);
   500    500           VdbeCoverage(v);
   501    501         }
   502    502       }else if( pPk ){
   503    503         addrLoop = sqlite3VdbeAddOp1(v, OP_Rewind, iEphCur); VdbeCoverage(v);
   504    504         sqlite3VdbeAddOp2(v, OP_RowKey, iEphCur, iKey);
................................................................................
   512    512       /* Delete the row */
   513    513   #ifndef SQLITE_OMIT_VIRTUALTABLE
   514    514       if( IsVirtual(pTab) ){
   515    515         const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
   516    516         sqlite3VtabMakeWritable(pParse, pTab);
   517    517         sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iKey, pVTab, P4_VTAB);
   518    518         sqlite3VdbeChangeP5(v, OE_Abort);
          519  +      assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE );
   519    520         sqlite3MayAbort(pParse);
          521  +      if( eOnePass==ONEPASS_SINGLE && pParse==sqlite3ParseToplevel(pParse) ){
          522  +        pParse->isMultiWrite = 0;
          523  +      }
   520    524       }else
   521    525   #endif
   522    526       {
   523    527         int count = (pParse->nested==0);    /* True to count changes */
   524    528         int iIdxNoSeek = -1;
   525    529         if( bComplex==0 && aiCurOnePass[1]!=iDataCur ){
   526    530           iIdxNoSeek = aiCurOnePass[1];
................................................................................
   527    531         }
   528    532         sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
   529    533             iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek);
   530    534       }
   531    535     
   532    536       /* End of the loop over all rowids/primary-keys. */
   533    537       if( eOnePass!=ONEPASS_OFF ){
   534         -      sqlite3VdbeResolveLabel(v, addrBypass);
   535         -      sqlite3WhereEnd(pWInfo);
          538  +      if( !IsVirtual(pTab) ){
          539  +        sqlite3VdbeResolveLabel(v, addrBypass);
          540  +        sqlite3WhereEnd(pWInfo);
          541  +      }
   536    542       }else if( pPk ){
   537    543         sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v);
   538    544         sqlite3VdbeJumpHere(v, addrLoop);
   539    545       }else{
   540    546         sqlite3VdbeGoto(v, addrLoop);
   541    547         sqlite3VdbeJumpHere(v, addrLoop);
   542    548       }     

Changes to src/sqlite.h.in.

  5664   5664     int idxNum;                /* Number used to identify the index */
  5665   5665     char *idxStr;              /* String, possibly obtained from sqlite3_malloc */
  5666   5666     int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
  5667   5667     int orderByConsumed;       /* True if output is already ordered */
  5668   5668     double estimatedCost;           /* Estimated cost of using this index */
  5669   5669     /* Fields below are only available in SQLite 3.8.2 and later */
  5670   5670     sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
         5671  +  /* Fields below are only available in SQLite 3.8.12 and later */
         5672  +  int flags;                 /* Mask of SQLITE_INDEX_SCAN_* flags */
  5671   5673   };
  5672   5674   
         5675  +/*
         5676  +** CAPI3REF: Virtual Table Scan Flags
         5677  +*/
         5678  +#define SQLITE_INDEX_SCAN_UNIQUE      1     /* Scan visits at most 1 row */
         5679  +
  5673   5680   /*
  5674   5681   ** CAPI3REF: Virtual Table Constraint Operator Codes
  5675   5682   **
  5676   5683   ** These macros defined the allowed values for the
  5677   5684   ** [sqlite3_index_info].aConstraint[].op field.  Each value represents
  5678   5685   ** an operator that is part of a constraint term in the wHERE clause of
  5679   5686   ** a query that uses a [virtual table].

Changes to src/where.c.

  2828   2828       if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
  2829   2829       pIdxInfo->idxStr = 0;
  2830   2830       pIdxInfo->idxNum = 0;
  2831   2831       pIdxInfo->needToFreeIdxStr = 0;
  2832   2832       pIdxInfo->orderByConsumed = 0;
  2833   2833       pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
  2834   2834       pIdxInfo->estimatedRows = 25;
         2835  +    pIdxInfo->flags = 0;
  2835   2836       rc = vtabBestIndex(pParse, pTab, pIdxInfo);
  2836   2837       if( rc ) goto whereLoopAddVtab_exit;
  2837   2838       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2838   2839       pNew->prereq = mExtra;
  2839   2840       mxTerm = -1;
  2840   2841       assert( pNew->nLSlot>=nConstraint );
  2841   2842       for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
................................................................................
  2873   2874             }
  2874   2875             /* A virtual table that is constrained by an IN clause may not
  2875   2876             ** consume the ORDER BY clause because (1) the order of IN terms
  2876   2877             ** is not necessarily related to the order of output terms and
  2877   2878             ** (2) Multiple outputs from a single IN value will not merge
  2878   2879             ** together.  */
  2879   2880             pIdxInfo->orderByConsumed = 0;
         2881  +          pIdxInfo->flags &= ~SQLITE_INDEX_SCAN_UNIQUE;
  2880   2882           }
  2881   2883         }
  2882   2884       }
  2883   2885       if( i>=nConstraint ){
  2884   2886         pNew->nLTerm = mxTerm+1;
  2885   2887         assert( pNew->nLTerm<=pNew->nLSlot );
  2886   2888         pNew->u.vtab.idxNum = pIdxInfo->idxNum;
................................................................................
  2888   2890         pIdxInfo->needToFreeIdxStr = 0;
  2889   2891         pNew->u.vtab.idxStr = pIdxInfo->idxStr;
  2890   2892         pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
  2891   2893                                         pIdxInfo->nOrderBy : 0);
  2892   2894         pNew->rSetup = 0;
  2893   2895         pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
  2894   2896         pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
         2897  +
         2898  +      /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated
         2899  +      ** that the scan will visit at most one row. Clear it otherwise. */
         2900  +      if( pIdxInfo->flags & SQLITE_INDEX_SCAN_UNIQUE ){
         2901  +        pNew->wsFlags |= WHERE_ONEROW;
         2902  +      }else{
         2903  +        pNew->wsFlags &= ~WHERE_ONEROW;
         2904  +      }
  2895   2905         whereLoopInsert(pBuilder, pNew);
  2896   2906         if( pNew->u.vtab.needFree ){
  2897   2907           sqlite3_free(pNew->u.vtab.idxStr);
  2898   2908           pNew->u.vtab.needFree = 0;
  2899   2909         }
  2900   2910       }
  2901   2911     }