SQLite

Check-in [c9734c2707]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c9734c27074d2039a1896a8c6965c08d03711b13
User & Date: drh 2003-06-15 23:42:24.000
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: 38461a7acb 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: c9734c2707 user: drh tags: trunk)
10:35
Updated sqlite_encode_binary() comments with tighter bounds on output length. (CVS 1023) (check-in: 826aab43d5 user: jplyon tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/encode.c.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** This file contains helper routines used to translate binary data into
** a null-terminated string (suitable for use in SQLite) and back again.
** These are convenience routines for use by people who want to store binary
** data in an SQLite database.  The code in this file is not used by any other
** part of the SQLite library.
**
** $Id: encode.c,v 1.7 2003/06/15 10:35:05 jplyon Exp $
*/
#include <string.h>

/*
** How This Encoder Works
**
** The output is allowed to contain any character except 0x27 (') and







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** This file contains helper routines used to translate binary data into
** a null-terminated string (suitable for use in SQLite) and back again.
** These are convenience routines for use by people who want to store binary
** data in an SQLite database.  The code in this file is not used by any other
** part of the SQLite library.
**
** $Id: encode.c,v 1.8 2003/06/15 23:42:24 drh Exp $
*/
#include <string.h>

/*
** How This Encoder Works
**
** The output is allowed to contain any character except 0x27 (') and
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
**
**       0x00  ->  0x01 0x01
**       0x01  ->  0x01 0x02
**       0x27  ->  0x01 0x03
**
** If that were all the encoder did, it would work, but in certain cases
** it could double the size of the encoded string.  For example, to
** encode a string of 100 0x27 character would require 100 instances of
** the 0x01 0x03 escape sequence resulting in a 200-character output.
** We would prefer to keep the size of the encoded string smaller than
** this.
**
** To minimize the encoding size, we first add a fixed offset value to each 
** byte in the sequence.  The addition is modulo 256.  (That is to say, if
** the sum of the original character value and the offset exceeds 256, then







|







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
**
**       0x00  ->  0x01 0x01
**       0x01  ->  0x01 0x02
**       0x27  ->  0x01 0x03
**
** If that were all the encoder did, it would work, but in certain cases
** it could double the size of the encoded string.  For example, to
** encode a string of 100 0x27 characters would require 100 instances of
** the 0x01 0x03 escape sequence resulting in a 200-character output.
** We would prefer to keep the size of the encoded string smaller than
** this.
**
** To minimize the encoding size, we first add a fixed offset value to each 
** byte in the sequence.  The addition is modulo 256.  (That is to say, if
** the sum of the original character value and the offset exceeds 256, then
Changes to src/pragma.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2003 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.9 2003/06/04 15:48:33 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Interpret the given string as a boolean value.
*/













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2003 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.10 2003/06/15 23:42:24 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Interpret the given string as a boolean value.
*/
505
506
507
508
509
510
511














512
513
514
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
    }else{
      sqliteParserTrace(0, 0);
    }
  }else
#endif

  if( sqliteStrICmp(zLeft, "integrity_check")==0 ){














    static VdbeOp checkDb[] = {
      { OP_SetInsert,   0, 0,        "2"},
      { OP_Integer,     0, 0,        0},   
      { OP_OpenRead,    0, 2,        0},
      { OP_Rewind,      0, 7,        0},
      { OP_Column,      0, 3,        0},    /* 4 */
      { OP_SetInsert,   0, 0,        0},
      { OP_Next,        0, 4,        0},
      { OP_IntegrityCk, 0, 0,        0},    /* 7 */

      { OP_ColumnName,  0, 0,        "integrity_check"},





      { OP_Callback,    1, 0,        0},
      { OP_SetInsert,   1, 0,        "2"},

      { OP_Integer,     1, 0,        0},
      { OP_OpenRead,    1, 2,        0},

      { OP_Rewind,      1, 17,       0},






      { OP_Column,      1, 3,        0},    /* 14 */
      { OP_SetInsert,   1, 0,        0},
      { OP_Next,        1, 14,       0},
      { OP_IntegrityCk, 1, 1,        0},    /* 17 */

      { OP_Callback,    1, 0,        0},
    };










    sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);

























































































  }else

  {}
  sqliteFree(zLeft);
  sqliteFree(zRight);
}







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


|

|


|

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


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






505
506
507
508
509
510
511
512
513
514
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
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
    }else{
      sqliteParserTrace(0, 0);
    }
  }else
