/ Check-in [086aa1c9]
Login

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

Overview
Comment:Provide a more informative error message when a uniqueness constraint fails. Ticket #419. (CVS 1068)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 086aa1c9922b7bf399b3ee8b73ba7353d126b119
User & Date: drh 2003-08-05 13:13:38
Context
2003-08-09
21:32
Add experimental date and time functions based on julian day number. (CVS 1069) check-in: a6197e20 user: drh tags: trunk
2003-08-05
13:13
Provide a more informative error message when a uniqueness constraint fails. Ticket #419. (CVS 1068) check-in: 086aa1c9 user: drh tags: trunk
2003-07-30
12:34
The {quote: SrcList} object was not being expanded correctly by a call to sqliteSrcListAppend() if the {quote: SrcList} had previously been duplicated by a call to sqliteSrcListDup(). Ticket #416. This check-in fixes that problem by keeping a separate nAlloc field on {quote: SrcList}. A similar change is made to {quote: IdList} and {quote: ExprList} to avoid future problems. (CVS 1067) check-in: da627325 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
...
813
814
815
816
817
818
819






















820
821
822
823
824
825
826
827
828
**    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.88 2003/06/04 16:24:39 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);

    /* Generate code that executes if the new index entry is not unique */
    switch( onError ){
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {






















        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
        sqliteVdbeChangeP3(v, -1, "uniqueness constraint failed", P3_STATIC);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
**    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.89 2003/08/05 13:13:38 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);

    /* Generate code that executes if the new index entry is not unique */
    switch( onError ){
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        int j, n1, n2;
        char zErrMsg[200];
        strcpy(zErrMsg, pIdx->nColumn>1 ? "columns " : "column ");
        n1 = strlen(zErrMsg);
        for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
          char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
          n2 = strlen(zCol);
          if( j>0 ){
            strcpy(&zErrMsg[n1], ", ");
            n1 += 2;
          }
          if( n1+n2>sizeof(zErrMsg)-30 ){
            strcpy(&zErrMsg[n1], "...");
            n1 += 3;
            break;
          }else{
            strcpy(&zErrMsg[n1], zCol);
            n1 += n2;
          }
        }
        strcpy(&zErrMsg[n1], 
            pIdx->nColumn>1 ? " are not unique" : " is not unique");
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
        sqliteVdbeChangeP3(v, -1, sqliteStrDup(zErrMsg), P3_DYNAMIC);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;

Changes to test/capi2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
#    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 script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.9 2003/07/09 00:28:15 drh Exp $
#

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

# Check basic functionality
#
................................................................................
do_test capi2-3.13 {
  set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,3,4)} TAIL]
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.13b {db changes} {0}
do_test capi2-3.14 {
  list [catch {sqlite_finalize $VM} msg] [set msg]
} {1 {(19) uniqueness constraint failed}}
do_test capi2-3.15 {
  set VM [sqlite_compile $DB {CREATE TABLE t2(a NOT NULL, b)} TAIL]
  set TAIL
} {}
do_test capi2-3.16 {
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 0 {} {}}
................................................................................
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 12 {x counter}}
do_test capi2-6.27 {
  catchsql {
    INSERT INTO t1 VALUES(2,4,5);
    SELECT * FROM t1;
  }
} {1 {uniqueness constraint failed}}
do_test capi2-6.28 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 13 {x counter}}
do_test capi2-6.99 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}
catchsql {ROLLBACK}







|







 







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
#    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 script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.10 2003/08/05 13:13:38 drh Exp $
#

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

# Check basic functionality
#
................................................................................
do_test capi2-3.13 {
  set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,3,4)} TAIL]
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.13b {db changes} {0}
do_test capi2-3.14 {
  list [catch {sqlite_finalize $VM} msg] [set msg]
} {1 {(19) column a is not unique}}
do_test capi2-3.15 {
  set VM [sqlite_compile $DB {CREATE TABLE t2(a NOT NULL, b)} TAIL]
  set TAIL
} {}
do_test capi2-3.16 {
  list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_DONE 0 {} {}}
................................................................................
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 12 {x counter}}
do_test capi2-6.27 {
  catchsql {
    INSERT INTO t1 VALUES(2,4,5);
    SELECT * FROM t1;
  }
} {1 {column a is not unique}}
do_test capi2-6.28 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 13 {x counter}}
do_test capi2-6.99 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}
catchsql {ROLLBACK}

Changes to test/conflict.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
...
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.18 2003/06/15 23:42:25 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {uniqueness constraint failed}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
      INSERT INTO t1 SELECT * FROM t2;
