000001  # 2014-12-19
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.
000012  #
000013  # This file implements tests for PRAGMA data_version command.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  
000019  if {[sqlite3 -has-codec]} {
000020    finish_test
000021    return
000022  }
000023  
000024  do_execsql_test pragma3-100 {
000025    PRAGMA data_version;
000026  } {1}
000027  do_execsql_test pragma3-101 {
000028    PRAGMA temp.data_version;
000029  } {1}
000030  
000031  # Writing to the pragma is a no-op 
000032  do_execsql_test pragma3-102 {
000033    PRAGMA main.data_version=1234;
000034    PRAGMA main.data_version;
000035  } {1 1}
000036  
000037  # EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides
000038  # an indication that the database file has been modified.
000039  #
000040  # EVIDENCE-OF: R-47505-58569 The "PRAGMA data_version" value is
000041  # unchanged for commits made on the same database connection.
000042  #
000043  do_execsql_test pragma3-110 {
000044    PRAGMA data_version;
000045    BEGIN IMMEDIATE;
000046    PRAGMA data_version;
000047    CREATE TABLE t1(a);
000048    INSERT INTO t1 VALUES(100),(200),(300);
000049    PRAGMA data_version;
000050    COMMIT;
000051    SELECT * FROM t1;
000052    PRAGMA data_version;
000053  } {1 1 1 100 200 300 1}
000054  
000055  sqlite3 db2 test.db
000056  do_test pragma3-120 {
000057    db2 eval {
000058      SELECT * FROM t1;
000059      PRAGMA data_version;
000060    }
000061  } {100 200 300 1}
000062  
000063  do_execsql_test pragma3-130 {
000064    PRAGMA data_version;
000065    BEGIN IMMEDIATE;
000066    PRAGMA data_version;
000067    INSERT INTO t1 VALUES(400),(500);
000068    PRAGMA data_version;
000069    COMMIT;
000070    SELECT * FROM t1;
000071    PRAGMA data_version;
000072    PRAGMA shrink_memory;
000073  } {1 1 1 100 200 300 400 500 1}
000074  
000075  # EVIDENCE-OF: R-63005-41812 The integer values returned by two
000076  # invocations of "PRAGMA data_version" from the same connection will be
000077  # different if changes were committed to the database by any other
000078  # connection in the interim.
000079  #
000080  # Value went from 1 in pragma3-120 to 2 here.
000081  #
000082  do_test pragma3-140 {
000083    db2 eval {
000084      SELECT * FROM t1;
000085      PRAGMA data_version;
000086      BEGIN IMMEDIATE;
000087      PRAGMA data_version;
000088      UPDATE t1 SET a=a+1;
000089      COMMIT;
000090      SELECT * FROM t1;
000091      PRAGMA data_version;
000092    }
000093  } {100 200 300 400 500 2 2 101 201 301 401 501 2}
000094  do_execsql_test pragma3-150 {
000095    SELECT * FROM t1;
000096    PRAGMA data_version;
000097  } {101 201 301 401 501 2}
000098  
000099  #
000100  do_test pragma3-160 {
000101    db eval {
000102      BEGIN;
000103      PRAGMA data_version;
000104      UPDATE t1 SET a=555 WHERE a=501;
000105      PRAGMA data_version;
000106      SELECT * FROM t1 ORDER BY a;
000107      PRAGMA data_version;
000108    }
000109  } {2 2 101 201 301 401 555 2}
000110  do_test pragma3-170 {
000111    db2 eval {
000112      PRAGMA data_version;
000113    }
000114  } {2}
000115  do_test pragma3-180 {
000116    db eval {
000117      COMMIT;
000118      PRAGMA data_version;
000119    }
000120  } {2}
000121  do_test pragma3-190 {
000122    db2 eval {
000123      PRAGMA data_version;
000124    }
000125  } {3}
000126  
000127  # EVIDENCE-OF: R-19326-44825 The "PRAGMA data_version" value is a local
000128  # property of each database connection and so values returned by two
000129  # concurrent invocations of "PRAGMA data_version" on separate database
000130  # connections are often different even though the underlying database is
000131  # identical.
000132  #
000133  do_test pragma3-195 {
000134    expr {[db eval {PRAGMA data_version}]!=[db2 eval {PRAGMA data_version}]}
000135  } {1}
000136  
000137  # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is
000138  # the same for all database connections, including database connections
000139  # in separate processes and shared cache database connections.
000140  #
000141  # The next block checks the behavior for separate processes.
000142  #
000143  do_test pragma3-200 {
000144    db eval {PRAGMA data_version; SELECT * FROM t1;}
000145  } {2 101 201 301 401 555}
000146  do_test pragma3-201 {
000147    set fd [open pragma3.txt wb]
000148    puts $fd {
000149       sqlite3 db test.db;
000150       db eval {DELETE FROM t1 WHERE a>300};
000151       db close;
000152       exit;
000153    }
000154    close $fd
000155    exec [info nameofexec] pragma3.txt
000156    forcedelete pragma3.txt
000157    db eval {
000158      PRAGMA data_version;
000159      SELECT * FROM t1;
000160    }
000161  } {3 101 201}
000162  db2 close
000163  db close
000164  
000165  # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is
000166  # the same for all database connections, including database connections
000167  # in separate processes and shared cache database connections.
000168  #
000169  # The next block checks that behavior is the same for shared-cache.
000170  #
000171  ifcapable shared_cache {
000172    set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
000173    sqlite3 db test.db
000174    sqlite3 db2 test.db
000175    do_test pragma3-300 {
000176      db eval {
000177        PRAGMA data_version;
000178        BEGIN;
000179        CREATE TABLE t3(a,b,c);
000180        CREATE TABLE t4(x,y,z);
000181        INSERT INTO t4 VALUES(123,456,789);
000182        PRAGMA data_version;
000183        COMMIT;
000184        PRAGMA data_version;
000185      }
000186    } {1 1 1}
000187    do_test pragma3-310 {
000188      db2 eval {
000189        PRAGMA data_version;
000190        BEGIN;
000191        INSERT INTO t3(a,b,c) VALUES('abc','def','ghi');
000192        SELECT * FROM t3;
000193        PRAGMA data_version;
000194      }
000195    } {2 abc def ghi 2}
000196    # The transaction in db2 has not yet committed, so the data_version in
000197    # db is unchanged.
000198    do_test pragma3-320 {
000199      db eval {
000200        PRAGMA data_version;
000201        SELECT * FROM t4;
000202      }
000203    } {1 123 456 789}
000204    do_test pragma3-330 {
000205      db2 eval {
000206        COMMIT;
000207        PRAGMA data_version;
000208        SELECT * FROM t4;
000209      }
000210    } {2 123 456 789}
000211    do_test pragma3-340 {
000212      db eval {
000213        PRAGMA data_version;
000214        SELECT * FROM t3;
000215        SELECT * FROM t4;
000216      }
000217    } {2 abc def ghi 123 456 789}
000218    db2 close
000219    db close
000220    sqlite3_enable_shared_cache $::enable_shared_cache
000221  }
000222  
000223  # Make sure this also works in WAL mode
000224  #
000225  # This will not work with the in-memory journal permutation, as opening
000226  # [db2] switches the journal mode back to "memory"
000227  #
000228  if {[wal_is_capable]} {
000229  if {[permutation]!="inmemory_journal"} {
000230  
000231    sqlite3 db test.db
000232    db eval {PRAGMA journal_mode=WAL}
000233    sqlite3 db2 test.db
000234    do_test pragma3-400 {
000235      db eval {
000236        PRAGMA data_version;
000237        PRAGMA journal_mode;
000238        SELECT * FROM t1;
000239      }
000240    } {2 wal 101 201}
000241    do_test pragma3-410 {
000242      db2 eval {
000243        PRAGMA data_version;
000244        PRAGMA journal_mode;
000245        SELECT * FROM t1;
000246      }
000247    } {2 wal 101 201}
000248    do_test pragma3-420 {
000249      db eval {UPDATE t1 SET a=111*(a/100); PRAGMA data_version; SELECT * FROM t1}
000250    } {2 111 222}
000251    do_test pragma3-430 {
000252      db2 eval {PRAGMA data_version; SELECT * FROM t1;}
000253    } {3 111 222}
000254    db2 close
000255  }
000256  }
000257  
000258  #-------------------------------------------------------------------------
000259  # Check that empty write transactions do not cause the return of "PRAGMA
000260  # data_version" to be decremented with journal_mode=PERSIST and
000261  # locking_mode=EXCLUSIVE
000262  #
000263  foreach {tn sql} {
000264    A {
000265    }
000266    B {
000267      PRAGMA journal_mode = PERSIST;
000268      PRAGMA locking_mode = EXCLUSIVE;
000269    }
000270  } {
000271    reset_db
000272    execsql $sql
000273  
000274    do_execsql_test pragma3-510$tn {
000275      CREATE TABLE t1(x, y);
000276      INSERT INTO t1 VALUES(1, 2);
000277      PRAGMA data_version;
000278    } {1}
000279  
000280    do_execsql_test pragma3-520$tn {
000281      BEGIN EXCLUSIVE;
000282      COMMIT;
000283      PRAGMA data_version;
000284    } {1}
000285  }
000286  
000287  finish_test