/ Check-in [c9734c27]
Login

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

Overview
Comment:Enhance the "PRAGMA integrity_check" command to verify that all indices are correctly constructed. New calls to integrity_check are made in the test suite. These changes are intended to prevent any future problems such as seen in ticket #334. (CVS 1024)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c9734c27074d2039a1896a8c6965c08d03711b13
User & Date: drh 2003-06-15 23:42:24
Context
2003-06-15
23:49
Update the documentation to reflect the new capabilities of PRAGMA integrity_check. Rewording in the VACUUM documentation. (CVS 1025) check-in: 38461a7a user: drh tags: trunk
23:42
Enhance the "PRAGMA integrity_check" command to verify that all indices are correctly constructed. New calls to integrity_check are made in the test suite. These changes are intended to prevent any future problems such as seen in ticket #334. (CVS 1024) check-in: c9734c27 user: drh tags: trunk
10:35
Updated sqlite_encode_binary() comments with tighter bounds on output length. (CVS 1023) check-in: 826aab43 user: jplyon tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/encode.c.

    11     11   *************************************************************************
    12     12   ** This file contains helper routines used to translate binary data into
    13     13   ** a null-terminated string (suitable for use in SQLite) and back again.
    14     14   ** These are convenience routines for use by people who want to store binary
    15     15   ** data in an SQLite database.  The code in this file is not used by any other
    16     16   ** part of the SQLite library.
    17     17   **
    18         -** $Id: encode.c,v 1.7 2003/06/15 10:35:05 jplyon Exp $
           18  +** $Id: encode.c,v 1.8 2003/06/15 23:42:24 drh Exp $
    19     19   */
    20     20   #include <string.h>
    21     21   
    22     22   /*
    23     23   ** How This Encoder Works
    24     24   **
    25     25   ** The output is allowed to contain any character except 0x27 (') and
................................................................................
    34     34   **
    35     35   **       0x00  ->  0x01 0x01
    36     36   **       0x01  ->  0x01 0x02
    37     37   **       0x27  ->  0x01 0x03
    38     38   **
    39     39   ** If that were all the encoder did, it would work, but in certain cases
    40     40   ** it could double the size of the encoded string.  For example, to
    41         -** encode a string of 100 0x27 character would require 100 instances of
           41  +** encode a string of 100 0x27 characters would require 100 instances of
    42     42   ** the 0x01 0x03 escape sequence resulting in a 200-character output.
    43     43   ** We would prefer to keep the size of the encoded string smaller than
    44     44   ** this.
    45     45   **
    46     46   ** To minimize the encoding size, we first add a fixed offset value to each 
    47     47   ** byte in the sequence.  The addition is modulo 256.  (That is to say, if
    48     48   ** the sum of the original character value and the offset exceeds 256, then

Changes to src/pragma.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code used to implement the PRAGMA command.
    13     13   **
    14         -** $Id: pragma.c,v 1.9 2003/06/04 15:48:33 drh Exp $
           14  +** $Id: pragma.c,v 1.10 2003/06/15 23:42:24 drh Exp $
    15     15   */
    16     16   #include "sqliteInt.h"
    17     17   #include <ctype.h>
    18     18   
    19     19   /*
    20     20   ** Interpret the given string as a boolean value.
    21     21   */
