github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/rollback2.test (about)

     1  # 2014 November 12
     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  # This file containst tests to verify that ROLLBACK or ROLLBACK TO 
    13  # operations interact correctly with ongoing SELECT statements.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  set ::testprefix rollback2
    19  
    20  proc int2hex {i} { format %.2X $i }
    21  db func int2hex int2hex
    22  do_execsql_test 1.0 {
    23    SELECT int2hex(0), int2hex(100), int2hex(255)
    24  } {00 64 FF}
    25  do_execsql_test 1.1 {
    26    CREATE TABLE t1(i, h);
    27    CREATE INDEX i1 ON t1(h);
    28    WITH data(a, b) AS (
    29      SELECT 1, int2hex(1)
    30        UNION ALL
    31      SELECT a+1, int2hex(a+1) FROM data WHERE a<40
    32    )
    33    INSERT INTO t1 SELECT * FROM data;
    34  } {}
    35  
    36  
    37  # do_rollback_test ID SWITCHES
    38  #
    39  # where SWITCHES are:
    40  #
    41  #   -setup      SQL script to open transaction and begin writing.
    42  #   -select     SELECT to execute after -setup script
    43  #   -result     Expected result of -select statement
    44  #   -rollback   Use this SQL command ("ROLLBACK" or "ROLLBACK TO ...") to
    45  #               rollback the transaction in the middle of the -select statment
    46  #               execution.
    47  #
    48  proc do_rollback_test {tn args} {
    49    set A(-setup)    ""
    50    set A(-select)   ""
    51    set A(-result)   ""
    52    set A(-rollback) ROLLBACK
    53  
    54    array set O $args
    55    foreach k [array names O] {
    56      if {[info exists A($k)]==0} { error "unknown option: $k" }
    57      set A($k) $O($k)
    58    }
    59  
    60    for {set iRollback 0} 1 {incr iRollback} {
    61      catch { db eval ROLLBACK }
    62      set res [list]
    63      db eval $A(-setup)
    64  
    65      set i 0
    66      db eval $A(-select) x {
    67        if {$i==$iRollback} { db eval $A(-rollback) }
    68        foreach k $x(*) { lappend res $x($k) }
    69        incr i
    70      }
    71  
    72      do_test $tn.$iRollback [list set {} $res] [list {*}$A(-result)]
    73      if {$i < $iRollback} break
    74    }
    75  }
    76  
    77  do_rollback_test 2.1 -setup {
    78    BEGIN;
    79      DELETE FROM t1 WHERE (i%2)==1;
    80  } -select {
    81    SELECT i FROM t1 WHERE (i%2)==0
    82  } -result {
    83    2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
    84  }
    85  
    86  do_rollback_test 2.2 -setup {
    87    BEGIN;
    88      DELETE FROM t1 WHERE (i%4)==1;
    89      SAVEPOINT one;
    90        DELETE FROM t1 WHERE (i%2)==1;
    91  } -rollback {
    92    ROLLBACK TO one;
    93  } -select {
    94    SELECT i FROM t1 WHERE (i%2)==0
    95  } -result {
    96    2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
    97  }
    98  
    99  #--------------------------------------------------------------------
   100  # Try with some index scans
   101  #
   102  do_eqp_test 3.1 {
   103    SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
   104  } {SCAN t1 USING INDEX i1}
   105  do_rollback_test 3.2 -setup {
   106    BEGIN;
   107      DELETE FROM t1 WHERE (i%2)==1;
   108  } -select {
   109    SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
   110  } -result {
   111    40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10  8  6  4  2
   112  }
   113  do_rollback_test 3.3 -setup {
   114    BEGIN;
   115      DELETE FROM t1 WHERE (i%4)==1;
   116      SAVEPOINT one;
   117        DELETE FROM t1 WHERE (i%2)==1;
   118  } -rollback {
   119    ROLLBACK TO one;
   120  } -select {
   121    SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
   122  } -result {
   123    40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10  8  6  4  2
   124  }
   125  
   126  #--------------------------------------------------------------------
   127  # Now with some index scans that feature overflow keys.
   128  #
   129  set leader [string repeat "abcdefghij" 70]
   130  do_execsql_test 4.1 { UPDATE t1 SET h = $leader || h; }
   131  
   132  do_eqp_test 4.2 {
   133    SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
   134  } {SCAN t1 USING INDEX i1}
   135  do_rollback_test 4.3 -setup {
   136    BEGIN;
   137      DELETE FROM t1 WHERE (i%2)==1;
   138  } -select {
   139    SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
   140  } -result {
   141    2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
   142  }
   143  do_rollback_test 4.4 -setup {
   144    BEGIN;
   145      DELETE FROM t1 WHERE (i%4)==1;
   146      SAVEPOINT one;
   147        DELETE FROM t1 WHERE (i%2)==1;
   148  } -rollback {
   149    ROLLBACK TO one;
   150  } -select {
   151    SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
   152  } -result {
   153    2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
   154  }
   155  
   156  finish_test