................................................................................
  }
} {0 {1 1 1 1 1 2 2 2 2 2}}
do_test conflict-9.5 {
  catchsql {
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.6 {
  catchsql {
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.7 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.8 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {2}
do_test conflict-9.9 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.10 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {3}
do_test conflict-9.11 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.12 {
  catchsql {
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.13 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.14 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {4}
do_test conflict-9.15 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.16 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.17 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.18 {
  catchsql {
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.19 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.20 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.21 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {uniqueness constraint failed}}
do_test conflict-9.22 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.23 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,1,3,3);







|







 







|







 







|





|







|











|









|





|







|











|









|





|







|











|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
...
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.19 2003/08/05 13:13:39 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {column a is not unique}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
      INSERT INTO t1 SELECT * FROM t2;
................................................................................
  }
} {0 {1 1 1 1 1 2 2 2 2 2}}
do_test conflict-9.5 {
  catchsql {
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.6 {
  catchsql {
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.7 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.8 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {2}
do_test conflict-9.9 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {column b is not unique}}
do_test conflict-9.10 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {3}
do_test conflict-9.11 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.12 {
  catchsql {
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.13 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.14 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {4}
do_test conflict-9.15 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {column d is not unique}}
do_test conflict-9.16 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.17 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.18 {
  catchsql {
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.19 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.20 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.21 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {column e is not unique}}
do_test conflict-9.22 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.23 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,1,3,3);

Changes to test/copy.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
#    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 the COPY statement.
#
# $Id: copy.test,v 1.14 2003/06/02 22:50:27 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
................................................................................
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {1 {uniqueness constraint failed}}
do_test copy-5.3 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
#    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 the COPY statement.
#
# $Id: copy.test,v 1.15 2003/08/05 13:13:39 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
................................................................................
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {1 {column b is not unique}}
do_test copy-5.3 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;

Changes to test/memdb.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
#    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 script is in-memory database backend.
#
# $Id: memdb.test,v 1.5 2003/06/15 23:42:25 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
................................................................................
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {uniqueness constraint failed}}
  do_test memdb-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
      INSERT INTO t1 SELECT * FROM t2;







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
#    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 script is in-memory database backend.
#
# $Id: memdb.test,v 1.6 2003/08/05 13:13:39 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
................................................................................
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {column a is not unique}}
  do_test memdb-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
      INSERT INTO t1 SELECT * FROM t2;

Changes to test/misc1.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.22 2003/06/03 01:47:12 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#
................................................................................
    SELECT * FROM t5 ORDER BY a;
  }
} {1 2 3}
do_test misc1-7.4 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,4);
  }
} {1 {uniqueness constraint failed}}
do_test misc1-7.5 {
  catchsql {
    INSERT INTO t5 VALUES(0,2,4);
  }
} {0 {}}
do_test misc1-7.6 {
  execsql {







|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.23 2003/08/05 13:13:39 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#
................................................................................
    SELECT * FROM t5 ORDER BY a;
  }
} {1 2 3}
do_test misc1-7.4 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,4);
  }
} {1 {columns a, b are not unique}}
do_test misc1-7.5 {
  catchsql {
    INSERT INTO t5 VALUES(0,2,4);
  }
} {0 {}}
do_test misc1-7.6 {
  execsql {

Changes to test/trigger2.test.

468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
...
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
...
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trigger2-6.1f {
  execsql {
................................................................................
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trigger2-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}
execsql {DELETE FROM tbl}

................................................................................
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trigger2-6.2f.1 {
  execsql {
................................................................................
    SELECT * FROM tbl;
  }
} {1 3 10 2 3 4}
do_test trigger2-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;







|









|







 







|







 







|









|







 







|







468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
...
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
...
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {column a is not unique}}
do_test trigger2-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {column a is not unique}}
do_test trigger2-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trigger2-6.1f {
  execsql {
................................................................................
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trigger2-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {column a is not unique}}
do_test trigger2-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}
execsql {DELETE FROM tbl}

................................................................................
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {column a is not unique}}
do_test trigger2-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {column a is not unique}}
do_test trigger2-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trigger2-6.2f.1 {
  execsql {
................................................................................
    SELECT * FROM tbl;
  }
} {1 3 10 2 3 4}
do_test trigger2-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {column a is not unique}}
do_test trigger2-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;

Changes to test/unique.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
..
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
...
194
195
196
197
198
199
200































