| blob | f482d015208a1aee710abe1109ca98afa898c93d |
1 # 2017 February 04
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 # Tests for the sessions module. Specifically, that a changeset can
13 # be applied after ALTER TABLE ADD COLUMN has been used to add
14 # columns to tables.
15 #
17 if {![info exists testdir]} {
18 set testdir [file join [file dirname [info script]] .. .. test]
19 }
20 source [file join [file dirname [info script]] session_common.tcl]
21 source $testdir/tester.tcl
22 ifcapable !session {finish_test; return}
24 set testprefix sessionat
26 db close
27 sqlite3_shutdown
28 test_sqlite3_log log
29 proc log {code msg} { lappend ::log $code $msg }
31 proc reset_test {} {
32 catch { db close }
33 catch { db2 close }
34 forcedelete test.db test.db2
35 sqlite3 db test.db
36 sqlite3 db2 test.db2
37 }
40 # Run all tests in this file twice. Once with "WITHOUT ROWID", and once
41 # with regular rowid tables.
42 #
43 # ?.1.*: Test that PK inconsistencies are detected if one or more of the PK
44 # columns are not present in the changeset.
45 #
46 # ?.2.*: Test that it is not possible to apply a changeset with N columns
47 # to a db with fewer than N columns.
48 #
49 # ?.3.*: Test some INSERT, UPDATE and DELETE operations that do not
50 # require conflict handling.
51 #
52 # ?.4.*: Test some INSERT, UPDATE and DELETE operations that do require
53 # conflict handling.
54 #
55 # ?.5.*: Test that attempting to concat two changesets with different
56 # numbers of columns for the same table is an error.
57 #
58 foreach {tn trailing} {
59 sessionat-ipk ""
60 sessionat-wor " WITHOUT ROWID "
61 } {
62 eval [string map [list %WR% $trailing] {
63 reset_test
65 #-----------------------------------------------------------------------
66 do_execsql_test $tn.1.0 {
67 CREATE TABLE t1(a, b, PRIMARY KEY(a)) %WR%;
68 }
69 do_execsql_test -db db2 $tn.1.1 {
70 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) %WR%;
71 }
72 do_test $tn.1.2 {
73 set ::log {}
74 do_then_apply_sql { INSERT INTO t1 VALUES('one', 'two') }
75 set ::log
76 } [list \
77 SQLITE_SCHEMA {sqlite3changeset_apply(): primary key mismatch for table t1}
78 ]
79 do_execsql_test $tn.1.3 { SELECT * FROM t1 } {one two}
80 do_execsql_test -db db2 $tn.1.4 { SELECT * FROM t1 } {}
82 #-----------------------------------------------------------------------
83 do_execsql_test $tn.2.0 {
84 CREATE TABLE t2(x, y, z, PRIMARY KEY(x)) %WR%;
85 }
86 do_execsql_test -db db2 $tn.2.1 {
87 CREATE TABLE t2(x, y, PRIMARY KEY(x)) %WR%;
88 }
89 do_test $tn.2.2 {
90 db cache flush
91 set ::log {}
92 do_then_apply_sql { INSERT INTO t2 VALUES(1, 2, 3) }
93 set ::log
94 } [list SQLITE_SCHEMA \
95 {sqlite3changeset_apply(): table t2 has 2 columns, expected 3 or more}
96 ]
97 do_execsql_test $tn.2.3 { SELECT * FROM t2 } {1 2 3}
98 do_execsql_test -db db2 $tn.2.4 { SELECT * FROM t2 } {}
100 #-----------------------------------------------------------------------
101 do_execsql_test $tn.3.0 {
102 CREATE TABLE t3(a, b, PRIMARY KEY(b)) %WR%;
103 }
104 do_execsql_test -db db2 $tn.3.1 {
105 CREATE TABLE t3(a, b, c DEFAULT 'D', PRIMARY KEY(b)) %WR%;
106 }
107 do_test $tn.3.2 {
108 do_then_apply_sql {
109 INSERT INTO t3 VALUES(1, 2);
110 INSERT INTO t3 VALUES(3, 4);
111 INSERT INTO t3 VALUES(5, 6);
112 };
113 db2 eval {SELECT * FROM t3}
114 } {1 2 D 3 4 D 5 6 D}
115 do_test $tn.3.3 {
116 do_then_apply_sql {
117 UPDATE t3 SET a=45 WHERE b=4;
118 DELETE FROM t3 WHERE a=5;
119 };
120 db2 eval {SELECT * FROM t3}
121 } {1 2 D 45 4 D}
123 #-----------------------------------------------------------------------
124 # 4.1: INSERT statements
125 # 4.2: DELETE statements
126 # 4.3: UPDATE statements
127 #
128 do_execsql_test $tn.4.1.0 {
129 CREATE TABLE t4(x INTEGER PRIMARY KEY, y) %WR%;
130 }
131 do_execsql_test -db db2 $tn.4.1.1 {
132 CREATE TABLE t4(x INTEGER PRIMARY KEY, y, z) %WR%;
133 INSERT INTO t4 VALUES(1, 2, 3);
134 INSERT INTO t4 VALUES(4, 5, 6);
135 }
136 do_conflict_test $tn.4.1.2 -tables t4 -sql {
137 INSERT INTO t4 VALUES(10, 20);
138 INSERT INTO t4 VALUES(4, 11);
139 } -conflicts {
140 {INSERT t4 CONFLICT {i 4 i 11} {i 4 i 5}}
141 }
142 do_execsql_test -db db2 $tn.4.1.3 {
143 SELECT * FROM t4 ORDER BY x
144 } {1 2 3 4 5 6 10 20 {}}
145 do_conflict_test $tn.4.1.4 -policy REPLACE -tables t4 -sql {
146 INSERT INTO t4 VALUES(1, 11);
147 } -conflicts {
148 {INSERT t4 CONFLICT {i 1 i 11} {i 1 i 2}}
149 }
150 do_execsql_test -db db2 $tn.4.1.5 {
151 SELECT * FROM t4 ORDER BY x
152 } {1 11 {} 4 5 6 10 20 {}}
154 do_execsql_test $tn.4.2.0 {
155 DELETE FROM t4;
156 INSERT INTO t4 VALUES(1, 'A');
157 INSERT INTO t4 VALUES(2, 'B');
158 INSERT INTO t4 VALUES(3, 'C');
159 INSERT INTO t4 VALUES(4, 'D');
160 }
161 do_execsql_test -db db2 $tn.4.2.1 {
162 DELETE FROM t4;
163 INSERT INTO t4 VALUES(1, 'A', 'a');
164 INSERT INTO t4 VALUES(3, 'C', 'c');
165 INSERT INTO t4 VALUES(4, 'E', 'd');
166 }
167 do_conflict_test $tn.4.2.2 -tables t4 -sql {
168 DELETE FROM t4 WHERE x=2;
169 DELETE FROM t4 WHERE x=4;
170 } -conflicts {
171 {DELETE t4 NOTFOUND {i 2 t B}}
172 {DELETE t4 DATA {i 4 t D} {i 4 t E}}
173 }
175 do_execsql_test $tn.4.3.0 {
176 CREATE TABLE t5(a, b, c PRIMARY KEY) %WR%;
177 INSERT INTO t5 VALUES(1,1,1), (2,2,2), (3,3,3), (4,4,4);
178 }
179 do_execsql_test -db db2 $tn.4.3.1 {
180 CREATE TABLE t5(a, b, c PRIMARY KEY, d CHECK(b!=10)) %WR%;
181 INSERT INTO t5 VALUES (2,2,2,2), (3,8,3,3), (4,4,4,4);
182 }
183 do_conflict_test $tn.4.3.2 -tables t5 -sql {
184 UPDATE t5 SET a=4 WHERE c=1;
185 UPDATE t5 SET b=9 WHERE c=3;
186 UPDATE t5 SET b=10 WHERE c=2;
187 } -conflicts {
188 {UPDATE t5 NOTFOUND {i 1 {} {} i 1} {i 4 {} {} {} {}}}
189 {UPDATE t5 DATA {{} {} i 3 i 3} {{} {} i 9 {} {}} {i 3 i 8 i 3}}
190 {UPDATE t5 CONSTRAINT {{} {} i 2 i 2} {{} {} i 10 {} {}}}
191 }
193 #-----------------------------------------------------------------------
194 do_execsql_test $tn.5.0 {
195 CREATE TABLE t6(a, b, c, PRIMARY KEY(a, b)) %WR%;
196 }
197 do_execsql_test -db db2 $tn.5.1 {
198 CREATE TABLE t6(a, b, c, d, e, PRIMARY KEY(a, b)) %WR%;
199 }
200 do_test $tn.5.2 {
201 set c1 [sql_exec_changeset db {
202 INSERT INTO t6 VALUES(1, 1, 1);
203 INSERT INTO t6 VALUES(2, 2, 2);
204 }]
205 set c2 [sql_exec_changeset db2 {
206 INSERT INTO t6 VALUES(3, 3, 3, 3, 3);
207 INSERT INTO t6 VALUES(4, 4, 4, 4, 4);
208 }]
209 list [catch { sqlite3changeset_concat $c1 $c2} msg] $msg
210 } {1 SQLITE_SCHEMA}
212 #-----------------------------------------------------------------------
213 db2 close
214 sqlite3 db2 test.db
215 do_execsql_test $tn.6.0 {
216 CREATE TABLE t7(a INTEGER PRIMARY KEY, b) %WR%;
217 INSERT INTO t7 VALUES(1, 1);
218 INSERT INTO t7 VALUES(2, 2);
219 INSERT INTO t7 VALUES(3, 3);
220 }
222 do_test $tn.6.1 {
223 set c1 [sql_exec_changeset db {
224 INSERT INTO t7 VALUES(4, 4);
225 DELETE FROM t7 WHERE a=1;
226 UPDATE t7 SET b=222 WHERE a=2;
227 }]
228 set cinv [sqlite3changeset_invert $c1]
229 execsql { SELECT * FROM t7 }
230 } {2 222 3 3 4 4}
232 do_execsql_test -db db2 $tn.6.2 {
233 ALTER TABLE t7 ADD COLUMN c DEFAULT 'ccc'
234 }
236 proc xConfict {args} { return "OMIT" }
237 do_test $tn.6.3 {
238 sqlite3changeset_apply db $cinv xConflict
239 execsql { SELECT * FROM t7 }
240 } {1 1 ccc 2 2 ccc 3 3 ccc}
241 }]
242 }
245 finish_test