................................................................................
   505    505       }else{
   506    506         sqliteParserTrace(0, 0);
   507    507       }
   508    508     }else
   509    509   #endif
   510    510   
   511    511     if( sqliteStrICmp(zLeft, "integrity_check")==0 ){
          512  +    int i, j, addr;
          513  +
          514  +    /* Code that initializes the integrity check program.  Set the
          515  +    ** error message to an empty string and register the callback
          516  +    ** column name.
          517  +    */
          518  +    static VdbeOp initCode[] = {
          519  +      { OP_String,      0, 0,        ""},
          520  +      { OP_MemStore,    0, 1,        0},
          521  +      { OP_ColumnName,  0, 0,        "integrity_check"},
          522  +    };
          523  +
          524  +    /* Code to do an BTree integrity check on a single database file.
          525  +    */
   512    526       static VdbeOp checkDb[] = {
   513    527         { OP_SetInsert,   0, 0,        "2"},
   514         -      { OP_Integer,     0, 0,        0},   
          528  +      { OP_Integer,     0, 0,        0},    /* 1 */
   515    529         { OP_OpenRead,    0, 2,        0},
   516         -      { OP_Rewind,      0, 7,        0},
          530  +      { OP_Rewind,      0, 7,        0},    /* 3 */
   517    531         { OP_Column,      0, 3,        0},    /* 4 */
   518    532         { OP_SetInsert,   0, 0,        0},
   519         -      { OP_Next,        0, 4,        0},
          533  +      { OP_Next,        0, 4,        0},    /* 6 */
   520    534         { OP_IntegrityCk, 0, 0,        0},    /* 7 */
   521         -      { OP_ColumnName,  0, 0,        "integrity_check"},
   522         -      { OP_Callback,    1, 0,        0},
   523         -      { OP_SetInsert,   1, 0,        "2"},
   524         -      { OP_Integer,     1, 0,        0},
   525         -      { OP_OpenRead,    1, 2,        0},
   526         -      { OP_Rewind,      1, 17,       0},
   527         -      { OP_Column,      1, 3,        0},    /* 14 */
   528         -      { OP_SetInsert,   1, 0,        0},
   529         -      { OP_Next,        1, 14,       0},
   530         -      { OP_IntegrityCk, 1, 1,        0},    /* 17 */
          535  +      { OP_Dup,         0, 1,        0},
          536  +      { OP_String,      0, 0,        "ok"},
          537  +      { OP_StrEq,       0, 12,       0},    /* 10 */
          538  +      { OP_MemLoad,     0, 0,        0},
          539  +      { OP_String,      0, 0,        "*** in database "},
          540  +      { OP_String,      0, 0,        0},    /* 13 */
          541  +      { OP_String,      0, 0,        " ***\n"},
          542  +      { OP_Pull,        4, 0,        0},
          543  +      { OP_Concat,      5, 1,        0},
          544  +      { OP_MemStore,    0, 1,        0},
          545  +      { OP_Integer,     0, 0,        0},
          546  +      { OP_Pop,         1, 0,        0},
          547  +    };
          548  +
          549  +    /* Code that appears at the end of the integrity check.  If no error
          550  +    ** messages have been generated, output OK.  Otherwise output the
          551  +    ** error message
          552  +    */
          553  +    static VdbeOp endCode[] = {
          554  +      { OP_MemLoad,     0, 0,        0},
          555  +      { OP_Dup,         0, 1,        0},
          556  +      { OP_String,      0, 0,        ""},
          557  +      { OP_StrNe,       0, 0,        0},    /* 3 */
          558  +      { OP_Pop,         1, 0,        0},
          559  +      { OP_String,      0, 0,        "ok"},
   531    560         { OP_Callback,    1, 0,        0},
   532    561       };
   533         -    sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
          562  +
          563  +    /* Initialize the VDBE program */
          564  +    sqliteVdbeAddOpList(v, ArraySize(initCode), initCode);
          565  +
          566  +    /* Do an integrity check on each database file */
          567  +    for(i=0; i<db->nDb; i++){
          568  +      HashElem *x;
          569  +
          570  +      /* Do an integrity check of the B-Tree
          571  +      */
          572  +      addr = sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
          573  +      sqliteVdbeChangeP1(v, addr+1, i);
          574  +      sqliteVdbeChangeP2(v, addr+3, addr+7);
          575  +      sqliteVdbeChangeP2(v, addr+6, addr+4);
          576  +      sqliteVdbeChangeP2(v, addr+7, i);
          577  +      sqliteVdbeChangeP2(v, addr+10, addr+ArraySize(checkDb)-1);
          578  +      sqliteVdbeChangeP3(v, addr+13, db->aDb[i].zName, P3_STATIC);
          579  +
          580  +      /* Make sure all the indices are constructed correctly.
          581  +      */
          582  +      sqliteCodeVerifySchema(pParse, i);
          583  +      for(x=sqliteHashFirst(&db->aDb[i].tblHash); x; x=sqliteHashNext(x)){
          584  +        Table *pTab = sqliteHashData(x);
          585  +        Index *pIdx;
          586  +        int loopTop;
          587  +
          588  +        if( pTab->pIndex==0 ) continue;
          589  +        sqliteVdbeAddOp(v, OP_Integer, i, 0);
          590  +        sqliteVdbeAddOp(v, OP_OpenRead, 1, pTab->tnum);
          591  +        sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
          592  +        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
          593  +          if( pIdx->tnum==0 ) continue;
          594  +          sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
          595  +          sqliteVdbeAddOp(v, OP_OpenRead, j+2, pIdx->tnum);
          596  +          sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
          597  +        }
          598  +        sqliteVdbeAddOp(v, OP_Integer, 0, 0);
          599  +        sqliteVdbeAddOp(v, OP_MemStore, 1, 1);
          600  +        loopTop = sqliteVdbeAddOp(v, OP_Rewind, 1, 0);
          601  +        sqliteVdbeAddOp(v, OP_MemIncr, 1, 0);
          602  +        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
          603  +          int k, jmp2;
          604  +          static VdbeOp idxErr[] = {
          605  +            { OP_MemLoad,     0,  0,  0},
          606  +            { OP_String,      0,  0,  "rowid "},
          607  +            { OP_Recno,       1,  0,  0},
          608  +            { OP_String,      0,  0,  " missing from index "},
          609  +            { OP_String,      0,  0,  0},    /* 4 */
          610  +            { OP_String,      0,  0,  "\n"},
          611  +            { OP_Concat,      6,  0,  0},
          612  +            { OP_MemStore,    0,  1,  0},
          613  +          };
          614  +          sqliteVdbeAddOp(v, OP_Recno, 1, 0);
          615  +          for(k=0; k<pIdx->nColumn; k++){
          616  +            int idx = pIdx->aiColumn[k];
          617  +            if( idx==pTab->iPKey ){
          618  +              sqliteVdbeAddOp(v, OP_Recno, 1, 0);
          619  +            }else{
          620  +              sqliteVdbeAddOp(v, OP_Column, 1, idx);
          621  +            }
          622  +          }
          623  +          sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
          624  +          if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
          625  +          jmp2 = sqliteVdbeAddOp(v, OP_Found, j+2, 0);
          626  +          addr = sqliteVdbeAddOpList(v, ArraySize(idxErr), idxErr);
          627  +          sqliteVdbeChangeP3(v, addr+4, pIdx->zName, P3_STATIC);
          628  +          sqliteVdbeChangeP2(v, jmp2, sqliteVdbeCurrentAddr(v));
          629  +        }
          630  +        sqliteVdbeAddOp(v, OP_Next, 1, loopTop+1);
          631  +        sqliteVdbeChangeP2(v, loopTop, sqliteVdbeCurrentAddr(v));
          632  +        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
          633  +          static VdbeOp cntIdx[] = {
          634  +             { OP_Integer,      0,  0,  0},
          635  +             { OP_MemStore,     2,  1,  0},
          636  +             { OP_Rewind,       0,  0,  0},  /* 2 */
          637  +             { OP_MemIncr,      2,  0,  0},
          638  +             { OP_Next,         0,  0,  0},  /* 4 */
          639  +             { OP_MemLoad,      1,  0,  0},
          640  +             { OP_MemLoad,      2,  0,  0},
          641  +             { OP_Eq,           0,  0,  0},  /* 7 */
          642  +             { OP_MemLoad,      0,  0,  0},
          643  +             { OP_String,       0,  0,  "wrong # of entries in index "},
          644  +             { OP_String,       0,  0,  0},  /* 10 */
          645  +             { OP_String,       0,  0,  "\n"},
          646  +             { OP_Concat,       4,  0,  0},
          647  +             { OP_MemStore,     0,  1,  0},
          648  +          };
          649  +          if( pIdx->tnum==0 ) continue;
          650  +          addr = sqliteVdbeAddOpList(v, ArraySize(cntIdx), cntIdx);
          651  +          sqliteVdbeChangeP1(v, addr+2, j+2);
          652  +          sqliteVdbeChangeP2(v, addr+2, addr+5);
          653  +          sqliteVdbeChangeP1(v, addr+4, j+2);
          654  +          sqliteVdbeChangeP2(v, addr+4, addr+3);
          655  +          sqliteVdbeChangeP2(v, addr+7, addr+ArraySize(cntIdx));
          656  +          sqliteVdbeChangeP3(v, addr+10, pIdx->zName, P3_STATIC);
          657  +        }
          658  +      } 
          659  +    }
          660  +    addr = sqliteVdbeAddOpList(v, ArraySize(endCode), endCode);
          661  +    sqliteVdbeChangeP2(v, addr+3, addr+ArraySize(endCode)-1);
   534    662     }else
   535    663   
   536    664     {}
   537    665     sqliteFree(zLeft);
   538    666     sqliteFree(zRight);
   539    667   }

