/ Check-in [3a0c347c]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Experimental "PRAGMA onconflict=FAIL" statement to change the default ON CONFLICT algorithm to something other than ABORT.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | pragma-onconflict
Files: files | file ages | folders
SHA1: 3a0c347cca58fdaefd1b3644ba1649a5b4ebe91f
User & Date: drh 2016-02-27 17:16:34
Context
2016-02-27
19:03
More compact implementation of PRAGMA onconflict, and some test cases. Leaf check-in: 3e5d38f5 user: drh tags: pragma-onconflict
17:16
Experimental "PRAGMA onconflict=FAIL" statement to change the default ON CONFLICT algorithm to something other than ABORT. check-in: 3a0c347c user: drh tags: pragma-onconflict
17:12
Enhance the ".stats" command in sqlite3.exe to show one-time stats information if invoked with one argument. Also show /proc/PID/io information if run on Linux. check-in: 3c36948f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

   984    984       ** do the insertion.
   985    985       */
   986    986   #ifndef SQLITE_OMIT_VIRTUALTABLE
   987    987       if( IsVirtual(pTab) ){
   988    988         const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
   989    989         sqlite3VtabMakeWritable(pParse, pTab);
   990    990         sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
   991         -      sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
          991  +      sqlite3VdbeChangeP5(v, onError==OE_Default ? db->dfltOnError : onError);
   992    992         sqlite3MayAbort(pParse);
   993    993       }else
   994    994   #endif
   995    995       {
   996    996         int isReplace;    /* Set to true if constraints may cause a replace */
   997    997         sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   998    998             regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
................................................................................
  1278   1278         continue;
  1279   1279       }
  1280   1280       onError = pTab->aCol[i].notNull;
  1281   1281       if( onError==OE_None ) continue;  /* This column is allowed to be NULL */
  1282   1282       if( overrideError!=OE_Default ){
  1283   1283         onError = overrideError;
  1284   1284       }else if( onError==OE_Default ){
  1285         -      onError = OE_Abort;
         1285  +      onError = db->dfltOnError;
  1286   1286       }
  1287   1287       if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
  1288         -      onError = OE_Abort;
         1288  +      onError = db->dfltOnError;
  1289   1289       }
  1290   1290       assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
  1291   1291           || onError==OE_Ignore || onError==OE_Replace );
  1292   1292       switch( onError ){
  1293   1293         case OE_Abort:
  1294   1294           sqlite3MayAbort(pParse);
  1295   1295           /* Fall through */
................................................................................
  1321   1321   
  1322   1322     /* Test all CHECK constraints
  1323   1323     */
  1324   1324   #ifndef SQLITE_OMIT_CHECK
  1325   1325     if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
  1326   1326       ExprList *pCheck = pTab->pCheck;
  1327   1327       pParse->ckBase = regNewData+1;
  1328         -    onError = overrideError!=OE_Default ? overrideError : OE_Abort;
         1328  +    onError = overrideError!=OE_Default ? overrideError : db->dfltOnError;
  1329   1329       for(i=0; i<pCheck->nExpr; i++){
  1330   1330         int allOk;
  1331   1331         Expr *pExpr = pCheck->a[i].pExpr;
  1332   1332         if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue;
  1333   1333         allOk = sqlite3VdbeMakeLabel(v);
  1334   1334         sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);
  1335   1335         if( onError==OE_Ignore ){
  1336   1336           sqlite3VdbeGoto(v, ignoreDest);
  1337   1337         }else{
  1338   1338           char *zName = pCheck->a[i].zName;
  1339   1339           if( zName==0 ) zName = pTab->zName;
  1340         -        if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */
         1340  +        if( onError==OE_Replace ) onError = db->dfltOnError; /* IMP: R-15569-63625 */
  1341   1341           sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
  1342   1342                                 onError, zName, P4_TRANSIENT,
  1343   1343                                 P5_ConstraintCheck);
  1344   1344         }
  1345   1345         sqlite3VdbeResolveLabel(v, allOk);
  1346   1346       }
  1347   1347     }
