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 14set testdir [file dirname $argv0] 15source $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 22proc signature {} { 23 return [db eval {SELECT count(*), md5sum(x) FROM t3}] 24} 25 26do_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} 47wal_check_journal_mode savepoint2-1.1 48 49unset -nocomplain ::sig 50unset -nocomplain SQL 51 52set iterations 20 53 54set 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} 59set 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} 65set 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} 70set SQL(4) { 71 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE (random()%10 == 0); 72} 73 74 75 76for {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 151unset -nocomplain ::sig 152unset -nocomplain SQL 153 154finish_test 155