/ Check-in [c0d20fa9]
Login

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

Overview
Comment:Fix CHECK constraints to use alternative conflict resolution. Ticket #1717. (CVS 3137)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:c0d20fa97f94313e6b7ee5b4baa236ef5b664d76
User & Date: drh 2006-03-15 16:26:10
Context
2006-03-15
22:44
Performance improvement in sqlite3VdbeAddOp(). (CVS 3138) check-in: 6bcbe8fc user: drh tags: trunk
16:26
Fix CHECK constraints to use alternative conflict resolution. Ticket #1717. (CVS 3137) check-in: c0d20fa9 user: drh tags: trunk
2006-03-14
13:10
Set and clear the full_column_name flag using direct invocation rather than the execsql tcl command so that the select1 test works with the serialized statement extension. (CVS 3136) check-in: 4d70013a 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
...
870
871
872
873
874
875
876





877

878
879
880
881
882
883
884
**    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.163 2006/03/06 20:55:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
  if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){
    int allOk = sqlite3VdbeMakeLabel(v);
    assert( pParse->ckOffset==0 );
    pParse->ckOffset = nCol;
    sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 1);
    assert( pParse->ckOffset==nCol );
    pParse->ckOffset = 0;





    sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, OE_Abort);

    sqlite3VdbeResolveLabel(v, allOk);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.







|







 







>
>
>
>
>
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
**    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.164 2006/03/15 16:26:10 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
  if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){
    int allOk = sqlite3VdbeMakeLabel(v);
    assert( pParse->ckOffset==0 );
    pParse->ckOffset = nCol;
    sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 1);
    assert( pParse->ckOffset==nCol );
    pParse->ckOffset = 0;
    onError = overrideError!=OE_Default ? overrideError : OE_Abort;
    if( onError==OE_Ignore || onError==OE_Replace ){
      sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
    }else{
      sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
    }
    sqlite3VdbeResolveLabel(v, allOk);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.

Changes to test/check.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
290
291
292
293
294
295
296
297







298





299





































300
#    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.  The
# focus of this file is testing CHECK constraints
#
# $Id: check.test,v 1.8 2006/01/17 09:35:02 danielk1977 Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
................................................................................
do_test check-5.2 {
  catchsql {
    CREATE TABLE t5(x, y,
      CHECK( x*y<? )
    );
  }
} {1 {parameters prohibited in CHECK constraints}}




















































finish_test







|







 








>
>
>
>
>
>
>
|
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
#    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.  The
# focus of this file is testing CHECK constraints
#
# $Id: check.test,v 1.9 2006/03/15 16:26:10 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
................................................................................
do_test check-5.2 {
  catchsql {
    CREATE TABLE t5(x, y,
      CHECK( x*y<? )
    );
  }
} {1 {parameters prohibited in CHECK constraints}}

do_test check-6.1 {
  execsql {SELECT * FROM t1}
} {4 11.0}
do_test check-6.2 {
  execsql {
    UPDATE OR IGNORE t1 SET x=5;
    SELECT * FROM t1;
  }
} {4 11.0}
do_test check-6.3 {
  execsql {
    INSERT OR IGNORE INTO t1 VALUES(5,4.0);
    SELECT * FROM t1;
  }
} {4 11.0}
do_test check-6.4 {
  execsql {
    INSERT OR IGNORE INTO t1 VALUES(2,20.0);
    SELECT * FROM t1;
  }
} {4 11.0 2 20.0}
do_test check-6.5 {
  catchsql {
    UPDATE OR FAIL t1 SET x=7-x, y=y+1;
  }
} {1 {constraint failed}}
do_test check-6.6 {
  execsql {
    SELECT * FROM t1;
  }
} {3 12.0 2 20.0}
do_test check-6.7 {
  catchsql {
    BEGIN;
    INSERT INTO t1 VALUES(1,30.0);
    INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
  }
} {1 {constraint failed}}
do_test check-6.8 {
  catchsql {
    COMMIT;
  }
} {1 {cannot commit - no transaction is active}}
do_test check-6.9 {
  execsql {
    SELECT * FROM t1
  }
} {3 12.0 2 20.0}



finish_test

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
....
1517
1518
1519
1520
1521
1522
1523
1524

1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.110 2006/02/15 17:30:31 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}
................................................................................
<dt><b>REPLACE</b></dt>
<dd><p>When a UNIQUE constraint violation occurs, the pre-existing rows
that are causing the constraint violation are removed prior to inserting
or updating the current row.  Thus the insert or update always occurs.
The command continues executing normally.  No error is returned.
If a NOT NULL constraint violation occurs, the NULL value is replaced
by the default value for that column.  If the column has no default
value, then the ABORT algorithm is used.</p>


<p>When this conflict resolution strategy deletes rows in order to
satisfy a constraint, it does not invoke delete triggers on those
rows.  But that may change in a future release.</p>
</dl>

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
}




|







 







|
>



|







1
2
3
4
5
6
7
8
9
10
11
....
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.111 2006/03/15 16:26:10 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}
................................................................................
<dt><b>REPLACE</b></dt>
<dd><p>When a UNIQUE constraint violation occurs, the pre-existing rows
that are causing the constraint violation are removed prior to inserting
or updating the current row.  Thus the insert or update always occurs.
The command continues executing normally.  No error is returned.
If a NOT NULL constraint violation occurs, the NULL value is replaced
by the default value for that column.  If the column has no default
value, then the ABORT algorithm is used.  If a CHECK constraint violation
occurs then the IGNORE algorithm is used.</p>

<p>When this conflict resolution strategy deletes rows in order to
satisfy a constraint, it does not invoke delete triggers on those
rows.  This behavior might change in a future release.</p>
</dl>

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
}