#endif

  if( sqliteStrICmp(zLeft, "integrity_check")==0 ){
    int i, j, addr;

    /* Code that initializes the integrity check program.  Set the
    ** error message to an empty string and register the callback
    ** column name.
    */
    static VdbeOp initCode[] = {
      { OP_String,      0, 0,        ""},
      { OP_MemStore,    0, 1,        0},
      { OP_ColumnName,  0, 0,        "integrity_check"},
    };

    /* Code to do an BTree integrity check on a single database file.
    */
    static VdbeOp checkDb[] = {
      { OP_SetInsert,   0, 0,        "2"},
      { OP_Integer,     0, 0,        0},    /* 1 */
      { OP_OpenRead,    0, 2,        0},
      { OP_Rewind,      0, 7,        0},    /* 3 */
      { OP_Column,      0, 3,        0},    /* 4 */
      { OP_SetInsert,   0, 0,        0},
      { OP_Next,        0, 4,        0},    /* 6 */
      { OP_IntegrityCk, 0, 0,        0},    /* 7 */
      { OP_Dup,         0, 1,        0},
      { OP_String,      0, 0,        "ok"},
      { OP_StrEq,       0, 12,       0},    /* 10 */
      { OP_MemLoad,     0, 0,        0},
      { OP_String,      0, 0,        "*** in database "},
      { OP_String,      0, 0,        0},    /* 13 */
      { OP_String,      0, 0,        " ***\n"},
      { OP_Pull,        4, 0,        0},
      { OP_Concat,      5, 1,        0},
      { OP_MemStore,    0, 1,        0},
      { OP_Integer,     0, 0,        0},
      { OP_Pop,         1, 0,        0},
    };

    /* Code that appears at the end of the integrity check.  If no error
    ** messages have been generated, output OK.  Otherwise output the
    ** error message
    */
    static VdbeOp endCode[] = {
      { OP_MemLoad,     0, 0,        0},
      { OP_Dup,         0, 1,        0},
      { OP_String,      0, 0,        ""},
      { OP_StrNe,       0, 0,        0},    /* 3 */
      { OP_Pop,         1, 0,        0},
      { OP_String,      0, 0,        "ok"},
      { OP_Callback,    1, 0,        0},
    };

    /* Initialize the VDBE program */
    sqliteVdbeAddOpList(v, ArraySize(initCode), initCode);

    /* Do an integrity check on each database file */
    for(i=0; i<db->nDb; i++){
      HashElem *x;

      /* Do an integrity check of the B-Tree
      */
      addr = sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
      sqliteVdbeChangeP1(v, addr+1, i);
      sqliteVdbeChangeP2(v, addr+3, addr+7);
      sqliteVdbeChangeP2(v, addr+6, addr+4);
      sqliteVdbeChangeP2(v, addr+7, i);
      sqliteVdbeChangeP2(v, addr+10, addr+ArraySize(checkDb)-1);
      sqliteVdbeChangeP3(v, addr+13, db->aDb[i].zName, P3_STATIC);

      /* Make sure all the indices are constructed correctly.
      */
      sqliteCodeVerifySchema(pParse, i);
      for(x=sqliteHashFirst(&db->aDb[i].tblHash); x; x=sqliteHashNext(x)){
        Table *pTab = sqliteHashData(x);
        Index *pIdx;
        int loopTop;

        if( pTab->pIndex==0 ) continue;
        sqliteVdbeAddOp(v, OP_Integer, i, 0);
        sqliteVdbeAddOp(v, OP_OpenRead, 1, pTab->tnum);
        sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
          if( pIdx->tnum==0 ) continue;
          sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
          sqliteVdbeAddOp(v, OP_OpenRead, j+2, pIdx->tnum);
          sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
        }
        sqliteVdbeAddOp(v, OP_Integer, 0, 0);
        sqliteVdbeAddOp(v, OP_MemStore, 1, 1);
        loopTop = sqliteVdbeAddOp(v, OP_Rewind, 1, 0);
        sqliteVdbeAddOp(v, OP_MemIncr, 1, 0);
        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
          int k, jmp2;
          static VdbeOp idxErr[] = {
            { OP_MemLoad,     0,  0,  0},
            { OP_String,      0,  0,  "rowid "},
            { OP_Recno,       1,  0,  0},
            { OP_String,      0,  0,  " missing from index "},
            { OP_String,      0,  0,  0},    /* 4 */
            { OP_String,      0,  0,  "\n"},
            { OP_Concat,      6,  0,  0},
            { OP_MemStore,    0,  1,  0},
          };
          sqliteVdbeAddOp(v, OP_Recno, 1, 0);
          for(k=0; k<pIdx->nColumn; k++){
            int idx = pIdx->aiColumn[k];
            if( idx==pTab->iPKey ){
              sqliteVdbeAddOp(v, OP_Recno, 1, 0);
            }else{
              sqliteVdbeAddOp(v, OP_Column, 1, idx);
            }
          }
          sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
          if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
          jmp2 = sqliteVdbeAddOp(v, OP_Found, j+2, 0);
          addr = sqliteVdbeAddOpList(v, ArraySize(idxErr), idxErr);
          sqliteVdbeChangeP3(v, addr+4, pIdx->zName, P3_STATIC);
          sqliteVdbeChangeP2(v, jmp2, sqliteVdbeCurrentAddr(v));
        }
        sqliteVdbeAddOp(v, OP_Next, 1, loopTop+1);
        sqliteVdbeChangeP2(v, loopTop, sqliteVdbeCurrentAddr(v));
        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
          static VdbeOp cntIdx[] = {
             { OP_Integer,      0,  0,  0},
             { OP_MemStore,     2,  1,  0},
             { OP_Rewind,       0,  0,  0},  /* 2 */
             { OP_MemIncr,      2,  0,  0},
             { OP_Next,         0,  0,  0},  /* 4 */
             { OP_MemLoad,      1,  0,  0},
             { OP_MemLoad,      2,  0,  0},
             { OP_Eq,           0,  0,  0},  /* 7 */
             { OP_MemLoad,      0,  0,  0},
             { OP_String,       0,  0,  "wrong # of entries in index "},
             { OP_String,       0,  0,  0},  /* 10 */
             { OP_String,       0,  0,  "\n"},
             { OP_Concat,       4,  0,  0},
             { OP_MemStore,     0,  1,  0},
          };
          if( pIdx->tnum==0 ) continue;
          addr = sqliteVdbeAddOpList(v, ArraySize(cntIdx), cntIdx);
          sqliteVdbeChangeP1(v, addr+2, j+2);
          sqliteVdbeChangeP2(v, addr+2, addr+5);
          sqliteVdbeChangeP1(v, addr+4, j+2);
          sqliteVdbeChangeP2(v, addr+4, addr+3);
          sqliteVdbeChangeP2(v, addr+7, addr+ArraySize(cntIdx));
          sqliteVdbeChangeP3(v, addr+10, pIdx->zName, P3_STATIC);
        }
      } 
    }
    addr = sqliteVdbeAddOpList(v, ArraySize(endCode), endCode);
    sqliteVdbeChangeP2(v, addr+3, addr+ArraySize(endCode)-1);
  }else

  {}
  sqliteFree(zLeft);
  sqliteFree(zRight);
}
Changes to src/vdbe.c.
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.227 2003/06/07 11:33:45 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following







