** 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.
**
*************************************************************************
** $Id: btree.c,v 1.47 2002/02/02 18:49:20 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
** commits or rolls back.
**
** Only one checkpoint may be active at a time.  It is an error to try
** to start a new checkpoint if another checkpoint is already active.
*/
int sqliteBtreeBeginCkpt(Btree *pBt){
  int rc;
  if( !pBt->inTrans || pBt->inCkpt ) return SQLITE_ERROR;


  rc = sqlitepager_ckpt_begin(pBt->pPager);
  pBt->inCkpt = 1;
  return rc;
}


/*
................................................................................
int sqliteBtreeCommitCkpt(Btree *pBt){
  int rc;
  if( pBt->inCkpt ){
    rc = sqlitepager_ckpt_commit(pBt->pPager);
  }else{
    rc = SQLITE_OK;
  }

  return rc;
}

/*
** Rollback the checkpoint to the current transaction.  If there
** is no active checkpoint or transaction, this routine is a no-op.
**
................................................................................
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pPage ){
      sqlitepager_unref(pCur->pPage);
      pCur->pPage = 0;
    }
  }
  rc = sqlitepager_ckpt_rollback(pBt->pPager);

  return rc;
}

/*
** Create a new cursor for the BTree whose root is on the page
** iTable.  The act of acquiring a cursor gets a read lock on 
** the database file.

** 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.
**
*************************************************************************
** $Id: btree.c,v 1.48 2002/02/03 00:56:10 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
** commits or rolls back.
**
** Only one checkpoint may be active at a time.  It is an error to try
** to start a new checkpoint if another checkpoint is already active.
*/
int sqliteBtreeBeginCkpt(Btree *pBt){
  int rc;
  if( !pBt->inTrans || pBt->inCkpt ){
    return SQLITE_ERROR;
  }
  rc = sqlitepager_ckpt_begin(pBt->pPager);
  pBt->inCkpt = 1;
  return rc;
}


/*
................................................................................
int sqliteBtreeCommitCkpt(Btree *pBt){
  int rc;
  if( pBt->inCkpt ){
    rc = sqlitepager_ckpt_commit(pBt->pPager);
  }else{
    rc = SQLITE_OK;
  }
  pBt->inCkpt = 0;
  return rc;
}

/*
** Rollback the checkpoint to the current transaction.  If there
** is no active checkpoint or transaction, this routine is a no-op.
**
................................................................................
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pPage ){
      sqlitepager_unref(pCur->pPage);
      pCur->pPage = 0;
    }
  }
  rc = sqlitepager_ckpt_rollback(pBt->pPager);
  pBt->inCkpt = 0;
  return rc;
}

/*
** Create a new cursor for the BTree whose root is on the page
** iTable.  The act of acquiring a cursor gets a read lock on 
** the database file.

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.71 2002/02/02 18:49:20 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
** the column currently under construction.
*/
void sqliteAddNotNull(Parse *pParse, int onError){
  Table *p;
  int i;
  if( (p = pParse->pNewTable)==0 ) return;
  i = p->nCol-1;
  if( onError==OE_Default ) onError = OE_Abort;
  if( i>=0 ) p->aCol[i].notNull = onError;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  The pFirst token is the first
** token in the sequence of tokens that describe the type of the
................................................................................
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( sqliteStrICmp(pList->a[0].zName, pTab->aCol[iCol].zName)==0 ) break;
    }
  }
  if( iCol>=0 && iCol<pTab->nCol ){
    zType = pTab->aCol[iCol].zType;
  }
  if( onError==OE_Default ) onError = OE_Abort;
  if( pParse->db->file_format>=1 && 
           zType && sqliteStrICmp(zType, "INTEGER")==0 ){
    pTab->iPKey = iCol;
    pTab->keyConf = onError;
  }else{
    sqliteCreateIndex(pParse, 0, 0, pList, onError, 0, 0);
  }
................................................................................
  char *zName = 0;
  int i, j;
  Token nullId;             /* Fake token for an empty ID list */
  sqlite *db = pParse->db;
  int hideName = 0;         /* Do not put table name in the hash table */

  if( pParse->nErr || sqlite_malloc_failed ) goto exit_create_index;
  if( onError==OE_Default ) onError = OE_Abort;

  /*
  ** Find the table that is to be indexed.  Return early if not found.
  */
  if( pTable!=0 ){
    assert( pName!=0 );
    pTab =  sqliteTableFromToken(pParse, pTable);

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.72 2002/02/03 00:56:10 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
** the column currently under construction.
*/
void sqliteAddNotNull(Parse *pParse, int onError){
  Table *p;
  int i;
  if( (p = pParse->pNewTable)==0 ) return;
  i = p->nCol-1;

  if( i>=0 ) p->aCol[i].notNull = onError;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  The pFirst token is the first
** token in the sequence of tokens that describe the type of the
................................................................................
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( sqliteStrICmp(pList->a[0].zName, pTab->aCol[iCol].zName)==0 ) break;
    }
  }
  if( iCol>=0 && iCol<pTab->nCol ){
    zType = pTab->aCol[iCol].zType;
  }

  if( pParse->db->file_format>=1 && 
           zType && sqliteStrICmp(zType, "INTEGER")==0 ){
    pTab->iPKey = iCol;
    pTab->keyConf = onError;
  }else{
    sqliteCreateIndex(pParse, 0, 0, pList, onError, 0, 0);
  }