Changes to src/vdbe.c.

    32     32   **
    33     33   ** Various scripts scan this source file in order to generate HTML
    34     34   ** documentation, headers files, or other derived files.  The formatting
    35     35   ** of the code in this file is, therefore, important.  See other comments
    36     36   ** in this file for details.  If in doubt, do not deviate from existing
    37     37   ** commenting and indentation practices when changing or adding code.
    38     38   **
    39         -** $Id: vdbe.c,v 1.227 2003/06/07 11:33:45 drh Exp $
           39  +** $Id: vdbe.c,v 1.228 2003/06/15 23:42:24 drh Exp $
    40     40   */
    41     41   #include "sqliteInt.h"
    42     42   #include "os.h"
    43     43   #include <ctype.h>
    44     44   
    45     45   /*
    46     46   ** The makefile scans this source file and creates the following
................................................................................
   482    482   #endif
   483    483       }
   484    484       p->nOp += nOp;
   485    485     }
   486    486     return addr;
   487    487   }
   488    488   
   489         -#if 0 /* NOT USED */
   490    489   /*
   491    490   ** Change the value of the P1 operand for a specific instruction.
   492    491   ** This routine is useful when a large program is loaded from a
   493    492   ** static array using sqliteVdbeAddOpList but we want to make a
   494    493   ** few minor changes to the program.
   495    494   */
   496    495   void sqliteVdbeChangeP1(Vdbe *p, int addr, int val){
   497    496     assert( p->magic==VDBE_MAGIC_INIT );
   498    497     if( p && addr>=0 && p->nOp>addr && p->aOp ){
   499    498       p->aOp[addr].p1 = val;
   500    499     }
   501    500   }
   502         -#endif /* NOT USED */
   503    501   
   504    502   /*
   505    503   ** Change the value of the P2 operand for a specific instruction.
   506    504   ** This routine is useful for setting a jump destination.
   507    505   */
   508    506   void sqliteVdbeChangeP2(Vdbe *p, int addr, int val){
   509    507     assert( val>=0 );
................................................................................
  4623   4621   /* Opcode: IntegrityCk P1 P2 *
  4624   4622   **
  4625   4623   ** Do an analysis of the currently open database.  Push onto the
  4626   4624   ** stack the text of an error message describing any problems.
  4627   4625   ** If there are no errors, push a "ok" onto the stack.
  4628   4626   **
  4629   4627   ** P1 is the index of a set that contains the root page numbers
  4630         -** for all tables and indices in the main database file.
         4628  +** for all tables and indices in the main database file.  The set
         4629  +** is cleared by this opcode.  In other words, after this opcode
         4630  +** has executed, the set will be empty.
  4631   4631   **
  4632   4632   ** If P2 is not zero, the check is done on the auxiliary database
  4633   4633   ** file, not the main database file.
  4634   4634   **
  4635   4635   ** This opcode is used for testing purposes only.
  4636   4636   */
  4637   4637   case OP_IntegrityCk: {
................................................................................
  4649   4649     nRoot = sqliteHashCount(&pSet->hash);
  4650   4650     aRoot = sqliteMallocRaw( sizeof(int)*(nRoot+1) );
  4651   4651     if( aRoot==0 ) goto no_mem;
  4652   4652     for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
  4653   4653       toInt((char*)sqliteHashKey(i), &aRoot[j]);
  4654   4654     }
  4655   4655     aRoot[j] = 0;
         4656  +  sqliteHashClear(&pSet->hash);
         4657  +  pSet->prev = 0;
  4656   4658     z = sqliteBtreeIntegrityCheck(db->aDb[pOp->p2].pBt, aRoot, nRoot);
  4657   4659     if( z==0 || z[0]==0 ){
  4658   4660       if( z ) sqliteFree(z);
  4659   4661       zStack[tos] = "ok";
  4660   4662       aStack[tos].n = 3;
  4661   4663       aStack[tos].flags = STK_Str | STK_Static;
  4662   4664     }else{

Changes to test/attach.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is testing the ATTACH and DETACH commands
    13     13   # and related functionality.
    14     14   #
    15         -# $Id: attach.test,v 1.9 2003/06/04 15:53:02 drh Exp $
           15  +# $Id: attach.test,v 1.10 2003/06/15 23:42:25 drh Exp $
    16     16   #
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   for {set i 2} {$i<=15} {incr i} {
    22     22     file delete -force test$i.db
................................................................................
   141    141     }
   142    142   } {0 {}}
   143    143   do_test attach-1.19 {
   144    144     catchsql {
   145    145       ATTACH 'test.db' as db12;
   146    146     }
   147    147   } {1 {too many attached databases - max 10}}
   148         -do_test attach-1.20 {
          148  +do_test attach-1.20.1 {
   149    149     execsql {
   150    150       DETACH db5;
   151    151     }
   152    152     db_list db
   153    153   } {0 main 1 temp 2 db2 3 db3 4 db4 5 db11 6 db6 7 db7 8 db8 9 db9 10 db10}
          154  +integrity_check attach-1.20.2
   154    155   do_test attach-1.21 {
   155    156     catchsql {
   156    157       ATTACH 'test.db' as db12;
   157    158     }
   158    159   } {0 {}}
   159    160   do_test attach-1.22 {
   160    161     catchsql {

Changes to test/conflict.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for the conflict resolution extension
    14     14   # to SQLite.
    15     15   #
    16         -# $Id: conflict.test,v 1.17 2003/05/16 02:30:27 drh Exp $
           16  +# $Id: conflict.test,v 1.18 2003/06/15 23:42:25 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Create tables for the first group of tests.
    22     22   #
    23     23   do_test conflict-1.0 {
................................................................................
   684    684       BEGIN ON CONFLICT ROLLBACK;
   685    685       INSERT INTO t4 VALUES(1);
   686    686       INSERT INTO t4 VALUES(1);
   687    687       COMMIT;
   688    688     }
   689    689     execsql {SELECT * FROM t4}
   690    690   } {}
          691  +
          692  +integrity_check conflict-99.0
   691    693   
   692    694   finish_test

Changes to test/delete.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the DELETE FROM statement.
    13     13   #
    14         -# $Id: delete.test,v 1.12 2003/05/04 20:42:56 drh Exp $
           14  +# $Id: delete.test,v 1.13 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to delete from a non-existant table.
    20     20   #
    21     21   do_test delete-1.1 {
................................................................................
    56     56   } {1 2 2 4 4 16}
    57     57   do_test delete-3.1.6 {
    58     58     execsql {DELETE FROM table1 WHERE f1=2}
    59     59   } {1}
    60     60   do_test delete-3.1.7 {
    61     61     execsql {SELECT * FROM table1 ORDER BY f1}
    62     62   } {1 2 4 16}
           63  +integrity_check delete-3.2
           64  +
    63     65   
    64     66   # Semantic errors in the WHERE clause
    65     67   #
    66     68   do_test delete-4.1 {
    67     69     execsql {CREATE TABLE table2(f1 int, f2 int)}
    68     70     set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
    69     71     lappend v $msg
    70     72   } {1 {no such column: f3}}
    71     73   
    72     74   do_test delete-4.2 {
    73     75     set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
    74     76     lappend v $msg
    75     77   } {1 {no such function: xyzzy}}
           78  +integrity_check delete-4.3
    76     79   
    77     80   # Lots of deletes
    78     81   #
    79     82   do_test delete-5.1.1 {
    80     83     execsql {DELETE FROM table1}
    81     84   } {2}
    82     85   do_test delete-5.1.2 {
................................................................................
   138    141     }
   139    142     execsql {SELECT f1 FROM table1 ORDER BY f1}
   140    143   } {42 44 47 48 50}
   141    144   do_test delete-5.7 {
   142    145     execsql "DELETE FROM table1 WHERE f1!=48"
   143    146     execsql {SELECT f1 FROM table1 ORDER BY f1}
   144    147   } {48}
          148  +integrity_check delete-5.8
          149  +
   145    150   
   146    151   # Delete large quantities of data.  We want to test the List overflow
   147    152   # mechanism in the vdbe.
   148    153   #
   149    154   do_test delete-6.1 {
   150    155     set fd [open data1.txt w]
   151    156     for {set i 1} {$i<=3000} {incr i} {
................................................................................
   188    193     execsql {DELETE FROM table2}
   189    194     execsql {SELECT f1 FROM table2}
   190    195   } {}
   191    196   do_test delete-6.10 {
   192    197     execsql {INSERT INTO table2 VALUES(2,3)}
   193    198     execsql {SELECT f1 FROM table2}
   194    199   } {2}
          200  +integrity_check delete-6.11
   195    201   
   196    202   do_test delete-7.1 {
   197    203     execsql {
   198    204       CREATE TABLE t3(a);
   199    205       INSERT INTO t3 VALUES(1);
   200    206       INSERT INTO t3 SELECT a+1 FROM t3;
   201    207       INSERT INTO t3 SELECT a+2 FROM t3;
................................................................................
   236    242       INSERT INTO t3 SELECT a+2 FROM t3;
   237    243       CREATE TABLE t4 AS SELECT * FROM t3;
   238    244       PRAGMA count_changes=ON;
   239    245       DELETE FROM t3;
   240    246       DELETE FROM t4;
   241    247     }
   242    248   } {4 4}
          249  +integrity_check delete-7.7
   243    250   
   244    251   # Make sure error messages are consistent when attempting to delete
   245    252   # from a read-only database.  Ticket #304.
   246    253   #
   247    254   do_test delete-8.0 {
   248    255     execsql {
   249    256       PRAGMA count_changes=OFF;
................................................................................
   275    282     catchsql {
   276    283       DELETE FROM t3 WHERE a<100;
   277    284     }
   278    285   } {0 {}}
   279    286   do_test delete-8.6 {
   280    287     execsql {SELECT * FROM t3}
   281    288   } {123}
   282         -
          289  +integrity_check delete-8.7
   283    290   
   284    291   finish_test

Changes to test/index.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE INDEX statement.
    13     13   #
    14         -# $Id: index.test,v 1.22 2003/04/16 02:17:36 drh Exp $
           14  +# $Id: index.test,v 1.23 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Create a basic index and verify it is added to sqlite_master
    20     20   #
    21     21   do_test index-1.1 {
................................................................................
   150    150   do_test index-4.12 {
   151    151     execsql {SELECT cnt FROM test1 WHERE power=1024}
   152    152   } {10}
   153    153   do_test index-4.13 {
   154    154     execsql {DROP TABLE test1}
   155    155     execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
   156    156   } {}
          157  +integrity_check index-4.14
   157    158   
   158    159   # Do not allow indices to be added to sqlite_master
   159    160   #
   160    161   do_test index-5.1 {
   161    162     set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
   162    163     lappend v $msg
   163    164   } {1 {table sqlite_master may not be indexed}}
................................................................................
   195    196       CREATE INDEX index1 ON test1(a);
   196    197       CREATE INDEX index2 ON test1(b);
   197    198       CREATE INDEX index3 ON test1(a,b);
   198    199       DROP TABLE test1;
   199    200       SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
   200    201     }
   201    202   } {}
          203  +integrity_check index-6.5
          204  +
   202    205   
   203    206   # Create a primary key
   204    207   #
   205    208   do_test index-7.1 {
   206    209     execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
   207    210     for {set i 1} {$i<20} {incr i} {
   208    211       execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
................................................................................
   218    221       WHERE type='index' AND tbl_name='test1'
   219    222     }
   220    223   } {{(test1 autoindex 1)}}
   221    224   do_test index-7.4 {
   222    225     execsql {DROP table test1}
   223    226     execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
   224    227   } {}
          228  +integrity_check index-7.5
   225    229   
   226    230   # Make sure we cannot drop a non-existant index.
   227    231   #
   228    232   do_test index-8.1 {
   229    233     set v [catch {execsql {DROP INDEX index1}} msg]
   230    234     lappend v $msg
   231    235   } {1 {no such index: index1}}
................................................................................
   238    242     execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
   239    243     execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
   240    244   } {tab1}
   241    245   do_test index-9.2 {
   242    246     execsql {CREATE INDEX idx1 ON tab1(a)}
   243    247     execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
   244    248   } {idx1 tab1}
          249  +integrity_check index-9.3
   245    250   
   246    251   # Allow more than one entry with the same key.
   247    252   #
   248    253   do_test index-10.0 {
   249    254     execsql {
   250    255       CREATE TABLE t1(a int, b int);
   251    256       CREATE INDEX i1 ON t1(a);
................................................................................
   308    313     }
   309    314   } {}
   310    315   do_test index-10.8 {
   311    316     execsql {
   312    317       SELECT b FROM t1 ORDER BY b;
   313    318     }
   314    319   } {0}
          320  +integrity_check index-10.9
   315    321   
   316    322   # Automatically create an index when we specify a primary key.
   317    323   #
   318    324   do_test index-11.1 {
   319    325     execsql {
   320    326       CREATE TABLE t3(
   321    327         a text,
................................................................................
   326    332     }
   327    333     for {set i 1} {$i<=50} {incr i} {
   328    334       execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
   329    335     }
   330    336     set sqlite_search_count 0
   331    337     concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
   332    338   } {0.10 3}
          339  +integrity_check index-11.2
          340  +
   333    341   
   334    342   # Numeric strings should compare as if they were numbers.  So even if the
   335    343   # strings are not character-by-character the same, if they represent the
   336    344   # same number they should compare equal to one another.  Verify that this
   337    345   # is true in indices.
   338    346   #
   339    347   do_test index-12.1 {
................................................................................
   376    384     }
   377    385   } {0.0 0.00 -1.0 0 00000}
   378    386   do_test index-12.7 {
   379    387     execsql {
   380    388       SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
   381    389     }
   382    390   } {0.0 0.00 abc +1.0 0 00000}
          391  +integrity_check index-12.8
   383    392   
   384    393   # Make sure we cannot drop an automatically created index.
   385    394   #
   386    395   do_test index-13.1 {
   387    396     execsql {
   388    397      CREATE TABLE t5(
   389    398         a int UNIQUE,
................................................................................
   410    419   }
   411    420   do_test index-13.4 {
   412    421     execsql {
   413    422       INSERT INTO t5 VALUES('a','b','c');
   414    423       SELECT * FROM t5;
   415    424     }
   416    425   } {1 2 3 a b c}
          426  +integrity_check index-13.5
   417    427   
   418    428   # Check the sort order of data in an index.
   419    429   #
   420    430   do_test index-14.1 {
   421    431     execsql {
   422    432       CREATE TABLE t6(a,b,c);
   423    433       CREATE INDEX t6i1 ON t6(a,b);
................................................................................
   475    485     }
   476    486   } {3 5 2 1}
   477    487   do_test index-14.11 {
   478    488     execsql {
   479    489       SELECT c FROM t6 WHERE a<'';
   480    490     }
   481    491   } {3 5}
          492  +integrity_check index-14.12
   482    493   
   483    494   do_test index-15.1 {
   484    495     execsql {
   485    496       DELETE FROM t1;
   486    497       SELECT * FROM t1;
   487    498     }
   488    499   } {}
................................................................................
   498    509       INSERT INTO t1 VALUES('+123.10000E+0003',8);
   499    510       INSERT INTO t1 VALUES('+',9);
   500    511       INSERT INTO t1 VALUES('+12347.E+02',10);
   501    512       INSERT INTO t1 VALUES('+12347E+02',11);
   502    513       SELECT b FROM t1 ORDER BY a;
   503    514     }
   504    515   } {8 5 2 1 3 6 11 9 10 4 7}
          516  +integrity_check index-15.1
   505    517   
   506    518   finish_test

Changes to test/insert.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the INSERT statement.
    13     13   #
    14         -# $Id: insert.test,v 1.14 2003/06/04 16:24:40 drh Exp $
           14  +# $Id: insert.test,v 1.15 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to insert into a non-existant table.
    20     20   #
    21     21   do_test insert-1.1 {
................................................................................
   276    276     }
   277    277   } {2 4}
   278    278   do_test insert-6.4 {
   279    279     execsql {
   280    280       SELECT * FROM t1 WHERE b=3;
   281    281     }
   282    282   } {}
          283  +
          284  +integrity_check insert-99.0
   283    285   
   284    286   finish_test

Changes to test/intpkey.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for the special processing associated
    14     14   # with INTEGER PRIMARY KEY columns.
    15     15   #
    16         -# $Id: intpkey.test,v 1.13 2003/03/07 19:50:08 drh Exp $
           16  +# $Id: intpkey.test,v 1.14 2003/06/15 23:42:25 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Create a table with a primary key and a datatype other than
    22     22   # integer
    23     23   #
................................................................................
   477    477     }
   478    478   } {hello}
   479    479   do_test intpkey-11.1 {
   480    480     execsql {
   481    481       SELECT b FROM t1 WHERE a=2.0+3.5;
   482    482     }
   483    483   } {}
          484  +
          485  +integrity_check intpkey-12.1
   484    486   
   485    487   finish_test

