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.  The

    12 # focus of this script is database locks.

    13 #

    14 # $Id: trans.test,v 1.38 2008/04/19 20:34:19 drh Exp $

    15 

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 

    21 # Create several tables to work with.

    22 #

    23 do_test trans-1.0 {

    24   execsql {

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

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

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

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

    29     SELECT b FROM one ORDER BY a;

    30   }

    31 } {one two three}

    32 integrity_check trans-1.0.1

    33 do_test trans-1.1 {

    34   execsql {

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

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

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

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

    39     SELECT b FROM two ORDER BY a;

    40   }

    41 } {I V X}

    42 do_test trans-1.9 {

    43   sqlite3 altdb test.db

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

    45 } {one two three}

    46 do_test trans-1.10 {

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

    48 } {I V X}

    49 integrity_check trans-1.11

    50 

    51 # Basic transactions

    52 #

    53 do_test trans-2.1 {

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

    55   lappend v $msg

    56 } {0 {}}

    57 do_test trans-2.2 {

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

    59   lappend v $msg

    60 } {0 {}}

    61 do_test trans-2.3 {

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

    63   lappend v $msg

    64 } {0 {}}

    65 do_test trans-2.4 {

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

    67   lappend v $msg

    68 } {0 {}}

    69 do_test trans-2.5 {

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

    71   lappend v $msg

    72 } {0 {}}

    73 do_test trans-2.6 {

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

    75   lappend v $msg

    76 } {0 {}}

    77 do_test trans-2.10 {

    78   execsql {

    79     BEGIN;

    80     SELECT a FROM one ORDER BY a;

    81     SELECT a FROM two ORDER BY a;

    82     END;

    83   }

    84 } {1 2 3 1 5 10}

    85 integrity_check trans-2.11

    86 

    87 # Check the locking behavior

    88 #

    89 do_test trans-3.1 {

    90   execsql {

    91     BEGIN;

    92     UPDATE one SET a = 0 WHERE 0;

    93     SELECT a FROM one ORDER BY a;

    94   }

    95 } {1 2 3}

    96 do_test trans-3.2 {

    97   catchsql {

    98     SELECT a FROM two ORDER BY a;

    99   } altdb

   100 } {0 {1 5 10}}

   101 

   102 do_test trans-3.3 {

   103   catchsql {

   104     SELECT a FROM one ORDER BY a;

   105   } altdb

   106 } {0 {1 2 3}}

   107 do_test trans-3.4 {

   108   catchsql {

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

   110   }

   111 } {0 {}}

   112 do_test trans-3.5 {

   113   catchsql {

   114     SELECT a FROM two ORDER BY a;

   115   } altdb

   116 } {0 {1 5 10}}

   117 do_test trans-3.6 {

   118   catchsql {

   119     SELECT a FROM one ORDER BY a;

   120   } altdb

   121 } {0 {1 2 3}}

   122 do_test trans-3.7 {

   123   catchsql {

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

   125   }

   126 } {0 {}}

   127 do_test trans-3.8 {

   128   catchsql {

   129     SELECT a FROM two ORDER BY a;

   130   } altdb

   131 } {0 {1 5 10}}

   132 do_test trans-3.9 {

   133   catchsql {

   134     SELECT a FROM one ORDER BY a;

   135   } altdb

   136 } {0 {1 2 3}}

   137 do_test trans-3.10 {

   138   execsql {END TRANSACTION}

   139 } {}

   140 

   141 do_test trans-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 trans-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 trans-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 trans-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 integrity_check trans-3.15

   166 

   167 do_test trans-4.1 {

   168   set v [catch {execsql {

   169     COMMIT;

   170   } db} msg]

   171   lappend v $msg

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

   173 do_test trans-4.2 {

   174   set v [catch {execsql {

   175     ROLLBACK;

   176   } db} msg]

   177   lappend v $msg

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

   179 do_test trans-4.3 {

   180   catchsql {

   181     BEGIN TRANSACTION;

   182     UPDATE two SET a = 0 WHERE 0;

   183     SELECT a FROM two ORDER BY a;

   184   } db

   185 } {0 {1 4 5 10}}

   186 do_test trans-4.4 {

   187   catchsql {

   188     SELECT a FROM two ORDER BY a;

   189   } altdb

   190 } {0 {1 4 5 10}}

   191 do_test trans-4.5 {

   192   catchsql {

   193     SELECT a FROM one ORDER BY a;

   194   } altdb

   195 } {0 {1 2 3 4}}

   196 do_test trans-4.6 {

   197   catchsql {

   198     BEGIN TRANSACTION;

   199     SELECT a FROM one ORDER BY a;

   200   } db

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

   202 do_test trans-4.7 {

   203   catchsql {

   204     SELECT a FROM two ORDER BY a;

   205   } altdb

   206 } {0 {1 4 5 10}}

   207 do_test trans-4.8 {

   208   catchsql {

   209     SELECT a FROM one ORDER BY a;

   210   } altdb

   211 } {0 {1 2 3 4}}

   212 do_test trans-4.9 {

   213   set v [catch {execsql {

   214     END TRANSACTION;

   215     SELECT a FROM two ORDER BY a;

   216   } db} msg]

   217   lappend v $msg

   218 } {0 {1 4 5 10}}

   219 do_test trans-4.10 {

   220   set v [catch {execsql {

   221     SELECT a FROM two ORDER BY a;

   222   } altdb} msg]

   223   lappend v $msg

   224 } {0 {1 4 5 10}}

   225 do_test trans-4.11 {

   226   set v [catch {execsql {

   227     SELECT a FROM one ORDER BY a;

   228   } altdb} msg]

   229   lappend v $msg

   230 } {0 {1 2 3 4}}

   231 integrity_check trans-4.12

   232 do_test trans-4.98 {

   233   altdb close

   234   execsql {

   235     DROP TABLE one;

   236     DROP TABLE two;

   237   }

   238 } {}

   239 integrity_check trans-4.99

   240 

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

   242 #

   243 do_test trans-5.1 {

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

   245 } {}

   246 do_test trans-5.2 {

   247   execsql {BEGIN TRANSACTION}

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

   249 } {}

   250 do_test trans-5.3 {

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

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

   253 } {one}

   254 do_test trans-5.4 {

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

   256 } {}

   257 do_test trans-5.5 {

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

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

   260 } {hello 1}

   261 do_test trans-5.6 {

   262   execsql {ROLLBACK}

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

   264 } {}

   265 do_test trans-5.7 {

   266   set v [catch {

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

   268   } msg]

   269   lappend v $msg

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

   271 

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

   273 # DROP TABLEs and DROP INDEXs

   274 #

   275 do_test trans-5.8 {

   276   execsql {

   277     SELECT name fROM sqlite_master 

   278     WHERE type='table' OR type='index'

   279     ORDER BY name

   280   }

   281 } {}

   282 do_test trans-5.9 {

   283   execsql {

   284     BEGIN TRANSACTION;

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

   286     SELECT name fROM sqlite_master 

   287     WHERE type='table' OR type='index'

   288     ORDER BY name;

   289   }

   290 } {t1}

   291 do_test trans-5.10 {

   292   execsql {

   293     CREATE INDEX i1 ON t1(a);

   294     SELECT name fROM sqlite_master 

   295     WHERE type='table' OR type='index'

   296     ORDER BY name;

   297   }

   298 } {i1 t1}

   299 do_test trans-5.11 {

   300   execsql {

   301     COMMIT;

   302     SELECT name fROM sqlite_master 

   303     WHERE type='table' OR type='index'

   304     ORDER BY name;

   305   }

   306 } {i1 t1}

   307 do_test trans-5.12 {

   308   execsql {

   309     BEGIN TRANSACTION;

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

   311     CREATE INDEX i2a ON t2(a);

   312     CREATE INDEX i2b ON t2(b);

   313     DROP TABLE t1;

   314     SELECT name fROM sqlite_master 

   315     WHERE type='table' OR type='index'

   316     ORDER BY name;

   317   }

   318 } {i2a i2b t2}

   319 do_test trans-5.13 {

   320   execsql {

   321     ROLLBACK;

   322     SELECT name fROM sqlite_master 

   323     WHERE type='table' OR type='index'

   324     ORDER BY name;

   325   }

   326 } {i1 t1}

   327 do_test trans-5.14 {

   328   execsql {

   329     BEGIN TRANSACTION;

   330     DROP INDEX i1;

   331     SELECT name fROM sqlite_master 

   332     WHERE type='table' OR type='index'

   333     ORDER BY name;

   334   }

   335 } {t1}

   336 do_test trans-5.15 {

   337   execsql {

   338     ROLLBACK;

   339     SELECT name fROM sqlite_master 

   340     WHERE type='table' OR type='index'

   341     ORDER BY name;

   342   }

   343 } {i1 t1}

   344 do_test trans-5.16 {

   345   execsql {

   346     BEGIN TRANSACTION;

   347     DROP INDEX i1;

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

   349     CREATE INDEX i2x ON t2(x);

   350     CREATE INDEX i2y ON t2(y);

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

   352     SELECT name fROM sqlite_master 

   353     WHERE type='table' OR type='index'

   354     ORDER BY name;

   355   }

   356 } {i2x i2y t1 t2}

   357 do_test trans-5.17 {

   358   execsql {

   359     COMMIT;

   360     SELECT name fROM sqlite_master 

   361     WHERE type='table' OR type='index'

   362     ORDER BY name;

   363   }

   364 } {i2x i2y t1 t2}

   365 do_test trans-5.18 {

   366   execsql {

   367     SELECT * FROM t2;

   368   }

   369 } {1 2 3}

   370 do_test trans-5.19 {

   371   execsql {

   372     SELECT x FROM t2 WHERE y=2;

   373   }

   374 } {1}

   375 do_test trans-5.20 {

   376   execsql {

   377     BEGIN TRANSACTION;

   378     DROP TABLE t1;

   379     DROP TABLE t2;

   380     SELECT name fROM sqlite_master 

   381     WHERE type='table' OR type='index'

   382     ORDER BY name;

   383   }

   384 } {}

   385 do_test trans-5.21 {

   386   set r [catch {execsql {

   387     SELECT * FROM t2

   388   }} msg]

   389   lappend r $msg

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

   391 do_test trans-5.22 {

   392   execsql {

   393     ROLLBACK;

   394     SELECT name fROM sqlite_master 

   395     WHERE type='table' OR type='index'

   396     ORDER BY name;

   397   }

   398 } {i2x i2y t1 t2}

   399 do_test trans-5.23 {

   400   execsql {

   401     SELECT * FROM t2;

   402   }

   403 } {1 2 3}

   404 integrity_check trans-5.23

   405 

   406 

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

   408 # within a transaction.  Make sure ROLLBACK works.

   409 #

   410 do_test trans-6.1 {

   411   execsql2 {

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

   413     BEGIN TRANSACTION;

   414     DROP TABLE t1;

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

   416     ROLLBACK;

   417     SELECT * FROM t1;

   418   }

   419 } {a 1 b 2 c 3}

   420 do_test trans-6.2 {

   421   execsql2 {

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

   423     BEGIN TRANSACTION;

   424     DROP TABLE t1;

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

   426     COMMIT;

   427     SELECT * FROM t1;

   428   }

   429 } {}

   430 do_test trans-6.3 {

   431   execsql2 {

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

   433     SELECT * FROM t1;

   434   }

   435 } {p 1 q 2 r 3}

   436 do_test trans-6.4 {

   437   execsql2 {

   438     BEGIN TRANSACTION;

   439     DROP TABLE t1;

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

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

   442     SELECT * FROM t1;

   443     DROP TABLE t1;

   444   }

   445 } {a 4 b 5 c 6}

   446 do_test trans-6.5 {

   447   execsql2 {

   448     ROLLBACK;

   449     SELECT * FROM t1;

   450   }

   451 } {p 1 q 2 r 3}

   452 do_test trans-6.6 {

   453   execsql2 {

   454     BEGIN TRANSACTION;

   455     DROP TABLE t1;

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

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

   458     SELECT * FROM t1;

   459     DROP TABLE t1;

   460   }

   461 } {a 4 b 5 c 6}

   462 do_test trans-6.7 {

   463   catchsql {

   464     COMMIT;

   465     SELECT * FROM t1;

   466   }

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

   468 

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

   470 #

   471 do_test trans-6.10 {

   472   execsql2 {

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

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

   475     BEGIN TRANSACTION;

   476     DROP TABLE t1;

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

   478     ROLLBACK;

   479     SELECT * FROM t1;

   480   }

   481 } {a 1 b 2 c 3}

   482 do_test trans-6.11 {

   483   execsql2 {

   484     BEGIN TRANSACTION;

   485     DROP TABLE t1;

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

   487     COMMIT;

   488     SELECT * FROM t1;

   489   }

   490 } {}

   491 do_test trans-6.12 {

   492   execsql2 {

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

   494     SELECT * FROM t1;

   495   }

   496 } {p 1 q 2 r 3}

   497 do_test trans-6.13 {

   498   execsql2 {

   499     BEGIN TRANSACTION;

   500     DROP TABLE t1;

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

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

   503     SELECT * FROM t1;

   504     DROP TABLE t1;

   505   }

   506 } {a 4 b 5 c 6}

   507 do_test trans-6.14 {

   508   execsql2 {

   509     ROLLBACK;

   510     SELECT * FROM t1;

   511   }

   512 } {p 1 q 2 r 3}

   513 do_test trans-6.15 {

   514   execsql2 {

   515     BEGIN TRANSACTION;

   516     DROP TABLE t1;

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

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

   519     SELECT * FROM t1;

   520     DROP TABLE t1;

   521   }

   522 } {a 4 b 5 c 6}

   523 do_test trans-6.16 {

   524   catchsql {

   525     COMMIT;

   526     SELECT * FROM t1;

   527   }

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

   529 

   530 do_test trans-6.20 {

   531   execsql {

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

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

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

   535     SELECT * FROM t1;

   536   }

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

   538 do_test trans-6.21 {

   539   execsql {

   540     CREATE INDEX i1 ON t1(b);

   541     SELECT * FROM t1 WHERE b<1;

   542   }

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

   544 do_test trans-6.22 {

   545   execsql {

   546     BEGIN TRANSACTION;

   547     DROP INDEX i1;

   548     SELECT * FROM t1 WHERE b<1;

   549     ROLLBACK;

   550   }

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

   552 do_test trans-6.23 {

   553   execsql {

   554     SELECT * FROM t1 WHERE b<1;

   555   }

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

   557 do_test trans-6.24 {

   558   execsql {

   559     BEGIN TRANSACTION;

   560     DROP TABLE t1;

   561     ROLLBACK;

   562     SELECT * FROM t1 WHERE b<1;

   563   }

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

   565 

   566 do_test trans-6.25 {

   567   execsql {

   568     BEGIN TRANSACTION;

   569     DROP INDEX i1;

   570     CREATE INDEX i1 ON t1(c);

   571     SELECT * FROM t1 WHERE b<1;

   572   }

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

   574 do_test trans-6.26 {

   575   execsql {

   576     SELECT * FROM t1 WHERE c<1;

   577   }

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

   579 do_test trans-6.27 {

   580   execsql {

   581     ROLLBACK;

   582     SELECT * FROM t1 WHERE b<1;

   583   }

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

   585 do_test trans-6.28 {

   586   execsql {

   587     SELECT * FROM t1 WHERE c<1;

   588   }

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

   590 

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

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

   593 # automatic index.

   594 #

   595 do_test trans-6.30 {

   596   execsql {

   597     BEGIN TRANSACTION;

   598     DROP TABLE t1;

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

   600     COMMIT;

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

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

   603     SELECT * FROM t1 ORDER BY a;

   604   }

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

   606 do_test trans-6.31 {

   607   execsql {

   608     CREATE INDEX i1 ON t1(b);

   609     SELECT * FROM t1 WHERE b<1;

   610   }

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

   612 do_test trans-6.32 {

   613   execsql {

   614     BEGIN TRANSACTION;

   615     DROP INDEX i1;

   616     SELECT * FROM t1 WHERE b<1;

   617     ROLLBACK;

   618   }

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

   620 do_test trans-6.33 {

   621   execsql {

   622     SELECT * FROM t1 WHERE b<1;

   623   }

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

   625 do_test trans-6.34 {

   626   execsql {

   627     BEGIN TRANSACTION;

   628     DROP TABLE t1;

   629     ROLLBACK;

   630     SELECT * FROM t1 WHERE b<1;

   631   }

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

   633 

   634 do_test trans-6.35 {

   635   execsql {

   636     BEGIN TRANSACTION;

   637     DROP INDEX i1;

   638     CREATE INDEX i1 ON t1(c);

   639     SELECT * FROM t1 WHERE b<1;

   640   }

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

   642 do_test trans-6.36 {

   643   execsql {

   644     SELECT * FROM t1 WHERE c<1;

   645   }

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

   647 do_test trans-6.37 {

   648   execsql {

   649     DROP INDEX i1;

   650     SELECT * FROM t1 WHERE c<1;

   651   }

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

   653 do_test trans-6.38 {

   654   execsql {

   655     ROLLBACK;

   656     SELECT * FROM t1 WHERE b<1;

   657   }

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

   659 do_test trans-6.39 {

   660   execsql {

   661     SELECT * FROM t1 WHERE c<1;

   662   }

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

   664 integrity_check trans-6.40

   665 

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

   667 # state.

   668 #

   669 do_test trans-7.1 {

   670   execsql {BEGIN}

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

   672     set r1 [expr {rand()}]

   673     set r2 [expr {rand()}]

   674     set r3 [expr {rand()}]

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

   676   }

   677   execsql {COMMIT}

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

   679   set ::checksum2 [

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

   681   ]

   682   execsql {SELECT count(*) FROM t2}

   683 } {1001}

   684 do_test trans-7.2 {

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

   686 } $checksum

   687 do_test trans-7.2.1 {

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

   689 } $checksum2

   690 do_test trans-7.3 {

   691   execsql {

   692     BEGIN;

   693     DELETE FROM t2;

   694     ROLLBACK;

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

   696   }

   697 } $checksum

   698 do_test trans-7.4 {

   699   execsql {

   700     BEGIN;

   701     INSERT INTO t2 SELECT * FROM t2;

   702     ROLLBACK;

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

   704   }

   705 } $checksum

   706 do_test trans-7.5 {

   707   execsql {

   708     BEGIN;

   709     DELETE FROM t2;

   710     ROLLBACK;

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

   712   }

   713 } $checksum

   714 do_test trans-7.6 {

   715   execsql {

   716     BEGIN;

   717     INSERT INTO t2 SELECT * FROM t2;

   718     ROLLBACK;

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

   720   }

   721 } $checksum

   722 do_test trans-7.7 {

   723   execsql {

   724     BEGIN;

   725     CREATE TABLE t3 AS SELECT * FROM t2;

   726     INSERT INTO t2 SELECT * FROM t3;

   727     ROLLBACK;

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

   729   }

   730 } $checksum

   731 do_test trans-7.8 {

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

   733 } $checksum2

   734 ifcapable tempdb {

   735   do_test trans-7.9 {

   736     execsql {

   737       BEGIN;

   738       CREATE TEMP TABLE t3 AS SELECT * FROM t2;

   739       INSERT INTO t2 SELECT * FROM t3;

   740       ROLLBACK;

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

   742     }

   743   } $checksum

   744 }

   745 do_test trans-7.10 {

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

   747 } $checksum2

   748 ifcapable tempdb {

   749   do_test trans-7.11 {

   750     execsql {

   751       BEGIN;

   752       CREATE TEMP TABLE t3 AS SELECT * FROM t2;

   753       INSERT INTO t2 SELECT * FROM t3;

   754       DROP INDEX i2x;

   755       DROP INDEX i2y;

   756       CREATE INDEX i3a ON t3(x);

   757       ROLLBACK;

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

   759     }

   760   } $checksum

   761 }

   762 do_test trans-7.12 {

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

   764 } $checksum2

   765 ifcapable tempdb {

   766   do_test trans-7.13 {

   767     execsql {

   768       BEGIN;

   769       DROP TABLE t2;

   770       ROLLBACK;

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

   772     }

   773   } $checksum

   774 }

   775 do_test trans-7.14 {

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

   777 } $checksum2

   778 integrity_check trans-7.15

   779 

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

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

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

   783 # back.  Verify that this happens correctly.

   784 #

   785 print_text "TclSqlite3-2" "begin"

   786 set fd [open test.tcl w]

   787 puts $fd {

   788   sqlite3 db test.db

   789   db eval {

   790     PRAGMA default_cache_size=20;

   791     BEGIN;

   792     CREATE TABLE t3 AS SELECT * FROM t2;

   793     DELETE FROM t2;

   794   }

   795   sqlite_abort

   796 }

   797 close $fd

   798 print_text "TclSqlite3-2" "end"

   799   

   800 do_test trans-8.1 {

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

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

   803 } $checksum

   804 do_test trans-8.2 {

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

   806 } $checksum2

   807 integrity_check trans-8.3

   808 

   809 print_text "TclSqlite3-2" "begin"

   810 set fd [open test.tcl w]

   811 puts $fd {

   812   sqlite3 db test.db

   813   db eval {

   814     PRAGMA journal_mode=persist;

   815     PRAGMA default_cache_size=20;

   816     BEGIN;

   817     CREATE TABLE t3 AS SELECT * FROM t2;

   818     DELETE FROM t2;

   819   }

   820   sqlite_abort

   821 }

   822 close $fd

   823 print_text "TclSqlite3-2" "end"

   824 

   825 do_test trans-8.4 {

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

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

   828 } $checksum

   829 do_test trans-8.5 {

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

   831 } $checksum2

   832 integrity_check trans-8.6

   833 

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

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

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

   837 #

   838 do_test trans-9.1 {

   839   execsql {

   840     PRAGMA default_cache_size=10;

   841   }

   842   db close

   843   sqlite3 db test.db

   844   execsql {

   845     BEGIN;

   846     CREATE TABLE t3(x TEXT);

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

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

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

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

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

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

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

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

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

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

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

   858     COMMIT;

   859     SELECT count(*) FROM t3;

   860   }

   861 } {1024}

   862 

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

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

   865 #

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

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

   868 # The two signatures should be the same.

   869 #

   870 proc signature {} {

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

   872 }

   873 

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

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

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

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

   878 #

   879 if {[info exists ISQUICK]} {

   880   set limit 20

   881 } elseif {[info exists SOAKTEST]} {

   882   set limit 100

   883 } else {

   884   set limit 40

   885 }

   886 

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

   888 #

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

   890   set ::sig [signature]

   891   set cnt [lindex $::sig 0]

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

   893     execsql {PRAGMA fullfsync=ON}

   894   } else {

   895     execsql {PRAGMA fullfsync=OFF}

   896   }

   897   set sqlite_sync_count 0

   898   set sqlite_fullsync_count 0

   899   do_test trans-9.$i.1-$cnt {

   900      execsql {

   901        BEGIN;

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

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

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

   905        ROLLBACK;

   906      }

   907      signature

   908   } $sig

   909   do_test trans-9.$i.2-$cnt {

   910      execsql {

   911        BEGIN;

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

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

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

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

   916        ROLLBACK;

   917      }

   918      signature

   919   } $sig

   920   if {$i<$limit} {

   921     do_test trans-9.$i.3-$cnt {

   922        execsql {

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

   924        }

   925     } {}

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

   927       do_test trans-9.$i.4-$cnt {

   928          expr {$sqlite_sync_count>0}

   929       } 1

   930       ifcapable pager_pragmas {

   931         do_test trans-9.$i.5-$cnt {

   932            expr {$sqlite_fullsync_count>0}

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

   934       } else {

   935         do_test trans-9.$i.5-$cnt {

   936           expr {$sqlite_fullsync_count==0}

   937         } {1}

   938       }

   939     }

   940   }

   941   set ::pager_old_format 0

   942 }

   943    

   944 finish_test