SQLite
Check-in [939d8282748b00a7032cdd72e5d3bf3086ebfc97]
Not logged in
Overview
SHA1 Hash:939d8282748b00a7032cdd72e5d3bf3086ebfc97
Date: 2013-02-11 13:47:39
User: drh
Comment:Add extended error codes for all SQLITE_CONSTRAINT errors.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c

2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
....
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
....
3688
3689
3690
3691
3692
3693
3694
3695






3696

3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
  sqlite3VdbeJumpHere(v, addr1);
  addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0);
  if( pIndex->onError!=OE_None ){
    int j2 = sqlite3VdbeCurrentAddr(v) + 3;
    sqlite3VdbeAddOp2(v, OP_Goto, 0, j2);
    addr2 = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp3(v, OP_SorterCompare, iSorter, j2, regRecord);
    sqlite3HaltConstraint(
        pParse, OE_Abort, "indexed columns are not unique", P4_STATIC
    );
  }else{
    addr2 = sqlite3VdbeCurrentAddr(v);
  }
  sqlite3VdbeAddOp2(v, OP_SorterData, iSorter, regRecord);
  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
................................................................................
    ** (made available to the compiler for reuse) using 
    ** sqlite3ReleaseTempRange(). So in some ways having the OP_IsUnique
    ** opcode use the values stored within seems dangerous. However, since
    ** we can be sure that no other temp registers have been allocated
    ** since sqlite3ReleaseTempRange() was called, it is safe to do so.
    */
    sqlite3VdbeAddOp4(v, OP_IsUnique, iIdx, j2, regRowid, pRegKey, P4_INT32);
    sqlite3HaltConstraint(
        pParse, OE_Abort, "indexed columns are not unique", P4_STATIC);
  }
  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 0);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
#endif
  sqlite3ReleaseTempReg(pParse, regRecord);
  sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2);
  sqlite3VdbeJumpHere(v, addr1);
................................................................................
}

/*
** Code an OP_Halt that causes the vdbe to return an SQLITE_CONSTRAINT
** error. The onError parameter determines which (if any) of the statement
** and/or current transaction is rolled back.
*/
void sqlite3HaltConstraint(Parse *pParse, int onError, char *p4, int p4type){






  Vdbe *v = sqlite3GetVdbe(pParse);

  if( onError==OE_Abort ){
    sqlite3MayAbort(pParse);
  }
  sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, onError, 0, p4, p4type);
}

/*
** Check to see if pIndex uses the collating sequence pColl.  Return
** true if it does and false if it does not.
*/
#ifndef SQLITE_OMIT_REINDEX







|
|







 







|
|







 







|
>
>
>
>
>
>

>



|







2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
....
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
....
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
3711
3712
3713
3714
  sqlite3VdbeJumpHere(v, addr1);
  addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0);
  if( pIndex->onError!=OE_None ){
    int j2 = sqlite3VdbeCurrentAddr(v) + 3;
    sqlite3VdbeAddOp2(v, OP_Goto, 0, j2);
    addr2 = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp3(v, OP_SorterCompare, iSorter, j2, regRecord);
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_UNIQUE,
        OE_Abort, "indexed columns are not unique", P4_STATIC
    );
  }else{
    addr2 = sqlite3VdbeCurrentAddr(v);
  }
  sqlite3VdbeAddOp2(v, OP_SorterData, iSorter, regRecord);
  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
................................................................................
    ** (made available to the compiler for reuse) using 
    ** sqlite3ReleaseTempRange(). So in some ways having the OP_IsUnique
    ** opcode use the values stored within seems dangerous. However, since
    ** we can be sure that no other temp registers have been allocated
    ** since sqlite3ReleaseTempRange() was called, it is safe to do so.
    */
    sqlite3VdbeAddOp4(v, OP_IsUnique, iIdx, j2, regRowid, pRegKey, P4_INT32);
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_UNIQUE,
        "indexed columns are not unique", P4_STATIC);
  }
  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 0);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
#endif
  sqlite3ReleaseTempReg(pParse, regRecord);
  sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2);
  sqlite3VdbeJumpHere(v, addr1);
................................................................................
}

/*
** Code an OP_Halt that causes the vdbe to return an SQLITE_CONSTRAINT
** error. The onError parameter determines which (if any) of the statement
** and/or current transaction is rolled back.
*/
void sqlite3HaltConstraint(
  Parse *pParse,    /* Parsing context */
  int errCode,      /* extended error code */
  int onError,      /* Constraint type */
  char *p4,         /* Error message */
  int p4type        /* P4_STATIC or P4_TRANSIENT */
){
  Vdbe *v = sqlite3GetVdbe(pParse);
  assert( (errCode&0xff)==SQLITE_CONSTRAINT );
  if( onError==OE_Abort ){
    sqlite3MayAbort(pParse);
  }
  sqlite3VdbeAddOp4(v, OP_Halt, errCode, onError, 0, p4, p4type);
}

/*
** Check to see if pIndex uses the collating sequence pColl.  Return
** true if it does and false if it does not.
*/
#ifndef SQLITE_OMIT_REINDEX

Changes to src/expr.c

2931
2932
2933
2934
2935
2936
2937

2938
2939
2940
2941
2942
2943
2944
2945
        sqlite3MayAbort(pParse);
      }
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      if( pExpr->affinity==OE_Ignore ){
        sqlite3VdbeAddOp4(
            v, OP_Halt, SQLITE_OK, OE_Ignore, 0, pExpr->u.zToken,0);
      }else{

        sqlite3HaltConstraint(pParse, pExpr->affinity, pExpr->u.zToken, 0);
      }

      break;
    }
#endif
  }
  sqlite3ReleaseTempReg(pParse, regFree1);







>
|







2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
        sqlite3MayAbort(pParse);
      }
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      if( pExpr->affinity==OE_Ignore ){
        sqlite3VdbeAddOp4(
            v, OP_Halt, SQLITE_OK, OE_Ignore, 0, pExpr->u.zToken,0);
      }else{
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_TRIGGER,
                              pExpr->affinity, pExpr->u.zToken, 0);
      }

      break;
    }
#endif
  }
  sqlite3ReleaseTempReg(pParse, regFree1);

Changes to src/fkey.c

17
18
19
20
21
22
23
24

25
26
27
28
29
30
31
32
..
82
83
84
85
86
87
88
89

90
91
92
93
94
95
96
...
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
...
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
#ifndef SQLITE_OMIT_TRIGGER

/*
** Deferred and Immediate FKs
** --------------------------
**
** Foreign keys in SQLite come in two flavours: deferred and immediate.
** If an immediate foreign key constraint is violated, SQLITE_CONSTRAINT

** is returned and the current statement transaction rolled back. If a 
** deferred foreign key constraint is violated, no action is taken 
** immediately. However if the application attempts to commit the 
** transaction before fixing the constraint violation, the attempt fails.
**
** Deferred constraints are implemented using a simple counter associated
** with the database handle. The counter is set to zero each time a 
** database transaction is opened. Each time a statement is executed 
................................................................................
** If a delete caused by OR REPLACE violates an FK constraint, an exception
** is thrown, even if the FK constraint would be satisfied after the new 
** row is inserted.
**
** Immediate constraints are usually handled similarly. The only difference 
** is that the counter used is stored as part of each individual statement
** object (struct Vdbe). If, after the statement has run, its immediate
** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT

** and the statement transaction is rolled back. An exception is an INSERT
** statement that inserts a single row only (no triggers). In this case,
** instead of using a counter, an exception is thrown immediately if the
** INSERT violates a foreign key constraint. This is necessary as such
** an INSERT does not open a statement transaction.
**
** TODO: How should dropping a table be handled? How should renaming a 
................................................................................

  if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
    /* Special case: If this is an INSERT statement that will insert exactly
    ** one row into the table, raise a constraint immediately instead of
    ** incrementing a counter. This is necessary as the VM code is being
    ** generated for will not open a statement transaction.  */
    assert( nIncr==1 );
    sqlite3HaltConstraint(
        pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }
    sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  }
................................................................................
    pParse->disableTriggers = 0;

    /* If the DELETE has generated immediate foreign key constraint 
    ** violations, halt the VDBE and return an error at this point, before
    ** any modifications to the schema are made. This is because statement
    ** transactions are not able to rollback schema changes.  */
    sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
    sqlite3HaltConstraint(
        pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );

    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}







