gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/savepoint2.test (about)

     1  # 2008 December 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  #
    12  # $Id: savepoint2.test,v 1.5 2009/06/05 17:09:12 drh Exp $
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  
    17  
    18  # Tests in this file are quite similar to those run by trans.test and
    19  # avtrans.test.
    20  #
    21  
    22  proc signature {} {
    23    return [db eval {SELECT count(*), md5sum(x) FROM t3}]
    24  }
    25  
    26  do_test savepoint2-1 {
    27    wal_set_journal_mode
    28    execsql {
    29      PRAGMA cache_size=10;
    30      BEGIN;
    31      CREATE TABLE t3(x TEXT);
    32      INSERT INTO t3 VALUES(randstr(10,400));
    33      INSERT INTO t3 VALUES(randstr(10,400));
    34      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    35      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    36      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    37      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    38      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    39      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    40      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    41      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    42      INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    43      COMMIT;
    44      SELECT count(*) FROM t3;
    45    }
    46  } {1024}
    47  wal_check_journal_mode savepoint2-1.1
    48  
    49  unset -nocomplain ::sig
    50  unset -nocomplain SQL
    51  
    52  set iterations 20
    53  
    54  set SQL(1) {
    55    DELETE FROM t3 WHERE random()%10!=0;
    56    INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
    57    INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
    58  }
    59  set SQL(2) {
    60    DELETE FROM t3 WHERE random()%10!=0;
    61    INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
    62    DELETE FROM t3 WHERE random()%10!=0;
    63    INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
    64  } 
    65  set SQL(3) {
    66    UPDATE t3 SET x = randstr(10, 400) WHERE random()%10;
    67    INSERT INTO t3 SELECT x FROM t3 WHERE random()%10;
    68    DELETE FROM t3 WHERE random()%10;
    69  }
    70  set SQL(4) {
    71    INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE (random()%10 == 0);
    72  }
    73  
    74  
    75  
    76  for {set ii 2} {$ii < ($iterations+2)} {incr ii} {
    77  
    78    # Record the database signature. Optionally (every second run) open a
    79    # transaction. In all cases open savepoint "one", which may or may
    80    # not be a transaction savepoint, depending on whether or not a real
    81    # transaction has been opened.
    82    #
    83    do_test savepoint2-$ii.1 {
    84      if {$ii % 2} { execsql BEGIN }
    85      set ::sig(one) [signature]
    86      execsql "SAVEPOINT one"
    87    } {}
    88  
    89    # Execute some SQL on the database. Then rollback to savepoint "one".
    90    # Check that the database signature is as it was when "one" was opened.
    91    # 
    92    do_test savepoint2-$ii.2 {
    93      execsql $SQL(1)
    94      execsql "ROLLBACK to one"
    95      signature
    96    } $::sig(one)
    97    integrity_check savepoint2-$ii.2.1
    98  
    99    # Execute some SQL. Then open savepoint "two". Savepoint "two" is therefore
   100    # nested in savepoint "one".
   101    #
   102    do_test savepoint2-$ii.3 {
   103      execsql $SQL(1)
   104      set ::sig(two) [signature]
   105      execsql "SAVEPOINT two"
   106    } {}
   107  
   108    # More SQL changes. The rollback to savepoint "two". Check that the 
   109    # signature is as it was when savepoint "two" was opened.
   110    #
   111    do_test savepoint2-$ii.4 {
   112      execsql $SQL(2)
   113      execsql "ROLLBACK to two"
   114      signature
   115    } $::sig(two)
   116    integrity_check savepoint2-$ii.4.1
   117  
   118    # More SQL changes. The rollback to savepoint "two". Check that the 
   119    # signature is as it was when savepoint "two" was opened.
   120    #
   121    do_test savepoint2-$ii.5 {
   122      execsql $SQL(2)
   123      execsql "SAVEPOINT three"
   124      execsql $SQL(3)
   125      execsql "RELEASE three"
   126      execsql "ROLLBACK to one"
   127      signature
   128    } $::sig(one)
   129  
   130    # By this point the database is in the same state as it was at the 
   131    # top of the for{} loop (everything having been rolled back by the 
   132    # "ROLLBACK TO one" command above). So make a few changes to the 
   133    # database and COMMIT the open transaction, so that the next iteration
   134    # of the for{} loop works on a different dataset.
   135    # 
   136    # The transaction being committed here may have been opened normally using
   137    # "BEGIN", or may have been opened using a transaction savepoint created
   138    # by the "SAVEPOINT one" statement.
   139    #
   140    do_test savepoint2-$ii.6 {
   141      execsql $SQL(4)
   142      execsql COMMIT
   143      sqlite3_get_autocommit db
   144    } {1}
   145    integrity_check savepoint2-$ii.6.1
   146  
   147    # Check that the connection is still running in WAL mode.
   148    wal_check_journal_mode savepoint2-$ii.7
   149  }
   150  
   151  unset -nocomplain ::sig
   152  unset -nocomplain SQL
   153  
   154  finish_test