trans.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

} {a 4 b 5 c 6}
do_test trans-6.7 {
  catchsql {
    COMMIT;
    SELECT * FROM t1;
  }
} {1 {no such table: t1}}

# Repeat on a table with an automatically generated index.
#
do_test trans-6.10 {
  execsql2 {
    CREATE TABLE t1(a unique,b,c);
    INSERT INTO t1 VALUES(1,2,3);
    BEGIN TRANSACTION;
    DROP TABLE t1;
    CREATE TABLE t1(p unique,q,r);
    ROLLBACK;
    SELECT * FROM t1;
  }
} {a 1 b 2 c 3}
do_test trans-6.11 {
  execsql2 {
    BEGIN TRANSACTION;
    DROP TABLE t1;
    CREATE TABLE t1(p unique,q,r);
    COMMIT;
    SELECT * FROM t1;
  }
} {}
do_test trans-6.12 {
  execsql2 {
    INSERT INTO t1 VALUES(1,2,3);
    SELECT * FROM t1;
  }
} {p 1 q 2 r 3}
do_test trans-6.13 {
  execsql2 {
    BEGIN TRANSACTION;
    DROP TABLE t1;
    CREATE TABLE t1(a unique,b,c);
    INSERT INTO t1 VALUES(4,5,6);
    SELECT * FROM t1;
    DROP TABLE t1;
  }
} {a 4 b 5 c 6}
do_test trans-6.14 {
  execsql2 {
    ROLLBACK;
    SELECT * FROM t1;
  }
} {p 1 q 2 r 3}
do_test trans-6.15 {
  execsql2 {
    BEGIN TRANSACTION;
    DROP TABLE t1;
    CREATE TABLE t1(a unique,b,c);
    INSERT INTO t1 VALUES(4,5,6);
    SELECT * FROM t1;
    DROP TABLE t1;
  }
} {a 4 b 5 c 6}
do_test trans-6.16 {
  catchsql {
    COMMIT;
    SELECT * FROM t1;
  }
} {1 {no such table: t1}}