|







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.228 2003/06/15 23:42:24 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
#endif
    }
    p->nOp += nOp;
  }
  return addr;
}

#if 0 /* NOT USED */
/*
** Change the value of the P1 operand for a specific instruction.
** This routine is useful when a large program is loaded from a
** static array using sqliteVdbeAddOpList but we want to make a
** few minor changes to the program.
*/
void sqliteVdbeChangeP1(Vdbe *p, int addr, int val){
  assert( p->magic==VDBE_MAGIC_INIT );
  if( p && addr>=0 && p->nOp>addr && p->aOp ){
    p->aOp[addr].p1 = val;
  }
}
#endif /* NOT USED */

/*
** Change the value of the P2 operand for a specific instruction.
** This routine is useful for setting a jump destination.
*/
void sqliteVdbeChangeP2(Vdbe *p, int addr, int val){
  assert( val>=0 );







<












<







482
483
484
485
486
487
488

489
490
491
492
493
494
495
496
497
498
499
500

501
502
503
504
505
506
507
#endif
    }
    p->nOp += nOp;
  }
  return addr;
}


/*
** Change the value of the P1 operand for a specific instruction.
** This routine is useful when a large program is loaded from a
** static array using sqliteVdbeAddOpList but we want to make a
** few minor changes to the program.
*/
void sqliteVdbeChangeP1(Vdbe *p, int addr, int val){
  assert( p->magic==VDBE_MAGIC_INIT );
  if( p && addr>=0 && p->nOp>addr && p->aOp ){
    p->aOp[addr].p1 = val;
  }
}


/*
** Change the value of the P2 operand for a specific instruction.
** This routine is useful for setting a jump destination.
*/
void sqliteVdbeChangeP2(Vdbe *p, int addr, int val){
  assert( val>=0 );
4623
4624
4625
4626
4627
4628
4629
4630


4631
4632
4633
4634
4635
4636
4637
/* Opcode: IntegrityCk P1 P2 *
**
** Do an analysis of the currently open database.  Push onto the
** stack the text of an error message describing any problems.
** If there are no errors, push a "ok" onto the stack.
**
** P1 is the index of a set that contains the root page numbers
** for all tables and indices in the main database file.


**
** If P2 is not zero, the check is done on the auxiliary database
** file, not the main database file.
**
** This opcode is used for testing purposes only.
*/
case OP_IntegrityCk: {







|
>
>







4621
4622
4623
4624
4625
4626
4627
4628
4629
4630
4631
4632
4633
4634
4635
4636
4637
/* Opcode: IntegrityCk P1 P2 *
**
** Do an analysis of the currently open database.  Push onto the
** stack the text of an error message describing any problems.
** If there are no errors, push a "ok" onto the stack.
**
** P1 is the index of a set that contains the root page numbers
** for all tables and indices in the main database file.  The set
** is cleared by this opcode.  In other words, after this opcode
** has executed, the set will be empty.
**
** If P2 is not zero, the check is done on the auxiliary database
** file, not the main database file.
**
** This opcode is used for testing purposes only.
*/
case OP_IntegrityCk: {
4649
4650
4651
4652
4653
4654
4655


4656
4657
4658
4659
4660
4661
4662
  nRoot = sqliteHashCount(&pSet->hash);
  aRoot = sqliteMallocRaw( sizeof(int)*(nRoot+1) );
  if( aRoot==0 ) goto no_mem;
  for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
    toInt((char*)sqliteHashKey(i), &aRoot[j]);
  }
  aRoot[j] = 0;


  z = sqliteBtreeIntegrityCheck(db->aDb[pOp->p2].pBt, aRoot, nRoot);
  if( z==0 || z[0]==0 ){
    if( z ) sqliteFree(z);
    zStack[tos] = "ok";
    aStack[tos].n = 3;
    aStack[tos].flags = STK_Str | STK_Static;
  }else{







>
>







4649
4650
4651
4652
4653
4654
4655
4656
4657
4658
4659
4660
4661
4662
4663
4664
  nRoot = sqliteHashCount(&pSet->hash);
  aRoot = sqliteMallocRaw( sizeof(int)*(nRoot+1) );
  if( aRoot==0 ) goto no_mem;
  for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
    toInt((char*)sqliteHashKey(i), &aRoot[j]);
  }
  aRoot[j] = 0;
  sqliteHashClear(&pSet->hash);
  pSet->prev = 0;
  z = sqliteBtreeIntegrityCheck(db->aDb[pOp->p2].pBt, aRoot, nRoot);
  if( z==0 || z[0]==0 ){
    if( z ) sqliteFree(z);
    zStack[tos] = "ok";
    aStack[tos].n = 3;
    aStack[tos].flags = STK_Str | STK_Static;
  }else{
Changes to test/attach.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.9 2003/06/04 15:53:02 drh Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.10 2003/06/15 23:42:25 drh Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
141
142
143
144
145
146
147
148
149
150
151
152
153

154
155
156
157
158
159
160
  }
} {0 {}}
do_test attach-1.19 {
  catchsql {
    ATTACH 'test.db' as db12;
  }
} {1 {too many attached databases - max 10}}
do_test attach-1.20 {
  execsql {
    DETACH db5;
  }
  db_list db
} {0 main 1 temp 2 db2 3 db3 4 db4 5 db11 6 db6 7 db7 8 db8 9 db9 10 db10}

do_test attach-1.21 {
  catchsql {
    ATTACH 'test.db' as db12;
  }
} {0 {}}
do_test attach-1.22 {
  catchsql {







|





>







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
  }
} {0 {}}
do_test attach-1.19 {
  catchsql {
    ATTACH 'test.db' as db12;
  }
} {1 {too many attached databases - max 10}}
do_test attach-1.20.1 {
  execsql {
    DETACH db5;
  }
  db_list db
} {0 main 1 temp 2 db2 3 db3 4 db4 5 db11 6 db6 7 db7 8 db8 9 db9 10 db10}
integrity_check attach-1.20.2
do_test attach-1.21 {
  catchsql {
    ATTACH 'test.db' as db12;
  }
} {0 {}}
do_test attach-1.22 {
  catchsql {
Changes to test/conflict.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.17 2003/05/16 02:30:27 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.18 2003/06/15 23:42:25 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
684
685
686
687
688
689
690
691


692
    BEGIN ON CONFLICT ROLLBACK;
    INSERT INTO t4 VALUES(1);
    INSERT INTO t4 VALUES(1);
    COMMIT;
  }
  execsql {SELECT * FROM t4}
} {}



finish_test








>
>

684
685
686
687
688
689
690
691
692
693
694
    BEGIN ON CONFLICT ROLLBACK;
    INSERT INTO t4 VALUES(1);
    INSERT INTO t4 VALUES(1);
    COMMIT;
  }
  execsql {SELECT * FROM t4}
} {}