|
>
|







 







|
>







 







|
|







 







|
|







17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
...
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
...
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
#ifndef SQLITE_OMIT_TRIGGER

/*
** Deferred and Immediate FKs
** --------------------------
**
** Foreign keys in SQLite come in two flavours: deferred and immediate.
** If an immediate foreign key constraint is violated,
** SQLITE_CONSTRAINT_FOREIGNKEY is returned and the current
** statement transaction rolled back. If a 
** deferred foreign key constraint is violated, no action is taken 
** immediately. However if the application attempts to commit the 
** transaction before fixing the constraint violation, the attempt fails.
**
** Deferred constraints are implemented using a simple counter associated
** with the database handle. The counter is set to zero each time a 
** database transaction is opened. Each time a statement is executed 
................................................................................
** If a delete caused by OR REPLACE violates an FK constraint, an exception
** is thrown, even if the FK constraint would be satisfied after the new 
** row is inserted.
**
** Immediate constraints are usually handled similarly. The only difference 
** is that the counter used is stored as part of each individual statement
** object (struct Vdbe). If, after the statement has run, its immediate
** constraint counter is greater than zero,
** it returns SQLITE_CONSTRAINT_FOREIGNKEY
** and the statement transaction is rolled back. An exception is an INSERT
** statement that inserts a single row only (no triggers). In this case,
** instead of using a counter, an exception is thrown immediately if the
** INSERT violates a foreign key constraint. This is necessary as such
** an INSERT does not open a statement transaction.
**
** TODO: How should dropping a table be handled? How should renaming a 
................................................................................

  if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
    /* Special case: If this is an INSERT statement that will insert exactly
    ** one row into the table, raise a constraint immediately instead of
    ** incrementing a counter. This is necessary as the VM code is being
    ** generated for will not open a statement transaction.  */
    assert( nIncr==1 );
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
        OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }
    sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  }
................................................................................
    pParse->disableTriggers = 0;

    /* If the DELETE has generated immediate foreign key constraint 
    ** violations, halt the VDBE and return an error at this point, before
    ** any modifications to the schema are made. This is because statement
    ** transactions are not able to rollback schema changes.  */
    sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
        OE_Abort, "foreign key constraint failed", P4_STATIC
    );

    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}

Changes to src/insert.c

1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
....
1281
1282
1283
1284
1285
1286
1287

1288
1289
1290
1291
1292
1293
1294
1295
....
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
....
1440
1441
1442
1443
1444
1445
1446
1447

