Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1113,10 +1113,11 @@ void sqlite3AddNotNull(Parse *pParse, int onError){ Table *p; p = pParse->pNewTable; if( p==0 || NEVER(p->nCol<1) ) return; p->aCol[p->nCol-1].notNull = (u8)onError; + p->tabFlags |= TF_HasNotNull; } /* ** Scan the column type name zType (length nType) and return the ** associated affinity type. Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -292,10 +292,26 @@ lwr = mid + 1; } } return lwr>upr ? 0 : &aPragmaName[mid]; } + +/* +** Helper subroutine for PRAGMA integrity_check: +** +** Generate code to output a single-column result row with the result +** held in register regResult. Decrement the result count and halt if +** the maximum number of result rows have been issued. +*/ +static int integrityCheckResultRow(Vdbe *v, int regResult){ + int addr; + sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 1); + addr = sqlite3VdbeAddOp3(v, OP_IfPos, 1, sqlite3VdbeCurrentAddr(v)+2, 1); + VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); + return addr; +} /* ** Process a pragma statement. ** ** Pragmas are of this form: @@ -1375,13 +1391,21 @@ #ifndef SQLITE_INTEGRITY_CHECK_ERROR_MAX # define SQLITE_INTEGRITY_CHECK_ERROR_MAX 100 #endif #ifndef SQLITE_OMIT_INTEGRITY_CHECK - /* Pragma "quick_check" is reduced version of + /* PRAGMA integrity_check + ** PRAGMA integrity_check(N) + ** PRAGMA quick_check + ** PRAGMA quick_check(N) + ** + ** Verify the integrity of the database. + ** + ** The "quick_check" is reduced version of ** integrity_check designed to detect most database corruption - ** without most of the overhead of a full integrity-check. + ** without the overhead of cross-checking indexes. Quick_check + ** is linear time wherease integrity_check is O(NlogN). */ case PragTyp_INTEGRITY_CHECK: { int i, j, addr, mxErr; int isQuick = (sqlite3Tolower(zLeft[0])=='q'); @@ -1408,11 +1432,11 @@ sqlite3GetInt32(zRight, &mxErr); if( mxErr<=0 ){ mxErr = SQLITE_INTEGRITY_CHECK_ERROR_MAX; } } - sqlite3VdbeAddOp2(v, OP_Integer, mxErr, 1); /* reg[1] holds errors left */ + sqlite3VdbeAddOp2(v, OP_Integer, mxErr-1, 1); /* reg[1] holds errors left */ /* Do an integrity check on each database file */ for(i=0; inDb; i++){ HashElem *x; Hash *pTbls; @@ -1423,14 +1447,10 @@ if( OMIT_TEMPDB && i==1 ) continue; if( iDb>=0 && i!=iDb ) continue; sqlite3CodeVerifySchema(pParse, i); - addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1); /* Halt if out of errors */ - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); - sqlite3VdbeJumpHere(v, addr); /* Do an integrity check of the B-Tree ** ** Begin by finding the root pages numbers ** for all tables and indices in the database. @@ -1466,29 +1486,30 @@ sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, sqlite3MPrintf(db, "*** in database %s ***\n", db->aDb[i].zDbSName), P4_DYNAMIC); sqlite3VdbeAddOp3(v, OP_Move, 2, 4, 1); sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 2); - sqlite3VdbeAddOp2(v, OP_ResultRow, 2, 1); + integrityCheckResultRow(v, 2); sqlite3VdbeJumpHere(v, addr); /* Make sure all the indices are constructed correctly. */ - for(x=sqliteHashFirst(pTbls); x && !isQuick; x=sqliteHashNext(x)){ + for(x=sqliteHashFirst(pTbls); x; x=sqliteHashNext(x)){ Table *pTab = sqliteHashData(x); Index *pIdx, *pPk; Index *pPrior = 0; int loopTop; int iDataCur, iIdxCur; int r1 = -1; - if( pTab->pIndex==0 ) continue; + if( pTab->pCheck==0 + && (pTab->tabFlags & TF_HasNotNull)==0 + && (pTab->pIndex==0 || isQuick) + ){ + continue; /* No additional checks needed for this table */ + } pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab); - addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1); /* Stop if out of errors */ - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); - sqlite3VdbeJumpHere(v, addr); sqlite3ExprCacheClear(pParse); sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenRead, 0, 1, 0, &iDataCur, &iIdxCur); sqlite3VdbeAddOp2(v, OP_Integer, 0, 7); for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ @@ -1499,28 +1520,46 @@ sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v); loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1); /* Verify that all NOT NULL columns really are NOT NULL */ for(j=0; jnCol; j++){ char *zErr; - int jmp2, jmp3; + int jmp2; if( j==pTab->iPKey ) continue; if( pTab->aCol[j].notNull==0 ) continue; sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3); sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); jmp2 = sqlite3VdbeAddOp1(v, OP_NotNull, 3); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); /* Decrement error limit */ zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName, pTab->aCol[j].zName); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC); - sqlite3VdbeAddOp2(v, OP_ResultRow, 3, 1); - jmp3 = sqlite3VdbeAddOp1(v, OP_IfPos, 1); VdbeCoverage(v); - sqlite3VdbeAddOp0(v, OP_Halt); + integrityCheckResultRow(v, 3); sqlite3VdbeJumpHere(v, jmp2); - sqlite3VdbeJumpHere(v, jmp3); + } + /* Verify CHECK constraints */ + if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){ + int addrCkFault = sqlite3VdbeMakeLabel(v); + int addrCkOk = sqlite3VdbeMakeLabel(v); + ExprList *pCheck = pTab->pCheck; + char *zErr; + int k; + pParse->iSelfTab = iDataCur; + sqlite3ExprCachePush(pParse); + for(k=pCheck->nExpr-1; k>0; k--){ + sqlite3ExprIfFalse(pParse, pCheck->a[k].pExpr, addrCkFault, 0); + } + sqlite3ExprIfTrue(pParse, pCheck->a[0].pExpr, addrCkOk, + SQLITE_JUMPIFNULL); + sqlite3VdbeResolveLabel(v, addrCkFault); + zErr = sqlite3MPrintf(db, "CHECK constraint failed in %s", + pTab->zName); + sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC); + integrityCheckResultRow(v, 3); + sqlite3VdbeResolveLabel(v, addrCkOk); + sqlite3ExprCachePop(pParse); } /* Validate index entries for the current row */ - for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ + for(j=0, pIdx=pTab->pIndex; pIdx && !isQuick; pIdx=pIdx->pNext, j++){ int jmp2, jmp3, jmp4, jmp5; int ckUniq = sqlite3VdbeMakeLabel(v); if( pPk==pIdx ) continue; r1 = sqlite3GenerateIndexKey(pParse, pIdx, iDataCur, 0, 0, &jmp3, pPrior, r1); @@ -1527,20 +1566,17 @@ pPrior = pIdx; sqlite3VdbeAddOp2(v, OP_AddImm, 8+j, 1); /* increment entry count */ /* Verify that an index entry exists for the current table row */ jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, iIdxCur+j, ckUniq, r1, pIdx->nColumn); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); /* Decrement error limit */ sqlite3VdbeLoadString(v, 3, "row "); sqlite3VdbeAddOp3(v, OP_Concat, 7, 3, 3); sqlite3VdbeLoadString(v, 4, " missing from index "); sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 3); jmp5 = sqlite3VdbeLoadString(v, 4, pIdx->zName); sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 3); - sqlite3VdbeAddOp2(v, OP_ResultRow, 3, 1); - jmp4 = sqlite3VdbeAddOp1(v, OP_IfPos, 1); VdbeCoverage(v); - sqlite3VdbeAddOp0(v, OP_Halt); + jmp4 = integrityCheckResultRow(v, 3); sqlite3VdbeJumpHere(v, jmp2); /* For UNIQUE indexes, verify that only one entry exists with the ** current key. The entry is unique if (1) any column is NULL ** or (2) the next entry has a different key */ if( IsUniqueIndex(pIdx) ){ @@ -1557,11 +1593,10 @@ jmp6 = sqlite3VdbeAddOp1(v, OP_Next, iIdxCur+j); VdbeCoverage(v); sqlite3VdbeGoto(v, uniqOk); sqlite3VdbeJumpHere(v, jmp6); sqlite3VdbeAddOp4Int(v, OP_IdxGT, iIdxCur+j, uniqOk, r1, pIdx->nKeyCol); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); /* Decrement error limit */ sqlite3VdbeLoadString(v, 3, "non-unique entry in index "); sqlite3VdbeGoto(v, jmp5); sqlite3VdbeResolveLabel(v, uniqOk); } sqlite3VdbeJumpHere(v, jmp4); @@ -1568,40 +1603,39 @@ sqlite3ResolvePartIdxLabel(pParse, jmp3); } sqlite3VdbeAddOp2(v, OP_Next, iDataCur, loopTop); VdbeCoverage(v); sqlite3VdbeJumpHere(v, loopTop-1); #ifndef SQLITE_OMIT_BTREECOUNT - sqlite3VdbeLoadString(v, 2, "wrong # of entries in index "); - for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ - if( pPk==pIdx ) continue; - addr = sqlite3VdbeCurrentAddr(v); - sqlite3VdbeAddOp2(v, OP_IfPos, 1, addr+2); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); - sqlite3VdbeAddOp2(v, OP_Count, iIdxCur+j, 3); - sqlite3VdbeAddOp3(v, OP_Eq, 8+j, addr+8, 3); VdbeCoverage(v); - sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); - sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); - sqlite3VdbeLoadString(v, 3, pIdx->zName); - sqlite3VdbeAddOp3(v, OP_Concat, 3, 2, 7); - sqlite3VdbeAddOp2(v, OP_ResultRow, 7, 1); + if( !isQuick ){ + sqlite3VdbeLoadString(v, 2, "wrong # of entries in index "); + for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ + if( pPk==pIdx ) continue; + sqlite3VdbeAddOp2(v, OP_Count, iIdxCur+j, 3); + addr = sqlite3VdbeAddOp3(v, OP_Eq, 8+j, 0, 3); VdbeCoverage(v); + sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); + sqlite3VdbeLoadString(v, 3, pIdx->zName); + sqlite3VdbeAddOp3(v, OP_Concat, 3, 2, 7); + integrityCheckResultRow(v, 7); + sqlite3VdbeJumpHere(v, addr); + } } #endif /* SQLITE_OMIT_BTREECOUNT */ } } { static const int iLn = VDBE_OFFSET_LINENO(2); static const VdbeOpList endCode[] = { { OP_AddImm, 1, 0, 0}, /* 0 */ - { OP_If, 1, 4, 0}, /* 1 */ + { OP_IfNotZero, 1, 4, 0}, /* 1 */ { OP_String8, 0, 3, 0}, /* 2 */ { OP_ResultRow, 3, 1, 0}, /* 3 */ }; VdbeOp *aOp; aOp = sqlite3VdbeAddOpList(v, ArraySize(endCode), endCode, iLn); if( aOp ){ - aOp[0].p2 = -mxErr; + aOp[0].p2 = 1-mxErr; aOp[2].p4type = P4_STATIC; aOp[2].p4.z = "ok"; } } } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1877,18 +1877,19 @@ ** followed by non-hidden columns. Example: "CREATE VIRTUAL TABLE x USING ** vtab1(a HIDDEN, b);". Since "b" is a non-hidden column but "a" is hidden, ** the TF_OOOHidden attribute would apply in this case. Such tables require ** special handling during INSERT processing. */ -#define TF_Readonly 0x01 /* Read-only system table */ -#define TF_Ephemeral 0x02 /* An ephemeral table */ -#define TF_HasPrimaryKey 0x04 /* Table has a primary key */ -#define TF_Autoincrement 0x08 /* Integer primary key is autoincrement */ -/* available for reuse: 0x10 */ -#define TF_WithoutRowid 0x20 /* No rowid. PRIMARY KEY is the key */ -#define TF_NoVisibleRowid 0x40 /* No user-visible "rowid" column */ -#define TF_OOOHidden 0x80 /* Out-of-Order hidden columns */ +#define TF_Readonly 0x0001 /* Read-only system table */ +#define TF_Ephemeral 0x0002 /* An ephemeral table */ +#define TF_HasPrimaryKey 0x0004 /* Table has a primary key */ +#define TF_Autoincrement 0x0008 /* Integer primary key is autoincrement */ +/* available for reuse: 0x0010 */ +#define TF_WithoutRowid 0x0020 /* No rowid. PRIMARY KEY is the key */ +#define TF_NoVisibleRowid 0x0040 /* No user-visible "rowid" column */ +#define TF_OOOHidden 0x0080 /* Out-of-Order hidden columns */ +#define TF_HasNotNull 0x0100 /* Contains NOT NULL constraints */ /* ** Test to see whether or not a table is a virtual table. This is ** done as a macro so that it will be optimized out when virtual Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -5609,11 +5609,11 @@ ** ** Do an analysis of the currently open database. Store in ** register P1 the text of an error message describing any problems. ** If no problems are found, store a NULL in register P1. ** -** The register P3 contains the maximum number of allowed errors. +** The register P3 contains one less than the maximum number of allowed errors. ** At most reg(P3) errors will be reported. ** In other words, the analysis stops as soon as reg(P1) errors are ** seen. Reg(P1) is updated with the number of errors remaining. ** ** The root page numbers of all tables in the database are integers @@ -5642,18 +5642,18 @@ assert( (pnErr->flags & (MEM_Str|MEM_Blob))==0 ); pIn1 = &aMem[pOp->p1]; assert( pOp->p5nDb ); assert( DbMaskTest(p->btreeMask, pOp->p5) ); z = sqlite3BtreeIntegrityCheck(db->aDb[pOp->p5].pBt, aRoot, nRoot, - (int)pnErr->u.i, &nErr); - pnErr->u.i -= nErr; + (int)pnErr->u.i+1, &nErr); sqlite3VdbeMemSetNull(pIn1); if( nErr==0 ){ assert( z==0 ); }else if( z==0 ){ goto no_mem; }else{ + pnErr->u.i -= nErr-1; sqlite3VdbeMemSetStr(pIn1, z, -1, SQLITE_UTF8, sqlite3_free); } UPDATE_MAX_BLOBSIZE(pIn1); sqlite3VdbeChangeEncoding(pIn1, encoding); break; Index: test/check.test ================================================================== --- test/check.test +++ test/check.test @@ -307,15 +307,19 @@ do_test check-4.8 { execsql { PRAGMA ignore_check_constraints=ON; UPDATE t4 SET x=0, y=1; SELECT * FROM t4; + PRAGMA integrity_check; } -} {0 1} +} {0 1 ok} +do_execsql_test check-4.8.1 { + PRAGMA ignore_check_constraints=OFF; + PRAGMA integrity_check; +} {{CHECK constraint failed in t4}} do_test check-4.9 { catchsql { - PRAGMA ignore_check_constraints=OFF; UPDATE t4 SET x=0, y=2; } } {1 {CHECK constraint failed: t4}} ifcapable vacuum { do_test check_4.10 {