Changes to test/memdb.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is in-memory database backend.
    13     13   #
    14         -# $Id: memdb.test,v 1.4 2003/05/16 02:30:27 drh Exp $
           14  +# $Id: memdb.test,v 1.5 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # In the following sequence of tests, compute the MD5 sum of the content
    21     21   # of a table, make lots of modifications to that table, then do a rollback.
................................................................................
   112    112     set ::pager_old_format 0
   113    113   }
   114    114   
   115    115   do_test memdb-2.1 {
   116    116     execsql {
   117    117       PRAGMA integrity_check
   118    118     }
   119         -} {ok ok}
          119  +} {ok}
   120    120   
   121    121   do_test memdb-3.1 {
   122    122     execsql {
   123    123       CREATE TABLE t4(a,b,c,d);
   124    124       BEGIN;
   125    125       INSERT INTO t4 VALUES(1,2,3,4);
   126    126       SELECT * FROM t4;

Changes to test/pragma.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for the PRAGMA command.
    14     14   #
    15         -# $Id: pragma.test,v 1.4 2003/02/15 23:09:17 drh Exp $
           15  +# $Id: pragma.test,v 1.5 2003/06/15 23:42:25 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Delete the preexisting database to avoid the special setup
    21     21   # that the "all.test" script does.
    22     22   #
................................................................................
   223    223     }
   224    224   } {WHATEVER BLOB}
   225    225   do_test pragma-2.10 {
   226    226     sqlite_datatypes $::DB {
   227    227       SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
   228    228     }
   229    229   } {WHATEVER BLOB}
   230         -    
   231    230   
          231  +# Construct a corrupted index and make sure the integrity_check
          232  +# pragma finds it.
          233  +#
          234  +do_test pragma-3.1 {
          235  +  execsql {
          236  +    BEGIN;
          237  +    CREATE TABLE t2(a,b,c);
          238  +    CREATE INDEX i2 ON t2(a);
          239  +    INSERT INTO t2 VALUES(11,2,3);
          240  +    INSERT INTO t2 VALUES(22,3,4);
          241  +    COMMIT;
          242  +    SELECT rowid, * from t2;
          243  +  }
          244  +} {1 11 2 3 2 22 3 4}
          245  +do_test pragma-3.2 {
          246  +  set rootpage [execsql {SELECT rootpage FROM sqlite_master WHERE name='i2'}]
          247  +  set db [btree_open test.db]
          248  +  btree_begin_transaction $db
          249  +  set c [btree_cursor $db $rootpage 1]
          250  +  btree_first $c
          251  +  btree_delete $c
          252  +  btree_commit $db
          253  +  btree_close $db
          254  +  execsql {PRAGMA integrity_check}
          255  +} {{rowid 1 missing from index i2
          256  +wrong # of entries in index i2
          257  +}}
   232    258   
   233    259   finish_test