1448
1449
1450
1451
1452
1453
1454
....
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
    switch( onError ){
      case OE_Abort:
        sqlite3MayAbort(pParse);
      case OE_Rollback:
      case OE_Fail: {
        char *zMsg;
        sqlite3VdbeAddOp3(v, OP_HaltIfNull,
                                  SQLITE_CONSTRAINT, onError, regData+i);
        zMsg = sqlite3MPrintf(db, "%s.%s may not be NULL",
                              pTab->zName, pTab->aCol[i].zName);
        sqlite3VdbeChangeP4(v, -1, zMsg, P4_DYNAMIC);
        break;
      }
      case OE_Ignore: {
        sqlite3VdbeAddOp2(v, OP_IsNull, regData+i, ignoreDest);
................................................................................
        char *zConsName = pCheck->a[i].zName;
        if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */
        if( zConsName ){
          zConsName = sqlite3MPrintf(db, "constraint %s failed", zConsName);
        }else{
          zConsName = 0;
        }

        sqlite3HaltConstraint(pParse, onError, zConsName, P4_DYNAMIC);
      }
      sqlite3VdbeResolveLabel(v, allOk);
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
................................................................................
      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
      }
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqlite3HaltConstraint(
          pParse, onError, "PRIMARY KEY must be unique", P4_STATIC);
        break;
      }
      case OE_Replace: {
        /* If there are DELETE triggers on this table and the
        ** recursive-triggers flag is set, call GenerateRowDelete() to
        ** remove the conflicting row from the table. This will fire
        ** the triggers and remove both the table and index b-tree entries.
................................................................................
          sqlite3StrAccumAppend(&errMsg, zSep, -1);
          zSep = ", ";
          sqlite3StrAccumAppend(&errMsg, zCol, -1);
        }
        sqlite3StrAccumAppend(&errMsg,
            pIdx->nColumn>1 ? " are not unique" : " is not unique", -1);
        zErr = sqlite3StrAccumFinish(&errMsg);
        sqlite3HaltConstraint(pParse, onError, zErr, 0);

        sqlite3DbFree(errMsg.db, zErr);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
................................................................................
  sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
  emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
  regData = sqlite3GetTempReg(pParse);
  regRowid = sqlite3GetTempReg(pParse);
  if( pDest->iPKey>=0 ){
    addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
    addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
    sqlite3HaltConstraint(
        pParse, onError, "PRIMARY KEY must be unique", P4_STATIC);
    sqlite3VdbeJumpHere(v, addr2);
    autoIncStep(pParse, regAutoinc, regRowid);
  }else if( pDest->pIndex==0 ){
    addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
  }else{
    addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
    assert( (pDest->tabFlags & TF_Autoincrement)==0 );







|







 







>
|







 







|
|







 







|
>







 







|
|







1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
....
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
....
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
....
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
....
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
    switch( onError ){
      case OE_Abort:
        sqlite3MayAbort(pParse);
      case OE_Rollback:
      case OE_Fail: {
        char *zMsg;
        sqlite3VdbeAddOp3(v, OP_HaltIfNull,
                          SQLITE_CONSTRAINT_NOTNULL, onError, regData+i);
        zMsg = sqlite3MPrintf(db, "%s.%s may not be NULL",
                              pTab->zName, pTab->aCol[i].zName);
        sqlite3VdbeChangeP4(v, -1, zMsg, P4_DYNAMIC);
        break;
      }
      case OE_Ignore: {
        sqlite3VdbeAddOp2(v, OP_IsNull, regData+i, ignoreDest);
................................................................................
        char *zConsName = pCheck->a[i].zName;
        if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */
        if( zConsName ){
          zConsName = sqlite3MPrintf(db, "constraint %s failed", zConsName);
        }else{
          zConsName = 0;
        }
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
                              onError, zConsName, P4_DYNAMIC);
      }
      sqlite3VdbeResolveLabel(v, allOk);
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
................................................................................
      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
      }
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_PRIMARYKEY,
           onError, "PRIMARY KEY must be unique", P4_STATIC);
        break;
      }
      case OE_Replace: {
        /* If there are DELETE triggers on this table and the
        ** recursive-triggers flag is set, call GenerateRowDelete() to
        ** remove the conflicting row from the table. This will fire
        ** the triggers and remove both the table and index b-tree entries.
................................................................................
          sqlite3StrAccumAppend(&errMsg, zSep, -1);
          zSep = ", ";
          sqlite3StrAccumAppend(&errMsg, zCol, -1);
        }
        sqlite3StrAccumAppend(&errMsg,
            pIdx->nColumn>1 ? " are not unique" : " is not unique", -1);
        zErr = sqlite3StrAccumFinish(&errMsg);
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_UNIQUE,
                              onError, zErr, 0);
        sqlite3DbFree(errMsg.db, zErr);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
................................................................................
  sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
  emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
  regData = sqlite3GetTempReg(pParse);
  regRowid = sqlite3GetTempReg(pParse);
  if( pDest->iPKey>=0 ){
    addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
    addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_PRIMARYKEY,
        onError, "PRIMARY KEY must be unique", P4_STATIC);
    sqlite3VdbeJumpHere(v, addr2);
    autoIncStep(pParse, regAutoinc, regRowid);
  }else if( pDest->pIndex==0 ){
    addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
  }else{
    addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
    assert( (pDest->tabFlags & TF_Autoincrement)==0 );

Changes to src/sqlite.h.in

475
476
477
478
479
480
481









482
483
484
485
486
487
488
#define SQLITE_CANTOPEN_NOTEMPDIR      (SQLITE_CANTOPEN | (1<<8))
#define SQLITE_CANTOPEN_ISDIR          (SQLITE_CANTOPEN | (2<<8))
#define SQLITE_CANTOPEN_FULLPATH       (SQLITE_CANTOPEN | (3<<8))
#define SQLITE_CORRUPT_VTAB            (SQLITE_CORRUPT | (1<<8))
#define SQLITE_READONLY_RECOVERY       (SQLITE_READONLY | (1<<8))
#define SQLITE_READONLY_CANTLOCK       (SQLITE_READONLY | (2<<8))
#define SQLITE_ABORT_ROLLBACK          (SQLITE_ABORT | (2<<8))










/*
** CAPI3REF: Flags For File Open Operations
**
** These bit values are intended for use in the
** 3rd parameter to the [sqlite3_open_v2()] interface and
** in the 4th parameter to the [sqlite3_vfs.xOpen] method.







>
>
>
>
>
>
>
>
>







475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
#define SQLITE_CANTOPEN_NOTEMPDIR      (SQLITE_CANTOPEN | (1<<8))
#define SQLITE_CANTOPEN_ISDIR          (SQLITE_CANTOPEN | (2<<8))
#define SQLITE_CANTOPEN_FULLPATH       (SQLITE_CANTOPEN | (3<<8))
#define SQLITE_CORRUPT_VTAB            (SQLITE_CORRUPT | (1<<8))
#define SQLITE_READONLY_RECOVERY       (SQLITE_READONLY | (1<<8))
#define SQLITE_READONLY_CANTLOCK       (SQLITE_READONLY | (2<<8))
#define SQLITE_ABORT_ROLLBACK          (SQLITE_ABORT | (2<<8))
#define SQLITE_CONSTRAINT_CHECK        (SQLITE_CONSTRAINT | (1<<8))
#define SQLITE_CONSTRAINT_COMMITHOOK   (SQLITE_CONSTRAINT | (2<<8))
#define SQLITE_CONSTRAINT_FOREIGNKEY   (SQLITE_CONSTRAINT | (3<<8))
#define SQLITE_CONSTRAINT_FUNCTION     (SQLITE_CONSTRAINT | (4<<8))
#define SQLITE_CONSTRAINT_NOTNULL      (SQLITE_CONSTRAINT | (5<<8))
#define SQLITE_CONSTRAINT_PRIMARYKEY   (SQLITE_CONSTRAINT | (6<<8))
#define SQLITE_CONSTRAINT_TRIGGER      (SQLITE_CONSTRAINT | (7<<8))
#define SQLITE_CONSTRAINT_UNIQUE       (SQLITE_CONSTRAINT | (8<<8))
#define SQLITE_CONSTRAINT_VTAB         (SQLITE_CONSTRAINT | (9<<8))

/*
** CAPI3REF: Flags For File Open Operations
**
** These bit values are intended for use in the
** 3rd parameter to the [sqlite3_open_v2()] interface and
** in the 4th parameter to the [sqlite3_vfs.xOpen] method.

Changes to src/sqliteInt.h

2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
void sqlite3GenerateConstraintChecks(Parse*,Table*,int,int,
                                     int*,int,int,int,int,int*);
void sqlite3CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);
int sqlite3OpenTableAndIndices(Parse*, Table*, int, int);
void sqlite3BeginWriteOperation(Parse*, int, int);
void sqlite3MultiWrite(Parse*);
void sqlite3MayAbort(Parse*);
void sqlite3HaltConstraint(Parse*, int, char*, int);
Expr *sqlite3ExprDup(sqlite3*,Expr*,int);
ExprList *sqlite3ExprListDup(sqlite3*,ExprList*,int);
SrcList *sqlite3SrcListDup(sqlite3*,SrcList*,int);
IdList *sqlite3IdListDup(sqlite3*,IdList*);
Select *sqlite3SelectDup(sqlite3*,Select*,int);
void sqlite3FuncDefInsert(FuncDefHash*, FuncDef*);
FuncDef *sqlite3FindFunction(sqlite3*,const char*,int,int,u8,u8);







|







2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
void sqlite3GenerateConstraintChecks(Parse*,Table*,int,int,
                                     int*,int,int,int,int,int*);
void sqlite3CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);
int sqlite3OpenTableAndIndices(Parse*, Table*, int, int);
void sqlite3BeginWriteOperation(Parse*, int, int);
void sqlite3MultiWrite(Parse*);
void sqlite3MayAbort(Parse*);
void sqlite3HaltConstraint(Parse*, int, int, char*, int);
Expr *sqlite3ExprDup(sqlite3*,Expr*,int);
ExprList *sqlite3ExprListDup(sqlite3*,ExprList*,int);
SrcList *sqlite3SrcListDup(sqlite3*,SrcList*,int);
IdList *sqlite3IdListDup(sqlite3*,IdList*);
Select *sqlite3SelectDup(sqlite3*,Select*,int);
void sqlite3FuncDefInsert(FuncDefHash*, FuncDef*);
FuncDef *sqlite3FindFunction(sqlite3*,const char*,int,int,u8,u8);

Changes to src/test1.c

134
135
136
137
138
139
140












141
142
143
144
145
146
147
    case SQLITE_FULL:                zName = "SQLITE_FULL";              break;
    case SQLITE_CANTOPEN:            zName = "SQLITE_CANTOPEN";          break;
    case SQLITE_PROTOCOL:            zName = "SQLITE_PROTOCOL";          break;
    case SQLITE_EMPTY:               zName = "SQLITE_EMPTY";             break;
    case SQLITE_SCHEMA:              zName = "SQLITE_SCHEMA";            break;
    case SQLITE_TOOBIG:              zName = "SQLITE_TOOBIG";            break;
    case SQLITE_CONSTRAINT:          zName = "SQLITE_CONSTRAINT";        break;












    case SQLITE_MISMATCH:            zName = "SQLITE_MISMATCH";          break;
    case SQLITE_MISUSE:              zName = "SQLITE_MISUSE";            break;
    case SQLITE_NOLFS:               zName = "SQLITE_NOLFS";             break;
    case SQLITE_AUTH:                zName = "SQLITE_AUTH";              break;
    case SQLITE_FORMAT:              zName = "SQLITE_FORMAT";            break;
    case SQLITE_RANGE:               zName = "SQLITE_RANGE";             break;
    case SQLITE_NOTADB:              zName = "SQLITE_NOTADB";            break;







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







134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
    case SQLITE_FULL:                zName = "SQLITE_FULL";              break;
    case SQLITE_CANTOPEN:            zName = "SQLITE_CANTOPEN";          break;
    case SQLITE_PROTOCOL:            zName = "SQLITE_PROTOCOL";          break;
    case SQLITE_EMPTY:               zName = "SQLITE_EMPTY";             break;
    case SQLITE_SCHEMA:              zName = "SQLITE_SCHEMA";            break;
    case SQLITE_TOOBIG:              zName = "SQLITE_TOOBIG";            break;
    case SQLITE_CONSTRAINT:          zName = "SQLITE_CONSTRAINT";        break;
    case SQLITE_CONSTRAINT_UNIQUE:   zName = "SQLITE_CONSTRAINT_UNIQUE"; break;
    case SQLITE_CONSTRAINT_TRIGGER:  zName = "SQLITE_CONSTRAINT_TRIGGER";break;
    case SQLITE_CONSTRAINT_FOREIGNKEY:
                                 zName = "SQLITE_CONSTRAINT_FOREIGNKEY"; break;
    case SQLITE_CONSTRAINT_CHECK:    zName = "SQLITE_CONSTRAINT_CHECK";  break;
    case SQLITE_CONSTRAINT_PRIMARYKEY:
                                 zName = "SQLITE_CONSTRAINT_PRIMARYKEY"; break;
    case SQLITE_CONSTRAINT_NOTNULL:  zName = "SQLITE_CONSTRAINT_NOTNULL";break;
    case SQLITE_CONSTRAINT_COMMITHOOK:
                                 zName = "SQLITE_CONSTRAINT_COMMITHOOK"; break;
    case SQLITE_CONSTRAINT_VTAB:     zName = "SQLITE_CONSTRAINT_VTAB";   break;
    case SQLITE_CONSTRAINT_FUNCTION: zName = "SQLITE_CONSTRAINT_FUNCTION";break;
    case SQLITE_MISMATCH:            zName = "SQLITE_MISMATCH";          break;
    case SQLITE_MISUSE:              zName = "SQLITE_MISUSE";            break;
    case SQLITE_NOLFS:               zName = "SQLITE_NOLFS";             break;
    case SQLITE_AUTH:                zName = "SQLITE_AUTH";              break;
    case SQLITE_FORMAT:              zName = "SQLITE_FORMAT";            break;
    case SQLITE_RANGE:               zName = "SQLITE_RANGE";             break;
    case SQLITE_NOTADB:              zName = "SQLITE_NOTADB";            break;

Changes to src/test_spellfix.c

2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
      ** cause zWord to be NULL, so we look at the "command" column to see
      ** what special actions to take */
      const char *zCmd = 
         (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]);
      if( zCmd==0 ){
        pVTab->zErrMsg = sqlite3_mprintf("%s.word may not be NULL",
                                         p->zTableName);
        return SQLITE_CONSTRAINT;
      }
      if( strcmp(zCmd,"reset")==0 ){
        /* Reset the  edit cost table (if there is one). */
        editDist3ConfigDelete(p->pConfig3);
        p->pConfig3 = 0;
        return SQLITE_OK;
      }







