SQLite4  Check-in [365eb3c6de]

  KVMem *p = (KVMem*)pKVStore;
  assert( p->iMagicKVMemBase==SQLITE_KVMEMBASE_MAGIC );
  assert( iLevel>=0 );
  assert( iLevel<p->base.iTransLevel );
  assertUpPointers(p->pRoot);
  while( p->base.iTransLevel>iLevel && p->base.iTransLevel>1 ){
    KVMemChng *pChng, *pNext;

    if( iLevel<2 ){
      for(pChng=p->apLog[p->base.iTransLevel-2]; pChng; pChng=pNext){
        KVMemNode *pNode = pChng->pNode;
        if( pNode->pData ){
          pNode->mxTrans = pChng->oldTrans;
        }else{
          kvmemRemoveNode(p, pNode);
        }
        kvmemDataUnref(pChng->pData);
        pNext = pChng->pNext;
        sqlite4_free(pChng);
      }
    }else{
      KVMemChng **pp;
      int iFrom = p->base.iTransLevel-2;
      int iTo = p->base.iTransLevel-3;
      assert( iTo>=0 );

      for(pp=&p->apLog[iFrom]; *pp; pp=&((*pp)->pNext)){
        assert( (*pp)->pNode->mxTrans==p->base.iTransLevel 
             || (*pp)->pNode->mxTrans==(p->base.iTransLevel-1)
        );
        (*pp)->pNode->mxTrans = p->base.iTransLevel - 1;
      }
      *pp = p->apLog[iTo];
      p->apLog[iTo] = p->apLog[iFrom];
    }

    p->apLog[p->base.iTransLevel-2] = 0;
    p->base.iTransLevel--;
  }
  assertUpPointers(p->pRoot);
  p->base.iTransLevel = iLevel;
  return SQLITE_OK;
}

  /* Open every index that needs updating. If any index could potentially 
  ** invoke a REPLACE conflict resolution action, then we need to open all 
  ** indices because we might need to be deleting some records.  */
  if( !isView ){
    /* Set bOpenAll to true if this UPDATE might strike a REPLACE */
    bOpenAll = (onError==OE_Replace);
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( aRegIdx[i] && pIdx->onError==OE_Replace ) bOpenAll = 1;
    }

    /* If bOpenAll is true, open all indexes. Otherwise, just open those
    ** indexes for which the corresponding aRegIdx[] entry is non-zero
    ** (those that index columns that will be modified by this UPDATE
    ** statement). Also, if the one-pass approach is being used, do not

    }

    /* Delete the index entries associated with the current record.  */
    j1 = sqlite4VdbeAddOp4(v, OP_NotFound, iCur+iPk, 0, regOldKey, 0, P4_INT32);
    sqlite4GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* Delete the old record */
    if( hasFK || bChngPk ){
      sqlite4VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite4VdbeJumpHere(v, j1);

    if( hasFK ){
      sqlite4FkCheck(pParse, pTab, 0, regNewRowid);
    }

  int bAutoCommit;                /* New value for auto-commit flag */
  int bRollback;                  /* True to do transaction rollback */

  bAutoCommit = pOp->p1;
  bRollback = pOp->p2;
  assert( bAutoCommit==1 || bRollback==0 );

  if( bAutoCommit==db->autoCommit ){
    /* This branch is taken if the user is trying to BEGIN a transaction
    ** when one is already open, or trying to commit or rollback a transaction
    ** when none is open. Return a suitable error message.  */
    const char *zErr;
    if( bAutoCommit==0 ){
      zErr = "cannot start a transaction within a transaction";
    }else if( bRollback ){
      zErr = "cannot rollback - no transaction is active";
    }else{
      zErr = "cannot commit - no transaction is active";
    }
    sqlite4SetString(&p->zErrMsg, db, zErr);
    rc = SQLITE_ERROR;
  }

  else if( bAutoCommit==0 ){
    db->autoCommit = 0;
  }else{
    if( bRollback ){
      sqlite4RollbackAll(db);
    }else if( sqlite4VdbeCheckFk(p, 1) ){
      goto vdbe_return;
    }

    db->autoCommit = 1;
    sqlite4VdbeHalt(p);
  }


  break;
}