Changes to test/tester.tcl.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements some common TCL routines used for regression
    12     12   # testing the SQLite library
    13     13   #
    14         -# $Id: tester.tcl,v 1.25 2003/03/01 19:45:35 drh Exp $
           14  +# $Id: tester.tcl,v 1.26 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   # Make sure tclsqlite was compiled correctly.  Abort now with an
    17     17   # error message if not.
    18     18   #
    19     19   if {[sqlite -tcl-uses-utf]} {
    20     20     if {"\u1234"=="u1234"} {
    21     21       puts stderr "***** BUILD PROBLEM *****"
................................................................................
   243    243   }
   244    244   
   245    245   # Do an integrity check of the entire database
   246    246   #
   247    247   proc integrity_check {name} {
   248    248     do_test $name {
   249    249       execsql {PRAGMA integrity_check}
   250         -  } {ok ok}
          250  +  } {ok}
   251    251   }

Changes to test/trigger1.test.

   303    303     db close
   304    304     sqlite db test.db
   305    305     execsql {
   306    306       SELECT * FROM t2;
   307    307     };
   308    308   } {3 4 7 8}
   309    309   
          310  +integrity_check trigger-5.1
   310    311   
   311    312   finish_test

Changes to test/trigger2.test.

   182    182     } [list 1 0 0 0 0 5 6 \
   183    183             2 0 0 5 6 5 6 ]
   184    184   
   185    185     do_test trigger2-1.$ii.4 {
   186    186       execsql {
   187    187         PRAGMA integrity_check;
   188    188       }
   189         -  } {ok ok}
          189  +  } {ok}
   190    190   }
   191    191   catchsql {
   192    192     DROP TABLE rlog;
   193    193     DROP TABLE clog;
   194    194     DROP TABLE tbl;
   195    195     DROP TABLE other_tbl;
   196    196   }