................................................................................
  1354   1354       int addrRowidOk = sqlite3VdbeMakeLabel(v);
  1355   1355   
  1356   1356       /* Figure out what action to take in case of a rowid collision */
  1357   1357       onError = pTab->keyConf;
  1358   1358       if( overrideError!=OE_Default ){
  1359   1359         onError = overrideError;
  1360   1360       }else if( onError==OE_Default ){
  1361         -      onError = OE_Abort;
         1361  +      onError = db->dfltOnError;
  1362   1362       }
  1363   1363   
  1364   1364       if( isUpdate ){
  1365   1365         /* pkChng!=0 does not mean that the rowid has change, only that
  1366   1366         ** it might have changed.  Skip the conflict logic below if the rowid
  1367   1367         ** is unchanged. */
  1368   1368         sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
................................................................................
  1386   1386   
  1387   1387       /* Check to see if the new rowid already exists in the table.  Skip
  1388   1388       ** the following conflict logic if it does not. */
  1389   1389       sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
  1390   1390       VdbeCoverage(v);
  1391   1391   
  1392   1392       /* Generate code that deals with a rowid collision */
         1393  +    if( onError==OE_Default ) onError = db->dfltOnError;
  1393   1394       switch( onError ){
  1394   1395         default: {
  1395   1396           onError = OE_Abort;
  1396   1397           /* Fall thru into the next case */
  1397   1398         }
  1398   1399         case OE_Rollback:
  1399   1400         case OE_Abort:
................................................................................
  1528   1529         sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);
  1529   1530         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1530   1531         continue;  /* pIdx is not a UNIQUE index */
  1531   1532       }
  1532   1533       if( overrideError!=OE_Default ){
  1533   1534         onError = overrideError;
  1534   1535       }else if( onError==OE_Default ){
  1535         -      onError = OE_Abort;
         1536  +      onError = db->dfltOnError;
  1536   1537       }
  1537   1538       
  1538   1539       /* Check to see if the new index entry will be unique */
  1539   1540       sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
  1540   1541                            regIdx, pIdx->nKeyCol); VdbeCoverage(v);
  1541   1542   
  1542   1543       /* Generate code to handle collisions */
................................................................................
  1906   1907   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1907   1908     if( pDest->tabFlags & TF_Virtual ){
  1908   1909       return 0;   /* tab1 must not be a virtual table */
  1909   1910     }
  1910   1911   #endif
  1911   1912     if( onError==OE_Default ){
  1912   1913       if( pDest->iPKey>=0 ) onError = pDest->keyConf;
  1913         -    if( onError==OE_Default ) onError = OE_Abort;
         1914  +    if( onError==OE_Default ) onError = db->dfltOnError;
  1914   1915     }
  1915   1916     assert(pSelect->pSrc);   /* allocated even if there is no FROM clause */
  1916   1917     if( pSelect->pSrc->nSrc!=1 ){
  1917   1918       return 0;   /* FROM clause must have exactly one term */
  1918   1919     }
  1919   1920     if( pSelect->pSrc->a[0].pSelect ){
  1920   1921       return 0;   /* FROM clause cannot contain a subquery */

Changes to src/main.c.

  2775   2775       }
  2776   2776     }
  2777   2777     sqlite3_mutex_enter(db->mutex);
  2778   2778     db->errMask = 0xff;
  2779   2779     db->nDb = 2;
  2780   2780     db->magic = SQLITE_MAGIC_BUSY;
  2781   2781     db->aDb = db->aDbStatic;
         2782  +  db->dfltOnError = OE_Abort;
  2782   2783   
  2783   2784     assert( sizeof(db->aLimit)==sizeof(aHardLimit) );
  2784   2785     memcpy(db->aLimit, aHardLimit, sizeof(db->aLimit));
  2785   2786     db->aLimit[SQLITE_LIMIT_WORKER_THREADS] = SQLITE_DEFAULT_WORKER_THREADS;
  2786   2787     db->autoCommit = 1;
  2787   2788     db->nextAutovac = -1;
  2788   2789     db->szMmap = sqlite3GlobalConfig.szMmap;