integrity_check conflict-99.0

finish_test
Changes to test/delete.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.12 2003/05/04 20:42:56 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.13 2003/06/15 23:42:25 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
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
82
} {1 2 2 4 4 16}
do_test delete-3.1.6 {
  execsql {DELETE FROM table1 WHERE f1=2}
} {1}
do_test delete-3.1.7 {
  execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 4 16}



# Semantic errors in the WHERE clause
#
do_test delete-4.1 {
  execsql {CREATE TABLE table2(f1 int, f2 int)}
  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
  lappend v $msg
} {1 {no such column: f3}}

do_test delete-4.2 {
  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
  lappend v $msg
} {1 {no such function: xyzzy}}


# Lots of deletes
#
do_test delete-5.1.1 {
  execsql {DELETE FROM table1}
} {2}
do_test delete-5.1.2 {







>
>













>







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
82
83
84
85
} {1 2 2 4 4 16}
do_test delete-3.1.6 {
  execsql {DELETE FROM table1 WHERE f1=2}
} {1}
do_test delete-3.1.7 {
  execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 4 16}
integrity_check delete-3.2


# Semantic errors in the WHERE clause
#
do_test delete-4.1 {
  execsql {CREATE TABLE table2(f1 int, f2 int)}
  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
  lappend v $msg
} {1 {no such column: f3}}

do_test delete-4.2 {
  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
  lappend v $msg
} {1 {no such function: xyzzy}}
integrity_check delete-4.3

# Lots of deletes
#
do_test delete-5.1.1 {
  execsql {DELETE FROM table1}
} {2}
do_test delete-5.1.2 {
138
139
140
141
142
143
144


145
146
147
148
149
150
151
  }
  execsql {SELECT f1 FROM table1 ORDER BY f1}
} {42 44 47 48 50}
do_test delete-5.7 {
  execsql "DELETE FROM table1 WHERE f1!=48"
  execsql {SELECT f1 FROM table1 ORDER BY f1}
} {48}



# Delete large quantities of data.  We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
  set fd [open data1.txt w]
  for {set i 1} {$i<=3000} {incr i} {







>
>







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
  }
  execsql {SELECT f1 FROM table1 ORDER BY f1}
} {42 44 47 48 50}
do_test delete-5.7 {
  execsql "DELETE FROM table1 WHERE f1!=48"
  execsql {SELECT f1 FROM table1 ORDER BY f1}
} {48}
integrity_check delete-5.8


# Delete large quantities of data.  We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
  set fd [open data1.txt w]
  for {set i 1} {$i<=3000} {incr i} {
188
189
190
191
192
193
194

195
196
197
198
199
200
201
  execsql {DELETE FROM table2}
  execsql {SELECT f1 FROM table2}
} {}
do_test delete-6.10 {
  execsql {INSERT INTO table2 VALUES(2,3)}
  execsql {SELECT f1 FROM table2}
} {2}