................................................................................
  char *zName = 0;
  int i, j;
  Token nullId;             /* Fake token for an empty ID list */
  sqlite *db = pParse->db;
  int hideName = 0;         /* Do not put table name in the hash table */

  if( pParse->nErr || sqlite_malloc_failed ) goto exit_create_index;


  /*
  ** Find the table that is to be indexed.  Return early if not found.
  */
  if( pTable!=0 ){
    assert( pName!=0 );
    pTab =  sqliteTableFromToken(pParse, pTable);

**    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.40 2002/02/02 18:49:20 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  int jumpInst;
  int contAddr;
  int hasTwoRecnos = (isUpdate && recnoChng);

  v = sqliteGetVdbe(pParse);
  assert( v!=0 );
  nCol = pTab->nCol;
  if( overrideError==OE_Default ){
    overrideError = pParse->db->onError;
  }

  /* Test all NOT NULL constraints.
  */
  for(i=0; i<nCol; i++){
    if( i==pTab->iPKey ){
      /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;

    }
    if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
      onError = OE_Abort;
    }
    sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
    addr = sqliteVdbeAddOp(v, OP_NotNull, 0, 0);
    switch( onError ){
................................................................................
  }

  /* Test all CHECK constraints
  */

  /* Test all UNIQUE constraints.  Add index records as we go.
  */
  if( (recnoChng || !isUpdate) && pTab->iPKey>=0 && pTab->keyConf!=OE_Replace 
      && overrideError!=OE_Replace ){
    sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;

    }



    switch( onError ){
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
        break;
      }
      case OE_Ignore: {
        sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      default: assert(0);
    }
    contAddr = sqliteVdbeCurrentAddr(v);
    sqliteVdbeChangeP2(v, jumpInst, contAddr);
    if( isUpdate ){
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

    }
  }
  extra = 0;
  for(extra=(-1), iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
    if( aIdxUsed && aIdxUsed[iCur]==0 ) continue;
    extra++;    
    sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
................................................................................
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    onError = pIdx->onError;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;

    }
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
    switch( onError ){
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {

**    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.41 2002/02/03 00:56:10 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  int jumpInst;
  int contAddr;
  int hasTwoRecnos = (isUpdate && recnoChng);

  v = sqliteGetVdbe(pParse);
  assert( v!=0 );
  nCol = pTab->nCol;




  /* Test all NOT NULL constraints.
  */
  for(i=0; i<nCol; i++){
    if( i==pTab->iPKey ){
      /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = pParse->db->onError;
      if( onError==OE_Default ) onError = OE_Abort;
    }
    if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
      onError = OE_Abort;
    }
    sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
    addr = sqliteVdbeAddOp(v, OP_NotNull, 0, 0);
    switch( onError ){
................................................................................
  }

  /* Test all CHECK constraints
  */

  /* Test all UNIQUE constraints.  Add index records as we go.
  */
  if( (recnoChng || !isUpdate) && pTab->iPKey>=0 ){



    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = pParse->db->onError;
      if( onError==OE_Default ) onError = OE_Abort;
    }
    if( onError!=OE_Replace ){
      sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
      jumpInst = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
      switch( onError ){
        case OE_Rollback:
        case OE_Abort:
        case OE_Fail: {
          sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
          break;
        }
        case OE_Ignore: {
          sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
          sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
          break;
        }
        default: assert(0);
      }
      contAddr = sqliteVdbeCurrentAddr(v);
      sqliteVdbeChangeP2(v, jumpInst, contAddr);
      if( isUpdate ){
        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
        sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
      }
    }
  }
  extra = 0;
  for(extra=(-1), iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
    if( aIdxUsed && aIdxUsed[iCur]==0 ) continue;
    extra++;    
    sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
................................................................................
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    onError = pIdx->onError;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = pParse->db->onError;
      if( onError==OE_Default ) onError = OE_Abort;
    }
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
    switch( onError ){
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.46 2002/02/02 15:01:16 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
ecmd ::= cmd.          {sqliteExec(pParse);}
ecmd ::= .
explain ::= EXPLAIN.    {pParse->explain = 1;}

///////////////////// Begin and end transactions. ////////////////////////////
//

// For now, disable the ability to change the default conflict resolution
// algorithm in a transaction.  We made add it back later.
// cmd ::= BEGIN trans_opt onconf(R).  {sqliteBeginTransaction(pParse,R);}
cmd ::= BEGIN trans_opt.        {sqliteBeginTransaction(pParse, OE_Default);}
trans_opt ::= .
trans_opt ::= TRANSACTION.
trans_opt ::= TRANSACTION ids.
cmd ::= COMMIT trans_opt.      {sqliteCommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqliteCommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqliteRollbackTransaction(pParse);}

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.47 2002/02/03 00:56:10 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
ecmd ::= cmd.          {sqliteExec(pParse);}
ecmd ::= .
explain ::= EXPLAIN.    {pParse->explain = 1;}

///////////////////// Begin and end transactions. ////////////////////////////
//



cmd ::= BEGIN trans_opt onconf(R).  {sqliteBeginTransaction(pParse,R);}

trans_opt ::= .
trans_opt ::= TRANSACTION.
trans_opt ::= TRANSACTION ids.
cmd ::= COMMIT trans_opt.      {sqliteCommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqliteCommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqliteRollbackTransaction(pParse);}

** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.114 2002/02/02 18:49:21 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
          sqliteCommitInternalChanges(db);
          db->flags &= ~SQLITE_InTrans;
          db->onError = OE_Default;
        }
        break;
      }
    }
  }else{

    sqliteBtreeCommitCkpt(pBt);
    if( db->pBeTemp ) sqliteBtreeCommitCkpt(db->pBeTemp);
  }   
  return rc;

  /* Jump to here if a malloc() fails.  It's hard to get a malloc()
  ** to fail on a modern VM computer, so this code is untested.
  */
no_mem:
  sqliteSetString(pzErrMsg, "out of memory", 0);

** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.115 2002/02/03 00:56:10 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
          sqliteCommitInternalChanges(db);
          db->flags &= ~SQLITE_InTrans;
          db->onError = OE_Default;
        }
        break;
      }
    }

  }
  sqliteBtreeCommitCkpt(pBt);
  if( db->pBeTemp ) sqliteBtreeCommitCkpt(db->pBeTemp);

  return rc;

  /* Jump to here if a malloc() fails.  It's hard to get a malloc()
  ** to fail on a modern VM computer, so this code is untested.
  */
no_mem:
  sqliteSetString(pzErrMsg, "out of memory", 0);

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.4 2002/01/31 15:54:23 drh Exp $

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

