/ Check-in [1aa27d70]
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 | SQL archive
Timelines: family | ancestors | descendants | both | vtab-onepass
Files: files | file ages | folders
SHA1: 1aa27d706db9b2e21737ce4b94b47ecd12c2570f
User & Date: dan 2015-09-28 20:03:49
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: 820c8044 user: dan tags: vtab-onepass
2015-09-28
20:03
Also allow UPDATE on virtual tables to use the onepass strategy. check-in: 1aa27d70 user: dan tags: vtab-onepass
15:23
Update fts3 to use the onepass strategy for delete operations. check-in: fffab4f7 user: dan tags: vtab-onepass
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3_write.c.

   870    870     /* TODO(shess) Explore whether partially flushing the buffer on
   871    871     ** forced-flush would provide better performance.  I suspect that if
   872    872     ** we ordered the doclists by size and flushed the largest until the
   873    873     ** buffer was half empty, that would let the less frequent terms
   874    874     ** generate longer doclists.
   875    875     */
   876    876     if( iDocid<p->iPrevDocid 
   877         -   || (iDocid==p->iPrevLangid && p->bPrevDelete==0)
          877  +   || (iDocid==p->iPrevDocid && p->bPrevDelete==0)
   878    878      || p->iPrevLangid!=iLangid
   879    879      || p->nPendingData>p->nMaxPendingData 
   880    880     ){
   881    881       int rc = sqlite3Fts3PendingTermsFlush(p);
   882    882       if( rc!=SQLITE_OK ) return rc;
   883    883     }
   884    884     p->iPrevDocid = iDocid;

