/*

** 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.172 2006/08/29 18:46:14 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

**  ------------------------------

**  'a'            TEXT

**  'b'            NONE

**  'c'            NUMERIC

**  'd'            INTEGER

**  'e'            REAL

*/

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

**  ------------------------------

**  'a'            TEXT

**  'b'            NONE

**  'c'            NUMERIC

**  'd'            INTEGER

**  'e'            REAL

*/

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, Schema *pSchema, 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, pSchema, iTab) ) return 1;

    }else{

      if( pItem->pTab->pSchema==pSchema && 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 */

  int iDb;

#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 = 0;  /* Memory cell holding rowid of autoinc counter */

#endif

  if( pParse->nErr || sqlite3MallocFailed() ){

    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;

  }

  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);

  assert( iDb<db->nDb );

  pDb = &db->aDb[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;

  }

  assert( pTab!=0 );

  /* If pTab is really a view, make sure it has been initialized.

  ** ViewGetColumnNames() is a no-op if pTab is not a view (or virtual 

  ** module table).

  */

  if( sqlite3ViewGetColumnNames(pParse, pTab) ){

    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, 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 addr = sqlite3VdbeCurrentAddr(v);

    counterRowid = pParse->nMem++;

    counterMem = pParse->nMem++;

    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);

    sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13);

    sqlite3VdbeAddOp(v, OP_Column, iCur, 0);

    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);

    sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+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, addr+13);

    sqlite3VdbeAddOp(v, OP_Next, iCur, addr+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 || sqlite3MallocFailed() ){

      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->pSchema,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);

      sqlite3VdbeAddOp(v, OP_NewRowid, srcTab, 0);

      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);

      sqlite3VdbeAddOp(v, OP_Insert, srcTab, 0);

      sqlite3VdbeAddOp(v, OP_Return, 0, 0);

      /* The following code runs first because the GOTO at the very top

      ** of the program jumps to it.  Create the temporary table, then jump

      ** back up and execute the SELECT code above.

      */

      sqlite3VdbeJumpHere(v, iInitCode);

      sqlite3VdbeAddOp(v, OP_OpenEphemeral, srcTab, 0);

      sqlite3VdbeAddOp(v, OP_SetNumColumns, srcTab, nColumn);

      sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop);

      sqlite3VdbeResolveLabel(v, iCleanup);

    }else{

      sqlite3VdbeJumpHere(v, iInitCode);

    }

  }else{

    /* This is the case if the data for the INSERT is coming from a VALUES

    ** clause

    */

    NameContext sNC;

    memset(&sNC, 0, sizeof(sNC));

    sNC.pParse = pParse;

    srcTab = -1;

    useTempTable = 0;

    nColumn = pList ? pList->nExpr : 0;

    for(i=0; i<nColumn; i++){

      if( sqlite3ExprResolveNames(&sNC, pList->a[i].pExpr) ){

        goto insert_cleanup;

      }

    }

  }

  /* Make sure the number of columns in the source data matches the number

  ** of columns to be inserted into the table.

  */

  if( pColumn==0 && nColumn && nColumn!=pTab->nCol ){

    sqlite3ErrorMsg(pParse, 

       "table %S has %d columns but %d values were supplied",

       pTabList, 0, pTab->nCol, nColumn);

    goto insert_cleanup;

  }

  if( pColumn!=0 && nColumn!=pColumn->nId ){

    sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);

    goto insert_cleanup;

  }

  /* If the INSERT statement included an IDLIST term, then make sure

  ** all elements of the IDLIST really are columns of the table and 

  ** remember the column indices.

  **

  ** If the table has an INTEGER PRIMARY KEY column and that column

  ** is named in the IDLIST, then record in the keyColumn variable

  ** the index into IDLIST of the primary key column.  keyColumn is

  ** the index of the primary key as it appears in IDLIST, not as

  ** is appears in the original table.  (The index of the primary

  ** key in the original table is pTab->iPKey.)

  */

  if( pColumn ){

    for(i=0; i<pColumn->nId; i++){

      pColumn->a[i].idx = -1;

    }

    for(i=0; i<pColumn->nId; i++){

      for(j=0; j<pTab->nCol; j++){

        if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){

          pColumn->a[i].idx = j;

          if( j==pTab->iPKey ){

            keyColumn = i;

          }

          break;

        }

      }

      if( j>=pTab->nCol ){

        if( sqlite3IsRowid(pColumn->a[i].zName) ){

          keyColumn = i;

        }else{

          sqlite3ErrorMsg(pParse, "table %S has no column named %s",

              pTabList, 0, pColumn->a[i].zName);

          pParse->nErr++;

          goto insert_cleanup;

        }

      }

    }

  }

  /* If there is no IDLIST term but the table has an integer primary

  ** key, the set the keyColumn variable to the primary key column index

  ** in the original table definition.

  */

  if( pColumn==0 && nColumn>0 ){

    keyColumn = pTab->iPKey;

  }

  /* Open the temp table for FOR EACH ROW triggers

  */

  if( triggers_exist ){

    sqlite3VdbeAddOp(v, OP_OpenPseudo, newIdx, 0);

    sqlite3VdbeAddOp(v, OP_SetNumColumns, newIdx, pTab->nCol);

  }

  /* Initialize the count of rows to be inserted

  */

  if( db->flags & SQLITE_CountRows ){

    iCntMem = pParse->nMem++;

    sqlite3VdbeAddOp(v, OP_MemInt, 0, iCntMem);

  }

  /* Open tables and indices if there are no row triggers */

  if( !triggers_exist ){

    base = pParse->nTab;

    sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite);

  }

  /* If the data source is a temporary table, then we have to create

  ** a loop because there might be multiple rows of data.  If the data

  ** source is a subroutine call from the SELECT statement, then we need

  ** to launch the SELECT statement processing.

  */

  if( useTempTable ){

    iBreak = sqlite3VdbeMakeLabel(v);

    sqlite3VdbeAddOp(v, OP_Rewind, srcTab, iBreak);

    iCont = sqlite3VdbeCurrentAddr(v);

  }else if( pSelect ){

    sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop);

    sqlite3VdbeResolveLabel(v, iInsertBlock);

  }

  /* Run the BEFORE and INSTEAD OF triggers, if there are any

  */

  endOfLoop = sqlite3VdbeMakeLabel(v);

  if( triggers_exist & TRIGGER_BEFORE ){

    /* build the NEW.* reference row.  Note that if there is an INTEGER

    ** PRIMARY KEY into which a NULL is being inserted, that NULL will be

    ** translated into a unique ID for the row.  But on a BEFORE trigger,

    ** we do not know what the unique ID will be (because the insert has

    ** not happened yet) so we substitute a rowid of -1

    */

    if( keyColumn<0 ){

      sqlite3VdbeAddOp(v, OP_Integer, -1, 0);

    }else if( useTempTable ){

      sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn);

    }else{

      assert( pSelect==0 );  /* Otherwise useTempTable is true */

      sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr);

      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);

      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);

      sqlite3VdbeAddOp(v, OP_Integer, -1, 0);

      sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);

    }

    /* Create the new column data

    */

    for(i=0; i<pTab->nCol; i++){

      if( pColumn==0 ){

        j = i;

      }else{

        for(j=0; j<pColumn->nId; j++){

          if( pColumn->a[j].idx==i ) break;

        }

      }

      if( pColumn && j>=pColumn->nId ){