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 do_not_use_codec
000019
000020 do_execsql_test pragma3-100 {
000021 PRAGMA data_version;
000022 } {1}
000023 do_execsql_test pragma3-101 {
000024 PRAGMA temp.data_version;
000025 } {1}
000026
000027 # Writing to the pragma is a no-op
000028 do_execsql_test pragma3-102 {
000029 PRAGMA main.data_version=1234;
000030 PRAGMA main.data_version;
000031 } {1 1}
000032
000033 # EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides
000034 # an indication that the database file has been modified.
000035 #
000036 # EVIDENCE-OF: R-47505-58569 The "PRAGMA data_version" value is
000037 # unchanged for commits made on the same database connection.
000038 #
000039 do_execsql_test pragma3-110 {
000040 PRAGMA data_version;
000041 BEGIN IMMEDIATE;
000042 PRAGMA data_version;
000043 CREATE TABLE t1(a);
000044 INSERT INTO t1 VALUES(100),(200),(300);
000045 PRAGMA data_version;
000046 COMMIT;
000047 SELECT * FROM t1;
000048 PRAGMA data_version;
000049 } {1 1 1 100 200 300 1}
000050
000051 sqlite3 db2 test.db
000052 do_test pragma3-120 {
000053 db2 eval {
000054 SELECT * FROM t1;
000055 PRAGMA data_version;
000056 }
000057 } {100 200 300 1}
000058
000059 do_execsql_test pragma3-130 {
000060 PRAGMA data_version;
000061 BEGIN IMMEDIATE;
000062 PRAGMA data_version;
000063 INSERT INTO t1 VALUES(400),(500);
000064 PRAGMA data_version;
000065 COMMIT;
000066 SELECT * FROM t1;
000067 PRAGMA data_version;
000068 PRAGMA shrink_memory;
000069 } {1 1 1 100 200 300 400 500 1}
000070
000071 # EVIDENCE-OF: R-63005-41812 The integer values returned by two
000072 # invocations of "PRAGMA data_version" from the same connection will be
000073 # different if changes were committed to the database by any other
000074 # connection in the interim.
000075 #
000076 # Value went from 1 in pragma3-120 to 2 here.
000077 #
000078 do_test pragma3-140 {
000079 db2 eval {
000080 SELECT * FROM t1;
000081 PRAGMA data_version;
000082 BEGIN IMMEDIATE;
000083 PRAGMA data_version;
000084 UPDATE t1 SET a=a+1;
000085 COMMIT;
000086 SELECT * FROM t1;
000087 PRAGMA data_version;
000088 }
000089 } {100 200 300 400 500 2 2 101 201 301 401 501 2}
000090 do_execsql_test pragma3-150 {
000091 SELECT * FROM t1;
000092 PRAGMA data_version;
000093 } {101 201 301 401 501 2}
000094
000095 #
000096 do_test pragma3-160 {
000097 db eval {
000098 BEGIN;
000099 PRAGMA data_version;
000100 UPDATE t1 SET a=555 WHERE a=501;
000101 PRAGMA data_version;
000102 SELECT * FROM t1 ORDER BY a;
000103 PRAGMA data_version;
000104 }
000105 } {2 2 101 201 301 401 555 2}
000106 do_test pragma3-170 {
000107 db2 eval {
000108 PRAGMA data_version;
000109 }
000110 } {2}
000111 do_test pragma3-180 {
000112 db eval {
000113 COMMIT;
000114 PRAGMA data_version;
000115 }
000116 } {2}
000117 do_test pragma3-190 {
000118 db2 eval {
000119 PRAGMA data_version;
000120 }
000121 } {3}
000122
000123 # EVIDENCE-OF: R-19326-44825 The "PRAGMA data_version" value is a local
000124 # property of each database connection and so values returned by two
000125 # concurrent invocations of "PRAGMA data_version" on separate database
000126 # connections are often different even though the underlying database is
000127 # identical.
000128 #
000129 do_test pragma3-195 {
000130 expr {[db eval {PRAGMA data_version}]!=[db2 eval {PRAGMA data_version}]}
000131 } {1}
000132
000133 # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is
000134 # the same for all database connections, including database connections
000135 # in separate processes and shared cache database connections.
000136 #
000137 # The next block checks the behavior for separate processes.
000138 #
000139 do_test pragma3-200 {
000140 db eval {PRAGMA data_version; SELECT * FROM t1;}
000141 } {2 101 201 301 401 555}
000142 do_test pragma3-201 {
000143 set fd [open pragma3.txt wb]
000144 puts $fd {
000145 sqlite3 db test.db;
000146 db eval {DELETE FROM t1 WHERE a>300};
000147 db close;
000148 exit;
000149 }
000150 close $fd
000151 exec [info nameofexec] pragma3.txt
000152 forcedelete pragma3.txt
000153 db eval {
000154 PRAGMA data_version;
000155 SELECT * FROM t1;
000156 }
000157 } {3 101 201}
000158 db2 close
000159 db close
000160
000161 # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is
000162 # the same for all database connections, including database connections
000163 # in separate processes and shared cache database connections.
000164 #
000165 # The next block checks that behavior is the same for shared-cache.
000166 #
000167 ifcapable shared_cache {
000168 set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
000169 sqlite3 db test.db
000170 sqlite3 db2 test.db
000171 do_test pragma3-300 {
000172 db eval {
000173 PRAGMA data_version;
000174 BEGIN;
000175 CREATE TABLE t3(a,b,c);
000176 CREATE TABLE t4(x,y,z);
000177 INSERT INTO t4 VALUES(123,456,789);
000178 PRAGMA data_version;
000179 COMMIT;
000180 PRAGMA data_version;
000181 }
000182 } {1 1 1}
000183 do_test pragma3-310 {
000184 db2 eval {
000185 PRAGMA data_version;
000186 BEGIN;
000187 INSERT INTO t3(a,b,c) VALUES('abc','def','ghi');
000188 SELECT * FROM t3;
000189 PRAGMA data_version;
000190 }
000191 } {2 abc def ghi 2}
000192 # The transaction in db2 has not yet committed, so the data_version in
000193 # db is unchanged.
000194 do_test pragma3-320 {
000195 db eval {
000196 PRAGMA data_version;
000197 SELECT * FROM t4;
000198 }
000199 } {1 123 456 789}
000200 do_test pragma3-330 {
000201 db2 eval {
000202 COMMIT;
000203 PRAGMA data_version;
000204 SELECT * FROM t4;
000205 }
000206 } {2 123 456 789}
000207 do_test pragma3-340 {
000208 db eval {
000209 PRAGMA data_version;
000210 SELECT * FROM t3;
000211 SELECT * FROM t4;
000212 }
000213 } {2 abc def ghi 123 456 789}
000214 db2 close
000215 db close
000216 sqlite3_enable_shared_cache $::enable_shared_cache
000217 }
000218
000219 # Make sure this also works in WAL mode
000220 #
000221 # This will not work with the in-memory journal permutation, as opening
000222 # [db2] switches the journal mode back to "memory"
000223 #
000224 if {[wal_is_capable]} {
000225 if {[permutation]!="inmemory_journal"} {
000226
000227 sqlite3 db test.db
000228 db eval {PRAGMA journal_mode=WAL}
000229 sqlite3 db2 test.db
000230 do_test pragma3-400 {
000231 db eval {
000232 PRAGMA data_version;
000233 PRAGMA journal_mode;
000234 SELECT * FROM t1;
000235 }
000236 } {2 wal 101 201}
000237 do_test pragma3-410 {
000238 db2 eval {
000239 PRAGMA data_version;
000240 PRAGMA journal_mode;
000241 SELECT * FROM t1;
000242 }
000243 } {2 wal 101 201}
000244 do_test pragma3-420 {
000245 db eval {UPDATE t1 SET a=111*(a/100); PRAGMA data_version; SELECT * FROM t1}
000246 } {2 111 222}
000247 do_test pragma3-430 {
000248 db2 eval {PRAGMA data_version; SELECT * FROM t1;}
000249 } {3 111 222}
000250 db2 close
000251 }
000252 }
000253
000254 finish_test