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.87 2003/06/04 12:23:31 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)

  /* Test all CHECK constraints
  */
  /**** TBD ****/

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.


  */
  if( recnoChng ){
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    

    if( isUpdate ){
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
    jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
    switch( onError ){
      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
      }
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
        sqliteVdbeChangeP3(v, -1, "PRIMARY KEY must be unique", P3_STATIC);
        break;
      }
      case OE_Replace: {
        sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0);
        if( isUpdate ){
          sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
          sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        }
        seenReplace = 1;
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
    }
    contAddr = sqliteVdbeCurrentAddr(v);
    sqliteVdbeChangeP2(v, jumpInst2, contAddr);
    if( isUpdate ){
      sqliteVdbeChangeP2(v, jumpInst1, contAddr);
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

    }
  }

  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Add the new records to the indices as we go.
  */

    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( seenReplace ){
      if( onError==OE_Ignore ) onError = OE_Replace;
      else if( onError==OE_Fail ) onError = OE_Abort;
    }
    

    /* Check to see if the new index entry will be unique */
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
    jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);

    /* Generate code that executes if the new index entry is not unique */
    switch( onError ){

# 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 implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.13 2003/06/04 12:23:32 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {

  # verify that no temporary table is used to copy test1 to t4
  set x [execsql {
    EXPLAIN INSERT INTO t4 SELECT one FROM test1;
  }]
  expr {[lsearch $x OpenTemp]>0}
} {0}

# Ticket #334:  REPLACE statement corrupting indices.
#
do_test insert-6.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(2,3);
    SELECT b FROM t1 WHERE b=2;
  }
} {2}
do_test insert-6.2 {
  execsql {
    REPLACE INTO t1 VALUES(1,4);
    SELECT b FROM t1 WHERE b=2;
  }
} {}

finish_test