1 # 2001 September 15

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  This

    12 # file is a copy of "trans.test" modified to run under autovacuum mode.

    13 # the point is to stress the autovacuum logic and try to get it to fail.

    14 #

    15 # $Id: avtrans.test,v 1.6 2007/09/12 17:01:45 danielk1977 Exp $

    16 

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 

    22 # Create several tables to work with.

    23 #

    24 do_test avtrans-1.0 {

    25   execsql {

    26     PRAGMA auto_vacuum=ON;

    27     CREATE TABLE one(a int PRIMARY KEY, b text);

    28     INSERT INTO one VALUES(1,'one');

    29     INSERT INTO one VALUES(2,'two');

    30     INSERT INTO one VALUES(3,'three');

    31     SELECT b FROM one ORDER BY a;

    32   }

    33 } {one two three}

    34 do_test avtrans-1.1 {

    35   execsql {

    36     CREATE TABLE two(a int PRIMARY KEY, b text);

    37     INSERT INTO two VALUES(1,'I');

    38     INSERT INTO two VALUES(5,'V');

    39     INSERT INTO two VALUES(10,'X');

    40     SELECT b FROM two ORDER BY a;

    41   }

    42 } {I V X}

    43 do_test avtrans-1.9 {

    44   sqlite3 altdb test.db

    45   execsql {SELECT b FROM one ORDER BY a} altdb

    46 } {one two three}

    47 do_test avtrans-1.10 {

    48   execsql {SELECT b FROM two ORDER BY a} altdb

    49 } {I V X}

    50 integrity_check avtrans-1.11

    51 

    52 # Basic transactions

    53 #

    54 do_test avtrans-2.1 {

    55   set v [catch {execsql {BEGIN}} msg]

    56   lappend v $msg

    57 } {0 {}}

    58 do_test avtrans-2.2 {

    59   set v [catch {execsql {END}} msg]

    60   lappend v $msg

    61 } {0 {}}

    62 do_test avtrans-2.3 {

    63   set v [catch {execsql {BEGIN TRANSACTION}} msg]

    64   lappend v $msg

    65 } {0 {}}

    66 do_test avtrans-2.4 {

    67   set v [catch {execsql {COMMIT TRANSACTION}} msg]

    68   lappend v $msg

    69 } {0 {}}

    70 do_test avtrans-2.5 {

    71   set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]

    72   lappend v $msg

    73 } {0 {}}

    74 do_test avtrans-2.6 {

    75   set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]

    76   lappend v $msg

    77 } {0 {}}

    78 do_test avtrans-2.10 {

    79   execsql {

    80     BEGIN;

    81     SELECT a FROM one ORDER BY a;

    82     SELECT a FROM two ORDER BY a;

    83     END;

    84   }

    85 } {1 2 3 1 5 10}

    86 integrity_check avtrans-2.11

    87 

    88 # Check the locking behavior

    89 #

    90 sqlite3_soft_heap_limit 0

    91 do_test avtrans-3.1 {

    92   execsql {

    93     BEGIN;

    94     UPDATE one SET a = 0 WHERE 0;

    95     SELECT a FROM one ORDER BY a;

    96   }

    97 } {1 2 3}

    98 do_test avtrans-3.2 {

    99   catchsql {

   100     SELECT a FROM two ORDER BY a;

   101   } altdb

   102 } {0 {1 5 10}}

   103 do_test avtrans-3.3 {

   104   catchsql {

   105     SELECT a FROM one ORDER BY a;

   106   } altdb

   107 } {0 {1 2 3}}

   108 do_test avtrans-3.4 {

   109   catchsql {

   110     INSERT INTO one VALUES(4,'four');

   111   }

   112 } {0 {}}

   113 do_test avtrans-3.5 {

   114   catchsql {

   115     SELECT a FROM two ORDER BY a;

   116   } altdb

   117 } {0 {1 5 10}}

   118 do_test avtrans-3.6 {

   119   catchsql {

   120     SELECT a FROM one ORDER BY a;

   121   } altdb

   122 } {0 {1 2 3}}

   123 do_test avtrans-3.7 {

   124   catchsql {

   125     INSERT INTO two VALUES(4,'IV');

   126   }

   127 } {0 {}}

   128 do_test avtrans-3.8 {

   129   catchsql {

   130     SELECT a FROM two ORDER BY a;

   131   } altdb

   132 } {0 {1 5 10}}

   133 do_test avtrans-3.9 {

   134   catchsql {

   135     SELECT a FROM one ORDER BY a;

   136   } altdb

   137 } {0 {1 2 3}}

   138 do_test avtrans-3.10 {

   139   execsql {END TRANSACTION}

   140 } {}

   141 do_test avtrans-3.11 {

   142   set v [catch {execsql {

   143     SELECT a FROM two ORDER BY a;

   144   } altdb} msg]

   145   lappend v $msg

   146 } {0 {1 4 5 10}}

   147 do_test avtrans-3.12 {

   148   set v [catch {execsql {

   149     SELECT a FROM one ORDER BY a;

   150   } altdb} msg]

   151   lappend v $msg

   152 } {0 {1 2 3 4}}

   153 do_test avtrans-3.13 {

   154   set v [catch {execsql {

   155     SELECT a FROM two ORDER BY a;

   156   } db} msg]

   157   lappend v $msg

   158 } {0 {1 4 5 10}}

   159 do_test avtrans-3.14 {

   160   set v [catch {execsql {

   161     SELECT a FROM one ORDER BY a;

   162   } db} msg]

   163   lappend v $msg

   164 } {0 {1 2 3 4}}

   165 sqlite3_soft_heap_limit $soft_limit

   166 integrity_check avtrans-3.15

   167 

   168 do_test avtrans-4.1 {

   169   set v [catch {execsql {

   170     COMMIT;

   171   } db} msg]

   172   lappend v $msg

   173 } {1 {cannot commit - no transaction is active}}

   174 do_test avtrans-4.2 {

   175   set v [catch {execsql {

   176     ROLLBACK;

   177   } db} msg]

   178   lappend v $msg

   179 } {1 {cannot rollback - no transaction is active}}

   180 do_test avtrans-4.3 {

   181   catchsql {

   182     BEGIN TRANSACTION;

   183     UPDATE two SET a = 0 WHERE 0;

   184     SELECT a FROM two ORDER BY a;

   185   } db

   186 } {0 {1 4 5 10}}

   187 do_test avtrans-4.4 {

   188   catchsql {

   189     SELECT a FROM two ORDER BY a;

   190   } altdb

   191 } {0 {1 4 5 10}}

   192 do_test avtrans-4.5 {

   193   catchsql {

   194     SELECT a FROM one ORDER BY a;

   195   } altdb

   196 } {0 {1 2 3 4}}

   197 do_test avtrans-4.6 {

   198   catchsql {

   199     BEGIN TRANSACTION;

   200     SELECT a FROM one ORDER BY a;

   201   } db

   202 } {1 {cannot start a transaction within a transaction}}

   203 do_test avtrans-4.7 {

   204   catchsql {

   205     SELECT a FROM two ORDER BY a;

   206   } altdb

   207 } {0 {1 4 5 10}}

   208 do_test avtrans-4.8 {

   209   catchsql {

   210     SELECT a FROM one ORDER BY a;

   211   } altdb

   212 } {0 {1 2 3 4}}

   213 do_test avtrans-4.9 {

   214   set v [catch {execsql {

   215     END TRANSACTION;

   216     SELECT a FROM two ORDER BY a;

   217   } db} msg]

   218   lappend v $msg

   219 } {0 {1 4 5 10}}

   220 do_test avtrans-4.10 {

   221   set v [catch {execsql {

   222     SELECT a FROM two ORDER BY a;

   223   } altdb} msg]

   224   lappend v $msg

   225 } {0 {1 4 5 10}}

   226 do_test avtrans-4.11 {

   227   set v [catch {execsql {

   228     SELECT a FROM one ORDER BY a;

   229   } altdb} msg]

   230   lappend v $msg

   231 } {0 {1 2 3 4}}

   232 integrity_check avtrans-4.12

   233 do_test avtrans-4.98 {

   234   altdb close

   235   execsql {

   236     DROP TABLE one;

   237     DROP TABLE two;

   238   }

   239 } {}

   240 integrity_check avtrans-4.99

   241 

   242 # Check out the commit/rollback behavior of the database

   243 #

   244 do_test avtrans-5.1 {

   245   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

   246 } {}

   247 do_test avtrans-5.2 {

   248   execsql {BEGIN TRANSACTION}

   249   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

   250 } {}

   251 do_test avtrans-5.3 {

   252   execsql {CREATE TABLE one(a text, b int)}

   253   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

   254 } {one}

   255 do_test avtrans-5.4 {

   256   execsql {SELECT a,b FROM one ORDER BY b}

   257 } {}

   258 do_test avtrans-5.5 {

   259   execsql {INSERT INTO one(a,b) VALUES('hello', 1)}

   260   execsql {SELECT a,b FROM one ORDER BY b}

   261 } {hello 1}

   262 do_test avtrans-5.6 {

   263   execsql {ROLLBACK}

   264   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

   265 } {}

   266 do_test avtrans-5.7 {

   267   set v [catch {

   268     execsql {SELECT a,b FROM one ORDER BY b}

   269   } msg]

   270   lappend v $msg

   271 } {1 {no such table: one}}

   272 

   273 # Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs

   274 # DROP TABLEs and DROP INDEXs

   275 #

   276 do_test avtrans-5.8 {

   277   execsql {

   278     SELECT name fROM sqlite_master 

   279     WHERE type='table' OR type='index'

   280     ORDER BY name

   281   }

   282 } {}

   283 do_test avtrans-5.9 {

   284   execsql {

   285     BEGIN TRANSACTION;

   286     CREATE TABLE t1(a int, b int, c int);

   287     SELECT name fROM sqlite_master 

   288     WHERE type='table' OR type='index'

   289     ORDER BY name;

   290   }

   291 } {t1}

   292 do_test avtrans-5.10 {

   293   execsql {

   294     CREATE INDEX i1 ON t1(a);

   295     SELECT name fROM sqlite_master 

   296     WHERE type='table' OR type='index'

   297     ORDER BY name;

   298   }

   299 } {i1 t1}

   300 do_test avtrans-5.11 {

   301   execsql {

   302     COMMIT;

   303     SELECT name fROM sqlite_master 

   304     WHERE type='table' OR type='index'

   305     ORDER BY name;

   306   }

   307 } {i1 t1}

   308 do_test avtrans-5.12 {

   309   execsql {

   310     BEGIN TRANSACTION;

   311     CREATE TABLE t2(a int, b int, c int);

   312     CREATE INDEX i2a ON t2(a);

   313     CREATE INDEX i2b ON t2(b);

   314     DROP TABLE t1;

   315     SELECT name fROM sqlite_master 

   316     WHERE type='table' OR type='index'

   317     ORDER BY name;

   318   }

   319 } {i2a i2b t2}

   320 do_test avtrans-5.13 {

   321   execsql {

   322     ROLLBACK;

   323     SELECT name fROM sqlite_master 

   324     WHERE type='table' OR type='index'

   325     ORDER BY name;

   326   }

   327 } {i1 t1}

   328 do_test avtrans-5.14 {

   329   execsql {

   330     BEGIN TRANSACTION;

   331     DROP INDEX i1;

   332     SELECT name fROM sqlite_master 

   333     WHERE type='table' OR type='index'

   334     ORDER BY name;

   335   }

   336 } {t1}

   337 do_test avtrans-5.15 {

   338   execsql {

   339     ROLLBACK;

   340     SELECT name fROM sqlite_master 

   341     WHERE type='table' OR type='index'

   342     ORDER BY name;

   343   }

   344 } {i1 t1}

   345 do_test avtrans-5.16 {

   346   execsql {

   347     BEGIN TRANSACTION;

   348     DROP INDEX i1;

   349     CREATE TABLE t2(x int, y int, z int);

   350     CREATE INDEX i2x ON t2(x);

   351     CREATE INDEX i2y ON t2(y);

   352     INSERT INTO t2 VALUES(1,2,3);

   353     SELECT name fROM sqlite_master 

   354     WHERE type='table' OR type='index'

   355     ORDER BY name;

   356   }

   357 } {i2x i2y t1 t2}

   358 do_test avtrans-5.17 {

   359   execsql {

   360     COMMIT;

   361     SELECT name fROM sqlite_master 

   362     WHERE type='table' OR type='index'

   363     ORDER BY name;

   364   }

   365 } {i2x i2y t1 t2}

   366 do_test avtrans-5.18 {

   367   execsql {

   368     SELECT * FROM t2;

   369   }

   370 } {1 2 3}

   371 do_test avtrans-5.19 {

   372   execsql {

   373     SELECT x FROM t2 WHERE y=2;

   374   }

   375 } {1}

   376 do_test avtrans-5.20 {

   377   execsql {

   378     BEGIN TRANSACTION;

   379     DROP TABLE t1;

   380     DROP TABLE t2;

   381     SELECT name fROM sqlite_master 

   382     WHERE type='table' OR type='index'

   383     ORDER BY name;

   384   }

   385 } {}

   386 do_test avtrans-5.21 {

   387   set r [catch {execsql {

   388     SELECT * FROM t2

   389   }} msg]

   390   lappend r $msg

   391 } {1 {no such table: t2}}

   392 do_test avtrans-5.22 {

   393   execsql {

   394     ROLLBACK;

   395     SELECT name fROM sqlite_master 

   396     WHERE type='table' OR type='index'

   397     ORDER BY name;

   398   }

   399 } {i2x i2y t1 t2}

   400 do_test avtrans-5.23 {

   401   execsql {

   402     SELECT * FROM t2;

   403   }

   404 } {1 2 3}

   405 integrity_check avtrans-5.23

   406 

   407 

   408 # Try to DROP and CREATE tables and indices with the same name

   409 # within a transaction.  Make sure ROLLBACK works.

   410 #

   411 do_test avtrans-6.1 {

   412   execsql2 {

   413     INSERT INTO t1 VALUES(1,2,3);

   414     BEGIN TRANSACTION;

   415     DROP TABLE t1;

   416     CREATE TABLE t1(p,q,r);

   417     ROLLBACK;

   418     SELECT * FROM t1;

   419   }

   420 } {a 1 b 2 c 3}

   421 do_test avtrans-6.2 {

   422   execsql2 {

   423     INSERT INTO t1 VALUES(1,2,3);

   424     BEGIN TRANSACTION;

   425     DROP TABLE t1;

   426     CREATE TABLE t1(p,q,r);

   427     COMMIT;

   428     SELECT * FROM t1;

   429   }

   430 } {}

   431 do_test avtrans-6.3 {

   432   execsql2 {

   433     INSERT INTO t1 VALUES(1,2,3);

   434     SELECT * FROM t1;

   435   }

   436 } {p 1 q 2 r 3}

   437 do_test avtrans-6.4 {

   438   execsql2 {

   439     BEGIN TRANSACTION;

   440     DROP TABLE t1;

   441     CREATE TABLE t1(a,b,c);

   442     INSERT INTO t1 VALUES(4,5,6);

   443     SELECT * FROM t1;

   444     DROP TABLE t1;

   445   }

   446 } {a 4 b 5 c 6}

   447 do_test avtrans-6.5 {

   448   execsql2 {

   449     ROLLBACK;

   450     SELECT * FROM t1;

   451   }

   452 } {p 1 q 2 r 3}

   453 do_test avtrans-6.6 {

   454   execsql2 {

   455     BEGIN TRANSACTION;

   456     DROP TABLE t1;

   457     CREATE TABLE t1(a,b,c);

   458     INSERT INTO t1 VALUES(4,5,6);

   459     SELECT * FROM t1;

   460     DROP TABLE t1;

   461   }

   462 } {a 4 b 5 c 6}

   463 do_test avtrans-6.7 {

   464   catchsql {

   465     COMMIT;

   466     SELECT * FROM t1;

   467   }

   468 } {1 {no such table: t1}}

   469 

   470 # Repeat on a table with an automatically generated index.

   471 #

   472 do_test avtrans-6.10 {

   473   execsql2 {

   474     CREATE TABLE t1(a unique,b,c);

   475     INSERT INTO t1 VALUES(1,2,3);

   476     BEGIN TRANSACTION;

   477     DROP TABLE t1;

   478     CREATE TABLE t1(p unique,q,r);

   479     ROLLBACK;

   480     SELECT * FROM t1;

   481   }

   482 } {a 1 b 2 c 3}

   483 do_test avtrans-6.11 {

   484   execsql2 {

   485     BEGIN TRANSACTION;

   486     DROP TABLE t1;

   487     CREATE TABLE t1(p unique,q,r);

   488     COMMIT;

   489     SELECT * FROM t1;

   490   }

   491 } {}

   492 do_test avtrans-6.12 {

   493   execsql2 {

   494     INSERT INTO t1 VALUES(1,2,3);

   495     SELECT * FROM t1;

   496   }

   497 } {p 1 q 2 r 3}

   498 do_test avtrans-6.13 {

   499   execsql2 {

   500     BEGIN TRANSACTION;

   501     DROP TABLE t1;

   502     CREATE TABLE t1(a unique,b,c);

   503     INSERT INTO t1 VALUES(4,5,6);

   504     SELECT * FROM t1;

   505     DROP TABLE t1;

   506   }

   507 } {a 4 b 5 c 6}

   508 do_test avtrans-6.14 {

   509   execsql2 {

   510     ROLLBACK;

   511     SELECT * FROM t1;

   512   }

   513 } {p 1 q 2 r 3}

   514 do_test avtrans-6.15 {

   515   execsql2 {

   516     BEGIN TRANSACTION;

   517     DROP TABLE t1;

   518     CREATE TABLE t1(a unique,b,c);

   519     INSERT INTO t1 VALUES(4,5,6);

   520     SELECT * FROM t1;

   521     DROP TABLE t1;

   522   }

   523 } {a 4 b 5 c 6}

   524 do_test avtrans-6.16 {

   525   catchsql {

   526     COMMIT;

   527     SELECT * FROM t1;

   528   }

   529 } {1 {no such table: t1}}

   530 

   531 do_test avtrans-6.20 {

   532   execsql {

   533     CREATE TABLE t1(a integer primary key,b,c);

   534     INSERT INTO t1 VALUES(1,-2,-3);

   535     INSERT INTO t1 VALUES(4,-5,-6);

   536     SELECT * FROM t1;

   537   }

   538 } {1 -2 -3 4 -5 -6}

   539 do_test avtrans-6.21 {

   540   execsql {

   541     CREATE INDEX i1 ON t1(b);

   542     SELECT * FROM t1 WHERE b<1;

   543   }

   544 } {4 -5 -6 1 -2 -3}

   545 do_test avtrans-6.22 {

   546   execsql {

   547     BEGIN TRANSACTION;

   548     DROP INDEX i1;

   549     SELECT * FROM t1 WHERE b<1;

   550     ROLLBACK;

   551   }

   552 } {1 -2 -3 4 -5 -6}

   553 do_test avtrans-6.23 {

   554   execsql {

   555     SELECT * FROM t1 WHERE b<1;

   556   }

   557 } {4 -5 -6 1 -2 -3}

   558 do_test avtrans-6.24 {

   559   execsql {

   560     BEGIN TRANSACTION;

   561     DROP TABLE t1;

   562     ROLLBACK;

   563     SELECT * FROM t1 WHERE b<1;

   564   }

   565 } {4 -5 -6 1 -2 -3}

   566 

   567 do_test avtrans-6.25 {

   568   execsql {

   569     BEGIN TRANSACTION;

   570     DROP INDEX i1;

   571     CREATE INDEX i1 ON t1(c);

   572     SELECT * FROM t1 WHERE b<1;

   573   }

   574 } {1 -2 -3 4 -5 -6}

   575 do_test avtrans-6.26 {

   576   execsql {

   577     SELECT * FROM t1 WHERE c<1;

   578   }

   579 } {4 -5 -6 1 -2 -3}

   580 do_test avtrans-6.27 {

   581   execsql {

   582     ROLLBACK;

   583     SELECT * FROM t1 WHERE b<1;

   584   }

   585 } {4 -5 -6 1 -2 -3}

   586 do_test avtrans-6.28 {

   587   execsql {

   588     SELECT * FROM t1 WHERE c<1;

   589   }

   590 } {1 -2 -3 4 -5 -6}

   591 

   592 # The following repeats steps 6.20 through 6.28, but puts a "unique"

   593 # constraint the first field of the table in order to generate an

   594 # automatic index.

   595 #

   596 do_test avtrans-6.30 {

   597   execsql {

   598     BEGIN TRANSACTION;

   599     DROP TABLE t1;

   600     CREATE TABLE t1(a int unique,b,c);

   601     COMMIT;

   602     INSERT INTO t1 VALUES(1,-2,-3);

   603     INSERT INTO t1 VALUES(4,-5,-6);

   604     SELECT * FROM t1 ORDER BY a;

   605   }

   606 } {1 -2 -3 4 -5 -6}

   607 do_test avtrans-6.31 {

   608   execsql {

   609     CREATE INDEX i1 ON t1(b);

   610     SELECT * FROM t1 WHERE b<1;

   611   }

   612 } {4 -5 -6 1 -2 -3}

   613 do_test avtrans-6.32 {

   614   execsql {

   615     BEGIN TRANSACTION;

   616     DROP INDEX i1;

   617     SELECT * FROM t1 WHERE b<1;

   618     ROLLBACK;

   619   }

   620 } {1 -2 -3 4 -5 -6}

   621 do_test avtrans-6.33 {

   622   execsql {

   623     SELECT * FROM t1 WHERE b<1;

   624   }

   625 } {4 -5 -6 1 -2 -3}

   626 do_test avtrans-6.34 {

   627   execsql {

   628     BEGIN TRANSACTION;

   629     DROP TABLE t1;

   630     ROLLBACK;

   631     SELECT * FROM t1 WHERE b<1;

   632   }

   633 } {4 -5 -6 1 -2 -3}

   634 

   635 do_test avtrans-6.35 {

   636   execsql {

   637     BEGIN TRANSACTION;

   638     DROP INDEX i1;

   639     CREATE INDEX i1 ON t1(c);

   640     SELECT * FROM t1 WHERE b<1;

   641   }

   642 } {1 -2 -3 4 -5 -6}

   643 do_test avtrans-6.36 {

   644   execsql {

   645     SELECT * FROM t1 WHERE c<1;

   646   }

   647 } {4 -5 -6 1 -2 -3}

   648 do_test avtrans-6.37 {

   649   execsql {

   650     DROP INDEX i1;

   651     SELECT * FROM t1 WHERE c<1;

   652   }

   653 } {1 -2 -3 4 -5 -6}

   654 do_test avtrans-6.38 {

   655   execsql {

   656     ROLLBACK;

   657     SELECT * FROM t1 WHERE b<1;

   658   }

   659 } {4 -5 -6 1 -2 -3}

   660 do_test avtrans-6.39 {

   661   execsql {

   662     SELECT * FROM t1 WHERE c<1;

   663   }

   664 } {1 -2 -3 4 -5 -6}

   665 integrity_check avtrans-6.40

   666 

   667 ifcapable !floatingpoint {

   668   finish_test

   669   return

   670 }

   671 

   672 # Test to make sure rollback restores the database back to its original

   673 # state.

   674 #

   675 do_test avtrans-7.1 {

   676   execsql {BEGIN}

   677   for {set i 0} {$i<1000} {incr i} {

   678     set r1 [expr {rand()}]

   679     set r2 [expr {rand()}]

   680     set r3 [expr {rand()}]

   681     execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)"

   682   }

   683   execsql {COMMIT}

   684   set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}]

   685   set ::checksum2 [

   686     execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   687   ]

   688   execsql {SELECT count(*) FROM t2}

   689 } {1001}

   690 do_test avtrans-7.2 {

   691   execsql {SELECT md5sum(x,y,z) FROM t2}

   692 } $checksum

   693 do_test avtrans-7.2.1 {

   694   execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   695 } $checksum2

   696 do_test avtrans-7.3 {

   697   execsql {

   698     BEGIN;

   699     DELETE FROM t2;

   700     ROLLBACK;

   701     SELECT md5sum(x,y,z) FROM t2;

   702   }

   703 } $checksum

   704 do_test avtrans-7.4 {

   705   execsql {

   706     BEGIN;

   707     INSERT INTO t2 SELECT * FROM t2;

   708     ROLLBACK;

   709     SELECT md5sum(x,y,z) FROM t2;

   710   }

   711 } $checksum

   712 do_test avtrans-7.5 {

   713   execsql {

   714     BEGIN;

   715     DELETE FROM t2;

   716     ROLLBACK;

   717     SELECT md5sum(x,y,z) FROM t2;

   718   }

   719 } $checksum

   720 do_test avtrans-7.6 {

   721   execsql {

   722     BEGIN;

   723     INSERT INTO t2 SELECT * FROM t2;

   724     ROLLBACK;

   725     SELECT md5sum(x,y,z) FROM t2;

   726   }

   727 } $checksum

   728 do_test avtrans-7.7 {

   729   execsql {

   730     BEGIN;

   731     CREATE TABLE t3 AS SELECT * FROM t2;

   732     INSERT INTO t2 SELECT * FROM t3;

   733     ROLLBACK;

   734     SELECT md5sum(x,y,z) FROM t2;

   735   }

   736 } $checksum

   737 do_test avtrans-7.8 {

   738   execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   739 } $checksum2

   740 ifcapable tempdb {

   741   do_test avtrans-7.9 {

   742     execsql {

   743       BEGIN;

   744       CREATE TEMP TABLE t3 AS SELECT * FROM t2;

   745       INSERT INTO t2 SELECT * FROM t3;

   746       ROLLBACK;

   747       SELECT md5sum(x,y,z) FROM t2;

   748     }

   749   } $checksum

   750 }

   751 do_test avtrans-7.10 {

   752   execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   753 } $checksum2

   754 ifcapable tempdb {

   755   do_test avtrans-7.11 {

   756     execsql {

   757       BEGIN;

   758       CREATE TEMP TABLE t3 AS SELECT * FROM t2;

   759       INSERT INTO t2 SELECT * FROM t3;

   760       DROP INDEX i2x;

   761       DROP INDEX i2y;

   762       CREATE INDEX i3a ON t3(x);

   763       ROLLBACK;

   764       SELECT md5sum(x,y,z) FROM t2;

   765     }

   766   } $checksum

   767 }

   768 do_test avtrans-7.12 {

   769   execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   770 } $checksum2

   771 ifcapable tempdb {

   772   do_test avtrans-7.13 {

   773     execsql {

   774       BEGIN;

   775       DROP TABLE t2;

   776       ROLLBACK;

   777       SELECT md5sum(x,y,z) FROM t2;

   778     }

   779   } $checksum

   780 }

   781 do_test avtrans-7.14 {

   782   execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   783 } $checksum2

   784 integrity_check avtrans-7.15

   785 

   786 # Arrange for another process to begin modifying the database but abort

   787 # and die in the middle of the modification.  Then have this process read

   788 # the database.  This process should detect the journal file and roll it

   789 # back.  Verify that this happens correctly.

   790 #

   791 set fd [open test.tcl w]

   792 puts $fd {

   793   sqlite3 db test.db

   794   db eval {

   795     PRAGMA default_cache_size=20;

   796     BEGIN;

   797     CREATE TABLE t3 AS SELECT * FROM t2;

   798     DELETE FROM t2;

   799   }

   800   sqlite_abort

   801 }

   802 close $fd

   803 do_test avtrans-8.1 {

   804   catch {exec [info nameofexec] test.tcl}

   805   execsql {SELECT md5sum(x,y,z) FROM t2}

   806 } $checksum

   807 do_test avtrans-8.2 {

   808   execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}

   809 } $checksum2

   810 integrity_check avtrans-8.3

   811 

   812 # In the following sequence of tests, compute the MD5 sum of the content

   813 # of a table, make lots of modifications to that table, then do a rollback.

   814 # Verify that after the rollback, the MD5 checksum is unchanged.

   815 #

   816 do_test avtrans-9.1 {

   817   execsql {

   818     PRAGMA default_cache_size=10;

   819   }

   820   db close

   821   sqlite3 db test.db

   822   execsql {

   823     BEGIN;

   824     CREATE TABLE t3(x TEXT);

   825     INSERT INTO t3 VALUES(randstr(10,400));

   826     INSERT INTO t3 VALUES(randstr(10,400));

   827     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   828     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   829     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   830     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   831     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   832     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   833     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   834     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   835     INSERT INTO t3 SELECT randstr(10,400) FROM t3;

   836     COMMIT;

   837     SELECT count(*) FROM t3;

   838   }

   839 } {1024}

   840 

   841 # The following procedure computes a "signature" for table "t3".  If

   842 # T3 changes in any way, the signature should change.  

   843 #

   844 # This is used to test ROLLBACK.  We gather a signature for t3, then

   845 # make lots of changes to t3, then rollback and take another signature.

   846 # The two signatures should be the same.

   847 #

   848 proc signature {} {

   849   return [db eval {SELECT count(*), md5sum(x) FROM t3}]

   850 }

   851 

   852 # Repeat the following group of tests 20 times for quick testing and

   853 # 40 times for full testing.  Each iteration of the test makes table

   854 # t3 a little larger, and thus takes a little longer, so doing 40 tests

   855 # is more than 2.0 times slower than doing 20 tests.  Considerably more.

   856 #

   857 if {[info exists ISQUICK]} {

   858   set limit 20

   859 } else {

   860   set limit 40

   861 }

   862 

   863 # Do rollbacks.  Make sure the signature does not change.

   864 #

   865 for {set i 2} {$i<=$limit} {incr i} {

   866   set ::sig [signature]

   867   set cnt [lindex $::sig 0]

   868   if {$i%2==0} {

   869     execsql {PRAGMA fullfsync=ON}

   870   } else {

   871     execsql {PRAGMA fullfsync=OFF}

   872   }

   873   set sqlite_sync_count 0

   874   set sqlite_fullsync_count 0

   875   do_test avtrans-9.$i.1-$cnt {

   876      execsql {

   877        BEGIN;

   878        DELETE FROM t3 WHERE random()%10!=0;

   879        INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   880        INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   881        ROLLBACK;

   882      }

   883      signature

   884   } $sig

   885   do_test avtrans-9.$i.2-$cnt {

   886      execsql {

   887        BEGIN;

   888        DELETE FROM t3 WHERE random()%10!=0;

   889        INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   890        DELETE FROM t3 WHERE random()%10!=0;

   891        INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

   892        ROLLBACK;

   893      }

   894      signature

   895   } $sig

   896   if {$i<$limit} {

   897     do_test avtrans-9.$i.3-$cnt {

   898        execsql {

   899          INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;

   900        }

   901     } {}

   902     if {$tcl_platform(platform)=="unix"} {

   903       do_test avtrans-9.$i.4-$cnt {

   904          expr {$sqlite_sync_count>0}

   905       } 1

   906       ifcapable pager_pragmas {

   907         do_test avtrans-9.$i.5-$cnt {

   908            expr {$sqlite_fullsync_count>0}

   909         } [expr {$i%2==0}]

   910       } else {

   911         do_test avtrans-9.$i.5-$cnt {

   912            expr {$sqlite_fullsync_count==0}

   913         } {1}

   914       }

   915     }

   916   }

   917   set ::pager_old_format 0

   918 }

   919 integrity_check avtrans-10.1

   920    

   921 finish_test