/ Check-in [d07f05e9]
Login

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

Overview
Comment:Get the ON CONFLICT DO NOTHING form of upsert working by mapping it into INSERT OR IGNORE.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: d07f05e98bb9ce0f9b46db159d9df161b7499d6face6a5299ecd2d00a94fb8d0
User & Date: drh 2018-04-13 15:14:33
Context
2018-04-13
16:29
Merge the preupdate hook change from trunk. check-in: 7353caab user: drh tags: upsert
15:14
Get the ON CONFLICT DO NOTHING form of upsert working by mapping it into INSERT OR IGNORE. check-in: d07f05e9 user: drh tags: upsert
14:27
Get the conflict-target clause parsing working correctly, with test cases. This change involves an enhancement to sqlite3ExprCompare() which needs to be reviewed on trunk prior to merging. check-in: 5bf70425 user: drh tags: upsert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1391   1391         /* pkChng!=0 does not mean that the rowid has changed, only that
  1392   1392         ** it might have changed.  Skip the conflict logic below if the rowid
  1393   1393         ** is unchanged. */
  1394   1394         sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
  1395   1395         sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
  1396   1396         VdbeCoverage(v);
  1397   1397       }
         1398  +
         1399  +    /* figure out whether or not upsert applies in this case */
         1400  +    if( pUpsert && (pUpsert->pUpsertTarget==0 || pUpsert->pUpsertIdx==0) ){
         1401  +      if( pUpsert->pUpsertSet==0 ){
         1402  +        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
         1403  +      }else{
         1404  +        onError = OE_Update;  /* DO UPDATE */
         1405  +      }
         1406  +    }
  1398   1407   
  1399   1408       /* If the response to a rowid conflict is REPLACE but the response
  1400   1409       ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
  1401   1410       ** to defer the running of the rowid conflict checking until after
  1402   1411       ** the UNIQUE constraints have run.
  1403   1412       */
  1404   1413       if( onError==OE_Replace && overrideError!=OE_Replace ){
................................................................................
  1411   1420       }
  1412   1421   
  1413   1422       /* Check to see if the new rowid already exists in the table.  Skip
  1414   1423       ** the following conflict logic if it does not. */
  1415   1424       sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
  1416   1425       VdbeCoverage(v);
  1417   1426   
  1418         -    /* Generate code that deals with a rowid collision */
  1419   1427       switch( onError ){
  1420   1428         default: {
  1421   1429           onError = OE_Abort;
  1422   1430           /* Fall thru into the next case */
  1423   1431         }
  1424   1432         case OE_Rollback:
  1425   1433         case OE_Abort:
................................................................................
  1474   1482               sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,-1);
  1475   1483             }
  1476   1484           }
  1477   1485           seenReplace = 1;
  1478   1486           break;
  1479   1487         }
  1480   1488         case OE_Ignore: {
  1481         -        /*assert( seenReplace==0 );*/
  1482   1489           sqlite3VdbeGoto(v, ignoreDest);
  1483   1490           break;
  1484   1491         }
  1485   1492       }
  1486   1493       sqlite3VdbeResolveLabel(v, addrRowidOk);
  1487   1494       if( ipkTop ){
  1488   1495         ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
................................................................................
  1564   1571         continue;  /* pIdx is not a UNIQUE index */
  1565   1572       }
  1566   1573       if( overrideError!=OE_Default ){
  1567   1574         onError = overrideError;
  1568   1575       }else if( onError==OE_Default ){
  1569   1576         onError = OE_Abort;
  1570   1577       }
         1578  +
         1579  +    /* Figure out if the upsert clause applies to this index */
         1580  +    if( pUpsert && (pUpsert->pUpsertTarget==0 || pUpsert->pUpsertIdx==pIdx) ){
         1581  +      if( pUpsert->pUpsertSet==0 ){
         1582  +        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
         1583  +      }else{
         1584  +        onError = OE_Update;  /* DO UPDATE */
         1585  +      }
         1586  +    }
  1571   1587   
  1572   1588       /* Collision detection may be omitted if all of the following are true:
  1573   1589       **   (1) The conflict resolution algorithm is REPLACE
  1574   1590       **   (2) The table is a WITHOUT ROWID table
  1575   1591       **   (3) There are no secondary indexes on the table
  1576   1592       **   (4) No delete triggers need to be fired if there is a conflict
  1577   1593       **   (5) No FK constraint counters need to be updated if a conflict occurs.

Changes to src/sqliteInt.h.

  2043   2043   */
  2044   2044   #define OE_None     0   /* There is no constraint to check */
  2045   2045   #define OE_Rollback 1   /* Fail the operation and rollback the transaction */
  2046   2046   #define OE_Abort    2   /* Back out changes but do no rollback transaction */
  2047   2047   #define OE_Fail     3   /* Stop the operation but leave all prior changes */
  2048   2048   #define OE_Ignore   4   /* Ignore the error. Do not do the INSERT or UPDATE */
  2049   2049   #define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */
  2050         -
  2051         -#define OE_Restrict 6   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
  2052         -#define OE_SetNull  7   /* Set the foreign key value to NULL */
  2053         -#define OE_SetDflt  8   /* Set the foreign key value to its default */
  2054         -#define OE_Cascade  9   /* Cascade the changes */
  2055         -
  2056         -#define OE_Default  10  /* Do whatever the default action is */
         2050  +#define OE_Update   6   /* Process as a DO UPDATE in an upsert */
         2051  +#define OE_Restrict 7   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
         2052  +#define OE_SetNull  8   /* Set the foreign key value to NULL */
         2053  +#define OE_SetDflt  9   /* Set the foreign key value to its default */
         2054  +#define OE_Cascade  10  /* Cascade the changes */
         2055  +#define OE_Default  11  /* Do whatever the default action is */
  2057   2056   
  2058   2057   
  2059   2058   /*
  2060   2059   ** An instance of the following structure is passed as the first
  2061   2060   ** argument to sqlite3VdbeKeyCompare and is used to control the
  2062   2061   ** comparison of the two index keys.
  2063   2062   **

Changes to test/upsert1.test.

    15     15   source $testdir/tester.tcl
    16     16   set testprefix zipfile
    17     17   
    18     18   do_execsql_test upsert1-100 {
    19     19     CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
    20     20     CREATE UNIQUE INDEX t1x1 ON t1(b);
    21     21     INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
           22  +  INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
    22     23     SELECT * FROM t1;
    23     24   } {1 2 0}
    24     25   do_execsql_test upsert1-101 {
    25     26     DELETE FROM t1;
    26     27     INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
           28  +  INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
    27     29     SELECT * FROM t1;
    28     30   } {2 3 0}
    29     31   do_execsql_test upsert1-102 {
    30     32     DELETE FROM t1;
    31     33     INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
           34  +  INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
    32     35     SELECT * FROM t1;
    33     36   } {3 4 0}
    34     37   do_catchsql_test upsert1-110 {
    35     38     INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
    36     39     SELECT * FROM t1;
    37     40   } {1 {no such column: x}}
    38     41   do_catchsql_test upsert1-120 {
................................................................................
    47     50   do_execsql_test upsert1-140 {
    48     51     DELETE FROM t1;
    49     52     INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
    50     53     SELECT * FROM t1;
    51     54   } {5 6 0}
    52     55   
    53     56   do_catchsql_test upsert1-200 {
    54         -  DROP INDEX t1x1;
    55         -  DELETE FROM t1;
    56         -  CREATE UNIQUE INDEX t1x1 ON t1(a||b);
    57         -  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING;
           57  +  DROP TABLE t1;
           58  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0);
           59  +  CREATE UNIQUE INDEX t1x1 ON t1(a+b);
           60  +  INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING;
           61  +  INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING;
    58     62     SELECT * FROM t1;
    59         -} {0 {5 6 0}}
           63  +} {0 {7 8 0}}
           64  +do_catchsql_test upsert1-201 {
           65  +  INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING;
           66  +} {1 {UNIQUE constraint failed: index 't1x1'}}
    60     67   do_catchsql_test upsert1-210 {
    61     68     DELETE FROM t1;
    62         -  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||+b) DO NOTHING;
           69  +  INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING;
    63     70     SELECT * FROM t1;
    64     71   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    65     72   
    66     73   do_catchsql_test upsert1-300 {
    67     74     DROP INDEX t1x1;
    68     75     DELETE FROM t1;
    69     76     CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
    70         -  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) DO NOTHING;
           77  +  INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING;
    71     78     SELECT * FROM t1;
    72     79   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    73     80   do_catchsql_test upsert1-310 {
    74     81     DELETE FROM t1;
    75         -  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
           82  +  INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
    76     83     SELECT * FROM t1;
    77     84   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    78     85   do_execsql_test upsert1-320 {
    79     86     DELETE FROM t1;
    80         -  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) WHERE b>10 DO NOTHING;
    81         -  SELECT * FROM t1;
    82         -} {5 6 0}
           87  +  INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
           88  +         ON CONFLICT(b) WHERE b>10 DO NOTHING;
           89  +  SELECT *, 'x' FROM t1 ORDER BY b, a;
           90  +} {1 2 0 x 3 2 0 x 4 20 0 x}
    83     91   
    84     92   finish_test