/ Check-in [8229b5f6]
Login

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

Overview
Comment:More bug fixes in the ON CONFLICT enhancement. (CVS 357)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:8229b5f6a348a56432a4a609ee125520c5831973
User & Date: drh 2002-01-30 00:54:56
Context
2002-01-30
04:32
Better testing of the ON CONFLICT logic. (CVS 358) check-in: 9bbddb8e user: drh tags: trunk
00:54
More bug fixes in the ON CONFLICT enhancement. (CVS 357) check-in: 8229b5f6 user: drh tags: trunk
2002-01-29
23:07
The new ON CONFLICT logic is in and passes the legacy tests. But the new capabilities have not been tested and are likely broken. (CVS 356) check-in: ac8a4189 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.35 2002/01/29 23:07:02 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    onError = pIdx->onError;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+2, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+extra+2+recnoChng, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      case OE_Replace: {
        sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        sqliteGenerateRowDelete(v, pTab, base);
        if( isUpdate ){
          sqliteVdbeAddOp(v, OP_Dup, nCol+extra+recnoChng, 1);
          sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        }
        seenReplace = 1;
        break;







|







 







|













<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498

499
500
501
502
503
504
505
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.36 2002/01/30 00:54:56 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    onError = pIdx->onError;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+recnoChng, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+extra+2+recnoChng, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      case OE_Replace: {

        sqliteGenerateRowDelete(v, pTab, base);
        if( isUpdate ){
          sqliteVdbeAddOp(v, OP_Dup, nCol+extra+recnoChng, 1);
          sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        }
        seenReplace = 1;
        break;

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
1207
1208
1209
1210
1211
1212
1213

1214
1215
1216
1217
1218
1219
1220
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.110 2002/01/29 23:07:02 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
}

/* Opcode: Pop P1 * *
**
** P1 elements are popped off of the top of stack and discarded.
*/
case OP_Pop: {

  PopStack(p, pOp->p1);
  break;
}

/* Opcode: Dup P1 P2 *
**
** A copy of the P1-th element of the stack 







|







 







>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.111 2002/01/30 00:54:56 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
}

/* Opcode: Pop P1 * *
**
** P1 elements are popped off of the top of stack and discarded.
*/
case OP_Pop: {
  assert( p->tos+1>=pOp->p1 );
  PopStack(p, pOp->p1);
  break;
}

/* Opcode: Dup P1 P2 *
**
** A copy of the P1-th element of the stack 

Added test/conflict.test.









































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
# 2002 January 29
#
# 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 implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.1 2002/01/30 00:54:57 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with three fields, two of which must be
# UNIQUE.
#
do_test conflict-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c, UNIQUE(a,b));
    INSERT INTO t1 VALUES(1,2,3);
    SELECT c FROM t1 ORDER BY c;
  }
} {3}
do_test conflict-1.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-1.3 {
  catchsql {
    INSERT ON CONFLICT IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-1.4 {
  catchsql {
    INSERT ON CONFLICT REPLACE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-1.5 {
  catchsql {
    INSERT ON CONFLICT ABORT INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-1.6 {
  catchsql {
    INSERT IGNORE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-1.7 {
  catchsql {
    INSERT REPLACE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 5}
do_test conflict-1.8 {
  catchsql {
    INSERT ON CONFLICT ABORT INTO t1 VALUES(1,2,6);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}

do_test conflict-1.9 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(1,2,11);
    INSERT INTO t2 VALUES(1,2,12);
    INSERT INTO t2 VALUES(1,2,13);
    INSERT INTO t2 VALUES(1,2,14);
    INSERT INTO t2 VALUES(1,3,21);
    INSERT INTO t2 VALUES(1,3,22);
    INSERT INTO t2 VALUES(1,3,23);
    INSERT INTO t2 VALUES(1,3,24);
    COMMIT;
    SELECT count(*) FROM t2;
  }
} 8
do_test conflict-1.10 {
  catchsql {
    INSERT IGNORE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {5 21}}
do_test conflict-1.11 {
  catchsql {
    INSERT REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

###### Fix me!
do_test conflict-1.12 {
  catchsql {
    INSERT REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c DESC;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

do_test conflict-1.13 {
  execsql {
    BEGIN;
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(1,3,4);
    INSERT INTO t1 VALUES(2,3,5);
    COMMIT;
    SELECT * FROM t1 ORDER BY c;
  }
} {1 2 3 1 3 4 2 3 5}
do_test conflict-1.14 {
  catchsql {
    UPDATE ON CONFLICT ABORT t1 SET b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-1.15 {
  catchsql {
    UPDATE t1 SET b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-1.16 {
  catchsql {
    UPDATE ON CONFLICT IGNORE t1 SET b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 2 3 1 3 4 2 3 5}}
do_test conflict-1.17 {
  catchsql {
    UPDATE ON CONFLICT REPLACE t1 SET b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 3 3 2 3 5}}


finish_test