................................................................................
   304    304       do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
   305    305       execsql "DROP TRIGGER the_trigger;"
   306    306   
   307    307       do_test trigger2-2.$ii-integrity {
   308    308         execsql {
   309    309           PRAGMA integrity_check;
   310    310         }
   311         -    } {ok ok}
          311  +    } {ok}
   312    312   
   313    313     }
   314    314   }
   315    315   catchsql {
   316    316     DROP TABLE tbl;
   317    317     DROP TABLE log;
   318    318   }
................................................................................
   380    380     DROP TABLE tbl;
   381    381     DROP TABLE log;
   382    382   }
   383    383   do_test trigger2-3.3 {
   384    384     execsql {
   385    385       PRAGMA integrity_check;
   386    386     }
   387         -} {ok ok}
          387  +} {ok}
   388    388   
   389    389   # Simple cascaded trigger
   390    390   execsql {
   391    391     CREATE TABLE tblA(a, b);
   392    392     CREATE TABLE tblB(a, b);
   393    393     CREATE TABLE tblC(a, b);
   394    394   
................................................................................
   710    710       UPDATE v1 SET x=x+100, y=y+200, z=z+300;
   711    711       SELECT * FROM v1log;
   712    712     }
   713    713   } {3 103 5 205 4 304 9 109 11 211 10 310}
   714    714   
   715    715   do_test trigger2-9.9 {
   716    716     execsql {PRAGMA integrity_check}
   717         -} {ok ok}
          717  +} {ok}
   718    718   
   719    719   finish_test

