Debian Codegraph @

ext/session/sessionG.test

raw
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
18 if {![info exists testdir]} {
19 set testdir [file join [file dirname [info script]] .. .. test]
20 }
21 source [file join [file dirname [info script]] session_common.tcl]
22 source $testdir/tester.tcl
23 ifcapable !session {finish_test; return}
24 set testprefix sessionG
25
26
27 forcedelete test.db2
28 sqlite3 db2 test.db2
29
30 do_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 -ignorenoop {
38 DELETE FROM t1 WHERE a=1;
39 INSERT INTO t1 VALUES(4, 'one');
40 }
41 compare_db db db2
42 } {}
43
44 do_test 1.1 {
45 do_then_apply_sql -ignorenoop {
46 DELETE FROM t1 WHERE a=4;
47 INSERT INTO t1 VALUES(1, 'one');
48 }
49 compare_db db db2
50 } {}
51
52 do_test 1.2 {
53 execsql { INSERT INTO t1 VALUES(5, 'five') } db2
54 do_then_apply_sql -ignorenoop {
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
61 do_test 1.3 {
62 execsql { SELECT * FROM t1 }
63 } {2 two 3 three 1 one 11 eleven 12 five}
64
65 #-------------------------------------------------------------------------
66 #
67 reset_db
68 db2 close
69 forcedelete test.db2
70 sqlite3 db2 test.db2
71
72 do_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
81 do_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 #
86 # UPDATE 19/05/2026 - it is now possible to apply such an update.
87 #
88 do_then_apply_sql -ignorenoop {
89 UPDATE t1 SET b=0 WHERE a=1;
90 UPDATE t1 SET b=1 WHERE a=2;
91 UPDATE t1 SET b=2 WHERE a=3;
92 UPDATE t1 SET b=3 WHERE a=1;
93 }
94 db2 eval { SELECT a, b FROM t1 }
95 } {1 3 2 1 3 2}
96 do_test 2.2.2 { db eval { SELECT a, b FROM t1 } } {1 3 2 1 3 2}
97
98 #-------------------------------------------------------------------------
99 #
100 reset_db
101 db2 close
102 forcedelete test.db2
103 sqlite3 db2 test.db2
104
105 do_test 3.1 {
106 do_common_sql {
107 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE);
108 INSERT INTO t1 VALUES(1, 1, 1);
109 INSERT INTO t1 VALUES(2, 2, 2);
110 INSERT INTO t1 VALUES(3, 3, 3);
111 }
112 } {}
113
114 do_test 3.3 {
115 do_then_apply_sql -ignorenoop {
116 UPDATE t1 SET b=4 WHERE a=3;
117 UPDATE t1 SET b=3 WHERE a=2;
118 UPDATE t1 SET b=2 WHERE a=1;
119 }
120 compare_db db db2
121 } {}
122
123 do_test 3.4 {
124 do_then_apply_sql -ignorenoop {
125 UPDATE t1 SET b=1 WHERE a=1;
126 UPDATE t1 SET b=2 WHERE a=2;
127 UPDATE t1 SET b=3 WHERE a=3;
128 }
129 compare_db db db2
130 } {}
131
132 #-------------------------------------------------------------------------
133 #
134 reset_db
135 db2 close
136 forcedelete test.db2
137 sqlite3 db2 test.db2
138
139 do_test 4.1 {
140 do_common_sql {
141 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
142 INSERT INTO t1 VALUES(1, 1);
143 INSERT INTO t1 VALUES(2, 2);
144 INSERT INTO t1 VALUES(3, 3);
145
146 CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
147 INSERT INTO t2 VALUES(1, 1);
148 INSERT INTO t2 VALUES(2, 2);
149 INSERT INTO t2 VALUES(3, 3);
150 }
151 } {}
152
153 do_test 4.2 {
154 do_then_apply_sql -ignorenoop {
155 UPDATE t1 SET b=4 WHERE a=3;
156 UPDATE t1 SET b=3 WHERE a=2;
157 UPDATE t1 SET b=2 WHERE a=1;
158
159 UPDATE t2 SET b=0 WHERE a=1;
160 UPDATE t2 SET b=1 WHERE a=2;
161 UPDATE t2 SET b=2 WHERE a=3;
162 }
163 compare_db db db2
164 } {}
165
166 do_test 4.3 {
167 do_then_apply_sql -ignorenoop {
168 UPDATE t1 SET b=1 WHERE a=1;
169 UPDATE t1 SET b=2 WHERE a=2;
170 UPDATE t1 SET b=3 WHERE a=3;
171
172 UPDATE t2 SET b=3 WHERE a=3;
173 UPDATE t2 SET b=2 WHERE a=2;
174 UPDATE t2 SET b=1 WHERE a=1;
175 }
176 compare_db db db2
177 } {}
178
179 #-------------------------------------------------------------------------
180 reset_db
181 catch { db2 close }
182 forcedelete test.db2
183 sqlite3 db2 test.db2
184
185 do_execsql_test 5.0.1 {
186 CREATE TABLE t1(a PRIMARY KEY, b, c);
187 CREATE TABLE t2(a, b, c PRIMARY KEY);
188 CREATE TABLE t3(a, b PRIMARY KEY, c);
189 }
190 do_execsql_test -db db2 5.0.2 {
191 CREATE TABLE t1(a PRIMARY KEY, b, c);
192 CREATE TABLE t2(a, b, c);
193 CREATE TABLE t3(a, b PRIMARY KEY, c);
194 }
195
196 do_test 5.1 {
197 do_then_apply_sql -ignorenoop {
198 INSERT INTO t1 VALUES(1, 2, 3);
199 INSERT INTO t2 VALUES(4, 5, 6);
200 INSERT INTO t3 VALUES(7, 8, 9);
201 }
202
203 db2 eval {
204 SELECT * FROM t1;
205 SELECT * FROM t2;
206 SELECT * FROM t3;
207 }
208 } {1 2 3 7 8 9}
209
210 #-------------------------------------------------------------------------
211
212 reset_db
213 db func number_name number_name
214 do_execsql_test 6.0 {
215 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
216 CREATE UNIQUE INDEX t1b ON t1(b);
217 WITH s(i) AS (
218 SELECT 1
219 UNION ALL
220 SELECT i+1 FROM s WHERE i<1000
221 )
222 INSERT INTO t1 SELECT i, number_name(i) FROM s;
223 }
224
225 do_test 6.1 {
226 db eval BEGIN
227 set ::C [changeset_from_sql {
228 DELETE FROM t1;
229 WITH s(i) AS (
230 SELECT 1
231 UNION ALL
232 SELECT i+1 FROM s WHERE i<1000
233 )
234 INSERT INTO t1 SELECT i, number_name(i+1) FROM s;
235 }]
236 db eval ROLLBACK
237 execsql { SELECT count(*) FROM t1 WHERE number_name(a) IS NOT b }
238 } {0}
239
240 proc xConflict {args} { exit ; return "OMIT" }
241 do_test 6.2 {
242 sqlite3changeset_apply db $C xConflict
243 } {}
244
245 do_execsql_test 6.3 { SELECT count(*) FROM t1; } {1000}
246 do_execsql_test 6.4 {
247 SELECT count(*) FROM t1 WHERE number_name(a+1) IS NOT b;
248 } {0}
249
250 # db eval { SELECT * FROM t1 } { puts "$a || $b" }
251
252
253 finish_test