1# 2014 August 16 2# 3# The author disclaims copyright to this source code. In place of 4# 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 focuses on the sqlite3session_diff() function. 12# 13 14if {![info exists testdir]} { 15 set testdir [file join [file dirname [info script]] .. .. test] 16} 17source [file join [file dirname [info script]] session_common.tcl] 18source $testdir/tester.tcl 19ifcapable !session {finish_test; return} 20 21set testprefix sessionD 22 23proc scksum {db dbname} { 24 25 if {$dbname=="temp"} { 26 set master sqlite_temp_master 27 } else { 28 set master $dbname.sqlite_master 29 } 30 31 set alltab [$db eval "SELECT name FROM $master WHERE type='table'"] 32 set txt [$db eval "SELECT * FROM $master ORDER BY type,name,sql"] 33 foreach tab $alltab { 34 set cols [list] 35 db eval "PRAGMA $dbname.table_info = $tab" x { 36 lappend cols "quote($x(name))" 37 } 38 set cols [join $cols ,] 39 append txt [db eval "SELECT $cols FROM $tab ORDER BY $cols"] 40 } 41 return [md5 $txt] 42} 43 44proc do_diff_test {tn setup} { 45 reset_db 46 forcedelete test.db2 47 execsql { ATTACH 'test.db2' AS aux } 48 execsql $setup 49 50 sqlite3session S db main 51 foreach tbl [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 52 S attach $tbl 53 S diff aux $tbl 54 } 55 56 set C [S changeset] 57 S delete 58 59 sqlite3 db2 test.db2 60 sqlite3changeset_apply db2 $C "" 61 uplevel do_test $tn.1 [list {execsql { PRAGMA integrity_check } db2}] ok 62 db2 close 63 64 set cksum [scksum db main] 65 uplevel do_test $tn.2 [list {scksum db aux}] [list $cksum] 66} 67 68# Ensure that the diff produced by comparing the current contents of [db] 69# with itself is empty. 70proc do_empty_diff_test {tn} { 71 forcedelete test.db2 72 forcecopy test.db test.db2 73 74 execsql { ATTACH 'test.db2' AS aux } 75 sqlite3session S db main 76 foreach tbl [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 77 S attach $tbl 78 S diff aux $tbl 79 } 80 81 set ::C [S changeset] 82 S delete 83 84 uplevel [list do_test $tn {string length $::C} 0] 85} 86 87 88forcedelete test.db2 89do_execsql_test 1.0 { 90 CREATE TABLE t2(a PRIMARY KEY, b); 91 INSERT INTO t2 VALUES(1, 'one'); 92 INSERT INTO t2 VALUES(2, 'two'); 93 94 ATTACH 'test.db2' AS aux; 95 CREATE TABLE aux.t2(a PRIMARY KEY, b); 96} 97 98do_test 1.1 { 99 sqlite3session S db main 100 S attach t2 101 S diff aux t2 102 set C [S changeset] 103 S delete 104} {} 105 106do_test 1.2 { 107 sqlite3 db2 test.db2 108 sqlite3changeset_apply db2 $C "" 109 db2 close 110 db eval { SELECT * FROM aux.t2 } 111} {1 one 2 two} 112 113do_diff_test 2.1 { 114 CREATE TABLE aux.t1(x, y, PRIMARY KEY(y)); 115 CREATE TABLE t1(x, y, PRIMARY KEY(y)); 116 117 INSERT INTO t1 VALUES(1, 2); 118 INSERT INTO t1 VALUES(NULL, 'xyz'); 119 INSERT INTO t1 VALUES(4.5, 5.5); 120} 121 122do_diff_test 2.2 { 123 CREATE TABLE aux.t1(x, y, PRIMARY KEY(y)); 124 CREATE TABLE t1(x, y, PRIMARY KEY(y)); 125 126 INSERT INTO aux.t1 VALUES(1, 2); 127 INSERT INTO aux.t1 VALUES(NULL, 'xyz'); 128 INSERT INTO aux.t1 VALUES(4.5, 5.5); 129} 130 131do_diff_test 2.3 { 132 CREATE TABLE aux.t1(a PRIMARY KEY, b TEXT); 133 CREATE TABLE t1(a PRIMARY KEY, b TEXT); 134 135 INSERT INTO aux.t1 VALUES(1, 'one'); 136 INSERT INTO aux.t1 VALUES(2, 'two'); 137 INSERT INTO aux.t1 VALUES(3, 'three'); 138 139 INSERT INTO t1 VALUES(1, 'I'); 140 INSERT INTO t1 VALUES(2, 'two'); 141 INSERT INTO t1 VALUES(3, 'III'); 142} 143 144do_diff_test 2.4 { 145 CREATE TABLE aux.t1(a, b, c, d, PRIMARY KEY(c, b, a)); 146 CREATE TABLE t1(a, b, c, d, PRIMARY KEY(c, b, a)); 147 148 INSERT INTO t1 VALUES('hvkzyipambwdqlvwv','',-458331.50,X'DA51ED5E84'); 149 INSERT INTO t1 VALUES(X'C5C6B5DD','jjxrath',40917,830244); 150 INSERT INTO t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88,NULL); 151 INSERT INTO t1 152 VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidhjcbvbppdt',-642987.37); 153 INSERT INTO t1 VALUES(-851726,-161992,-469943,-159541); 154 INSERT INTO t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL); 155 156 INSERT INTO aux.t1 VALUES(415075.74,'auawczkb',X'',X'57B4FAAF2595'); 157 INSERT INTO aux.t1 VALUES(727637,711560,-181340,'hphuo'); 158 INSERT INTO aux.t1 159 VALUES(-921322.81,662959,'lvlgwdgxaurr','ajjrzrbhqflsutnymgc'); 160 INSERT INTO aux.t1 VALUES(-146061,-377892,X'4E','gepvpvvuhszpxabbb'); 161 INSERT INTO aux.t1 VALUES(-851726,-161992,-469943,-159541); 162 INSERT INTO aux.t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL); 163 INSERT INTO aux.t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88, 4); 164 INSERT INTO aux.t1 165 VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidgtsplhjcbvbppdt',-642987.3); 166} 167 168reset_db 169do_execsql_test 3.0 { 170 CREATE TABLE t1(a, b, c, PRIMARY KEY(a)); 171 INSERT INTO t1 VALUES(1, 2, 3); 172 INSERT INTO t1 VALUES(4, 5, 6); 173 INSERT INTO t1 VALUES(7, 8, 9); 174 175 CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)); 176 INSERT INTO t2 VALUES(1, 2, 3); 177 INSERT INTO t2 VALUES(4, 5, 6); 178 INSERT INTO t2 VALUES(7, 8, 9); 179 180 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b, c)); 181 INSERT INTO t3 VALUES(1, 2, 3); 182 INSERT INTO t3 VALUES(4, 5, 6); 183 INSERT INTO t3 VALUES(7, 8, 9); 184} 185do_empty_diff_test 3.1 186 187 188#------------------------------------------------------------------------- 189# Test some error cases: 190# 191# 1) schema mismatches between the two dbs, and 192# 2) tables with no primary keys. This is not actually an error, but 193# should not add any changes to the session object. 194# 195reset_db 196forcedelete test.db2 197do_execsql_test 4.0 { 198 ATTACH 'test.db2' AS ixua; 199 CREATE TABLE ixua.t1(a, b, c); 200 CREATE TABLE main.t1(a, b, c); 201 INSERT INTO main.t1 VALUES(1, 2, 3); 202 203 CREATE TABLE ixua.t2(a PRIMARY KEY, b, c); 204 CREATE TABLE main.t2(a PRIMARY KEY, b, x); 205} 206 207do_test 4.1.1 { 208 sqlite3session S db main 209 S attach t1 210 list [catch { S diff ixua t1 } msg] $msg 211} {0 {}} 212do_test 4.1.2 { 213 string length [S changeset] 214} {0} 215S delete 216 217do_test 4.2.2 { 218 sqlite3session S db main 219 S attach t2 220 list [catch { S diff ixua t2 } msg] $msg 221} {1 {SQLITE_SCHEMA - table schemas do not match}} 222S delete 223 224do_test 4.3.1 { 225 sqlite3session S db main 226 S attach t4 227 execsql { CREATE TABLE t4(i PRIMARY KEY, b) } 228 list [catch { S diff ixua t4 } msg] $msg 229} {1 {SQLITE_SCHEMA - table schemas do not match}} 230S delete 231do_catchsql_test 4.3.2 { 232 SELECT * FROM ixua.t4; 233} {1 {no such table: ixua.t4}} 234 235do_test 4.4.1 { 236 sqlite3session S db main 237 S attach sqlite_stat1 238 execsql { ANALYZE } 239 execsql { DROP TABLE ixua.sqlite_stat1 } 240 list [catch { S diff ixua sqlite_stat1 } msg] $msg 241} {1 {SQLITE_SCHEMA - table schemas do not match}} 242S delete 243do_catchsql_test 4.4.2 { 244 SELECT * FROM ixua.sqlite_stat1; 245} {1 {no such table: ixua.sqlite_stat1}} 246 247do_test 4.5.1 { 248 sqlite3session S db main 249 S attach t8 250 list [catch { S diff ixua t8 } msg] $msg 251} {0 {}} 252S delete 253do_catchsql_test 4.5.2 { 254 SELECT * FROM ixua.i8; 255} {1 {no such table: ixua.i8}} 256 257finish_test 258