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#include "sqliteInt.h" 16 17/* 18** Generate code that will open a table for reading. 19*/ 20void sqlite3OpenTable( 21 Parse *p, /* Generate code into this VDBE */ 22 int iCur, /* The cursor number of the table */ 23 int iDb, /* The database index in sqlite3.aDb[] */ 24 Table *pTab, /* The table to be opened */ 25 int opcode /* OP_OpenRead or OP_OpenWrite */ 26){ 27 Vdbe *v; 28 if( IsVirtual(pTab) ) return; 29 v = sqlite3GetVdbe(p); 30 assert( opcode==OP_OpenWrite || opcode==OP_OpenRead ); 31 sqlite3TableLock(p, iDb, pTab->tnum, (opcode==OP_OpenWrite)?1:0, pTab->zName); 32 sqlite3VdbeAddOp3(v, opcode, iCur, pTab->tnum, iDb); 33 sqlite3VdbeChangeP4(v, -1, SQLITE_INT_TO_PTR(pTab->nCol), P4_INT32); 34 VdbeComment((v, "%s", pTab->zName)); 35} 36 37/* 38** Return a pointer to the column affinity string associated with index 39** pIdx. A column affinity string has one character for each column in 40** the table, according to the affinity of the column: 41** 42** Character Column affinity 43** ------------------------------ 44** 'a' TEXT 45** 'b' NONE 46** 'c' NUMERIC 47** 'd' INTEGER 48** 'e' REAL 49** 50** An extra 'b' is appended to the end of the string to cover the 51** rowid that appears as the last column in every index. 52** 53** Memory for the buffer containing the column index affinity string 54** is managed along with the rest of the Index structure. It will be 55** released when sqlite3DeleteIndex() is called. 56*/ 57const char *sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){ 58 if( !pIdx->zColAff ){ 59 /* The first time a column affinity string for a particular index is 60 ** required, it is allocated and populated here. It is then stored as 61 ** a member of the Index structure for subsequent use. 62 ** 63 ** The column affinity string will eventually be deleted by 64 ** sqliteDeleteIndex() when the Index structure itself is cleaned 65 ** up. 66 */ 67 int n; 68 Table *pTab = pIdx->pTable; 69 sqlite3 *db = sqlite3VdbeDb(v); 70 pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+2); 71 if( !pIdx->zColAff ){ 72 db->mallocFailed = 1; 73 return 0; 74 } 75 for(n=0; n<pIdx->nColumn; n++){ 76 pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity; 77 } 78 pIdx->zColAff[n++] = SQLITE_AFF_NONE; 79 pIdx->zColAff[n] = 0; 80 } 81 82 return pIdx->zColAff; 83} 84 85/* 86** Set P4 of the most recently inserted opcode to a column affinity 87** string for table pTab. A column affinity string has one character 88** for each column indexed by the index, according to the affinity of the 89** column: 90** 91** Character Column affinity 92** ------------------------------ 93** 'a' TEXT 94** 'b' NONE 95** 'c' NUMERIC 96** 'd' INTEGER 97** 'e' REAL 98*/ 99void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){ 100 /* The first time a column affinity string for a particular table 101 ** is required, it is allocated and populated here. It is then 102 ** stored as a member of the Table structure for subsequent use. 103 ** 104 ** The column affinity string will eventually be deleted by 105 ** sqlite3DeleteTable() when the Table structure itself is cleaned up. 106 */ 107 if( !pTab->zColAff ){ 108 char *zColAff; 109 int i; 110 sqlite3 *db = sqlite3VdbeDb(v); 111 112 zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1); 113 if( !zColAff ){ 114 db->mallocFailed = 1; 115 return; 116 } 117 118 for(i=0; i<pTab->nCol; i++){ 119 zColAff[i] = pTab->aCol[i].affinity; 120 } 121 zColAff[pTab->nCol] = '\0'; 122 123 pTab->zColAff = zColAff; 124 } 125 126 sqlite3VdbeChangeP4(v, -1, pTab->zColAff, P4_TRANSIENT); 127} 128 129/* 130** Return non-zero if the table pTab in database iDb or any of its indices 131** have been opened at any point in the VDBE program beginning at location 132** iStartAddr throught the end of the program. This is used to see if 133** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can 134** run without using temporary table for the results of the SELECT. 135*/ 136static int readsTable(Parse *p, int iStartAddr, int iDb, Table *pTab){ 137 Vdbe *v = sqlite3GetVdbe(p); 138 int i; 139 int iEnd = sqlite3VdbeCurrentAddr(v); 140#ifndef SQLITE_OMIT_VIRTUALTABLE 141 VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0; 142#endif 143 144 for(i=iStartAddr; i<iEnd; i++){ 145 VdbeOp *pOp = sqlite3VdbeGetOp(v, i); 146 assert( pOp!=0 ); 147 if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){ 148 Index *pIndex; 149 int tnum = pOp->p2; 150 if( tnum==pTab->tnum ){ 151 return 1; 152 } 153 for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){ 154 if( tnum==pIndex->tnum ){ 155 return 1; 156 } 157 } 158 } 159#ifndef SQLITE_OMIT_VIRTUALTABLE 160 if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pVTab ){ 161 assert( pOp->p4.pVtab!=0 ); 162 assert( pOp->p4type==P4_VTAB ); 163 return 1; 164 } 165#endif 166 } 167 return 0; 168} 169 170#ifndef SQLITE_OMIT_AUTOINCREMENT 171/* 172** Locate or create an AutoincInfo structure associated with table pTab 173** which is in database iDb. Return the register number for the register 174** that holds the maximum rowid. 175** 176** There is at most one AutoincInfo structure per table even if the 177** same table is autoincremented multiple times due to inserts within 178** triggers. A new AutoincInfo structure is created if this is the 179** first use of table pTab. On 2nd and subsequent uses, the original 180** AutoincInfo structure is used. 181** 182** Three memory locations are allocated: 183** 184** (1) Register to hold the name of the pTab table. 185** (2) Register to hold the maximum ROWID of pTab. 186** (3) Register to hold the rowid in sqlite_sequence of pTab 187** 188** The 2nd register is the one that is returned. That is all the 189** insert routine needs to know about. 190*/ 191static int autoIncBegin( 192 Parse *pParse, /* Parsing context */ 193 int iDb, /* Index of the database holding pTab */ 194 Table *pTab /* The table we are writing to */ 195){ 196 int memId = 0; /* Register holding maximum rowid */ 197 if( pTab->tabFlags & TF_Autoincrement ){ 198 Parse *pToplevel = sqlite3ParseToplevel(pParse); 199 AutoincInfo *pInfo; 200 201 pInfo = pToplevel->pAinc; 202 while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; } 203 if( pInfo==0 ){ 204 pInfo = sqlite3DbMallocRaw(pParse->db, sizeof(*pInfo)); 205 if( pInfo==0 ) return 0; 206 pInfo->pNext = pToplevel->pAinc; 207 pToplevel->pAinc = pInfo; 208 pInfo->pTab = pTab; 209 pInfo->iDb = iDb; 210 pToplevel->nMem++; /* Register to hold name of table */ 211 pInfo->regCtr = ++pToplevel->nMem; /* Max rowid register */ 212 pToplevel->nMem++; /* Rowid in sqlite_sequence */ 213 } 214 memId = pInfo->regCtr; 215 } 216 return memId; 217} 218 219/* 220** This routine generates code that will initialize all of the 221** register used by the autoincrement tracker. 222*/ 223void sqlite3AutoincrementBegin(Parse *pParse){ 224 AutoincInfo *p; /* Information about an AUTOINCREMENT */ 225 sqlite3 *db = pParse->db; /* The database connection */ 226 Db *pDb; /* Database only autoinc table */ 227 int memId; /* Register holding max rowid */ 228 int addr; /* A VDBE address */ 229 Vdbe *v = pParse->pVdbe; /* VDBE under construction */ 230 231 /* This routine is never called during trigger-generation. It is 232 ** only called from the top-level */ 233 assert( pParse->pTriggerTab==0 ); 234 assert( pParse==sqlite3ParseToplevel(pParse) ); 235 236 assert( v ); /* We failed long ago if this is not so */ 237 for(p = pParse->pAinc; p; p = p->pNext){ 238 pDb = &db->aDb[p->iDb]; 239 memId = p->regCtr; 240 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) ); 241 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead); 242 addr = sqlite3VdbeCurrentAddr(v); 243 sqlite3VdbeAddOp4(v, OP_String8, 0, memId-1, 0, p->pTab->zName, 0); 244 sqlite3VdbeAddOp2(v, OP_Rewind, 0, addr+9); 245 sqlite3VdbeAddOp3(v, OP_Column, 0, 0, memId); 246 sqlite3VdbeAddOp3(v, OP_Ne, memId-1, addr+7, memId); 247 sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL); 248 sqlite3VdbeAddOp2(v, OP_Rowid, 0, memId+1); 249 sqlite3VdbeAddOp3(v, OP_Column, 0, 1, memId); 250 sqlite3VdbeAddOp2(v, OP_Goto, 0, addr+9); 251 sqlite3VdbeAddOp2(v, OP_Next, 0, addr+2); 252 sqlite3VdbeAddOp2(v, OP_Integer, 0, memId); 253 sqlite3VdbeAddOp0(v, OP_Close); 254 } 255} 256 257/* 258** Update the maximum rowid for an autoincrement calculation. 259** 260** This routine should be called when the top of the stack holds a 261** new rowid that is about to be inserted. If that new rowid is 262** larger than the maximum rowid in the memId memory cell, then the 263** memory cell is updated. The stack is unchanged. 264*/ 265static void autoIncStep(Parse *pParse, int memId, int regRowid){ 266 if( memId>0 ){ 267 sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid); 268 } 269} 270 271/* 272** This routine generates the code needed to write autoincrement 273** maximum rowid values back into the sqlite_sequence register. 274** Every statement that might do an INSERT into an autoincrement 275** table (either directly or through triggers) needs to call this 276** routine just before the "exit" code. 277*/ 278void sqlite3AutoincrementEnd(Parse *pParse){ 279 AutoincInfo *p; 280 Vdbe *v = pParse->pVdbe; 281 sqlite3 *db = pParse->db; 282 283 assert( v ); 284 for(p = pParse->pAinc; p; p = p->pNext){ 285 Db *pDb = &db->aDb[p->iDb]; 286 int j1, j2, j3, j4, j5; 287 int iRec; 288 int memId = p->regCtr; 289 290 iRec = sqlite3GetTempReg(pParse); 291 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) ); 292 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite); 293 j1 = sqlite3VdbeAddOp1(v, OP_NotNull, memId+1); 294 j2 = sqlite3VdbeAddOp0(v, OP_Rewind); 295 j3 = sqlite3VdbeAddOp3(v, OP_Column, 0, 0, iRec); 296 j4 = sqlite3VdbeAddOp3(v, OP_Eq, memId-1, 0, iRec); 297 sqlite3VdbeAddOp2(v, OP_Next, 0, j3); 298 sqlite3VdbeJumpHere(v, j2); 299 sqlite3VdbeAddOp2(v, OP_NewRowid, 0, memId+1); 300 j5 = sqlite3VdbeAddOp0(v, OP_Goto); 301 sqlite3VdbeJumpHere(v, j4); 302 sqlite3VdbeAddOp2(v, OP_Rowid, 0, memId+1); 303 sqlite3VdbeJumpHere(v, j1); 304 sqlite3VdbeJumpHere(v, j5); 305 sqlite3VdbeAddOp3(v, OP_MakeRecord, memId-1, 2, iRec); 306 sqlite3VdbeAddOp3(v, OP_Insert, 0, iRec, memId+1); 307 sqlite3VdbeChangeP5(v, OPFLAG_APPEND); 308 sqlite3VdbeAddOp0(v, OP_Close); 309 sqlite3ReleaseTempReg(pParse, iRec); 310 } 311} 312#else 313/* 314** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines 315** above are all no-ops 316*/ 317# define autoIncBegin(A,B,C) (0) 318# define autoIncStep(A,B,C) 319#endif /* SQLITE_OMIT_AUTOINCREMENT */ 320 321 322/* Forward declaration */ 323static int xferOptimization( 324 Parse *pParse, /* Parser context */ 325 Table *pDest, /* The table we are inserting into */ 326 Select *pSelect, /* A SELECT statement to use as the data source */ 327 int onError, /* How to handle constraint errors */ 328 int iDbDest /* The database of pDest */ 329); 330 331/* 332** This routine is call to handle SQL of the following forms: 333** 334** insert into TABLE (IDLIST) values(EXPRLIST) 335** insert into TABLE (IDLIST) select 336** 337** The IDLIST following the table name is always optional. If omitted, 338** then a list of all columns for the table is substituted. The IDLIST 339** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted. 340** 341** The pList parameter holds EXPRLIST in the first form of the INSERT 342** statement above, and pSelect is NULL. For the second form, pList is 343** NULL and pSelect is a pointer to the select statement used to generate 344** data for the insert. 345** 346** The code generated follows one of four templates. For a simple 347** select with data coming from a VALUES clause, the code executes 348** once straight down through. Pseudo-code follows (we call this 349** the "1st template"): 350** 351** open write cursor to <table> and its indices 352** puts VALUES clause expressions onto the stack 353** write the resulting record into <table> 354** cleanup 355** 356** The three remaining templates assume the statement is of the form 357** 358** INSERT INTO <table> SELECT ... 359** 360** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" - 361** in other words if the SELECT pulls all columns from a single table 362** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and 363** if <table2> and <table1> are distinct tables but have identical 364** schemas, including all the same indices, then a special optimization 365** is invoked that copies raw records from <table2> over to <table1>. 366** See the xferOptimization() function for the implementation of this 367** template. This is the 2nd template. 368** 369** open a write cursor to <table> 370** open read cursor on <table2> 371** transfer all records in <table2> over to <table> 372** close cursors 373** foreach index on <table> 374** open a write cursor on the <table> index 375** open a read cursor on the corresponding <table2> index 376** transfer all records from the read to the write cursors 377** close cursors 378** end foreach 379** 380** The 3rd template is for when the second template does not apply 381** and the SELECT clause does not read from <table> at any time. 382** The generated code follows this template: 383** 384** EOF <- 0 385** X <- A 386** goto B 387** A: setup for the SELECT 388** loop over the rows in the SELECT 389** load values into registers R..R+n 390** yield X 391** end loop 392** cleanup after the SELECT 393** EOF <- 1 394** yield X 395** goto A 396** B: open write cursor to <table> and its indices 397** C: yield X 398** if EOF goto D 399** insert the select result into <table> from R..R+n 400** goto C 401** D: cleanup 402** 403** The 4th template is used if the insert statement takes its 404** values from a SELECT but the data is being inserted into a table 405** that is also read as part of the SELECT. In the third form, 406** we have to use a intermediate table to store the results of 407** the select. The template is like this: 408** 409** EOF <- 0 410** X <- A 411** goto B 412** A: setup for the SELECT 413** loop over the tables in the SELECT 414** load value into register R..R+n 415** yield X 416** end loop 417** cleanup after the SELECT 418** EOF <- 1 419** yield X 420** halt-error 421** B: open temp table 422** L: yield X 423** if EOF goto M 424** insert row from R..R+n into temp table 425** goto L 426** M: open write cursor to <table> and its indices 427** rewind temp table 428** C: loop over rows of intermediate table 429** transfer values form intermediate table into <table> 430** end loop 431** D: cleanup 432*/ 433void sqlite3Insert( 434 Parse *pParse, /* Parser context */ 435 SrcList *pTabList, /* Name of table into which we are inserting */ 436 ExprList *pList, /* List of values to be inserted */ 437 Select *pSelect, /* A SELECT statement to use as the data source */ 438 IdList *pColumn, /* Column names corresponding to IDLIST. */ 439 int onError /* How to handle constraint errors */ 440){ 441 sqlite3 *db; /* The main database structure */ 442 Table *pTab; /* The table to insert into. aka TABLE */ 443 char *zTab; /* Name of the table into which we are inserting */ 444 const char *zDb; /* Name of the database holding this table */ 445 int i, j, idx; /* Loop counters */ 446 Vdbe *v; /* Generate code into this virtual machine */ 447 Index *pIdx; /* For looping over indices of the table */ 448 int nColumn; /* Number of columns in the data */ 449 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */ 450 int baseCur = 0; /* VDBE Cursor number for pTab */ 451 int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ 452 int endOfLoop; /* Label for the end of the insertion loop */ 453 int useTempTable = 0; /* Store SELECT results in intermediate table */ 454 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */ 455 int addrInsTop = 0; /* Jump to label "D" */ 456 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */ 457 int addrSelect = 0; /* Address of coroutine that implements the SELECT */ 458 SelectDest dest; /* Destination for SELECT on rhs of INSERT */ 459 int iDb; /* Index of database holding TABLE */ 460 Db *pDb; /* The database containing table being inserted into */ 461 int appendFlag = 0; /* True if the insert is likely to be an append */ 462 463 /* Register allocations */ 464 int regFromSelect = 0;/* Base register for data coming from SELECT */ 465 int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */ 466 int regRowCount = 0; /* Memory cell used for the row counter */ 467 int regIns; /* Block of regs holding rowid+data being inserted */ 468 int regRowid; /* registers holding insert rowid */ 469 int regData; /* register holding first column to insert */ 470 int regEof = 0; /* Register recording end of SELECT data */ 471 int *aRegIdx = 0; /* One register allocated to each index */ 472 473#ifndef SQLITE_OMIT_TRIGGER 474 int isView; /* True if attempting to insert into a view */ 475 Trigger *pTrigger; /* List of triggers on pTab, if required */ 476 int tmask; /* Mask of trigger times */ 477#endif 478 479 db = pParse->db; 480 memset(&dest, 0, sizeof(dest)); 481 if( pParse->nErr || db->mallocFailed ){ 482 goto insert_cleanup; 483 } 484 485 /* Locate the table into which we will be inserting new information. 486 */ 487 assert( pTabList->nSrc==1 ); 488 zTab = pTabList->a[0].zName; 489 if( NEVER(zTab==0) ) goto insert_cleanup; 490 pTab = sqlite3SrcListLookup(pParse, pTabList); 491 if( pTab==0 ){ 492 goto insert_cleanup; 493 } 494 iDb = sqlite3SchemaToIndex(db, pTab->pSchema); 495 assert( iDb<db->nDb ); 496 pDb = &db->aDb[iDb]; 497 zDb = pDb->zName; 498 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){ 499 goto insert_cleanup; 500 } 501 502 /* Figure out if we have any triggers and if the table being 503 ** inserted into is a view 504 */ 505#ifndef SQLITE_OMIT_TRIGGER 506 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask); 507 isView = pTab->pSelect!=0; 508#else 509# define pTrigger 0 510# define tmask 0 511# define isView 0 512#endif 513#ifdef SQLITE_OMIT_VIEW 514# undef isView 515# define isView 0 516#endif 517 assert( (pTrigger && tmask) || (pTrigger==0 && tmask==0) ); 518 519 /* If pTab is really a view, make sure it has been initialized. 520 ** ViewGetColumnNames() is a no-op if pTab is not a view (or virtual 521 ** module table). 522 */ 523 if( sqlite3ViewGetColumnNames(pParse, pTab) ){ 524 goto insert_cleanup; 525 } 526 527 /* Ensure that: 528 * (a) the table is not read-only, 529 * (b) that if it is a view then ON INSERT triggers exist 530 */ 531 if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ 532 goto insert_cleanup; 533 } 534 535 /* Allocate a VDBE 536 */ 537 v = sqlite3GetVdbe(pParse); 538 if( v==0 ) goto insert_cleanup; 539 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); 540 sqlite3BeginWriteOperation(pParse, pSelect || pTrigger, iDb); 541 542#ifndef SQLITE_OMIT_XFER_OPT 543 /* If the statement is of the form 544 ** 545 ** INSERT INTO <table1> SELECT * FROM <table2>; 546 ** 547 ** Then special optimizations can be applied that make the transfer 548 ** very fast and which reduce fragmentation of indices. 549 ** 550 ** This is the 2nd template. 551 */ 552 if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){ 553 assert( !pTrigger ); 554 assert( pList==0 ); 555 goto insert_end; 556 } 557#endif /* SQLITE_OMIT_XFER_OPT */ 558 559 /* If this is an AUTOINCREMENT table, look up the sequence number in the 560 ** sqlite_sequence table and store it in memory cell regAutoinc. 561 */ 562 regAutoinc = autoIncBegin(pParse, iDb, pTab); 563 564 /* Figure out how many columns of data are supplied. If the data 565 ** is coming from a SELECT statement, then generate a co-routine that 566 ** produces a single row of the SELECT on each invocation. The 567 ** co-routine is the common header to the 3rd and 4th templates. 568 */ 569 if( pSelect ){ 570 /* Data is coming from a SELECT. Generate code to implement that SELECT 571 ** as a co-routine. The code is common to both the 3rd and 4th 572 ** templates: 573 ** 574 ** EOF <- 0 575 ** X <- A 576 ** goto B 577 ** A: setup for the SELECT 578 ** loop over the tables in the SELECT 579 ** load value into register R..R+n 580 ** yield X 581 ** end loop 582 ** cleanup after the SELECT 583 ** EOF <- 1 584 ** yield X 585 ** halt-error 586 ** 587 ** On each invocation of the co-routine, it puts a single row of the 588 ** SELECT result into registers dest.iMem...dest.iMem+dest.nMem-1. 589 ** (These output registers are allocated by sqlite3Select().) When 590 ** the SELECT completes, it sets the EOF flag stored in regEof. 591 */ 592 int rc, j1; 593 594 regEof = ++pParse->nMem; 595 sqlite3VdbeAddOp2(v, OP_Integer, 0, regEof); /* EOF <- 0 */ 596 VdbeComment((v, "SELECT eof flag")); 597 sqlite3SelectDestInit(&dest, SRT_Coroutine, ++pParse->nMem); 598 addrSelect = sqlite3VdbeCurrentAddr(v)+2; 599 sqlite3VdbeAddOp2(v, OP_Integer, addrSelect-1, dest.iParm); 600 j1 = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0); 601 VdbeComment((v, "Jump over SELECT coroutine")); 602 603 /* Resolve the expressions in the SELECT statement and execute it. */ 604 rc = sqlite3Select(pParse, pSelect, &dest); 605 assert( pParse->nErr==0 || rc ); 606 if( rc || NEVER(pParse->nErr) || db->mallocFailed ){ 607 goto insert_cleanup; 608 } 609 sqlite3VdbeAddOp2(v, OP_Integer, 1, regEof); /* EOF <- 1 */ 610 sqlite3VdbeAddOp1(v, OP_Yield, dest.iParm); /* yield X */ 611 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_INTERNAL, OE_Abort); 612 VdbeComment((v, "End of SELECT coroutine")); 613 sqlite3VdbeJumpHere(v, j1); /* label B: */ 614 615 regFromSelect = dest.iMem; 616 assert( pSelect->pEList ); 617 nColumn = pSelect->pEList->nExpr; 618 assert( dest.nMem==nColumn ); 619 620 /* Set useTempTable to TRUE if the result of the SELECT statement 621 ** should be written into a temporary table (template 4). Set to 622 ** FALSE if each* row of the SELECT can be written directly into 623 ** the destination table (template 3). 624 ** 625 ** A temp table must be used if the table being updated is also one 626 ** of the tables being read by the SELECT statement. Also use a 627 ** temp table in the case of row triggers. 628 */ 629 if( pTrigger || readsTable(pParse, addrSelect, iDb, pTab) ){ 630 useTempTable = 1; 631 } 632 633 if( useTempTable ){ 634 /* Invoke the coroutine to extract information from the SELECT 635 ** and add it to a transient table srcTab. The code generated 636 ** here is from the 4th template: 637 ** 638 ** B: open temp table 639 ** L: yield X 640 ** if EOF goto M 641 ** insert row from R..R+n into temp table 642 ** goto L 643 ** M: ... 644 */ 645 int regRec; /* Register to hold packed record */ 646 int regTempRowid; /* Register to hold temp table ROWID */ 647 int addrTop; /* Label "L" */ 648 int addrIf; /* Address of jump to M */ 649 650 srcTab = pParse->nTab++; 651 regRec = sqlite3GetTempReg(pParse); 652 regTempRowid = sqlite3GetTempReg(pParse); 653 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn); 654 addrTop = sqlite3VdbeAddOp1(v, OP_Yield, dest.iParm); 655 addrIf = sqlite3VdbeAddOp1(v, OP_If, regEof); 656 sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec); 657 sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regTempRowid); 658 sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid); 659 sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop); 660 sqlite3VdbeJumpHere(v, addrIf); 661 sqlite3ReleaseTempReg(pParse, regRec); 662 sqlite3ReleaseTempReg(pParse, regTempRowid); 663 } 664 }else{ 665 /* This is the case if the data for the INSERT is coming from a VALUES 666 ** clause 667 */ 668 NameContext sNC; 669 memset(&sNC, 0, sizeof(sNC)); 670 sNC.pParse = pParse; 671 srcTab = -1; 672 assert( useTempTable==0 ); 673 nColumn = pList ? pList->nExpr : 0; 674 for(i=0; i<nColumn; i++){ 675 if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){ 676 goto insert_cleanup; 677 } 678 } 679 } 680 681 /* Make sure the number of columns in the source data matches the number 682 ** of columns to be inserted into the table. 683 */ 684 if( IsVirtual(pTab) ){ 685 for(i=0; i<pTab->nCol; i++){ 686 nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0); 687 } 688 } 689 if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){ 690 sqlite3ErrorMsg(pParse, 691 "table %S has %d columns but %d values were supplied", 692 pTabList, 0, pTab->nCol-nHidden, nColumn); 693 goto insert_cleanup; 694 } 695 if( pColumn!=0 && nColumn!=pColumn->nId ){ 696 sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId); 697 goto insert_cleanup; 698 } 699 700 /* If the INSERT statement included an IDLIST term, then make sure 701 ** all elements of the IDLIST really are columns of the table and 702 ** remember the column indices. 703 ** 704 ** If the table has an INTEGER PRIMARY KEY column and that column 705 ** is named in the IDLIST, then record in the keyColumn variable 706 ** the index into IDLIST of the primary key column. keyColumn is 707 ** the index of the primary key as it appears in IDLIST, not as 708 ** is appears in the original table. (The index of the primary 709 ** key in the original table is pTab->iPKey.) 710 */ 711 if( pColumn ){ 712 for(i=0; i<pColumn->nId; i++){ 713 pColumn->a[i].idx = -1; 714 } 715 for(i=0; i<pColumn->nId; i++){ 716 for(j=0; j<pTab->nCol; j++){ 717 if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){ 718 pColumn->a[i].idx = j; 719 if( j==pTab->iPKey ){ 720 keyColumn = i; 721 } 722 break; 723 } 724 } 725 if( j>=pTab->nCol ){ 726 if( sqlite3IsRowid(pColumn->a[i].zName) ){ 727 keyColumn = i; 728 }else{ 729 sqlite3ErrorMsg(pParse, "table %S has no column named %s", 730 pTabList, 0, pColumn->a[i].zName); 731 pParse->checkSchema = 1; 732 goto insert_cleanup; 733 } 734 } 735 } 736 } 737 738 /* If there is no IDLIST term but the table has an integer primary 739 ** key, the set the keyColumn variable to the primary key column index 740 ** in the original table definition. 741 */ 742 if( pColumn==0 && nColumn>0 ){ 743 keyColumn = pTab->iPKey; 744 } 745 746 /* Initialize the count of rows to be inserted 747 */ 748 if( db->flags & SQLITE_CountRows ){ 749 regRowCount = ++pParse->nMem; 750 sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); 751 } 752 753 /* If this is not a view, open the table and and all indices */ 754 if( !isView ){ 755 int nIdx; 756 757 baseCur = pParse->nTab; 758 nIdx = sqlite3OpenTableAndIndices(pParse, pTab, baseCur, OP_OpenWrite); 759 aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1)); 760 if( aRegIdx==0 ){ 761 goto insert_cleanup; 762 } 763 for(i=0; i<nIdx; i++){ 764 aRegIdx[i] = ++pParse->nMem; 765 } 766 } 767 768 /* This is the top of the main insertion loop */ 769 if( useTempTable ){ 770 /* This block codes the top of loop only. The complete loop is the 771 ** following pseudocode (template 4): 772 ** 773 ** rewind temp table 774 ** C: loop over rows of intermediate table 775 ** transfer values form intermediate table into <table> 776 ** end loop 777 ** D: ... 778 */ 779 addrInsTop = sqlite3VdbeAddOp1(v, OP_Rewind, srcTab); 780 addrCont = sqlite3VdbeCurrentAddr(v); 781 }else if( pSelect ){ 782 /* This block codes the top of loop only. The complete loop is the 783 ** following pseudocode (template 3): 784 ** 785 ** C: yield X 786 ** if EOF goto D 787 ** insert the select result into <table> from R..R+n 788 ** goto C 789 ** D: ... 790 */ 791 addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iParm); 792 addrInsTop = sqlite3VdbeAddOp1(v, OP_If, regEof); 793 } 794 795 /* Allocate registers for holding the rowid of the new row, 796 ** the content of the new row, and the assemblied row record. 797 */ 798 regRowid = regIns = pParse->nMem+1; 799 pParse->nMem += pTab->nCol + 1; 800 if( IsVirtual(pTab) ){ 801 regRowid++; 802 pParse->nMem++; 803 } 804 regData = regRowid+1; 805 806 /* Run the BEFORE and INSTEAD OF triggers, if there are any 807 */ 808 endOfLoop = sqlite3VdbeMakeLabel(v); 809 if( tmask & TRIGGER_BEFORE ){ 810 int regCols = sqlite3GetTempRange(pParse, pTab->nCol+1); 811 812 /* build the NEW.* reference row. Note that if there is an INTEGER 813 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be 814 ** translated into a unique ID for the row. But on a BEFORE trigger, 815 ** we do not know what the unique ID will be (because the insert has 816 ** not happened yet) so we substitute a rowid of -1 817 */ 818 if( keyColumn<0 ){ 819 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols); 820 }else{ 821 int j1; 822 if( useTempTable ){ 823 sqlite3VdbeAddOp3(v, OP_Column, srcTab, keyColumn, regCols); 824 }else{ 825 assert( pSelect==0 ); /* Otherwise useTempTable is true */ 826 sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr, regCols); 827 } 828 j1 = sqlite3VdbeAddOp1(v, OP_NotNull, regCols); 829 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols); 830 sqlite3VdbeJumpHere(v, j1); 831 sqlite3VdbeAddOp1(v, OP_MustBeInt, regCols); 832 } 833 834 /* Cannot have triggers on a virtual table. If it were possible, 835 ** this block would have to account for hidden column. 836 */ 837 assert( !IsVirtual(pTab) ); 838 839 /* Create the new column data 840 */ 841 for(i=0; i<pTab->nCol; i++){ 842 if( pColumn==0 ){ 843 j = i; 844 }else{ 845 for(j=0; j<pColumn->nId; j++){ 846 if( pColumn->a[j].idx==i ) break; 847 } 848 } 849 if( (!useTempTable && !pList) || (pColumn && j>=pColumn->nId) ){ 850 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regCols+i+1); 851 }else if( useTempTable ){ 852 sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, regCols+i+1); 853 }else{ 854 assert( pSelect==0 ); /* Otherwise useTempTable is true */ 855 sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr, regCols+i+1); 856 } 857 } 858 859 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger, 860 ** do not attempt any conversions before assembling the record. 861 ** If this is a real table, attempt conversions as required by the 862 ** table column affinities. 863 */ 864 if( !isView ){ 865 sqlite3VdbeAddOp2(v, OP_Affinity, regCols+1, pTab->nCol); 866 sqlite3TableAffinityStr(v, pTab); 867 } 868 869 /* Fire BEFORE or INSTEAD OF triggers */ 870 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_BEFORE, 871 pTab, regCols-pTab->nCol-1, onError, endOfLoop); 872 873 sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol+1); 874 } 875 876 /* Push the record number for the new entry onto the stack. The 877 ** record number is a randomly generate integer created by NewRowid 878 ** except when the table has an INTEGER PRIMARY KEY column, in which 879 ** case the record number is the same as that column. 880 */ 881 if( !isView ){ 882 if( IsVirtual(pTab) ){ 883 /* The row that the VUpdate opcode will delete: none */ 884 sqlite3VdbeAddOp2(v, OP_Null, 0, regIns); 885 } 886 if( keyColumn>=0 ){ 887 if( useTempTable ){ 888 sqlite3VdbeAddOp3(v, OP_Column, srcTab, keyColumn, regRowid); 889 }else if( pSelect ){ 890 sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+keyColumn, regRowid); 891 }else{ 892 VdbeOp *pOp; 893 sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr, regRowid); 894 pOp = sqlite3VdbeGetOp(v, -1); 895 if( ALWAYS(pOp) && pOp->opcode==OP_Null && !IsVirtual(pTab) ){ 896 appendFlag = 1; 897 pOp->opcode = OP_NewRowid; 898 pOp->p1 = baseCur; 899 pOp->p2 = regRowid; 900 pOp->p3 = regAutoinc; 901 } 902 } 903 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid 904 ** to generate a unique primary key value. 905 */ 906 if( !appendFlag ){ 907 int j1; 908 if( !IsVirtual(pTab) ){ 909 j1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid); 910 sqlite3VdbeAddOp3(v, OP_NewRowid, baseCur, regRowid, regAutoinc); 911 sqlite3VdbeJumpHere(v, j1); 912 }else{ 913 j1 = sqlite3VdbeCurrentAddr(v); 914 sqlite3VdbeAddOp2(v, OP_IsNull, regRowid, j1+2); 915 } 916 sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid); 917 } 918 }else if( IsVirtual(pTab) ){ 919 sqlite3VdbeAddOp2(v, OP_Null, 0, regRowid); 920 }else{ 921 sqlite3VdbeAddOp3(v, OP_NewRowid, baseCur, regRowid, regAutoinc); 922 appendFlag = 1; 923 } 924 autoIncStep(pParse, regAutoinc, regRowid); 925 926 /* Push onto the stack, data for all columns of the new entry, beginning 927 ** with the first column. 928 */ 929 nHidden = 0; 930 for(i=0; i<pTab->nCol; i++){ 931 int iRegStore = regRowid+1+i; 932 if( i==pTab->iPKey ){ 933 /* The value of the INTEGER PRIMARY KEY column is always a NULL. 934 ** Whenever this column is read, the record number will be substituted 935 ** in its place. So will fill this column with a NULL to avoid 936 ** taking up data space with information that will never be used. */ 937 sqlite3VdbeAddOp2(v, OP_Null, 0, iRegStore); 938 continue; 939 } 940 if( pColumn==0 ){ 941 if( IsHiddenColumn(&pTab->aCol[i]) ){ 942 assert( IsVirtual(pTab) ); 943 j = -1; 944 nHidden++; 945 }else{ 946 j = i - nHidden; 947 } 948 }else{ 949 for(j=0; j<pColumn->nId; j++){ 950 if( pColumn->a[j].idx==i ) break; 951 } 952 } 953 if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){ 954 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore); 955 }else if( useTempTable ){ 956 sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, iRegStore); 957 }else if( pSelect ){ 958 sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+j, iRegStore); 959 }else{ 960 sqlite3ExprCode(pParse, pList->a[j].pExpr, iRegStore); 961 } 962 } 963 964 /* Generate code to check constraints and generate index keys and 965 ** do the insertion. 966 */ 967#ifndef SQLITE_OMIT_VIRTUALTABLE 968 if( IsVirtual(pTab) ){ 969 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); 970 sqlite3VtabMakeWritable(pParse, pTab); 971 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB); 972 sqlite3MayAbort(pParse); 973 }else 974#endif 975 { 976 int isReplace; /* Set to true if constraints may cause a replace */ 977 sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, 978 keyColumn>=0, 0, onError, endOfLoop, &isReplace 979 ); 980 sqlite3FkCheck(pParse, pTab, 0, regIns); 981 sqlite3CompleteInsertion( 982 pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 983 ); 984 } 985 } 986 987 /* Update the count of rows that are inserted 988 */ 989 if( (db->flags & SQLITE_CountRows)!=0 ){ 990 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); 991 } 992 993 if( pTrigger ){ 994 /* Code AFTER triggers */ 995 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER, 996 pTab, regData-2-pTab->nCol, onError, endOfLoop); 997 } 998 999 /* The bottom of the main insertion loop, if the data source 1000 ** is a SELECT statement. 1001 */ 1002 sqlite3VdbeResolveLabel(v, endOfLoop); 1003 if( useTempTable ){ 1004 sqlite3VdbeAddOp2(v, OP_Next, srcTab, addrCont); 1005 sqlite3VdbeJumpHere(v, addrInsTop); 1006 sqlite3VdbeAddOp1(v, OP_Close, srcTab); 1007 }else if( pSelect ){ 1008 sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont); 1009 sqlite3VdbeJumpHere(v, addrInsTop); 1010 } 1011 1012 if( !IsVirtual(pTab) && !isView ){ 1013 /* Close all tables opened */ 1014 sqlite3VdbeAddOp1(v, OP_Close, baseCur); 1015 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 1016 sqlite3VdbeAddOp1(v, OP_Close, idx+baseCur); 1017 } 1018 } 1019 1020insert_end: 1021 /* Update the sqlite_sequence table by storing the content of the 1022 ** maximum rowid counter values recorded while inserting into 1023 ** autoincrement tables. 1024 */ 1025 if( pParse->nested==0 && pParse->pTriggerTab==0 ){ 1026 sqlite3AutoincrementEnd(pParse); 1027 } 1028 1029 /* 1030 ** Return the number of rows inserted. If this routine is 1031 ** generating code because of a call to sqlite3NestedParse(), do not 1032 ** invoke the callback function. 1033 */ 1034 if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){ 1035 sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); 1036 sqlite3VdbeSetNumCols(v, 1); 1037 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC); 1038 } 1039 1040insert_cleanup: 1041 sqlite3SrcListDelete(db, pTabList); 1042 sqlite3ExprListDelete(db, pList); 1043 sqlite3SelectDelete(db, pSelect); 1044 sqlite3IdListDelete(db, pColumn); 1045 sqlite3DbFree(db, aRegIdx); 1046} 1047 1048/* Make sure "isView" and other macros defined above are undefined. Otherwise 1049** thely may interfere with compilation of other functions in this file 1050** (or in another file, if this file becomes part of the amalgamation). */ 1051#ifdef isView 1052 #undef isView 1053#endif 1054#ifdef pTrigger 1055 #undef pTrigger 1056#endif 1057#ifdef tmask 1058 #undef tmask 1059#endif 1060 1061 1062/* 1063** Generate code to do constraint checks prior to an INSERT or an UPDATE. 1064** 1065** The input is a range of consecutive registers as follows: 1066** 1067** 1. The rowid of the row after the update. 1068** 1069** 2. The data in the first column of the entry after the update. 1070** 1071** i. Data from middle columns... 1072** 1073** N. The data in the last column of the entry after the update. 1074** 1075** The regRowid parameter is the index of the register containing (1). 1076** 1077** If isUpdate is true and rowidChng is non-zero, then rowidChng contains 1078** the address of a register containing the rowid before the update takes 1079** place. isUpdate is true for UPDATEs and false for INSERTs. If isUpdate 1080** is false, indicating an INSERT statement, then a non-zero rowidChng 1081** indicates that the rowid was explicitly specified as part of the 1082** INSERT statement. If rowidChng is false, it means that the rowid is 1083** computed automatically in an insert or that the rowid value is not 1084** modified by an update. 1085** 1086** The code generated by this routine store new index entries into 1087** registers identified by aRegIdx[]. No index entry is created for 1088** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is 1089** the same as the order of indices on the linked list of indices 1090** attached to the table. 1091** 1092** This routine also generates code to check constraints. NOT NULL, 1093** CHECK, and UNIQUE constraints are all checked. If a constraint fails, 1094** then the appropriate action is performed. There are five possible 1095** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE. 1096** 1097** Constraint type Action What Happens 1098** --------------- ---------- ---------------------------------------- 1099** any ROLLBACK The current transaction is rolled back and 1100** sqlite3_exec() returns immediately with a 1101** return code of SQLITE_CONSTRAINT. 1102** 1103** any ABORT Back out changes from the current command 1104** only (do not do a complete rollback) then 1105** cause sqlite3_exec() to return immediately 1106** with SQLITE_CONSTRAINT. 1107** 1108** any FAIL Sqlite_exec() returns immediately with a 1109** return code of SQLITE_CONSTRAINT. The 1110** transaction is not rolled back and any 1111** prior changes are retained. 1112** 1113** any IGNORE The record number and data is popped from 1114** the stack and there is an immediate jump 1115** to label ignoreDest. 1116** 1117** NOT NULL REPLACE The NULL value is replace by the default 1118** value for that column. If the default value 1119** is NULL, the action is the same as ABORT. 1120** 1121** UNIQUE REPLACE The other row that conflicts with the row 1122** being inserted is removed. 1123** 1124** CHECK REPLACE Illegal. The results in an exception. 1125** 1126** Which action to take is determined by the overrideError parameter. 1127** Or if overrideError==OE_Default, then the pParse->onError parameter 1128** is used. Or if pParse->onError==OE_Default then the onError value 1129** for the constraint is used. 1130** 1131** The calling routine must open a read/write cursor for pTab with 1132** cursor number "baseCur". All indices of pTab must also have open 1133** read/write cursors with cursor number baseCur+i for the i-th cursor. 1134** Except, if there is no possibility of a REPLACE action then 1135** cursors do not need to be open for indices where aRegIdx[i]==0. 1136*/ 1137void sqlite3GenerateConstraintChecks( 1138 Parse *pParse, /* The parser context */ 1139 Table *pTab, /* the table into which we are inserting */ 1140 int baseCur, /* Index of a read/write cursor pointing at pTab */ 1141 int regRowid, /* Index of the range of input registers */ 1142 int *aRegIdx, /* Register used by each index. 0 for unused indices */ 1143 int rowidChng, /* True if the rowid might collide with existing entry */ 1144 int isUpdate, /* True for UPDATE, False for INSERT */ 1145 int overrideError, /* Override onError to this if not OE_Default */ 1146 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */ 1147 int *pbMayReplace /* OUT: Set to true if constraint may cause a replace */ 1148){ 1149 int i; /* loop counter */ 1150 Vdbe *v; /* VDBE under constrution */ 1151 int nCol; /* Number of columns */ 1152 int onError; /* Conflict resolution strategy */ 1153 int j1; /* Addresss of jump instruction */ 1154 int j2 = 0, j3; /* Addresses of jump instructions */ 1155 int regData; /* Register containing first data column */ 1156 int iCur; /* Table cursor number */ 1157 Index *pIdx; /* Pointer to one of the indices */ 1158 int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */ 1159 int regOldRowid = (rowidChng && isUpdate) ? rowidChng : regRowid; 1160 1161 v = sqlite3GetVdbe(pParse); 1162 assert( v!=0 ); 1163 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 1164 nCol = pTab->nCol; 1165 regData = regRowid + 1; 1166 1167 /* Test all NOT NULL constraints. 1168 */ 1169 for(i=0; i<nCol; i++){ 1170 if( i==pTab->iPKey ){ 1171 continue; 1172 } 1173 onError = pTab->aCol[i].notNull; 1174 if( onError==OE_None ) continue; 1175 if( overrideError!=OE_Default ){ 1176 onError = overrideError; 1177 }else if( onError==OE_Default ){ 1178 onError = OE_Abort; 1179 } 1180 if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){ 1181 onError = OE_Abort; 1182 } 1183 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail 1184 || onError==OE_Ignore || onError==OE_Replace ); 1185 switch( onError ){ 1186 case OE_Abort: 1187 sqlite3MayAbort(pParse); 1188 case OE_Rollback: 1189 case OE_Fail: { 1190 char *zMsg; 1191 sqlite3VdbeAddOp3(v, OP_HaltIfNull, 1192 SQLITE_CONSTRAINT, onError, regData+i); 1193 zMsg = sqlite3MPrintf(pParse->db, "%s.%s may not be NULL", 1194 pTab->zName, pTab->aCol[i].zName); 1195 sqlite3VdbeChangeP4(v, -1, zMsg, P4_DYNAMIC); 1196 break; 1197 } 1198 case OE_Ignore: { 1199 sqlite3VdbeAddOp2(v, OP_IsNull, regData+i, ignoreDest); 1200 break; 1201 } 1202 default: { 1203 assert( onError==OE_Replace ); 1204 j1 = sqlite3VdbeAddOp1(v, OP_NotNull, regData+i); 1205 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regData+i); 1206 sqlite3VdbeJumpHere(v, j1); 1207 break; 1208 } 1209 } 1210 } 1211 1212 /* Test all CHECK constraints 1213 */ 1214#ifndef SQLITE_OMIT_CHECK 1215 if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){ 1216 int allOk = sqlite3VdbeMakeLabel(v); 1217 pParse->ckBase = regData; 1218 sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, SQLITE_JUMPIFNULL); 1219 onError = overrideError!=OE_Default ? overrideError : OE_Abort; 1220 if( onError==OE_Ignore ){ 1221 sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); 1222 }else{ 1223 if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */ 1224 sqlite3HaltConstraint(pParse, onError, 0, 0); 1225 } 1226 sqlite3VdbeResolveLabel(v, allOk); 1227 } 1228#endif /* !defined(SQLITE_OMIT_CHECK) */ 1229 1230 /* If we have an INTEGER PRIMARY KEY, make sure the primary key 1231 ** of the new record does not previously exist. Except, if this 1232 ** is an UPDATE and the primary key is not changing, that is OK. 1233 */ 1234 if( rowidChng ){ 1235 onError = pTab->keyConf; 1236 if( overrideError!=OE_Default ){ 1237 onError = overrideError; 1238 }else if( onError==OE_Default ){ 1239 onError = OE_Abort; 1240 } 1241 1242 if( isUpdate ){ 1243 j2 = sqlite3VdbeAddOp3(v, OP_Eq, regRowid, 0, rowidChng); 1244 } 1245 j3 = sqlite3VdbeAddOp3(v, OP_NotExists, baseCur, 0, regRowid); 1246 switch( onError ){ 1247 default: { 1248 onError = OE_Abort; 1249 /* Fall thru into the next case */ 1250 } 1251 case OE_Rollback: 1252 case OE_Abort: 1253 case OE_Fail: { 1254 sqlite3HaltConstraint( 1255 pParse, onError, "PRIMARY KEY must be unique", P4_STATIC); 1256 break; 1257 } 1258 case OE_Replace: { 1259 /* If there are DELETE triggers on this table and the 1260 ** recursive-triggers flag is set, call GenerateRowDelete() to 1261 ** remove the conflicting row from the the table. This will fire 1262 ** the triggers and remove both the table and index b-tree entries. 1263 ** 1264 ** Otherwise, if there are no triggers or the recursive-triggers 1265 ** flag is not set, but the table has one or more indexes, call 1266 ** GenerateRowIndexDelete(). This removes the index b-tree entries 1267 ** only. The table b-tree entry will be replaced by the new entry 1268 ** when it is inserted. 1269 ** 1270 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called, 1271 ** also invoke MultiWrite() to indicate that this VDBE may require 1272 ** statement rollback (if the statement is aborted after the delete 1273 ** takes place). Earlier versions called sqlite3MultiWrite() regardless, 1274 ** but being more selective here allows statements like: 1275 ** 1276 ** REPLACE INTO t(rowid) VALUES($newrowid) 1277 ** 1278 ** to run without a statement journal if there are no indexes on the 1279 ** table. 1280 */ 1281 Trigger *pTrigger = 0; 1282 if( pParse->db->flags&SQLITE_RecTriggers ){ 1283 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); 1284 } 1285 if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){ 1286 sqlite3MultiWrite(pParse); 1287 sqlite3GenerateRowDelete( 1288 pParse, pTab, baseCur, regRowid, 0, pTrigger, OE_Replace 1289 ); 1290 }else if( pTab->pIndex ){ 1291 sqlite3MultiWrite(pParse); 1292 sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0); 1293 } 1294 seenReplace = 1; 1295 break; 1296 } 1297 case OE_Ignore: { 1298 assert( seenReplace==0 ); 1299 sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); 1300 break; 1301 } 1302 } 1303 sqlite3VdbeJumpHere(v, j3); 1304 if( isUpdate ){ 1305 sqlite3VdbeJumpHere(v, j2); 1306 } 1307 } 1308 1309 /* Test all UNIQUE constraints by creating entries for each UNIQUE 1310 ** index and making sure that duplicate entries do not already exist. 1311 ** Add the new records to the indices as we go. 1312 */ 1313 for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){ 1314 int regIdx; 1315 int regR; 1316 1317 if( aRegIdx[iCur]==0 ) continue; /* Skip unused indices */ 1318 1319 /* Create a key for accessing the index entry */ 1320 regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn+1); 1321 for(i=0; i<pIdx->nColumn; i++){ 1322 int idx = pIdx->aiColumn[i]; 1323 if( idx==pTab->iPKey ){ 1324 sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i); 1325 }else{ 1326 sqlite3VdbeAddOp2(v, OP_SCopy, regData+idx, regIdx+i); 1327 } 1328 } 1329 sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i); 1330 sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]); 1331 sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), P4_TRANSIENT); 1332 sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1); 1333 1334 /* Find out what action to take in case there is an indexing conflict */ 1335 onError = pIdx->onError; 1336 if( onError==OE_None ){ 1337 sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); 1338 continue; /* pIdx is not a UNIQUE index */ 1339 } 1340 if( overrideError!=OE_Default ){ 1341 onError = overrideError; 1342 }else if( onError==OE_Default ){ 1343 onError = OE_Abort; 1344 } 1345 if( seenReplace ){ 1346 if( onError==OE_Ignore ) onError = OE_Replace; 1347 else if( onError==OE_Fail ) onError = OE_Abort; 1348 } 1349 1350 /* Check to see if the new index entry will be unique */ 1351 regR = sqlite3GetTempReg(pParse); 1352 sqlite3VdbeAddOp2(v, OP_SCopy, regOldRowid, regR); 1353 j3 = sqlite3VdbeAddOp4(v, OP_IsUnique, baseCur+iCur+1, 0, 1354 regR, SQLITE_INT_TO_PTR(regIdx), 1355 P4_INT32); 1356 sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); 1357 1358 /* Generate code that executes if the new index entry is not unique */ 1359 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail 1360 || onError==OE_Ignore || onError==OE_Replace ); 1361 switch( onError ){ 1362 case OE_Rollback: 1363 case OE_Abort: 1364 case OE_Fail: { 1365 int j; 1366 StrAccum errMsg; 1367 const char *zSep; 1368 char *zErr; 1369 1370 sqlite3StrAccumInit(&errMsg, 0, 0, 200); 1371 errMsg.db = pParse->db; 1372 zSep = pIdx->nColumn>1 ? "columns " : "column "; 1373 for(j=0; j<pIdx->nColumn; j++){ 1374 char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName; 1375 sqlite3StrAccumAppend(&errMsg, zSep, -1); 1376 zSep = ", "; 1377 sqlite3StrAccumAppend(&errMsg, zCol, -1); 1378 } 1379 sqlite3StrAccumAppend(&errMsg, 1380 pIdx->nColumn>1 ? " are not unique" : " is not unique", -1); 1381 zErr = sqlite3StrAccumFinish(&errMsg); 1382 sqlite3HaltConstraint(pParse, onError, zErr, 0); 1383 sqlite3DbFree(errMsg.db, zErr); 1384 break; 1385 } 1386 case OE_Ignore: { 1387 assert( seenReplace==0 ); 1388 sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); 1389 break; 1390 } 1391 default: { 1392 Trigger *pTrigger = 0; 1393 assert( onError==OE_Replace ); 1394 sqlite3MultiWrite(pParse); 1395 if( pParse->db->flags&SQLITE_RecTriggers ){ 1396 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); 1397 } 1398 sqlite3GenerateRowDelete( 1399 pParse, pTab, baseCur, regR, 0, pTrigger, OE_Replace 1400 ); 1401 seenReplace = 1; 1402 break; 1403 } 1404 } 1405 sqlite3VdbeJumpHere(v, j3); 1406 sqlite3ReleaseTempReg(pParse, regR); 1407 } 1408 1409 if( pbMayReplace ){ 1410 *pbMayReplace = seenReplace; 1411 } 1412} 1413 1414/* 1415** This routine generates code to finish the INSERT or UPDATE operation 1416** that was started by a prior call to sqlite3GenerateConstraintChecks. 1417** A consecutive range of registers starting at regRowid contains the 1418** rowid and the content to be inserted. 1419** 1420** The arguments to this routine should be the same as the first six 1421** arguments to sqlite3GenerateConstraintChecks. 1422*/ 1423void sqlite3CompleteInsertion( 1424 Parse *pParse, /* The parser context */ 1425 Table *pTab, /* the table into which we are inserting */ 1426 int baseCur, /* Index of a read/write cursor pointing at pTab */ 1427 int regRowid, /* Range of content */ 1428 int *aRegIdx, /* Register used by each index. 0 for unused indices */ 1429 int isUpdate, /* True for UPDATE, False for INSERT */ 1430 int appendBias, /* True if this is likely to be an append */ 1431 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */ 1432){ 1433 int i; 1434 Vdbe *v; 1435 int nIdx; 1436 Index *pIdx; 1437 u8 pik_flags; 1438 int regData; 1439 int regRec; 1440 1441 v = sqlite3GetVdbe(pParse); 1442 assert( v!=0 ); 1443 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 1444 for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} 1445 for(i=nIdx-1; i>=0; i--){ 1446 if( aRegIdx[i]==0 ) continue; 1447 sqlite3VdbeAddOp2(v, OP_IdxInsert, baseCur+i+1, aRegIdx[i]); 1448 if( useSeekResult ){ 1449 sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); 1450 } 1451 } 1452 regData = regRowid + 1; 1453 regRec = sqlite3GetTempReg(pParse); 1454 sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec); 1455 sqlite3TableAffinityStr(v, pTab); 1456 sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol); 1457 if( pParse->nested ){ 1458 pik_flags = 0; 1459 }else{ 1460 pik_flags = OPFLAG_NCHANGE; 1461 pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID); 1462 } 1463 if( appendBias ){ 1464 pik_flags |= OPFLAG_APPEND; 1465 } 1466 if( useSeekResult ){ 1467 pik_flags |= OPFLAG_USESEEKRESULT; 1468 } 1469 sqlite3VdbeAddOp3(v, OP_Insert, baseCur, regRec, regRowid); 1470 if( !pParse->nested ){ 1471 sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT); 1472 } 1473 sqlite3VdbeChangeP5(v, pik_flags); 1474} 1475 1476/* 1477** Generate code that will open cursors for a table and for all 1478** indices of that table. The "baseCur" parameter is the cursor number used 1479** for the table. Indices are opened on subsequent cursors. 1480** 1481** Return the number of indices on the table. 1482*/ 1483int sqlite3OpenTableAndIndices( 1484 Parse *pParse, /* Parsing context */ 1485 Table *pTab, /* Table to be opened */ 1486 int baseCur, /* Cursor number assigned to the table */ 1487 int op /* OP_OpenRead or OP_OpenWrite */ 1488){ 1489 int i; 1490 int iDb; 1491 Index *pIdx; 1492 Vdbe *v; 1493 1494 if( IsVirtual(pTab) ) return 0; 1495 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); 1496 v = sqlite3GetVdbe(pParse); 1497 assert( v!=0 ); 1498 sqlite3OpenTable(pParse, baseCur, iDb, pTab, op); 1499 for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 1500 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); 1501 assert( pIdx->pSchema==pTab->pSchema ); 1502 sqlite3VdbeAddOp4(v, op, i+baseCur, pIdx->tnum, iDb, 1503 (char*)pKey, P4_KEYINFO_HANDOFF); 1504 VdbeComment((v, "%s", pIdx->zName)); 1505 } 1506 if( pParse->nTab<baseCur+i ){ 1507 pParse->nTab = baseCur+i; 1508 } 1509 return i-1; 1510} 1511 1512 1513#ifdef SQLITE_TEST 1514/* 1515** The following global variable is incremented whenever the 1516** transfer optimization is used. This is used for testing 1517** purposes only - to make sure the transfer optimization really 1518** is happening when it is suppose to. 1519*/ 1520int sqlite3_xferopt_count; 1521#endif /* SQLITE_TEST */ 1522 1523 1524#ifndef SQLITE_OMIT_XFER_OPT 1525/* 1526** Check to collation names to see if they are compatible. 1527*/ 1528static int xferCompatibleCollation(const char *z1, const char *z2){ 1529 if( z1==0 ){ 1530 return z2==0; 1531 } 1532 if( z2==0 ){ 1533 return 0; 1534 } 1535 return sqlite3StrICmp(z1, z2)==0; 1536} 1537 1538 1539/* 1540** Check to see if index pSrc is compatible as a source of data 1541** for index pDest in an insert transfer optimization. The rules 1542** for a compatible index: 1543** 1544** * The index is over the same set of columns 1545** * The same DESC and ASC markings occurs on all columns 1546** * The same onError processing (OE_Abort, OE_Ignore, etc) 1547** * The same collating sequence on each column 1548*/ 1549static int xferCompatibleIndex(Index *pDest, Index *pSrc){ 1550 int i; 1551 assert( pDest && pSrc ); 1552 assert( pDest->pTable!=pSrc->pTable ); 1553 if( pDest->nColumn!=pSrc->nColumn ){ 1554 return 0; /* Different number of columns */ 1555 } 1556 if( pDest->onError!=pSrc->onError ){ 1557 return 0; /* Different conflict resolution strategies */ 1558 } 1559 for(i=0; i<pSrc->nColumn; i++){ 1560 if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ 1561 return 0; /* Different columns indexed */ 1562 } 1563 if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){ 1564 return 0; /* Different sort orders */ 1565 } 1566 if( !xferCompatibleCollation(pSrc->azColl[i],pDest->azColl[i]) ){ 1567 return 0; /* Different collating sequences */ 1568 } 1569 } 1570 1571 /* If no test above fails then the indices must be compatible */ 1572 return 1; 1573} 1574 1575/* 1576** Attempt the transfer optimization on INSERTs of the form 1577** 1578** INSERT INTO tab1 SELECT * FROM tab2; 1579** 1580** This optimization is only attempted if 1581** 1582** (1) tab1 and tab2 have identical schemas including all the 1583** same indices and constraints 1584** 1585** (2) tab1 and tab2 are different tables 1586** 1587** (3) There must be no triggers on tab1 1588** 1589** (4) The result set of the SELECT statement is "*" 1590** 1591** (5) The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY, 1592** or LIMIT clause. 1593** 1594** (6) The SELECT statement is a simple (not a compound) select that 1595** contains only tab2 in its FROM clause 1596** 1597** This method for implementing the INSERT transfers raw records from 1598** tab2 over to tab1. The columns are not decoded. Raw records from 1599** the indices of tab2 are transfered to tab1 as well. In so doing, 1600** the resulting tab1 has much less fragmentation. 1601** 1602** This routine returns TRUE if the optimization is attempted. If any 1603** of the conditions above fail so that the optimization should not 1604** be attempted, then this routine returns FALSE. 1605*/ 1606static int xferOptimization( 1607 Parse *pParse, /* Parser context */ 1608 Table *pDest, /* The table we are inserting into */ 1609 Select *pSelect, /* A SELECT statement to use as the data source */ 1610 int onError, /* How to handle constraint errors */ 1611 int iDbDest /* The database of pDest */ 1612){ 1613 ExprList *pEList; /* The result set of the SELECT */ 1614 Table *pSrc; /* The table in the FROM clause of SELECT */ 1615 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */ 1616 struct SrcList_item *pItem; /* An element of pSelect->pSrc */ 1617 int i; /* Loop counter */ 1618 int iDbSrc; /* The database of pSrc */ 1619 int iSrc, iDest; /* Cursors from source and destination */ 1620 int addr1, addr2; /* Loop addresses */ 1621 int emptyDestTest; /* Address of test for empty pDest */ 1622 int emptySrcTest; /* Address of test for empty pSrc */ 1623 Vdbe *v; /* The VDBE we are building */ 1624 KeyInfo *pKey; /* Key information for an index */ 1625 int regAutoinc; /* Memory register used by AUTOINC */ 1626 int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */ 1627 int regData, regRowid; /* Registers holding data and rowid */ 1628 1629 if( pSelect==0 ){ 1630 return 0; /* Must be of the form INSERT INTO ... SELECT ... */ 1631 } 1632 if( sqlite3TriggerList(pParse, pDest) ){ 1633 return 0; /* tab1 must not have triggers */ 1634 } 1635#ifndef SQLITE_OMIT_VIRTUALTABLE 1636 if( pDest->tabFlags & TF_Virtual ){ 1637 return 0; /* tab1 must not be a virtual table */ 1638 } 1639#endif 1640 if( onError==OE_Default ){ 1641 onError = OE_Abort; 1642 } 1643 if( onError!=OE_Abort && onError!=OE_Rollback ){ 1644 return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */ 1645 } 1646 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */ 1647 if( pSelect->pSrc->nSrc!=1 ){ 1648 return 0; /* FROM clause must have exactly one term */ 1649 } 1650 if( pSelect->pSrc->a[0].pSelect ){ 1651 return 0; /* FROM clause cannot contain a subquery */ 1652 } 1653 if( pSelect->pWhere ){ 1654 return 0; /* SELECT may not have a WHERE clause */ 1655 } 1656 if( pSelect->pOrderBy ){ 1657 return 0; /* SELECT may not have an ORDER BY clause */ 1658 } 1659 /* Do not need to test for a HAVING clause. If HAVING is present but 1660 ** there is no ORDER BY, we will get an error. */ 1661 if( pSelect->pGroupBy ){ 1662 return 0; /* SELECT may not have a GROUP BY clause */ 1663 } 1664 if( pSelect->pLimit ){ 1665 return 0; /* SELECT may not have a LIMIT clause */ 1666 } 1667 assert( pSelect->pOffset==0 ); /* Must be so if pLimit==0 */ 1668 if( pSelect->pPrior ){ 1669 return 0; /* SELECT may not be a compound query */ 1670 } 1671 if( pSelect->selFlags & SF_Distinct ){ 1672 return 0; /* SELECT may not be DISTINCT */ 1673 } 1674 pEList = pSelect->pEList; 1675 assert( pEList!=0 ); 1676 if( pEList->nExpr!=1 ){ 1677 return 0; /* The result set must have exactly one column */ 1678 } 1679 assert( pEList->a[0].pExpr ); 1680 if( pEList->a[0].pExpr->op!=TK_ALL ){ 1681 return 0; /* The result set must be the special operator "*" */ 1682 } 1683 1684 /* At this point we have established that the statement is of the 1685 ** correct syntactic form to participate in this optimization. Now 1686 ** we have to check the semantics. 1687 */ 1688 pItem = pSelect->pSrc->a; 1689 pSrc = sqlite3LocateTable(pParse, 0, pItem->zName, pItem->zDatabase); 1690 if( pSrc==0 ){ 1691 return 0; /* FROM clause does not contain a real table */ 1692 } 1693 if( pSrc==pDest ){ 1694 return 0; /* tab1 and tab2 may not be the same table */ 1695 } 1696#ifndef SQLITE_OMIT_VIRTUALTABLE 1697 if( pSrc->tabFlags & TF_Virtual ){ 1698 return 0; /* tab2 must not be a virtual table */ 1699 } 1700#endif 1701 if( pSrc->pSelect ){ 1702 return 0; /* tab2 may not be a view */ 1703 } 1704 if( pDest->nCol!=pSrc->nCol ){ 1705 return 0; /* Number of columns must be the same in tab1 and tab2 */ 1706 } 1707 if( pDest->iPKey!=pSrc->iPKey ){ 1708 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ 1709 } 1710 for(i=0; i<pDest->nCol; i++){ 1711 if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){ 1712 return 0; /* Affinity must be the same on all columns */ 1713 } 1714 if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){ 1715 return 0; /* Collating sequence must be the same on all columns */ 1716 } 1717 if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){ 1718 return 0; /* tab2 must be NOT NULL if tab1 is */ 1719 } 1720 } 1721 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ 1722 if( pDestIdx->onError!=OE_None ){ 1723 destHasUniqueIdx = 1; 1724 } 1725 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ 1726 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; 1727 } 1728 if( pSrcIdx==0 ){ 1729 return 0; /* pDestIdx has no corresponding index in pSrc */ 1730 } 1731 } 1732#ifndef SQLITE_OMIT_CHECK 1733 if( pDest->pCheck && sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){ 1734 return 0; /* Tables have different CHECK constraints. Ticket #2252 */ 1735 } 1736#endif 1737 1738 /* If we get this far, it means either: 1739 ** 1740 ** * We can always do the transfer if the table contains an 1741 ** an integer primary key 1742 ** 1743 ** * We can conditionally do the transfer if the destination 1744 ** table is empty. 1745 */ 1746#ifdef SQLITE_TEST 1747 sqlite3_xferopt_count++; 1748#endif 1749 iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema); 1750 v = sqlite3GetVdbe(pParse); 1751 sqlite3CodeVerifySchema(pParse, iDbSrc); 1752 iSrc = pParse->nTab++; 1753 iDest = pParse->nTab++; 1754 regAutoinc = autoIncBegin(pParse, iDbDest, pDest); 1755 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); 1756 if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){ 1757 /* If tables do not have an INTEGER PRIMARY KEY and there 1758 ** are indices to be copied and the destination is not empty, 1759 ** we have to disallow the transfer optimization because the 1760 ** the rowids might change which will mess up indexing. 1761 ** 1762 ** Or if the destination has a UNIQUE index and is not empty, 1763 ** we also disallow the transfer optimization because we cannot 1764 ** insure that all entries in the union of DEST and SRC will be 1765 ** unique. 1766 */ 1767 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); 1768 emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0); 1769 sqlite3VdbeJumpHere(v, addr1); 1770 }else{ 1771 emptyDestTest = 0; 1772 } 1773 sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead); 1774 emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); 1775 regData = sqlite3GetTempReg(pParse); 1776 regRowid = sqlite3GetTempReg(pParse); 1777 if( pDest->iPKey>=0 ){ 1778 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); 1779 addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid); 1780 sqlite3HaltConstraint( 1781 pParse, onError, "PRIMARY KEY must be unique", P4_STATIC); 1782 sqlite3VdbeJumpHere(v, addr2); 1783 autoIncStep(pParse, regAutoinc, regRowid); 1784 }else if( pDest->pIndex==0 ){ 1785 addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid); 1786 }else{ 1787 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); 1788 assert( (pDest->tabFlags & TF_Autoincrement)==0 ); 1789 } 1790 sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData); 1791 sqlite3VdbeAddOp3(v, OP_Insert, iDest, regData, regRowid); 1792 sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND); 1793 sqlite3VdbeChangeP4(v, -1, pDest->zName, 0); 1794 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); 1795 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ 1796 for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){ 1797 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; 1798 } 1799 assert( pSrcIdx ); 1800 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 1801 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 1802 pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx); 1803 sqlite3VdbeAddOp4(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc, 1804 (char*)pKey, P4_KEYINFO_HANDOFF); 1805 VdbeComment((v, "%s", pSrcIdx->zName)); 1806 pKey = sqlite3IndexKeyinfo(pParse, pDestIdx); 1807 sqlite3VdbeAddOp4(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest, 1808 (char*)pKey, P4_KEYINFO_HANDOFF); 1809 VdbeComment((v, "%s", pDestIdx->zName)); 1810 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); 1811 sqlite3VdbeAddOp2(v, OP_RowKey, iSrc, regData); 1812 sqlite3VdbeAddOp3(v, OP_IdxInsert, iDest, regData, 1); 1813 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); 1814 sqlite3VdbeJumpHere(v, addr1); 1815 } 1816 sqlite3VdbeJumpHere(v, emptySrcTest); 1817 sqlite3ReleaseTempReg(pParse, regRowid); 1818 sqlite3ReleaseTempReg(pParse, regData); 1819 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 1820 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 1821 if( emptyDestTest ){ 1822 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_OK, 0); 1823 sqlite3VdbeJumpHere(v, emptyDestTest); 1824 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 1825 return 0; 1826 }else{ 1827 return 1; 1828 } 1829} 1830#endif /* SQLITE_OMIT_XFER_OPT */ 1831