/ Check-in [52eba4de]
Login

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

Overview
Comment:Enhance the INTEGRITY_CHECK pragma to test the auxiliary database file in addition to the main database file. (CVS 642)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:52eba4de301121f254174b74042015bf6069ef11
User & Date: drh 2002-06-25 13:16:03
Context
2002-06-25
14:43
Bug fix: When the database file grows in size during a transaction, make sure the last page of the file gets written to disk even if that page is on the free list and contains no data. Otherwise the disk file will be too small which can lead to database corruption in subsequent transactions. (CVS 643) check-in: 36fc0add user: drh tags: trunk
13:16
Enhance the INTEGRITY_CHECK pragma to test the auxiliary database file in addition to the main database file. (CVS 642) check-in: 52eba4de user: drh tags: trunk
01:09
Version 2.5.2 (CVS 641) check-in: 756310ca user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
....
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138








2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.98 2002/06/25 01:09:11 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
#endif

  if( sqliteStrICmp(zLeft, "integrity_check")==0 ){
    static VdbeOp checkDb[] = {
      { OP_SetInsert,   0, 0,        "2"},
      { OP_Open,        0, 2,        0},
      { OP_Rewind,      0, 6,        0},
      { OP_Column,      0, 3,        0},
      { OP_SetInsert,   0, 0,        0},
      { OP_Next,        0, 3,        0},
      { OP_IntegrityCk, 0, 0,        0},
      { OP_ColumnCount, 1, 0,        0},
      { OP_ColumnName,  0, 0,        "integrity_check"},
      { OP_Callback,    1, 0,        0},








    };
    Vdbe *v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
  }else

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







|







 







|


|



>
>
>
>
>
>
>
>










21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
....
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.99 2002/06/25 13:16:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
#endif

  if( sqliteStrICmp(zLeft, "integrity_check")==0 ){
    static VdbeOp checkDb[] = {
      { OP_SetInsert,   0, 0,        "2"},
      { OP_Open,        0, 2,        0},
      { OP_Rewind,      0, 6,        0},
      { OP_Column,      0, 3,        0},    /* 3 */
      { OP_SetInsert,   0, 0,        0},
      { OP_Next,        0, 3,        0},
      { OP_IntegrityCk, 0, 0,        0},    /* 6 */
      { OP_ColumnCount, 1, 0,        0},
      { OP_ColumnName,  0, 0,        "integrity_check"},
      { OP_Callback,    1, 0,        0},
      { OP_SetInsert,   1, 0,        "2"},
      { OP_OpenAux,     1, 2,        0},
      { OP_Rewind,      1, 16,       0},
      { OP_Column,      1, 3,        0},    /* 13 */
      { OP_SetInsert,   1, 0,        0},
      { OP_Next,        1, 13,       0},
      { OP_IntegrityCk, 1, 1,        0},    /* 16 */
      { OP_Callback,    1, 0,        0},
    };
    Vdbe *v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
  }else

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

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
4015
4016
4017
4018
4019
4020
4021
4022
4023
4024
4025
4026
4027
4028
4029



4030
4031
4032
4033
4034
4035
4036
....
4045
4046
4047
4048
4049
4050
4051
4052
4053

4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.159 2002/06/25 01:09:12 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
    aStack[i].flags = STK_Int;
    *(u32*)pOp->p3 = pgno;
    pOp->p3 = 0;
  }
  break;
}

/* Opcode: IntegrityCk P1 * *
**
** 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 this database.



**
** This opcode is used for testing purposes only.
*/
case OP_IntegrityCk: {
  int nRoot;
  int *aRoot;
  int tos = ++p->tos;
................................................................................
  pSet = &p->aSet[iSet];
  nRoot = sqliteHashCount(&pSet->hash);
  aRoot = sqliteMalloc( sizeof(int)*(nRoot+1) );
  for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
    aRoot[j] = atoi((char*)sqliteHashKey(i));
  }
  aRoot[j] = 0;
  z = sqliteBtreeIntegrityCheck(pBt, aRoot, nRoot);
  if( z==0 || z[0]==0 ){

    zStack[tos] = "ok";
    aStack[tos].n = 3;
    aStack[tos].flags = STK_Str | STK_Static;
    if( z ) sqliteFree(z);
  }else{
    zStack[tos] = z;
    aStack[tos].n = strlen(z) + 1;
    aStack[tos].flags = STK_Str | STK_Dyn;
  }
  sqliteFree(aRoot);
  break;