Changes to test/trigger3.test.

   152    152       }
   153    153   } {0 {}}
   154    154   do_test trigger3-7.3 {
   155    155       catchsql {
   156    156   	INSERT INTO tbl_view VALUES(3, 2, 3);
   157    157       }
   158    158   } {1 {View abort}}
          159  +
          160  +integrity_check trigger3-8.1
   159    161   
   160    162   catchsql { DROP TABLE tbl; } 
   161    163   catchsql { DROP TABLE tbl2; } 
   162    164   catchsql { DROP VIEW tbl_view; }
   163    165   
   164    166   finish_test

Changes to test/trigger4.test.

   117    117     db close
   118    118     sqlite db test.db
   119    119     execsql {
   120    120       update test set b=99 where id=7;
   121    121       select * from test2;
   122    122     }
   123    123   } {7 99}
          124  +
          125  +integrity_check trigger4-4.1
   124    126   
   125    127   finish_test

Changes to test/unique.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE UNIQUE INDEX statement,
    13     13   # and primary keys, and the UNIQUE constraint on table columns
    14     14   #
    15         -# $Id: unique.test,v 1.5 2003/01/29 18:46:54 drh Exp $
           15  +# $Id: unique.test,v 1.6 2003/06/15 23:42:25 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Try to create a table with two primary keys.
    21     21   # (This is allowed in SQLite even that it is not valid SQL)
    22     22   #
