/ Check-in [e557b7d8]
Login

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

Overview
Comment:Progress toward getting UPDATE to work in WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: e557b7d80f1ede63427a31b16757bf5d8dbfb66d
User & Date: drh 2013-10-25 19:17:17
Context
2013-10-26
00:58
Minor refactoring of variable names and fixes to comments in insert.c. check-in: ae61a343 user: drh tags: omit-rowid
2013-10-25
19:17
Progress toward getting UPDATE to work in WITHOUT ROWID tables. check-in: e557b7d8 user: drh tags: omit-rowid
14:46
Basic DELETE operations now working on WITHOUT ROWID tables. check-in: 9eafafa3 user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1141   1141   **
  1142   1142   **    i.  Data from middle columns...
  1143   1143   **
  1144   1144   **    N.  The data in the last column of the entry after the update.
  1145   1145   **
  1146   1146   ** The regRowid parameter is the index of the register containing (1).
  1147   1147   **
  1148         -** If isUpdate is true and rowidChng is non-zero, then rowidChng contains
         1148  +** If isUpdate is true and pkChng is non-zero, then pkChng contains
  1149   1149   ** the address of a register containing the rowid before the update takes
  1150   1150   ** place. isUpdate is true for UPDATEs and false for INSERTs. If isUpdate
  1151         -** is false, indicating an INSERT statement, then a non-zero rowidChng 
         1151  +** is false, indicating an INSERT statement, then a non-zero pkChng 
  1152   1152   ** indicates that the rowid was explicitly specified as part of the
  1153         -** INSERT statement. If rowidChng is false, it means that  the rowid is
         1153  +** INSERT statement. If pkChng is false, it means that  the rowid is
  1154   1154   ** computed automatically in an insert or that the rowid value is not 
  1155   1155   ** modified by an update.
  1156   1156   **
  1157         -** The code generated by this routine store new index entries into
         1157  +** The code generated by this routine should store new index entries into
  1158   1158   ** registers identified by aRegIdx[].  No index entry is created for
  1159   1159   ** indices where aRegIdx[i]==0.  The order of indices in aRegIdx[] is
  1160   1160   ** the same as the order of indices on the linked list of indices
  1161   1161   ** attached to the table.
  1162   1162   **
  1163   1163   ** This routine also generates code to check constraints.  NOT NULL,
  1164   1164   ** CHECK, and UNIQUE constraints are all checked.  If a constraint fails,
