Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -986,11 +986,11 @@ #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); sqlite3VtabMakeWritable(pParse, pTab); sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB); - sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError); + sqlite3VdbeChangeP5(v, onError==OE_Default ? db->dfltOnError : onError); sqlite3MayAbort(pParse); }else #endif { int isReplace; /* Set to true if constraints may cause a replace */ @@ -1280,14 +1280,14 @@ onError = pTab->aCol[i].notNull; if( onError==OE_None ) continue; /* This column is allowed to be NULL */ if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ - onError = OE_Abort; + onError = db->dfltOnError; } if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){ - onError = OE_Abort; + onError = db->dfltOnError; } assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail || onError==OE_Ignore || onError==OE_Replace ); switch( onError ){ case OE_Abort: @@ -1323,11 +1323,11 @@ */ #ifndef SQLITE_OMIT_CHECK if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){ ExprList *pCheck = pTab->pCheck; pParse->ckBase = regNewData+1; - onError = overrideError!=OE_Default ? overrideError : OE_Abort; + onError = overrideError!=OE_Default ? overrideError : db->dfltOnError; for(i=0; inExpr; i++){ int allOk; Expr *pExpr = pCheck->a[i].pExpr; if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue; allOk = sqlite3VdbeMakeLabel(v); @@ -1335,11 +1335,11 @@ if( onError==OE_Ignore ){ sqlite3VdbeGoto(v, ignoreDest); }else{ char *zName = pCheck->a[i].zName; if( zName==0 ) zName = pTab->zName; - if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */ + if( onError==OE_Replace ) onError = db->dfltOnError; /* IMP: R-15569-63625 */ sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK, onError, zName, P4_TRANSIENT, P5_ConstraintCheck); } sqlite3VdbeResolveLabel(v, allOk); @@ -1356,11 +1356,11 @@ /* Figure out what action to take in case of a rowid collision */ onError = pTab->keyConf; if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ - onError = OE_Abort; + onError = db->dfltOnError; } if( isUpdate ){ /* pkChng!=0 does not mean that the rowid has change, only that ** it might have changed. Skip the conflict logic below if the rowid @@ -1388,10 +1388,11 @@ ** the following conflict logic if it does not. */ sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData); VdbeCoverage(v); /* Generate code that deals with a rowid collision */ + if( onError==OE_Default ) onError = db->dfltOnError; switch( onError ){ default: { onError = OE_Abort; /* Fall thru into the next case */ } @@ -1530,11 +1531,11 @@ continue; /* pIdx is not a UNIQUE index */ } if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ - onError = OE_Abort; + onError = db->dfltOnError; } /* Check to see if the new index entry will be unique */ sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, regIdx, pIdx->nKeyCol); VdbeCoverage(v); @@ -1908,11 +1909,11 @@ return 0; /* tab1 must not be a virtual table */ } #endif if( onError==OE_Default ){ if( pDest->iPKey>=0 ) onError = pDest->keyConf; - if( onError==OE_Default ) onError = OE_Abort; + if( onError==OE_Default ) onError = db->dfltOnError; } assert(pSelect->pSrc); /* allocated even if there is no FROM clause */ if( pSelect->pSrc->nSrc!=1 ){ return 0; /* FROM clause must have exactly one term */ } Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -2777,10 +2777,11 @@ sqlite3_mutex_enter(db->mutex); db->errMask = 0xff; db->nDb = 2; db->magic = SQLITE_MAGIC_BUSY; db->aDb = db->aDbStatic; + db->dfltOnError = OE_Abort; assert( sizeof(db->aLimit)==sizeof(aHardLimit) ); memcpy(db->aLimit, aHardLimit, sizeof(db->aLimit)); db->aLimit[SQLITE_LIMIT_WORKER_THREADS] = SQLITE_DEFAULT_WORKER_THREADS; db->autoCommit = 1; Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1891,10 +1891,31 @@ } returnSingleInt(v, "threads", sqlite3_limit(db, SQLITE_LIMIT_WORKER_THREADS, -1)); break; } + + /* + ** PRAGMA onconflict + ** PRAGMA onconflict = FAIL + ** PRAGMA onconflict = ABORT + ** PRAGMA onconflict = ROLLBACK + ** + ** Set the default conflict handling algorithm. + */ + case PragTyp_ONCONFLICT: { + static const char *azMode[] = { "ABORT", "FAIL", "ROLLBACK" }; + static const u8 aeMode[] = { OE_Abort, OE_Fail, OE_Rollback }; + const char *zRes = 0; + int i; + for(i=0; idfltOnError = aeMode[i]; + if( db->dfltOnError==aeMode[i] ) zRes = azMode[i]; + } + returnSingleText(v, "onconflict", zRes); + break; + } #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) /* ** Report the current state of file logs for all databases */ Index: src/pragma.h ================================================================== --- src/pragma.h +++ src/pragma.h @@ -26,28 +26,29 @@ #define PragTyp_JOURNAL_SIZE_LIMIT 20 #define PragTyp_LOCK_PROXY_FILE 21 #define PragTyp_LOCKING_MODE 22 #define PragTyp_PAGE_COUNT 23 #define PragTyp_MMAP_SIZE 24 -#define PragTyp_PAGE_SIZE 25 -#define PragTyp_SECURE_DELETE 26 -#define PragTyp_SHRINK_MEMORY 27 -#define PragTyp_SOFT_HEAP_LIMIT 28 -#define PragTyp_STATS 29 -#define PragTyp_SYNCHRONOUS 30 -#define PragTyp_TABLE_INFO 31 -#define PragTyp_TEMP_STORE 32 -#define PragTyp_TEMP_STORE_DIRECTORY 33 -#define PragTyp_THREADS 34 -#define PragTyp_WAL_AUTOCHECKPOINT 35 -#define PragTyp_WAL_CHECKPOINT 36 -#define PragTyp_ACTIVATE_EXTENSIONS 37 -#define PragTyp_HEXKEY 38 -#define PragTyp_KEY 39 -#define PragTyp_REKEY 40 -#define PragTyp_LOCK_STATUS 41 -#define PragTyp_PARSER_TRACE 42 +#define PragTyp_ONCONFLICT 25 +#define PragTyp_PAGE_SIZE 26 +#define PragTyp_SECURE_DELETE 27 +#define PragTyp_SHRINK_MEMORY 28 +#define PragTyp_SOFT_HEAP_LIMIT 29 +#define PragTyp_STATS 30 +#define PragTyp_SYNCHRONOUS 31 +#define PragTyp_TABLE_INFO 32 +#define PragTyp_TEMP_STORE 33 +#define PragTyp_TEMP_STORE_DIRECTORY 34 +#define PragTyp_THREADS 35 +#define PragTyp_WAL_AUTOCHECKPOINT 36 +#define PragTyp_WAL_CHECKPOINT 37 +#define PragTyp_ACTIVATE_EXTENSIONS 38 +#define PragTyp_HEXKEY 39 +#define PragTyp_KEY 40 +#define PragTyp_REKEY 41 +#define PragTyp_LOCK_STATUS 42 +#define PragTyp_PARSER_TRACE 43 #define PragFlag_NeedSchema 0x01 #define PragFlag_ReadOnly 0x02 static const struct sPragmaNames { const char *const zName; /* Name of pragma */ u8 ePragTyp; /* PragTyp_XXX value */ @@ -297,10 +298,16 @@ /* iArg: */ 0 }, { /* zName: */ "mmap_size", /* ePragTyp: */ PragTyp_MMAP_SIZE, /* ePragFlag: */ 0, /* iArg: */ 0 }, +#endif + { /* zName: */ "onconflict", + /* ePragTyp: */ PragTyp_ONCONFLICT, + /* ePragFlag: */ 0, + /* iArg: */ 0 }, +#if !defined(SQLITE_OMIT_PAGER_PRAGMAS) { /* zName: */ "page_count", /* ePragTyp: */ PragTyp_PAGE_COUNT, /* ePragFlag: */ PragFlag_NeedSchema, /* iArg: */ 0 }, { /* zName: */ "page_size", @@ -459,6 +466,6 @@ /* ePragTyp: */ PragTyp_FLAG, /* ePragFlag: */ 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; -/* Number of pragmas: 60 on by default, 73 total. */ +/* Number of pragmas: 61 on by default, 74 total. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1192,10 +1192,11 @@ u8 dfltLockMode; /* Default locking-mode for attached dbs */ signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ u8 suppressErr; /* Do not issue error messages if true */ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ + u8 dfltOnError; /* Default conflict handling. OE_Abort */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -792,11 +792,11 @@ sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i); } } sqlite3VtabMakeWritable(pParse, pTab); sqlite3VdbeAddOp4(v, OP_VUpdate, 0, nArg, regArg, pVTab, P4_VTAB); - sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError); + sqlite3VdbeChangeP5(v, onError==OE_Default ? db->dfltOnError : onError); sqlite3MayAbort(pParse); /* End of the ephemeral table scan. Or, if using the onepass strategy, ** jump to here if the scan visited zero rows. */ if( bOnePass==0 ){ ADDED test/onconflict1.test Index: test/onconflict1.test ================================================================== --- /dev/null +++ test/onconflict1.test @@ -0,0 +1,99 @@ +# 2016-02-27 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# +# This file focuses on the PRAGMA onconflict statement. + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix onconflict1 + +do_execsql_test 100 { + PRAGMA onconflict; +} {ABORT} +do_execsql_test 110 { + PRAGMA onconflict=Rollback; +} {ROLLBACK} +do_execsql_test 120 { + PRAGMA onconflict=fail; +} {FAIL} +do_execsql_test 130 { + PRAGMA onconflict=whatever; +} {FAIL} +do_execsql_test 140 { + PRAGMA onconflict=abort; +} {ABORT} + +do_catchsql_test 200 { + CREATE TABLE t1(a, b UNIQUE); + INSERT INTO t1(a,b) VALUES(1,1),(2,2),(3,3),(98,98),(99,99); + BEGIN; + UPDATE t1 SET a=100 WHERE a=101; + UPDATE t1 SET b=10; +} {1 {UNIQUE constraint failed: t1.b}} +do_execsql_test 201 { + SELECT a, b FROM t1 ORDER BY a; +} {1 1 2 2 3 3 98 98 99 99} + +do_catchsql_test 210 { + PRAGMA onconflict=FAIL; + UPDATE t1 SET b=10; +} {1 {UNIQUE constraint failed: t1.b}} +do_execsql_test 211 { + SELECT a, b FROM t1 ORDER BY a; +} {1 10 2 2 3 3 98 98 99 99} + +do_catchsql_test 220 { + ROLLBACK; + BEGIN; + PRAGMA onconflict=ROLLBACK; + UPDATE t1 SET b=10; +} {1 {UNIQUE constraint failed: t1.b}} +do_execsql_test 221 { + SELECT a, b FROM t1 ORDER BY a; +} {1 1 2 2 3 3 98 98 99 99} +do_catchsql_test 222 { + ROLLBACK +} {1 {cannot rollback - no transaction is active}} + +do_catchsql_test 300 { + PRAGMA onconflict=ABORT; + BEGIN; + INSERT INTO t1(a,b) VALUES(4,4),(5,1),(6,6); +} {1 {UNIQUE constraint failed: t1.b}} +do_execsql_test 301 { + SELECT a, b FROM t1 ORDER BY a; +} {1 1 2 2 3 3 98 98 99 99} + +do_catchsql_test 310 { + PRAGMA onconflict=ROLLBACK; + INSERT INTO t1(a,b) VALUES(4,4),(5,1),(6,6); +} {1 {UNIQUE constraint failed: t1.b}} +do_execsql_test 311 { + SELECT a, b FROM t1 ORDER BY a; +} {1 1 2 2 3 3 98 98 99 99} +do_catchsql_test 312 { + ROLLBACK +} {1 {cannot rollback - no transaction is active}} + +do_catchsql_test 320 { + PRAGMA onconflict=FAIL; + BEGIN; + INSERT INTO t1(a,b) VALUES(4,4),(5,1),(6,6); +} {1 {UNIQUE constraint failed: t1.b}} +do_execsql_test 321 { + SELECT a, b FROM t1 ORDER BY a; +} {1 1 2 2 3 3 4 4 98 98 99 99} +do_catchsql_test 322 { + ROLLBACK +} {0 {}} + +finish_test Index: tool/mkpragmatab.tcl ================================================================== --- tool/mkpragmatab.tcl +++ tool/mkpragmatab.tcl @@ -315,10 +315,12 @@ IF: defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) NAME: soft_heap_limit NAME: threads + + NAME: onconflict } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h"