Debian Codegraph @

ext/session/session1.test

raw
1 # 2011 March 07
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 implements regression tests for SQLite library.
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 session1
22
23 # Run all tests in this file twice. Once with "WITHOUT ROWID", and once
24 # with regular rowid tables.
25 #
26 foreach {tn trailing} {
27 1 ""
28 2 " WITHOUT ROWID "
29 } {
30 eval [string map [list %WR% $trailing] {
31
32 db close
33 forcedelete test.db test.db2
34 reset_db
35
36 do_execsql_test $tn.1.0 {
37 CREATE TABLE t1(x PRIMARY KEY, y) %WR%;
38 INSERT INTO t1 VALUES('abc', 'def');
39 }
40
41 #-------------------------------------------------------------------------
42 # Test creating, attaching tables to and deleting session objects.
43 #
44 do_test $tn.1.1 { sqlite3session S db main } {S}
45 do_test $tn.1.2 { S delete } {}
46 do_test $tn.1.3 { sqlite3session S db main } {S}
47 do_test $tn.1.4 { S attach t1 } {}
48 do_test $tn.1.5 { S delete } {}
49 do_test $tn.1.6 { sqlite3session S db main } {S}
50 do_test $tn.1.7 { S attach t1 ; S attach t2 ; S attach t3 } {}
51 do_test $tn.1.8 { S attach t1 ; S attach t2 ; S attach t3 } {}
52 do_test $tn.1.9 { S delete } {}
53 do_test $tn.1.10 {
54 sqlite3session S db main
55 S attach t1
56 execsql { INSERT INTO t1 VALUES('ghi', 'jkl') }
57 } {}
58 do_test $tn.1.11 { S delete } {}
59 if {$tn==1} {
60 do_test $tn.1.12 {
61 sqlite3session S db main
62 S attach t1
63 execsql { INSERT INTO t1 VALUES('mno', 'pqr') }
64 execsql { UPDATE t1 SET x = 111 WHERE rowid = 1 }
65 execsql { DELETE FROM t1 WHERE rowid = 2 }
66 } {}
67 do_test $tn.1.13 {
68 S changeset
69 S delete
70 } {}
71 }
72
73 #-------------------------------------------------------------------------
74 # Simple changeset tests. Also test the sqlite3changeset_invert()
75 # function.
76 #
77 do_test $tn.2.1.1 {
78 execsql { DELETE FROM t1 }
79 sqlite3session S db main
80 S attach t1
81 execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') }
82 execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') }
83 execsql { INSERT INTO t1 VALUES(3, 'Thonburi') }
84 } {}
85 do_changeset_test $tn.2.1.2 S {
86 {INSERT t1 0 X. {} {i 1 t Sukhothai}}
87 {INSERT t1 0 X. {} {i 2 t Ayutthaya}}
88 {INSERT t1 0 X. {} {i 3 t Thonburi}}
89 }
90 do_changeset_invert_test $tn.2.1.3 S {
91 {DELETE t1 0 X. {i 1 t Sukhothai} {}}
92 {DELETE t1 0 X. {i 2 t Ayutthaya} {}}
93 {DELETE t1 0 X. {i 3 t Thonburi} {}}
94 }
95 do_test $tn.2.1.4 { S delete } {}
96
97 do_test $tn.2.2.1 {
98 sqlite3session S db main
99 S attach t1
100 execsql { DELETE FROM t1 WHERE 1 }
101 } {}
102 do_changeset_test $tn.2.2.2 S {
103 {DELETE t1 0 X. {i 1 t Sukhothai} {}}
104 {DELETE t1 0 X. {i 2 t Ayutthaya} {}}
105 {DELETE t1 0 X. {i 3 t Thonburi} {}}
106 }
107 do_changeset_invert_test $tn.2.2.3 S {
108 {INSERT t1 0 X. {} {i 1 t Sukhothai}}
109 {INSERT t1 0 X. {} {i 2 t Ayutthaya}}
110 {INSERT t1 0 X. {} {i 3 t Thonburi}}
111 }
112 do_test $tn.2.2.4 { S delete } {}
113
114 do_test $tn.2.3.1 {
115 execsql { DELETE FROM t1 }
116 sqlite3session S db main
117 execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') }
118 execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') }
119 execsql { INSERT INTO t1 VALUES(3, 'Thonburi') }
120 S attach t1
121 execsql {
122 UPDATE t1 SET x = 10 WHERE x = 1;
123 UPDATE t1 SET y = 'Surin' WHERE x = 2;
124 UPDATE t1 SET x = 20, y = 'Thapae' WHERE x = 3;
125 }
126 } {}
127
128 do_changeset_test $tn.2.3.2 S {
129 {INSERT t1 0 X. {} {i 10 t Sukhothai}}
130 {DELETE t1 0 X. {i 1 t Sukhothai} {}}
131 {UPDATE t1 0 X. {i 2 t Ayutthaya} {{} {} t Surin}}
132 {DELETE t1 0 X. {i 3 t Thonburi} {}}
133 {INSERT t1 0 X. {} {i 20 t Thapae}}
134 }
135
136 do_changeset_invert_test $tn.2.3.3 S {
137 {DELETE t1 0 X. {i 10 t Sukhothai} {}}
138 {INSERT t1 0 X. {} {i 1 t Sukhothai}}
139 {UPDATE t1 0 X. {i 2 t Surin} {{} {} t Ayutthaya}}
140 {INSERT t1 0 X. {} {i 3 t Thonburi}}
141 {DELETE t1 0 X. {i 20 t Thapae} {}}
142 }
143 do_test $tn.2.3.4 { S delete } {}
144
145 do_test $tn.2.4.1 {
146 sqlite3session S db main
147 S attach t1
148 execsql { INSERT INTO t1 VALUES(100, 'Bangkok') }
149 execsql { DELETE FROM t1 WHERE x = 100 }
150 } {}
151 do_changeset_test $tn.2.4.2 S {}
152 do_changeset_invert_test $tn.2.4.3 S {}
153 do_test $tn.2.4.4 { S delete } {}
154
155 do_execsql_test $tn.2.5.0 {
156 SELECT * FROM t1 ORDER BY x
157 } {
158 2 Surin
159 10 Sukhothai
160 20 Thapae
161 }
162
163 do_test $tn.2.5.1 {
164 sqlite3session S db main
165 S attach t1
166 execsql { DELETE FROM t1 }
167 } {}
168 do_changeset_test $tn.2.5.2 S {
169 {DELETE t1 0 X. {i 10 t Sukhothai} {}}
170 {DELETE t1 0 X. {i 2 t Surin} {}}
171 {DELETE t1 0 X. {i 20 t Thapae} {}}
172 }
173 do_test $tn.2.5.3 { S delete } {}
174
175 #-------------------------------------------------------------------------
176 # Test the application of simple changesets. These tests also test that
177 # the conflict callback is invoked correctly. For these tests, the
178 # conflict callback always returns OMIT.
179 #
180 db close
181 forcedelete test.db test.db2
182 sqlite3 db test.db
183 sqlite3 db2 test.db2
184
185 proc xConflict {args} {
186 lappend ::xConflict $args
187 return ""
188 }
189
190 proc bgerror {args} { set ::background_error $args }
191
192 proc do_conflict_test {tn args} {
193 set O(-tables) [list]
194 set O(-sql) [list]
195 set O(-conflicts) [list]
196
197 array set V $args
198 foreach key [array names V] {
199 if {![info exists O($key)]} {error "no such option: $key"}
200 }
201 array set O $args
202
203 sqlite3session S db main
204 foreach t $O(-tables) { S attach $t }
205 execsql $O(-sql)
206 set ::xConflict [list]
207 catch {
208 sqlite3changeset_apply db2 [S changeset] xConflict
209 }
210
211 set conflicts [list]
212 foreach c $O(-conflicts) {
213 lappend conflicts $c
214 }
215
216 after 1 {set go 1}
217 vwait go
218
219 uplevel do_test $tn [list { set ::xConflict }] [list $conflicts]
220 S delete
221 }
222
223 proc do_db2_test {testname sql {result {}}} {
224 uplevel do_test $testname [list "execsql {$sql} db2"] [list [list {*}$result]]
225 }
226
227 # Test INSERT changesets.
228 #
229 do_test $tn.3.1.0 {
230 execsql { CREATE TABLE t1(a PRIMARY KEY, b NOT NULL) %WR% } db2
231 execsql {
232 CREATE TABLE t1(a PRIMARY KEY, b) %WR%;
233 INSERT INTO t1 VALUES(1, 'one');
234 INSERT INTO t1 VALUES(2, 'two');
235 } db
236 } {}
237 do_db2_test $tn.3.1.1 "INSERT INTO t1 VALUES(6, 'VI')"
238 do_conflict_test $tn.3.1.2 -tables t1 -sql {
239 INSERT INTO t1 VALUES(3, 'three');
240 INSERT INTO t1 VALUES(4, 'four');
241 INSERT INTO t1 VALUES(5, 'five');
242 INSERT INTO t1 VALUES(6, 'six');
243 INSERT INTO t1 VALUES(7, 'seven');
244 INSERT INTO t1 VALUES(8, NULL);
245 } -conflicts {
246 {INSERT t1 CONFLICT {i 6 t six} {i 6 t VI}}
247 {INSERT t1 CONSTRAINT {i 8 n {}}}
248 }
249
250 do_db2_test $tn.3.1.3 "SELECT * FROM t1 ORDER BY a" {
251 3 three 4 four 5 five 6 VI 7 seven
252 }
253 do_execsql_test $tn.3.1.4 "SELECT * FROM t1" {
254 1 one 2 two 3 three 4 four 5 five 6 six 7 seven 8 {}
255 }
256
257 # Test DELETE changesets.
258 #
259 do_execsql_test $tn.3.2.1 {
260 PRAGMA foreign_keys = on;
261 CREATE TABLE t2(a PRIMARY KEY, b)%WR%;
262 CREATE TABLE t3(c, d REFERENCES t2);
263 INSERT INTO t2 VALUES(1, 'one');
264 INSERT INTO t2 VALUES(2, 'two');
265 INSERT INTO t2 VALUES(3, 'three');
266 INSERT INTO t2 VALUES(4, 'four');
267 }
268 do_db2_test $tn.3.2.2 {
269 PRAGMA foreign_keys = on;
270 CREATE TABLE t2(a PRIMARY KEY, b)%WR%;
271 CREATE TABLE t3(c, d REFERENCES t2);
272 INSERT INTO t2 VALUES(1, 'one');
273 INSERT INTO t2 VALUES(2, 'two');
274 INSERT INTO t2 VALUES(4, 'five');
275 INSERT INTO t3 VALUES('i', 1);
276 }
277 do_conflict_test $tn.3.2.3 -tables t2 -sql {
278 DELETE FROM t2 WHERE a = 1;
279 DELETE FROM t2 WHERE a = 2;
280 DELETE FROM t2 WHERE a = 3;
281 DELETE FROM t2 WHERE a = 4;
282 } -conflicts {
283 {DELETE t2 NOTFOUND {i 3 t three}}
284 {DELETE t2 DATA {i 4 t four} {i 4 t five}}
285 {FOREIGN_KEY 1}
286 }
287 do_execsql_test $tn.3.2.4 "SELECT * FROM t2" {}
288 do_db2_test $tn.3.2.5 "SELECT * FROM t2" {4 five}
289
290 # Test UPDATE changesets.
291 #
292 do_execsql_test $tn.3.3.1 {
293 CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c))%WR%;
294 INSERT INTO t4 VALUES(1, 2, 3);
295 INSERT INTO t4 VALUES(4, 5, 6);
296 INSERT INTO t4 VALUES(7, 8, 9);
297 INSERT INTO t4 VALUES(10, 11, 12);
298 }
299 do_db2_test $tn.3.3.2 {
300 CREATE TABLE t4(a NOT NULL, b, c, PRIMARY KEY(b, c))%WR%;
301 INSERT INTO t4 VALUES(0, 2, 3);
302 INSERT INTO t4 VALUES(4, 5, 7);
303 INSERT INTO t4 VALUES(7, 8, 9);
304 INSERT INTO t4 VALUES(10, 11, 12);
305 }
306 do_conflict_test $tn.3.3.3 -tables t4 -sql {
307 UPDATE t4 SET a = -1 WHERE b = 2;
308 UPDATE t4 SET a = -1 WHERE b = 5;
309 UPDATE t4 SET a = NULL WHERE c = 9;
310 UPDATE t4 SET a = 'x' WHERE b = 11;
311 } -conflicts {
312 {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 {} {} {} {}} {i 0 i 2 i 3}}
313 {UPDATE t4 NOTFOUND {i 4 i 5 i 6} {i -1 {} {} {} {}}}
314 {UPDATE t4 CONSTRAINT {i 7 i 8 i 9} {n {} {} {} {} {}}}
315 }
316 do_db2_test $tn.3.3.4 { SELECT * FROM t4 } {0 2 3 4 5 7 7 8 9 x 11 12}
317 do_execsql_test $tn.3.3.5 { SELECT * FROM t4 } {-1 2 3 -1 5 6 {} 8 9 x 11 12}
318
319 #-------------------------------------------------------------------------
320 # This next block of tests verifies that values returned by the conflict
321 # handler are intepreted correctly.
322 #
323
324 proc test_reset {} {
325 db close
326 db2 close
327 forcedelete test.db test.db2
328 sqlite3 db test.db
329 sqlite3 db2 test.db2
330 }
331
332 proc xConflict {args} {
333 lappend ::xConflict $args
334 return $::conflict_return
335 }
336
337 foreach {tn2 conflict_return after} {
338 1 OMIT {1 2 value1 4 5 7 10 x x}
339 2 REPLACE {1 2 value1 4 5 value2 10 8 9}
340 } {
341 test_reset
342
343 do_test $tn.4.$tn2.1 {
344 foreach db {db db2} {
345 execsql {
346 CREATE TABLE t1(a, b, c, PRIMARY KEY(a))%WR%;
347 INSERT INTO t1 VALUES(1, 2, 3);
348 INSERT INTO t1 VALUES(4, 5, 6);
349 INSERT INTO t1 VALUES(7, 8, 9);
350 } $db
351 }
352 execsql {
353 REPLACE INTO t1 VALUES(4, 5, 7);
354 REPLACE INTO t1 VALUES(10, 'x', 'x');
355 } db2
356 } {}
357
358 do_conflict_test $tn.4.$tn2.2 -tables t1 -sql {
359 UPDATE t1 SET c = 'value1' WHERE a = 1; -- no conflict
360 UPDATE t1 SET c = 'value2' WHERE a = 4; -- DATA conflict
361 UPDATE t1 SET a = 10 WHERE a = 7; -- CONFLICT conflict
362 } -conflicts {
363 {INSERT t1 CONFLICT {i 10 i 8 i 9} {i 10 t x t x}}
364 {UPDATE t1 DATA {i 4 {} {} i 6} {{} {} {} {} t value2} {i 4 i 5 i 7}}
365 }
366
367 do_db2_test $tn.4.$tn2.3 "SELECT * FROM t1 ORDER BY a" $after
368 }
369
370 foreach {tn2 conflict_return} {
371 1 OMIT
372 2 REPLACE
373 } {
374 test_reset
375
376 do_test $tn.5.$tn2.1 {
377 # Create an identical schema in both databases.
378 set schema {
379 CREATE TABLE "'foolish name'"(x, y, z, PRIMARY KEY(x, y))%WR%;
380 }
381 execsql $schema db
382 execsql $schema db2
383
384 # Add some rows to [db2]. These rows will cause conflicts later
385 # on when the changeset from [db] is applied to it.
386 execsql {
387 INSERT INTO "'foolish name'" VALUES('one', 'one', 'ii');
388 INSERT INTO "'foolish name'" VALUES('one', 'two', 'i');
389 INSERT INTO "'foolish name'" VALUES('two', 'two', 'ii');
390 } db2
391
392 } {}
393
394 do_conflict_test $tn.5.$tn2.2 -tables {{'foolish name'}} -sql {
395 INSERT INTO "'foolish name'" VALUES('one', 'two', 2);
396 } -conflicts {
397 {INSERT {'foolish name'} CONFLICT {t one t two i 2} {t one t two t i}}
398 }
399
400 set res(REPLACE) {one one ii one two 2 two two ii}
401 set res(OMIT) {one one ii one two i two two ii}
402 do_db2_test $tn.5.$tn2.3 {
403 SELECT * FROM "'foolish name'" ORDER BY x, y
404 } $res($conflict_return)
405
406
407 do_test $tn.5.$tn2.1 {
408 set schema {
409 CREATE TABLE d1("z""z" PRIMARY KEY, y)%WR%;
410 INSERT INTO d1 VALUES(1, 'one');
411 INSERT INTO d1 VALUES(2, 'two');
412 }
413 execsql $schema db
414 execsql $schema db2
415
416 execsql {
417 UPDATE d1 SET y = 'TWO' WHERE "z""z" = 2;
418 } db2
419
420 } {}
421
422 do_conflict_test $tn.5.$tn2.2 -tables d1 -sql {
423 DELETE FROM d1 WHERE "z""z" = 2;
424 } -conflicts {
425 {DELETE d1 DATA {i 2 t two} {i 2 t TWO}}
426 }
427
428 set res(REPLACE) {1 one}
429 set res(OMIT) {1 one 2 TWO}
430 do_db2_test $tn.5.$tn2.3 "SELECT * FROM d1" $res($conflict_return)
431 }
432
433 #-------------------------------------------------------------------------
434 # Test that two tables can be monitored by a single session object.
435 #
436 test_reset
437 set schema {
438 CREATE TABLE t1(a COLLATE nocase PRIMARY KEY, b)%WR%;
439 CREATE TABLE t2(a, b PRIMARY KEY)%WR%;
440 }
441 do_test $tn.6.0 {
442 execsql $schema db
443 execsql $schema db2
444 execsql {
445 INSERT INTO t1 VALUES('a', 'b');
446 INSERT INTO t2 VALUES('a', 'b');
447 } db2
448 } {}
449
450 set conflict_return ""
451 do_conflict_test $tn.6.1 -tables {t1 t2} -sql {
452 INSERT INTO t1 VALUES('1', '2');
453 INSERT INTO t1 VALUES('A', 'B');
454 INSERT INTO t2 VALUES('A', 'B');
455 } -conflicts {
456 {INSERT t1 CONFLICT {t A t B} {t a t b}}
457 }
458
459 do_db2_test $tn.6.2 "SELECT * FROM t1 ORDER BY a" {1 2 a b}
460 do_db2_test $tn.6.3 "SELECT * FROM t2 ORDER BY a" {A B a b}
461
462 #-------------------------------------------------------------------------
463 # Test that session objects are not confused by changes to table in
464 # other databases.
465 #
466 catch { db2 close }
467 drop_all_tables
468 forcedelete test.db2
469 do_iterator_test $tn.7.1 * {
470 ATTACH 'test.db2' AS aux;
471 CREATE TABLE main.t1(x PRIMARY KEY, y)%WR%;
472 CREATE TABLE aux.t1(x PRIMARY KEY, y)%WR%;
473
474 INSERT INTO main.t1 VALUES('one', 1);
475 INSERT INTO main.t1 VALUES('two', 2);
476 INSERT INTO aux.t1 VALUES('three', 3);
477 INSERT INTO aux.t1 VALUES('four', 4);
478 } {
479 {INSERT t1 0 X. {} {t two i 2}}
480 {INSERT t1 0 X. {} {t one i 1}}
481 }
482
483 #-------------------------------------------------------------------------
484 # Test the sqlite3session_isempty() function.
485 #
486 do_test $tn.8.1 {
487 execsql {
488 CREATE TABLE t5(x PRIMARY KEY, y)%WR%;
489 CREATE TABLE t6(x PRIMARY KEY, y)%WR%;
490 INSERT INTO t5 VALUES('a', 'b');
491 INSERT INTO t6 VALUES('a', 'b');
492 }
493 sqlite3session S db main
494 S attach *
495
496 S isempty
497 } {1}
498 do_test $tn.8.2 {
499 execsql { DELETE FROM t5 }
500 S isempty
501 } {0}
502 do_test $tn.8.3 {
503 S delete
504 sqlite3session S db main
505 S attach t5
506 execsql { DELETE FROM t5 }
507 S isempty
508 } {1}
509 do_test $tn.8.4 { S delete } {}
510
511 do_test $tn.8.5 {
512 sqlite3session S db main
513 S attach t5
514 S attach t6
515 execsql { INSERT INTO t5 VALUES(1, 2) }
516 S isempty
517 } {0}
518
519 do_test $tn.8.6 {
520 S delete
521 sqlite3session S db main
522 S attach t5
523 S attach t6
524 execsql { INSERT INTO t6 VALUES(1, 2) }
525 S isempty
526 } {0}
527 do_test $tn.8.7 { S delete } {}
528
529 #-------------------------------------------------------------------------
530 #
531 do_execsql_test $tn.9.1 {
532 CREATE TABLE t7(a, b, c, d, e PRIMARY KEY, f, g)%WR%;
533 INSERT INTO t7 VALUES(1, 1, 1, 1, 1, 1, 1);
534 }
535 do_test $tn.9.2 {
536 sqlite3session S db main
537 S attach *
538 execsql { UPDATE t7 SET b=2, d=2 }
539 } {}
540 do_changeset_test $tn.9.2 S {{UPDATE t7 0 ....X.. {{} {} i 1 {} {} i 1 i 1 {} {} {} {}} {{} {} i 2 {} {} i 2 {} {} {} {} {} {}}}}
541 S delete
542 catch { db2 close }
543
544 #-------------------------------------------------------------------------
545 # Test a really long table name.
546 #
547 reset_db
548 set tblname [string repeat tblname123 100]
549 do_test $tn.10.1.1 {
550 execsql "
551 CREATE TABLE $tblname (a PRIMARY KEY, b)%WR%;
552 INSERT INTO $tblname VALUES('xyz', 'def');
553 "
554 sqlite3session S db main
555 S attach $tblname
556 execsql "
557 INSERT INTO $tblname VALUES('uvw', 'abc');
558 DELETE FROM $tblname WHERE a = 'xyz';
559 "
560 } {}
561 do_changeset_test $tn.10.1.2 S "
562 {INSERT $tblname 0 X. {} {t uvw t abc}}
563 {DELETE $tblname 0 X. {t xyz t def} {}}
564 "
565 do_test $tn.10.1.4 { S delete } {}
566
567 #---------------------------------------------------------------
568 reset_db
569 do_execsql_test $tn.11.1 {
570 CREATE TABLE t1(a, b);
571 }
572 do_test $tn.11.2 {
573 sqlite3session S db main
574 S attach t1
575 execsql {
576 INSERT INTO t1 VALUES(1, 2);
577 }
578 S changeset
579 } {}
580
581 S delete
582
583
584 #-------------------------------------------------------------------------
585 # Test a really long table name.
586 #
587 reset_db
588 set tblname [string repeat tblname123 100]
589 do_test $tn.10.1.1 {
590 execsql "
591 CREATE TABLE $tblname (a PRIMARY KEY, b)%WR%;
592 INSERT INTO $tblname VALUES('xyz', 'def');
593 "
594 sqlite3session S db main
595 S attach $tblname
596 execsql "
597 INSERT INTO $tblname VALUES('uvw', 'abc');
598 DELETE FROM $tblname WHERE a = 'xyz';
599 "
600 } {}
601 do_changeset_test $tn.10.1.2 S "
602 {INSERT $tblname 0 X. {} {t uvw t abc}}
603 {DELETE $tblname 0 X. {t xyz t def} {}}
604 "
605 do_test $tn.10.1.4 { S delete } {}
606
607 #-------------------------------------------------------------------------
608 # Test the effect of updating a column from 0.0 to 0.0.
609 #
610 reset_db
611 do_execsql_test $tn.11.1 {
612 CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL)%WR%;
613 INSERT INTO t1 VALUES(1, 0.0);
614 }
615 do_iterator_test $tn.11.2 * {
616 UPDATE t1 SET b = 0.0;
617 } {
618 }
619
620 reset_db
621 do_execsql_test $tn.12.1 {
622 CREATE TABLE t1(r INTEGER PRIMARY KEY, a, b)%WR%;
623 CREATE INDEX i1 ON t1(a);
624 INSERT INTO t1 VALUES(1, 1, 1);
625 INSERT INTO t1 VALUES(2, 1, 2);
626 INSERT INTO t1 VALUES(3, 1, 3);
627 }
628
629 do_iterator_test $tn.12.2 * {
630 UPDATE t1 SET b='one' WHERE a=1;
631 } {
632 {UPDATE t1 0 X.. {i 1 {} {} i 1} {{} {} {} {} t one}}
633 {UPDATE t1 0 X.. {i 2 {} {} i 2} {{} {} {} {} t one}}
634 {UPDATE t1 0 X.. {i 3 {} {} i 3} {{} {} {} {} t one}}
635 }
636
637 #-------------------------------------------------------------------------
638 # Test that no savepoint is used if -nosavepoint is specified.
639 #
640 do_execsql_test $tn.13.1 {
641 CREATE TABLE x1(a INTEGER PRIMARY KEY, b)%WR%;
642 }
643 do_test $tn.13.2 {
644 execsql BEGIN
645 set C [changeset_from_sql {
646 INSERT INTO x1 VALUES(1, 'one');
647 INSERT INTO x1 VALUES(2, 'two');
648 INSERT INTO x1 VALUES(3, 'three');
649 }]
650 execsql ROLLBACK
651 execsql {
652 INSERT INTO x1 VALUES(1, 'i');
653 INSERT INTO x1 VALUES(2, 'ii');
654 INSERT INTO x1 VALUES(3, 'iii');
655 }
656 } {}
657
658 proc xConflict {args} {
659 set ret [lindex $::CONFLICT_HANDLERS 0]
660 set ::CONFLICT_HANDLERS [lrange $::CONFLICT_HANDLERS 1 end]
661 set ret
662 }
663 do_test $tn.13.3 {
664 set CONFLICT_HANDLERS [list REPLACE REPLACE ABORT]
665 execsql BEGIN
666 catch { sqlite3changeset_apply_v2 db $C xConflict } msg
667 execsql {
668 SELECT * FROM x1
669 }
670 } {1 i 2 ii 3 iii}
671 do_test $tn.13.3 {
672 set CONFLICT_HANDLERS [list REPLACE REPLACE ABORT]
673 execsql ROLLBACK
674 execsql BEGIN
675 catch { sqlite3changeset_apply_v2 -nosavepoint db $C xConflict } msg
676 execsql { SELECT * FROM x1 }
677 } {1 one 2 two 3 iii}
678 execsql ROLLBACK
679
680 do_test $tn.14.1 { sqlite3session_config strm_size -1 } 64
681 do_test $tn.14.2 { sqlite3session_config strm_size 65536 } 65536
682 do_test $tn.14.3 { sqlite3session_config strm_size 64 } 64
683 do_test $tn.14.4 {
684 list [catch {sqlite3session_config invalid 123} msg] $msg
685 } {1 SQLITE_MISUSE}
686
687 #-------------------------------------------------------------------------
688 reset_db
689
690 do_execsql_test $tn.15.0 {
691 CREATE TABLE records(
692 idx INTEGER PRIMARY KEY AUTOINCREMENT,
693 tbl TEXT NOT NULL,
694 pk TEXT NOT NULL,
695 value TEXT,
696 UNIQUE(tbl, pk)
697 );
698
699 CREATE TABLE changes(
700 idx INTEGER PRIMARY KEY AUTOINCREMENT,
701 tbl TEXT NOT NULL,
702 pk TEXT NOT NULL,
703 op TEXT DEFAULT ('='),
704 path TEXT DEFAULT ('$'),
705 value TEXT,
706 UNIQUE(tbl, pk)
707 );
708
709 CREATE TRIGGER changes_after_insert
710 AFTER INSERT ON changes
711 FOR EACH ROW
712 BEGIN
713 INSERT INTO records(tbl, pk, value)
714 VALUES (NEW.tbl, NEW.pk, NEW.value)
715 ON CONFLICT(tbl, pk) DO UPDATE SET value = NEW.value;
716 END;
717
718 CREATE VIEW data_view AS
719 SELECT *, '=' AS op, '$' AS path FROM records
720 WHERE tbl = 'data';
721
722 CREATE TRIGGER data_insert
723 INSTEAD OF INSERT ON data_view
724 BEGIN
725 INSERT INTO changes(tbl, pk, op, path, value)
726 VALUES (NEW.tbl, NEW.pk, NEW.op, NEW.path, NEW.value);
727 END;
728
729 CREATE TRIGGER data_update
730 INSTEAD OF UPDATE ON data_view
731 BEGIN
732 INSERT INTO changes(tbl, pk, op, path, value)
733 VALUES (NEW.tbl, NEW.pk, NEW.op, NEW.path, NEW.value);
734 END;
735
736 CREATE TRIGGER data_delete
737 INSTEAD OF DELETE ON data_view
738 BEGIN
739 INSERT INTO changes(tbl, pk, op, path, value)
740 VALUES (NEW.tbl, NEW.pk, NEW.op, NEW.path, NEW.value);
741 END;
742 }
743
744 do_test $tn.15.1 {
745 set C [changeset_from_sql {
746 INSERT INTO data_view (tbl, pk, value) VALUES
747 ('data', '1', 'First'),
748 ('data', '2', 'Second');
749 }]
750 changeset_to_list $C
751 } [list \
752 {INSERT changes 1 X..... {} {i 1 t data t 1 n {} n {} t First}} \
753 {INSERT changes 1 X..... {} {i 2 t data t 2 n {} n {} t Second}} \
754 {INSERT records 1 X... {} {i 1 t data t 1 t First}} \
755 {INSERT records 1 X... {} {i 2 t data t 2 t Second}} \
756 ]
757
758 forcedelete test.db2
759 sqlite3 db2 test.db2
760
761 do_test $tn.15.2 {
762 db2 eval {
763 CREATE TABLE records(
764 idx INTEGER PRIMARY KEY AUTOINCREMENT,
765 tbl TEXT NOT NULL,
766 pk TEXT NOT NULL,
767 value TEXT,
768 UNIQUE(tbl, pk)
769 );
770 CREATE TABLE changes(
771 idx INTEGER PRIMARY KEY AUTOINCREMENT,
772 tbl TEXT NOT NULL,
773 pk TEXT NOT NULL,
774 op TEXT DEFAULT ('='),
775 path TEXT DEFAULT ('$'),
776 value TEXT,
777 UNIQUE(tbl, pk)
778 );
779
780 CREATE TRIGGER changes_after_insert
781 AFTER INSERT ON changes
782 FOR EACH ROW
783 BEGIN
784 INSERT INTO records(tbl, pk, value)
785 VALUES (NEW.tbl, NEW.pk, NEW.value)
786 ON CONFLICT(tbl, pk) DO UPDATE SET value = NEW.value;
787 END;
788 }
789
790 sqlite3changeset_apply db2 $C ""
791
792 db2 eval {
793 SELECT * FROM records;
794 }
795 } [list 1 data 1 First 2 data 2 Second]
796
797 do_test $tn.15.3 {
798 db2 eval {
799 SELECT * FROM changes;
800 }
801 } {1 data 1 {} {} First 2 data 2 {} {} Second}
802
803 db2 close
804 }]
805 }
806
807 finish_test