................................................................................
  1204   1204   ** read/write cursors with cursor number baseCur+i for the i-th cursor.
  1205   1205   ** Except, if there is no possibility of a REPLACE action then
  1206   1206   ** cursors do not need to be open for indices where aRegIdx[i]==0.
  1207   1207   */
  1208   1208   void sqlite3GenerateConstraintChecks(
  1209   1209     Parse *pParse,      /* The parser context */
  1210   1210     Table *pTab,        /* the table into which we are inserting */
  1211         -  int baseCur,        /* Index of a read/write cursor pointing at pTab */
  1212         -  int regRowid,       /* Index of the range of input registers */
         1211  +  int baseCur,        /* A read/write cursor pointing at pTab */
         1212  +  int regRowid,       /* First register in a range holding values to insert */
  1213   1213     int *aRegIdx,       /* Register used by each index.  0 for unused indices */
  1214         -  int rowidChng,      /* True if the rowid might collide with existing entry */
         1214  +  int pkChng,         /* Non-zero if the PRIMARY KEY might collide */
  1215   1215     int isUpdate,       /* True for UPDATE, False for INSERT */
  1216   1216     int overrideError,  /* Override onError to this if not OE_Default */
  1217   1217     int ignoreDest,     /* Jump to this label on an OE_Ignore resolution */
  1218   1218     int *pbMayReplace   /* OUT: Set to true if constraint may cause a replace */
  1219   1219   ){
  1220   1220     int i;              /* loop counter */
  1221   1221     Vdbe *v;            /* VDBE under constrution */
................................................................................
  1224   1224     int j1;             /* Addresss of jump instruction */
  1225   1225     int j2 = 0, j3;     /* Addresses of jump instructions */
  1226   1226     int regData;        /* Register containing first data column */
  1227   1227     int iCur;           /* Table cursor number */
  1228   1228     Index *pIdx;         /* Pointer to one of the indices */
  1229   1229     sqlite3 *db;         /* Database connection */
  1230   1230     int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  1231         -  int regOldRowid = (rowidChng && isUpdate) ? rowidChng : regRowid;
         1231  +  int regOldRowid = (pkChng && isUpdate) ? pkChng : regRowid;
  1232   1232   
  1233   1233     db = pParse->db;
  1234   1234     v = sqlite3GetVdbe(pParse);
  1235   1235     assert( v!=0 );
  1236   1236     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1237   1237     nCol = pTab->nCol;
  1238   1238     regData = regRowid + 1;
................................................................................
  1310   1310     }
  1311   1311   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1312   1312   
  1313   1313     /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  1314   1314     ** of the new record does not previously exist.  Except, if this
  1315   1315     ** is an UPDATE and the primary key is not changing, that is OK.
  1316   1316     */
  1317         -  if( rowidChng ){
         1317  +  if( pkChng ){
  1318   1318       onError = pTab->keyConf;
  1319   1319       if( overrideError!=OE_Default ){
  1320   1320         onError = overrideError;
  1321   1321       }else if( onError==OE_Default ){
  1322   1322         onError = OE_Abort;
  1323   1323       }
  1324   1324       
  1325   1325       if( isUpdate ){
  1326         -      j2 = sqlite3VdbeAddOp3(v, OP_Eq, regRowid, 0, rowidChng);
         1326  +      j2 = sqlite3VdbeAddOp3(v, OP_Eq, regRowid, 0, pkChng);
  1327   1327       }
  1328   1328       j3 = sqlite3VdbeAddOp3(v, OP_NotExists, baseCur, 0, regRowid);
  1329   1329       switch( onError ){
  1330   1330         default: {
  1331   1331           onError = OE_Abort;
  1332   1332           /* Fall thru into the next case */
  1333   1333         }

Changes to src/update.c.

    95     95   ){
    96     96     int i, j;              /* Loop counters */
    97     97     Table *pTab;           /* The table to be updated */
    98     98     int addr = 0;          /* VDBE instruction address of the start of the loop */
    99     99     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   100    100     Vdbe *v;               /* The virtual database engine */
   101    101     Index *pIdx;           /* For looping over indices */
          102  +  Index *pPk;            /* The PRIMARY KEY index for WITHOUT ROWID tables */
   102    103     int nIdx;              /* Number of indices that need updating */
   103    104     int iCur;              /* VDBE Cursor number of pTab */
   104    105     sqlite3 *db;           /* The database structure */
   105    106     int *aRegIdx = 0;      /* One register assigned to each index to be updated */
   106    107     int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
   107    108                            ** an expression for the i-th column of the table.
   108    109                            ** aXRef[i]==-1 if the i-th column is not changed. */
   109    110     int chngRowid;         /* True if the record number is being changed */
          111  +  int chngPk;            /* The PRIMARY KEY of a WITHOUT ROWID table changed */
   110    112     Expr *pRowidExpr = 0;  /* Expression defining the new record number */
   111    113     int openAll = 0;       /* True if all indices need to be opened */
   112    114     AuthContext sContext;  /* The authorization context */
   113    115     NameContext sNC;       /* The name-context to resolve expressions in */
   114    116     int iDb;               /* Database containing the table being updated */
   115    117     int okOnePass;         /* True for one-pass algorithm without the FIFO */
   116    118     int hasFK;             /* True if foreign key processing is required */
          119  +  int labelBreak;        /* Jump here to break out of UPDATE loop */
          120  +  int labelContinue;     /* Jump here to continue next step of UPDATE loop */
   117    121   
   118    122   #ifndef SQLITE_OMIT_TRIGGER
   119    123     int isView;            /* True when updating a view (INSTEAD OF trigger) */
   120    124     Trigger *pTrigger;     /* List of triggers on pTab, if required */
   121    125     int tmask;             /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
   122    126   #endif
   123    127     int newmask;           /* Mask of NEW.* columns accessed by BEFORE triggers */
          128  +  int iEph = 0;          /* Ephemeral table holding all primary key values */
   124    129   
   125    130     /* Register Allocations */
   126    131     int regRowCount = 0;   /* A count of rows changed */
   127    132     int regOldRowid;       /* The old rowid */
   128    133     int regNewRowid;       /* The new rowid */
   129    134     int regNew;            /* Content of the NEW.* table in triggers */
   130    135     int regOld = 0;        /* Content of OLD.* table in triggers */
   131    136     int regRowSet = 0;     /* Rowset of rows to be updated */
          137  +  int regKey = 0;        /* composite PRIMARY KEY value */
   132    138   
   133    139     memset(&sContext, 0, sizeof(sContext));
   134    140     db = pParse->db;
   135    141     if( pParse->nErr || db->mallocFailed ){
   136    142       goto update_cleanup;
   137    143     }
   138    144     assert( pTabList->nSrc==1 );
................................................................................
   175    181     ** need to occur right after the database cursor.  So go ahead and
   176    182     ** allocate enough space, just in case.
   177    183     */
   178    184     pTabList->a[0].iCursor = iCur = pParse->nTab++;
   179    185     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   180    186       pParse->nTab++;
   181    187     }
          188  +  pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
   182    189   
   183    190     /* Initialize the name-context */
   184    191     memset(&sNC, 0, sizeof(sNC));
   185    192     sNC.pParse = pParse;
   186    193     sNC.pSrcList = pTabList;
   187    194   
   188    195     /* Resolve the column names in all the expressions of the
   189    196     ** of the UPDATE statement.  Also find the column index
   190    197     ** for each column to be updated in the pChanges array.  For each
   191    198     ** column to be updated, make sure we have authorization to change
   192    199     ** that column.
   193    200     */
   194         -  chngRowid = 0;
          201  +  chngPk = chngRowid = 0;
   195    202     for(i=0; i<pChanges->nExpr; i++){
   196    203       if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){
   197    204         goto update_cleanup;
   198    205       }
   199    206       for(j=0; j<pTab->nCol; j++){
   200    207         if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
   201    208           if( j==pTab->iPKey ){
   202    209             chngRowid = 1;
   203    210             pRowidExpr = pChanges->a[i].pExpr;
          211  +        }else if( pPk && (pTab->aCol[j].colFlags & COLFLAG_PRIMKEY)!=0 ){
          212  +          chngPk = 1;
   204    213           }
   205    214           aXRef[j] = i;
   206    215           break;
   207    216         }
   208    217       }
   209    218       if( j>=pTab->nCol ){
   210         -      if( sqlite3IsRowid(pChanges->a[i].zName) ){
          219  +      if( pPk==0 && sqlite3IsRowid(pChanges->a[i].zName) ){
   211    220           j = -1;
   212    221           chngRowid = 1;
   213    222           pRowidExpr = pChanges->a[i].pExpr;
   214    223         }else{
   215    224           sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
   216    225           pParse->checkSchema = 1;
   217    226           goto update_cleanup;
................................................................................
   246    255     }
   247    256     for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
   248    257       int reg;
   249    258       if( hasFK || chngRowid || pIdx->pPartIdxWhere ){
   250    259         reg = ++pParse->nMem;
   251    260       }else{
   252    261         reg = 0;
   253         -      for(i=0; i<pIdx->nKeyCol; i++){
          262  +      for(i=0; i<pIdx->nColumn; i++){
   254    263           if( aXRef[pIdx->aiColumn[i]]>=0 ){
   255    264             reg = ++pParse->nMem;
   256    265             break;
   257    266           }
   258    267         }
   259    268       }
   260    269       aRegIdx[j] = reg;
................................................................................
   309    318     */
   310    319     if( sqlite3ResolveExprNames(&sNC, pWhere) ){
   311    320       goto update_cleanup;
   312    321     }
   313    322   
   314    323     /* Begin the database scan
   315    324     */
   316         -  sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
   317         -  pWInfo = sqlite3WhereBegin(
   318         -      pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, 0
   319         -  );
   320         -  if( pWInfo==0 ) goto update_cleanup;
   321         -  okOnePass = sqlite3WhereOkOnePass(pWInfo);
   322         -
   323         -  /* Remember the rowid of every item to be updated.
   324         -  */
   325         -  sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid);
   326         -  if( !okOnePass ){
   327         -    sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid);
   328         -  }
   329         -
   330         -  /* End the database scan loop.
   331         -  */
   332         -  sqlite3WhereEnd(pWInfo);
          325  +  if( HasRowid(pTab) ){
          326  +    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
          327  +    pWInfo = sqlite3WhereBegin(
          328  +        pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, 0
          329  +    );
          330  +    if( pWInfo==0 ) goto update_cleanup;
          331  +    okOnePass = sqlite3WhereOkOnePass(pWInfo);
          332  +  
          333  +    /* Remember the rowid of every item to be updated.
          334  +    */
          335  +    sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid);
          336  +    if( !okOnePass ){
          337  +      sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid);
          338  +    }
          339  +  
          340  +    /* End the database scan loop.
          341  +    */
          342  +    sqlite3WhereEnd(pWInfo);
          343  +  }else{
          344  +    int iPk;         /* First of nPk memory cells holding PRIMARY KEY value */
          345  +    i16 nPk;         /* Number of components of the PRIMARY KEY */
          346  +
          347  +    assert( pPk!=0 );
          348  +    nPk = pPk->nKeyCol;
          349  +    iPk = pParse->nMem+1;
          350  +    pParse->nMem += nPk;
          351  +    regKey = ++pParse->nMem;
          352  +    iEph = pParse->nTab++;
          353  +    sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iEph, nPk, 0, 
          354  +                      (char*)sqlite3IndexKeyinfo(pParse, pPk),
          355  +                      P4_KEYINFO_HANDOFF);
          356  +    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 0, 0);
          357  +    if( pWInfo==0 ) goto update_cleanup;
          358  +    for(i=0; i<nPk; i++){
          359  +      sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, pPk->aiColumn[i], iPk+i);
          360  +    }
          361  +    sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
          362  +                      sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
          363  +    sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey);
          364  +    sqlite3WhereEnd(pWInfo);
          365  +    okOnePass = 0;
          366  +  }
   333    367   
   334    368     /* Initialize the count of updated rows
   335    369     */
   336    370     if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
   337    371       regRowCount = ++pParse->nMem;
   338    372       sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
   339    373     }