|







2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
      ** cause zWord to be NULL, so we look at the "command" column to see
      ** what special actions to take */
      const char *zCmd = 
         (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]);
      if( zCmd==0 ){
        pVTab->zErrMsg = sqlite3_mprintf("%s.word may not be NULL",
                                         p->zTableName);
        return SQLITE_CONSTRAINT_NOTNULL;
      }
      if( strcmp(zCmd,"reset")==0 ){
        /* Reset the  edit cost table (if there is one). */
        editDist3ConfigDelete(p->pConfig3);
        p->pConfig3 = 0;
        return SQLITE_OK;
      }

Changes to src/vdbe.c

865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
....
6059
6060
6061
6062
6063
6064
6065
6066
6067
6068
6069
6070
6071
6072
6073
    sqlite3_log(pOp->p1, "constraint failed at %d in [%s]", pc, p->zSql);
  }
  rc = sqlite3VdbeHalt(p);
  assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR );
  if( rc==SQLITE_BUSY ){
    p->rc = rc = SQLITE_BUSY;
  }else{
    assert( rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT );
    assert( rc==SQLITE_OK || db->nDeferredCons>0 );
    rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
  }
  goto vdbe_return;
}

/* Opcode: Integer P1 P2 * * *
................................................................................
    rc = pModule->xUpdate(pVtab, nArg, apArg, &rowid);
    db->vtabOnConflict = vtabOnConflict;
    importVtabErrMsg(p, pVtab);
    if( rc==SQLITE_OK && pOp->p1 ){
      assert( nArg>1 && apArg[0] && (apArg[0]->flags&MEM_Null) );
      db->lastRowid = lastRowid = rowid;
    }
    if( rc==SQLITE_CONSTRAINT && pOp->p4.pVtab->bConstraint ){
      if( pOp->p5==OE_Ignore ){
        rc = SQLITE_OK;
      }else{
        p->errorAction = ((pOp->p5==OE_Replace) ? OE_Abort : pOp->p5);
      }
    }else{
      p->nChange++;







|







 







|







865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
....
6059
6060
6061
6062
6063
6064
6065
6066
6067
6068
6069
6070
6071
6072
6073
    sqlite3_log(pOp->p1, "constraint failed at %d in [%s]", pc, p->zSql);
  }
  rc = sqlite3VdbeHalt(p);
  assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR );
  if( rc==SQLITE_BUSY ){
    p->rc = rc = SQLITE_BUSY;
  }else{
    assert( rc==SQLITE_OK || (p->rc&0xff)==SQLITE_CONSTRAINT );
    assert( rc==SQLITE_OK || db->nDeferredCons>0 );
    rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
  }
  goto vdbe_return;
}

/* Opcode: Integer P1 P2 * * *
................................................................................
    rc = pModule->xUpdate(pVtab, nArg, apArg, &rowid);
    db->vtabOnConflict = vtabOnConflict;
    importVtabErrMsg(p, pVtab);
    if( rc==SQLITE_OK && pOp->p1 ){
      assert( nArg>1 && apArg[0] && (apArg[0]->flags&MEM_Null) );
      db->lastRowid = lastRowid = rowid;
    }
    if( (rc&0xff)==SQLITE_CONSTRAINT && pOp->p4.pVtab->bConstraint ){
      if( pOp->p5==OE_Ignore ){
        rc = SQLITE_OK;
      }else{
        p->errorAction = ((pOp->p5==OE_Replace) ? OE_Abort : pOp->p5);
      }
    }else{
      p->nChange++;

Changes to src/vdbeaux.c

363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
....
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
....
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
....
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
....
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
  while( (pOp = opIterNext(&sIter))!=0 ){
    int opcode = pOp->opcode;
    if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename 
#ifndef SQLITE_OMIT_FOREIGN_KEY
     || (opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1) 
#endif
     || ((opcode==OP_Halt || opcode==OP_HaltIfNull) 
      && (pOp->p1==SQLITE_CONSTRAINT && pOp->p2==OE_Abort))
    ){
      hasAbort = 1;
      break;
    }
  }
  sqlite3DbFree(v->db, sIter.apSub);

................................................................................
    return rc;
  }

  /* If there are any write-transactions at all, invoke the commit hook */
  if( needXcommit && db->xCommitCallback ){
    rc = db->xCommitCallback(db->pCommitArg);
    if( rc ){
      return SQLITE_CONSTRAINT;
    }
  }

  /* The simple case - no more than one database file (not counting the
  ** TEMP database) has a transaction active.   There is no need for the
  ** master-journal.
  **
................................................................................
/*
** This function is called when a transaction opened by the database 
** handle associated with the VM passed as an argument is about to be 
** committed. If there are outstanding deferred foreign key constraint
** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK.
**
** If there are outstanding FK violations and this function returns 
** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT and write
** an error message to it. Then return SQLITE_ERROR.
*/
#ifndef SQLITE_OMIT_FOREIGN_KEY
int sqlite3VdbeCheckFk(Vdbe *p, int deferred){
  sqlite3 *db = p->db;
  if( (deferred && db->nDeferredCons>0) || (!deferred && p->nFkConstraint>0) ){
    p->rc = SQLITE_CONSTRAINT;
    p->errorAction = OE_Abort;
    sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed");
    return SQLITE_ERROR;
  }
  return SQLITE_OK;
}
#endif
................................................................................
      if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
        rc = sqlite3VdbeCheckFk(p, 1);
        if( rc!=SQLITE_OK ){
          if( NEVER(p->readOnly) ){
            sqlite3VdbeLeave(p);
            return SQLITE_ERROR;
          }
          rc = SQLITE_CONSTRAINT;
        }else{ 
          /* The auto-commit flag is true, the vdbe program was successful 
          ** or hit an 'OR FAIL' constraint and there are no deferred foreign
          ** key constraints to hold up the transaction. This means a commit 
          ** is required. */
          rc = vdbeCommit(db, p);
        }
................................................................................
    ** do so. If this operation returns an error, and the current statement
    ** error code is SQLITE_OK or SQLITE_CONSTRAINT, then promote the
    ** current statement error code.
    */
    if( eStatementOp ){
      rc = sqlite3VdbeCloseStatement(p, eStatementOp);
      if( rc ){
        if( p->rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT ){
          p->rc = rc;
          sqlite3DbFree(db, p->zErrMsg);
          p->zErrMsg = 0;
        }
        sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK);
        sqlite3CloseSavepoints(db);
        db->autoCommit = 1;







|







 







|







 







|
|





|







 







|







 







|







363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
....
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
....
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
....
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
....
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
  while( (pOp = opIterNext(&sIter))!=0 ){
    int opcode = pOp->opcode;
    if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename 
#ifndef SQLITE_OMIT_FOREIGN_KEY
     || (opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1) 
#endif
     || ((opcode==OP_Halt || opcode==OP_HaltIfNull) 
      && ((pOp->p1&0xff)==SQLITE_CONSTRAINT && pOp->p2==OE_Abort))
    ){
      hasAbort = 1;
      break;
    }
  }
  sqlite3DbFree(v->db, sIter.apSub);

