/ Check-in [820c8044]
Login

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

Overview
Comment:Add tests for fts3 and onepass update/delete operations. Also fix a problem with onepass updates that do not affect any rows.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-onepass
Files: files | file ages | folders
SHA1: 820c804468abff692742952de670c5d906a50956
User & Date: dan 2015-09-29 10:11:26
Context
2015-09-29
11:57
Change the name of the new sqlite3_index_info.flags field to "idxFlags". Add documentation for the same to sqlite.h.in. check-in: f61203bc user: dan tags: vtab-onepass
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   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 && !IsVirtual(pTab) ){
          468  +    if( eOnePass!=ONEPASS_OFF ){
   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
................................................................................
   531    531         }
   532    532         sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
   533    533             iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek);
   534    534       }
   535    535     
   536    536       /* End of the loop over all rowids/primary-keys. */
   537    537       if( eOnePass!=ONEPASS_OFF ){
   538         -      if( !IsVirtual(pTab) ){
   539         -        sqlite3VdbeResolveLabel(v, addrBypass);
   540         -        sqlite3WhereEnd(pWInfo);
   541         -      }
          538  +      sqlite3VdbeResolveLabel(v, addrBypass);
          539  +      sqlite3WhereEnd(pWInfo);
   542    540       }else if( pPk ){
   543    541         sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v);
   544    542         sqlite3VdbeJumpHere(v, addrLoop);
   545    543       }else{
   546    544         sqlite3VdbeGoto(v, addrLoop);
   547    545         sqlite3VdbeJumpHere(v, addrLoop);
   548    546       }     

Changes to src/update.c.

   773    773       /* Create a record from the argument register contents and insert it into
   774    774       ** the ephemeral table. */
   775    775       sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
   776    776       sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);
   777    777       sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid);
   778    778     }
   779    779   
   780         -  /* End the virtual table scan */
   781         -  sqlite3WhereEnd(pWInfo);
   782    780   
   783    781     if( bOnePass==0 ){
          782  +    /* End the virtual table scan */
          783  +    sqlite3WhereEnd(pWInfo);
          784  +
   784    785       /* Begin scannning through the ephemeral table. */
   785         -    addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); VdbeCoverage(v);
          786  +    addr = sqlite3VdbeAddOp1(v, OP_Rewind, ephemTab); VdbeCoverage(v);
   786    787   
   787    788       /* Extract arguments from the current row of the ephemeral table and 
   788    789       ** invoke the VUpdate method.  */
   789    790       for(i=0; i<nArg; i++){
   790    791         sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
   791    792       }
   792    793     }
   793    794     sqlite3VtabMakeWritable(pParse, pTab);
   794    795     sqlite3VdbeAddOp4(v, OP_VUpdate, 0, nArg, regArg, pVTab, P4_VTAB);
   795    796     sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
   796    797     sqlite3MayAbort(pParse);
   797    798   
   798         -  /* End of the ephemeral table scan */
          799  +  /* End of the ephemeral table scan. Or, if using the onepass strategy,
          800  +  ** jump to here if the scan visited zero rows. */
   799    801     if( bOnePass==0 ){
   800    802       sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
   801    803       sqlite3VdbeJumpHere(v, addr);
   802    804       sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
          805  +  }else{
          806  +    sqlite3WhereEnd(pWInfo);
   803    807     }
   804    808   }
   805    809   #endif /* SQLITE_OMIT_VIRTUALTABLE */

