/*** 2001 September 15**** The author disclaims copyright to this source code.  In place of** a legal notice, here is a blessing:****    May you do good and not evil.**    May you find forgiveness for yourself and forgive others.**    May you share freely, never taking more than you give.***************************************************************************** This file contains C code routines that are called by the parser** to handle INSERT statements in SQLite.**** $Id: insert.c,v 1.143 2005/09/20 17:42:23 drh Exp $*/#include "sqliteInt.h"/*** Set P3 of the most recently inserted opcode to a column affinity** string for index pIdx. A column affinity string has one character** for each column in the table, according to the affinity of the column:****  Character      Column affinity**  ------------------------------**  'n'            NUMERIC**  'i'            INTEGER**  't'            TEXT**  'o'            NONE*/void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){  if( !pIdx->zColAff ){    /* The first time a column affinity string for a particular index is    ** required, it is allocated and populated here. It is then stored as    ** a member of the Index structure for subsequent use.    **    ** The column affinity string will eventually be deleted by    ** sqliteDeleteIndex() when the Index structure itself is cleaned    ** up.    */    int n;    Table *pTab = pIdx->pTable;    pIdx->zColAff = (char *)sqliteMalloc(pIdx->nColumn+1);    if( !pIdx->zColAff ){      return;    }    for(n=0; n<pIdx->nColumn; n++){      pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity;    }    pIdx->zColAff[pIdx->nColumn] = '\0';  }   sqlite3VdbeChangeP3(v, -1, pIdx->zColAff, 0);}/*** Set P3 of the most recently inserted opcode to a column affinity** string for table pTab. A column affinity string has one character** for each column indexed by the index, according to the affinity of the** column:****  Character      Column affinity**  ------------------------------**  'n'            NUMERIC**  'i'            INTEGER**  't'            TEXT**  'o'            NONE*/void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){  /* The first time a column affinity string for a particular table  ** is required, it is allocated and populated here. It is then   ** stored as a member of the Table structure for subsequent use.  **  ** The column affinity string will eventually be deleted by  ** sqlite3DeleteTable() when the Table structure itself is cleaned up.  */  if( !pTab->zColAff ){    char *zColAff;    int i;    zColAff = (char *)sqliteMalloc(pTab->nCol+1);    if( !zColAff ){      return;    }    for(i=0; i<pTab->nCol; i++){      zColAff[i] = pTab->aCol[i].affinity;    }    zColAff[pTab->nCol] = '\0';    pTab->zColAff = zColAff;  }  sqlite3VdbeChangeP3(v, -1, pTab->zColAff, 0);}/*** Return non-zero if SELECT statement p opens the table with rootpage** iTab in database iDb.  This is used to see if a statement of the form ** "INSERT INTO <iDb, iTab> SELECT ..." can run without using temporary** table for the results of the SELECT. **** No checking is done for sub-selects that are part of expressions.*/static int selectReadsTable(Select *p, int iDb, int iTab){  int i;  struct SrcList_item *pItem;  if( p->pSrc==0 ) return 0;  for(i=0, pItem=p->pSrc->a; i<p->pSrc->nSrc; i++, pItem++){    if( pItem->pSelect ){      if( selectReadsTable(pItem->pSelect, iDb, iTab) ) return 1;    }else{      if( pItem->pTab->iDb==iDb && pItem->pTab->tnum==iTab ) return 1;    }  }  return 0;}/*** This routine is call to handle SQL of the following forms:****    insert into TABLE (IDLIST) values(EXPRLIST)**    insert into TABLE (IDLIST) select**** The IDLIST following the table name is always optional.  If omitted,** then a list of all columns for the table is substituted.  The IDLIST** appears in the pColumn parameter.  pColumn is NULL if IDLIST is omitted.**** The pList parameter holds EXPRLIST in the first form of the INSERT** statement above, and pSelect is NULL.  For the second form, pList is** NULL and pSelect is a pointer to the select statement used to generate** data for the insert.**** The code generated follows one of three templates.  For a simple** select with data coming from a VALUES clause, the code executes** once straight down through.  The template looks like this:****         open write cursor to <table> and its indices**         puts VALUES clause expressions onto the stack**         write the resulting record into <table>**         cleanup**** If the statement is of the form****   INSERT INTO <table> SELECT ...**** And the SELECT clause does not read from <table> at any time, then** the generated code follows this template:****         goto B**      A: setup for the SELECT**         loop over the tables in the SELECT**           gosub C**         end loop**         cleanup after the SELECT**         goto D**      B: open write cursor to <table> and its indices**         goto A**      C: insert the select result into <table>**         return**      D: cleanup**** The third template is used if the insert statement takes its** values from a SELECT but the data is being inserted into a table** that is also read as part of the SELECT.  In the third form,** we have to use a intermediate table to store the results of** the select.  The template is like this:****         goto B**      A: setup for the SELECT**         loop over the tables in the SELECT**           gosub C**         end loop**         cleanup after the SELECT**         goto D**      C: insert the select result into the intermediate table**         return**      B: open a cursor to an intermediate table**         goto A**      D: open write cursor to <table> and its indices**         loop over the intermediate table**           transfer values form intermediate table into <table>**         end the loop**         cleanup*/void sqlite3Insert(  Parse *pParse,        /* Parser context */  SrcList *pTabList,    /* Name of table into which we are inserting */  ExprList *pList,      /* List of values to be inserted */  Select *pSelect,      /* A SELECT statement to use as the data source */  IdList *pColumn,      /* Column names corresponding to IDLIST. */  int onError           /* How to handle constraint errors */){  Table *pTab;          /* The table to insert into */  char *zTab;           /* Name of the table into which we are inserting */  const char *zDb;      /* Name of the database holding this table */  int i, j, idx;        /* Loop counters */  Vdbe *v;              /* Generate code into this virtual machine */  Index *pIdx;          /* For looping over indices of the table */  int nColumn;          /* Number of columns in the data */  int base = 0;         /* VDBE Cursor number for pTab */  int iCont=0,iBreak=0; /* Beginning and end of the loop over srcTab */  sqlite3 *db;          /* The main database structure */  int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */  int endOfLoop;        /* Label for the end of the insertion loop */  int useTempTable = 0; /* Store SELECT results in intermediate table */  int srcTab = 0;       /* Data comes from this temporary cursor if >=0 */  int iSelectLoop = 0;  /* Address of code that implements the SELECT */  int iCleanup = 0;     /* Address of the cleanup code */  int iInsertBlock = 0; /* Address of the subroutine used to insert data */  int iCntMem = 0;      /* Memory cell used for the row counter */  int newIdx = -1;      /* Cursor for the NEW table */  Db *pDb;              /* The database containing table being inserted into */  int counterMem = 0;   /* Memory cell holding AUTOINCREMENT counter */#ifndef SQLITE_OMIT_TRIGGER  int isView;                 /* True if attempting to insert into a view */  int triggers_exist = 0;     /* True if there are FOR EACH ROW triggers */#endif#ifndef SQLITE_OMIT_AUTOINCREMENT  int counterRowid;     /* Memory cell holding rowid of autoinc counter */#endif  if( pParse->nErr || sqlite3_malloc_failed ) goto insert_cleanup;  db = pParse->db;  /* Locate the table into which we will be inserting new information.  */  assert( pTabList->nSrc==1 );  zTab = pTabList->a[0].zName;  if( zTab==0 ) goto insert_cleanup;  pTab = sqlite3SrcListLookup(pParse, pTabList);  if( pTab==0 ){    goto insert_cleanup;  }  assert( pTab->iDb<db->nDb );  pDb = &db->aDb[pTab->iDb];  zDb = pDb->zName;  if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){    goto insert_cleanup;  }  /* Figure out if we have any triggers and if the table being  ** inserted into is a view  */#ifndef SQLITE_OMIT_TRIGGER  triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0);  isView = pTab->pSelect!=0;#else# define triggers_exist 0# define isView 0#endif#ifdef SQLITE_OMIT_VIEW# undef isView# define isView 0#endif  /* Ensure that:  *  (a) the table is not read-only,   *  (b) that if it is a view then ON INSERT triggers exist  */  if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){    goto insert_cleanup;  }  if( pTab==0 ) goto insert_cleanup;  /* If pTab is really a view, make sure it has been initialized.  */  if( isView && sqlite3ViewGetColumnNames(pParse, pTab) ){    goto insert_cleanup;  }  /* Ensure all required collation sequences are available. */  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){    if( sqlite3CheckIndexCollSeq(pParse, pIdx) ){      goto insert_cleanup;    }  }  /* Allocate a VDBE  */  v = sqlite3GetVdbe(pParse);  if( v==0 ) goto insert_cleanup;  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);  sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, pTab->iDb);  /* if there are row triggers, allocate a temp table for new.* references. */  if( triggers_exist ){    newIdx = pParse->nTab++;  }#ifndef SQLITE_OMIT_AUTOINCREMENT  /* If this is an AUTOINCREMENT table, look up the sequence number in the  ** sqlite_sequence table and store it in memory cell counterMem.  Also  ** remember the rowid of the sqlite_sequence table entry in memory cell  ** counterRowid.  */  if( pTab->autoInc ){    int iCur = pParse->nTab;    int base = sqlite3VdbeCurrentAddr(v);    counterRowid = pParse->nMem++;    counterMem = pParse->nMem++;    sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);    sqlite3VdbeAddOp(v, OP_OpenRead, iCur, pDb->pSeqTab->tnum);    sqlite3VdbeAddOp(v, OP_SetNumColumns, iCur, 2);    sqlite3VdbeAddOp(v, OP_Rewind, iCur, base+13);    sqlite3VdbeAddOp(v, OP_Column, iCur, 0);    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);    sqlite3VdbeAddOp(v, OP_Ne, 28417, base+12);    sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);    sqlite3VdbeAddOp(v, OP_MemStore, counterRowid, 1);    sqlite3VdbeAddOp(v, OP_Column, iCur, 1);    sqlite3VdbeAddOp(v, OP_MemStore, counterMem, 1);    sqlite3VdbeAddOp(v, OP_Goto, 0, base+13);    sqlite3VdbeAddOp(v, OP_Next, iCur, base+4);    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);  }#endif /* SQLITE_OMIT_AUTOINCREMENT */  /* Figure out how many columns of data are supplied.  If the data  ** is coming from a SELECT statement, then this step also generates  ** all the code to implement the SELECT statement and invoke a subroutine  ** to process each row of the result. (Template 2.) If the SELECT  ** statement uses the the table that is being inserted into, then the  ** subroutine is also coded here.  That subroutine stores the SELECT  ** results in a temporary table. (Template 3.)  */  if( pSelect ){    /* Data is coming from a SELECT.  Generate code to implement that SELECT    */    int rc, iInitCode;    iInitCode = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);    iSelectLoop = sqlite3VdbeCurrentAddr(v);    iInsertBlock = sqlite3VdbeMakeLabel(v);    /* Resolve the expressions in the SELECT statement and execute it. */    rc = sqlite3Select(pParse, pSelect, SRT_Subroutine, iInsertBlock,0,0,0,0);    if( rc || pParse->nErr || sqlite3_malloc_failed ) goto insert_cleanup;    iCleanup = sqlite3VdbeMakeLabel(v);    sqlite3VdbeAddOp(v, OP_Goto, 0, iCleanup);    assert( pSelect->pEList );    nColumn = pSelect->pEList->nExpr;    /* Set useTempTable to TRUE if the result of the SELECT statement    ** should be written into a temporary table.  Set to FALSE if each    ** row of the SELECT can be written directly into the result table.    **    ** A temp table must be used if the table being updated is also one    ** of the tables being read by the SELECT statement.  Also use a     ** temp table in the case of row triggers.    */    if( triggers_exist || selectReadsTable(pSelect, pTab->iDb, pTab->tnum) ){      useTempTable = 1;    }    if( useTempTable ){      /* Generate the subroutine that SELECT calls to process each row of      ** the result.  Store the result in a temporary table      */      srcTab = pParse->nTab++;      sqlite3VdbeResolveLabel(v, iInsertBlock);      sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);      sqlite3TableAffinityStr(v, pTab);      sqlite3VdbeAddOp(v, OP_NewRowid, srcTab, 0);      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);      sqlite3VdbeAddOp(v, OP_Insert, srcTab, 0);      sqlite3VdbeAddOp(v, OP_Return, 0, 0);