Changes to src/update.c.

   296    296   
   297    297     /* Begin generating code. */
   298    298     v = sqlite3GetVdbe(pParse);
   299    299     if( v==0 ) goto update_cleanup;
   300    300     if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
   301    301     sqlite3BeginWriteOperation(pParse, 1, iDb);
   302    302   
   303         -#ifndef SQLITE_OMIT_VIRTUALTABLE
   304         -  /* Virtual tables must be handled separately */
   305         -  if( IsVirtual(pTab) ){
   306         -    updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
   307         -                       pWhere, onError);
   308         -    pWhere = 0;
   309         -    pTabList = 0;
   310         -    goto update_cleanup;
   311         -  }
   312         -#endif
   313         -
   314    303     /* Allocate required registers. */
   315         -  regRowSet = ++pParse->nMem;
   316         -  regOldRowid = regNewRowid = ++pParse->nMem;
   317         -  if( chngPk || pTrigger || hasFK ){
   318         -    regOld = pParse->nMem + 1;
          304  +  if( !IsVirtual(pTab) ){
          305  +    regRowSet = ++pParse->nMem;
          306  +    regOldRowid = regNewRowid = ++pParse->nMem;
          307  +    if( chngPk || pTrigger || hasFK ){
          308  +      regOld = pParse->nMem + 1;
          309  +      pParse->nMem += pTab->nCol;
          310  +    }
          311  +    if( chngKey || pTrigger || hasFK ){
          312  +      regNewRowid = ++pParse->nMem;
          313  +    }
          314  +    regNew = pParse->nMem + 1;
   319    315       pParse->nMem += pTab->nCol;
   320    316     }
   321         -  if( chngKey || pTrigger || hasFK ){
   322         -    regNewRowid = ++pParse->nMem;
   323         -  }
   324         -  regNew = pParse->nMem + 1;
   325         -  pParse->nMem += pTab->nCol;
   326    317   
   327    318     /* Start the view context. */
   328    319     if( isView ){
   329    320       sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
   330    321     }
   331    322   
   332    323     /* If we are trying to update a view, realize that view into
................................................................................
   340    331   
   341    332     /* Resolve the column names in all the expressions in the
   342    333     ** WHERE clause.
   343    334     */
   344    335     if( sqlite3ResolveExprNames(&sNC, pWhere) ){
   345    336       goto update_cleanup;
   346    337     }
          338  +
          339  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          340  +  /* Virtual tables must be handled separately */
          341  +  if( IsVirtual(pTab) ){
          342  +    updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
          343  +                       pWhere, onError);
          344  +    goto update_cleanup;
          345  +  }
          346  +#endif
   347    347   
   348    348     /* Begin the database scan
   349    349     */
   350    350     if( HasRowid(pTab) ){
   351    351       sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
   352    352       pWInfo = sqlite3WhereBegin(
   353    353           pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur
................................................................................
   681    681    #undef pTrigger
   682    682   #endif
   683    683   
   684    684   #ifndef SQLITE_OMIT_VIRTUALTABLE
   685    685   /*
   686    686   ** Generate code for an UPDATE of a virtual table.
   687    687   **
   688         -** The strategy is that we create an ephemeral table that contains
          688  +** There are two possible strategies - the default and the special 
          689  +** "onepass" strategy. Onepass is only used if the virtual table 
          690  +** implementation indicates that pWhere may match at most one row.
          691  +**
          692  +** The default strategy is to create an ephemeral table that contains
   689    693   ** for each row to be changed:
   690    694   **
   691    695   **   (A)  The original rowid of that row.
   692         -**   (B)  The revised rowid for the row. (note1)
          696  +**   (B)  The revised rowid for the row.
   693    697   **   (C)  The content of every column in the row.
   694    698   **
   695         -** Then we loop over this ephemeral table and for each row in
   696         -** the ephemeral table call VUpdate.
          699  +** Then loop through the contents of this ephemeral table executing a
          700  +** VUpdate for each row. When finished, drop the ephemeral table.
   697    701   **
   698         -** When finished, drop the ephemeral table.
   699         -**
   700         -** (note1) Actually, if we know in advance that (A) is always the same
   701         -** as (B) we only store (A), then duplicate (A) when pulling
   702         -** it out of the ephemeral table before calling VUpdate.
          702  +** The "onepass" strategy does not use an ephemeral table. Instead, it
          703  +** stores the same values (A, B and C above) in a register array and
          704  +** makes a single invocation of VUpdate.
   703    705   */
   704    706   static void updateVirtualTable(
   705    707     Parse *pParse,       /* The parsing context */
   706    708     SrcList *pSrc,       /* The virtual table to be modified */
   707    709     Table *pTab,         /* The virtual table */
   708    710     ExprList *pChanges,  /* The columns to change in the UPDATE statement */
   709    711     Expr *pRowid,        /* Expression used to recompute the rowid */
   710    712     int *aXRef,          /* Mapping from columns of pTab to entries in pChanges */
   711    713     Expr *pWhere,        /* WHERE clause of the UPDATE statement */
   712    714     int onError          /* ON CONFLICT strategy */
   713    715   ){
   714    716     Vdbe *v = pParse->pVdbe;  /* Virtual machine under construction */
   715         -  ExprList *pEList = 0;     /* The result set of the SELECT statement */
   716         -  Select *pSelect = 0;      /* The SELECT statement */
   717         -  Expr *pExpr;              /* Temporary expression */
   718    717     int ephemTab;             /* Table holding the result of the SELECT */
   719    718     int i;                    /* Loop counter */
   720         -  int addr;                 /* Address of top of loop */
   721         -  int iReg;                 /* First register in set passed to OP_VUpdate */
   722    719     sqlite3 *db = pParse->db; /* Database connection */
   723    720     const char *pVTab = (const char*)sqlite3GetVTable(db, pTab);
   724         -  SelectDest dest;
          721  +  WhereInfo *pWInfo;
          722  +  int nArg = 2 + pTab->nCol;      /* Number of arguments to VUpdate */
          723  +  int regArg;                     /* First register in VUpdate arg array */
          724  +  int regRec;                     /* Register in which to assemble record */
          725  +  int regRowid;                   /* Register for ephem table rowid */
          726  +  int iCsr = pSrc->a[0].iCursor;  /* Cursor used for virtual table scan */
          727  +  int aDummy[2];                  /* Unused arg for sqlite3WhereOkOnePass() */
          728  +  int bOnePass;                   /* True to use onepass strategy */
          729  +  int addr;                       /* Address of OP_OpenEphemeral */
          730  +  NameContext sNC;
   725    731   
   726         -  /* Construct the SELECT statement that will find the new values for
   727         -  ** all updated rows. 
   728         -  */
   729         -  pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, "_rowid_"));
   730         -  if( pRowid ){
   731         -    pEList = sqlite3ExprListAppend(pParse, pEList,
   732         -                                   sqlite3ExprDup(db, pRowid, 0));
   733         -  }
   734         -  assert( pTab->iPKey<0 );
   735         -  for(i=0; i<pTab->nCol; i++){
   736         -    if( aXRef[i]>=0 ){
   737         -      pExpr = sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0);
   738         -    }else{
   739         -      pExpr = sqlite3Expr(db, TK_ID, pTab->aCol[i].zName);
   740         -    }
   741         -    pEList = sqlite3ExprListAppend(pParse, pEList, pExpr);
   742         -  }
   743         -  pSelect = sqlite3SelectNew(pParse, pEList, pSrc, pWhere, 0, 0, 0, 0, 0, 0);
   744         -  
   745         -  /* Create the ephemeral table into which the update results will
   746         -  ** be stored.
   747         -  */
          732  +  /* Allocate nArg registers to martial the arguments to VUpdate. Then
          733  +  ** create and open the ephemeral table in which the records created from
          734  +  ** these arguments will be temporarily stored. */
   748    735     assert( v );
   749    736     ephemTab = pParse->nTab++;
          737  +  addr= sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, nArg);
          738  +  regArg = pParse->nMem + 1;
          739  +  pParse->nMem += nArg;
          740  +  regRec = ++pParse->nMem;
          741  +  regRowid = ++pParse->nMem;
   750    742   
   751         -  /* fill the ephemeral table 
   752         -  */
   753         -  sqlite3SelectDestInit(&dest, SRT_EphemTab, ephemTab);
   754         -  sqlite3Select(pParse, pSelect, &dest);
          743  +  /* Start scanning the virtual table */
          744  +  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0,0,WHERE_ONEPASS_DESIRED,0);
          745  +  if( pWInfo==0 ) return;
   755    746   
   756         -  /* Generate code to scan the ephemeral table and call VUpdate. */
   757         -  iReg = ++pParse->nMem;
   758         -  pParse->nMem += pTab->nCol+1;
   759         -  addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); VdbeCoverage(v);
   760         -  sqlite3VdbeAddOp3(v, OP_Column,  ephemTab, 0, iReg);
   761         -  sqlite3VdbeAddOp3(v, OP_Column, ephemTab, (pRowid?1:0), iReg+1);
          747  +  /* Populate the argument registers. */
          748  +  sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg);
          749  +  if( pRowid ){
          750  +    sqlite3ExprCode(pParse, pRowid, regArg+1);
          751  +  }else{
          752  +    sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg+1);
          753  +  }
   762    754     for(i=0; i<pTab->nCol; i++){
   763         -    sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i+1+(pRowid!=0), iReg+2+i);
          755  +    if( aXRef[i]>=0 ){
          756  +      sqlite3ExprCode(pParse, pChanges->a[aXRef[i]].pExpr, regArg+2+i);
          757  +    }else{
          758  +      sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, i, regArg+2+i);
          759  +    }
          760  +  }
          761  +
          762  +  bOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy);
          763  +
          764  +  if( bOnePass ){
          765  +    /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded
          766  +    ** above. Also, if this is a top-level parse (not a trigger), clear the
          767  +    ** multi-write flag so that the VM does not open a statement journal */
          768  +    sqlite3VdbeChangeToNoop(v, addr);
          769  +    if( sqlite3ParseToplevel(pParse)==pParse ){
          770  +      pParse->isMultiWrite = 0;
          771  +    }
          772  +  }else{
          773  +    /* Create a record from the argument register contents and insert it into
          774  +    ** the ephemeral table. */
          775  +    sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
          776  +    sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);
          777  +    sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid);
          778  +  }
          779  +
          780  +  /* End the virtual table scan */
          781  +  sqlite3WhereEnd(pWInfo);
          782  +
          783  +  if( bOnePass==0 ){
          784  +    /* Begin scannning through the ephemeral table. */
          785  +    addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); VdbeCoverage(v);
          786  +
          787  +    /* Extract arguments from the current row of the ephemeral table and 
          788  +    ** invoke the VUpdate method.  */
          789  +    for(i=0; i<nArg; i++){
          790  +      sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
          791  +    }
   764    792     }
   765    793     sqlite3VtabMakeWritable(pParse, pTab);
   766         -  sqlite3VdbeAddOp4(v, OP_VUpdate, 0, pTab->nCol+2, iReg, pVTab, P4_VTAB);
          794  +  sqlite3VdbeAddOp4(v, OP_VUpdate, 0, nArg, regArg, pVTab, P4_VTAB);
   767    795     sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
   768    796     sqlite3MayAbort(pParse);
   769         -  sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
   770         -  sqlite3VdbeJumpHere(v, addr);
   771         -  sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
   772    797   
   773         -  /* Cleanup */
   774         -  sqlite3SelectDelete(db, pSelect);  
          798  +  /* End of the ephemeral table scan */
          799  +  if( bOnePass==0 ){
          800  +    sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
          801  +    sqlite3VdbeJumpHere(v, addr);
          802  +    sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
          803  +  }
   775    804   }
   776    805   #endif /* SQLITE_OMIT_VIRTUALTABLE */

