SQLite

  int i;               /* loop counter */
  int ix;              /* Index loop counter */
  int nCol;            /* Number of columns */
  int onError;         /* Conflict resolution strategy */
  int j1;              /* Addresss of jump instruction */
  int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  int ipkTop = 0;      /* Top of the rowid change constraint check */
  int ipkBottom = 0;   /* Bottom of the rowid change constraint check */
  u8 isUpdate;
  u8 isUpdate;         /* True if this is an UPDATE operation */

  isUpdate = regOldData!=0;
  db = pParse->db;
  v = sqlite3GetVdbe(pParse);
  assert( v!=0 );
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  nCol = pTab->nCol;

    if( isUpdate ){
      /* pkChng!=0 does not mean that the rowid has change, only that
      ** it might have changed.  Skip the conflict logic below if the rowid
      ** is unchanged. */
      sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
    }

    /* If the response to a rowid conflict is REPLACE but the response
    ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
    ** to defer the running of the rowid conflict checking until after
    ** the UNIQUE constraints have run.
    */
    if( onError==OE_Replace && overrideError!=OE_Replace ){
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        if( pIdx->onError==OE_Ignore || pIdx->onError==OE_Fail ){
          ipkTop = sqlite3VdbeAddOp0(v, OP_Goto);
          break;
        }
      }
    }

    /* Check to see if the new rowid already exists in the table.  Skip
    ** the following conflict logic if it does not. */
    sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);

    /* Generate code that deals with a rowid collision */
    switch( onError ){

          sqlite3MultiWrite(pParse);
          sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0);
        }
        seenReplace = 1;
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        /*assert( seenReplace==0 );*/
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
      }
    }
    sqlite3VdbeResolveLabel(v, addrRowidOk);
    if( ipkTop ){
      ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
      sqlite3VdbeJumpHere(v, ipkTop);
    }
  }

  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Compute the revised record entries for indices as we go.
  **
  ** This loop also handles the case of the PRIMARY KEY index for a

      continue;  /* pIdx is not a UNIQUE index */
    }
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }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 */
    regR = sqlite3GetTempRange(pParse, nPkField);
    sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
                         regIdx, pIdx->nKeyCol);

    /* Generate code to handle collisions */

      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqlite3UniqueConstraint(pParse, onError, pIdx);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
      }
      default: {
        Trigger *pTrigger = 0;
        assert( onError==OE_Replace );
        sqlite3MultiWrite(pParse);
        if( db->flags&SQLITE_RecTriggers ){
          pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
        }
        sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
                                 regR, nPkField, 0, OE_Replace);
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeResolveLabel(v, addrUniqueOk);
    sqlite3ReleaseTempRange(pParse, regR, nPkField);
  }
  if( ipkTop ){
    sqlite3VdbeAddOp2(v, OP_Goto, 0, ipkTop+1);
    sqlite3VdbeJumpHere(v, ipkBottom);
  }
  
  if( pbMayReplace ){
    *pbMayReplace = seenReplace;
  }
  VdbeModuleComment((v, "END: GenCnstCks()"));
}

# 2013-11-05
#
# 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.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# This file focuses on making sure that combinations of REPLACE,
# IGNORE, and FAIL conflict resolution play well together.
#

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

ifcapable !conflict {
  finish_test
  return
}

do_execsql_test conflict-1.1 {
  CREATE TABLE t1(
    a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-1.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-1.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-1.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Replete the tests above, but this time on a table non-INTEGER primary key.
#
do_execsql_test conflict-2.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a INT PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-2.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-2.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-2.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Replete again on a WITHOUT ROWID table.
#
do_execsql_test conflict-3.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a INT PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-3.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-3.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-3.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test conflict-4.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL,
    a INT PRIMARY KEY ON CONFLICT REPLACE
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-4.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-4.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-4.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test conflict-5.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    b UNIQUE ON CONFLICT IGNORE,
    a INT PRIMARY KEY ON CONFLICT REPLACE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-5.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-5.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-5.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test conflict-6.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    c UNIQUE ON CONFLICT FAIL,
    a INT PRIMARY KEY ON CONFLICT REPLACE,
    b UNIQUE ON CONFLICT IGNORE
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-6.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-6.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-6.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-7.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-7.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-7.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-7.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-8.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INT PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-8.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-8.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-8.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-9.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INT PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-9.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-9.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-9.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-10.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c INTEGER PRIMARY KEY ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-10.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-10.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-10.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-11.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c PRIMARY KEY ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-11.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-11.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-11.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}


finish_test