/ Check-in [27cd3b2f]
Login

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

Overview
Comment:Add some more simple test cases for UPSERT. And a minor fix.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: 27cd3b2fb2ad0cf2b36741bd1057cb7973954d40456e9db158261a38b049d2b5
User & Date: dan 2018-04-17 18:16:10
Context
2018-04-17
18:18
Simplification to the upsert logic. check-in: f36d07a5 user: drh tags: upsert
18:16
Add some more simple test cases for UPSERT. And a minor fix. check-in: 27cd3b2f user: dan tags: upsert
16:16
New test cases for upsert. check-in: 907b5a37 user: drh tags: upsert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1576   1576             }
  1577   1577           }
  1578   1578           seenReplace = 1;
  1579   1579           break;
  1580   1580         }
  1581   1581   #ifndef SQLITE_OMIT_UPSERT
  1582   1582         case OE_Update: {
  1583         -        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur, 0);
         1583  +        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur);
  1584   1584           /* Fall through */
  1585   1585         }
  1586   1586   #endif
  1587   1587         case OE_Ignore: {
  1588   1588           sqlite3VdbeGoto(v, ignoreDest);
  1589   1589           break;
  1590   1590         }
................................................................................
  1783   1783         case OE_Abort:
  1784   1784         case OE_Fail: {
  1785   1785           sqlite3UniqueConstraint(pParse, onError, pIdx);
  1786   1786           break;
  1787   1787         }
  1788   1788   #ifndef SQLITE_OMIT_UPSERT
  1789   1789         case OE_Update: {
  1790         -        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iDataCur, iIdxCur);
         1790  +        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix);
  1791   1791           /* Fall through */
  1792   1792         }
  1793   1793   #endif
  1794   1794         case OE_Ignore: {
  1795   1795           sqlite3VdbeGoto(v, ignoreDest);
  1796   1796           break;
  1797   1797         }

Changes to src/sqliteInt.h.

  4289   4289   #define sqlite3WithDelete(x,y)
  4290   4290   #endif
  4291   4291   #ifndef SQLITE_OMIT_UPSERT
  4292   4292     Upsert *sqlite3UpsertNew(sqlite3*,ExprList*,Expr*,ExprList*,Expr*);
  4293   4293     void sqlite3UpsertDelete(sqlite3*,Upsert*);
  4294   4294     Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  4295   4295     int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*);
  4296         -  void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int,int);
         4296  +  void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int);
  4297   4297   #else
  4298   4298   #define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
  4299   4299   #define sqlite3UpsertDelete(x,y)
  4300   4300   #define sqlite3UpsertDup(x,y)     ((Upsert*)0)
  4301   4301   #endif
  4302   4302   
  4303   4303   

Changes to src/upsert.c.

   178    178     sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
   179    179                             "PRIMARY KEY or UNIQUE constraint");
   180    180     return SQLITE_ERROR;
   181    181   }
   182    182   
   183    183   /*
   184    184   ** Generate bytecode that does an UPDATE as part of an upsert.
          185  +**
          186  +** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
          187  +** In this case parameter iCur is a cursor open on the table b-tree that
          188  +** currently points to the conflicting table row. Otherwise, if pIdx
          189  +** is not NULL, then pIdx is the constraint that failed and iCur is a
          190  +** cursor points to the conflicting row.
   185    191   */
   186    192   void sqlite3UpsertDoUpdate(
   187    193     Parse *pParse,        /* The parsing and code-generating context */
   188    194     Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
   189    195     Table *pTab,          /* The table being updated */
   190    196     Index *pIdx,          /* The UNIQUE constraint that failed */
   191         -  int iDataCur,         /* Cursor for the pTab, table being updated */
   192         -  int iIdxCur           /* Cursor for pIdx */
          197  +  int iCur              /* Cursor for pIdx (or pTab if pIdx==NULL) */
   193    198   ){
   194    199     Vdbe *v = pParse->pVdbe;
   195    200     sqlite3 *db = pParse->db;
   196    201     int regKey;               /* Register(s) containing the key */
   197    202     Expr *pWhere;             /* Where clause for the UPDATE */
   198    203     Expr *pE1, *pE2;
   199    204     SrcList *pSrc;            /* FROM clause for the UPDATE */
................................................................................
   201    206     assert( v!=0 );
   202    207     VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
   203    208     pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0);
   204    209     if( pIdx==0 || HasRowid(pTab) ){
   205    210       /* We are dealing with an IPK */
   206    211       regKey = ++pParse->nMem;
   207    212       if( pIdx ){
   208         -      sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, regKey);
          213  +      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey);
   209    214       }else{
   210         -      sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regKey);
          215  +      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey);
   211    216       }
   212    217       pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
   213    218       if( pE1 ){
   214    219         pE1->pTab = pTab;
   215    220         pE1->iTable = pParse->nTab;
   216    221         pE1->iColumn = -1;
   217    222       }
