Debian Codegraph @

ext/session/sessionD.test

raw
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
14 if {![info exists testdir]} {
15 set testdir [file join [file dirname [info script]] .. .. test]
16 }
17 source [file join [file dirname [info script]] session_common.tcl]
18 source $testdir/tester.tcl
19 ifcapable !session {finish_test; return}
20
21 set testprefix sessionD
22
23 proc 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
44 # Ensure that the diff produced by comparing the current contents of [db]
45 # with itself is empty.
46 proc do_empty_diff_test {tn} {
47 forcedelete test.db2
48 forcecopy test.db test.db2
49
50 execsql { ATTACH 'test.db2' AS aux }
51 sqlite3session S db main
52 foreach tbl [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
53 S attach $tbl
54 S diff aux $tbl
55 }
56
57 set ::C [S changeset]
58 S delete
59
60 uplevel [list do_test $tn {string length $::C} 0]
61 }
62
63
64 forcedelete test.db2
65 do_execsql_test 1.0 {
66 CREATE TABLE t2(a PRIMARY KEY, b);
67 INSERT INTO t2 VALUES(1, 'one');
68 INSERT INTO t2 VALUES(2, 'two');
69
70 ATTACH 'test.db2' AS aux;
71 CREATE TABLE aux.t2(a PRIMARY KEY, b);
72 }
73
74 do_test 1.1 {
75 sqlite3session S db main
76 S attach t2
77 S diff aux t2
78 set C [S changeset]
79 S delete
80 } {}
81
82 do_test 1.2 {
83 sqlite3 db2 test.db2
84 sqlite3changeset_apply db2 $C ""
85 db2 close
86 db eval { SELECT * FROM aux.t2 }
87 } {1 one 2 two}
88
89 do_diff_test 2.1 {
90 CREATE TABLE aux.t1(x, y, PRIMARY KEY(y));
91 CREATE TABLE t1(x, y, PRIMARY KEY(y));
92
93 INSERT INTO t1 VALUES(1, 2);
94 INSERT INTO t1 VALUES(NULL, 'xyz');
95 INSERT INTO t1 VALUES(4.5, 5.5);
96 }
97
98 do_diff_test 2.2 {
99 CREATE TABLE aux.t1(x, y, PRIMARY KEY(y));
100 CREATE TABLE t1(x, y, PRIMARY KEY(y));
101
102 INSERT INTO aux.t1 VALUES(1, 2);
103 INSERT INTO aux.t1 VALUES(NULL, 'xyz');
104 INSERT INTO aux.t1 VALUES(4.5, 5.5);
105 }
106
107 do_diff_test 2.3 {
108 CREATE TABLE aux.t1(a PRIMARY KEY, b TEXT);
109 CREATE TABLE t1(a PRIMARY KEY, b TEXT);
110
111 INSERT INTO aux.t1 VALUES(1, 'one');
112 INSERT INTO aux.t1 VALUES(2, 'two');
113 INSERT INTO aux.t1 VALUES(3, 'three');
114
115 INSERT INTO t1 VALUES(1, 'I');
116 INSERT INTO t1 VALUES(2, 'two');
117 INSERT INTO t1 VALUES(3, 'III');
118 }
119
120 do_diff_test 2.4 {
121 CREATE TABLE aux.t1(a, b, c, d, PRIMARY KEY(c, b, a));
122 CREATE TABLE t1(a, b, c, d, PRIMARY KEY(c, b, a));
123
124 INSERT INTO t1 VALUES('hvkzyipambwdqlvwv','',-458331.50,X'DA51ED5E84');
125 INSERT INTO t1 VALUES(X'C5C6B5DD','jjxrath',40917,830244);
126 INSERT INTO t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88,NULL);
127 INSERT INTO t1
128 VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidhjcbvbppdt',-642987.37);
129 INSERT INTO t1 VALUES(-851726,-161992,-469943,-159541);
130 INSERT INTO t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL);
131
132 INSERT INTO aux.t1 VALUES(415075.74,'auawczkb',X'',X'57B4FAAF2595');
133 INSERT INTO aux.t1 VALUES(727637,711560,-181340,'hphuo');
134 INSERT INTO aux.t1
135 VALUES(-921322.81,662959,'lvlgwdgxaurr','ajjrzrbhqflsutnymgc');
136 INSERT INTO aux.t1 VALUES(-146061,-377892,X'4E','gepvpvvuhszpxabbb');
137 INSERT INTO aux.t1 VALUES(-851726,-161992,-469943,-159541);
138 INSERT INTO aux.t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL);
139 INSERT INTO aux.t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88, 4);
140 INSERT INTO aux.t1
141 VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidgtsplhjcbvbppdt',-642987.3);
142 }
143
144 reset_db
145 do_execsql_test 3.0 {
146 CREATE TABLE t1(a, b, c, PRIMARY KEY(a));
147 INSERT INTO t1 VALUES(1, 2, 3);
148 INSERT INTO t1 VALUES(4, 5, 6);
149 INSERT INTO t1 VALUES(7, 8, 9);
150
151 CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b));
152 INSERT INTO t2 VALUES(1, 2, 3);
153 INSERT INTO t2 VALUES(4, 5, 6);
154 INSERT INTO t2 VALUES(7, 8, 9);
155
156 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b, c));
157 INSERT INTO t3 VALUES(1, 2, 3);
158 INSERT INTO t3 VALUES(4, 5, 6);
159 INSERT INTO t3 VALUES(7, 8, 9);
160 }
161 do_empty_diff_test 3.1
162
163
164 #-------------------------------------------------------------------------
165 # Test some error cases:
166 #
167 # 1) schema mismatches between the two dbs, and
168 # 2) tables with no primary keys. This is not actually an error, but
169 # should not add any changes to the session object.
170 #
171 reset_db
172 forcedelete test.db2
173 do_execsql_test 4.0 {
174 ATTACH 'test.db2' AS ixua;
175 CREATE TABLE ixua.t1(a, b, c);
176 CREATE TABLE main.t1(a, b, c);
177 INSERT INTO main.t1 VALUES(1, 2, 3);
178
179 CREATE TABLE ixua.t2(a PRIMARY KEY, b, c);
180 CREATE TABLE main.t2(a PRIMARY KEY, b, x);
181 }
182
183 do_test 4.1.1 {
184 sqlite3session S db main
185 S attach t1
186 list [catch { S diff ixua t1 } msg] $msg
187 } {0 {}}
188 do_test 4.1.2 {
189 string length [S changeset]
190 } {0}
191 S delete
192
193 do_test 4.2.2 {
194 sqlite3session S db main
195 S attach t2
196 list [catch { S diff ixua t2 } msg] $msg
197 } {1 {SQLITE_SCHEMA - table schemas do not match}}
198 S delete
199
200 do_test 4.3.1 {
201 sqlite3session S db main
202 S attach t4
203 execsql { CREATE TABLE t4(i PRIMARY KEY, b) }
204 list [catch { S diff ixua t4 } msg] $msg
205 } {1 {SQLITE_SCHEMA - no such table: ixua.t4}}
206 S delete
207 do_catchsql_test 4.3.2 {
208 SELECT * FROM ixua.t4;
209 } {1 {no such table: ixua.t4}}
210
211 do_test 4.4.1 {
212 sqlite3session S db main
213 S attach sqlite_stat1
214 execsql { ANALYZE }
215 execsql { DROP TABLE ixua.sqlite_stat1 }
216 list [catch { S diff ixua sqlite_stat1 } msg] $msg
217 } {1 {SQLITE_SCHEMA - no such table: ixua.sqlite_stat1}}
218 S delete
219 do_catchsql_test 4.4.2 {
220 SELECT * FROM ixua.sqlite_stat1;
221 } {1 {no such table: ixua.sqlite_stat1}}
222
223 do_test 4.5.1 {
224 sqlite3session S db main
225 S attach t8
226 list [catch { S diff ixua t8 } msg] $msg
227 } {0 {}}
228 S delete
229 do_catchsql_test 4.5.2 {
230 SELECT * FROM ixua.i8;
231 } {1 {no such table: ixua.i8}}
232
233 #-------------------------------------------------------------------------
234 # Test that sqlite3session_diff() really does automatically attach tables,
235 # as documented.
236 #
237 reset_db
238 forcedelete test.db2
239 do_execsql_test 4.0 {
240 ATTACH 'test.db2' AS two;
241 CREATE TABLE two.t1(a PRIMARY KEY, b, c);
242 CREATE TABLE t1(a PRIMARY KEY, b, c);
243
244 INSERT INTO two.t1 VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);
245 }
246
247 do_test 4.1 {
248 sqlite3session S db main
249 S attach t8
250 S diff two t1
251 } {}
252
253 do_changeset_test 4.2 S {
254 {DELETE t1 0 X.. {i 1 i 2 i 3} {}}
255 {DELETE t1 0 X.. {i 4 i 5 i 6} {}}
256 {DELETE t1 0 X.. {i 7 i 8 i 9} {}}
257 }
258
259 S delete
260
261 #-------------------------------------------------------------------------
262 # Test that sqlite3session_diff() really does return errors if the named
263 # table or database do not exist.
264 #
265 reset_db
266 forcedelete test.db2
267 do_execsql_test 5.0 {
268 ATTACH 'test.db2' AS two;
269 CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b);
270 CREATE TABLE main.t2(a INTEGER PRIMARY KEY, b);
271 CREATE TABLE two.t1(a, b INTEGER PRIMARY KEY);
272 }
273
274 proc do_sessions_diff_error {tn db tbl err} {
275 sqlite3session S db main
276 set rc [catch {S diff $db $tbl} msg]
277
278 set ::sdgot [list $rc $msg]
279 do_test $tn [list set sdgot] [list {*}$err]
280
281 S delete
282 }
283
284 # Test that it is an error if the named db is missing.
285 do_sessions_diff_error 5.1 nosuchdb t1 {
286 1 {SQLITE_SCHEMA - no such table: nosuchdb.t1}
287 }
288
289 # Test that it is an error if the named db is present, but named table is not.
290 do_sessions_diff_error 5.2 two t2 {
291 1 {SQLITE_SCHEMA - no such table: two.t2}
292 }
293
294 # Test that it is an error if the tables are present, but schemas do not match.
295 do_sessions_diff_error 5.3 two t1 {
296 1 {SQLITE_SCHEMA - table schemas do not match}
297 }
298
299 do_test 5.4 {
300 sqlite3session S db main
301
302 catch {S diff two blue}
303 catch {S diff two blue}
304
305 S delete
306 } {}
307
308 finish_test