|







 







|






|
>
>
>







 







|

>



<







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
4015
4016
4017
4018
4019
4020
4021
4022
4023
4024
4025
4026
4027
4028
4029
4030
4031
4032
4033
4034
4035
4036
4037
4038
4039
....
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060

4061
4062
4063
4064
4065
4066
4067
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.160 2002/06/25 13:16:04 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
    aStack[i].flags = STK_Int;
    *(u32*)pOp->p3 = pgno;
    pOp->p3 = 0;
  }
  break;
}

/* 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: {
  int nRoot;
  int *aRoot;
  int tos = ++p->tos;
................................................................................
  pSet = &p->aSet[iSet];
  nRoot = sqliteHashCount(&pSet->hash);
  aRoot = sqliteMalloc( sizeof(int)*(nRoot+1) );
  for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
    aRoot[j] = atoi((char*)sqliteHashKey(i));
  }
  aRoot[j] = 0;
  z = sqliteBtreeIntegrityCheck(pOp->p2 ? db->pBeTemp : 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{
    zStack[tos] = z;
    aStack[tos].n = strlen(z) + 1;
    aStack[tos].flags = STK_Str | STK_Dyn;
  }
  sqliteFree(aRoot);
  break;

Changes to test/conflict.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
487
488
489
490
491
492
493
494
495
496
497
498
499
500
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.11 2002/04/12 10:09:00 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
    INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
  }
} {3}
do_test conflict-8.6.1 {
  db changes
} {3}

do_test insert-99.1 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}

finish_test







|







 







<
|
<
<
<


9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
487
488
489
490
491
492
493

494



495
496
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.12 2002/06/25 13:16:04 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
    INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
  }
} {3}
do_test conflict-8.6.1 {
  db changes
} {3}


integrity_check conflict-99.1




finish_test

Changes to test/copy.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the COPY statement.
#
# $Id: copy.test,v 1.10 2002/02/19 13:39:23 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
................................................................................
} {2}
do_test copy-5.7 {
  execsql {
    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {0}

do_test copy-6.0 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}
 

# Cleanup 
#
file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt data6.txt

finish_test







|







 







<
|
<
<
<
<






7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
222
223
224
225
226
227
228

229




230
231
232
233
234
235
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the COPY statement.
#
# $Id: copy.test,v 1.11 2002/06/25 13:16:04 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
................................................................................
} {2}
do_test copy-5.7 {
  execsql {
    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {0}


integrity_check copy-6.0





# Cleanup 
#
file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt data6.txt

finish_test

Changes to test/insert.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
#    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.10 2002/04/12 13:11:53 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}
do_test insert-3.5 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}

# Test of expressions in the VALUES clause
#
do_test insert-4.1 {
  execsql {
    CREATE TABLE t3(a,b,c);
    INSERT INTO t3 VALUES(1+2+3,4,5);







|







 







|
<
<
<
<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
152
153
154
155
156
157
158
159




160
161
162
163
164
165
166
#    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.11 2002/06/25 13:16:04 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}
integrity_check insert-3.5





# Test of expressions in the VALUES clause
#
do_test insert-4.1 {
  execsql {
    CREATE TABLE t3(a,b,c);
    INSERT INTO t3 VALUES(1+2+3,4,5);

Changes to test/insert2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
189
190
191
192
193
194
195

196
197
#    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 that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.9 2002/04/12 10:09:00 drh Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
................................................................................
  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

do_test insert2-3.0 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}

# File table t4 with lots of data
#
do_test insert2-3.1 {
  execsql {
    SELECT * from t4;
  }
................................................................................
    SELECT count(*) FROM t4;
    ROLLBACK;
  }
} {1}
do_test insert2-3.8 {
  db changes
} {159}


finish_test







|







 







|
<
<
<
<







 







>


8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
111
112
113
114
115
116
117
118




119
120
121
122
123
124
125
...
185
186
187
188
189
190
191
192
193
194
#    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 that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.10 2002/06/25 13:16:04 drh Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
................................................................................
  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

integrity_check insert2-3.0





# File table t4 with lots of data
#
do_test insert2-3.1 {
  execsql {
    SELECT * from t4;
  }
................................................................................
    SELECT count(*) FROM t4;
    ROLLBACK;
  }
} {1}
do_test insert2-3.8 {
  db changes
} {159}
integrity_check insert2-3.9

finish_test

Changes to test/lock.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
141
142
143
144
145
146
147

148
149
150
151
152
153
154
...
227
228
229
230
231
232
233

234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
#    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 database locks.
#
# $Id: lock.test,v 1.14 2002/05/10 13:14:08 drh Exp $


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

# Create an alternative connection to the database
#
................................................................................
    db eval {SELECT * FROM t1} qv {
      set r [catch {db2 eval {SELECT a FROM t1}} msg]
      lappend r $msg
    }
    set r
  } {0 2}
}


# If one thread has a transaction another thread cannot start
# a transaction.
#
do_test lock-2.1 {
  execsql {BEGIN TRANSACTION}
  set r [catch {execsql {BEGIN TRANSACTION} db2} msg]
................................................................................
  set ::callback_value {}
  db2 busy callback
  set r [catch {execsql {BEGIN TRANSACTION} db2} msg]
  execsql {ROLLBACK} db2
  lappend r $msg
  lappend r $::callback_value
} {0 {} {1 2 3}}


# Try to start two transactions in a row
#
do_test lock-3.1 {
  execsql {BEGIN TRANSACTION}
  set r [catch {execsql {BEGIN TRANSACTION}} msg]
  execsql {ROLLBACK}
  lappend r $msg
} {0 {}}


do_test lock-999.1 {
  rename db2 {}
} {}

finish_test







|







 







>







 







>









|






7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
#    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 database locks.
#
# $Id: lock.test,v 1.15 2002/06/25 13:16:04 drh Exp $


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

# Create an alternative connection to the database
#
................................................................................
    db eval {SELECT * FROM t1} qv {
      set r [catch {db2 eval {SELECT a FROM t1}} msg]
      lappend r $msg
    }
    set r
  } {0 2}
}
integrity_check lock-1.23

# If one thread has a transaction another thread cannot start
# a transaction.
#
do_test lock-2.1 {
  execsql {BEGIN TRANSACTION}
  set r [catch {execsql {BEGIN TRANSACTION} db2} msg]
................................................................................
  set ::callback_value {}
  db2 busy callback
  set r [catch {execsql {BEGIN TRANSACTION} db2} msg]
  execsql {ROLLBACK} db2
  lappend r $msg
  lappend r $::callback_value
} {0 {} {1 2 3}}
integrity_check lock-2.8

# Try to start two transactions in a row
#
do_test lock-3.1 {
  execsql {BEGIN TRANSACTION}
  set r [catch {execsql {BEGIN TRANSACTION}} msg]
  execsql {ROLLBACK}
  lappend r $msg
} {0 {}}
integrity_check lock-3.2

do_test lock-999.1 {
  rename db2 {}
} {}

finish_test

Changes to test/tester.tcl.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
213
214
215
216
217
218
219








#    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.22 2002/03/06 22:01:37 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 *****"
................................................................................
# Delete a file or directory
#
proc forcedelete {filename} {
  if {[catch {file delete -force $filename}]} {
    exec rm -rf $filename
  }
}















|







 







>
>
>
>
>
>
>
>
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
#    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.23 2002/06/25 13:16:04 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 *****"
................................................................................
# Delete a file or directory
#
proc forcedelete {filename} {
  if {[catch {file delete -force $filename}]} {
    exec rm -rf $filename
  }
}

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

Changes to test/trans.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
41
42
43
44
45
46
47

48
49
50
51
52
53
54
..
76
77
78
79
80
81
82

83
84
85
86
87
88
89
...
160
161
162
163
164
165
166

167
168
169
170
171
172
173
...
230
231
232
233
234
235
236

237
238
239
240
241
242
243

244
245
246
247
248
249
250
...
401
402
403
404
405
406
407


408
409
410
411
412
413
414
...
659
660
661
662
663
664
665

666
667
668
669
670
671
672
...
766
767
768
769
770
771
772

773
774
775
776
777
778
779
...
790
791
792
793
794
795
796

797
798
799
#    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 database locks.
#
# $Id: trans.test,v 1.11 2002/03/11 02:06:14 drh Exp $


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


# Create several tables to work with.
................................................................................
do_test trans-1.9 {
  sqlite altdb test.db
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test trans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}


# Basic transactions
#
do_test trans-2.1 {
  set v [catch {execsql {BEGIN}} msg]
  lappend v $msg
} {0 {}}
................................................................................
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
    SELECT a FROM two ORDER BY a;
    END;
  }
} {1 2 3 1 5 10}


# Check the locking behavior
#
do_test trans-3.1 {
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
................................................................................
} {0 {1 4 5 10}}
do_test trans-3.14 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } db} msg]
  lappend v $msg
} {0 {1 2 3 4}}


do_test trans-4.1 {
  set v [catch {execsql {
    COMMIT;
  } db} msg]
  lappend v $msg
} {0 {}}
................................................................................
} {0 {1 4 5 10}}
do_test trans-4.11 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 2 3 4}}

do_test trans-4.99 {
  altdb close
  execsql {
    DROP TABLE one;
    DROP TABLE two;
  }
} {}


# Check out the commit/rollback behavior of the database
#
do_test trans-5.1 {
  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
} {}
do_test trans-5.2 {
................................................................................
  }
} {i2x i2y t1 t2}
do_test trans-5.23 {
  execsql {
    SELECT * FROM t2;
  }
} {1 2 3}



# Try to DROP and CREATE tables and indices with the same name
# within a transaction.  Make sure ROLLBACK works.
#
do_test trans-6.1 {
  execsql2 {
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.39 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}


# Test to make sure rollback restores the database back to its original
# state.
#
do_test trans-7.1 {
  execsql {BEGIN}
  for {set i 0} {$i<1000} {incr i} {
................................................................................
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.14 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2


# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read
# the database.  This process should detect the journal file and roll it
# back.  Verify that this happens correctly.
#
set fd [open test.tcl w]
................................................................................
do_test trans-8.1 {
  catch {exec [info nameofexec] test.tcl}
  execsql {SELECT md5sum(x,y,z) FROM t2}
} $checksum
do_test trans-8.2 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2


   
finish_test







|







 







>







 







>







 







>







 







>
|






>







 







>
>







 







>







 







>







 







>



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
..
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
...
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
...
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
...
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
...
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
...
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
...
799
800
801
802
803
804
805
806
807
808
809
#    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 database locks.
#
# $Id: trans.test,v 1.12 2002/06/25 13:16:04 drh Exp $


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


# Create several tables to work with.
................................................................................
do_test trans-1.9 {
  sqlite altdb test.db
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test trans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}
integrity_check trans-1.11

# Basic transactions
#
do_test trans-2.1 {
  set v [catch {execsql {BEGIN}} msg]
  lappend v $msg
} {0 {}}
................................................................................
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
    SELECT a FROM two ORDER BY a;
    END;
  }
} {1 2 3 1 5 10}
integrity_check trans-2.11

# Check the locking behavior
#
do_test trans-3.1 {
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
................................................................................
} {0 {1 4 5 10}}
do_test trans-3.14 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } db} msg]
  lappend v $msg
} {0 {1 2 3 4}}
integrity_check trans-3.15

do_test trans-4.1 {
  set v [catch {execsql {
    COMMIT;
  } db} msg]
  lappend v $msg
} {0 {}}
................................................................................
} {0 {1 4 5 10}}
do_test trans-4.11 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 2 3 4}}
integrity_check trans-4.12
do_test trans-4.98 {
  altdb close
  execsql {
    DROP TABLE one;
    DROP TABLE two;
  }
} {}
integrity_check trans-4.99

# Check out the commit/rollback behavior of the database
#
do_test trans-5.1 {
  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
} {}
do_test trans-5.2 {
................................................................................
  }
} {i2x i2y t1 t2}
do_test trans-5.23 {
  execsql {
    SELECT * FROM t2;
  }
} {1 2 3}
integrity_check trans-5.23


# Try to DROP and CREATE tables and indices with the same name
# within a transaction.  Make sure ROLLBACK works.
#
do_test trans-6.1 {
  execsql2 {
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.39 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}
integrity_check trans-6.40

# Test to make sure rollback restores the database back to its original
# state.
#
do_test trans-7.1 {
  execsql {BEGIN}
  for {set i 0} {$i<1000} {incr i} {
................................................................................
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.14 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-7.15

# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read
# the database.  This process should detect the journal file and roll it
# back.  Verify that this happens correctly.
#
set fd [open test.tcl w]
................................................................................
do_test trans-8.1 {
  catch {exec [info nameofexec] test.tcl}
  execsql {SELECT md5sum(x,y,z) FROM t2}
} $checksum
do_test trans-8.2 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-8.3

   
finish_test