................................................................................
   341    375     if( !isView ){
   342    376       /* 
   343    377       ** Open every index that needs updating.  Note that if any
   344    378       ** index could potentially invoke a REPLACE conflict resolution 
   345    379       ** action, then we need to open all indices because we might need
   346    380       ** to be deleting some records.
   347    381       */
   348         -    if( !okOnePass ) sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); 
          382  +    if( !okOnePass && HasRowid(pTab) ){
          383  +      sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); 
          384  +    }
   349    385       if( onError==OE_Replace ){
   350    386         openAll = 1;
   351    387       }else{
   352    388         openAll = 0;
   353    389         for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   354    390           if( pIdx->onError==OE_Replace ){
   355    391             openAll = 1;
................................................................................
   365    401                          (char*)pKey, P4_KEYINFO_HANDOFF);
   366    402           assert( pParse->nTab>iCur+i+1 );
   367    403         }
   368    404       }
   369    405     }
   370    406   
   371    407     /* Top of the update loop */
   372         -  if( okOnePass ){
          408  +  labelBreak = sqlite3VdbeMakeLabel(v);
          409  +  labelContinue = sqlite3VdbeMakeLabel(v);
          410  +  if( pPk ){
          411  +    sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak);
          412  +    addr = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey);
          413  +    sqlite3VdbeAddOp3(v, OP_NotFound, iEph, labelContinue, regKey);
          414  +  }else if( okOnePass ){
   373    415       int a1 = sqlite3VdbeAddOp1(v, OP_NotNull, regOldRowid);
   374         -    addr = sqlite3VdbeAddOp0(v, OP_Goto);
          416  +    addr = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelBreak);
   375    417       sqlite3VdbeJumpHere(v, a1);
   376    418     }else{
   377         -    addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid);
          419  +    addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak,
          420  +                             regOldRowid);
          421  +    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid);
   378    422     }
   379    423   
   380         -  /* Make cursor iCur point to the record that is being updated. If
   381         -  ** this record does not exist for some reason (deleted by a trigger,
   382         -  ** for example, then jump to the next iteration of the RowSet loop.  */
   383         -  sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid);
   384         -
   385    424     /* If the record number will change, set register regNewRowid to
   386    425     ** contain the new value. If the record number is not being modified,
   387    426     ** then regNewRowid is the same register as regOldRowid, which is
   388    427     ** already populated.  */
   389    428     assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid );
   390    429     if( chngRowid ){
   391    430       sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
................................................................................
   524    563   
   525    564     sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
   526    565         TRIGGER_AFTER, pTab, regOldRowid, onError, addr);
   527    566   
   528    567     /* Repeat the above with the next record to be updated, until
   529    568     ** all record selected by the WHERE clause have been updated.
   530    569     */
   531         -  sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
   532         -  sqlite3VdbeJumpHere(v, addr);
          570  +  if( pPk ){
          571  +    sqlite3VdbeResolveLabel(v, labelContinue);
          572  +    sqlite3VdbeAddOp2(v, OP_Next, iEph, addr);
          573  +  }else if( !okOnePass ){
          574  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
          575  +  }
          576  +  sqlite3VdbeResolveLabel(v, labelBreak);
   533    577   
   534    578     /* Close all tables */
   535    579     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
   536    580       assert( aRegIdx );
   537    581       if( openAll || aRegIdx[i]>0 ){
   538    582         sqlite3VdbeAddOp2(v, OP_Close, iCur+i+1, 0);
   539    583       }