................................................................................
    return rc;
  }

  /* If there are any write-transactions at all, invoke the commit hook */
  if( needXcommit && db->xCommitCallback ){
    rc = db->xCommitCallback(db->pCommitArg);
    if( rc ){
      return SQLITE_CONSTRAINT_COMMITHOOK;
    }
  }

  /* The simple case - no more than one database file (not counting the
  ** TEMP database) has a transaction active.   There is no need for the
  ** master-journal.
  **
................................................................................
/*
** This function is called when a transaction opened by the database 
** handle associated with the VM passed as an argument is about to be 
** committed. If there are outstanding deferred foreign key constraint
** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK.
**
** If there are outstanding FK violations and this function returns 
** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT_FOREIGNKEY
** and write an error message to it. Then return SQLITE_ERROR.
*/
#ifndef SQLITE_OMIT_FOREIGN_KEY
int sqlite3VdbeCheckFk(Vdbe *p, int deferred){
  sqlite3 *db = p->db;
  if( (deferred && db->nDeferredCons>0) || (!deferred && p->nFkConstraint>0) ){
    p->rc = SQLITE_CONSTRAINT_FOREIGNKEY;
    p->errorAction = OE_Abort;
    sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed");
    return SQLITE_ERROR;
  }
  return SQLITE_OK;
}
#endif
................................................................................
      if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
        rc = sqlite3VdbeCheckFk(p, 1);
        if( rc!=SQLITE_OK ){
          if( NEVER(p->readOnly) ){
            sqlite3VdbeLeave(p);
            return SQLITE_ERROR;
          }
          rc = SQLITE_CONSTRAINT_FOREIGNKEY;
        }else{ 
          /* The auto-commit flag is true, the vdbe program was successful 
          ** or hit an 'OR FAIL' constraint and there are no deferred foreign
          ** key constraints to hold up the transaction. This means a commit 
          ** is required. */
          rc = vdbeCommit(db, p);
        }
................................................................................
    ** do so. If this operation returns an error, and the current statement
    ** error code is SQLITE_OK or SQLITE_CONSTRAINT, then promote the
    ** current statement error code.
    */
    if( eStatementOp ){
      rc = sqlite3VdbeCloseStatement(p, eStatementOp);
      if( rc ){
        if( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_CONSTRAINT ){
          p->rc = rc;
          sqlite3DbFree(db, p->zErrMsg);
          p->zErrMsg = 0;
        }
        sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK);
        sqlite3CloseSavepoints(db);
        db->autoCommit = 1;

Changes to test/capi2.test

231
232
233
234
235
236
237
238

239
240
241
242
243
244
245
246
...
254
255
256
257
258
259
260
261

262
263
264
265
266
267
268
269
...
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289

# Update for v3: Preparing a statement does not affect the change counter.
# (Test result changes from 0 to 1).  (Later:) change counter updates occur
# when sqlite3_step returns, not at finalize time.
do_test capi2-3.13b {db changes} {0}

do_test capi2-3.14 {
  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]

} {SQLITE_CONSTRAINT {column a is not unique}}
do_test capi2-3.15 {
  set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]
  set TAIL
} {}
do_test capi2-3.16 {
  list [sqlite3_step $VM] \
       [sqlite3_column_count $VM] \
................................................................................
  set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL]
  list [sqlite3_step $VM] \
       [sqlite3_column_count $VM] \
       [get_row_values $VM] \
       [get_column_names $VM]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.19 {
  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]

} {SQLITE_CONSTRAINT {t2.a may not be NULL}}

do_test capi2-3.20 {
  execsql {
    CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );
    INSERT INTO a1 VALUES(1, 1);
  }
} {}
................................................................................
do_test capi2-3.22 {
  sqlite3_errcode $DB
} {SQLITE_ERROR}
do_test capi2-3.23 {
  sqlite3_finalize $VM
} {SQLITE_CONSTRAINT}
do_test capi2-3.24 {
  sqlite3_errcode $DB
} {SQLITE_CONSTRAINT}

# Two or more virtual machines exists at the same time.
#
do_test capi2-4.1 {
  set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]
  set TAIL
} {}







|
>
|







 







|
>
|







 







|
|







231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
...
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291

# Update for v3: Preparing a statement does not affect the change counter.
# (Test result changes from 0 to 1).  (Later:) change counter updates occur
# when sqlite3_step returns, not at finalize time.
do_test capi2-3.13b {db changes} {0}

do_test capi2-3.14 {
  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] \
       [sqlite3_extended_errcode $DB]
} {SQLITE_CONSTRAINT {column a is not unique} SQLITE_CONSTRAINT_UNIQUE}
do_test capi2-3.15 {
  set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]
  set TAIL
} {}
do_test capi2-3.16 {
  list [sqlite3_step $VM] \
       [sqlite3_column_count $VM] \
................................................................................
  set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL]
  list [sqlite3_step $VM] \
       [sqlite3_column_count $VM] \
       [get_row_values $VM] \
       [get_column_names $VM]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.19 {
  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] \
       [sqlite3_extended_errcode $DB]
} {SQLITE_CONSTRAINT {t2.a may not be NULL} SQLITE_CONSTRAINT_NOTNULL}

do_test capi2-3.20 {
  execsql {
    CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );
    INSERT INTO a1 VALUES(1, 1);
  }
} {}
................................................................................
do_test capi2-3.22 {
  sqlite3_errcode $DB
} {SQLITE_ERROR}
do_test capi2-3.23 {
  sqlite3_finalize $VM
} {SQLITE_CONSTRAINT}
do_test capi2-3.24 {
  list [sqlite3_errcode $DB] [sqlite3_extended_errcode $DB]
} {SQLITE_CONSTRAINT SQLITE_CONSTRAINT_UNIQUE}

# Two or more virtual machines exists at the same time.
#
do_test capi2-4.1 {
  set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]
  set TAIL
} {}

Changes to test/conflict.test

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
...
777
778
779
780
781
782
783

784
785
786
787
788
789
790
...
798
799
800
801
802
803
804

805
806
807
808
809
810
811
812
813
814
  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);
................................................................................
  }
} {1 one 2 two}
do_test conflict-12.3 {
  catchsql {
    UPDATE t5 SET a=a+1 WHERE a=1;
  }
} {1 {PRIMARY KEY must be unique}}

do_test conflict-12.4 {
  execsql {
    UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
    SELECT * FROM t5;
  }
} {2 one}

................................................................................
    BEGIN;
    REPLACE INTO t13 VALUES(1);
  }
  catchsql {
    REPLACE INTO t13 VALUES(2);
  }
} {1 {constraint failed}}

do_test conflict-13.2 {
  execsql {
    REPLACE INTO t13 VALUES(3);
    COMMIT;
    SELECT * FROM t13;
  }
} {1 3}


finish_test







>












>







 







>







 







>










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
...
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
...
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
  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}}
verify_ex_errcode conflict-9.21b SQLITE_CONSTRAINT_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}}
verify_ex_errcode conflict-9.21b SQLITE_CONSTRAINT_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);
................................................................................
  }
} {1 one 2 two}
do_test conflict-12.3 {
  catchsql {
    UPDATE t5 SET a=a+1 WHERE a=1;
  }
} {1 {PRIMARY KEY must be unique}}
verify_ex_errcode conflict-12.3b SQLITE_CONSTRAINT_PRIMARYKEY
do_test conflict-12.4 {
  execsql {
    UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
    SELECT * FROM t5;
  }
} {2 one}

................................................................................
    BEGIN;
    REPLACE INTO t13 VALUES(1);
  }
  catchsql {
    REPLACE INTO t13 VALUES(2);
  }
} {1 {constraint failed}}
verify_ex_errcode conflict-13.1b SQLITE_CONSTRAINT_CHECK
do_test conflict-13.2 {
  execsql {
    REPLACE INTO t13 VALUES(3);
    COMMIT;
    SELECT * FROM t13;
  }
} {1 3}


finish_test

Changes to test/errmsg.test

76
77
78
79
80
81
82

83
84
85
86
87
88

89
90
91
92
93
94
95
}
do_test 2.2 {
  error_messages "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE_ERROR      {SQL logic error or missing database} 
    SQLITE_CONSTRAINT {column b is not unique}
}]