/* Opcode: Transaction P1 P2 * * *
**
** Begin a transaction.
**

  nShort = sqlite4VdbeShortKey(pProbe->z, pProbe->n, 
      pC->pKeyInfo->nField - pC->pKeyInfo->nPK
  );
  assert( nShort<=pProbe->n );
  assert( (nShort==pProbe->n)==(pC->pKeyInfo->nPK==0) );

  dir = (pC->pKeyInfo->nPK==0 ? 0 : 1);
  rc = sqlite4KVCursorSeek(pC->pKVCur, pProbe->z, nShort, dir);

  if( rc==SQLITE_NOTFOUND ){
    rc = SQLITE_OK;
    pc = pOp->p2-1;
  }else if( rc==SQLITE_INEXACT ){
    assert( nShort<pProbe->n );

          assert( db->nDeferredCons<=0 );
          sqlite4CommitInternalChanges(db);
        }
      }

      if( eAction ){
        sqlite4RollbackAll(db);
        db->autoCommit = 1;
      }

      db->nDeferredCons = 0;
    }else if( p->stmtTransMask ){
      /* Auto-commit mode is turned off and no "OR ROLLBACK" constraint was
      ** encountered. So either commit (if eAction==0) or rollback (if 
      ** eAction==1) any statement transactions opened by this VM.  */

        break;

      case 0x22:                  /* Large positive number */
      case 0x16:                  /* Small positive number */
      case 0x14:                  /* Small negative number */
      case 0x08:                  /* Large negative number */
        p += sqlite4GetVarint64(p, pEnd-p, &dummy);
        while( (*p++) & 0x01 );
        break;

      default:                    /* Medium sized number */
        while( (*p++) & 0x01 );
        break;
    }
  }

  return (p - aKey);
}

  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
} {
  do_test conflict-4.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    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;
      INSERT INTO t2 VALUES(1); 
    }]
    set r0 [catch {execsql "$cmd INTO t1 VALUES(1,2,4)"} r1]

    catch {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]
}

} {
  if {$t0} {set t1 {column a is not unique}}
  if {[info exists TEMP_STORE] && $TEMP_STORE==3} {
    set t3 0
  } else {
    set t3 [expr {$t3+$t4}]
  }

  # Update for SQLite 4: No temporary files ever.
  set t3 0

  do_test conflict-6.$i {


    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    execsql {pragma temp_store=file}
    set ::sqlite_opentemp_count 0
    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;
      $cmd t3 SET x=1;
      $cmd t1 SET b=b*2;
      $cmd t1 SET a=c+5;
    }]} r1]
    catch {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 $::sqlite_opentemp_count
  } [list $t0 $t1 $t2 $t3]
}


