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