Changes to src/wherecode.c.

   696    696       VdbeCoverage(v);
   697    697       pLoop->u.vtab.needFree = 0;
   698    698       for(j=0; j<nConstraint && j<16; j++){
   699    699         if( (pLoop->u.vtab.omitMask>>j)&1 ){
   700    700           disableTerm(pLevel, pLoop->aLTerm[j]);
   701    701         }
   702    702       }
   703         -    pLevel->op = OP_VNext;
   704    703       pLevel->p1 = iCur;
          704  +    pLevel->op = pWInfo->eOnePass ? OP_Noop : OP_VNext;
   705    705       pLevel->p2 = sqlite3VdbeCurrentAddr(v);
   706    706       sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
   707    707       sqlite3ExprCachePop(pParse);
   708    708     }else
   709    709   #endif /* SQLITE_OMIT_VIRTUALTABLE */
   710    710   
   711    711     if( (pLoop->wsFlags & WHERE_IPK)!=0

Added test/fts4onepass.test.

            1  +# 2015 Sep 27
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +source $testdir/fts3_common.tcl
           16  +set ::testprefix fts4onepass
           17  +
           18  +# If SQLITE_ENABLE_FTS3 is defined, omit this file.
           19  +ifcapable !fts3 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE VIRTUAL TABLE ft USING fts3;
           26  +  INSERT INTO ft(rowid, content) VALUES(1, '1 2 3');
           27  +  INSERT INTO ft(rowid, content) VALUES(2, '4 5 6');
           28  +  INSERT INTO ft(rowid, content) VALUES(3, '7 8 9');
           29  +}
           30  +
           31  +#-------------------------------------------------------------------------
           32  +# Check that UPDATE and DELETE statements that feature "WHERE rowid=?" or 
           33  +# or "WHERE docid=?" clauses do not use statement journals. But that other
           34  +# DELETE and UPDATE statements do.
           35  +#
           36  +# Note: "MATCH ? AND docid=?" does use a statement journal.
           37  +#
           38  +foreach {tn sql uses} {
           39  +  1.1 { DELETE FROM ft } 1
           40  +  1.2 { DELETE FROM ft WHERE docid=? } 0
           41  +  1.3 { DELETE FROM ft WHERE rowid=? } 0
           42  +  1.4 { DELETE FROM ft WHERE ft MATCH '1' } 1
           43  +  1.5 { DELETE FROM ft WHERE ft MATCH '1' AND docid=? } 1
           44  +  1.6 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1
           45  +
           46  +  2.1 { UPDATE ft SET content='a b c' } 1
           47  +  2.2 { UPDATE ft SET content='a b c' WHERE docid=? } 0
           48  +  2.3 { UPDATE ft SET content='a b c' WHERE rowid=? } 0
           49  +  2.4 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' } 1
           50  +  2.5 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND docid=? } 1
           51  +  2.6 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1
           52  +} {
           53  +  do_test 1.$tn { sql_uses_stmt db $sql } $uses
           54  +}
           55  +
           56  +#-------------------------------------------------------------------------
           57  +# Check that putting a "DELETE/UPDATE ... WHERE rowid=?" statement in a
           58  +# trigger program does not prevent the VM from using a statement 
           59  +# transaction. Even if the calling statement cannot hit a constraint.
           60  +#
           61  +do_execsql_test 2.0 {
           62  +  CREATE TABLE t1(x);
           63  +
           64  +  CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
           65  +    DELETE FROM ft WHERE rowid=new.x;
           66  +  END;
           67  +
           68  +  CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
           69  +    UPDATE ft SET content = 'a b c' WHERE rowid=old.x;
           70  +  END;
           71  +
           72  +  CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
           73  +    DELETE FROM ft WHERE rowid=old.x;
           74  +  END;
           75  +}
           76  +
           77  +foreach {tn sql uses} {
           78  +  1 { INSERT INTO t1 VALUES(1)      } 1
           79  +  2 { DELETE FROM t1 WHERE x=4      } 1
           80  +  3 { UPDATE t1 SET x=10 WHERE x=11 } 1
           81  +} {
           82  +  do_test 2.$tn { sql_uses_stmt db $sql } $uses
           83  +}
           84  +
           85  +#-------------------------------------------------------------------------
           86  +# Test that an "UPDATE ... WHERE rowid=?" works and does not corrupt the
           87  +# index when it strikes a constraint. Both inside and outside a 
           88  +# transaction.
           89  +#
           90  +foreach {tn tcl1 tcl2}  {
           91  +  1 {} {}
           92  +
           93  +  2 {
           94  +    execsql BEGIN
           95  +  } {
           96  +    if {[sqlite3_get_autocommit db]==1} { error "transaction rolled back!" }
           97  +    execsql COMMIT
           98  +  }
           99  +} {
          100  +
          101  +  do_execsql_test 3.$tn.0 {
          102  +    DROP TABLE IF EXISTS ft2;
          103  +    CREATE VIRTUAL TABLE ft2 USING fts4;
          104  +    INSERT INTO ft2(rowid, content) VALUES(1, 'a b c');
          105  +    INSERT INTO ft2(rowid, content) VALUES(2, 'a b d');
          106  +    INSERT INTO ft2(rowid, content) VALUES(3, 'a b e');
          107  +  }
          108  +
          109  +  eval $tcl1
          110  +  foreach {tn2 sql content} {
          111  +    1 { UPDATE ft2 SET docid=2 WHERE docid=1 }
          112  +      { 1 {a b c} 2 {a b d} 3 {a b e} }
          113  +
          114  +    2 { 
          115  +      INSERT INTO ft2(rowid, content) VALUES(4, 'a b f');
          116  +      UPDATE ft2 SET docid=5 WHERE docid=4;
          117  +      UPDATE ft2 SET docid=3 WHERE docid=5;
          118  +    } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
          119  +
          120  +    3 {
          121  +      UPDATE ft2 SET docid=3 WHERE docid=4;           -- matches 0 rows
          122  +      UPDATE ft2 SET docid=2 WHERE docid=3;
          123  +    } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
          124  +
          125  +    4 {
          126  +      INSERT INTO ft2(rowid, content) VALUES(4, 'a b g');
          127  +      UPDATE ft2 SET docid=-1 WHERE docid=4;
          128  +      UPDATE ft2 SET docid=3 WHERE docid=-1;
          129  +    } {-1 {a b g} 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
          130  +
          131  +    5 {
          132  +      DELETE FROM ft2 WHERE rowid=451;
          133  +      DELETE FROM ft2 WHERE rowid=-1;
          134  +      UPDATE ft2 SET docid = 2 WHERE docid = 1;
          135  +    } {1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
          136  +  } {
          137  +    do_catchsql_test 3.$tn.$tn2.a $sql {1 {constraint failed}}
          138  +    do_execsql_test  3.$tn.$tn2.b { SELECT rowid, content FROM ft2 } $content
          139  +    do_execsql_test  3.$tn.$tn2.c { 
          140  +      INSERT INTO ft2(ft2) VALUES('integrity-check');
          141  +    }
          142  +  }
          143  +  eval $tcl2
          144  +}
          145  +
          146  +finish_test
          147  +