/ Check-in [0e56ba69]
Login

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

Overview
Comment:Delete PRIMARY KEY index entries last. Only construct the unique prefix of an index key when deleting entries from an index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 0e56ba69f0fcd12a5166c32f6e4eacdcc29fba04
User & Date: drh 2013-10-24 14:16:10
Context
2013-10-24
19:48
Update the interface to the sqlite3GenerateRowDelete() utility so that it is able to handle WITHOUT ROWID tables. The implementation still needs to be completed. check-in: 85daf517 user: drh tags: omit-rowid
14:16
Delete PRIMARY KEY index entries last. Only construct the unique prefix of an index key when deleting entries from an index. check-in: 0e56ba69 user: drh tags: omit-rowid
11:55
Remove an obsolete comment from the VDBE. No code changes. check-in: 53bb070c user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1637   1637     assert( pPk!=0 );
  1638   1638     nPk = pPk->nKeyCol;
  1639   1639   
  1640   1640     /* Make sure every column of the PRIMARY KEY is NOT NULL */
  1641   1641     for(i=0; i<nPk; i++){
  1642   1642       pTab->aCol[pPk->aiColumn[i]].notNull = 1;
  1643   1643     }
         1644  +  pPk->uniqNotNull = 1;
  1644   1645   
  1645   1646     /* Update the in-memory representation of all UNIQUE indices by converting
  1646   1647     ** the final rowid column into one or more columns of the PRIMARY KEY.
  1647   1648     */
  1648   1649     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1649   1650       int n;
  1650   1651       if( pIdx->autoIndex==2 ) continue;
................................................................................
  2631   2632   
  2632   2633     /* Open the table. Loop through all rows of the table, inserting index
  2633   2634     ** records into the sorter. */
  2634   2635     sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
  2635   2636     addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
  2636   2637     regRecord = sqlite3GetTempReg(pParse);
  2637   2638   
  2638         -  sqlite3GenerateIndexKey(pParse, pIndex, iTab, regRecord, 1, &iPartIdxLabel);
         2639  +  sqlite3GenerateIndexKey(pParse, pIndex, iTab, regRecord, 0, &iPartIdxLabel);
  2639   2640     sqlite3VdbeAddOp2(v, OP_SorterInsert, iSorter, regRecord);
  2640   2641     sqlite3VdbeResolveLabel(v, iPartIdxLabel);
  2641   2642     sqlite3VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  2642   2643     sqlite3VdbeJumpHere(v, addr1);
  2643   2644     if( memRootPage<0 ) sqlite3VdbeAddOp2(v, OP_Clear, tnum, iDb);
  2644   2645     sqlite3VdbeAddOp4(v, OP_OpenWrite, iIdx, tnum, iDb, 
  2645   2646                       (char *)pKey, P4_KEYINFO_HANDOFF);
................................................................................
  2928   2929     assert( EIGHT_BYTE_ALIGNMENT(pIndex->aiRowEst) );
  2929   2930     assert( EIGHT_BYTE_ALIGNMENT(pIndex->azColl) );
  2930   2931     pIndex->zName = zExtra;
  2931   2932     zExtra += nName + 1;
  2932   2933     memcpy(pIndex->zName, zName, nName+1);
  2933   2934     pIndex->pTable = pTab;
  2934   2935     pIndex->onError = (u8)onError;
  2935         -  pIndex->uniqNotNull = onError==OE_Abort;
         2936  +  pIndex->uniqNotNull = onError!=OE_None;
  2936   2937     pIndex->autoIndex = (u8)(pName==0);
  2937   2938     pIndex->pSchema = db->aDb[iDb].pSchema;
  2938   2939     if( pPIWhere ){
  2939   2940       sqlite3ResolveSelfReference(pParse, pTab, NC_PartIdx, pPIWhere, 0);
  2940   2941       pIndex->pPartIdxWhere = pPIWhere;
  2941   2942       pPIWhere = 0;
  2942   2943     }