# Create a table with three fields, two of which must be
# UNIQUE.
#
do_test conflict-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c, UNIQUE(a,b));
    INSERT INTO t1 VALUES(1,2,3);
    SELECT c FROM t1 ORDER BY c;
  }
} {3}
do_test conflict-1.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-1.3 {
  catchsql {
    INSERT OR IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-1.4 {
  catchsql {
    INSERT OR REPLACE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-1.5 {
  catchsql {
    INSERT OR ABORT INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-1.6 {
  catchsql {
    INSERT OR IGNORE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-1.7 {
  catchsql {
    INSERT OR REPLACE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 5}
do_test conflict-1.8 {
  catchsql {
    INSERT OR ABORT INTO t1 VALUES(1,2,6);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}

do_test conflict-1.9 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(1,2,11);
    INSERT INTO t2 VALUES(1,2,12);
    INSERT INTO t2 VALUES(1,2,13);
    INSERT INTO t2 VALUES(1,2,14);
    INSERT INTO t2 VALUES(1,3,21);
    INSERT INTO t2 VALUES(1,3,22);
    INSERT INTO t2 VALUES(1,3,23);
    INSERT INTO t2 VALUES(1,3,24);
    COMMIT;
    SELECT count(*) FROM t2;
  }
} 8
do_test conflict-1.10 {
  catchsql {
    INSERT OR IGNORE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {5 21}}
do_test conflict-1.11 {
  catchsql {
    INSERT OR REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

###### Fix me!
do_test conflict-1.12 {
  catchsql {
    INSERT OR REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c DESC;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

do_test conflict-1.13 {
  execsql {
    BEGIN;
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(1,3,4);
    INSERT INTO t1 VALUES(2,3,5);
    COMMIT;
    SELECT * FROM t1 ORDER BY c;
  }
} {1 2 3 1 3 4 2 3 5}
do_test conflict-1.14 {
  catchsql {
    UPDATE OR ABORT t1 SET b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-1.15 {
  catchsql {
    UPDATE t1 SET b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-1.16 {
  catchsql {
    UPDATE OR IGNORE t1 SET b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 2 3 1 3 4 2 3 5}}
do_test conflict-1.17 {
  catchsql {
    UPDATE OR REPLACE t1 SET b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 3 3 2 3 5}}

do_test conflict-2.1 {
  execsql {
    DROP TABLE t1;
    DROP TABLE t2;
    CREATE TABLE t1(a integer primary key, b, c, UNIQUE(a,b));
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1bc ON t1(b,c);
    INSERT INTO t1 VALUES(1,2,3);
    SELECT c FROM t1 ORDER BY c;
  }
} {3}
do_test conflict-2.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-2.3 {
  catchsql { 
    INSERT OR IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-2.4 {
  catchsql {
    INSERT OR REPLACE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-2.5 {
  catchsql {
    INSERT OR ABORT INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-2.6 {
  catchsql {
    INSERT OR IGNORE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-2.7 {
  catchsql {
    INSERT OR REPLACE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 5}
do_test conflict-2.8 {
  catchsql {
    INSERT OR ABORT INTO t1 VALUES(1,2,6);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}

do_test conflict-2.9 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c INTEGER PRIMARY KEY);
    INSERT INTO t2 VALUES(1,2,11);
    INSERT INTO t2 VALUES(1,2,12);
    INSERT INTO t2 VALUES(1,2,13);
    INSERT INTO t2 VALUES(1,2,14);
    INSERT INTO t2 VALUES(2,2,21);
    INSERT INTO t2 VALUES(2,2,22);
    INSERT INTO t2 VALUES(2,2,23);
    INSERT INTO t2 VALUES(2,2,24);
    COMMIT;
    SELECT count(*) FROM t2;
  }
} 8
do_test conflict-2.10 {
  catchsql {
    INSERT OR IGNORE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {5 21}}
do_test conflict-2.11 {
  catchsql {
    INSERT OR REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

###### Fix me!
do_test conflict-2.12 {
  catchsql {
    INSERT OR REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c DESC;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

do_test conflict-2.13 {
  execsql {
    BEGIN;
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(2,3,4);
    INSERT INTO t1 VALUES(3,3,5);
    COMMIT;
    SELECT * FROM t1 ORDER BY c;
  }
} {1 2 3 2 3 4 3 3 5}
do_test conflict-2.14 {
  catchsql {
    UPDATE OR ABORT t1 SET a=2, b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-2.15 {
  catchsql {
    UPDATE t1 SET a=2, b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-2.16 {
  catchsql {
    UPDATE OR IGNORE t1 SET a=2, b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 2 3 2 3 4 3 3 5}}
do_test conflict-2.17 {
  catchsql {
    UPDATE OR REPLACE t1 SET a=2, b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {2 3 3 3 3 5}}


finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.5 2002/02/03 00:56:11 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
  execsql {
    CREATE TABLE t1(a, b, c, UNIQUE(a,b));
    CREATE TABLE t2(x);
    SELECT c FROM t1 ORDER BY c;
  }
} {}

# Six columns of configuration data as follows:
#
#   i      The reference number of the test
#   conf   The conflict resolution algorithm on the BEGIN statement
#   cmd    An INSERT or REPLACE command to execute against table t1
#   t0     True if there is an error from $cmd
#   t1     Content of "c" column of t1 assuming no error in $cmd
#   t2     Content of "x" column of t2
#
foreach {i conf cmd t0 t1 t2} {
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}
  8 IGNORE   INSERT                  0 3   1
  9 IGNORE   {INSERT OR IGNORE}      0 3   1
 10 IGNORE   {INSERT OR REPLACE}     0 4   1
 11 IGNORE   REPLACE                 0 4   1
 12 IGNORE   {INSERT OR FAIL}        1 {}  1
 13 IGNORE   {INSERT OR ABORT}       1 {}  1
 14 IGNORE   {INSERT OR ROLLBACK}    1 {}  {}
 15 REPLACE  INSERT                  0 4   1
 16 FAIL     INSERT                  1 {}  1
 17 ABORT    INSERT                  1 {}  1
 18 ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-1.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
      BEGIN $conf;
      INSERT INTO t2 VALUES(1); 
      $cmd INTO t1 VALUES(1,2,4);
    }]} r1]
    execsql {COMMIT}
    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
    set r2 [execsql {SELECT x FROM t2}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}

# Create tables for the first group of tests.
#
do_test conflict-2.0 {
  execsql {
    DROP TABLE t1;
    DROP TABLE t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b));
    CREATE TABLE t2(x);
    SELECT c FROM t1 ORDER BY c;
  }
} {}

# Six columns of configuration data as follows:
#
#   i      The reference number of the test
#   conf   The conflict resolution algorithm on the BEGIN statement
#   cmd    An INSERT or REPLACE command to execute against table t1
#   t0     True if there is an error from $cmd
#   t1     Content of "c" column of t1 assuming no error in $cmd
#   t2     Content of "x" column of t2
#
foreach {i conf cmd t0 t1 t2} {
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}
  8 IGNORE   INSERT                  0 3   1
  9 IGNORE   {INSERT OR IGNORE}      0 3   1
 10 IGNORE   {INSERT OR REPLACE}     0 4   1
 11 IGNORE   REPLACE                 0 4   1
 12 IGNORE   {INSERT OR FAIL}        1 {}  1
 13 IGNORE   {INSERT OR ABORT}       1 {}  1
 14 IGNORE   {INSERT OR ROLLBACK}    1 {}  {}
 15 REPLACE  INSERT                  0 4   1
 16 FAIL     INSERT                  1 {}  1
 17 ABORT    INSERT                  1 {}  1
 18 ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-2.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
      BEGIN $conf;
      INSERT INTO t2 VALUES(1); 
      $cmd INTO t1 VALUES(1,2,4);
    }]} r1]
    execsql {COMMIT}
    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
    set r2 [execsql {SELECT x FROM t2}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}

# Create tables for the first group of tests.
#
do_test conflict-3.0 {










  execsql {
    DROP TABLE t1;
    DROP TABLE t2;
    CREATE TABLE t1(a, b, c INTEGER PRIMARY KEY, UNIQUE(a,b));
    CREATE TABLE t2(x);
    SELECT c FROM t1 ORDER BY c;
  }
} {}

# Six columns of configuration data as follows:
#
#   i      The reference number of the test
#   conf   The conflict resolution algorithm on the BEGIN statement
#   cmd    An INSERT or REPLACE command to execute against table t1
#   t0     True if there is an error from $cmd
#   t1     Content of "c" column of t1 assuming no error in $cmd
#   t2     Content of "x" column of t2
#
foreach {i conf cmd t0 t1 t2} {
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}
  8 IGNORE   INSERT                  0 3   1
  9 IGNORE   {INSERT OR IGNORE}      0 3   1
 10 IGNORE   {INSERT OR REPLACE}     0 4   1
 11 IGNORE   REPLACE                 0 4   1
 12 IGNORE   {INSERT OR FAIL}        1 {}  1
 13 IGNORE   {INSERT OR ABORT}       1 {}  1
 14 IGNORE   {INSERT OR ROLLBACK}    1 {}  {}
 15 REPLACE  INSERT                  0 4   1
 16 FAIL     INSERT                  1 {}  1
 17 ABORT    INSERT                  1 {}  1
 18 ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-3.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
      BEGIN $conf;
      INSERT INTO t2 VALUES(1); 
      $cmd INTO t1 VALUES(1,2,4);
    }]} r1]
    execsql {COMMIT}
    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
    set r2 [execsql {SELECT x FROM t2}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}

do_test conflict-4.0 {
  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(x);
    SELECT x FROM t2;
  }
} {}

# Six columns of configuration data as follows:
#
#   i      The reference number of the test
#   conf1  The conflict resolution algorithm on the UNIQUE constraint
#   conf2  The conflict resolution algorithm on the BEGIN statement
#   cmd    An INSERT or REPLACE command to execute against table t1
#   t0     True if there is an error from $cmd
#   t1     Content of "c" column of t1 assuming no error in $cmd
#   t2     Content of "x" column of t2
#
foreach {i conf1 conf2 cmd t0 t1 t2} {
  1 {}       {}       INSERT                  1 {}  1
  2 REPLACE  {}       INSERT                  0 4   1
  3 IGNORE   {}       INSERT                  0 3   1
  4 FAIL     {}       INSERT                  1 {}  1
  5 ABORT    {}       INSERT                  1 {}  1
  6 ROLLBACK {}       INSERT                  1 {}  {}
  7 REPLACE  {}       {INSERT OR IGNORE}      0 3   1
  8 IGNORE   {}       {INSERT OR REPLACE}     0 4   1
  9 FAIL     {}       {INSERT OR IGNORE}      0 3   1
 10 ABORT    {}       {INSERT OR REPLACE}     0 4   1
 11 ROLLBACK {}       {INSERT OR IGNORE }     0 3   1
 12 REPLACE  IGNORE   INSERT                  0 4   1
 13 IGNORE   REPLACE  INSERT                  0 3   1
 14 FAIL     IGNORE   INSERT                  1 {}  1
 15 ABORT    REPLACE  INSERT                  1 {}  1
 16 ROLLBACK IGNORE   INSERT                  1 {}  {}
} {
  do_test conflict-4.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
      BEGIN $conf2;
      INSERT INTO t2 VALUES(1); 
      $cmd INTO t1 VALUES(1,2,4);
    }]} r1]
    execsql {COMMIT}
    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
    set r2 [execsql {SELECT x FROM t2}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}

do_test conflict-5.0 {
  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(x);
    SELECT x FROM t2;
  }
} {}

# Six columns of configuration data as follows:
#
#   i      The reference number of the test
#   conf1  The conflict resolution algorithm on the NOT NULL constraint
#   conf2  The conflict resolution algorithm on the BEGIN statement
#   cmd    An INSERT or REPLACE command to execute against table t1
#   t0     True if there is an error from $cmd
#   t1     Content of "c" column of t1 assuming no error in $cmd
#   t2     Content of "x" column of t2
#
foreach {i conf1 conf2 cmd t0 t1 t2} {
  1 {}       {}       INSERT                  1 {}  1
  2 REPLACE  {}       INSERT                  0 5   1
  3 IGNORE   {}       INSERT                  0 {}  1
  4 FAIL     {}       INSERT                  1 {}  1
  5 ABORT    {}       INSERT                  1 {}  1
  6 ROLLBACK {}       INSERT                  1 {}  {}
  7 REPLACE  {}       {INSERT OR IGNORE}      0 {}  1
  8 IGNORE   {}       {INSERT OR REPLACE}     0 5   1
  9 FAIL     {}       {INSERT OR IGNORE}      0 {}  1
 10 ABORT    {}       {INSERT OR REPLACE}     0 5   1
 11 ROLLBACK {}       {INSERT OR IGNORE}      0 {}  1
 12 {}       {}       {INSERT OR IGNORE}      0 {}  1
 13 {}       {}       {INSERT OR REPLACE}     0 5   1
 14 {}       {}       {INSERT OR FAIL}        1 {}  1
 15 {}       {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {}       {INSERT OR ROLLBACK}    1 {}  {}
 17 {}       IGNORE   INSERT                  0 {}  1
 18 {}       REPLACE  INSERT                  0 5   1
 19 {}       FAIL     INSERT                  1 {}  1
 20 {}       ABORT    INSERT                  1 {}  1
 21 {}       ROLLBACK INSERT                  1 {}  {}
 22 REPLACE  FAIL     INSERT                  0 5   1
 23 IGNORE   ROLLBACK INSERT                  0 {}  1
} {
  if {$t0} {set t1 {constraint failed}}
  do_test conflict-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
      DELETE FROM t2;
      BEGIN $conf2;
      INSERT INTO t2 VALUES(1); 
      $cmd INTO t1 VALUES(1,2,NULL);
    }]} r1]
    execsql {COMMIT}
    if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}
    set r2 [execsql {SELECT x FROM t2}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}

do_test conflict-6.0 {
  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(1,2,1);
    INSERT INTO t2 VALUES(2,3,2);
    INSERT INTO t2 VALUES(3,4,1);
    INSERT INTO t2 VALUES(4,5,4);
    SELECT c FROM t2 ORDER BY b;
    CREATE TABLE t3(x);
    INSERT INTO t3 VALUES(1);
  }
} {1 2 1 4}

# Six columns of configuration data as follows:
#
#   i      The reference number of the test
#   conf1  The conflict resolution algorithm on the UNIQUE constraint
#   conf2  The conflict resolution algorithm on the BEGIN statement
#   cmd    An UPDATE command to execute against table t1
#   t0     True if there is an error from $cmd
#   t1     Content of "b" column of t1 assuming no error in $cmd
#   t2     Content of "x" column of t3
#
foreach {i conf1 conf2 cmd t0 t1 t2} {
  1 {}       {}       UPDATE                  1 {6 7 8 9}  1
  2 REPLACE  {}       UPDATE                  0 {7 6 9}    1
  3 IGNORE   {}       UPDATE                  0 {6 7 3 9}  1
  4 FAIL     {}       UPDATE                  1 {6 7 3 4}  1
  5 ABORT    {}       UPDATE                  1 {1 2 3 4}  1
  6 ROLLBACK {}       UPDATE                  1 {1 2 3 4}  0
  7 REPLACE  {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1
  8 IGNORE   {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
  9 FAIL     {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1
 10 ABORT    {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
 11 ROLLBACK {}       {UPDATE OR IGNORE}      0 {6 7 3 9}   1
 12 {}       {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1
 13 {}       {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
 14 {}       {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0
 17 {}       IGNORE   UPDATE                  0 {6 7 3 9}  1
 18 {}       REPLACE  UPDATE                  0 {7 6 9}    1
 19 {}       FAIL     UPDATE                  1 {6 7 3 4}  1
 20 {}       ABORT    UPDATE                  1 {1 2 3 4}  1
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  FAIL     UPDATE                  0 {7 6 9}    1
 23 IGNORE   ROLLBACK UPDATE                  0 {6 7 3 9}  1
} {
  if {$t0} {set t1 {constraint failed}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
      INSERT INTO t1 SELECT * FROM t2;
      UPDATE t3 SET x=0;
      BEGIN $conf2;
      $cmd t3 SET x=1;
      $cmd t1 SET a=c+5;
    }]} r1]
    execsql {COMMIT}
    if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
    set r2 [execsql {SELECT x FROM t3}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}


finish_test

#
# Run this Tcl script to generate the constraint.html file.
#
set rcsid {$Id: conflict.tcl,v 1.1 2002/01/30 16:17:25 drh Exp $ }

puts {<html>
<head>
  <title>Constraint Conflict Resolution in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
puts {
<h2>Introduction</h2>

<p>
In most SQL databases, if you have a UNIQUE constraint on
a table and you try to do an UPDATE or INSERT that violates
that constraint, the database will aborts the operation in
progress and rolls back the current transaction.

This is the default behavior of SQLite.
Beginning with version 2.3.0, though, SQLite allows you to
define alternative ways for dealing with constraint violations.
This article describes those alternatives and how to use them.
</p>

<h2>Conflict Resolution Algorithms</h2>

<p>
The default conflict resolution algorithm is to abort the
operation in progress, rollback all changes, and cancel the
current transaction.  Call this algorithm "ABORT".  Abort
is the standard way of dealing with a constraint error
in most SQL databases.
</p>

<p>




Sometimes ABORT is not the most helpful way of dealing
with constraint violations.  Suppose, for example, you are
inserting 1000 records into a database, all within a single
transaction, but one of those records is malformed and causes

a constraint error.  With the default ABORT behavior, none
of the 1000 records gets inserted.  But sometimes it is 
desirable to just omit the single malformed insert and
finish the other 999.
</p>


<p>
SQLite defines two addition conflict resolution algorithms
called "IGNORE" and "REPLACE".  
If you are trying to do multiple INSERTs or UPDATEs when a constraint
fails for a single row and the conflict behavior is IGNORE, then
that row remains uninserted or unmodified.  But the overall operation
is not aborted and no rollback occurs.  If a constraint
fails and the behavior is REPLACE, then SQLite tries to
delete other rows in the table in order to eliminate the
constraint problem.  Again, the overall operation continues
and no rollback occurs.
</p>

<p>




The default conflict resolution algorithm is always ABORT
but you can specify an alternative algorithm using special
(non-standard) syntax on the INSERT and UPDATE commands.
You can add the clause "ON CONFLICT <algorithm>" immediately
after the "INSERT" or "UPDATE" keywords to specify the 
conflict resolution algorithm to use for that one operation.
(Substitute "ABORT", "IGNORE", or "REPLACE" for <algorithm>,
of course.)
</p>

Consider this example:

<blockquote><pre>
   BEGIN;
   CREATE TABLE t1(
      a INTEGER,
      b INTEGER,
      c INTEGER,
      UNIQUE(a,b)
   );
   INSERT INTO a VALUES(1,2,3);
   COMMIT;

   BEGIN;
   INSERT INTO a VALUES(2,3,4);
   INSERT INTO a VALUES(1,2,5);
</pre></blockquote>

<p>
In the last instruction, the UNIQUE constraint fails
and the entire transaction is rolled back.  The database
now contains a single entry: {1,2,3}.  





</p>

<blockquote><pre>
   BEGIN;
   INSERT ON CONFLICT IGNORE INTO a VALUES(2,3,4);
   INSERT ON CONFLICT IGNORE INTO a VALUES(1,2,5);
   COMMIT;
</pre></blockquote>


<p>This time the "ON CONFLICT IGNORE" clause tells SQLite to use
IGNORE semantics when a constraint fails.  The second
INSERT statement fails, but the database is
not rolled back and there is no failure.  The database
now contains two rows:  {1,2,3} and {2,3,4}.</p>

<blockquote><pre>
   BEGIN;
   INSERT ON CONFLICT REPLACE INTO a VALUES(1,2,5);
   COMMIT;
</pre></blockquote>

<p>Here the "ON CONFLICT REPLACE" clause tells SQLite to use REPLACE
semantics.  The {1,2,3} is deleted when the {1,2,5} row
is inserted in order to satisfy the constraint.  After
the above, the database contains {1,2,5} and {2,3,4}.</p>

<h2>A Syntactic Shortcut</h2>

<p>On an INSERT, the "ON CONFLICT" keywords may be omitted for brevity.
So you can say</p>

<blockquote><pre>
   INSERT IGNORE INTO a VALUES(1,2,5);
</pre></blockquote>

<p>Instead of the more wordy:</p>

<blockquote><pre>
   INSERT ON CONFLICT IGNORE INTO a VALUES(1,2,5);
</pre></blockquote>

<p>Unfortunately, you cannot do this with an UPDATE.</p>

<h2>Changing The Default Conflict Resolution Algorithm</h2>

<p>You can change the default conflict resolution algorithm
on a constraint-by-constraint basis using special (non-standard)
syntax in CREATE TABLE and CREATE INDEX statements.  The
same "ON CONFLICT" clause that appears in INSERT and UPDATE
statements is used but the clause is attached to the constraint
in the CREATE TABLE statement.  Like this:

<blockquote><pre>
   CREATE TABLE t1 (
     a INTEGER,
     b INTEGER,
     c INTEGER,
     UNIQUE(a,b) ON CONFLICT REPLACE
   );
</pre></blockquote>

<p>The ON CONFLICT clause in the above table definition says that


the default conflict resolution algorithm is REPLACE instead
of ABORT.  REPLACE will always be used unless you override
this by saying "INSERT IGNORE" or "INSERT ABORT".</p>





<p>The ON CONFLICT clause can also appear on a NOT NULL constraint,
a PRIMARY KEY constraint, and a CHECK constraint.
(Note, however, that CHECK constraints are not currently enforced
so the ON CONFLICT clause has no effect there.)</p>












<p>A NOT NULL constraint will normally ABORT if you try to insert
a NULL.  But if you substitute the REPLACE algorithm, it tries to insert
the default value in place of the NULL.  If there is no default value,
then REPLACE is the same as ABORT for NOT NULL constraints.
With the IGNORE algorithm on a NOT NULL, the INSERT or UPDATE 
is suppressed if the value is NULL.</p>

<h2>Portability</h2>

<p>The ON CONFLICT syntax is not standard SQL and will not
(as far as is known) work on any other database product.  Furthermore,
the syntax might change in future versions of SQLite.  So use it
with appropriate discretion.</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the constraint.html file.
#
set rcsid {$Id: conflict.tcl,v 1.2 2002/02/03 00:56:11 drh Exp $ }

puts {<html>
<head>
  <title>Constraint Conflict Resolution in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
puts {
<h2>Introduction</h2>

<p>
In most SQL databases, if you have a UNIQUE constraint on
a table and you try to do an UPDATE or INSERT that violates
that constraint, the database will aborts the operation in
progress, back out any prior changes associated with that 
one UPDATE or INSERT command, and return an error.
This is the default behavior of SQLite.
Beginning with version 2.3.0, though, SQLite allows you to
define alternative ways for dealing with constraint violations.
This article describes those alternatives and how to use them.
</p>

<h2>Conflict Resolution Algorithms</h2>

<p>
SQLite defines five constraint conflict resolution algorithms
as follows:



</p>


<dl>
<dt><b>ROLLBACK</b></dt>
<dd><p>When a constraint violation occurs, an immediate ROLLBACK
occurs, thus ending the current transaction, and the command aborts
with a return code of SQLITE_CONSTRAINT.  If no transaction is
active (other than the implied transaction that is created on every
command) then this algorithm works the same as ABORT.</p></dd>


<dt><b>ABORT</b></dt>
<dd><p>When a constraint violation occurs, the command backs out
any prior changes it might have made and aborts with a return code
of SQLITE_CONSTRAINT.  But no ROLLBACK is executed so changes
from prior commands within the same transaction
are preserved.  This is the default behavior for SQLite.</p></dd>


<dt><b>FAIL</b></dt>
<dd><p>When a constraint violation occurs, the command aborts with a
return code SQLITE_CONSTRAINT.  But any changes to the database that
the command made prior to encountering the constraint violation
are preserved and are not backed out.  For example, if an UPDATE
statement encountered a constraint violation on the 100th row that
it attempts to update, then the first 99 row changes are preserved
by change to rows 100 and beyond never occur.</p></dd>





<dt><b>IGNORE</b></dt>
<dd><p>When a constraint violation occurs, the one row that contains
the constraint violation is not inserted or changed.  But the command
continues executing normally.  Other rows before and after the row that
contained the constraint violation continue to be inserted or updated
normally.  No error is returned.</p></dd>






























<dt><b>REPLACE</b></dt>
<dd><p>When a UNIQUE constraint violation occurs, the pre-existing row
that caused the constraint violation is removed prior to inserting
or updating the current row.  Thus the insert or update always occurs.
The command continues executing normally.  No error is returned.</p></dd>
</dl>







<h2>Why So Many Choices?</h2>





































<p>SQLite provides multiple conflict resolution algorithms for a
















couple of reasons.  First, SQLite tries to be roughly compatible with as
many other SQL databases as possible, but different SQL database
engines exhibit different conflict resolution strategies.  For


example, PostgreSQL always uses ROLLBACK, Oracle always uses ABORT, and
MySQL usually uses FAIL but can be instructed to use IGNORE or REPLACE.
By supporting all five alternatives, SQLite provides maximum
portability.</p>





<p>Another reason for supporing multiple algorithms is that sometimes
it is useful to use an algorithm other than the default.
Suppose, for example, you are
inserting 1000 records into a database, all within a single
transaction, but one of those records is malformed and causes
a constraint error.  Under PostgreSQL or Oracle, none of the
1000 records would get inserted.  In MySQL, some subset of the
records that appeared before the malformed record would be inserted
but the rest would not.  Neither behavior is espeically helpful.
What you really want is to use the IGNORE algorithm to insert
all but the malformed record.</p>







}








puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.20 2002/01/31 15:54:23 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}

  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
................................................................................
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
}

proc Syntax {args} {
  puts {<table cellpadding="15">}
  foreach {rule body} $args {
    puts "<tr><td align=\"right\" valign=\"top\">"
    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
    regsub -all < $body {%LT} body
    regsub -all > $body {%GT} body
    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
................................................................................
proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}

Section {BEGIN TRANSACTION} createindex

Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.  However, only a single level of
transaction is allowed.  Transactions may not be nested.
</p>

<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically starts a transaction if
one is not already in effect.  Automatically stared transactions
are committed at the conclusion of the command.
</p>

<p>
Transactions can be started manually using the BEGIN TRANSACTION
command. Such transactions persist until a COMMIT or ROLLBACK
or until an error occurs or the database is closed.  If an
error is encountered or the database is closed, the transaction
is automatically rolled back.  The END TRANSACTION command is
a alias for COMMIT.


</p>
}

Section {ON CONFLICT clause} conflict


Syntax {conflict-clause} {
ON CONFLICT <algorithm>
} {algorithm} {
ABORT | IGNORE | REPLACE
}



puts {
<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The ON CONFLICT clause specifies an algorithm used to resolve
constraint conflicts.  The default algorithm is ABORT.  When the
ABORT algorithm is in use, any constraint violation causes the
command to abort and the current transaction to be rolled back.
This is the only behavior exhibited by most SQL engines.  But
SQLite allows two alternative behaviors: IGNORE and REPLACE.
The IGNORE algorithm means that when a constraint violation occurs
on a COPY, INSERT or UPDATE, the particular row that caused the constraint
violation is not inserted or changed, but other rows effected by the
COPY, INSERT, or UPDATE are insert or changed as usual.
The command is not aborted and no rollback occurs.
If the algorithm is REPLACE, then SQLite tries to
delete preexisting rows from the table to remove the constraint
violation before inserting or changing the row.</p>

<p>For additional information, see 
<a href="conflict.html">conflict.html</a>.</p>
}

Section COPY copy

Syntax {sql-statement} {
COPY [ <conflict-clause> ] <table-name> FROM <filename>
[ USING DELIMITERS <delim> ]
}


puts {
<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
................................................................................
puts "\"[Operator \\.]\".</p>"

Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
[ <conflict-clause> ]
} {column-name} {
<name> [ ASC | DESC ]
}


puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
................................................................................
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) [ <conflict-clause> ]|
UNIQUE ( <name> [, <name>]* ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]


}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  The only reserved table name is "<b>sqlite_master</b>" which
................................................................................
"<b>count(distinct</b> <i>COLUMN-NAME</i><b>)</b>" is not.
</p>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT [ <conflict-clause> ] INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT [ <conflict-clause> ] INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
................................................................................
for every row of the SELECT result.  The SELECT may be simple
or compound.  If the SELECT statement has an ORDER BY clause,
the ORDER BY is ignored.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>
































































































































































































}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
................................................................................
removing the results of the right SELECT.  When three are more SELECTs
are connected into a compound, they group from left to right.</p>
}

Section UPDATE update

Syntax {sql-statement} {
UPDATE [ <conflict-clause> ] <table-name>
SET <assignment> [, <assignment>] 
[WHERE <expression>]
} {assignment} {
<column-name> = <expression>
}

puts {
................................................................................
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.
Beginning with version 2.0 of SQLite, GDBM is no longer used for
the database backend and VACUUM has become a no-op.
</p>
}

Section PRAGMA pragma

Syntax {sql-statement} {
PRAGMA <name> = <value> |
PRAGMA <function>(<arg>)
}

puts {
<p>The PRAGMA command is used to modify the operation of the SQLite library.
The pragma command is experimental and specific pragma statements may
removed or added in future releases of SQLite.  Use this command
with caution.</p>

<p>The current implementation supports the following pragmas:</p>

<ul>
<li><p><b>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of RAM.
    The default cache size is 100.  If you are doing UPDATEs or DELETEs
    that change many rows of a database and you do not mind if SQLite
    uses more memory, you can increase the cache size for a possible speed
    improvement.</p></li>

<li><p><b>PRAGMA count_changes = ON;
       <br>PRAGMA count_changes = OFF;</b></p>
    <p>When on, the COUNT_CHANGES pragma causes the callback function to
    be invoked once for each DELETE, INSERT, or UPDATE operation.  The
    argument is the number of rows that were changed.</p>

<li><p><b>PRAGMA empty_result_callbacks = ON;
       <br>PRAGMA empty_result_callbacks = OFF;</b></p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p>

<li><p><b>PRAGMA full_column_names = ON;
       <br>PRAGMA full_column_names = OFF;</b></p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>

<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p>

<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
    <p>For each index on the named table, invoke the callback function
    once with information about that index.  Arguments include the
    index name and a flag to indicate whether or not the index must be
    unique.</p>

<li><p><b>PRAGMA parser_trace = ON;<br>PRAGMA parser_trace = OFF;</b></p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>

<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.</p>

<li><p><b>PRAGMA vdbe_trace = ON;<br>PRAGMA vdbe_trace = OFF;</b></p>
    <p>Turn tracing of the virtual database engine inside of the
    SQLite library on and off.  This is used for debugging.</p></li>
</ul>

<p>No error message is generated if an unknown pragma is issued.
Unknown pragmas are ignored.</p>
}

puts {
<p></p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.21 2002/02/03 00:56:11 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {REPLACE replace}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
................................................................................
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
}

proc Syntax {args} {
  puts {<table cellpadding="10">}
  foreach {rule body} $args {
    puts "<tr><td align=\"right\" valign=\"top\">"
    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
    regsub -all < $body {%LT} body
    regsub -all > $body {%GT} body
    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
................................................................................
proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}

Section {BEGIN TRANSACTION} createindex

Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]] [ON CONFLICT <conflict-algorithm>]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.</p>



<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically starts a transaction if
one is not already in effect.  Automatically stared transactions
are committed at the conclusion of the command.
</p>

<p>
Transactions can be started manually using the BEGIN
command. Such transactions usually persist until the next
COMMIT or ROLLBACK command. But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documention on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>




<p>
The optional ON CONFLICT clause at the end of a BEGIN statement
can be used to changed the default conflict resolution algorithm.
The normal default is ABORT.  If an alternative is specified by
the ON CONFLICT clause of a BEGIN, then that alternative is used


as the default for all commands within the transaction.  The default
algorithm is overridden by ON CONFLICT clauses on individual
constraints within the CREATE TABLE or CREATE INDEX statements
and by the OR clauses on COPY, INSERT, and UPDATE commands.
</p>


}



















Section COPY copy

Syntax {sql-statement} {
COPY [ OR <conflict-algorithm> ] <table-name> FROM <filename>
[ USING DELIMITERS <delim> ]
}


puts {
<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
................................................................................
puts "\"[Operator \\.]\".</p>"

Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ ASC | DESC ]
}


puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
................................................................................
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) [ <conflict-clause> ]|
UNIQUE ( <name> [, <name>]* ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  The only reserved table name is "<b>sqlite_master</b>" which
................................................................................
"<b>count(distinct</b> <i>COLUMN-NAME</i><b>)</b>" is not.
</p>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
................................................................................
for every row of the SELECT result.  The SELECT may be simple
or compound.  If the SELECT statement has an ORDER BY clause,
the ORDER BY is ignored.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.
For compatibility with MySQL, the parser allows the use of the
single keyword "REPLACE" as an alias for "INSERT OR REPLACE".
</p>
}

Section {ON CONFLICT clause} conflict

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}

puts {
<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE, CREATE INDEX, and BEGIN TRANSACTION commands.
For the COPY, INSERT, and UPDATE commands, the keywords
"ON CONFLICT" are replaced by "OR", to make the syntax seem more
natural.  But the meaning of the clause is the same either way.</p>

<p>The ON CONFLICT clause specifies an algorithm used to resolve
constraint conflicts.  There are five choices: ROLLBACK, ABORT,
FAIL, IGNORE, and REPLACE. The default algorithm is ABORT.  This
is what they mean:</p>

<dl>
<dt><b>ROLLBACK</b></dt>
<dd><p>When a constraint violation occurs, an immediate ROLLBACK
occurs, thus ending the current transaction, and the command aborts
with a return code of SQLITE_CONSTRAINT.  If no transaction is
active (other than the implied transaction that is created on every
command) then this algorithm works the same as ABORT.</p></dd>

<dt><b>ABORT</b></dt>
<dd><p>When a constraint violation occurs, the command backs out
any prior changes it might have made and aborts with a return code
of SQLITE_CONSTRAINT.  But no ROLLBACK is executed so changes
from prior commands within the same transaction
are preserved.  This is the default behavior.</p></dd>

<dt><b>FAIL</b></dt>
<dd><p>When a constraint violation occurs, the command aborts with a
return code SQLITE_CONSTRAINT.  But any changes to the database that
the command made prior to encountering the constraint violation
are preserved and are not backed out.  For example, if an UPDATE
statement encountered a constraint violation on the 100th row that
it attempts to update, then the first 99 row changes are preserved
by change to rows 100 and beyond never occur.</p></dd>

<dt><b>IGNORE</b></dt>
<dd><p>When a constraint violation occurs, the one row that contains
the constraint violation is not inserted or changed.  But the command
continues executing normally.  Other rows before and after the row that
contained the constraint violation continue to be inserted or updated
normally.  No error is returned.</p></dd>

<dt><b>REPLACE</b></dt>
<dd><p>When a UNIQUE constraint violation occurs, the pre-existing row
that is causing the constraint violation is removed prior to inserting
or updating the current row.  Thus the insert or update always occurs.
The command continues executing normally.  No error is returned.</p></dd>
</dl>

<p>
The conflict resolution algorithm can be specified in three places,
in order from lowest to highest precedence:
</p>

<ol>
<li><p>
On a BEGIN TRANSACTION command.
</p></li>

<li><p>
On individual constraints within a CREATE TABLE or CREATE INDEX
statement.
</p></li>

<li><p>
In the OR clause of a COPY, INSERT, or UPDATE command.
</p></li>
</ol>

<p>The algorithm specified in the OR clause of a COPY, INSERT, or UPDATE
overrides any algorithm specified by a CREATE TABLE or CREATE INDEX.
The algorithm specified within a CREATE TABLE or CREATE INDEX will, in turn,
override the algorithm specified by a BEGIN TRANSACTION command.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

}
# <p>For additional information, see 
# <a href="conflict.html">conflict.html</a>.</p>


Section PRAGMA pragma

Syntax {sql-statement} {
PRAGMA <name> = <value> |
PRAGMA <function>(<arg>)
}

puts {
<p>The PRAGMA command is used to modify the operation of the SQLite library.
The pragma command is experimental and specific pragma statements may
removed or added in future releases of SQLite.  Use this command
with caution.</p>

<p>The current implementation supports the following pragmas:</p>

<ul>
<li><p><b>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of RAM.
    The default cache size is 100.  If you are doing UPDATEs or DELETEs
    that change many rows of a database and you do not mind if SQLite
    uses more memory, you can increase the cache size for a possible speed
    improvement.</p></li>

<li><p><b>PRAGMA count_changes = ON;
       <br>PRAGMA count_changes = OFF;</b></p>
    <p>When on, the COUNT_CHANGES pragma causes the callback function to
    be invoked once for each DELETE, INSERT, or UPDATE operation.  The
    argument is the number of rows that were changed.</p>

<li><p><b>PRAGMA empty_result_callbacks = ON;
       <br>PRAGMA empty_result_callbacks = OFF;</b></p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p>

<li><p><b>PRAGMA full_column_names = ON;
       <br>PRAGMA full_column_names = OFF;</b></p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>

<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p>

<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
    <p>For each index on the named table, invoke the callback function
    once with information about that index.  Arguments include the
    index name and a flag to indicate whether or not the index must be
    unique.</p>

<li><p><b>PRAGMA parser_trace = ON;<br>PRAGMA parser_trace = OFF;</b></p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>

<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.</p>

<li><p><b>PRAGMA vdbe_trace = ON;<br>PRAGMA vdbe_trace = OFF;</b></p>
    <p>Turn tracing of the virtual database engine inside of the
    SQLite library on and off.  This is used for debugging.</p></li>
</ul>

<p>No error message is generated if an unknown pragma is issued.
Unknown pragmas are ignored.</p>
}

Section REPLACE replace

Syntax {sql-statement} {
REPLACE INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
REPLACE INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant
of the <a href="#insert">INSERT command</a>.  This alias is provided for
compatibility with MySQL.  See the 
<a href="#insert">INSERT command</a> documentation for additional
information.</p>  
}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
................................................................................
removing the results of the right SELECT.  When three are more SELECTs
are connected into a compound, they group from left to right.</p>
}

Section UPDATE update

Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] <table-name>
SET <assignment> [, <assignment>] 
[WHERE <expression>]
} {assignment} {
<column-name> = <expression>
}

puts {
................................................................................
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.
Beginning with version 2.0 of SQLite, GDBM is no longer used for
the database backend and VACUUM has become a no-op.
</p>
}








puts {













































































<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}