Changes to test/fts3conf.test.

    82     82   
    83     83     6    "INSERT OR ROLLBACK $T2"   1 1 {{a b c d} {e f g h}}
    84     84     7    "INSERT OR ABORT    $T2"   1 1 {{a b c d} {e f g h} {i j k l}}
    85     85     8    "INSERT OR FAIL     $T2"   1 1 {{a b c d} {e f g h} {i j k l} z}
    86     86     9    "INSERT OR IGNORE   $T2"   1 0 {{a b c d} {e f g h} {i j k l} z}
    87     87     10   "INSERT OR REPLACE  $T2"   1 0 {{a b c d} y {i j k l} z}
    88     88   
    89         -  11   "UPDATE OR ROLLBACK $T3"   1 1 {{a b c d} {e f g h}}
    90         -  12   "UPDATE OR ABORT    $T3"   1 1 {{a b c d} {e f g h} {i j k l}}
    91         -  13   "UPDATE OR FAIL     $T3"   1 1 {{a b c d} {e f g h} {i j k l}}
    92         -  14   "UPDATE OR IGNORE   $T3"   1 0 {{a b c d} {e f g h} {i j k l}}
    93         -  15   "UPDATE OR REPLACE  $T3"   1 0 {{a b c d} {i j k l}}
           89  +  11   "UPDATE OR ROLLBACK $T3"   0 1 {{a b c d} {e f g h}}
           90  +  12   "UPDATE OR ABORT    $T3"   0 1 {{a b c d} {e f g h} {i j k l}}
           91  +  13   "UPDATE OR FAIL     $T3"   0 1 {{a b c d} {e f g h} {i j k l}}
           92  +  14   "UPDATE OR IGNORE   $T3"   0 0 {{a b c d} {e f g h} {i j k l}}
           93  +  15   "UPDATE OR REPLACE  $T3"   0 0 {{a b c d} {i j k l}}
    94     94   
    95     95     16   "UPDATE OR ROLLBACK $T4"   1 1 {{a b c d} {e f g h}}
    96     96     17   "UPDATE OR ABORT    $T4"   1 1 {{a b c d} {e f g h} {i j k l}}
    97     97     18   "UPDATE OR FAIL     $T4"   1 1 {{e f g h} {i j k l} {a b c d}}
    98     98     19   "UPDATE OR IGNORE   $T4"   1 0 {{e f g h} {i j k l} {a b c d}}
    99     99     20   "UPDATE OR REPLACE  $T4"   1 0 {{e f g h} {a b c d}}
   100    100   }] {

Changes to test/fts4growth.test.

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