do_test delete-7.1 {
  execsql {
    CREATE TABLE t3(a);
    INSERT INTO t3 VALUES(1);
    INSERT INTO t3 SELECT a+1 FROM t3;
    INSERT INTO t3 SELECT a+2 FROM t3;







>







193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
  execsql {DELETE FROM table2}
  execsql {SELECT f1 FROM table2}
} {}
do_test delete-6.10 {
  execsql {INSERT INTO table2 VALUES(2,3)}
  execsql {SELECT f1 FROM table2}
} {2}
integrity_check delete-6.11

do_test delete-7.1 {
  execsql {
    CREATE TABLE t3(a);
    INSERT INTO t3 VALUES(1);
    INSERT INTO t3 SELECT a+1 FROM t3;
    INSERT INTO t3 SELECT a+2 FROM t3;
236
237
238
239
240
241
242

243
244
245
246
247
248
249
    INSERT INTO t3 SELECT a+2 FROM t3;
    CREATE TABLE t4 AS SELECT * FROM t3;
    PRAGMA count_changes=ON;
    DELETE FROM t3;
    DELETE FROM t4;
  }
} {4 4}


# Make sure error messages are consistent when attempting to delete
# from a read-only database.  Ticket #304.
#
do_test delete-8.0 {
  execsql {
    PRAGMA count_changes=OFF;







>







242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
    INSERT INTO t3 SELECT a+2 FROM t3;
    CREATE TABLE t4 AS SELECT * FROM t3;
    PRAGMA count_changes=ON;
    DELETE FROM t3;
    DELETE FROM t4;
  }
} {4 4}
integrity_check delete-7.7

# Make sure error messages are consistent when attempting to delete
# from a read-only database.  Ticket #304.
#
do_test delete-8.0 {
  execsql {
    PRAGMA count_changes=OFF;
275
276
277
278
279
280
281
282
283
284
  catchsql {
    DELETE FROM t3 WHERE a<100;
  }
} {0 {}}
do_test delete-8.6 {
  execsql {SELECT * FROM t3}
} {123}


finish_test







|


282
283
284
285
286
287
288
289
290
291
  catchsql {
    DELETE FROM t3 WHERE a<100;
  }
} {0 {}}
do_test delete-8.6 {
  execsql {SELECT * FROM t3}
} {123}
integrity_check delete-8.7

finish_test
Changes to test/index.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.22 2003/04/16 02:17:36 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.23 2003/06/15 23:42:25 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
150
151
152
153
154
155
156

157
158
159
160
161
162
163
do_test index-4.12 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.13 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} {}


# Do not allow indices to be added to sqlite_master
#
do_test index-5.1 {
  set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
  lappend v $msg
} {1 {table sqlite_master may not be indexed}}







>







150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
do_test index-4.12 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test index-4.13 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} {}
integrity_check index-4.14

# Do not allow indices to be added to sqlite_master
#
do_test index-5.1 {
  set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
  lappend v $msg
} {1 {table sqlite_master may not be indexed}}
195
196
197
198
199
200
201


202
203
204
205
206
207
208
    CREATE INDEX index1 ON test1(a);
    CREATE INDEX index2 ON test1(b);
    CREATE INDEX index3 ON test1(a,b);
    DROP TABLE test1;
    SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
  }
} {}



# Create a primary key
#
do_test index-7.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
  for {set i 1} {$i<20} {incr i} {
    execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"







>
>







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
    CREATE INDEX index1 ON test1(a);
    CREATE INDEX index2 ON test1(b);
    CREATE INDEX index3 ON test1(a,b);
    DROP TABLE test1;
    SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
  }
} {}
integrity_check index-6.5


# Create a primary key
#
do_test index-7.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
  for {set i 1} {$i<20} {incr i} {
    execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
218
219
220
221
222
223
224

225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

245
246
247
248
249
250
251
    WHERE type='index' AND tbl_name='test1'
  }
} {{(test1 autoindex 1)}}
do_test index-7.4 {
  execsql {DROP table test1}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
} {}


# Make sure we cannot drop a non-existant index.
#
do_test index-8.1 {
  set v [catch {execsql {DROP INDEX index1}} msg]
  lappend v $msg
} {1 {no such index: index1}}

