Back to index

php5  5.3.10
insert.c
Go to the documentation of this file.
00001 /*
00002 ** 2001 September 15
00003 **
00004 ** The author disclaims copyright to this source code.  In place of
00005 ** a legal notice, here is a blessing:
00006 **
00007 **    May you do good and not evil.
00008 **    May you find forgiveness for yourself and forgive others.
00009 **    May you share freely, never taking more than you give.
00010 **
00011 *************************************************************************
00012 ** This file contains C code routines that are called by the parser
00013 ** to handle INSERT statements in SQLite.
00014 **
00015 ** $Id: insert.c 195361 2005-09-07 15:11:33Z iliaa $
00016 */
00017 #include "sqliteInt.h"
00018 
00019 /*
00020 ** This routine is call to handle SQL of the following forms:
00021 **
00022 **    insert into TABLE (IDLIST) values(EXPRLIST)
00023 **    insert into TABLE (IDLIST) select
00024 **
00025 ** The IDLIST following the table name is always optional.  If omitted,
00026 ** then a list of all columns for the table is substituted.  The IDLIST
00027 ** appears in the pColumn parameter.  pColumn is NULL if IDLIST is omitted.
00028 **
00029 ** The pList parameter holds EXPRLIST in the first form of the INSERT
00030 ** statement above, and pSelect is NULL.  For the second form, pList is
00031 ** NULL and pSelect is a pointer to the select statement used to generate
00032 ** data for the insert.
00033 **
00034 ** The code generated follows one of three templates.  For a simple
00035 ** select with data coming from a VALUES clause, the code executes
00036 ** once straight down through.  The template looks like this:
00037 **
00038 **         open write cursor to <table> and its indices
00039 **         puts VALUES clause expressions onto the stack
00040 **         write the resulting record into <table>
00041 **         cleanup
00042 **
00043 ** If the statement is of the form
00044 **
00045 **   INSERT INTO <table> SELECT ...
00046 **
00047 ** And the SELECT clause does not read from <table> at any time, then
00048 ** the generated code follows this template:
00049 **
00050 **         goto B
00051 **      A: setup for the SELECT
00052 **         loop over the tables in the SELECT
00053 **           gosub C
00054 **         end loop
00055 **         cleanup after the SELECT
00056 **         goto D
00057 **      B: open write cursor to <table> and its indices
00058 **         goto A
00059 **      C: insert the select result into <table>
00060 **         return
00061 **      D: cleanup
00062 **
00063 ** The third template is used if the insert statement takes its
00064 ** values from a SELECT but the data is being inserted into a table
00065 ** that is also read as part of the SELECT.  In the third form,
00066 ** we have to use a intermediate table to store the results of
00067 ** the select.  The template is like this:
00068 **
00069 **         goto B
00070 **      A: setup for the SELECT
00071 **         loop over the tables in the SELECT
00072 **           gosub C
00073 **         end loop
00074 **         cleanup after the SELECT
00075 **         goto D
00076 **      C: insert the select result into the intermediate table
00077 **         return
00078 **      B: open a cursor to an intermediate table
00079 **         goto A
00080 **      D: open write cursor to <table> and its indices
00081 **         loop over the intermediate table
00082 **           transfer values form intermediate table into <table>
00083 **         end the loop
00084 **         cleanup
00085 */
00086 void sqliteInsert(
00087   Parse *pParse,        /* Parser context */
00088   SrcList *pTabList,    /* Name of table into which we are inserting */
00089   ExprList *pList,      /* List of values to be inserted */
00090   Select *pSelect,      /* A SELECT statement to use as the data source */
00091   IdList *pColumn,      /* Column names corresponding to IDLIST. */
00092   int onError           /* How to handle constraint errors */
00093 ){
00094   Table *pTab;          /* The table to insert into */
00095   char *zTab;           /* Name of the table into which we are inserting */
00096   const char *zDb;      /* Name of the database holding this table */
00097   int i, j, idx;        /* Loop counters */
00098   Vdbe *v;              /* Generate code into this virtual machine */
00099   Index *pIdx;          /* For looping over indices of the table */
00100   int nColumn;          /* Number of columns in the data */
00101   int base;             /* VDBE Cursor number for pTab */
00102   int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
00103   sqlite *db;           /* The main database structure */
00104   int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
00105   int endOfLoop;        /* Label for the end of the insertion loop */
00106   int useTempTable;     /* Store SELECT results in intermediate table */
00107   int srcTab;           /* Data comes from this temporary cursor if >=0 */
00108   int iSelectLoop;      /* Address of code that implements the SELECT */
00109   int iCleanup;         /* Address of the cleanup code */
00110   int iInsertBlock;     /* Address of the subroutine used to insert data */
00111   int iCntMem;          /* Memory cell used for the row counter */
00112   int isView;           /* True if attempting to insert into a view */
00113 
00114   int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
00115   int before_triggers;        /* True if there are BEFORE triggers */
00116   int after_triggers;         /* True if there are AFTER triggers */
00117   int newIdx = -1;            /* Cursor for the NEW table */
00118 
00119   if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
00120   db = pParse->db;
00121 
00122   /* Locate the table into which we will be inserting new information.
00123   */
00124   assert( pTabList->nSrc==1 );
00125   zTab = pTabList->a[0].zName;
00126   if( zTab==0 ) goto insert_cleanup;
00127   pTab = sqliteSrcListLookup(pParse, pTabList);
00128   if( pTab==0 ){
00129     goto insert_cleanup;
00130   }
00131   assert( pTab->iDb<db->nDb );
00132   zDb = db->aDb[pTab->iDb].zName;
00133   if( sqliteAuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
00134     goto insert_cleanup;
00135   }
00136 
00137   /* Ensure that:
00138   *  (a) the table is not read-only, 
00139   *  (b) that if it is a view then ON INSERT triggers exist
00140   */
00141   before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, 
00142                                        TK_BEFORE, TK_ROW, 0);
00143   after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
00144                                        TK_AFTER, TK_ROW, 0);
00145   row_triggers_exist = before_triggers || after_triggers;
00146   isView = pTab->pSelect!=0;
00147   if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
00148     goto insert_cleanup;
00149   }
00150   if( pTab==0 ) goto insert_cleanup;
00151 
00152   /* If pTab is really a view, make sure it has been initialized.
00153   */
00154   if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
00155     goto insert_cleanup;
00156   }
00157 
00158   /* Allocate a VDBE
00159   */
00160   v = sqliteGetVdbe(pParse);
00161   if( v==0 ) goto insert_cleanup;
00162   sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist, pTab->iDb);
00163 
00164   /* if there are row triggers, allocate a temp table for new.* references. */
00165   if( row_triggers_exist ){
00166     newIdx = pParse->nTab++;
00167   }
00168 
00169   /* Figure out how many columns of data are supplied.  If the data
00170   ** is coming from a SELECT statement, then this step also generates
00171   ** all the code to implement the SELECT statement and invoke a subroutine
00172   ** to process each row of the result. (Template 2.) If the SELECT
00173   ** statement uses the the table that is being inserted into, then the
00174   ** subroutine is also coded here.  That subroutine stores the SELECT
00175   ** results in a temporary table. (Template 3.)
00176   */
00177   if( pSelect ){
00178     /* Data is coming from a SELECT.  Generate code to implement that SELECT
00179     */
00180     int rc, iInitCode;
00181     iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
00182     iSelectLoop = sqliteVdbeCurrentAddr(v);
00183     iInsertBlock = sqliteVdbeMakeLabel(v);
00184     rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0);
00185     if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
00186     iCleanup = sqliteVdbeMakeLabel(v);
00187     sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup);
00188     assert( pSelect->pEList );
00189     nColumn = pSelect->pEList->nExpr;
00190 
00191     /* Set useTempTable to TRUE if the result of the SELECT statement
00192     ** should be written into a temporary table.  Set to FALSE if each
00193     ** row of the SELECT can be written directly into the result table.
00194     **
00195     ** A temp table must be used if the table being updated is also one
00196     ** of the tables being read by the SELECT statement.  Also use a 
00197     ** temp table in the case of row triggers.
00198     */
00199     if( row_triggers_exist ){
00200       useTempTable = 1;
00201     }else{
00202       int addr = sqliteVdbeFindOp(v, OP_OpenRead, pTab->tnum);
00203       useTempTable = 0;
00204       if( addr>0 ){
00205         VdbeOp *pOp = sqliteVdbeGetOp(v, addr-2);
00206         if( pOp->opcode==OP_Integer && pOp->p1==pTab->iDb ){
00207           useTempTable = 1;
00208         }
00209       }
00210     }
00211 
00212     if( useTempTable ){
00213       /* Generate the subroutine that SELECT calls to process each row of
00214       ** the result.  Store the result in a temporary table
00215       */
00216       srcTab = pParse->nTab++;
00217       sqliteVdbeResolveLabel(v, iInsertBlock);
00218       sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
00219       sqliteVdbeAddOp(v, OP_NewRecno, srcTab, 0);
00220       sqliteVdbeAddOp(v, OP_Pull, 1, 0);
00221       sqliteVdbeAddOp(v, OP_PutIntKey, srcTab, 0);
00222       sqliteVdbeAddOp(v, OP_Return, 0, 0);
00223 
00224       /* The following code runs first because the GOTO at the very top
00225       ** of the program jumps to it.  Create the temporary table, then jump
00226       ** back up and execute the SELECT code above.
00227       */
00228       sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
00229       sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
00230       sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
00231       sqliteVdbeResolveLabel(v, iCleanup);
00232     }else{
00233       sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
00234     }
00235   }else{
00236     /* This is the case if the data for the INSERT is coming from a VALUES
00237     ** clause
00238     */
00239     SrcList dummy;
00240     assert( pList!=0 );
00241     srcTab = -1;
00242     useTempTable = 0;
00243     assert( pList );
00244     nColumn = pList->nExpr;
00245     dummy.nSrc = 0;
00246     for(i=0; i<nColumn; i++){
00247       if( sqliteExprResolveIds(pParse, &dummy, 0, pList->a[i].pExpr) ){
00248         goto insert_cleanup;
00249       }
00250       if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
00251         goto insert_cleanup;
00252       }
00253     }
00254   }
00255 
00256   /* Make sure the number of columns in the source data matches the number
00257   ** of columns to be inserted into the table.
00258   */
00259   if( pColumn==0 && nColumn!=pTab->nCol ){
00260     sqliteErrorMsg(pParse, 
00261        "table %S has %d columns but %d values were supplied",
00262        pTabList, 0, pTab->nCol, nColumn);
00263     goto insert_cleanup;
00264   }
00265   if( pColumn!=0 && nColumn!=pColumn->nId ){
00266     sqliteErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
00267     goto insert_cleanup;
00268   }
00269 
00270   /* If the INSERT statement included an IDLIST term, then make sure
00271   ** all elements of the IDLIST really are columns of the table and 
00272   ** remember the column indices.
00273   **
00274   ** If the table has an INTEGER PRIMARY KEY column and that column
00275   ** is named in the IDLIST, then record in the keyColumn variable
00276   ** the index into IDLIST of the primary key column.  keyColumn is
00277   ** the index of the primary key as it appears in IDLIST, not as
00278   ** is appears in the original table.  (The index of the primary
00279   ** key in the original table is pTab->iPKey.)
00280   */
00281   if( pColumn ){
00282     for(i=0; i<pColumn->nId; i++){
00283       pColumn->a[i].idx = -1;
00284     }
00285     for(i=0; i<pColumn->nId; i++){
00286       for(j=0; j<pTab->nCol; j++){
00287         if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
00288           pColumn->a[i].idx = j;
00289           if( j==pTab->iPKey ){
00290             keyColumn = i;
00291           }
00292           break;
00293         }
00294       }
00295       if( j>=pTab->nCol ){
00296         if( sqliteIsRowid(pColumn->a[i].zName) ){
00297           keyColumn = i;
00298         }else{
00299           sqliteErrorMsg(pParse, "table %S has no column named %s",
00300               pTabList, 0, pColumn->a[i].zName);
00301           pParse->nErr++;
00302           goto insert_cleanup;
00303         }
00304       }
00305     }
00306   }
00307 
00308   /* If there is no IDLIST term but the table has an integer primary
00309   ** key, the set the keyColumn variable to the primary key column index
00310   ** in the original table definition.
00311   */
00312   if( pColumn==0 ){
00313     keyColumn = pTab->iPKey;
00314   }
00315 
00316   /* Open the temp table for FOR EACH ROW triggers
00317   */
00318   if( row_triggers_exist ){
00319     sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
00320   }
00321     
00322   /* Initialize the count of rows to be inserted
00323   */
00324   if( db->flags & SQLITE_CountRows ){
00325     iCntMem = pParse->nMem++;
00326     sqliteVdbeAddOp(v, OP_Integer, 0, 0);
00327     sqliteVdbeAddOp(v, OP_MemStore, iCntMem, 1);
00328   }
00329 
00330   /* Open tables and indices if there are no row triggers */
00331   if( !row_triggers_exist ){
00332     base = pParse->nTab;
00333     idx = sqliteOpenTableAndIndices(pParse, pTab, base);
00334     pParse->nTab += idx;
00335   }
00336 
00337   /* If the data source is a temporary table, then we have to create
00338   ** a loop because there might be multiple rows of data.  If the data
00339   ** source is a subroutine call from the SELECT statement, then we need
00340   ** to launch the SELECT statement processing.
00341   */
00342   if( useTempTable ){
00343     iBreak = sqliteVdbeMakeLabel(v);
00344     sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
00345     iCont = sqliteVdbeCurrentAddr(v);
00346   }else if( pSelect ){
00347     sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
00348     sqliteVdbeResolveLabel(v, iInsertBlock);
00349   }
00350 
00351   /* Run the BEFORE and INSTEAD OF triggers, if there are any
00352   */
00353   endOfLoop = sqliteVdbeMakeLabel(v);
00354   if( before_triggers ){
00355 
00356     /* build the NEW.* reference row.  Note that if there is an INTEGER
00357     ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
00358     ** translated into a unique ID for the row.  But on a BEFORE trigger,
00359     ** we do not know what the unique ID will be (because the insert has
00360     ** not happened yet) so we substitute a rowid of -1
00361     */
00362     if( keyColumn<0 ){
00363       sqliteVdbeAddOp(v, OP_Integer, -1, 0);
00364     }else if( useTempTable ){
00365       sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
00366     }else if( pSelect ){
00367       sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
00368     }else{
00369       sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
00370       sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
00371       sqliteVdbeAddOp(v, OP_Pop, 1, 0);
00372       sqliteVdbeAddOp(v, OP_Integer, -1, 0);
00373       sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
00374     }
00375 
00376     /* Create the new column data
00377     */
00378     for(i=0; i<pTab->nCol; i++){
00379       if( pColumn==0 ){
00380         j = i;
00381       }else{
00382         for(j=0; j<pColumn->nId; j++){
00383           if( pColumn->a[j].idx==i ) break;
00384         }
00385       }
00386       if( pColumn && j>=pColumn->nId ){
00387         sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
00388       }else if( useTempTable ){
00389         sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
00390       }else if( pSelect ){
00391         sqliteVdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
00392       }else{
00393         sqliteExprCode(pParse, pList->a[j].pExpr);
00394       }
00395     }
00396     sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
00397     sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
00398 
00399     /* Fire BEFORE or INSTEAD OF triggers */
00400     if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab, 
00401         newIdx, -1, onError, endOfLoop) ){
00402       goto insert_cleanup;
00403     }
00404   }
00405 
00406   /* If any triggers exists, the opening of tables and indices is deferred
00407   ** until now.
00408   */
00409   if( row_triggers_exist && !isView ){
00410     base = pParse->nTab;
00411     idx = sqliteOpenTableAndIndices(pParse, pTab, base);
00412     pParse->nTab += idx;
00413   }
00414 
00415   /* Push the record number for the new entry onto the stack.  The
00416   ** record number is a randomly generate integer created by NewRecno
00417   ** except when the table has an INTEGER PRIMARY KEY column, in which
00418   ** case the record number is the same as that column. 
00419   */
00420   if( !isView ){
00421     if( keyColumn>=0 ){
00422       if( useTempTable ){
00423         sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
00424       }else if( pSelect ){
00425         sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
00426       }else{
00427         sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
00428       }
00429       /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
00430       ** to generate a unique primary key value.
00431       */
00432       sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
00433       sqliteVdbeAddOp(v, OP_Pop, 1, 0);
00434       sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
00435       sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
00436     }else{
00437       sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
00438     }
00439 
00440     /* Push onto the stack, data for all columns of the new entry, beginning
00441     ** with the first column.
00442     */
00443     for(i=0; i<pTab->nCol; i++){
00444       if( i==pTab->iPKey ){
00445         /* The value of the INTEGER PRIMARY KEY column is always a NULL.
00446         ** Whenever this column is read, the record number will be substituted
00447         ** in its place.  So will fill this column with a NULL to avoid
00448         ** taking up data space with information that will never be used. */
00449         sqliteVdbeAddOp(v, OP_String, 0, 0);
00450         continue;
00451       }
00452       if( pColumn==0 ){
00453         j = i;
00454       }else{
00455         for(j=0; j<pColumn->nId; j++){
00456           if( pColumn->a[j].idx==i ) break;
00457         }
00458       }
00459       if( pColumn && j>=pColumn->nId ){
00460         sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
00461       }else if( useTempTable ){
00462         sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
00463       }else if( pSelect ){
00464         sqliteVdbeAddOp(v, OP_Dup, i+nColumn-j, 1);
00465       }else{
00466         sqliteExprCode(pParse, pList->a[j].pExpr);
00467       }
00468     }
00469 
00470     /* Generate code to check constraints and generate index keys and
00471     ** do the insertion.
00472     */
00473     sqliteGenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0,
00474                                    0, onError, endOfLoop);
00475     sqliteCompleteInsertion(pParse, pTab, base, 0,0,0,
00476                             after_triggers ? newIdx : -1);
00477   }
00478 
00479   /* Update the count of rows that are inserted
00480   */
00481   if( (db->flags & SQLITE_CountRows)!=0 ){
00482     sqliteVdbeAddOp(v, OP_MemIncr, iCntMem, 0);
00483   }
00484 
00485   if( row_triggers_exist ){
00486     /* Close all tables opened */
00487     if( !isView ){
00488       sqliteVdbeAddOp(v, OP_Close, base, 0);
00489       for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
00490         sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
00491       }
00492     }
00493 
00494     /* Code AFTER triggers */
00495     if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1, 
00496           onError, endOfLoop) ){
00497       goto insert_cleanup;
00498     }
00499   }
00500 
00501   /* The bottom of the loop, if the data source is a SELECT statement
00502   */
00503   sqliteVdbeResolveLabel(v, endOfLoop);
00504   if( useTempTable ){
00505     sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
00506     sqliteVdbeResolveLabel(v, iBreak);
00507     sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
00508   }else if( pSelect ){
00509     sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
00510     sqliteVdbeAddOp(v, OP_Return, 0, 0);
00511     sqliteVdbeResolveLabel(v, iCleanup);
00512   }
00513 
00514   if( !row_triggers_exist ){
00515     /* Close all tables opened */
00516     sqliteVdbeAddOp(v, OP_Close, base, 0);
00517     for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
00518       sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
00519     }
00520   }
00521 
00522   sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
00523   sqliteEndWriteOperation(pParse);
00524 
00525   /*
00526   ** Return the number of rows inserted.
00527   */
00528   if( db->flags & SQLITE_CountRows ){
00529     sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows inserted", P3_STATIC);
00530     sqliteVdbeAddOp(v, OP_MemLoad, iCntMem, 0);
00531     sqliteVdbeAddOp(v, OP_Callback, 1, 0);
00532   }
00533 
00534 insert_cleanup:
00535   sqliteSrcListDelete(pTabList);
00536   if( pList ) sqliteExprListDelete(pList);
00537   if( pSelect ) sqliteSelectDelete(pSelect);
00538   sqliteIdListDelete(pColumn);
00539 }
00540 
00541 /*
00542 ** Generate code to do a constraint check prior to an INSERT or an UPDATE.
00543 **
00544 ** When this routine is called, the stack contains (from bottom to top)
00545 ** the following values:
00546 **
00547 **    1.  The recno of the row to be updated before the update.  This
00548 **        value is omitted unless we are doing an UPDATE that involves a
00549 **        change to the record number.
00550 **
00551 **    2.  The recno of the row after the update.
00552 **
00553 **    3.  The data in the first column of the entry after the update.
00554 **
00555 **    i.  Data from middle columns...
00556 **
00557 **    N.  The data in the last column of the entry after the update.
00558 **
00559 ** The old recno shown as entry (1) above is omitted unless both isUpdate
00560 ** and recnoChng are 1.  isUpdate is true for UPDATEs and false for
00561 ** INSERTs and recnoChng is true if the record number is being changed.
00562 **
00563 ** The code generated by this routine pushes additional entries onto
00564 ** the stack which are the keys for new index entries for the new record.
00565 ** The order of index keys is the same as the order of the indices on
00566 ** the pTable->pIndex list.  A key is only created for index i if 
00567 ** aIdxUsed!=0 and aIdxUsed[i]!=0.
00568 **
00569 ** This routine also generates code to check constraints.  NOT NULL,
00570 ** CHECK, and UNIQUE constraints are all checked.  If a constraint fails,
00571 ** then the appropriate action is performed.  There are five possible
00572 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
00573 **
00574 **  Constraint type  Action       What Happens
00575 **  ---------------  ----------   ----------------------------------------
00576 **  any              ROLLBACK     The current transaction is rolled back and
00577 **                                sqlite_exec() returns immediately with a
00578 **                                return code of SQLITE_CONSTRAINT.
00579 **
00580 **  any              ABORT        Back out changes from the current command
00581 **                                only (do not do a complete rollback) then
00582 **                                cause sqlite_exec() to return immediately
00583 **                                with SQLITE_CONSTRAINT.
00584 **
00585 **  any              FAIL         Sqlite_exec() returns immediately with a
00586 **                                return code of SQLITE_CONSTRAINT.  The
00587 **                                transaction is not rolled back and any
00588 **                                prior changes are retained.
00589 **
00590 **  any              IGNORE       The record number and data is popped from
00591 **                                the stack and there is an immediate jump
00592 **                                to label ignoreDest.
00593 **
00594 **  NOT NULL         REPLACE      The NULL value is replace by the default
00595 **                                value for that column.  If the default value
00596 **                                is NULL, the action is the same as ABORT.
00597 **
00598 **  UNIQUE           REPLACE      The other row that conflicts with the row
00599 **                                being inserted is removed.
00600 **
00601 **  CHECK            REPLACE      Illegal.  The results in an exception.
00602 **
00603 ** Which action to take is determined by the overrideError parameter.
00604 ** Or if overrideError==OE_Default, then the pParse->onError parameter
00605 ** is used.  Or if pParse->onError==OE_Default then the onError value
00606 ** for the constraint is used.
00607 **
00608 ** The calling routine must open a read/write cursor for pTab with
00609 ** cursor number "base".  All indices of pTab must also have open
00610 ** read/write cursors with cursor number base+i for the i-th cursor.
00611 ** Except, if there is no possibility of a REPLACE action then
00612 ** cursors do not need to be open for indices where aIdxUsed[i]==0.
00613 **
00614 ** If the isUpdate flag is true, it means that the "base" cursor is
00615 ** initially pointing to an entry that is being updated.  The isUpdate
00616 ** flag causes extra code to be generated so that the "base" cursor
00617 ** is still pointing at the same entry after the routine returns.
00618 ** Without the isUpdate flag, the "base" cursor might be moved.
00619 */
00620 void sqliteGenerateConstraintChecks(
00621   Parse *pParse,      /* The parser context */
00622   Table *pTab,        /* the table into which we are inserting */
00623   int base,           /* Index of a read/write cursor pointing at pTab */
00624   char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
00625   int recnoChng,      /* True if the record number will change */
00626   int isUpdate,       /* True for UPDATE, False for INSERT */
00627   int overrideError,  /* Override onError to this if not OE_Default */
00628   int ignoreDest      /* Jump to this label on an OE_Ignore resolution */
00629 ){
00630   int i;
00631   Vdbe *v;
00632   int nCol;
00633   int onError;
00634   int addr;
00635   int extra;
00636   int iCur;
00637   Index *pIdx;
00638   int seenReplace = 0;
00639   int jumpInst1, jumpInst2;
00640   int contAddr;
00641   int hasTwoRecnos = (isUpdate && recnoChng);
00642 
00643   v = sqliteGetVdbe(pParse);
00644   assert( v!=0 );
00645   assert( pTab->pSelect==0 );  /* This table is not a VIEW */
00646   nCol = pTab->nCol;
00647 
00648   /* Test all NOT NULL constraints.
00649   */
00650   for(i=0; i<nCol; i++){
00651     if( i==pTab->iPKey ){
00652       continue;
00653     }
00654     onError = pTab->aCol[i].notNull;
00655     if( onError==OE_None ) continue;
00656     if( overrideError!=OE_Default ){
00657       onError = overrideError;
00658     }else if( pParse->db->onError!=OE_Default ){
00659       onError = pParse->db->onError;
00660     }else if( onError==OE_Default ){
00661       onError = OE_Abort;
00662     }
00663     if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
00664       onError = OE_Abort;
00665     }
00666     sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
00667     addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
00668     switch( onError ){
00669       case OE_Rollback:
00670       case OE_Abort:
00671       case OE_Fail: {
00672         char *zMsg = 0;
00673         sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
00674         sqliteSetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
00675                         " may not be NULL", (char*)0);
00676         sqliteVdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
00677         break;
00678       }
00679       case OE_Ignore: {
00680         sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
00681         sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
00682         break;
00683       }
00684       case OE_Replace: {
00685         sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
00686         sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
00687         break;
00688       }
00689       default: assert(0);
00690     }
00691     sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
00692   }
00693 
00694   /* Test all CHECK constraints
00695   */
00696   /**** TBD ****/
00697 
00698   /* If we have an INTEGER PRIMARY KEY, make sure the primary key
00699   ** of the new record does not previously exist.  Except, if this
00700   ** is an UPDATE and the primary key is not changing, that is OK.
00701   */
00702   if( recnoChng ){
00703     onError = pTab->keyConf;
00704     if( overrideError!=OE_Default ){
00705       onError = overrideError;
00706     }else if( pParse->db->onError!=OE_Default ){
00707       onError = pParse->db->onError;
00708     }else if( onError==OE_Default ){
00709       onError = OE_Abort;
00710     }
00711     
00712     if( isUpdate ){
00713       sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
00714       sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
00715       jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
00716     }
00717     sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
00718     jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
00719     switch( onError ){
00720       default: {
00721         onError = OE_Abort;
00722         /* Fall thru into the next case */
00723       }
00724       case OE_Rollback:
00725       case OE_Abort:
00726       case OE_Fail: {
00727         sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
00728                          "PRIMARY KEY must be unique", P3_STATIC);
00729         break;
00730       }
00731       case OE_Replace: {
00732         sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0);
00733         if( isUpdate ){
00734           sqliteVdbeAddOp(v, OP_Dup, nCol+hasTwoRecnos, 1);
00735           sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
00736         }
00737         seenReplace = 1;
00738         break;
00739       }
00740       case OE_Ignore: {
00741         assert( seenReplace==0 );
00742         sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
00743         sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
00744         break;
00745       }
00746     }
00747     contAddr = sqliteVdbeCurrentAddr(v);
00748     sqliteVdbeChangeP2(v, jumpInst2, contAddr);
00749     if( isUpdate ){
00750       sqliteVdbeChangeP2(v, jumpInst1, contAddr);
00751       sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
00752       sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
00753     }
00754   }
00755 
00756   /* Test all UNIQUE constraints by creating entries for each UNIQUE
00757   ** index and making sure that duplicate entries do not already exist.
00758   ** Add the new records to the indices as we go.
00759   */
00760   extra = -1;
00761   for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
00762     if( aIdxUsed && aIdxUsed[iCur]==0 ) continue;  /* Skip unused indices */
00763     extra++;
00764 
00765     /* Create a key for accessing the index entry */
00766     sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
00767     for(i=0; i<pIdx->nColumn; i++){
00768       int idx = pIdx->aiColumn[i];
00769       if( idx==pTab->iPKey ){
00770         sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
00771       }else{
00772         sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
00773       }
00774     }
00775     jumpInst1 = sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
00776     if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
00777 
00778     /* Find out what action to take in case there is an indexing conflict */
00779     onError = pIdx->onError;
00780     if( onError==OE_None ) continue;  /* pIdx is not a UNIQUE index */
00781     if( overrideError!=OE_Default ){
00782       onError = overrideError;
00783     }else if( pParse->db->onError!=OE_Default ){
00784       onError = pParse->db->onError;
00785     }else if( onError==OE_Default ){
00786       onError = OE_Abort;
00787     }
00788     if( seenReplace ){
00789       if( onError==OE_Ignore ) onError = OE_Replace;
00790       else if( onError==OE_Fail ) onError = OE_Abort;
00791     }
00792     
00793 
00794     /* Check to see if the new index entry will be unique */
00795     sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
00796     jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
00797 
00798     /* Generate code that executes if the new index entry is not unique */
00799     switch( onError ){
00800       case OE_Rollback:
00801       case OE_Abort:
00802       case OE_Fail: {
00803         int j, n1, n2;
00804         char zErrMsg[200];
00805         strcpy(zErrMsg, pIdx->nColumn>1 ? "columns " : "column ");
00806         n1 = strlen(zErrMsg);
00807         for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
00808           char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
00809           n2 = strlen(zCol);
00810           if( j>0 ){
00811             strcpy(&zErrMsg[n1], ", ");
00812             n1 += 2;
00813           }
00814           if( n1+n2>sizeof(zErrMsg)-30 ){
00815             strcpy(&zErrMsg[n1], "...");
00816             n1 += 3;
00817             break;
00818           }else{
00819             strcpy(&zErrMsg[n1], zCol);
00820             n1 += n2;
00821           }
00822         }
00823         strcpy(&zErrMsg[n1], 
00824             pIdx->nColumn>1 ? " are not unique" : " is not unique");
00825         sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0);
00826         break;
00827       }
00828       case OE_Ignore: {
00829         assert( seenReplace==0 );
00830         sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
00831         sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
00832         break;
00833       }
00834       case OE_Replace: {
00835         sqliteGenerateRowDelete(pParse->db, v, pTab, base, 0);
00836         if( isUpdate ){
00837           sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
00838           sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
00839         }
00840         seenReplace = 1;
00841         break;
00842       }
00843       default: assert(0);
00844     }
00845     contAddr = sqliteVdbeCurrentAddr(v);
00846 #if NULL_DISTINCT_FOR_UNIQUE
00847     sqliteVdbeChangeP2(v, jumpInst1, contAddr);
00848 #endif
00849     sqliteVdbeChangeP2(v, jumpInst2, contAddr);
00850   }
00851 }
00852 
00853 /*
00854 ** This routine generates code to finish the INSERT or UPDATE operation
00855 ** that was started by a prior call to sqliteGenerateConstraintChecks.
00856 ** The stack must contain keys for all active indices followed by data
00857 ** and the recno for the new entry.  This routine creates the new
00858 ** entries in all indices and in the main table.
00859 **
00860 ** The arguments to this routine should be the same as the first six
00861 ** arguments to sqliteGenerateConstraintChecks.
00862 */
00863 void sqliteCompleteInsertion(
00864   Parse *pParse,      /* The parser context */
00865   Table *pTab,        /* the table into which we are inserting */
00866   int base,           /* Index of a read/write cursor pointing at pTab */
00867   char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
00868   int recnoChng,      /* True if the record number will change */
00869   int isUpdate,       /* True for UPDATE, False for INSERT */
00870   int newIdx          /* Index of NEW table for triggers.  -1 if none */
00871 ){
00872   int i;
00873   Vdbe *v;
00874   int nIdx;
00875   Index *pIdx;
00876 
00877   v = sqliteGetVdbe(pParse);
00878   assert( v!=0 );
00879   assert( pTab->pSelect==0 );  /* This table is not a VIEW */
00880   for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
00881   for(i=nIdx-1; i>=0; i--){
00882     if( aIdxUsed && aIdxUsed[i]==0 ) continue;
00883     sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
00884   }
00885   sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
00886   if( newIdx>=0 ){
00887     sqliteVdbeAddOp(v, OP_Dup, 1, 0);
00888     sqliteVdbeAddOp(v, OP_Dup, 1, 0);
00889     sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
00890   }
00891   sqliteVdbeAddOp(v, OP_PutIntKey, base,
00892     (pParse->trigStack?0:OPFLAG_NCHANGE) |
00893     (isUpdate?0:OPFLAG_LASTROWID) | OPFLAG_CSCHANGE);
00894   if( isUpdate && recnoChng ){
00895     sqliteVdbeAddOp(v, OP_Pop, 1, 0);
00896   }
00897 }
00898 
00899 /*
00900 ** Generate code that will open write cursors for a table and for all
00901 ** indices of that table.  The "base" parameter is the cursor number used
00902 ** for the table.  Indices are opened on subsequent cursors.
00903 **
00904 ** Return the total number of cursors opened.  This is always at least
00905 ** 1 (for the main table) plus more for each cursor.
00906 */
00907 int sqliteOpenTableAndIndices(Parse *pParse, Table *pTab, int base){
00908   int i;
00909   Index *pIdx;
00910   Vdbe *v = sqliteGetVdbe(pParse);
00911   assert( v!=0 );
00912   sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
00913   sqliteVdbeOp3(v, OP_OpenWrite, base, pTab->tnum, pTab->zName, P3_STATIC);
00914   for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
00915     sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
00916     sqliteVdbeOp3(v, OP_OpenWrite, i+base, pIdx->tnum, pIdx->zName, P3_STATIC);
00917   }
00918   return i;
00919 }