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