# Make sure we don't actually create an index when the EXPLAIN keyword
# is used.
#
do_test index-9.1 {
  execsql {CREATE TABLE tab1(a int)}
  execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
} {tab1}
do_test index-9.2 {
  execsql {CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
} {idx1 tab1}


# Allow more than one entry with the same key.
#
do_test index-10.0 {
  execsql {
    CREATE TABLE t1(a int, b int);
    CREATE INDEX i1 ON t1(a);







>




















>







221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
    WHERE type='index' AND tbl_name='test1'
  }
} {{(test1 autoindex 1)}}
do_test index-7.4 {
  execsql {DROP table test1}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
} {}
integrity_check index-7.5

# Make sure we cannot drop a non-existant index.
#
do_test index-8.1 {
  set v [catch {execsql {DROP INDEX index1}} msg]
  lappend v $msg
} {1 {no such index: index1}}

# Make sure we don't actually create an index when the EXPLAIN keyword
# is used.
#
do_test index-9.1 {
  execsql {CREATE TABLE tab1(a int)}
  execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
} {tab1}
do_test index-9.2 {
  execsql {CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
} {idx1 tab1}
integrity_check index-9.3

# Allow more than one entry with the same key.
#
do_test index-10.0 {
  execsql {
    CREATE TABLE t1(a int, b int);
    CREATE INDEX i1 ON t1(a);
308
309
310
311
312
313
314

315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332


333
334
335
336
337
338
339
  }
} {}
do_test index-10.8 {
  execsql {
    SELECT b FROM t1 ORDER BY b;
  }
} {0}


# Automatically create an index when we specify a primary key.
#
do_test index-11.1 {
  execsql {
    CREATE TABLE t3(
      a text,
      b int,
      c float,
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  set sqlite_search_count 0
  concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
} {0.10 3}



# Numeric strings should compare as if they were numbers.  So even if the
# strings are not character-by-character the same, if they represent the
# same number they should compare equal to one another.  Verify that this
# is true in indices.
#
do_test index-12.1 {







>


















>
>







313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
  }
} {}
do_test index-10.8 {
  execsql {
    SELECT b FROM t1 ORDER BY b;
  }
} {0}
integrity_check index-10.9

# Automatically create an index when we specify a primary key.
#
do_test index-11.1 {
  execsql {
    CREATE TABLE t3(
      a text,
      b int,
      c float,
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  set sqlite_search_count 0
  concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
} {0.10 3}
integrity_check index-11.2


# Numeric strings should compare as if they were numbers.  So even if the
# strings are not character-by-character the same, if they represent the
# same number they should compare equal to one another.  Verify that this
# is true in indices.
#
do_test index-12.1 {
376
377
378
379
380
381
382

383
384
385
386
387
388
389
  }
} {0.0 0.00 -1.0 0 00000}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0.0 0.00 abc +1.0 0 00000}


# Make sure we cannot drop an automatically created index.
#
do_test index-13.1 {
  execsql {
   CREATE TABLE t5(
      a int UNIQUE,







>







384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
  }
} {0.0 0.00 -1.0 0 00000}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0.0 0.00 abc +1.0 0 00000}
integrity_check index-12.8

# Make sure we cannot drop an automatically created index.
#
do_test index-13.1 {
  execsql {
   CREATE TABLE t5(
      a int UNIQUE,
410
411
412
413
414
415
416

417
418
419
420
421
422
423
}
do_test index-13.4 {
  execsql {
    INSERT INTO t5 VALUES('a','b','c');
    SELECT * FROM t5;
  }
} {1 2 3 a b c}


# Check the sort order of data in an index.
#
do_test index-14.1 {
  execsql {
    CREATE TABLE t6(a,b,c);
    CREATE INDEX t6i1 ON t6(a,b);







>







419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
}
do_test index-13.4 {
  execsql {
    INSERT INTO t5 VALUES('a','b','c');
    SELECT * FROM t5;
  }
} {1 2 3 a b c}
integrity_check index-13.5

# Check the sort order of data in an index.
#
do_test index-14.1 {
  execsql {
    CREATE TABLE t6(a,b,c);
    CREATE INDEX t6i1 ON t6(a,b);
475
476
477
478
479
480
481

482
483
484
485
486
487
488
  }
} {3 5 2 1}
do_test index-14.11 {
  execsql {
    SELECT c FROM t6 WHERE a<'';
  }
} {3 5}


do_test index-15.1 {
  execsql {
    DELETE FROM t1;
    SELECT * FROM t1;
  }
} {}







>







485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
  }
} {3 5 2 1}
do_test index-14.11 {
  execsql {
    SELECT c FROM t6 WHERE a<'';
  }
} {3 5}
integrity_check index-14.12

do_test index-15.1 {
  execsql {
    DELETE FROM t1;
    SELECT * FROM t1;
  }
} {}
498
499
500
501
502
503
504

505
506
    INSERT INTO t1 VALUES('+123.10000E+0003',8);
    INSERT INTO t1 VALUES('+',9);
    INSERT INTO t1 VALUES('+12347.E+02',10);
    INSERT INTO t1 VALUES('+12347E+02',11);
    SELECT b FROM t1 ORDER BY a;
  }
} {8 5 2 1 3 6 11 9 10 4 7}


finish_test







>


509
510
511
512
513
514
515
516
517
518
    INSERT INTO t1 VALUES('+123.10000E+0003',8);
    INSERT INTO t1 VALUES('+',9);
    INSERT INTO t1 VALUES('+12347.E+02',10);
    INSERT INTO t1 VALUES('+12347E+02',11);
    SELECT b FROM t1 ORDER BY a;
  }
} {8 5 2 1 3 6 11 9 10 4 7}
integrity_check index-15.1

finish_test
Changes to test/insert.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.14 2003/06/04 16:24:40 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.15 2003/06/15 23:42:25 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
276
277
278
279
280
281
282
283


284
  }
} {2 4}
do_test insert-6.4 {
  execsql {
    SELECT * FROM t1 WHERE b=3;
  }
} {}



finish_test








>
>

276
277
278
279
280
281
282
283
284
285
286
  }
} {2 4}
do_test insert-6.4 {
  execsql {
    SELECT * FROM t1 WHERE b=3;
  }
} {}

integrity_check insert-99.0

finish_test
Changes to test/intpkey.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.13 2003/03/07 19:50:08 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.14 2003/06/15 23:42:25 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
477
478
479
480
481
482
483
484


