SQLite

/*
** 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.
**
*************************************************************************
** $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;
}


/*
** Commit a checkpoint to transaction currently in progress.  If no
** checkpoint is active, this is a no-op.
*/
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.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.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.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.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.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.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>
Constraint Conflict Resolution in SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

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.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}
  {PRAGMA pragma}
  {{ON CONFLICT clause} conflict}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
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>}