do_test 2.3 {
  error_messages_v2 "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE_CONSTRAINT {column b is not unique}
    SQLITE_CONSTRAINT {column b is not unique}
}]


#-------------------------------------------------------------------------
# Test SQLITE_SCHEMA errors. And, for _v2(), test that if the schema
# change invalidates the SQL statement itself the error message is returned
# correctly.
#
do_execsql_test 3.1.1 {







>






>







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
}
do_test 2.2 {
  error_messages "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE_ERROR      {SQL logic error or missing database} 
    SQLITE_CONSTRAINT {column b is not unique}
}]
verify_ex_errcode 2.2b SQLITE_CONSTRAINT_UNIQUE
do_test 2.3 {
  error_messages_v2 "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE_CONSTRAINT {column b is not unique}
    SQLITE_CONSTRAINT {column b is not unique}
}]
verify_ex_errcode 2.3b SQLITE_CONSTRAINT_UNIQUE

#-------------------------------------------------------------------------
# Test SQLITE_SCHEMA errors. And, for _v2(), test that if the schema
# change invalidates the SQL statement itself the error message is returned
# correctly.
#
do_execsql_test 3.1.1 {

Changes to test/fkey2.test

1429
1430
1431
1432
1433
1434
1435

1436
1437
1438
1439

1440
1441
1442
1443
1444
1445
1446
1447

1448
1449
1450
1451
1452
1453
1454
....
1484
1485
1486
1487
1488
1489
1490

1491
1492
1493

1494
1495
1496
1497
1498
1499
1500
....
1640
1641
1642
1643
1644
1645
1646

1647
1648
1649

1650
1651
1652
1653
1654
1655
1656
    INSERT INTO one VALUES(1, 2, 3);
  }
} {1}
do_test fkey2-17.1.2 {
  set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
  sqlite3_step $STMT
} {SQLITE_CONSTRAINT}

ifcapable autoreset {
  do_test fkey2-17.1.3 {
    sqlite3_step $STMT
  } {SQLITE_CONSTRAINT}

} else {
  do_test fkey2-17.1.3 {
    sqlite3_step $STMT
  } {SQLITE_MISUSE}
}
do_test fkey2-17.1.4 {
  sqlite3_finalize $STMT
} {SQLITE_CONSTRAINT}

do_test fkey2-17.1.5 {
  execsql {
    INSERT INTO one VALUES(2, 3, 4);
    INSERT INTO one VALUES(3, 4, 5);
    INSERT INTO two VALUES(1, 2, 3);
    INSERT INTO two VALUES(2, 3, 4);
    INSERT INTO two VALUES(3, 4, 5);
................................................................................
} {SQLITE_ROW}
do_test fkey2-17.1.12 {
  sqlite3_column_text $STMT 0
} {1}
do_test fkey2-17.1.13 {
  sqlite3_step $STMT
} {SQLITE_CONSTRAINT}

do_test fkey2-17.1.14 {
  sqlite3_finalize $STMT
} {SQLITE_CONSTRAINT}


drop_all_tables
do_test fkey2-17.2.1 {
  execsql {
    CREATE TABLE high("a'b!" PRIMARY KEY, b);
    CREATE TABLE low(
      c, 
................................................................................
  }
} {}
do_test fkey2-19.2 {
  set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
  sqlite3_bind_int $S 1 2
  sqlite3_step $S
} {SQLITE_CONSTRAINT}

do_test fkey2-19.3 {
  sqlite3_reset $S
} {SQLITE_CONSTRAINT}

do_test fkey2-19.4 {
  sqlite3_bind_int $S 1 1
  sqlite3_step $S
} {SQLITE_DONE}
do_test fkey2-19.4 {
  sqlite3_finalize $S
} {SQLITE_OK}







>




>








>







 







>



>







 







>



>







1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
....
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
....
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
    INSERT INTO one VALUES(1, 2, 3);
  }
} {1}
do_test fkey2-17.1.2 {
  set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
  sqlite3_step $STMT
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
ifcapable autoreset {
  do_test fkey2-17.1.3 {
    sqlite3_step $STMT
  } {SQLITE_CONSTRAINT}
  verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
} else {
  do_test fkey2-17.1.3 {
    sqlite3_step $STMT
  } {SQLITE_MISUSE}
}
do_test fkey2-17.1.4 {
  sqlite3_finalize $STMT
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
do_test fkey2-17.1.5 {
  execsql {
    INSERT INTO one VALUES(2, 3, 4);
    INSERT INTO one VALUES(3, 4, 5);
    INSERT INTO two VALUES(1, 2, 3);
    INSERT INTO two VALUES(2, 3, 4);
    INSERT INTO two VALUES(3, 4, 5);
................................................................................
} {SQLITE_ROW}
do_test fkey2-17.1.12 {
  sqlite3_column_text $STMT 0
} {1}
do_test fkey2-17.1.13 {
  sqlite3_step $STMT
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
do_test fkey2-17.1.14 {
  sqlite3_finalize $STMT
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY

drop_all_tables
do_test fkey2-17.2.1 {
  execsql {
    CREATE TABLE high("a'b!" PRIMARY KEY, b);
    CREATE TABLE low(
      c, 
................................................................................
  }
} {}
do_test fkey2-19.2 {
  set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
  sqlite3_bind_int $S 1 2
  sqlite3_step $S
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
do_test fkey2-19.3 {
  sqlite3_reset $S
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
do_test fkey2-19.4 {
  sqlite3_bind_int $S 1 1
  sqlite3_step $S
} {SQLITE_DONE}
do_test fkey2-19.4 {
  sqlite3_finalize $S
} {SQLITE_OK}

Changes to test/fkey4.test

38
39
40
41
42
43
44

45
46
47
48

49
50
51
52
53
54
55

do_test fkey4-1.2 {
  set ::DB [sqlite3_connection_pointer db]
  set ::SQL {INSERT INTO t2 VALUES(2,4)}
  set ::STMT1 [sqlite3_prepare_v2 $::DB $::SQL -1 TAIL]
  sqlite3_step $::STMT1
} {SQLITE_CONSTRAINT}

do_test fkey4-1.3 {
  set ::STMT2 [sqlite3_prepare_v2 $::DB $::SQL -1 TAIL]
  sqlite3_step $::STMT2
} {SQLITE_CONSTRAINT}

do_test fkey4-1.4 {
  db eval {SELECT * FROM t2}
} {1 3}
sqlite3_finalize $::STMT1
sqlite3_finalize $::STMT2

finish_test







>




>







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

do_test fkey4-1.2 {
  set ::DB [sqlite3_connection_pointer db]
  set ::SQL {INSERT INTO t2 VALUES(2,4)}
  set ::STMT1 [sqlite3_prepare_v2 $::DB $::SQL -1 TAIL]
  sqlite3_step $::STMT1
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey4-1.2b SQLITE_CONSTRAINT_FOREIGNKEY
do_test fkey4-1.3 {
  set ::STMT2 [sqlite3_prepare_v2 $::DB $::SQL -1 TAIL]
  sqlite3_step $::STMT2
} {SQLITE_CONSTRAINT}
verify_ex_errcode fkey4-1.3b SQLITE_CONSTRAINT_FOREIGNKEY
do_test fkey4-1.4 {
  db eval {SELECT * FROM t2}
} {1 3}
sqlite3_finalize $::STMT1
sqlite3_finalize $::STMT2

finish_test

Changes to test/hook.test

70
71
72
73
74
75
76

77
78
79
80
81
82
83
    set ::commit_cnt [execsql {SELECT * FROM t2}] 
    return 1
  }
  catchsql {
    INSERT INTO t2 VALUES(6,7);
  }
} {1 {constraint failed}}

do_test hook-3.7 {
  set ::commit_cnt
} {1 2 2 3 3 4 4 5 5 6 6 7}
do_test hook-3.8 {
  execsql {SELECT * FROM t2}
} {1 2 2 3 3 4 4 5 5 6}








>







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
    set ::commit_cnt [execsql {SELECT * FROM t2}] 
    return 1
  }
  catchsql {
    INSERT INTO t2 VALUES(6,7);
  }
} {1 {constraint failed}}
verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK
do_test hook-3.7 {
  set ::commit_cnt
} {1 2 2 3 3 4 4 5 5 6 6 7}
do_test hook-3.8 {
  execsql {SELECT * FROM t2}
} {1 2 2 3 3 4 4 5 5 6}

Changes to test/notnull.test

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
...
100
101
102
103
104
105
106

107
108
109
110
111
112
113
...
142
143
144
145
146
147
148

149
150
151
152
153
154
155

156
157
158
159
160
161
162
...
170
171
172
173
174
175
176

177
178
179
180
181
182
183
...
186
187
188
189
190
191
192

193
194
195
196
197
198
199
200

201
202
203
204
205
206
207
...
210
211
212
213
214
215
216

217
218
219
220
221
222
223
224

225
226
227
228
229
230
231
...
258
259
260
261
262
263
264

265
266
267
268
269
270
271
...
283
284
285
286
287
288
289

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
...
339
340
341
342
343
344
345

346
347
348
349
350
351
352
...
381
382
383
384
385
386
387

388
389
390
391
392
393
394

395
396
397
398
399
400
401
...
409
410
411
412
413
414
415

416
417
418
419
420
421
422
...
425
426
427
428
429
430
431

432
433
434
435
436
437
438
439

440
441
442
443
444
445
446
...
449
450
451
452
453
454
455

456
457
458
459
460
461
462
463

464
465
466
467
468
469
470
...
497
498
499
500
501
502
503

504
505
506
507
508
509
510
...
515
516
517
518
519
520
521

522
523
524
525
526
527
528
529
530
531
532
533

534
535
536
537
538
539
do_test notnull-1.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-1.3 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-1.4 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-1.5 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-1.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 5 3 4 5}}
................................................................................
do_test notnull-1.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.b may not be NULL}}

do_test notnull-1.11 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-1.16 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.c may not be NULL}}