485
  }
} {hello}
do_test intpkey-11.1 {
  execsql {
    SELECT b FROM t1 WHERE a=2.0+3.5;
  }
} {}



finish_test








>
>

477
478
479
480
481
482
483
484
485
486
487
  }
} {hello}
do_test intpkey-11.1 {
  execsql {
    SELECT b FROM t1 WHERE a=2.0+3.5;
  }
} {}

integrity_check intpkey-12.1

finish_test
Changes to test/memdb.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is in-memory database backend.
#
# $Id: memdb.test,v 1.4 2003/05/16 02:30:27 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is in-memory database backend.
#
# $Id: memdb.test,v 1.5 2003/06/15 23:42:25 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
  set ::pager_old_format 0
}

do_test memdb-2.1 {
  execsql {
    PRAGMA integrity_check
  }
} {ok ok}

do_test memdb-3.1 {
  execsql {
    CREATE TABLE t4(a,b,c,d);
    BEGIN;
    INSERT INTO t4 VALUES(1,2,3,4);
    SELECT * FROM t4;







|







112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
  set ::pager_old_format 0
}

do_test memdb-2.1 {
  execsql {
    PRAGMA integrity_check
  }
} {ok}

do_test memdb-3.1 {
  execsql {
    CREATE TABLE t4(a,b,c,d);
    BEGIN;
    INSERT INTO t4 VALUES(1,2,3,4);
    SELECT * FROM t4;
Changes to test/pragma.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.4 2003/02/15 23:09:17 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.5 2003/06/15 23:42:25 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
223
224
225
226
227
228
229
230


231









232















233
  }
} {WHATEVER BLOB}
do_test pragma-2.10 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
  }
} {WHATEVER BLOB}
    




























finish_test







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

223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
  }
} {WHATEVER BLOB}
do_test pragma-2.10 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
  }
} {WHATEVER BLOB}

# Construct a corrupted index and make sure the integrity_check
# pragma finds it.
#
do_test pragma-3.1 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c);
    CREATE INDEX i2 ON t2(a);
    INSERT INTO t2 VALUES(11,2,3);
    INSERT INTO t2 VALUES(22,3,4);
    COMMIT;
    SELECT rowid, * from t2;
  }
} {1 11 2 3 2 22 3 4}
do_test pragma-3.2 {
  set rootpage [execsql {SELECT rootpage FROM sqlite_master WHERE name='i2'}]
  set db [btree_open test.db]
  btree_begin_transaction $db
  set c [btree_cursor $db $rootpage 1]
  btree_first $c
  btree_delete $c
  btree_commit $db
  btree_close $db
  execsql {PRAGMA integrity_check}
} {{rowid 1 missing from index i2
wrong # of entries in index i2
}}

finish_test
Changes to test/tester.tcl.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.25 2003/03/01 19:45:35 drh Exp $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.26 2003/06/15 23:42:25 drh Exp $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
243
244
245
246
247
248
249
250
251
}

# Do an integrity check of the entire database
#
proc integrity_check {name} {
  do_test $name {
    execsql {PRAGMA integrity_check}
  } {ok ok}
}







|

243
244
245
246
247
248
249
250
251
}

# Do an integrity check of the entire database
#
proc integrity_check {name} {
  do_test $name {
    execsql {PRAGMA integrity_check}
  } {ok}
}
Changes to test/trigger1.test.
303
304
305
306
307
308
309

310
311
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM t2;
  };
} {3 4 7 8}



finish_test







>


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

integrity_check trigger-5.1

finish_test
Changes to test/trigger2.test.
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
  } [list 1 0 0 0 0 5 6 \
          2 0 0 5 6 5 6 ]

  do_test trigger2-1.$ii.4 {
    execsql {
      PRAGMA integrity_check;
    }
  } {ok ok}
}
catchsql {
  DROP TABLE rlog;
  DROP TABLE clog;
  DROP TABLE tbl;
  DROP TABLE other_tbl;
}







|







182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
  } [list 1 0 0 0 0 5 6 \
          2 0 0 5 6 5 6 ]

  do_test trigger2-1.$ii.4 {
    execsql {
      PRAGMA integrity_check;
    }
  } {ok}
}
catchsql {
  DROP TABLE rlog;
  DROP TABLE clog;
  DROP TABLE tbl;
  DROP TABLE other_tbl;
}
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
    do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"

    do_test trigger2-2.$ii-integrity {
      execsql {
        PRAGMA integrity_check;
      }
    } {ok ok}

  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}







|







304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
    do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"

    do_test trigger2-2.$ii-integrity {
      execsql {
        PRAGMA integrity_check;
      }
    } {ok}

  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
  DROP TABLE tbl;
  DROP TABLE log;
}
do_test trigger2-3.3 {
  execsql {
    PRAGMA integrity_check;
  }
} {ok ok}

# Simple cascaded trigger
execsql {
  CREATE TABLE tblA(a, b);
  CREATE TABLE tblB(a, b);
  CREATE TABLE tblC(a, b);








|







380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
  DROP TABLE tbl;
  DROP TABLE log;
}
do_test trigger2-3.3 {
  execsql {
    PRAGMA integrity_check;
  }
} {ok}

