SQLite

**    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.136 2005/02/08 08:42:28 danielk1977 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:

    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(p, 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
**

  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 */

    ** 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++;

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.15 2005/02/08 08:42:29 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create some tables with data that we can select against
#
do_test insert2-1.0 {

           DepCheck.name == Dependencies.name AND 
           DepCheck.flag == Dependencies.flag 
       WHERE 
           Dependencies.depId is NULL;
  };
} {}

#--------------------------------------------------------------------
# Test that the INSERT works when the SELECT statement (a) references
# the table being inserted into and (b) is optimized to use an index
# only.
do_test insert2-5.1 {
  execsql {
    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES(1, 2);
    CREATE INDEX t2i1 ON t2(a);
    INSERT INTO t2 SELECT a, 3 FROM t2 WHERE a = 1;
    SELECT * FROM t2;
  }
} {1 2 1 3}
do_test insert2-5.2 {
  execsql {
    INSERT INTO t2 SELECT (SELECT a FROM t2), 4;
    SELECT * FROM t2;
  }
} {1 2 1 3 1 4}

finish_test