3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file focuses on the sqlite3session_diff() function.
14 if {![info exists testdir]} {
15 set testdir [file join [file dirname [info script]] .. .. test]
17 source [file join [file dirname [info script]] session_common.tcl]
18 source $testdir/tester.tcl
19 ifcapable !session {finish_test; return}
21 set testprefix sessionD
23 proc scksum {db dbname} {
25 if {$dbname=="temp"} {
26 set master sqlite_temp_master
28 set master $dbname.sqlite_master
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"]
35 db eval "PRAGMA $dbname.table_info = $tab" x {
36 lappend cols "quote($x(name))"
38 set cols [join $cols ,]
39 append txt [db eval "SELECT $cols FROM $tab ORDER BY $cols"]
44 proc do_diff_test {tn setup} {
47 execsql { ATTACH 'test.db2' AS aux }
50 sqlite3session S db main
51 foreach tbl [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
60 sqlite3changeset_apply db2 $C ""
61 uplevel do_test $tn.1 [list {execsql { PRAGMA integrity_check } db2}] ok
64 set cksum [scksum db main]
65 uplevel do_test $tn.2 [list {scksum db aux}] [list $cksum]
68 # Ensure that the diff produced by comparing the current contents of [db]
69 # with itself is empty.
70 proc do_empty_diff_test {tn} {
72 forcecopy test.db test.db2
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'}] {
84 uplevel [list do_test $tn {string length $::C} 0]
90 CREATE TABLE t2(a PRIMARY KEY, b);
91 INSERT INTO t2 VALUES(1, 'one');
92 INSERT INTO t2 VALUES(2, 'two');
94 ATTACH 'test.db2' AS aux;
95 CREATE TABLE aux.t2(a PRIMARY KEY, b);
99 sqlite3session S db main
108 sqlite3changeset_apply db2 $C ""
110 db eval { SELECT * FROM aux.t2 }
114 CREATE TABLE aux.t1(x, y, PRIMARY KEY(y));
115 CREATE TABLE t1(x, y, PRIMARY KEY(y));
117 INSERT INTO t1 VALUES(1, 2);
118 INSERT INTO t1 VALUES(NULL, 'xyz');
119 INSERT INTO t1 VALUES(4.5, 5.5);
123 CREATE TABLE aux.t1(x, y, PRIMARY KEY(y));
124 CREATE TABLE t1(x, y, PRIMARY KEY(y));
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);
132 CREATE TABLE aux.t1(a PRIMARY KEY, b TEXT);
133 CREATE TABLE t1(a PRIMARY KEY, b TEXT);
135 INSERT INTO aux.t1 VALUES(1, 'one');
136 INSERT INTO aux.t1 VALUES(2, 'two');
137 INSERT INTO aux.t1 VALUES(3, 'three');
139 INSERT INTO t1 VALUES(1, 'I');
140 INSERT INTO t1 VALUES(2, 'two');
141 INSERT INTO t1 VALUES(3, 'III');
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));
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);
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);
156 INSERT INTO aux.t1 VALUES(415075.74,'auawczkb',X'',X'57B4FAAF2595');
157 INSERT INTO aux.t1 VALUES(727637,711560,-181340,'hphuo');
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);
165 VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidgtsplhjcbvbppdt',-642987.3);
169 do_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);
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);
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);
185 do_empty_diff_test 3.1
188 #-------------------------------------------------------------------------
189 # Test some error cases:
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.
197 do_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);
203 CREATE TABLE ixua.t2(a PRIMARY KEY, b, c);
204 CREATE TABLE main.t2(a PRIMARY KEY, b, x);
208 sqlite3session S db main
210 list [catch { S diff ixua t1 } msg] $msg
213 string length [S changeset]
218 sqlite3session S db main
220 list [catch { S diff ixua t2 } msg] $msg
221 } {1 {SQLITE_SCHEMA - table schemas do not match}}
225 sqlite3session S db main
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}}
231 do_catchsql_test 4.3.2 {
232 SELECT * FROM ixua.t4;
233 } {1 {no such table: ixua.t4}}
236 sqlite3session S db main
237 S attach sqlite_stat1
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}}
243 do_catchsql_test 4.4.2 {
244 SELECT * FROM ixua.sqlite_stat1;
245 } {1 {no such table: ixua.sqlite_stat1}}
248 sqlite3session S db main
250 list [catch { S diff ixua t8 } msg] $msg
253 do_catchsql_test 4.5.2 {
254 SELECT * FROM ixua.i8;
255 } {1 {no such table: ixua.i8}}