201
#    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 the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
# $Id: unique.test,v 1.6 2003/06/15 23:42:25 drh Exp $

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

# Try to create a table with two primary keys.
# (This is allowed in SQLite even that it is not valid SQL)
#
................................................................................
    INSERT INTO t1(a,b,c) VALUES(1,2,3)
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {uniqueness constraint failed}}
do_test unique-1.4 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.5 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(3,2,4)
  }
} {1 {uniqueness constraint failed}}
do_test unique-1.6 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.7 {
  catchsql {
................................................................................
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.3 {
  catchsql {
    INSERT INTO t2 VALUES(1,5);
  }
} {1 {uniqueness constraint failed}}
do_test unique-2.4 {
  catchsql {
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.5 {
  catchsql {
................................................................................
  }
} {0 {1 2 3 4 1 2 3 5}}
do_test unique-3.4 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {1 {uniqueness constraint failed}}
integrity_check unique-3.5

# Make sure NULLs are distinct as far as the UNIQUE tests are
# concerned.
#
do_test unique-4.1 {
  execsql {
................................................................................
do_test unique-4.5 {
  execsql {
    SELECT * FROM t4
  }
} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
integrity_check unique-4.6
































finish_test







|







 







|









|







 







|







 







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
..
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
...
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
#    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 the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
# $Id: unique.test,v 1.7 2003/08/05 13:13:39 drh Exp $

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

# Try to create a table with two primary keys.
# (This is allowed in SQLite even that it is not valid SQL)
#
................................................................................
    INSERT INTO t1(a,b,c) VALUES(1,2,3)
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {column a is not unique}}
do_test unique-1.4 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.5 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(3,2,4)
  }
} {1 {column b is not unique}}
do_test unique-1.6 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.7 {
  catchsql {
................................................................................
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.3 {
  catchsql {
    INSERT INTO t2 VALUES(1,5);
  }
} {1 {column a is not unique}}
do_test unique-2.4 {
  catchsql {
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.5 {
  catchsql {
................................................................................
  }
} {0 {1 2 3 4 1 2 3 5}}
do_test unique-3.4 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {1 {columns a, c, d are not unique}}
integrity_check unique-3.5

# Make sure NULLs are distinct as far as the UNIQUE tests are
# concerned.
#
do_test unique-4.1 {
  execsql {
................................................................................
do_test unique-4.5 {
  execsql {
    SELECT * FROM t4
  }
} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
integrity_check unique-4.6

# Test the error message generation logic.  In particular, make sure we
# do not overflow the static buffer used to generate the error message.
#
do_test unique-5.1 {
  execsql {
    CREATE TABLE t5(
      first_column_with_long_name,
      second_column_with_long_name,
      third_column_with_long_name,
      fourth_column_with_long_name,
      fifth_column_with_long_name,
      sixth_column_with_long_name,
      UNIQUE(
        first_column_with_long_name,
        second_column_with_long_name,
        third_column_with_long_name,
        fourth_column_with_long_name,
        fifth_column_with_long_name,
        sixth_column_with_long_name
      )
    );
    INSERT INTO t5 VALUES(1,2,3,4,5,6);
    SELECT * FROM t5;
  }
} {1 2 3 4 5 6}
do_test unique-5.2 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,3,4,5,6);
  }
} {1 {columns first_column_with_long_name, second_column_with_long_name, third_column_with_long_name, fourth_column_with_long_name, fifth_column_with_long_name, ... are not unique}}

finish_test

Changes to test/update.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
...
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
#    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 the UPDATE statement.
#
# $Id: update.test,v 1.13 2003/06/15 23:42:25 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  }
} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
do_test update-10.6 {
  catchsql {
    UPDATE t1 SET b=2, e=12 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {uniqueness constraint failed}}
do_test update-10.7 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
do_test update-10.8 {
  catchsql {
................................................................................
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
do_test update-10.9 {
  catchsql {
    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {uniqueness constraint failed}}
do_test update-10.10 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}

# Make sure we can handle a subquery in the where clause.







|







 







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
...
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
#    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 the UPDATE statement.
#
# $Id: update.test,v 1.14 2003/08/05 13:13:39 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  }
} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
do_test update-10.6 {
  catchsql {
    UPDATE t1 SET b=2, e=12 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {column b is not unique}}
do_test update-10.7 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
do_test update-10.8 {
  catchsql {
................................................................................
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
do_test update-10.9 {
  catchsql {
    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {columns c, d are not unique}}
do_test update-10.10 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}

# Make sure we can handle a subquery in the where clause.