# Simple cascaded trigger
execsql {
  CREATE TABLE tblA(a, b);
  CREATE TABLE tblB(a, b);
  CREATE TABLE tblC(a, b);

710
711
712
713
714
715
716
717
718
719
    UPDATE v1 SET x=x+100, y=y+200, z=z+300;
    SELECT * FROM v1log;
  }
} {3 103 5 205 4 304 9 109 11 211 10 310}

do_test trigger2-9.9 {
  execsql {PRAGMA integrity_check}
} {ok ok}

finish_test







|


710
711
712
713
714
715
716
717
718
719
    UPDATE v1 SET x=x+100, y=y+200, z=z+300;
    SELECT * FROM v1log;
  }
} {3 103 5 205 4 304 9 109 11 211 10 310}

do_test trigger2-9.9 {
  execsql {PRAGMA integrity_check}
} {ok}

finish_test
Changes to test/trigger3.test.
152
153
154
155
156
157
158


159
160
161
162
163
164
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
	INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}



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

finish_test







>
>






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

integrity_check trigger3-8.1

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

finish_test
Changes to test/trigger4.test.
117
118
119
120
121
122
123


124
125
  db close
  sqlite db test.db
  execsql {
    update test set b=99 where id=7;
    select * from test2;
  }
} {7 99}



finish_test







>
>


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

integrity_check trigger4-4.1

finish_test
Changes to test/unique.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
# $Id: unique.test,v 1.5 2003/01/29 18:46:54 drh Exp $

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

# Try to create a table with two primary keys.
# (This is allowed in SQLite even that it is not valid SQL)
#







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
# $Id: unique.test,v 1.6 2003/06/15 23:42:25 drh Exp $

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

# Try to create a table with two primary keys.
# (This is allowed in SQLite even that it is not valid SQL)
#
69
70
71
72
73
74
75

76
77
78
79
80
81
82
  }
} {0 {}}
do_test unique-1.8 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3 3 4 5}


do_test unique-2.0 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t2(a int, b int);
    INSERT INTO t2(a,b) VALUES(1,2);
    INSERT INTO t2(a,b) VALUES(3,4);







>







69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
  }
} {0 {}}
do_test unique-1.8 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3 3 4 5}
integrity_check unique-1.9

do_test unique-2.0 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t2(a int, b int);
    INSERT INTO t2(a,b) VALUES(1,2);
    INSERT INTO t2(a,b) VALUES(3,4);
125
126
127
128
129
130
131

132
133
134
135
136
137
138
  }
} {1 {indexed columns are not unique}}
do_test unique-2.9 {
  catchsql {
    CREATE INDEX i2 ON t2(a);
  }
} {0 {}}


# Test the UNIQUE keyword as used on two or more fields.
#
do_test unique-3.1 {
  catchsql {
    CREATE TABLE t3(
       a int,







>







126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
  }
} {1 {indexed columns are not unique}}
do_test unique-2.9 {
  catchsql {
    CREATE INDEX i2 ON t2(a);
  }
} {0 {}}
integrity_check unique-2.10

# Test the UNIQUE keyword as used on two or more fields.
#
do_test unique-3.1 {
  catchsql {
    CREATE TABLE t3(
       a int,
157
158
159
160
161
162
163

164
165
166
167
168
169
170
} {0 {1 2 3 4 1 2 3 5}}
do_test unique-3.4 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {1 {uniqueness constraint failed}}


# Make sure NULLs are distinct as far as the UNIQUE tests are
# concerned.
#
do_test unique-4.1 {
  execsql {
    CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));







>







159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
} {0 {1 2 3 4 1 2 3 5}}
do_test unique-3.4 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {1 {uniqueness constraint failed}}
integrity_check unique-3.5

# Make sure NULLs are distinct as far as the UNIQUE tests are
# concerned.
#
do_test unique-4.1 {
  execsql {
    CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
189
190
191
192
193
194
195
196
197
198
  }
} {0 {}}
do_test unique-4.5 {
  execsql {
    SELECT * FROM t4
  }
} {1 2 3 {} 2 {} {} 3 4 2 2 {}}


finish_test







|


192
193
194
195
196
197
198
199
200
201
  }
} {0 {}}
do_test unique-4.5 {
  execsql {
    SELECT * FROM t4
  }
} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
integrity_check unique-4.6

finish_test
Changes to test/update.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the UPDATE statement.
#
# $Id: update.test,v 1.12 2003/02/15 23:09:17 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the UPDATE statement.
#
# $Id: update.test,v 1.13 2003/06/15 23:42:25 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
491
492
493
494
495
496
497
498


499
} {2 14 3 7}
do_test update-11.2 {
  execsql {
    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
    SELECT a,e FROM t1;
  }
} {1 15 2 8}



finish_test








>
>

491
492
493
494
495
496
497
498
499
500
501
} {2 14 3 7}
do_test update-11.2 {
  execsql {
    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
    SELECT a,e FROM t1;
  }
} {1 15 2 8}

integrity_check update-12.1

finish_test
Changes to test/where.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.16 2003/04/19 17:27:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
733
734
735
736
737
738
739

740
741
    return $v
  }
  execsql {
    SELECT count(*) FROM t1 WHERE tclvar('v1');
  }
} {50}



finish_test







>


733
734
735
736
737
738
739
740
741
742
    return $v
  }
  execsql {
    SELECT count(*) FROM t1 WHERE tclvar('v1');
  }
} {50}

integrity_check {where-99.0}

finish_test