do_test notnull-1.17 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.d may not be NULL}}

do_test notnull-1.18 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 2 3 7 5}}
................................................................................
do_test notnull-1.20 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.e may not be NULL}}

do_test notnull-1.21 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {0 {5 5 3 2 1}}
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-2.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-2.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR IGNORE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR ABORT t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-2.5 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET b=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.b may not be NULL}}

do_test notnull-2.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET e=null, a=b, b=a;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.e may not be NULL}}


do_test notnull-3.0 {
  execsql {
    CREATE INDEX t1a ON t1(a);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1c ON t1(c);
    CREATE INDEX t1d ON t1(d);
................................................................................
do_test notnull-3.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-3.3 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-3.4 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-3.5 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-3.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 5 3 4 5}}
................................................................................
do_test notnull-3.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.b may not be NULL}}

do_test notnull-3.11 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-3.16 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.c may not be NULL}}

do_test notnull-3.17 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.d may not be NULL}}

do_test notnull-3.18 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 2 3 7 5}}
................................................................................
do_test notnull-3.20 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.e may not be NULL}}

do_test notnull-3.21 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {0 {5 5 3 2 1}}
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-4.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-4.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR IGNORE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR ABORT t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}

do_test notnull-4.5 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET b=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.b may not be NULL}}

do_test notnull-4.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET e=null, a=b, b=a;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.e may not be NULL}}


# Test that bug 29ab7be99f is fixed.
#
do_test notnull-5.1 {
  execsql {
    DROP TABLE IF EXISTS t1;
    CREATE TABLE t1(a, b NOT NULL);
................................................................................
}  {}
do_test notnull-5.2 {
  catchsql {
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {t1.b may not be NULL}}

do_test notnull-5.3 {
  execsql { SELECT * FROM t1 }
} {1 2}
do_test notnull-5.4 {
  catchsql {
    DELETE FROM t1;
    BEGIN;
      INSERT INTO t1 VALUES(1, 2);
      INSERT INTO t1 SELECT * FROM t2;
    COMMIT;
  }
} {1 {t1.b may not be NULL}}

do_test notnull-5.5 {
  execsql { SELECT * FROM t1 }
} {1 2}

finish_test








>







 







>







>







 







>







 







>







>







 







>







 







>








>







 







>








>







 







>







 







>







 







>







>







 







>







 







>







>







 







>







 







>








>







 







>








>







 







>







 







>












>





<
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
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
...
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
...
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
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
...
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
...
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
...
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
...
444
445
446
447
448
449
450
451
452
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
485
486
487
488
489
490
491
492
493
...
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
...
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

do_test notnull-1.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-1.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.3 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-1.4 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-1.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.5 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-1.5b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 5 3 4 5}}
................................................................................
do_test notnull-1.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.b may not be NULL}}
verify_ex_errcode notnull-1.10b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.11 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-1.16 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.c may not be NULL}}
verify_ex_errcode notnull-1.16b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.17 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.d may not be NULL}}
verify_ex_errcode notnull-1.17b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.18 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 2 3 7 5}}
................................................................................
do_test notnull-1.20 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.e may not be NULL}}
verify_ex_errcode notnull-1.20b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.21 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {0 {5 5 3 2 1}}
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-2.1b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-2.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR IGNORE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR ABORT t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-2.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.5 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET b=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.b may not be NULL}}
verify_ex_errcode notnull-2.6b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET e=null, a=b, b=a;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.e may not be NULL}}
verify_ex_errcode notnull-2.10b SQLITE_CONSTRAINT_NOTNULL

do_test notnull-3.0 {
  execsql {
    CREATE INDEX t1a ON t1(a);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1c ON t1(c);
    CREATE INDEX t1d ON t1(d);
................................................................................
do_test notnull-3.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-3.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.3 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-3.4 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-3.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.5 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-3.5b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 5 3 4 5}}
................................................................................
do_test notnull-3.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.b may not be NULL}}
verify_ex_errcode notnull-3.10b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.11 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
................................................................................
do_test notnull-3.16 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.c may not be NULL}}
verify_ex_errcode notnull-3.16b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.17 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.d may not be NULL}}
verify_ex_errcode notnull-3.17b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.18 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
    SELECT * FROM t1 order by a;
  }
} {0 {1 2 3 7 5}}
................................................................................
do_test notnull-3.20 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
    SELECT * FROM t1 order by a;
  }
} {1 {t1.e may not be NULL}}
verify_ex_errcode notnull-3.20b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.21 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {0 {5 5 3 2 1}}
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-4.1b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-4.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR IGNORE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR ABORT t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.a may not be NULL}}
verify_ex_errcode notnull-4.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.5 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET b=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.b may not be NULL}}
verify_ex_errcode notnull-4.5b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
    SELECT * FROM t1 ORDER BY a;
  }
................................................................................
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET e=null, a=b, b=a;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {t1.e may not be NULL}}
verify_ex_errcode notnull-4.10b SQLITE_CONSTRAINT_NOTNULL

