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 implements regression tests for SQLite library.
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 session1
23 # Run all tests in this file twice. Once with "WITHOUT ROWID", and once
24 # with regular rowid tables.
26 foreach {tn trailing} {
30 eval [string map [list %WR% $trailing] {
33 forcedelete test.db test.db2
36 do_execsql_test $tn.1.0 {
37 CREATE TABLE t1(x PRIMARY KEY, y) %WR%;
38 INSERT INTO t1 VALUES('abc', 'def');
41 #-------------------------------------------------------------------------
42 # Test creating, attaching tables to and deleting session objects.
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 } {}
54 sqlite3session S db main
56 execsql { INSERT INTO t1 VALUES('ghi', 'jkl') }
58 do_test $tn.1.11 { S delete } {}
61 sqlite3session S db main
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 }
73 #-------------------------------------------------------------------------
74 # Simple changeset tests. Also test the sqlite3changeset_invert()
78 execsql { DELETE FROM t1 }
79 sqlite3session S db main
81 execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') }
82 execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') }
83 execsql { INSERT INTO t1 VALUES(3, 'Thonburi') }
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}}
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} {}}
95 do_test $tn.2.1.4 { S delete } {}
98 sqlite3session S db main
100 execsql { DELETE FROM t1 WHERE 1 }
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} {}}
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}}
112 do_test $tn.2.2.4 { S delete } {}
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') }
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;
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}}
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} {}}
143 do_test $tn.2.3.4 { S delete } {}
146 sqlite3session S db main
148 execsql { INSERT INTO t1 VALUES(100, 'Bangkok') }
149 execsql { DELETE FROM t1 WHERE x = 100 }
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 } {}
155 do_execsql_test $tn.2.5.0 {
156 SELECT * FROM t1 ORDER BY x
164 sqlite3session S db main
166 execsql { DELETE FROM t1 }
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} {}}
173 do_test $tn.2.5.3 { S delete } {}
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.
181 forcedelete test.db test.db2
185 proc xConflict {args} {
186 lappend ::xConflict $args
190 proc bgerror {args} { set ::background_error $args }
192 proc do_conflict_test {tn args} {
193 set O(-tables) [list]
195 set O(-conflicts) [list]
198 foreach key [array names V] {
199 if {![info exists O($key)]} {error "no such option: $key"}
203 sqlite3session S db main
204 foreach t $O(-tables) { S attach $t }
206 set ::xConflict [list]
207 sqlite3changeset_apply db2 [S changeset] xConflict
210 foreach c $O(-conflicts) {
217 uplevel do_test $tn [list { set ::xConflict }] [list $conflicts]
221 proc do_db2_test {testname sql {result {}}} {
222 uplevel do_test $testname [list "execsql {$sql} db2"] [list [list {*}$result]]
225 # Test INSERT changesets.
228 execsql { CREATE TABLE t1(a PRIMARY KEY, b NOT NULL) %WR% } db2
230 CREATE TABLE t1(a PRIMARY KEY, b) %WR%;
231 INSERT INTO t1 VALUES(1, 'one');
232 INSERT INTO t1 VALUES(2, 'two');
235 do_db2_test $tn.3.1.1 "INSERT INTO t1 VALUES(6, 'VI')"
236 do_conflict_test $tn.3.1.2 -tables t1 -sql {
237 INSERT INTO t1 VALUES(3, 'three');
238 INSERT INTO t1 VALUES(4, 'four');
239 INSERT INTO t1 VALUES(5, 'five');
240 INSERT INTO t1 VALUES(6, 'six');
241 INSERT INTO t1 VALUES(7, 'seven');
242 INSERT INTO t1 VALUES(8, NULL);
244 {INSERT t1 CONFLICT {i 6 t six} {i 6 t VI}}
245 {INSERT t1 CONSTRAINT {i 8 n {}}}
248 do_db2_test $tn.3.1.3 "SELECT * FROM t1 ORDER BY a" {
249 3 three 4 four 5 five 6 VI 7 seven
251 do_execsql_test $tn.3.1.4 "SELECT * FROM t1" {
252 1 one 2 two 3 three 4 four 5 five 6 six 7 seven 8 {}
255 # Test DELETE changesets.
257 do_execsql_test $tn.3.2.1 {
258 PRAGMA foreign_keys = on;
259 CREATE TABLE t2(a PRIMARY KEY, b)%WR%;
260 CREATE TABLE t3(c, d REFERENCES t2);
261 INSERT INTO t2 VALUES(1, 'one');
262 INSERT INTO t2 VALUES(2, 'two');
263 INSERT INTO t2 VALUES(3, 'three');
264 INSERT INTO t2 VALUES(4, 'four');
266 do_db2_test $tn.3.2.2 {
267 PRAGMA foreign_keys = on;
268 CREATE TABLE t2(a PRIMARY KEY, b)%WR%;
269 CREATE TABLE t3(c, d REFERENCES t2);
270 INSERT INTO t2 VALUES(1, 'one');
271 INSERT INTO t2 VALUES(2, 'two');
272 INSERT INTO t2 VALUES(4, 'five');
273 INSERT INTO t3 VALUES('i', 1);
275 do_conflict_test $tn.3.2.3 -tables t2 -sql {
276 DELETE FROM t2 WHERE a = 1;
277 DELETE FROM t2 WHERE a = 2;
278 DELETE FROM t2 WHERE a = 3;
279 DELETE FROM t2 WHERE a = 4;
281 {DELETE t2 NOTFOUND {i 3 t three}}
282 {DELETE t2 DATA {i 4 t four} {i 4 t five}}
285 do_execsql_test $tn.3.2.4 "SELECT * FROM t2" {}
286 do_db2_test $tn.3.2.5 "SELECT * FROM t2" {4 five}
288 # Test UPDATE changesets.
290 do_execsql_test $tn.3.3.1 {
291 CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c))%WR%;
292 INSERT INTO t4 VALUES(1, 2, 3);
293 INSERT INTO t4 VALUES(4, 5, 6);
294 INSERT INTO t4 VALUES(7, 8, 9);
295 INSERT INTO t4 VALUES(10, 11, 12);
297 do_db2_test $tn.3.3.2 {
298 CREATE TABLE t4(a NOT NULL, b, c, PRIMARY KEY(b, c))%WR%;
299 INSERT INTO t4 VALUES(0, 2, 3);
300 INSERT INTO t4 VALUES(4, 5, 7);
301 INSERT INTO t4 VALUES(7, 8, 9);
302 INSERT INTO t4 VALUES(10, 11, 12);
304 do_conflict_test $tn.3.3.3 -tables t4 -sql {
305 UPDATE t4 SET a = -1 WHERE b = 2;
306 UPDATE t4 SET a = -1 WHERE b = 5;
307 UPDATE t4 SET a = NULL WHERE c = 9;
308 UPDATE t4 SET a = 'x' WHERE b = 11;
310 {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 {} {} {} {}} {i 0 i 2 i 3}}
311 {UPDATE t4 NOTFOUND {i 4 i 5 i 6} {i -1 {} {} {} {}}}
312 {UPDATE t4 CONSTRAINT {i 7 i 8 i 9} {n {} {} {} {} {}}}
314 do_db2_test $tn.3.3.4 { SELECT * FROM t4 } {0 2 3 4 5 7 7 8 9 x 11 12}
315 do_execsql_test $tn.3.3.5 { SELECT * FROM t4 } {-1 2 3 -1 5 6 {} 8 9 x 11 12}
317 #-------------------------------------------------------------------------
318 # This next block of tests verifies that values returned by the conflict
319 # handler are intepreted correctly.
325 forcedelete test.db test.db2
330 proc xConflict {args} {
331 lappend ::xConflict $args
332 return $::conflict_return
335 foreach {tn2 conflict_return after} {
336 1 OMIT {1 2 value1 4 5 7 10 x x}
337 2 REPLACE {1 2 value1 4 5 value2 10 8 9}
341 do_test $tn.4.$tn2.1 {
342 foreach db {db db2} {
344 CREATE TABLE t1(a, b, c, PRIMARY KEY(a))%WR%;
345 INSERT INTO t1 VALUES(1, 2, 3);
346 INSERT INTO t1 VALUES(4, 5, 6);
347 INSERT INTO t1 VALUES(7, 8, 9);
351 REPLACE INTO t1 VALUES(4, 5, 7);
352 REPLACE INTO t1 VALUES(10, 'x', 'x');
356 do_conflict_test $tn.4.$tn2.2 -tables t1 -sql {
357 UPDATE t1 SET c = 'value1' WHERE a = 1; -- no conflict
358 UPDATE t1 SET c = 'value2' WHERE a = 4; -- DATA conflict
359 UPDATE t1 SET a = 10 WHERE a = 7; -- CONFLICT conflict
361 {INSERT t1 CONFLICT {i 10 i 8 i 9} {i 10 t x t x}}
362 {UPDATE t1 DATA {i 4 {} {} i 6} {{} {} {} {} t value2} {i 4 i 5 i 7}}
365 do_db2_test $tn.4.$tn2.3 "SELECT * FROM t1 ORDER BY a" $after
368 foreach {tn2 conflict_return} {
374 do_test $tn.5.$tn2.1 {
375 # Create an identical schema in both databases.
377 CREATE TABLE "'foolish name'"(x, y, z, PRIMARY KEY(x, y))%WR%;
382 # Add some rows to [db2]. These rows will cause conflicts later
383 # on when the changeset from [db] is applied to it.
385 INSERT INTO "'foolish name'" VALUES('one', 'one', 'ii');
386 INSERT INTO "'foolish name'" VALUES('one', 'two', 'i');
387 INSERT INTO "'foolish name'" VALUES('two', 'two', 'ii');
392 do_conflict_test $tn.5.$tn2.2 -tables {{'foolish name'}} -sql {
393 INSERT INTO "'foolish name'" VALUES('one', 'two', 2);
395 {INSERT {'foolish name'} CONFLICT {t one t two i 2} {t one t two t i}}
398 set res(REPLACE) {one one ii one two 2 two two ii}
399 set res(OMIT) {one one ii one two i two two ii}
400 do_db2_test $tn.5.$tn2.3 {
401 SELECT * FROM "'foolish name'" ORDER BY x, y
402 } $res($conflict_return)
405 do_test $tn.5.$tn2.1 {
407 CREATE TABLE d1("z""z" PRIMARY KEY, y)%WR%;
408 INSERT INTO d1 VALUES(1, 'one');
409 INSERT INTO d1 VALUES(2, 'two');
415 UPDATE d1 SET y = 'TWO' WHERE "z""z" = 2;
420 do_conflict_test $tn.5.$tn2.2 -tables d1 -sql {
421 DELETE FROM d1 WHERE "z""z" = 2;
423 {DELETE d1 DATA {i 2 t two} {i 2 t TWO}}
426 set res(REPLACE) {1 one}
427 set res(OMIT) {1 one 2 TWO}
428 do_db2_test $tn.5.$tn2.3 "SELECT * FROM d1" $res($conflict_return)
431 #-------------------------------------------------------------------------
432 # Test that two tables can be monitored by a single session object.
436 CREATE TABLE t1(a COLLATE nocase PRIMARY KEY, b)%WR%;
437 CREATE TABLE t2(a, b PRIMARY KEY)%WR%;
443 INSERT INTO t1 VALUES('a', 'b');
444 INSERT INTO t2 VALUES('a', 'b');
448 set conflict_return ""
449 do_conflict_test $tn.6.1 -tables {t1 t2} -sql {
450 INSERT INTO t1 VALUES('1', '2');
451 INSERT INTO t1 VALUES('A', 'B');
452 INSERT INTO t2 VALUES('A', 'B');
454 {INSERT t1 CONFLICT {t A t B} {t a t b}}
457 do_db2_test $tn.6.2 "SELECT * FROM t1 ORDER BY a" {1 2 a b}
458 do_db2_test $tn.6.3 "SELECT * FROM t2 ORDER BY a" {A B a b}
460 #-------------------------------------------------------------------------
461 # Test that session objects are not confused by changes to table in
467 do_iterator_test $tn.7.1 * {
468 ATTACH 'test.db2' AS aux;
469 CREATE TABLE main.t1(x PRIMARY KEY, y)%WR%;
470 CREATE TABLE aux.t1(x PRIMARY KEY, y)%WR%;
472 INSERT INTO main.t1 VALUES('one', 1);
473 INSERT INTO main.t1 VALUES('two', 2);
474 INSERT INTO aux.t1 VALUES('three', 3);
475 INSERT INTO aux.t1 VALUES('four', 4);
477 {INSERT t1 0 X. {} {t two i 2}}
478 {INSERT t1 0 X. {} {t one i 1}}
481 #-------------------------------------------------------------------------
482 # Test the sqlite3session_isempty() function.
486 CREATE TABLE t5(x PRIMARY KEY, y)%WR%;
487 CREATE TABLE t6(x PRIMARY KEY, y)%WR%;
488 INSERT INTO t5 VALUES('a', 'b');
489 INSERT INTO t6 VALUES('a', 'b');
491 sqlite3session S db main
497 execsql { DELETE FROM t5 }
502 sqlite3session S db main
504 execsql { DELETE FROM t5 }
507 do_test $tn.8.4 { S delete } {}
510 sqlite3session S db main
513 execsql { INSERT INTO t5 VALUES(1, 2) }
519 sqlite3session S db main
522 execsql { INSERT INTO t6 VALUES(1, 2) }
525 do_test $tn.8.7 { S delete } {}
527 #-------------------------------------------------------------------------
529 do_execsql_test $tn.9.1 {
530 CREATE TABLE t7(a, b, c, d, e PRIMARY KEY, f, g)%WR%;
531 INSERT INTO t7 VALUES(1, 1, 1, 1, 1, 1, 1);
534 sqlite3session S db main
536 execsql { UPDATE t7 SET b=2, d=2 }
538 do_changeset_test $tn.9.2 S {{UPDATE t7 0 ....X.. {{} {} i 1 {} {} i 1 i 1 {} {} {} {}} {{} {} i 2 {} {} i 2 {} {} {} {} {} {}}}}
542 #-------------------------------------------------------------------------
543 # Test a really long table name.
546 set tblname [string repeat tblname123 100]
549 CREATE TABLE $tblname (a PRIMARY KEY, b)%WR%;
550 INSERT INTO $tblname VALUES('xyz', 'def');
552 sqlite3session S db main
555 INSERT INTO $tblname VALUES('uvw', 'abc');
556 DELETE FROM $tblname WHERE a = 'xyz';
559 do_changeset_test $tn.10.1.2 S "
560 {INSERT $tblname 0 X. {} {t uvw t abc}}
561 {DELETE $tblname 0 X. {t xyz t def} {}}
563 do_test $tn.10.1.4 { S delete } {}
565 #---------------------------------------------------------------
567 do_execsql_test $tn.11.1 {
568 CREATE TABLE t1(a, b);
571 sqlite3session S db main
574 INSERT INTO t1 VALUES(1, 2);
582 #-------------------------------------------------------------------------
583 # Test a really long table name.
586 set tblname [string repeat tblname123 100]
589 CREATE TABLE $tblname (a PRIMARY KEY, b)%WR%;
590 INSERT INTO $tblname VALUES('xyz', 'def');
592 sqlite3session S db main
595 INSERT INTO $tblname VALUES('uvw', 'abc');
596 DELETE FROM $tblname WHERE a = 'xyz';
599 do_changeset_test $tn.10.1.2 S "
600 {INSERT $tblname 0 X. {} {t uvw t abc}}
601 {DELETE $tblname 0 X. {t xyz t def} {}}
603 do_test $tn.10.1.4 { S delete } {}
605 #-------------------------------------------------------------------------
606 # Test the effect of updating a column from 0.0 to 0.0.
609 do_execsql_test $tn.11.1 {
610 CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL)%WR%;
611 INSERT INTO t1 VALUES(1, 0.0);
613 do_iterator_test $tn.11.2 * {
614 UPDATE t1 SET b = 0.0;
619 do_execsql_test $tn.12.1 {
620 CREATE TABLE t1(r INTEGER PRIMARY KEY, a, b)%WR%;
621 CREATE INDEX i1 ON t1(a);
622 INSERT INTO t1 VALUES(1, 1, 1);
623 INSERT INTO t1 VALUES(2, 1, 2);
624 INSERT INTO t1 VALUES(3, 1, 3);
627 do_iterator_test $tn.12.2 * {
628 UPDATE t1 SET b='one' WHERE a=1;
630 {UPDATE t1 0 X.. {i 1 {} {} i 1} {{} {} {} {} t one}}
631 {UPDATE t1 0 X.. {i 2 {} {} i 2} {{} {} {} {} t one}}
632 {UPDATE t1 0 X.. {i 3 {} {} i 3} {{} {} {} {} t one}}
635 #-------------------------------------------------------------------------
636 # Test that no savepoint is used if -nosavepoint is specified.
638 do_execsql_test $tn.13.1 {
639 CREATE TABLE x1(a INTEGER PRIMARY KEY, b)%WR%;
643 set C [changeset_from_sql {
644 INSERT INTO x1 VALUES(1, 'one');
645 INSERT INTO x1 VALUES(2, 'two');
646 INSERT INTO x1 VALUES(3, 'three');
650 INSERT INTO x1 VALUES(1, 'i');
651 INSERT INTO x1 VALUES(2, 'ii');
652 INSERT INTO x1 VALUES(3, 'iii');
656 proc xConflict {args} {
657 set ret [lindex $::CONFLICT_HANDLERS 0]
658 set ::CONFLICT_HANDLERS [lrange $::CONFLICT_HANDLERS 1 end]
662 set CONFLICT_HANDLERS [list REPLACE REPLACE ABORT]
664 catch { sqlite3changeset_apply_v2 db $C xConflict } msg
670 set CONFLICT_HANDLERS [list REPLACE REPLACE ABORT]
673 catch { sqlite3changeset_apply_v2 -nosavepoint db $C xConflict } msg
674 execsql { SELECT * FROM x1 }
675 } {1 one 2 two 3 iii}
678 do_test $tn.14.1 { sqlite3session_config strm_size -1 } 64
679 do_test $tn.14.2 { sqlite3session_config strm_size 65536 } 65536
680 do_test $tn.14.3 { sqlite3session_config strm_size 64 } 64
682 list [catch {sqlite3session_config invalid 123} msg] $msg