do_test trans-6.20 {
  execsql {
    CREATE TABLE t1(a integer primary key,b,c);
    INSERT INTO t1 VALUES(1,-2,-3);
    INSERT INTO t1 VALUES(4,-5,-6);
    SELECT * FROM t1;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.21 {
  execsql {
    CREATE INDEX i1 ON t1(b);
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.22 {
  execsql {
    BEGIN TRANSACTION;
    DROP INDEX i1;
    SELECT * FROM t1 WHERE b<1;
    ROLLBACK;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.23 {
  execsql {
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.24 {
  execsql {
    BEGIN TRANSACTION;
    DROP TABLE t1;
    ROLLBACK;
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}

do_test trans-6.25 {
  execsql {
    BEGIN TRANSACTION;
    DROP INDEX i1;
    CREATE INDEX i1 ON t1(c);
    SELECT * FROM t1 WHERE b<1;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.26 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.27 {
  execsql {
    ROLLBACK;
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.28 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}

# The following repeats steps 6.20 through 6.28, but puts a "unique"
# constraint the first field of the table in order to generate an
# automatic index.
#
do_test trans-6.30 {
  execsql {
    BEGIN TRANSACTION;
    DROP TABLE t1;
    CREATE TABLE t1(a int unique,b,c);
    COMMIT;
    INSERT INTO t1 VALUES(1,-2,-3);
    INSERT INTO t1 VALUES(4,-5,-6);
    SELECT * FROM t1 ORDER BY a;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.31 {
  execsql {
    CREATE INDEX i1 ON t1(b);
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.32 {
  execsql {
    BEGIN TRANSACTION;
    DROP INDEX i1;
    SELECT * FROM t1 WHERE b<1;
    ROLLBACK;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.33 {
  execsql {
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.34 {
  execsql {
    BEGIN TRANSACTION;
    DROP TABLE t1;
    ROLLBACK;
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}

do_test trans-6.35 {
  execsql {
    BEGIN TRANSACTION;
    DROP INDEX i1;
    CREATE INDEX i1 ON t1(c);
    SELECT * FROM t1 WHERE b<1;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.36 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.37 {
  execsql {
    DROP INDEX i1;
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.38 {
  execsql {
    ROLLBACK;
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.39 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}
integrity_check trans-6.40

# Test to make sure rollback restores the database back to its original
# state.
#
do_test trans-7.1 {
  execsql {BEGIN}
  for {set i 0} {$i<1000} {incr i} {
    set r1 [expr {rand()}]
    set r2 [expr {rand()}]
    set r3 [expr {rand()}]
    execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)"
  }
  execsql {COMMIT}
  set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}]
  set ::checksum2 [
    execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
  ]
  execsql {SELECT count(*) FROM t2}
} {1001}
do_test trans-7.2 {
  execsql {SELECT md5sum(x,y,z) FROM t2}
} $checksum
do_test trans-7.2.1 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
do_test trans-7.3 {
  execsql {
    BEGIN;
    DELETE FROM t2;
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.4 {
  execsql {
    BEGIN;
    INSERT INTO t2 SELECT * FROM t2;
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.5 {
  execsql {
    BEGIN;
    DELETE FROM t2;
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.6 {
  execsql {
    BEGIN;
    INSERT INTO t2 SELECT * FROM t2;
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.7 {
  execsql {
    BEGIN;
    CREATE TABLE t3 AS SELECT * FROM t2;
    INSERT INTO t2 SELECT * FROM t3;
    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.8 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
ifcapable tempdb {
  do_test trans-7.9 {
    execsql {
      BEGIN;
      CREATE TEMP TABLE t3 AS SELECT * FROM t2;
      INSERT INTO t2 SELECT * FROM t3;
      ROLLBACK;
      SELECT md5sum(x,y,z) FROM t2;
    }
  } $checksum
}
do_test trans-7.10 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
ifcapable tempdb {
  do_test trans-7.11 {
    execsql {
      BEGIN;
      CREATE TEMP TABLE t3 AS SELECT * FROM t2;
      INSERT INTO t2 SELECT * FROM t3;
      DROP INDEX i2x;
      DROP INDEX i2y;
      CREATE INDEX i3a ON t3(x);
      ROLLBACK;
      SELECT md5sum(x,y,z) FROM t2;
    }
  } $checksum
}
do_test trans-7.12 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
ifcapable tempdb {
  do_test trans-7.13 {
    execsql {
      BEGIN;
      DROP TABLE t2;
      ROLLBACK;
      SELECT md5sum(x,y,z) FROM t2;
    }
  } $checksum
}
do_test trans-7.14 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-7.15

# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read
# the database.  This process should detect the journal file and roll it
# back.  Verify that this happens correctly.
#
set fd [open test.tcl w]
puts $fd {
  sqlite3 db test.db
  db eval {
    PRAGMA default_cache_size=20;
    BEGIN;
    CREATE TABLE t3 AS SELECT * FROM t2;
    DELETE FROM t2;
  }
  sqlite_abort
}
close $fd
file copy -force test.db test.db-bu1
do_test trans-8.1 {
  catch {exec [info nameofexec] test.tcl}
  file copy -force test.db test.db-bu2
  file copy -force test.db-journal test.db-bu2-journal
  execsql {SELECT md5sum(x,y,z) FROM t2}
} $checksum
do_test trans-8.2 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-8.3

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
# Verify that after the rollback, the MD5 checksum is unchanged.
#
do_test trans-9.1 {
  execsql {
    PRAGMA default_cache_size=10;
  }
  db close
  sqlite3 db test.db
  execsql {
    BEGIN;
    CREATE TABLE t3(x TEXT);
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    COMMIT;
    SELECT count(*) FROM t3;
  }
} {1024}

# The following procedure computes a "signature" for table "t3".  If
# T3 changes in any way, the signature should change.  
#
# This is used to test ROLLBACK.  We gather a signature for t3, then
# make lots of changes to t3, then rollback and take another signature.
# The two signatures should be the same.
#
proc signature {} {
  return [db eval {SELECT count(*), md5sum(x) FROM t3}]
}

# Repeat the following group of tests 20 times for quick testing and
# 40 times for full testing.  Each iteration of the test makes table
# t3 a little larger, and thus takes a little longer, so doing 40 tests
# is more than 2.0 times slower than doing 20 tests.  Considerably more.
#
if {[info exists ISQUICK]} {
  set limit 20
} elseif {[info exists SOAKTEST]} {
  set limit 100
} else {
  set limit 40
}

# Do rollbacks.  Make sure the signature does not change.
#
for {set i 2} {$i<=$limit} {incr i} {
  set ::sig [signature]
  set cnt [lindex $::sig 0]
  if {$i%2==0} {
    execsql {PRAGMA fullfsync=ON}
  } else {
    execsql {PRAGMA fullfsync=OFF}
  }
  set sqlite_sync_count 0
  set sqlite_fullsync_count 0
  do_test trans-9.$i.1-$cnt {
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       ROLLBACK;
     }
     signature
  } $sig
  do_test trans-9.$i.2-$cnt {
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       ROLLBACK;
     }
     signature
  } $sig
  if {$i<$limit} {
    do_test trans-9.$i.3-$cnt {
       execsql {
         INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
       }
    } {}
    if {$tcl_platform(platform)=="unix"} {
      do_test trans-9.$i.4-$cnt {
         expr {$sqlite_sync_count>0}
      } 1
      ifcapable pager_pragmas {
        do_test trans-9.$i.5-$cnt {
           expr {$sqlite_fullsync_count>0}
        } [expr {$i%2==0}]
      } else {
        do_test trans-9.$i.5-$cnt {
          expr {$sqlite_fullsync_count>0}
        } {1}
      }
    }
  }
  set ::pager_old_format 0
}
   
finish_test