Changes to src/delete.c.

   130    130   Expr *sqlite3LimitWhere(
   131    131     Parse *pParse,               /* The parser context */
   132    132     SrcList *pSrc,               /* the FROM clause -- which tables to scan */
   133    133     Expr *pWhere,                /* The WHERE clause.  May be null */
   134    134     ExprList *pOrderBy,          /* The ORDER BY clause.  May be null */
   135    135     Expr *pLimit,                /* The LIMIT clause.  May be null */
   136    136     Expr *pOffset,               /* The OFFSET clause.  May be null */
   137         -  char *zStmtType              /* Either DELETE or UPDATE.  For error messages. */
          137  +  char *zStmtType              /* Either DELETE or UPDATE.  For err msgs. */
   138    138   ){
   139    139     Expr *pWhereRowid = NULL;    /* WHERE rowid .. */
   140    140     Expr *pInClause = NULL;      /* WHERE rowid IN ( select ) */
   141    141     Expr *pSelectRowid = NULL;   /* SELECT rowid ... */
   142    142     ExprList *pEList = NULL;     /* Expression list contaning only pSelectRowid */
   143    143     SrcList *pSelectSrc = NULL;  /* SELECT rowid FROM x ... (dup of pSrc) */
   144    144     Select *pSelect = NULL;      /* Complete SELECT tree */
................................................................................
   205    205   limit_where_cleanup_2:
   206    206     sqlite3ExprDelete(pParse->db, pWhere);
   207    207     sqlite3ExprListDelete(pParse->db, pOrderBy);
   208    208     sqlite3ExprDelete(pParse->db, pLimit);
   209    209     sqlite3ExprDelete(pParse->db, pOffset);
   210    210     return 0;
   211    211   }
   212         -#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) */
          212  +#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) */
          213  +       /*      && !defined(SQLITE_OMIT_SUBQUERY) */
   213    214   
   214    215   /*
   215    216   ** Generate code for a DELETE FROM statement.
   216    217   **
   217    218   **     DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
   218    219   **                 \________/       \________________/
   219    220   **                  pTabList              pWhere
................................................................................
   404    405         sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB);
   405    406         sqlite3VdbeChangeP5(v, OE_Abort);
   406    407         sqlite3MayAbort(pParse);
   407    408       }else
   408    409   #endif
   409    410       {
   410    411         int count = (pParse->nested==0);    /* True to count changes */
   411         -      sqlite3GenerateRowDelete(pParse, pTab, iCur, iRowid, count, pTrigger, OE_Default);
          412  +      sqlite3GenerateRowDelete(pParse, pTab, iCur, iRowid, count,
          413  +                               pTrigger, OE_Default);
   412    414       }
   413    415   
   414    416       /* End of the delete loop */
   415    417       sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
   416    418       sqlite3VdbeResolveLabel(v, end);
   417    419   
   418    420       /* Close the cursors open on the table and its indexes. */
................................................................................
   462    464   ** This routine generates VDBE code that causes a single row of a
   463    465   ** single table to be deleted.
   464    466   **
   465    467   ** The VDBE must be in a particular state when this routine is called.
   466    468   ** These are the requirements:
   467    469   **
   468    470   **   1.  A read/write cursor pointing to pTab, the table containing the row
   469         -**       to be deleted, must be opened as cursor number $iCur.
          471  +**       to be deleted, must be opened as cursor number iCur.
   470    472   **
   471    473   **   2.  Read/write cursors for all indices of pTab must be open as
   472         -**       cursor number base+i for the i-th index.
          474  +**       cursor number iCur+i for the i-th index.
   473    475   **
   474    476   **   3.  The record number of the row to be deleted must be stored in
   475    477   **       memory cell iRowid.
   476    478   **
   477    479   ** This routine generates code to remove both the table record and all 
   478    480   ** index entries that point to that record.
   479    481   */