................................................................................
   227    232       int iTab = pParse->nTab+1;
   228    233       Index *pX;
   229    234       for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){
   230    235         iTab++;
   231    236       }
   232    237       for(i=0; i<pIdx->nKeyCol; i++){
   233    238         regKey = ++pParse->nMem;
   234         -      sqlite3VdbeAddOp3(v, OP_Column, iDataCur, i, regKey);
          239  +      sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey);
   235    240         j = pIdx->aiColumn[i];
   236    241         VdbeComment((v, "%s", pTab->aCol[j].zName));
   237    242         pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
   238    243         if( pE1 ){
   239    244           pE1->pTab = pTab;
   240    245           pE1->iTable = iTab;
   241    246           pE1->iColumn = j;

Added test/upsert4.test.

            1  +# 2018-04-17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for UPSERT
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix upsert2
           17  +
           18  +foreach {tn sql} {
           19  +  1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) }
           20  +  2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) }
           21  +  3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID}
           22  +} {
           23  +  reset_db
           24  +  execsql $sql
           25  +
           26  +  do_execsql_test 1.$tn.0 {
           27  +    INSERT INTO t1 VALUES(1, NULL, 'one');
           28  +    INSERT INTO t1 VALUES(2, NULL, 'two');
           29  +    INSERT INTO t1 VALUES(3, NULL, 'three');
           30  +  }
           31  +  
           32  +  do_execsql_test 1.$tn.1 {
           33  +    INSERT INTO t1 VALUES(1, NULL, 'xyz') ON CONFLICT DO NOTHING;
           34  +    SELECT * FROM t1;
           35  +  } {
           36  +    1 {} one 2 {} two 3 {} three
           37  +  }
           38  +  
           39  +  do_execsql_test 1.$tn.2 {
           40  +    INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT DO NOTHING;
           41  +    SELECT * FROM t1;
           42  +  } {
           43  +    1 {} one 2 {} two 3 {} three
           44  +  }
           45  +  
           46  +  do_execsql_test 1.$tn.3 {
           47  +    INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT (c) DO UPDATE SET b = 1;
           48  +    SELECT * FROM t1;
           49  +  } {
           50  +    1 {} one 2 1 two 3 {} three
           51  +  }
           52  +  
           53  +  do_execsql_test 1.$tn.4 {
           54  +    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET b=2;
           55  +    SELECT * FROM t1;
           56  +  } {1 {} one 2 2 two 3 {} three}
           57  +
           58  +  do_catchsql_test 1.$tn.5 {
           59  +    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 
           60  +      DO UPDATE SET c = 'one';
           61  +  } {1 {UNIQUE constraint failed: t1.c}}
           62  +
           63  +  do_execsql_test 1.$tn.6 {
           64  +    SELECT * FROM t1;
           65  +  } {1 {} one 2 2 two 3 {} three}
           66  +}
           67  +
           68  +finish_test
           69  +