AryaWu/sqlite

1# 2014-12-192#3# The author disclaims copyright to this source code.  In place of4# 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# This file implements regression tests for SQLite library.12#13# This file implements tests for PRAGMA data_version command.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19if {[sqlite3 -has-codec]} {20  finish_test21  return22}23 24do_execsql_test pragma3-100 {25  PRAGMA data_version;26} {1}27do_execsql_test pragma3-101 {28  PRAGMA temp.data_version;29} {1}30 31# Writing to the pragma is a no-op 32do_execsql_test pragma3-102 {33  PRAGMA main.data_version=1234;34  PRAGMA main.data_version;35} {1 1}36 37# EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides38# an indication that the database file has been modified.39#40# EVIDENCE-OF: R-47505-58569 The "PRAGMA data_version" value is41# unchanged for commits made on the same database connection.42#43do_execsql_test pragma3-110 {44  PRAGMA data_version;45  BEGIN IMMEDIATE;46  PRAGMA data_version;47  CREATE TABLE t1(a);48  INSERT INTO t1 VALUES(100),(200),(300);49  PRAGMA data_version;50  COMMIT;51  SELECT * FROM t1;52  PRAGMA data_version;53} {1 1 1 100 200 300 1}54 55sqlite3 db2 test.db56do_test pragma3-120 {57  db2 eval {58    SELECT * FROM t1;59    PRAGMA data_version;60  }61} {100 200 300 1}62 63do_execsql_test pragma3-130 {64  PRAGMA data_version;65  BEGIN IMMEDIATE;66  PRAGMA data_version;67  INSERT INTO t1 VALUES(400),(500);68  PRAGMA data_version;69  COMMIT;70  SELECT * FROM t1;71  PRAGMA data_version;72  PRAGMA shrink_memory;73} {1 1 1 100 200 300 400 500 1}74 75# EVIDENCE-OF: R-63005-41812 The integer values returned by two76# invocations of "PRAGMA data_version" from the same connection will be77# different if changes were committed to the database by any other78# connection in the interim.79#80# Value went from 1 in pragma3-120 to 2 here.81#82do_test pragma3-140 {83  db2 eval {84    SELECT * FROM t1;85    PRAGMA data_version;86    BEGIN IMMEDIATE;87    PRAGMA data_version;88    UPDATE t1 SET a=a+1;89    COMMIT;90    SELECT * FROM t1;91    PRAGMA data_version;92  }93} {100 200 300 400 500 2 2 101 201 301 401 501 2}94do_execsql_test pragma3-150 {95  SELECT * FROM t1;96  PRAGMA data_version;97} {101 201 301 401 501 2}98 99#100do_test pragma3-160 {101  db eval {102    BEGIN;103    PRAGMA data_version;104    UPDATE t1 SET a=555 WHERE a=501;105    PRAGMA data_version;106    SELECT * FROM t1 ORDER BY a;107    PRAGMA data_version;108  }109} {2 2 101 201 301 401 555 2}110do_test pragma3-170 {111  db2 eval {112    PRAGMA data_version;113  }114} {2}115do_test pragma3-180 {116  db eval {117    COMMIT;118    PRAGMA data_version;119  }120} {2}121do_test pragma3-190 {122  db2 eval {123    PRAGMA data_version;124  }125} {3}126 127# EVIDENCE-OF: R-19326-44825 The "PRAGMA data_version" value is a local128# property of each database connection and so values returned by two129# concurrent invocations of "PRAGMA data_version" on separate database130# connections are often different even though the underlying database is131# identical.132#133do_test pragma3-195 {134  expr {[db eval {PRAGMA data_version}]!=[db2 eval {PRAGMA data_version}]}135} {1}136 137# EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is138# the same for all database connections, including database connections139# in separate processes and shared cache database connections.140#141# The next block checks the behavior for separate processes.142#143do_test pragma3-200 {144  db eval {PRAGMA data_version; SELECT * FROM t1;}145} {2 101 201 301 401 555}146do_test pragma3-201 {147  set fd [open pragma3.txt wb]148  puts $fd {149     sqlite3 db test.db;150     db eval {DELETE FROM t1 WHERE a>300};151     db close;152     exit;153  }154  close $fd155  exec [info nameofexec] pragma3.txt156  forcedelete pragma3.txt157  db eval {158    PRAGMA data_version;159    SELECT * FROM t1;160  }161} {3 101 201}162db2 close163db close164 165# EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is166# the same for all database connections, including database connections167# in separate processes and shared cache database connections.168#169# The next block checks that behavior is the same for shared-cache.170#171ifcapable shared_cache {172  set ::enable_shared_cache [sqlite3_enable_shared_cache 1]173  sqlite3 db test.db174  sqlite3 db2 test.db175  do_test pragma3-300 {176    db eval {177      PRAGMA data_version;178      BEGIN;179      CREATE TABLE t3(a,b,c);180      CREATE TABLE t4(x,y,z);181      INSERT INTO t4 VALUES(123,456,789);182      PRAGMA data_version;183      COMMIT;184      PRAGMA data_version;185    }186  } {1 1 1}187  do_test pragma3-310 {188    db2 eval {189      PRAGMA data_version;190      BEGIN;191      INSERT INTO t3(a,b,c) VALUES('abc','def','ghi');192      SELECT * FROM t3;193      PRAGMA data_version;194    }195  } {2 abc def ghi 2}196  # The transaction in db2 has not yet committed, so the data_version in197  # db is unchanged.198  do_test pragma3-320 {199    db eval {200      PRAGMA data_version;201      SELECT * FROM t4;202    }203  } {1 123 456 789}204  do_test pragma3-330 {205    db2 eval {206      COMMIT;207      PRAGMA data_version;208      SELECT * FROM t4;209    }210  } {2 123 456 789}211  do_test pragma3-340 {212    db eval {213      PRAGMA data_version;214      SELECT * FROM t3;215      SELECT * FROM t4;216    }217  } {2 abc def ghi 123 456 789}218  db2 close219  db close220  sqlite3_enable_shared_cache $::enable_shared_cache221}222 223# Make sure this also works in WAL mode224#225# This will not work with the in-memory journal permutation, as opening226# [db2] switches the journal mode back to "memory"227#228if {[wal_is_capable]} {229if {[permutation]!="inmemory_journal"} {230 231  sqlite3 db test.db232  db eval {PRAGMA journal_mode=WAL}233  sqlite3 db2 test.db234  do_test pragma3-400 {235    db eval {236      PRAGMA data_version;237      PRAGMA journal_mode;238      SELECT * FROM t1;239    }240  } {2 wal 101 201}241  do_test pragma3-410 {242    db2 eval {243      PRAGMA data_version;244      PRAGMA journal_mode;245      SELECT * FROM t1;246    }247  } {2 wal 101 201}248  do_test pragma3-420 {249    db eval {UPDATE t1 SET a=111*(a/100); PRAGMA data_version; SELECT * FROM t1}250  } {2 111 222}251  do_test pragma3-430 {252    db2 eval {PRAGMA data_version; SELECT * FROM t1;}253  } {3 111 222}254  db2 close255}256}257 258#-------------------------------------------------------------------------259# Check that empty write transactions do not cause the return of "PRAGMA260# data_version" to be decremented with journal_mode=PERSIST and261# locking_mode=EXCLUSIVE262#263foreach {tn sql} {264  A {265  }266  B {267    PRAGMA journal_mode = PERSIST;268    PRAGMA locking_mode = EXCLUSIVE;269  }270} {271  reset_db272  execsql $sql273 274  do_execsql_test pragma3-510$tn {275    CREATE TABLE t1(x, y);276    INSERT INTO t1 VALUES(1, 2);277    PRAGMA data_version;278  } {1}279 280  do_execsql_test pragma3-520$tn {281    BEGIN EXCLUSIVE;282    COMMIT;283    PRAGMA data_version;284  } {1}285}286 287finish_test288