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 |
Timelines: | family | ancestors | descendants | both | vtab-onepass |
Files: | files | file ages | folders |
SHA1: |
820c804468abff692742952de670c5d9 |
User & Date: | dan 2015-09-29 10:11:26.685 |
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: f61203bc0f 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: 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) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
461 462 463 464 465 466 467 | nKey = 1; /* OP_Seek always uses a single rowid */ sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey); } } /* If this DELETE cannot use the ONEPASS strategy, this is the ** end of the WHERE loop */ | | | 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 | nKey = 1; /* OP_Seek always uses a single rowid */ sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey); } } /* If this DELETE cannot use the ONEPASS strategy, this is the ** end of the WHERE loop */ if( eOnePass!=ONEPASS_OFF ){ addrBypass = sqlite3VdbeMakeLabel(v); }else{ sqlite3WhereEnd(pWInfo); } /* Unless this is a view, open cursors for the table we are ** deleting from and all its indices. If this is a view, then the |
︙ | ︙ | |||
531 532 533 534 535 536 537 | } sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek); } /* End of the loop over all rowids/primary-keys. */ if( eOnePass!=ONEPASS_OFF ){ | < | | < | 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 | } sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek); } /* End of the loop over all rowids/primary-keys. */ if( eOnePass!=ONEPASS_OFF ){ sqlite3VdbeResolveLabel(v, addrBypass); sqlite3WhereEnd(pWInfo); }else if( pPk ){ sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v); sqlite3VdbeJumpHere(v, addrLoop); }else{ sqlite3VdbeGoto(v, addrLoop); sqlite3VdbeJumpHere(v, addrLoop); } |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
773 774 775 776 777 778 779 | /* 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); } | > > | | < | | > > > | 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 806 807 808 809 | /* 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); } if( bOnePass==0 ){ /* End the virtual table scan */ sqlite3WhereEnd(pWInfo); /* Begin scannning through the ephemeral table. */ addr = sqlite3VdbeAddOp1(v, OP_Rewind, ephemTab); 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. Or, if using the onepass strategy, ** jump to here if the scan visited zero rows. */ if( bOnePass==0 ){ sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v); sqlite3VdbeJumpHere(v, addr); sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0); }else{ sqlite3WhereEnd(pWInfo); } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ |
Changes to src/wherecode.c.
︙ | ︙ | |||
696 697 698 699 700 701 702 | VdbeCoverage(v); pLoop->u.vtab.needFree = 0; for(j=0; j<nConstraint && j<16; j++){ if( (pLoop->u.vtab.omitMask>>j)&1 ){ disableTerm(pLevel, pLoop->aLTerm[j]); } } | < > | 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 | VdbeCoverage(v); pLoop->u.vtab.needFree = 0; for(j=0; j<nConstraint && j<16; j++){ if( (pLoop->u.vtab.omitMask>>j)&1 ){ disableTerm(pLevel, pLoop->aLTerm[j]); } } pLevel->p1 = iCur; pLevel->op = pWInfo->eOnePass ? OP_Noop : OP_VNext; pLevel->p2 = sqlite3VdbeCurrentAddr(v); sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); sqlite3ExprCachePop(pParse); }else #endif /* SQLITE_OMIT_VIRTUALTABLE */ if( (pLoop->wsFlags & WHERE_IPK)!=0 |
︙ | ︙ |
Added test/fts4onepass.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | # 2015 Sep 27 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/fts3_common.tcl set ::testprefix fts4onepass # If SQLITE_ENABLE_FTS3 is defined, omit this file. ifcapable !fts3 { finish_test return } do_execsql_test 1.0 { CREATE VIRTUAL TABLE ft USING fts3; INSERT INTO ft(rowid, content) VALUES(1, '1 2 3'); INSERT INTO ft(rowid, content) VALUES(2, '4 5 6'); INSERT INTO ft(rowid, content) VALUES(3, '7 8 9'); } #------------------------------------------------------------------------- # Check that UPDATE and DELETE statements that feature "WHERE rowid=?" or # or "WHERE docid=?" clauses do not use statement journals. But that other # DELETE and UPDATE statements do. # # Note: "MATCH ? AND docid=?" does use a statement journal. # foreach {tn sql uses} { 1.1 { DELETE FROM ft } 1 1.2 { DELETE FROM ft WHERE docid=? } 0 1.3 { DELETE FROM ft WHERE rowid=? } 0 1.4 { DELETE FROM ft WHERE ft MATCH '1' } 1 1.5 { DELETE FROM ft WHERE ft MATCH '1' AND docid=? } 1 1.6 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1 2.1 { UPDATE ft SET content='a b c' } 1 2.2 { UPDATE ft SET content='a b c' WHERE docid=? } 0 2.3 { UPDATE ft SET content='a b c' WHERE rowid=? } 0 2.4 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' } 1 2.5 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND docid=? } 1 2.6 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1 } { do_test 1.$tn { sql_uses_stmt db $sql } $uses } #------------------------------------------------------------------------- # Check that putting a "DELETE/UPDATE ... WHERE rowid=?" statement in a # trigger program does not prevent the VM from using a statement # transaction. Even if the calling statement cannot hit a constraint. # do_execsql_test 2.0 { CREATE TABLE t1(x); CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN DELETE FROM ft WHERE rowid=new.x; END; CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN UPDATE ft SET content = 'a b c' WHERE rowid=old.x; END; CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN DELETE FROM ft WHERE rowid=old.x; END; } foreach {tn sql uses} { 1 { INSERT INTO t1 VALUES(1) } 1 2 { DELETE FROM t1 WHERE x=4 } 1 3 { UPDATE t1 SET x=10 WHERE x=11 } 1 } { do_test 2.$tn { sql_uses_stmt db $sql } $uses } #------------------------------------------------------------------------- # Test that an "UPDATE ... WHERE rowid=?" works and does not corrupt the # index when it strikes a constraint. Both inside and outside a # transaction. # foreach {tn tcl1 tcl2} { 1 {} {} 2 { execsql BEGIN } { if {[sqlite3_get_autocommit db]==1} { error "transaction rolled back!" } execsql COMMIT } } { do_execsql_test 3.$tn.0 { DROP TABLE IF EXISTS ft2; CREATE VIRTUAL TABLE ft2 USING fts4; INSERT INTO ft2(rowid, content) VALUES(1, 'a b c'); INSERT INTO ft2(rowid, content) VALUES(2, 'a b d'); INSERT INTO ft2(rowid, content) VALUES(3, 'a b e'); } eval $tcl1 foreach {tn2 sql content} { 1 { UPDATE ft2 SET docid=2 WHERE docid=1 } { 1 {a b c} 2 {a b d} 3 {a b e} } 2 { INSERT INTO ft2(rowid, content) VALUES(4, 'a b f'); UPDATE ft2 SET docid=5 WHERE docid=4; UPDATE ft2 SET docid=3 WHERE docid=5; } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } 3 { UPDATE ft2 SET docid=3 WHERE docid=4; -- matches 0 rows UPDATE ft2 SET docid=2 WHERE docid=3; } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } 4 { INSERT INTO ft2(rowid, content) VALUES(4, 'a b g'); UPDATE ft2 SET docid=-1 WHERE docid=4; UPDATE ft2 SET docid=3 WHERE docid=-1; } {-1 {a b g} 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } 5 { DELETE FROM ft2 WHERE rowid=451; DELETE FROM ft2 WHERE rowid=-1; UPDATE ft2 SET docid = 2 WHERE docid = 1; } {1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} } } { do_catchsql_test 3.$tn.$tn2.a $sql {1 {constraint failed}} do_execsql_test 3.$tn.$tn2.b { SELECT rowid, content FROM ft2 } $content do_execsql_test 3.$tn.$tn2.c { INSERT INTO ft2(ft2) VALUES('integrity-check'); } } eval $tcl2 } finish_test |