................................................................................
   571    573   ** This routine generates VDBE code that causes the deletion of all
   572    574   ** index entries associated with a single row of a single table.
   573    575   **
   574    576   ** The VDBE must be in a particular state when this routine is called.
   575    577   ** These are the requirements:
   576    578   **
   577    579   **   1.  A read/write cursor pointing to pTab, the table containing the row
   578         -**       to be deleted, must be opened as cursor number "iCur".
          580  +**       to be deleted, must be opened as cursor number "iCur".  For
          581  +**       WITHOUT ROWID tables that do not have a main table, the iCur
          582  +**       cursor is unused.
   579    583   **
   580    584   **   2.  Read/write cursors for all indices of pTab must be open as
   581    585   **       cursor number iCur+i for the i-th index.
   582    586   **
   583    587   **   3.  The "iCur" cursor must be pointing to the row that is to be
   584         -**       deleted.
          588  +**       deleted.  Or, for WITHOUT ROWID tables, the iCur+i cursor for
          589  +**       the PRIMARY KEY index must be pointing to the row to that is
          590  +**       to be deleted.
   585    591   */
   586    592   void sqlite3GenerateRowIndexDelete(
   587    593     Parse *pParse,     /* Parsing and code generating context */
   588    594     Table *pTab,       /* Table containing the row to be deleted */
   589    595     int iCur,          /* Cursor number for the table */
   590    596     int *aRegIdx       /* Only delete if aRegIdx!=0 && aRegIdx[i]>0 */
   591    597   ){
   592         -  int i;
          598  +  int i, iPk = 0;
   593    599     Index *pIdx;
   594    600     int r1;
   595    601     int iPartIdxLabel;
   596    602     Vdbe *v = pParse->pVdbe;
          603  +  Index *pPk;
   597    604   
          605  +  pPk = sqlite3PrimaryKeyIndex(pTab);
   598    606     for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   599    607       if( aRegIdx!=0 && aRegIdx[i-1]==0 ) continue;
   600         -    r1 = sqlite3GenerateIndexKey(pParse, pIdx, iCur, 0, 0, &iPartIdxLabel);
   601         -    sqlite3VdbeAddOp3(v, OP_IdxDelete, iCur+i, r1, pIdx->nKeyCol+1);
          608  +    if( pIdx==pPk ){ iPk = iCur+i; continue; }
          609  +    r1 = sqlite3GenerateIndexKey(pParse, pIdx, iCur, 0, 1, &iPartIdxLabel);
          610  +    sqlite3VdbeAddOp3(v, OP_IdxDelete, iCur+i, r1,
          611  +                      pIdx->uniqNotNull ? pIdx->nKeyCol : pIdx->nColumn);
          612  +    sqlite3VdbeResolveLabel(v, iPartIdxLabel);
          613  +  }
          614  +  if( iPk ){
          615  +    r1 = sqlite3GenerateIndexKey(pParse, pPk, iCur, 0, 1, &iPartIdxLabel);
          616  +    sqlite3VdbeAddOp3(v, OP_IdxDelete, iPk, r1,
          617  +                      pPk->uniqNotNull ? pPk->nKeyCol : pPk->nColumn);
   602    618       sqlite3VdbeResolveLabel(v, iPartIdxLabel);
   603    619     }
   604    620   }
   605    621   
   606    622   /*
   607         -** Generate code that will assemble an index key and put it in register
          623  +** Generate code that will assemble an index key and stores it in register
   608    624   ** regOut.  The key with be for index pIdx which is an index on pTab.
   609    625   ** iCur is the index of a cursor open on the pTab table and pointing to
   610    626   ** the entry that needs indexing.
   611    627   **
   612    628   ** Return a register number which is the first in a block of
   613    629   ** registers that holds the elements of the index key.  The
   614    630   ** block of registers has already been deallocated by the time
................................................................................
   621    637   ** will be set to zero which is an empty label that is ignored by
   622    638   ** sqlite3VdbeResolveLabel().
   623    639   */
   624    640   int sqlite3GenerateIndexKey(
   625    641     Parse *pParse,       /* Parsing context */
   626    642     Index *pIdx,         /* The index for which to generate a key */
   627    643     int iCur,            /* Cursor number for the pIdx->pTable table */
   628         -  int regOut,          /* Write the new index key to this register */
   629         -  int doMakeRec,       /* Run the OP_MakeRecord instruction if true */
          644  +  int regOut,          /* Put the new key into this register if not 0 */
          645  +  int prefixOnly,      /* Compute only a unique prefix of the key */
   630    646     int *piPartIdxLabel  /* OUT: Jump to this label to skip partial index */
   631    647   ){
   632    648     Vdbe *v = pParse->pVdbe;
   633    649     int j;
   634    650     Table *pTab = pIdx->pTable;
   635    651     int regBase;
   636    652     int nCol;
................................................................................
   642    658         pParse->iPartIdxTab = iCur;
   643    659         sqlite3ExprIfFalse(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel, 
   644    660                            SQLITE_JUMPIFNULL);
   645    661       }else{
   646    662         *piPartIdxLabel = 0;
   647    663       }
   648    664     }
   649         -  nCol = pIdx->nColumn;
          665  +  nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn;
   650    666     regBase = sqlite3GetTempRange(pParse, nCol);
   651    667     pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
   652    668     for(j=0; j<nCol; j++){
   653    669       i16 idx = pIdx->aiColumn[j];
   654    670       if( pPk ) idx = sqlite3ColumnOfIndex(pPk, idx);
   655    671       if( idx<0 || idx==pTab->iPKey ){
   656    672         sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regBase+j);
   657    673       }else{
   658    674         sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j);
   659    675         sqlite3ColumnDefault(v, pTab, idx, -1);
   660    676       }
   661    677     }
   662         -  if( doMakeRec ){
          678  +  if( regOut ){
   663    679       const char *zAff;
   664    680       if( pTab->pSelect
   665    681        || OptimizationDisabled(pParse->db, SQLITE_IdxRealAsInt)
   666    682       ){
   667    683         zAff = 0;
   668    684       }else{
   669    685         zAff = sqlite3IndexAffinityStr(v, pIdx);

Changes to src/pragma.c.

  1865   1865               { OP_Concat,      4,  3,  3},
  1866   1866               { OP_Concat,      5,  3,  3},
  1867   1867               { OP_Concat,      6,  3,  3},
  1868   1868               { OP_ResultRow,   3,  1,  0},
  1869   1869               { OP_IfPos,       1,  0,  0},    /* 9 */
  1870   1870               { OP_Halt,        0,  0,  0},
  1871   1871             };
  1872         -          r1 = sqlite3GenerateIndexKey(pParse, pIdx, 1, 3, 0, &jmp3);
         1872  +          r1 = sqlite3GenerateIndexKey(pParse, pIdx, 1, 0, 0, &jmp3);
  1873   1873             sqlite3VdbeAddOp2(v, OP_AddImm, 7+j, 1);  /* increment entry count */
  1874   1874             jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, j+2, 0, r1, pIdx->nKeyCol+1);
  1875   1875             addr = sqlite3VdbeAddOpList(v, ArraySize(idxErr), idxErr);
  1876   1876             sqlite3VdbeChangeP4(v, addr+1, "rowid ", P4_STATIC);
  1877   1877             sqlite3VdbeChangeP4(v, addr+3, " missing from index ", P4_STATIC);
  1878   1878             sqlite3VdbeChangeP4(v, addr+4, pIdx->zName, P4_TRANSIENT);
  1879   1879             sqlite3VdbeJumpHere(v, addr+9);

Changes to src/where.c.

  2135   2135     sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nKeyCol+1, 0,
  2136   2136                       (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
  2137   2137     VdbeComment((v, "for %s", pTable->zName));
  2138   2138   
  2139   2139     /* Fill the automatic index with content */
  2140   2140     addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
  2141   2141     regRecord = sqlite3GetTempReg(pParse);
  2142         -  sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1, 0);
         2142  +  sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0);
  2143   2143     sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  2144   2144     sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  2145   2145     sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
  2146   2146     sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
  2147   2147     sqlite3VdbeJumpHere(v, addrTop);
  2148   2148     sqlite3ReleaseTempReg(pParse, regRecord);
  2149   2149