# Test that bug 29ab7be99f is fixed.
#
do_test notnull-5.1 {
  execsql {
    DROP TABLE IF EXISTS t1;
    CREATE TABLE t1(a, b NOT NULL);
................................................................................
}  {}
do_test notnull-5.2 {
  catchsql {
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {t1.b may not be NULL}}
verify_ex_errcode notnull-5.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-5.3 {
  execsql { SELECT * FROM t1 }
} {1 2}
do_test notnull-5.4 {
  catchsql {
    DELETE FROM t1;
    BEGIN;
      INSERT INTO t1 VALUES(1, 2);
      INSERT INTO t1 SELECT * FROM t2;
    COMMIT;
  }
} {1 {t1.b may not be NULL}}
verify_ex_errcode notnull-5.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-5.5 {
  execsql { SELECT * FROM t1 }
} {1 2}

finish_test

Changes to test/tester.tcl

50
51
52
53
54
55
56

57
58
59
60
61
62
63
...
963
964
965
966
967
968
969






970
971
972
973
974
975
976
#      execsql                SQL ?DB?
#
# Commands to run test cases:
#
#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?

#      do_test                TESTNAME SCRIPT EXPECTED
#      do_execsql_test        TESTNAME SQL EXPECTED
#      do_catchsql_test       TESTNAME SQL EXPECTED
#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
................................................................................
# Do an integrity check of the entire database
#
proc integrity_check {name {db db}} {
  ifcapable integrityck {
    do_test $name [list execsql {PRAGMA integrity_check} $db] {ok}
  }
}








# Return true if the SQL statement passed as the second argument uses a
# statement transaction.
#
proc sql_uses_stmt {db sql} {
  set stmt [sqlite3_prepare $db $sql -1 dummy]







>







 







>
>
>
>
>
>







50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
...
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
#      execsql                SQL ?DB?
#
# Commands to run test cases:
#
#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?
#      verify_ex_errcode      TESTNAME EXPECTED ?DB?
#      do_test                TESTNAME SCRIPT EXPECTED
#      do_execsql_test        TESTNAME SQL EXPECTED
#      do_catchsql_test       TESTNAME SQL EXPECTED
#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
................................................................................
# Do an integrity check of the entire database
#
proc integrity_check {name {db db}} {
  ifcapable integrityck {
    do_test $name [list execsql {PRAGMA integrity_check} $db] {ok}
  }
}

# Check the extended error code
#
proc verify_ex_errcode {name expected {db db}} {
  do_test $name [list sqlite3_extended_errcode $db] $expected
}


# Return true if the SQL statement passed as the second argument uses a
# statement transaction.
#
proc sql_uses_stmt {db sql} {
  set stmt [sqlite3_prepare $db $sql -1 dummy]

Changes to test/trigger1.test

419
420
421
422
423
424
425

426
427
428
429
430
431
432
    END;
    SELECT type, name FROM sqlite_master;
  }
} [concat $view_v1 {table t2 trigger t2}]
do_test trigger1-6.3 {
  catchsql {DELETE FROM t2}
} {1 {deletes are not permitted}}

do_test trigger1-6.4 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger1-6.5 {
  db close
  sqlite3 db test.db
  execsql {SELECT type, name FROM sqlite_master}







>







419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
    END;
    SELECT type, name FROM sqlite_master;
  }
} [concat $view_v1 {table t2 trigger t2}]
do_test trigger1-6.3 {
  catchsql {DELETE FROM t2}
} {1 {deletes are not permitted}}
verify_ex_errcode trigger1-6.3b SQLITE_CONSTRAINT_TRIGGER
do_test trigger1-6.4 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger1-6.5 {
  db close
  sqlite3 db test.db
  execsql {SELECT type, name FROM sqlite_master}

Changes to test/trigger3.test

41
42
43
44
45
46
47

48
49
50
51
52
53
54
..
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
..
88
89
90
91
92
93
94

95
96
97
98
99
100
101
...
168
169
170
171
172
173
174

175
176
177
178
179
180
181
182
183
184

185
186
187
188
189
190
191
192
193
194
do_test trigger3-1.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}

do_test trigger3-1.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6}
do_test trigger3-1.3 {
................................................................................
do_test trigger3-2.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}

do_test trigger3-2.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
................................................................................
do_test trigger3-3.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}

do_test trigger3-3.2 {
    execsql {
        SELECT * FROM tbl;
    }
} {}

# Verify that a ROLLBACK trigger works like a FAIL trigger if
................................................................................
#
do_test trigger3-3.3 {
    catchsql {COMMIT}
    catchsql {
        INSERT INTO tbl VALUES (3, 9, 10);
    }
} {1 {Trigger rollback}}

do_test trigger3-3.4 {
    execsql {SELECT * FROM tbl}
} {}

# IGNORE
do_test trigger3-4.1 {
    catchsql {
................................................................................
}

do_test trigger3-7.1 {
    catchsql {
        INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}

do_test trigger3-7.2 {
    catchsql {
        INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
        INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}


} ;# ifcapable view

integrity_check trigger3-8.1

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test







>







 







>







 







>







 







>







 







>










>










41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
..
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
..
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
do_test trigger3-1.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}
verify_ex_errcode trigger3-1.1b SQLITE_CONSTRAINT_TRIGGER
do_test trigger3-1.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6}
do_test trigger3-1.3 {
................................................................................
do_test trigger3-2.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}
verify_ex_errcode trigger3-2.1b SQLITE_CONSTRAINT_TRIGGER
do_test trigger3-2.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
................................................................................
do_test trigger3-3.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}
verify_ex_errcode trigger3-3.1b SQLITE_CONSTRAINT_TRIGGER
do_test trigger3-3.2 {
    execsql {
        SELECT * FROM tbl;
    }
} {}

# Verify that a ROLLBACK trigger works like a FAIL trigger if
................................................................................
#
do_test trigger3-3.3 {
    catchsql {COMMIT}
    catchsql {
        INSERT INTO tbl VALUES (3, 9, 10);
    }
} {1 {Trigger rollback}}
verify_ex_errcode trigger3-3.3b SQLITE_CONSTRAINT_TRIGGER
do_test trigger3-3.4 {
    execsql {SELECT * FROM tbl}
} {}

# IGNORE
do_test trigger3-4.1 {
    catchsql {
................................................................................
}

do_test trigger3-7.1 {
    catchsql {
        INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}
verify_ex_errcode trigger3-7.1b SQLITE_CONSTRAINT_TRIGGER
do_test trigger3-7.2 {
    catchsql {
        INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
        INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}
verify_ex_errcode trigger3-7.3b SQLITE_CONSTRAINT_TRIGGER

} ;# ifcapable view

integrity_check trigger3-8.1

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test

Changes to test/unique.test

44
45
46
47
48
49
50

51
52
53
54
55
56
57
58
59
60

61
62
63
64
65
66
67
..
95
96
97
98
99
100
101

102
103
104
105
106
107
108
...
121
122
123
124
125
126
127

128
129
130
131
132
133
134
...
159
160
161
162
163
164
165

166
167
168
169
170
171
172
...
213
214
215
216
217
218
219

220
221
222
223
224
225
226
...
245
246
247
248
249
250
251

252

253
  }
} {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 {
................................................................................
  }
} {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 1 5 3 4}}
do_test unique-2.8 {
  catchsql {
    CREATE UNIQUE INDEX i2 ON t2(a);
  }
} {1 {indexed columns are not unique}}

do_test unique-2.9 {
  catchsql {
    CREATE INDEX i2 ON t2(a);
  }
} {0 {}}
integrity_check unique-2.10

................................................................................
} {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 {
................................................................................
} {0 {}}
do_test unique-4.9 {
  catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
} {0 {}}
do_test unique-4.10 {
  catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
} {1 {indexed columns are not unique}}

integrity_check unique-4.99

# 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 {
................................................................................
  }
} {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, sixth_column_with_long_name are not unique}}



finish_test







>










>







 







>







 







>







 







>







 







>







 







>

>

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
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
...
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
251
252
253
254
255
256
257
258
259
260
261
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {column a is not unique}}
verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_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}}
verify_ex_errcode unique-1.5b SQLITE_CONSTRAINT_UNIQUE
do_test unique-1.6 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.7 {
  catchsql {
................................................................................
  }
} {0 {1 2 3 4}}
do_test unique-2.3 {
  catchsql {
    INSERT INTO t2 VALUES(1,5);
  }
} {1 {column a is not unique}}
verify_ex_errcode unique-2.3b SQLITE_CONSTRAINT_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 1 5 3 4}}
do_test unique-2.8 {
  catchsql {
    CREATE UNIQUE INDEX i2 ON t2(a);
  }
} {1 {indexed columns are not unique}}
verify_ex_errcode unique-2.8b SQLITE_CONSTRAINT_UNIQUE
do_test unique-2.9 {
  catchsql {
    CREATE INDEX i2 ON t2(a);
  }
} {0 {}}
integrity_check unique-2.10

................................................................................
} {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}}
verify_ex_errcode unique-3.4b SQLITE_CONSTRAINT_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 {
................................................................................
} {0 {}}
do_test unique-4.9 {
  catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
} {0 {}}
do_test unique-4.10 {
  catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
} {1 {indexed columns are not unique}}
verify_ex_errcode unique-4.10b SQLITE_CONSTRAINT_UNIQUE
integrity_check unique-4.99

# 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 {
................................................................................
  }
} {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, sixth_column_with_long_name are not unique}}
verify_ex_errcode unique-5.2b SQLITE_CONSTRAINT_UNIQUE


finish_test