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 #-------------------------------------------------------------------------
156 # Test the application of simple changesets. These tests also test that
157 # the conflict callback is invoked correctly. For these tests, the
158 # conflict callback always returns OMIT.
161 forcedelete test.db test.db2
165 proc xConflict {args} {
166 lappend ::xConflict $args
170 proc bgerror {args} { set ::background_error $args }
172 proc do_conflict_test {tn args} {
173 set O(-tables) [list]
175 set O(-conflicts) [list]
178 foreach key [array names V] {
179 if {![info exists O($key)]} {error "no such option: $key"}
183 sqlite3session S db main
184 foreach t $O(-tables) { S attach $t }
186 set ::xConflict [list]
187 sqlite3changeset_apply db2 [S changeset] xConflict
190 foreach c $O(-conflicts) {
197 uplevel do_test $tn [list { set ::xConflict }] [list $conflicts]
201 proc do_db2_test {testname sql {result {}}} {
202 uplevel do_test $testname [list "execsql {$sql} db2"] [list [list {*}$result]]
205 # Test INSERT changesets.
208 execsql { CREATE TABLE t1(a PRIMARY KEY, b NOT NULL) %WR% } db2
210 CREATE TABLE t1(a PRIMARY KEY, b) %WR%;
211 INSERT INTO t1 VALUES(1, 'one');
212 INSERT INTO t1 VALUES(2, 'two');
215 do_db2_test $tn.3.1.1 "INSERT INTO t1 VALUES(6, 'VI')"
216 do_conflict_test $tn.3.1.2 -tables t1 -sql {
217 INSERT INTO t1 VALUES(3, 'three');
218 INSERT INTO t1 VALUES(4, 'four');
219 INSERT INTO t1 VALUES(5, 'five');
220 INSERT INTO t1 VALUES(6, 'six');
221 INSERT INTO t1 VALUES(7, 'seven');
222 INSERT INTO t1 VALUES(8, NULL);
224 {INSERT t1 CONFLICT {i 6 t six} {i 6 t VI}}
225 {INSERT t1 CONSTRAINT {i 8 n {}}}
228 do_db2_test $tn.3.1.3 "SELECT * FROM t1 ORDER BY a" {
229 3 three 4 four 5 five 6 VI 7 seven
231 do_execsql_test $tn.3.1.4 "SELECT * FROM t1" {
232 1 one 2 two 3 three 4 four 5 five 6 six 7 seven 8 {}
235 # Test DELETE changesets.
237 do_execsql_test $tn.3.2.1 {
238 PRAGMA foreign_keys = on;
239 CREATE TABLE t2(a PRIMARY KEY, b)%WR%;
240 CREATE TABLE t3(c, d REFERENCES t2);
241 INSERT INTO t2 VALUES(1, 'one');
242 INSERT INTO t2 VALUES(2, 'two');
243 INSERT INTO t2 VALUES(3, 'three');
244 INSERT INTO t2 VALUES(4, 'four');
246 do_db2_test $tn.3.2.2 {
247 PRAGMA foreign_keys = on;
248 CREATE TABLE t2(a PRIMARY KEY, b)%WR%;
249 CREATE TABLE t3(c, d REFERENCES t2);
250 INSERT INTO t2 VALUES(1, 'one');
251 INSERT INTO t2 VALUES(2, 'two');
252 INSERT INTO t2 VALUES(4, 'five');
253 INSERT INTO t3 VALUES('i', 1);
255 do_conflict_test $tn.3.2.3 -tables t2 -sql {
256 DELETE FROM t2 WHERE a = 1;
257 DELETE FROM t2 WHERE a = 2;
258 DELETE FROM t2 WHERE a = 3;
259 DELETE FROM t2 WHERE a = 4;
261 {DELETE t2 NOTFOUND {i 3 t three}}
262 {DELETE t2 DATA {i 4 t four} {i 4 t five}}
265 do_execsql_test $tn.3.2.4 "SELECT * FROM t2" {}
266 do_db2_test $tn.3.2.5 "SELECT * FROM t2" {4 five}
268 # Test UPDATE changesets.
270 do_execsql_test $tn.3.3.1 {
271 CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c))%WR%;
272 INSERT INTO t4 VALUES(1, 2, 3);
273 INSERT INTO t4 VALUES(4, 5, 6);
274 INSERT INTO t4 VALUES(7, 8, 9);
275 INSERT INTO t4 VALUES(10, 11, 12);
277 do_db2_test $tn.3.3.2 {
278 CREATE TABLE t4(a NOT NULL, b, c, PRIMARY KEY(b, c))%WR%;
279 INSERT INTO t4 VALUES(0, 2, 3);
280 INSERT INTO t4 VALUES(4, 5, 7);
281 INSERT INTO t4 VALUES(7, 8, 9);
282 INSERT INTO t4 VALUES(10, 11, 12);
284 do_conflict_test $tn.3.3.3 -tables t4 -sql {
285 UPDATE t4 SET a = -1 WHERE b = 2;
286 UPDATE t4 SET a = -1 WHERE b = 5;
287 UPDATE t4 SET a = NULL WHERE c = 9;
288 UPDATE t4 SET a = 'x' WHERE b = 11;
290 {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 {} {} {} {}} {i 0 i 2 i 3}}
291 {UPDATE t4 NOTFOUND {i 4 i 5 i 6} {i -1 {} {} {} {}}}
292 {UPDATE t4 CONSTRAINT {i 7 i 8 i 9} {n {} {} {} {} {}}}
294 do_db2_test $tn.3.3.4 { SELECT * FROM t4 } {0 2 3 4 5 7 7 8 9 x 11 12}
295 do_execsql_test $tn.3.3.5 { SELECT * FROM t4 } {-1 2 3 -1 5 6 {} 8 9 x 11 12}
297 #-------------------------------------------------------------------------
298 # This next block of tests verifies that values returned by the conflict
299 # handler are intepreted correctly.
305 forcedelete test.db test.db2
310 proc xConflict {args} {
311 lappend ::xConflict $args
312 return $::conflict_return
315 foreach {tn2 conflict_return after} {
316 1 OMIT {1 2 value1 4 5 7 10 x x}
317 2 REPLACE {1 2 value1 4 5 value2 10 8 9}
321 do_test $tn.4.$tn2.1 {
322 foreach db {db db2} {
324 CREATE TABLE t1(a, b, c, PRIMARY KEY(a))%WR%;
325 INSERT INTO t1 VALUES(1, 2, 3);
326 INSERT INTO t1 VALUES(4, 5, 6);
327 INSERT INTO t1 VALUES(7, 8, 9);
331 REPLACE INTO t1 VALUES(4, 5, 7);
332 REPLACE INTO t1 VALUES(10, 'x', 'x');
336 do_conflict_test $tn.4.$tn2.2 -tables t1 -sql {
337 UPDATE t1 SET c = 'value1' WHERE a = 1; -- no conflict
338 UPDATE t1 SET c = 'value2' WHERE a = 4; -- DATA conflict
339 UPDATE t1 SET a = 10 WHERE a = 7; -- CONFLICT conflict
341 {INSERT t1 CONFLICT {i 10 i 8 i 9} {i 10 t x t x}}
342 {UPDATE t1 DATA {i 4 {} {} i 6} {{} {} {} {} t value2} {i 4 i 5 i 7}}
345 do_db2_test $tn.4.$tn2.3 "SELECT * FROM t1 ORDER BY a" $after
348 foreach {tn2 conflict_return} {
354 do_test $tn.5.$tn2.1 {
355 # Create an identical schema in both databases.
357 CREATE TABLE "'foolish name'"(x, y, z, PRIMARY KEY(x, y))%WR%;
362 # Add some rows to [db2]. These rows will cause conflicts later
363 # on when the changeset from [db] is applied to it.
365 INSERT INTO "'foolish name'" VALUES('one', 'one', 'ii');
366 INSERT INTO "'foolish name'" VALUES('one', 'two', 'i');
367 INSERT INTO "'foolish name'" VALUES('two', 'two', 'ii');
372 do_conflict_test $tn.5.$tn2.2 -tables {{'foolish name'}} -sql {
373 INSERT INTO "'foolish name'" VALUES('one', 'two', 2);
375 {INSERT {'foolish name'} CONFLICT {t one t two i 2} {t one t two t i}}
378 set res(REPLACE) {one one ii one two 2 two two ii}
379 set res(OMIT) {one one ii one two i two two ii}
380 do_db2_test $tn.5.$tn2.3 {
381 SELECT * FROM "'foolish name'" ORDER BY x, y
382 } $res($conflict_return)
385 do_test $tn.5.$tn2.1 {
387 CREATE TABLE d1("z""z" PRIMARY KEY, y)%WR%;
388 INSERT INTO d1 VALUES(1, 'one');
389 INSERT INTO d1 VALUES(2, 'two');
395 UPDATE d1 SET y = 'TWO' WHERE "z""z" = 2;
400 do_conflict_test $tn.5.$tn2.2 -tables d1 -sql {
401 DELETE FROM d1 WHERE "z""z" = 2;
403 {DELETE d1 DATA {i 2 t two} {i 2 t TWO}}
406 set res(REPLACE) {1 one}
407 set res(OMIT) {1 one 2 TWO}
408 do_db2_test $tn.5.$tn2.3 "SELECT * FROM d1" $res($conflict_return)
411 #-------------------------------------------------------------------------
412 # Test that two tables can be monitored by a single session object.
416 CREATE TABLE t1(a COLLATE nocase PRIMARY KEY, b)%WR%;
417 CREATE TABLE t2(a, b PRIMARY KEY)%WR%;
423 INSERT INTO t1 VALUES('a', 'b');
424 INSERT INTO t2 VALUES('a', 'b');
428 set conflict_return ""
429 do_conflict_test $tn.6.1 -tables {t1 t2} -sql {
430 INSERT INTO t1 VALUES('1', '2');
431 INSERT INTO t1 VALUES('A', 'B');
432 INSERT INTO t2 VALUES('A', 'B');
434 {INSERT t1 CONFLICT {t A t B} {t a t b}}
437 do_db2_test $tn.6.2 "SELECT * FROM t1 ORDER BY a" {1 2 a b}
438 do_db2_test $tn.6.3 "SELECT * FROM t2 ORDER BY a" {A B a b}
440 #-------------------------------------------------------------------------
441 # Test that session objects are not confused by changes to table in
447 do_iterator_test $tn.7.1 * {
448 ATTACH 'test.db2' AS aux;
449 CREATE TABLE main.t1(x PRIMARY KEY, y)%WR%;
450 CREATE TABLE aux.t1(x PRIMARY KEY, y)%WR%;
452 INSERT INTO main.t1 VALUES('one', 1);
453 INSERT INTO main.t1 VALUES('two', 2);
454 INSERT INTO aux.t1 VALUES('three', 3);
455 INSERT INTO aux.t1 VALUES('four', 4);
457 {INSERT t1 0 X. {} {t two i 2}}
458 {INSERT t1 0 X. {} {t one i 1}}
461 #-------------------------------------------------------------------------
462 # Test the sqlite3session_isempty() function.
466 CREATE TABLE t5(x PRIMARY KEY, y)%WR%;
467 CREATE TABLE t6(x PRIMARY KEY, y)%WR%;
468 INSERT INTO t5 VALUES('a', 'b');
469 INSERT INTO t6 VALUES('a', 'b');
471 sqlite3session S db main
477 execsql { DELETE FROM t5 }
482 sqlite3session S db main
484 execsql { DELETE FROM t5 }
487 do_test $tn.8.4 { S delete } {}
490 sqlite3session S db main
493 execsql { INSERT INTO t5 VALUES(1, 2) }
499 sqlite3session S db main
502 execsql { INSERT INTO t6 VALUES(1, 2) }
505 do_test $tn.8.7 { S delete } {}
507 #-------------------------------------------------------------------------
509 do_execsql_test $tn.9.1 {
510 CREATE TABLE t7(a, b, c, d, e PRIMARY KEY, f, g)%WR%;
511 INSERT INTO t7 VALUES(1, 1, 1, 1, 1, 1, 1);
514 sqlite3session S db main
516 execsql { UPDATE t7 SET b=2, d=2 }
518 do_changeset_test $tn.9.2 S {{UPDATE t7 0 ....X.. {{} {} i 1 {} {} i 1 i 1 {} {} {} {}} {{} {} i 2 {} {} i 2 {} {} {} {} {} {}}}}
522 #-------------------------------------------------------------------------
523 # Test a really long table name.
526 set tblname [string repeat tblname123 100]
529 CREATE TABLE $tblname (a PRIMARY KEY, b)%WR%;
530 INSERT INTO $tblname VALUES('xyz', 'def');
532 sqlite3session S db main
535 INSERT INTO $tblname VALUES('uvw', 'abc');
536 DELETE FROM $tblname WHERE a = 'xyz';
539 do_changeset_test $tn.10.1.2 S "
540 {INSERT $tblname 0 X. {} {t uvw t abc}}
541 {DELETE $tblname 0 X. {t xyz t def} {}}
543 do_test $tn.10.1.4 { S delete } {}
545 #---------------------------------------------------------------
547 do_execsql_test $tn.11.1 {
548 CREATE TABLE t1(a, b);
551 sqlite3session S db main
554 INSERT INTO t1 VALUES(1, 2);
562 #-------------------------------------------------------------------------
563 # Test a really long table name.
566 set tblname [string repeat tblname123 100]
569 CREATE TABLE $tblname (a PRIMARY KEY, b)%WR%;
570 INSERT INTO $tblname VALUES('xyz', 'def');
572 sqlite3session S db main
575 INSERT INTO $tblname VALUES('uvw', 'abc');
576 DELETE FROM $tblname WHERE a = 'xyz';
579 do_changeset_test $tn.10.1.2 S "
580 {INSERT $tblname 0 X. {} {t uvw t abc}}
581 {DELETE $tblname 0 X. {t xyz t def} {}}
583 do_test $tn.10.1.4 { S delete } {}
585 #-------------------------------------------------------------------------
586 # Test the effect of updating a column from 0.0 to 0.0.
589 do_execsql_test $tn.11.1 {
590 CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL)%WR%;
591 INSERT INTO t1 VALUES(1, 0.0);
593 do_iterator_test $tn.11.2 * {
594 UPDATE t1 SET b = 0.0;
599 do_execsql_test $tn.12.1 {
600 CREATE TABLE t1(r INTEGER PRIMARY KEY, a, b)%WR%;
601 CREATE INDEX i1 ON t1(a);
602 INSERT INTO t1 VALUES(1, 1, 1);
603 INSERT INTO t1 VALUES(2, 1, 2);
604 INSERT INTO t1 VALUES(3, 1, 3);
607 do_iterator_test $tn.12.2 * {
608 UPDATE t1 SET b='one' WHERE a=1;
610 {UPDATE t1 0 X.. {i 1 {} {} i 1} {{} {} {} {} t one}}
611 {UPDATE t1 0 X.. {i 2 {} {} i 2} {{} {} {} {} t one}}
612 {UPDATE t1 0 X.. {i 3 {} {} i 3} {{} {} {} {} t one}}