/ Check-in [592da134]
Login

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

Overview
Comment:Fix for ticket #45: Allow an UPDATE statement to change the INTEGER PRIMARY KEY to itself without triggering a constraint error. (CVS 573)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:592da1346872e1373bd13525d05d0f33c1056709
User & Date: drh 2002-05-21 12:56:43
Context
2002-05-21
13:02
Modify the shell so that when it is doing a ".dump" it always uses single quotes and not double quotes for quoting literal strings. This is for portability to other databases that only support single quote string literals. (CVS 574) check-in: f795afd6 user: drh tags: trunk
12:56
Fix for ticket #45: Allow an UPDATE statement to change the INTEGER PRIMARY KEY to itself without triggering a constraint error. (CVS 573) check-in: 592da134 user: drh tags: trunk
11:38
Additional code cleanup resulting from a review of the new trigger code. (CVS 572) check-in: 37dbdd55 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.56 2002/05/21 11:38:11 drh Exp $
           15  +** $Id: insert.c,v 1.57 2002/05/21 12:56:43 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** This routine is call to handle SQL of the following forms:
    21     21   **
    22     22   **    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
   561    561       if( overrideError!=OE_Default ){
   562    562         onError = overrideError;
   563    563       }else if( onError==OE_Default ){
   564    564         onError = pParse->db->onError;
   565    565         if( onError==OE_Default ) onError = OE_Abort;
   566    566       }
   567    567       if( onError!=OE_Replace ){
          568  +      int jumpInst2;
          569  +      if( isUpdate ){
          570  +        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
          571  +        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
          572  +        jumpInst2 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
          573  +      }
   568    574         sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
   569    575         jumpInst = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
   570    576         switch( onError ){
   571    577           case OE_Rollback:
   572    578           case OE_Abort:
   573    579           case OE_Fail: {
   574    580             sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
................................................................................
   580    586             break;
   581    587           }
   582    588           default: assert(0);
   583    589         }
   584    590         contAddr = sqliteVdbeCurrentAddr(v);
   585    591         sqliteVdbeChangeP2(v, jumpInst, contAddr);
   586    592         if( isUpdate ){
          593  +        sqliteVdbeChangeP2(v, jumpInst2, contAddr);
   587    594           sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
   588    595           sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
   589    596         }
   590    597       }
   591    598     }
   592    599     extra = 0;
   593    600     for(extra=(-1), iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){

Changes to test/update.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the UPDATE statement.
    13     13   #
    14         -# $Id: update.test,v 1.8 2001/11/09 22:41:45 drh Exp $
           14  +# $Id: update.test,v 1.9 2002/05/21 12:56:44 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to update an non-existent table
    20     20   #
    21     21   do_test update-1.1 {
................................................................................
   406    406   do_test update-9.4 {
   407    407     set v [catch {execsql {
   408    408       UPDATE test1 SET f1=11 WHERE x(f1)=1025
   409    409     }} msg]
   410    410     lappend v $msg
   411    411   } {1 {no such function: x}}
   412    412   
   413         -
   414         -
   415         -
   416         -
          413  +# Try doing updates on a unique column where the value does not
          414  +# really change.
          415  +#
          416  +do_test update-10.1 {
          417  +  execsql {
          418  +    DROP TABLE test1;
          419  +    CREATE TABLE t1(
          420  +       a integer primary key,
          421  +       b UNIQUE, 
          422  +       c, d,
          423  +       e, f,
          424  +       UNIQUE(c,d)
          425  +    );
          426  +    INSERT INTO t1 VALUES(1,2,3,4,5,6);
          427  +    INSERT INTO t1 VALUES(2,3,4,4,6,7);
          428  +    SELECT * FROM t1
          429  +  }
          430  +} {1 2 3 4 5 6 2 3 4 4 6 7}
          431  +do_test update-10.2 {
          432  +  catchsql {
          433  +    UPDATE t1 SET a=1, e=9 WHERE f=6;
          434  +    SELECT * FROM t1;
          435  +  }
          436  +} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
          437  +do_test update-10.3 {
          438  +  catchsql {
          439  +    UPDATE t1 SET a=1, e=10 WHERE f=7;
          440  +    SELECT * FROM t1;
          441  +  }
          442  +} {1 {constraint failed}}
          443  +do_test update-10.4 {
          444  +  catchsql {
          445  +    SELECT * FROM t1;
          446  +  }
          447  +} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
          448  +do_test update-10.5 {
          449  +  catchsql {
          450  +    UPDATE t1 SET b=2, e=11 WHERE f=6;
          451  +    SELECT * FROM t1;
          452  +  }
          453  +} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
          454  +do_test update-10.6 {
          455  +  catchsql {
          456  +    UPDATE t1 SET b=2, e=12 WHERE f=7;
          457  +    SELECT * FROM t1;
          458  +  }
          459  +} {1 {constraint failed}}
          460  +do_test update-10.7 {
          461  +  catchsql {
          462  +    SELECT * FROM t1;
          463  +  }
          464  +} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
          465  +do_test update-10.8 {
          466  +  catchsql {
          467  +    UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
          468  +    SELECT * FROM t1;
          469  +  }
          470  +} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
          471  +do_test update-10.9 {
          472  +  catchsql {
          473  +    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
          474  +    SELECT * FROM t1;
          475  +  }
          476  +} {1 {constraint failed}}
          477  +do_test update-10.10 {
          478  +  catchsql {
          479  +    SELECT * FROM t1;
          480  +  }
          481  +} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
   417    482   
   418    483   finish_test