xref: /sqlite-3.40.0/ext/session/sessionG.test (revision dfe4e6bb) 
1# 2016 March 30
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#
12# This file implements regression tests for the sessions module.
13# Specifically, it tests that UNIQUE constraints are dealt with correctly.
14#
15
16
17
18if {![info exists testdir]} {
19  set testdir [file join [file dirname [info script]] .. .. test]
20}
21source [file join [file dirname [info script]] session_common.tcl]
22source $testdir/tester.tcl
23ifcapable !session {finish_test; return}
24set testprefix sessionG
25
26
27forcedelete test.db2
28sqlite3 db2 test.db2
29
30do_test 1.0 {
31  do_common_sql {
32    CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
33    INSERT INTO t1 VALUES(1, 'one');
34    INSERT INTO t1 VALUES(2, 'two');
35    INSERT INTO t1 VALUES(3, 'three');
36  }
37  do_then_apply_sql {
38    DELETE FROM t1 WHERE a=1;
39    INSERT INTO t1 VALUES(4, 'one');
40  }
41  compare_db db db2
42} {}
43
44do_test 1.1 {
45  do_then_apply_sql {
46    DELETE FROM t1 WHERE a=4;
47    INSERT INTO t1 VALUES(1, 'one');
48  }
49  compare_db db db2
50} {}
51
52do_test 1.2 {
53  execsql { INSERT INTO t1 VALUES(5, 'five') } db2
54  do_then_apply_sql {
55    INSERT INTO t1 VALUES(11, 'eleven');
56    INSERT INTO t1 VALUES(12, 'five');
57  }
58  execsql { SELECT * FROM t1 } db2
59} {2 two 3 three 1 one 5 five 11 eleven}
60
61do_test 1.3 {
62  execsql { SELECT * FROM t1 }
63} {2 two 3 three 1 one 11 eleven 12 five}
64
65#-------------------------------------------------------------------------
66#
67reset_db
68db2 close
69forcedelete test.db2
70sqlite3 db2 test.db2
71
72do_test 2.1 {
73  do_common_sql {
74    CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE);
75    INSERT INTO t1 VALUES(1, 1, 1);
76    INSERT INTO t1 VALUES(2, 2, 2);
77    INSERT INTO t1 VALUES(3, 3, 3);
78  }
79} {}
80
81do_test 2.2.1 {
82  # It is not possible to apply the changeset generated by the following
83  # SQL, as none of the three updated rows may be updated as part of the
84  # first pass.
85  do_then_apply_sql {
86    UPDATE t1 SET b=0 WHERE a=1;
87    UPDATE t1 SET b=1 WHERE a=2;
88    UPDATE t1 SET b=2 WHERE a=3;
89    UPDATE t1 SET b=3 WHERE a=1;
90  }
91  db2 eval { SELECT a, b FROM t1 }
92} {1 1 2 2 3 3}
93do_test 2.2.2 { db eval { SELECT a, b FROM t1 } } {1 3 2 1 3 2}
94
95#-------------------------------------------------------------------------
96#
97reset_db
98db2 close
99forcedelete test.db2
100sqlite3 db2 test.db2
101
102do_test 3.1 {
103  do_common_sql {
104    CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE);
105    INSERT INTO t1 VALUES(1, 1, 1);
106    INSERT INTO t1 VALUES(2, 2, 2);
107    INSERT INTO t1 VALUES(3, 3, 3);
108  }
109} {}
110
111do_test 3.3 {
112  do_then_apply_sql {
113    UPDATE t1 SET b=4 WHERE a=3;
114    UPDATE t1 SET b=3 WHERE a=2;
115    UPDATE t1 SET b=2 WHERE a=1;
116  }
117  compare_db db db2
118} {}
119
120do_test 3.4 {
121  do_then_apply_sql {
122    UPDATE t1 SET b=1 WHERE a=1;
123    UPDATE t1 SET b=2 WHERE a=2;
124    UPDATE t1 SET b=3 WHERE a=3;
125  }
126  compare_db db db2
127} {}
128
129#-------------------------------------------------------------------------
130#
131reset_db
132db2 close
133forcedelete test.db2
134sqlite3 db2 test.db2
135
136do_test 4.1 {
137  do_common_sql {
138    CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
139    INSERT INTO t1 VALUES(1, 1);
140    INSERT INTO t1 VALUES(2, 2);
141    INSERT INTO t1 VALUES(3, 3);
142
143    CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
144    INSERT INTO t2 VALUES(1, 1);
145    INSERT INTO t2 VALUES(2, 2);
146    INSERT INTO t2 VALUES(3, 3);
147  }
148} {}
149
150do_test 4.2 {
151  do_then_apply_sql {
152    UPDATE t1 SET b=4 WHERE a=3;
153    UPDATE t1 SET b=3 WHERE a=2;
154    UPDATE t1 SET b=2 WHERE a=1;
155
156    UPDATE t2 SET b=0 WHERE a=1;
157    UPDATE t2 SET b=1 WHERE a=2;
158    UPDATE t2 SET b=2 WHERE a=3;
159  }
160  compare_db db db2
161} {}
162
163do_test 4.3 {
164  do_then_apply_sql {
165    UPDATE t1 SET b=1 WHERE a=1;
166    UPDATE t1 SET b=2 WHERE a=2;
167    UPDATE t1 SET b=3 WHERE a=3;
168
169    UPDATE t2 SET b=3 WHERE a=3;
170    UPDATE t2 SET b=2 WHERE a=2;
171    UPDATE t2 SET b=1 WHERE a=1;
172  }
173  compare_db db db2
174} {}
175
176finish_test
177
178