Changes to src/pragma.c.

  1889   1889       ){
  1890   1890         sqlite3_limit(db, SQLITE_LIMIT_WORKER_THREADS, (int)(N&0x7fffffff));
  1891   1891       }
  1892   1892       returnSingleInt(v, "threads",
  1893   1893                       sqlite3_limit(db, SQLITE_LIMIT_WORKER_THREADS, -1));
  1894   1894       break;
  1895   1895     }
         1896  +
         1897  +  /*
         1898  +  **   PRAGMA onconflict
         1899  +  **   PRAGMA onconflict = FAIL
         1900  +  **   PRAGMA onconflict = ABORT
         1901  +  **   PRAGMA onconflict = ROLLBACK
         1902  +  **
         1903  +  ** Set the default conflict handling algorithm.
         1904  +  */
         1905  +  case PragTyp_ONCONFLICT: {
         1906  +    const char *zRes = "ABORT";
         1907  +    if( zRight ){
         1908  +      if( sqlite3StrICmp(zRight,"FAIL")==0 ) db->dfltOnError = OE_Fail;
         1909  +      if( sqlite3StrICmp(zRight,"ABORT")==0 ) db->dfltOnError = OE_Abort;
         1910  +      if( sqlite3StrICmp(zRight,"ROLLBACK")==0 ) db->dfltOnError = OE_Rollback;
         1911  +    }
         1912  +    switch( db->dfltOnError ){
         1913  +      case OE_Fail:     zRes = "FAIL";      break;
         1914  +      case OE_Rollback: zRes = "ROLLBACK";  break;
         1915  +    }
         1916  +    returnSingleText(v, "onconflict", zRes);
         1917  +    break;
         1918  +  }
  1896   1919   
  1897   1920   #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST)
  1898   1921     /*
  1899   1922     ** Report the current state of file logs for all databases
  1900   1923     */
  1901   1924     case PragTyp_LOCK_STATUS: {
  1902   1925       static const char *const azLockName[] = {

Changes to src/pragma.h.

    24     24   #define PragTyp_INTEGRITY_CHECK               18
    25     25   #define PragTyp_JOURNAL_MODE                  19
    26     26   #define PragTyp_JOURNAL_SIZE_LIMIT            20
    27     27   #define PragTyp_LOCK_PROXY_FILE               21
    28     28   #define PragTyp_LOCKING_MODE                  22
    29     29   #define PragTyp_PAGE_COUNT                    23
    30     30   #define PragTyp_MMAP_SIZE                     24
    31         -#define PragTyp_PAGE_SIZE                     25
    32         -#define PragTyp_SECURE_DELETE                 26
    33         -#define PragTyp_SHRINK_MEMORY                 27
    34         -#define PragTyp_SOFT_HEAP_LIMIT               28
    35         -#define PragTyp_STATS                         29
    36         -#define PragTyp_SYNCHRONOUS                   30
    37         -#define PragTyp_TABLE_INFO                    31
    38         -#define PragTyp_TEMP_STORE                    32
    39         -#define PragTyp_TEMP_STORE_DIRECTORY          33
    40         -#define PragTyp_THREADS                       34
    41         -#define PragTyp_WAL_AUTOCHECKPOINT            35
    42         -#define PragTyp_WAL_CHECKPOINT                36
    43         -#define PragTyp_ACTIVATE_EXTENSIONS           37
    44         -#define PragTyp_HEXKEY                        38
    45         -#define PragTyp_KEY                           39
    46         -#define PragTyp_REKEY                         40
    47         -#define PragTyp_LOCK_STATUS                   41
    48         -#define PragTyp_PARSER_TRACE                  42
           31  +#define PragTyp_ONCONFLICT                    25
           32  +#define PragTyp_PAGE_SIZE                     26
           33  +#define PragTyp_SECURE_DELETE                 27
           34  +#define PragTyp_SHRINK_MEMORY                 28
           35  +#define PragTyp_SOFT_HEAP_LIMIT               29
           36  +#define PragTyp_STATS                         30
           37  +#define PragTyp_SYNCHRONOUS                   31
           38  +#define PragTyp_TABLE_INFO                    32
           39  +#define PragTyp_TEMP_STORE                    33
           40  +#define PragTyp_TEMP_STORE_DIRECTORY          34
           41  +#define PragTyp_THREADS                       35
           42  +#define PragTyp_WAL_AUTOCHECKPOINT            36
           43  +#define PragTyp_WAL_CHECKPOINT                37
           44  +#define PragTyp_ACTIVATE_EXTENSIONS           38
           45  +#define PragTyp_HEXKEY                        39
           46  +#define PragTyp_KEY                           40
           47  +#define PragTyp_REKEY                         41
           48  +#define PragTyp_LOCK_STATUS                   42
           49  +#define PragTyp_PARSER_TRACE                  43
    49     50   #define PragFlag_NeedSchema           0x01
    50     51   #define PragFlag_ReadOnly             0x02
    51     52   static const struct sPragmaNames {
    52     53     const char *const zName;  /* Name of pragma */
    53     54     u8 ePragTyp;              /* PragTyp_XXX value */
    54     55     u8 mPragFlag;             /* Zero or more PragFlag_XXX values */
    55     56     u32 iArg;                 /* Extra argument */
................................................................................
   295    296       /* ePragTyp:  */ PragTyp_PAGE_COUNT,
   296    297       /* ePragFlag: */ PragFlag_NeedSchema,
   297    298       /* iArg:      */ 0 },
   298    299     { /* zName:     */ "mmap_size",
   299    300       /* ePragTyp:  */ PragTyp_MMAP_SIZE,
   300    301       /* ePragFlag: */ 0,
   301    302       /* iArg:      */ 0 },
          303  +#endif
          304  +  { /* zName:     */ "onconflict",
          305  +    /* ePragTyp:  */ PragTyp_ONCONFLICT,
          306  +    /* ePragFlag: */ 0,
          307  +    /* iArg:      */ 0 },
          308  +#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   302    309     { /* zName:     */ "page_count",
   303    310       /* ePragTyp:  */ PragTyp_PAGE_COUNT,
   304    311       /* ePragFlag: */ PragFlag_NeedSchema,
   305    312       /* iArg:      */ 0 },
   306    313     { /* zName:     */ "page_size",
   307    314       /* ePragTyp:  */ PragTyp_PAGE_SIZE,
   308    315       /* ePragFlag: */ 0,
................................................................................
   457    464   #if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
   458    465     { /* zName:     */ "writable_schema",
   459    466       /* ePragTyp:  */ PragTyp_FLAG,
   460    467       /* ePragFlag: */ 0,
   461    468       /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
   462    469   #endif
   463    470   };
   464         -/* Number of pragmas: 60 on by default, 73 total. */
          471  +/* Number of pragmas: 61 on by default, 74 total. */

Changes to src/sqliteInt.h.

  1190   1190     u8 mallocFailed;              /* True if we have seen a malloc failure */
  1191   1191     u8 bBenignMalloc;             /* Do not require OOMs if true */
  1192   1192     u8 dfltLockMode;              /* Default locking-mode for attached dbs */
  1193   1193     signed char nextAutovac;      /* Autovac setting after VACUUM if >=0 */
  1194   1194     u8 suppressErr;               /* Do not issue error messages if true */
  1195   1195     u8 vtabOnConflict;            /* Value to return for s3_vtab_on_conflict() */
  1196   1196     u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
         1197  +  u8 dfltOnError;               /* Default conflict handling.  OE_Abort */
  1197   1198     int nextPagesize;             /* Pagesize after VACUUM if >0 */
  1198   1199     u32 magic;                    /* Magic number for detect library misuse */
  1199   1200     int nChange;                  /* Value returned by sqlite3_changes() */
  1200   1201     int nTotalChange;             /* Value returned by sqlite3_total_changes() */
  1201   1202     int aLimit[SQLITE_N_LIMIT];   /* Limits */
  1202   1203     int nMaxSorterMmap;           /* Maximum size of regions mapped by sorter */
  1203   1204     struct sqlite3InitInfo {      /* Information used during initialization */

Changes to src/update.c.

   790    790       ** invoke the VUpdate method.  */
   791    791       for(i=0; i<nArg; i++){
   792    792         sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
   793    793       }
   794    794     }
   795    795     sqlite3VtabMakeWritable(pParse, pTab);
   796    796     sqlite3VdbeAddOp4(v, OP_VUpdate, 0, nArg, regArg, pVTab, P4_VTAB);
   797         -  sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
          797  +  sqlite3VdbeChangeP5(v, onError==OE_Default ? db->dfltOnError : onError);
   798    798     sqlite3MayAbort(pParse);
   799    799   
   800    800     /* End of the ephemeral table scan. Or, if using the onepass strategy,
   801    801     ** jump to here if the scan visited zero rows. */
   802    802     if( bOnePass==0 ){
   803    803       sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
   804    804       sqlite3VdbeJumpHere(v, addr);
   805    805       sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
   806    806     }else{
   807    807       sqlite3WhereEnd(pWInfo);
   808    808     }
   809    809   }
   810    810   #endif /* SQLITE_OMIT_VIRTUALTABLE */

Changes to tool/mkpragmatab.tcl.

   313    313   
   314    314     NAME: activate_extensions
   315    315     IF:   defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD)
   316    316   
   317    317     NAME: soft_heap_limit
   318    318   
   319    319     NAME: threads
          320  +
          321  +  NAME: onconflict
   320    322   }
   321    323   
   322    324   # Open the output file
   323    325   #
   324    326   set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h"
   325    327   puts "Overwriting $destfile with new pragma table..."
   326    328   set fd [open $destfile wb]