# Test to make sure a lot of IGNOREs don't cause a stack overflow
#
do_test conflict-7.1 {
  execsql {
    DROP TABLE t1;
    DROP TABLE t2;

  }
} {1}
do_test conflict-7.2.1 {
  db changes
} {1}
do_test conflict-7.3 {
  execsql {
    SELECT b FROM t1 WHERE +a=1000;
  }
} {2}
do_test conflict-7.4 {
  execsql {
    SELECT count(*) FROM t1;
  }
} {50}
do_test conflict-7.5 {
  execsql {
    PRAGMA count_changes=on;
    UPDATE OR REPLACE t1 SET a=1001;
  }
} {50}
do_test conflict-7.5.1 {
  db changes
} {50}
do_test conflict-7.6 {
  execsql {
    SELECT b FROM t1 WHERE +a=1001;
  }
} {51}
do_test conflict-7.7 {
  execsql {
    SELECT count(*) FROM t1;
  }
} {1}

  catchsql {
    INSERT INTO t2 VALUES(1,3,3,3,3);
    SELECT * FROM t2;
  }
} {0 {1 1 1 1 1 2 2 2 2 2}}
do_test conflict-9.4 {
  catchsql {
    UPDATE t2 SET a=a+1 WHERE +a=1;
    SELECT * FROM t2;
  }
} {0 {1 1 1 1 1 2 2 2 2 2}}
do_test conflict-9.5 {
  catchsql {
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.6 {
  catchsql {
    UPDATE t2 SET b=b+1 WHERE +b=1;
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.7 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.8 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {2}
do_test conflict-9.9 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET b=b+1 WHERE +b=1;
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.10 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {3}
do_test conflict-9.11 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.12 {
  catchsql {
    UPDATE t2 SET d=d+1 WHERE +d=1;
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.13 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.14 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {4}
do_test conflict-9.15 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET d=d+1 WHERE +d=1;
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.16 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.17 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.18 {
  catchsql {
    UPDATE t2 SET e=e+1 WHERE +e=1;
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.19 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.20 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.21 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET e=e+1 WHERE +e=1;
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.22 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.23 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,1,3,3);
    SELECT * FROM t2;
  }
} {0 {2 2 2 2 2 3 3 1 3 3}}
do_test conflict-9.24 {
  catchsql {
    UPDATE t2 SET c=c-1 WHERE +c=2;
    SELECT * FROM t2;
  }
} {0 {2 2 1 2 2}}
do_test conflict-9.25 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;

#
do_test conflict-11.1 {
  execsql {
    -- Create a database object (pages 2, 3 of the file)
    BEGIN;
      CREATE TABLE abc(a UNIQUE, b, c);
      INSERT INTO abc VALUES(1, 2, 3);
      INSert into abc VALUES(4, 5, 6);
      insERT INTO abc VALUES(7, 8, 9);
    COMMIT;
  }

  
  # Set a small cache size so that changes will spill into
  # the database file.  
  execsql {
    PRAGMA cache_size = 10;
  }
  

      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      DELETE FROM abc WHERE +a = 4;
  }

  # Execute a statement that does a statement rollback due to
  # a constraint failure.
  #
  catchsql {
    INSERT INTO abc SELECT 10, 20, 30 FROM def;

      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      DELETE FROM abc WHERE +a = 4;
  }
  catchsql {
    INSERT INTO abc SELECT 10, 20, 30 FROM def;
  }
  execsql {
    ROLLBACK;
    SELECT * FROM abc;
  }
} {1 2 3 4 5 6 7 8 9}

# Repeat test conflict-11.1 but this time commit.
#
do_test conflict-11.5 {
  execsql {
    BEGIN;
      -- Make sure the pager is in EXCLUSIVE state.
      CREATE TABLE def(d, e, f);
      INSERT INTO def VALUES
          ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      INSERT INTO def SELECT * FROM def;
      DELETE FROM abc WHERE +a = 4;
  }
  catchsql {
    INSERT INTO abc SELECT 10, 20, 30 FROM def;
  }
  execsql {
    COMMIT;
    SELECT * FROM abc;

    SELECT * FROM t5;
  }
} {1 one 2 two}
do_test conflict-12.3 {
  catchsql {
    UPDATE t5 SET a=a+1 WHERE a=1;
  }
} {1 {column a is not unique}}
do_test conflict-12.4 {
  execsql {
    UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
    SELECT * FROM t5;
  }
} {2 one}


# Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437]
# REPLACE works like ABORT on a CHECK constraint.
#
do_test conflict-13.1 {
  execsql {
    CREATE TABLE t13(a CHECK(a!=2));

do_test conflict-13.2 {
  execsql {
    REPLACE INTO t13 VALUES(3);
    COMMIT;
    SELECT * FROM t13;
  }
} {1 3}


finish_test

#   quick
#   full
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files [
  test_set simple.test fkey1.test conflict.test
]

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [

}

do_catchsql_test 17.2 { 
  INSERT INTO t1 SELECT b FROM t2;
} {1 {column x is not unique}}

do_execsql_test 17.3 { SELECT * FROM t1 } {X}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 18.1 {
  CREATE TABLE t1(a, b, c, UNIQUE(a,b) ON CONFLICT IGNORE);
  CREATE TABLE t2(x);

  INSERT INTO t1 VALUES(1,2,3);
  BEGIN;
    INSERT INTO t2 VALUES(1); 
    INSERT INTO t1 VALUES(1,2,4);
  COMMIT;
}

do_execsql_test 18.2 { SELECT * FROM t1 } {1 2 3}
do_execsql_test 18.3 { SELECT * FROM t2 } {1}

#-------------------------------------------------------------------------
reset_db

do_test 19.1 {
  catchsql {
    CREATE TABLE t4(x);
    CREATE UNIQUE INDEX t4x ON t4(x);
    BEGIN;
    INSERT INTO t4 VALUES(1);
    INSERT OR ROLLBACK INTO t4 VALUES(1);
  }
  execsql { SELECT * FROM t4 }
} {}

# Check the above closed the transaction.
do_execsql_test 19.2 { BEGIN }
do_execsql_test 19.3 { COMMIT }

#-------------------------------------------------------------------------
reset_db

do_execsql_test 20.1 {
  CREATE TABLE def(d, e, f);
  BEGIN;
  INSERT INTO def VALUES('a', 'b', 'c');

  INSERT INTO def SELECT * FROM def;
  INSERT INTO def SELECT * FROM def;
  INSERT INTO def SELECT * FROM def;
  INSERT INTO def SELECT * FROM def;
  INSERT INTO def SELECT * FROM def;

  SELECT count(*) FROM def;
} {32}

do_execsql_test 20.2 { ROLLBACK }
do_execsql_test 20.3 { SELECT count(*) FROM def } 0


#execsql {PRAGMA  kvdump }

finish_test

#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');

#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all the tests run by quick.test except for those related
# to malloc or IO error simulation. With these tests omitted, the overall
# run time is reduced by about 75%.
#
# $Id: veryquick.test,v 1.9 2008/07/12 14:52:21 drh Exp $

set testdir [file dirname $argv0]
source $testdir/permutations.test

run_test_suite src4

finish_test