................................................................................
    69     69     }
    70     70   } {0 {}}
    71     71   do_test unique-1.8 {
    72     72     execsql {
    73     73       SELECT * FROM t1 ORDER BY a;
    74     74     }
    75     75   } {1 2 3 3 4 5}
           76  +integrity_check unique-1.9
    76     77   
    77     78   do_test unique-2.0 {
    78     79     execsql {
    79     80       DROP TABLE t1;
    80     81       CREATE TABLE t2(a int, b int);
    81     82       INSERT INTO t2(a,b) VALUES(1,2);
    82     83       INSERT INTO t2(a,b) VALUES(3,4);
................................................................................
   125    126     }
   126    127   } {1 {indexed columns are not unique}}
   127    128   do_test unique-2.9 {
   128    129     catchsql {
   129    130       CREATE INDEX i2 ON t2(a);
   130    131     }
   131    132   } {0 {}}
          133  +integrity_check unique-2.10
   132    134   
   133    135   # Test the UNIQUE keyword as used on two or more fields.
   134    136   #
   135    137   do_test unique-3.1 {
   136    138     catchsql {
   137    139       CREATE TABLE t3(
   138    140          a int,
................................................................................
   157    159   } {0 {1 2 3 4 1 2 3 5}}
   158    160   do_test unique-3.4 {
   159    161     catchsql {
   160    162       INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
   161    163       SELECT * FROM t3 ORDER BY a,b,c,d;
   162    164     }
   163    165   } {1 {uniqueness constraint failed}}
          166  +integrity_check unique-3.5
   164    167   
   165    168   # Make sure NULLs are distinct as far as the UNIQUE tests are
   166    169   # concerned.
   167    170   #
   168    171   do_test unique-4.1 {
   169    172     execsql {
   170    173       CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
................................................................................
   189    192     }
   190    193   } {0 {}}
   191    194   do_test unique-4.5 {
   192    195     execsql {
   193    196       SELECT * FROM t4
   194    197     }
   195    198   } {1 2 3 {} 2 {} {} 3 4 2 2 {}}
   196         -
          199  +integrity_check unique-4.6
   197    200   
   198    201   finish_test

Changes to test/update.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the UPDATE statement.
    13     13   #
    14         -# $Id: update.test,v 1.12 2003/02/15 23:09:17 drh Exp $
           14  +# $Id: update.test,v 1.13 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to update an non-existent table
    20     20   #
    21     21   do_test update-1.1 {
................................................................................
   491    491   } {2 14 3 7}
   492    492   do_test update-11.2 {
   493    493     execsql {
   494    494       UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
   495    495       SELECT a,e FROM t1;
   496    496     }
   497    497   } {1 15 2 8}
          498  +
          499  +integrity_check update-12.1
   498    500   
   499    501   finish_test

Changes to test/where.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clases.
    13     13   #
    14         -# $Id: where.test,v 1.16 2003/04/19 17:27:25 drh Exp $
           14  +# $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Build some test data
    20     20   #
    21     21   do_test where-1.0 {
................................................................................
   733    733       return $v
   734    734     }
   735    735     execsql {
   736    736       SELECT count(*) FROM t1 WHERE tclvar('v1');
   737    737     }
   738    738   } {50}
   739    739   
          740  +integrity_check {where-99.0}
   740    741   
   741    742   finish_test