Annotation of embedaddon/php/ext/sqlite/libsqlite/src/insert.c, revision 1.1
1.1 ! misho 1: /*
! 2: ** 2001 September 15
! 3: **
! 4: ** The author disclaims copyright to this source code. In place of
! 5: ** a legal notice, here is a blessing:
! 6: **
! 7: ** May you do good and not evil.
! 8: ** May you find forgiveness for yourself and forgive others.
! 9: ** May you share freely, never taking more than you give.
! 10: **
! 11: *************************************************************************
! 12: ** This file contains C code routines that are called by the parser
! 13: ** to handle INSERT statements in SQLite.
! 14: **
! 15: ** $Id: insert.c 195361 2005-09-07 15:11:33Z iliaa $
! 16: */
! 17: #include "sqliteInt.h"
! 18:
! 19: /*
! 20: ** This routine is call to handle SQL of the following forms:
! 21: **
! 22: ** insert into TABLE (IDLIST) values(EXPRLIST)
! 23: ** insert into TABLE (IDLIST) select
! 24: **
! 25: ** The IDLIST following the table name is always optional. If omitted,
! 26: ** then a list of all columns for the table is substituted. The IDLIST
! 27: ** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted.
! 28: **
! 29: ** The pList parameter holds EXPRLIST in the first form of the INSERT
! 30: ** statement above, and pSelect is NULL. For the second form, pList is
! 31: ** NULL and pSelect is a pointer to the select statement used to generate
! 32: ** data for the insert.
! 33: **
! 34: ** The code generated follows one of three templates. For a simple
! 35: ** select with data coming from a VALUES clause, the code executes
! 36: ** once straight down through. The template looks like this:
! 37: **
! 38: ** open write cursor to <table> and its indices
! 39: ** puts VALUES clause expressions onto the stack
! 40: ** write the resulting record into <table>
! 41: ** cleanup
! 42: **
! 43: ** If the statement is of the form
! 44: **
! 45: ** INSERT INTO <table> SELECT ...
! 46: **
! 47: ** And the SELECT clause does not read from <table> at any time, then
! 48: ** the generated code follows this template:
! 49: **
! 50: ** goto B
! 51: ** A: setup for the SELECT
! 52: ** loop over the tables in the SELECT
! 53: ** gosub C
! 54: ** end loop
! 55: ** cleanup after the SELECT
! 56: ** goto D
! 57: ** B: open write cursor to <table> and its indices
! 58: ** goto A
! 59: ** C: insert the select result into <table>
! 60: ** return
! 61: ** D: cleanup
! 62: **
! 63: ** The third template is used if the insert statement takes its
! 64: ** values from a SELECT but the data is being inserted into a table
! 65: ** that is also read as part of the SELECT. In the third form,
! 66: ** we have to use a intermediate table to store the results of
! 67: ** the select. The template is like this:
! 68: **
! 69: ** goto B
! 70: ** A: setup for the SELECT
! 71: ** loop over the tables in the SELECT
! 72: ** gosub C
! 73: ** end loop
! 74: ** cleanup after the SELECT
! 75: ** goto D
! 76: ** C: insert the select result into the intermediate table
! 77: ** return
! 78: ** B: open a cursor to an intermediate table
! 79: ** goto A
! 80: ** D: open write cursor to <table> and its indices
! 81: ** loop over the intermediate table
! 82: ** transfer values form intermediate table into <table>
! 83: ** end the loop
! 84: ** cleanup
! 85: */
! 86: void sqliteInsert(
! 87: Parse *pParse, /* Parser context */
! 88: SrcList *pTabList, /* Name of table into which we are inserting */
! 89: ExprList *pList, /* List of values to be inserted */
! 90: Select *pSelect, /* A SELECT statement to use as the data source */
! 91: IdList *pColumn, /* Column names corresponding to IDLIST. */
! 92: int onError /* How to handle constraint errors */
! 93: ){
! 94: Table *pTab; /* The table to insert into */
! 95: char *zTab; /* Name of the table into which we are inserting */
! 96: const char *zDb; /* Name of the database holding this table */
! 97: int i, j, idx; /* Loop counters */
! 98: Vdbe *v; /* Generate code into this virtual machine */
! 99: Index *pIdx; /* For looping over indices of the table */
! 100: int nColumn; /* Number of columns in the data */
! 101: int base; /* VDBE Cursor number for pTab */
! 102: int iCont, iBreak; /* Beginning and end of the loop over srcTab */
! 103: sqlite *db; /* The main database structure */
! 104: int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
! 105: int endOfLoop; /* Label for the end of the insertion loop */
! 106: int useTempTable; /* Store SELECT results in intermediate table */
! 107: int srcTab; /* Data comes from this temporary cursor if >=0 */
! 108: int iSelectLoop; /* Address of code that implements the SELECT */
! 109: int iCleanup; /* Address of the cleanup code */
! 110: int iInsertBlock; /* Address of the subroutine used to insert data */
! 111: int iCntMem; /* Memory cell used for the row counter */
! 112: int isView; /* True if attempting to insert into a view */
! 113:
! 114: int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
! 115: int before_triggers; /* True if there are BEFORE triggers */
! 116: int after_triggers; /* True if there are AFTER triggers */
! 117: int newIdx = -1; /* Cursor for the NEW table */
! 118:
! 119: if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
! 120: db = pParse->db;
! 121:
! 122: /* Locate the table into which we will be inserting new information.
! 123: */
! 124: assert( pTabList->nSrc==1 );
! 125: zTab = pTabList->a[0].zName;
! 126: if( zTab==0 ) goto insert_cleanup;
! 127: pTab = sqliteSrcListLookup(pParse, pTabList);
! 128: if( pTab==0 ){
! 129: goto insert_cleanup;
! 130: }
! 131: assert( pTab->iDb<db->nDb );
! 132: zDb = db->aDb[pTab->iDb].zName;
! 133: if( sqliteAuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
! 134: goto insert_cleanup;
! 135: }
! 136:
! 137: /* Ensure that:
! 138: * (a) the table is not read-only,
! 139: * (b) that if it is a view then ON INSERT triggers exist
! 140: */
! 141: before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
! 142: TK_BEFORE, TK_ROW, 0);
! 143: after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
! 144: TK_AFTER, TK_ROW, 0);
! 145: row_triggers_exist = before_triggers || after_triggers;
! 146: isView = pTab->pSelect!=0;
! 147: if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
! 148: goto insert_cleanup;
! 149: }
! 150: if( pTab==0 ) goto insert_cleanup;
! 151:
! 152: /* If pTab is really a view, make sure it has been initialized.
! 153: */
! 154: if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
! 155: goto insert_cleanup;
! 156: }
! 157:
! 158: /* Allocate a VDBE
! 159: */
! 160: v = sqliteGetVdbe(pParse);
! 161: if( v==0 ) goto insert_cleanup;
! 162: sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist, pTab->iDb);
! 163:
! 164: /* if there are row triggers, allocate a temp table for new.* references. */
! 165: if( row_triggers_exist ){
! 166: newIdx = pParse->nTab++;
! 167: }
! 168:
! 169: /* Figure out how many columns of data are supplied. If the data
! 170: ** is coming from a SELECT statement, then this step also generates
! 171: ** all the code to implement the SELECT statement and invoke a subroutine
! 172: ** to process each row of the result. (Template 2.) If the SELECT
! 173: ** statement uses the the table that is being inserted into, then the
! 174: ** subroutine is also coded here. That subroutine stores the SELECT
! 175: ** results in a temporary table. (Template 3.)
! 176: */
! 177: if( pSelect ){
! 178: /* Data is coming from a SELECT. Generate code to implement that SELECT
! 179: */
! 180: int rc, iInitCode;
! 181: iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
! 182: iSelectLoop = sqliteVdbeCurrentAddr(v);
! 183: iInsertBlock = sqliteVdbeMakeLabel(v);
! 184: rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0);
! 185: if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
! 186: iCleanup = sqliteVdbeMakeLabel(v);
! 187: sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup);
! 188: assert( pSelect->pEList );
! 189: nColumn = pSelect->pEList->nExpr;
! 190:
! 191: /* Set useTempTable to TRUE if the result of the SELECT statement
! 192: ** should be written into a temporary table. Set to FALSE if each
! 193: ** row of the SELECT can be written directly into the result table.
! 194: **
! 195: ** A temp table must be used if the table being updated is also one
! 196: ** of the tables being read by the SELECT statement. Also use a
! 197: ** temp table in the case of row triggers.
! 198: */
! 199: if( row_triggers_exist ){
! 200: useTempTable = 1;
! 201: }else{
! 202: int addr = sqliteVdbeFindOp(v, OP_OpenRead, pTab->tnum);
! 203: useTempTable = 0;
! 204: if( addr>0 ){
! 205: VdbeOp *pOp = sqliteVdbeGetOp(v, addr-2);
! 206: if( pOp->opcode==OP_Integer && pOp->p1==pTab->iDb ){
! 207: useTempTable = 1;
! 208: }
! 209: }
! 210: }
! 211:
! 212: if( useTempTable ){
! 213: /* Generate the subroutine that SELECT calls to process each row of
! 214: ** the result. Store the result in a temporary table
! 215: */
! 216: srcTab = pParse->nTab++;
! 217: sqliteVdbeResolveLabel(v, iInsertBlock);
! 218: sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
! 219: sqliteVdbeAddOp(v, OP_NewRecno, srcTab, 0);
! 220: sqliteVdbeAddOp(v, OP_Pull, 1, 0);
! 221: sqliteVdbeAddOp(v, OP_PutIntKey, srcTab, 0);
! 222: sqliteVdbeAddOp(v, OP_Return, 0, 0);
! 223:
! 224: /* The following code runs first because the GOTO at the very top
! 225: ** of the program jumps to it. Create the temporary table, then jump
! 226: ** back up and execute the SELECT code above.
! 227: */
! 228: sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
! 229: sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
! 230: sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
! 231: sqliteVdbeResolveLabel(v, iCleanup);
! 232: }else{
! 233: sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
! 234: }
! 235: }else{
! 236: /* This is the case if the data for the INSERT is coming from a VALUES
! 237: ** clause
! 238: */
! 239: SrcList dummy;
! 240: assert( pList!=0 );
! 241: srcTab = -1;
! 242: useTempTable = 0;
! 243: assert( pList );
! 244: nColumn = pList->nExpr;
! 245: dummy.nSrc = 0;
! 246: for(i=0; i<nColumn; i++){
! 247: if( sqliteExprResolveIds(pParse, &dummy, 0, pList->a[i].pExpr) ){
! 248: goto insert_cleanup;
! 249: }
! 250: if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
! 251: goto insert_cleanup;
! 252: }
! 253: }
! 254: }
! 255:
! 256: /* Make sure the number of columns in the source data matches the number
! 257: ** of columns to be inserted into the table.
! 258: */
! 259: if( pColumn==0 && nColumn!=pTab->nCol ){
! 260: sqliteErrorMsg(pParse,
! 261: "table %S has %d columns but %d values were supplied",
! 262: pTabList, 0, pTab->nCol, nColumn);
! 263: goto insert_cleanup;
! 264: }
! 265: if( pColumn!=0 && nColumn!=pColumn->nId ){
! 266: sqliteErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
! 267: goto insert_cleanup;
! 268: }
! 269:
! 270: /* If the INSERT statement included an IDLIST term, then make sure
! 271: ** all elements of the IDLIST really are columns of the table and
! 272: ** remember the column indices.
! 273: **
! 274: ** If the table has an INTEGER PRIMARY KEY column and that column
! 275: ** is named in the IDLIST, then record in the keyColumn variable
! 276: ** the index into IDLIST of the primary key column. keyColumn is
! 277: ** the index of the primary key as it appears in IDLIST, not as
! 278: ** is appears in the original table. (The index of the primary
! 279: ** key in the original table is pTab->iPKey.)
! 280: */
! 281: if( pColumn ){
! 282: for(i=0; i<pColumn->nId; i++){
! 283: pColumn->a[i].idx = -1;
! 284: }
! 285: for(i=0; i<pColumn->nId; i++){
! 286: for(j=0; j<pTab->nCol; j++){
! 287: if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
! 288: pColumn->a[i].idx = j;
! 289: if( j==pTab->iPKey ){
! 290: keyColumn = i;
! 291: }
! 292: break;
! 293: }
! 294: }
! 295: if( j>=pTab->nCol ){
! 296: if( sqliteIsRowid(pColumn->a[i].zName) ){
! 297: keyColumn = i;
! 298: }else{
! 299: sqliteErrorMsg(pParse, "table %S has no column named %s",
! 300: pTabList, 0, pColumn->a[i].zName);
! 301: pParse->nErr++;
! 302: goto insert_cleanup;
! 303: }
! 304: }
! 305: }
! 306: }
! 307:
! 308: /* If there is no IDLIST term but the table has an integer primary
! 309: ** key, the set the keyColumn variable to the primary key column index
! 310: ** in the original table definition.
! 311: */
! 312: if( pColumn==0 ){
! 313: keyColumn = pTab->iPKey;
! 314: }
! 315:
! 316: /* Open the temp table for FOR EACH ROW triggers
! 317: */
! 318: if( row_triggers_exist ){
! 319: sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
! 320: }
! 321:
! 322: /* Initialize the count of rows to be inserted
! 323: */
! 324: if( db->flags & SQLITE_CountRows ){
! 325: iCntMem = pParse->nMem++;
! 326: sqliteVdbeAddOp(v, OP_Integer, 0, 0);
! 327: sqliteVdbeAddOp(v, OP_MemStore, iCntMem, 1);
! 328: }
! 329:
! 330: /* Open tables and indices if there are no row triggers */
! 331: if( !row_triggers_exist ){
! 332: base = pParse->nTab;
! 333: idx = sqliteOpenTableAndIndices(pParse, pTab, base);
! 334: pParse->nTab += idx;
! 335: }
! 336:
! 337: /* If the data source is a temporary table, then we have to create
! 338: ** a loop because there might be multiple rows of data. If the data
! 339: ** source is a subroutine call from the SELECT statement, then we need
! 340: ** to launch the SELECT statement processing.
! 341: */
! 342: if( useTempTable ){
! 343: iBreak = sqliteVdbeMakeLabel(v);
! 344: sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
! 345: iCont = sqliteVdbeCurrentAddr(v);
! 346: }else if( pSelect ){
! 347: sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
! 348: sqliteVdbeResolveLabel(v, iInsertBlock);
! 349: }
! 350:
! 351: /* Run the BEFORE and INSTEAD OF triggers, if there are any
! 352: */
! 353: endOfLoop = sqliteVdbeMakeLabel(v);
! 354: if( before_triggers ){
! 355:
! 356: /* build the NEW.* reference row. Note that if there is an INTEGER
! 357: ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
! 358: ** translated into a unique ID for the row. But on a BEFORE trigger,
! 359: ** we do not know what the unique ID will be (because the insert has
! 360: ** not happened yet) so we substitute a rowid of -1
! 361: */
! 362: if( keyColumn<0 ){
! 363: sqliteVdbeAddOp(v, OP_Integer, -1, 0);
! 364: }else if( useTempTable ){
! 365: sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
! 366: }else if( pSelect ){
! 367: sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
! 368: }else{
! 369: sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
! 370: sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
! 371: sqliteVdbeAddOp(v, OP_Pop, 1, 0);
! 372: sqliteVdbeAddOp(v, OP_Integer, -1, 0);
! 373: sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
! 374: }
! 375:
! 376: /* Create the new column data
! 377: */
! 378: for(i=0; i<pTab->nCol; i++){
! 379: if( pColumn==0 ){
! 380: j = i;
! 381: }else{
! 382: for(j=0; j<pColumn->nId; j++){
! 383: if( pColumn->a[j].idx==i ) break;
! 384: }
! 385: }
! 386: if( pColumn && j>=pColumn->nId ){
! 387: sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
! 388: }else if( useTempTable ){
! 389: sqliteVdbeAddOp(v, OP_Column, srcTab, j);
! 390: }else if( pSelect ){
! 391: sqliteVdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
! 392: }else{
! 393: sqliteExprCode(pParse, pList->a[j].pExpr);
! 394: }
! 395: }
! 396: sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
! 397: sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
! 398:
! 399: /* Fire BEFORE or INSTEAD OF triggers */
! 400: if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab,
! 401: newIdx, -1, onError, endOfLoop) ){
! 402: goto insert_cleanup;
! 403: }
! 404: }
! 405:
! 406: /* If any triggers exists, the opening of tables and indices is deferred
! 407: ** until now.
! 408: */
! 409: if( row_triggers_exist && !isView ){
! 410: base = pParse->nTab;
! 411: idx = sqliteOpenTableAndIndices(pParse, pTab, base);
! 412: pParse->nTab += idx;
! 413: }
! 414:
! 415: /* Push the record number for the new entry onto the stack. The
! 416: ** record number is a randomly generate integer created by NewRecno
! 417: ** except when the table has an INTEGER PRIMARY KEY column, in which
! 418: ** case the record number is the same as that column.
! 419: */
! 420: if( !isView ){
! 421: if( keyColumn>=0 ){
! 422: if( useTempTable ){
! 423: sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
! 424: }else if( pSelect ){
! 425: sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
! 426: }else{
! 427: sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
! 428: }
! 429: /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
! 430: ** to generate a unique primary key value.
! 431: */
! 432: sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
! 433: sqliteVdbeAddOp(v, OP_Pop, 1, 0);
! 434: sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
! 435: sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
! 436: }else{
! 437: sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
! 438: }
! 439:
! 440: /* Push onto the stack, data for all columns of the new entry, beginning
! 441: ** with the first column.
! 442: */
! 443: for(i=0; i<pTab->nCol; i++){
! 444: if( i==pTab->iPKey ){
! 445: /* The value of the INTEGER PRIMARY KEY column is always a NULL.
! 446: ** Whenever this column is read, the record number will be substituted
! 447: ** in its place. So will fill this column with a NULL to avoid
! 448: ** taking up data space with information that will never be used. */
! 449: sqliteVdbeAddOp(v, OP_String, 0, 0);
! 450: continue;
! 451: }
! 452: if( pColumn==0 ){
! 453: j = i;
! 454: }else{
! 455: for(j=0; j<pColumn->nId; j++){
! 456: if( pColumn->a[j].idx==i ) break;
! 457: }
! 458: }
! 459: if( pColumn && j>=pColumn->nId ){
! 460: sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
! 461: }else if( useTempTable ){
! 462: sqliteVdbeAddOp(v, OP_Column, srcTab, j);
! 463: }else if( pSelect ){
! 464: sqliteVdbeAddOp(v, OP_Dup, i+nColumn-j, 1);
! 465: }else{
! 466: sqliteExprCode(pParse, pList->a[j].pExpr);
! 467: }
! 468: }
! 469:
! 470: /* Generate code to check constraints and generate index keys and
! 471: ** do the insertion.
! 472: */
! 473: sqliteGenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0,
! 474: 0, onError, endOfLoop);
! 475: sqliteCompleteInsertion(pParse, pTab, base, 0,0,0,
! 476: after_triggers ? newIdx : -1);
! 477: }
! 478:
! 479: /* Update the count of rows that are inserted
! 480: */
! 481: if( (db->flags & SQLITE_CountRows)!=0 ){
! 482: sqliteVdbeAddOp(v, OP_MemIncr, iCntMem, 0);
! 483: }
! 484:
! 485: if( row_triggers_exist ){
! 486: /* Close all tables opened */
! 487: if( !isView ){
! 488: sqliteVdbeAddOp(v, OP_Close, base, 0);
! 489: for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
! 490: sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
! 491: }
! 492: }
! 493:
! 494: /* Code AFTER triggers */
! 495: if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1,
! 496: onError, endOfLoop) ){
! 497: goto insert_cleanup;
! 498: }
! 499: }
! 500:
! 501: /* The bottom of the loop, if the data source is a SELECT statement
! 502: */
! 503: sqliteVdbeResolveLabel(v, endOfLoop);
! 504: if( useTempTable ){
! 505: sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
! 506: sqliteVdbeResolveLabel(v, iBreak);
! 507: sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
! 508: }else if( pSelect ){
! 509: sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
! 510: sqliteVdbeAddOp(v, OP_Return, 0, 0);
! 511: sqliteVdbeResolveLabel(v, iCleanup);
! 512: }
! 513:
! 514: if( !row_triggers_exist ){
! 515: /* Close all tables opened */
! 516: sqliteVdbeAddOp(v, OP_Close, base, 0);
! 517: for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
! 518: sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
! 519: }
! 520: }
! 521:
! 522: sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
! 523: sqliteEndWriteOperation(pParse);
! 524:
! 525: /*
! 526: ** Return the number of rows inserted.
! 527: */
! 528: if( db->flags & SQLITE_CountRows ){
! 529: sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows inserted", P3_STATIC);
! 530: sqliteVdbeAddOp(v, OP_MemLoad, iCntMem, 0);
! 531: sqliteVdbeAddOp(v, OP_Callback, 1, 0);
! 532: }
! 533:
! 534: insert_cleanup:
! 535: sqliteSrcListDelete(pTabList);
! 536: if( pList ) sqliteExprListDelete(pList);
! 537: if( pSelect ) sqliteSelectDelete(pSelect);
! 538: sqliteIdListDelete(pColumn);
! 539: }
! 540:
! 541: /*
! 542: ** Generate code to do a constraint check prior to an INSERT or an UPDATE.
! 543: **
! 544: ** When this routine is called, the stack contains (from bottom to top)
! 545: ** the following values:
! 546: **
! 547: ** 1. The recno of the row to be updated before the update. This
! 548: ** value is omitted unless we are doing an UPDATE that involves a
! 549: ** change to the record number.
! 550: **
! 551: ** 2. The recno of the row after the update.
! 552: **
! 553: ** 3. The data in the first column of the entry after the update.
! 554: **
! 555: ** i. Data from middle columns...
! 556: **
! 557: ** N. The data in the last column of the entry after the update.
! 558: **
! 559: ** The old recno shown as entry (1) above is omitted unless both isUpdate
! 560: ** and recnoChng are 1. isUpdate is true for UPDATEs and false for
! 561: ** INSERTs and recnoChng is true if the record number is being changed.
! 562: **
! 563: ** The code generated by this routine pushes additional entries onto
! 564: ** the stack which are the keys for new index entries for the new record.
! 565: ** The order of index keys is the same as the order of the indices on
! 566: ** the pTable->pIndex list. A key is only created for index i if
! 567: ** aIdxUsed!=0 and aIdxUsed[i]!=0.
! 568: **
! 569: ** This routine also generates code to check constraints. NOT NULL,
! 570: ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
! 571: ** then the appropriate action is performed. There are five possible
! 572: ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
! 573: **
! 574: ** Constraint type Action What Happens
! 575: ** --------------- ---------- ----------------------------------------
! 576: ** any ROLLBACK The current transaction is rolled back and
! 577: ** sqlite_exec() returns immediately with a
! 578: ** return code of SQLITE_CONSTRAINT.
! 579: **
! 580: ** any ABORT Back out changes from the current command
! 581: ** only (do not do a complete rollback) then
! 582: ** cause sqlite_exec() to return immediately
! 583: ** with SQLITE_CONSTRAINT.
! 584: **
! 585: ** any FAIL Sqlite_exec() returns immediately with a
! 586: ** return code of SQLITE_CONSTRAINT. The
! 587: ** transaction is not rolled back and any
! 588: ** prior changes are retained.
! 589: **
! 590: ** any IGNORE The record number and data is popped from
! 591: ** the stack and there is an immediate jump
! 592: ** to label ignoreDest.
! 593: **
! 594: ** NOT NULL REPLACE The NULL value is replace by the default
! 595: ** value for that column. If the default value
! 596: ** is NULL, the action is the same as ABORT.
! 597: **
! 598: ** UNIQUE REPLACE The other row that conflicts with the row
! 599: ** being inserted is removed.
! 600: **
! 601: ** CHECK REPLACE Illegal. The results in an exception.
! 602: **
! 603: ** Which action to take is determined by the overrideError parameter.
! 604: ** Or if overrideError==OE_Default, then the pParse->onError parameter
! 605: ** is used. Or if pParse->onError==OE_Default then the onError value
! 606: ** for the constraint is used.
! 607: **
! 608: ** The calling routine must open a read/write cursor for pTab with
! 609: ** cursor number "base". All indices of pTab must also have open
! 610: ** read/write cursors with cursor number base+i for the i-th cursor.
! 611: ** Except, if there is no possibility of a REPLACE action then
! 612: ** cursors do not need to be open for indices where aIdxUsed[i]==0.
! 613: **
! 614: ** If the isUpdate flag is true, it means that the "base" cursor is
! 615: ** initially pointing to an entry that is being updated. The isUpdate
! 616: ** flag causes extra code to be generated so that the "base" cursor
! 617: ** is still pointing at the same entry after the routine returns.
! 618: ** Without the isUpdate flag, the "base" cursor might be moved.
! 619: */
! 620: void sqliteGenerateConstraintChecks(
! 621: Parse *pParse, /* The parser context */
! 622: Table *pTab, /* the table into which we are inserting */
! 623: int base, /* Index of a read/write cursor pointing at pTab */
! 624: char *aIdxUsed, /* Which indices are used. NULL means all are used */
! 625: int recnoChng, /* True if the record number will change */
! 626: int isUpdate, /* True for UPDATE, False for INSERT */
! 627: int overrideError, /* Override onError to this if not OE_Default */
! 628: int ignoreDest /* Jump to this label on an OE_Ignore resolution */
! 629: ){
! 630: int i;
! 631: Vdbe *v;
! 632: int nCol;
! 633: int onError;
! 634: int addr;
! 635: int extra;
! 636: int iCur;
! 637: Index *pIdx;
! 638: int seenReplace = 0;
! 639: int jumpInst1, jumpInst2;
! 640: int contAddr;
! 641: int hasTwoRecnos = (isUpdate && recnoChng);
! 642:
! 643: v = sqliteGetVdbe(pParse);
! 644: assert( v!=0 );
! 645: assert( pTab->pSelect==0 ); /* This table is not a VIEW */
! 646: nCol = pTab->nCol;
! 647:
! 648: /* Test all NOT NULL constraints.
! 649: */
! 650: for(i=0; i<nCol; i++){
! 651: if( i==pTab->iPKey ){
! 652: continue;
! 653: }
! 654: onError = pTab->aCol[i].notNull;
! 655: if( onError==OE_None ) continue;
! 656: if( overrideError!=OE_Default ){
! 657: onError = overrideError;
! 658: }else if( pParse->db->onError!=OE_Default ){
! 659: onError = pParse->db->onError;
! 660: }else if( onError==OE_Default ){
! 661: onError = OE_Abort;
! 662: }
! 663: if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
! 664: onError = OE_Abort;
! 665: }
! 666: sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
! 667: addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
! 668: switch( onError ){
! 669: case OE_Rollback:
! 670: case OE_Abort:
! 671: case OE_Fail: {
! 672: char *zMsg = 0;
! 673: sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
! 674: sqliteSetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
! 675: " may not be NULL", (char*)0);
! 676: sqliteVdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
! 677: break;
! 678: }
! 679: case OE_Ignore: {
! 680: sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
! 681: sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
! 682: break;
! 683: }
! 684: case OE_Replace: {
! 685: sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
! 686: sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
! 687: break;
! 688: }
! 689: default: assert(0);
! 690: }
! 691: sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
! 692: }
! 693:
! 694: /* Test all CHECK constraints
! 695: */
! 696: /**** TBD ****/
! 697:
! 698: /* If we have an INTEGER PRIMARY KEY, make sure the primary key
! 699: ** of the new record does not previously exist. Except, if this
! 700: ** is an UPDATE and the primary key is not changing, that is OK.
! 701: */
! 702: if( recnoChng ){
! 703: onError = pTab->keyConf;
! 704: if( overrideError!=OE_Default ){
! 705: onError = overrideError;
! 706: }else if( pParse->db->onError!=OE_Default ){
! 707: onError = pParse->db->onError;
! 708: }else if( onError==OE_Default ){
! 709: onError = OE_Abort;
! 710: }
! 711:
! 712: if( isUpdate ){
! 713: sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
! 714: sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
! 715: jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
! 716: }
! 717: sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
! 718: jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
! 719: switch( onError ){
! 720: default: {
! 721: onError = OE_Abort;
! 722: /* Fall thru into the next case */
! 723: }
! 724: case OE_Rollback:
! 725: case OE_Abort:
! 726: case OE_Fail: {
! 727: sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
! 728: "PRIMARY KEY must be unique", P3_STATIC);
! 729: break;
! 730: }
! 731: case OE_Replace: {
! 732: sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0);
! 733: if( isUpdate ){
! 734: sqliteVdbeAddOp(v, OP_Dup, nCol+hasTwoRecnos, 1);
! 735: sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
! 736: }
! 737: seenReplace = 1;
! 738: break;
! 739: }
! 740: case OE_Ignore: {
! 741: assert( seenReplace==0 );
! 742: sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
! 743: sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
! 744: break;
! 745: }
! 746: }
! 747: contAddr = sqliteVdbeCurrentAddr(v);
! 748: sqliteVdbeChangeP2(v, jumpInst2, contAddr);
! 749: if( isUpdate ){
! 750: sqliteVdbeChangeP2(v, jumpInst1, contAddr);
! 751: sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
! 752: sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
! 753: }
! 754: }
! 755:
! 756: /* Test all UNIQUE constraints by creating entries for each UNIQUE
! 757: ** index and making sure that duplicate entries do not already exist.
! 758: ** Add the new records to the indices as we go.
! 759: */
! 760: extra = -1;
! 761: for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
! 762: if( aIdxUsed && aIdxUsed[iCur]==0 ) continue; /* Skip unused indices */
! 763: extra++;
! 764:
! 765: /* Create a key for accessing the index entry */
! 766: sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
! 767: for(i=0; i<pIdx->nColumn; i++){
! 768: int idx = pIdx->aiColumn[i];
! 769: if( idx==pTab->iPKey ){
! 770: sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
! 771: }else{
! 772: sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
! 773: }
! 774: }
! 775: jumpInst1 = sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
! 776: if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
! 777:
! 778: /* Find out what action to take in case there is an indexing conflict */
! 779: onError = pIdx->onError;
! 780: if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */
! 781: if( overrideError!=OE_Default ){
! 782: onError = overrideError;
! 783: }else if( pParse->db->onError!=OE_Default ){
! 784: onError = pParse->db->onError;
! 785: }else if( onError==OE_Default ){
! 786: onError = OE_Abort;
! 787: }
! 788: if( seenReplace ){
! 789: if( onError==OE_Ignore ) onError = OE_Replace;
! 790: else if( onError==OE_Fail ) onError = OE_Abort;
! 791: }
! 792:
! 793:
! 794: /* Check to see if the new index entry will be unique */
! 795: sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
! 796: jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
! 797:
! 798: /* Generate code that executes if the new index entry is not unique */
! 799: switch( onError ){
! 800: case OE_Rollback:
! 801: case OE_Abort:
! 802: case OE_Fail: {
! 803: int j, n1, n2;
! 804: char zErrMsg[200];
! 805: strcpy(zErrMsg, pIdx->nColumn>1 ? "columns " : "column ");
! 806: n1 = strlen(zErrMsg);
! 807: for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
! 808: char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
! 809: n2 = strlen(zCol);
! 810: if( j>0 ){
! 811: strcpy(&zErrMsg[n1], ", ");
! 812: n1 += 2;
! 813: }
! 814: if( n1+n2>sizeof(zErrMsg)-30 ){
! 815: strcpy(&zErrMsg[n1], "...");
! 816: n1 += 3;
! 817: break;
! 818: }else{
! 819: strcpy(&zErrMsg[n1], zCol);
! 820: n1 += n2;
! 821: }
! 822: }
! 823: strcpy(&zErrMsg[n1],
! 824: pIdx->nColumn>1 ? " are not unique" : " is not unique");
! 825: sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0);
! 826: break;
! 827: }
! 828: case OE_Ignore: {
! 829: assert( seenReplace==0 );
! 830: sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
! 831: sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
! 832: break;
! 833: }
! 834: case OE_Replace: {
! 835: sqliteGenerateRowDelete(pParse->db, v, pTab, base, 0);
! 836: if( isUpdate ){
! 837: sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
! 838: sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
! 839: }
! 840: seenReplace = 1;
! 841: break;
! 842: }
! 843: default: assert(0);
! 844: }
! 845: contAddr = sqliteVdbeCurrentAddr(v);
! 846: #if NULL_DISTINCT_FOR_UNIQUE
! 847: sqliteVdbeChangeP2(v, jumpInst1, contAddr);
! 848: #endif
! 849: sqliteVdbeChangeP2(v, jumpInst2, contAddr);
! 850: }
! 851: }
! 852:
! 853: /*
! 854: ** This routine generates code to finish the INSERT or UPDATE operation
! 855: ** that was started by a prior call to sqliteGenerateConstraintChecks.
! 856: ** The stack must contain keys for all active indices followed by data
! 857: ** and the recno for the new entry. This routine creates the new
! 858: ** entries in all indices and in the main table.
! 859: **
! 860: ** The arguments to this routine should be the same as the first six
! 861: ** arguments to sqliteGenerateConstraintChecks.
! 862: */
! 863: void sqliteCompleteInsertion(
! 864: Parse *pParse, /* The parser context */
! 865: Table *pTab, /* the table into which we are inserting */
! 866: int base, /* Index of a read/write cursor pointing at pTab */
! 867: char *aIdxUsed, /* Which indices are used. NULL means all are used */
! 868: int recnoChng, /* True if the record number will change */
! 869: int isUpdate, /* True for UPDATE, False for INSERT */
! 870: int newIdx /* Index of NEW table for triggers. -1 if none */
! 871: ){
! 872: int i;
! 873: Vdbe *v;
! 874: int nIdx;
! 875: Index *pIdx;
! 876:
! 877: v = sqliteGetVdbe(pParse);
! 878: assert( v!=0 );
! 879: assert( pTab->pSelect==0 ); /* This table is not a VIEW */
! 880: for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
! 881: for(i=nIdx-1; i>=0; i--){
! 882: if( aIdxUsed && aIdxUsed[i]==0 ) continue;
! 883: sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
! 884: }
! 885: sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
! 886: if( newIdx>=0 ){
! 887: sqliteVdbeAddOp(v, OP_Dup, 1, 0);
! 888: sqliteVdbeAddOp(v, OP_Dup, 1, 0);
! 889: sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
! 890: }
! 891: sqliteVdbeAddOp(v, OP_PutIntKey, base,
! 892: (pParse->trigStack?0:OPFLAG_NCHANGE) |
! 893: (isUpdate?0:OPFLAG_LASTROWID) | OPFLAG_CSCHANGE);
! 894: if( isUpdate && recnoChng ){
! 895: sqliteVdbeAddOp(v, OP_Pop, 1, 0);
! 896: }
! 897: }
! 898:
! 899: /*
! 900: ** Generate code that will open write cursors for a table and for all
! 901: ** indices of that table. The "base" parameter is the cursor number used
! 902: ** for the table. Indices are opened on subsequent cursors.
! 903: **
! 904: ** Return the total number of cursors opened. This is always at least
! 905: ** 1 (for the main table) plus more for each cursor.
! 906: */
! 907: int sqliteOpenTableAndIndices(Parse *pParse, Table *pTab, int base){
! 908: int i;
! 909: Index *pIdx;
! 910: Vdbe *v = sqliteGetVdbe(pParse);
! 911: assert( v!=0 );
! 912: sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
! 913: sqliteVdbeOp3(v, OP_OpenWrite, base, pTab->tnum, pTab->zName, P3_STATIC);
! 914: for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
! 915: sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
! 916: sqliteVdbeOp3(v, OP_OpenWrite, i+base, pIdx->tnum, pIdx->zName, P3_STATIC);
! 917: }
! 918: return i;
! 919: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to insert.c CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / sqlite / libsqlite / src