Debian Codegraph @

ext/session/sessionalter.test

raw
1 # 2023 October 02
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 # This file implements that the sessions module interacts well with
12 # the ALTER TABLE ADD COLUMN command.
13 #
14
15 if {![info exists testdir]} {
16 set testdir [file join [file dirname [info script]] .. .. test]
17 }
18 source [file join [file dirname [info script]] session_common.tcl]
19 source $testdir/tester.tcl
20
21 ifcapable !session {finish_test; return}
22 set testprefix sessionalter
23
24
25 forcedelete test.db2
26 sqlite3 db2 test.db2
27
28 do_execsql_test 1.0 {
29 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
30 }
31
32 do_execsql_test -db db2 1.1 {
33 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c DEFAULT 1234);
34 }
35
36 do_then_apply_sql {
37 INSERT INTO t1 VALUES(1, 'one');
38 INSERT INTO t1 VALUES(2, 'two');
39 }
40
41 do_execsql_test -db db2 1.2 {
42 SELECT * FROM t1
43 } {
44 1 one 1234
45 2 two 1234
46 }
47
48 do_then_apply_sql {
49 UPDATE t1 SET b='four' WHERE a=2;
50 }
51
52 do_execsql_test -db db2 1.3 {
53 SELECT * FROM t1
54 } {
55 1 one 1234
56 2 four 1234
57 }
58
59 do_then_apply_sql {
60 DELETE FROM t1 WHERE a=1;
61 }
62
63 do_execsql_test -db db2 1.4 {
64 SELECT * FROM t1
65 } {
66 2 four 1234
67 }
68
69 db2 close
70
71 #--------------------------------------------------------------------------
72 reset_db
73
74 do_execsql_test 2.0 {
75 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
76 }
77
78 do_test 2.1 {
79 sqlite3session S db main
80 S attach t1
81 set {} {}
82 } {}
83 do_execsql_test 2.2 {
84 INSERT INTO t1 VALUES(1, 2);
85 ALTER TABLE t1 ADD COLUMN c DEFAULT 'abcd';
86 INSERT INTO t1 VALUES(2, 3, 4);
87 }
88 do_changeset_test 2.3 S {
89 {INSERT t1 0 X.. {} {i 1 i 2 t abcd}}
90 {INSERT t1 0 X.. {} {i 2 i 3 i 4}}
91 }
92
93 do_iterator_test 2.4 {} {
94 DELETE FROM t1 WHERE a=2;
95 ALTER TABLE t1 ADD COLUMN d DEFAULT 'abcd';
96 ALTER TABLE t1 ADD COLUMN e DEFAULT 5;
97 ALTER TABLE t1 ADD COLUMN f DEFAULT 7.2;
98 -- INSERT INTO t1 VALUES(9, 9, 9, 9);
99 } {
100 {DELETE t1 0 X..... {i 2 i 3 i 4 t abcd i 5 f 7.2} {}}
101 }
102
103 #-------------------------------------------------------------------------
104 # Tests of the sqlite3changegroup_xxx() APIs.
105 #
106 reset_db
107 do_execsql_test 3.0 {
108 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
109 CREATE TABLE t2(x PRIMARY KEY, y);
110 CREATE TABLE t3(x, y);
111 CREATE TABLE t4(y PRIMARY KEY, x) WITHOUT ROWID;
112
113 INSERT INTO t1 VALUES(1, 2), (3, 4), (5, 6);
114 INSERT INTO t2 VALUES('one', 'two'), ('three', 'four'), ('five', 'six');
115 INSERT INTO t3 VALUES(1, 2), (3, 4), (5, 6);
116
117 INSERT INTO t4(x, y) VALUES(1, 2), (3, 4), (5, 6);
118 }
119
120 db_save_and_close
121 foreach {tn sql1 at sql2} {
122 1 {
123 INSERT INTO t1(x, y) VALUES(7, 8);
124 } {
125 ALTER TABLE t1 ADD COLUMN z DEFAULT 10;
126 } {
127 UPDATE t1 SET y=11 WHERE x=7;
128 }
129
130 2 {
131 UPDATE t2 SET y='two.two' WHERE x='one';
132 DELETE FROM t2 WHERE x='five';
133 INSERT INTO t2(x, y) VALUES('seven', 'eight');
134 } {
135 ALTER TABLE t2 ADD COLUMN z;
136 ALTER TABLE t2 ADD COLUMN zz;
137 } {
138 }
139
140 3 {
141 DELETE FROM t2 WHERE x='five';
142 } {
143 ALTER TABLE t2 ADD COLUMN z DEFAULT 'xyz';
144 } {
145 }
146
147 4 {
148 UPDATE t2 SET y='two.two' WHERE x='three';
149 } {
150 ALTER TABLE t2 ADD COLUMN z;
151 } {
152 UPDATE t2 SET z='abc' WHERE x='one';
153 }
154
155 5* {
156 UPDATE t2 SET y='two.two' WHERE x='three';
157 } {
158 ALTER TABLE t2 ADD COLUMN z DEFAULT 'defu1';
159 } {
160 }
161
162 6* {
163 INSERT INTO t2(x, y) VALUES('nine', 'ten');
164 } {
165 ALTER TABLE t2 ADD COLUMN z;
166 ALTER TABLE t2 ADD COLUMN a DEFAULT 'eelve';
167 ALTER TABLE t2 ADD COLUMN b DEFAULT x'1234abcd';
168 ALTER TABLE t2 ADD COLUMN c DEFAULT 4.2;
169 ALTER TABLE t2 ADD COLUMN d DEFAULT NULL;
170 } {
171 }
172
173 7 {
174 INSERT INTO t3(x, y) VALUES(7, 8);
175 UPDATE t3 SET y='fourteen' WHERE x=1;
176 DELETE FROM t3 WHERE x=3;
177 } {
178 ALTER TABLE t3 ADD COLUMN c;
179 } {
180 INSERT INTO t3(x, y, c) VALUES(9, 10, 11);
181 }
182
183 8 {
184 INSERT INTO t4(x, y) VALUES(7, 8);
185 UPDATE t4 SET y='fourteen' WHERE x=1;
186 DELETE FROM t4 WHERE x=3;
187 } {
188 ALTER TABLE t4 ADD COLUMN c;
189 } {
190 INSERT INTO t4(x, y, c) VALUES(9, 10, 11);
191 }
192 } {
193 foreach {tn2 cmd} {
194 1 changeset_from_sql
195 2 patchset_from_sql
196 } {
197 db_restore_and_reopen
198
199 set C1 [$cmd $sql1]
200 execsql $at
201 set C2 [$cmd $sql2]
202
203 sqlite3changegroup grp
204 grp schema db main
205 breakpoint
206 grp add $C1
207 grp add $C2
208 set T1 [grp output]
209 grp delete
210
211 db_restore_and_reopen
212 execsql $at
213 set T2 [$cmd "$sql1 ; $sql2"]
214
215 if {[string range $tn end end]!="*"} {
216 do_test 3.1.$tn.$tn2.1 { changeset_to_list $T1 } [changeset_to_list $T2]
217 set testname "$tn.$tn2"
218 } else {
219 set testname "[string range $tn 0 end-1].$tn2"
220 }
221
222 db_restore_and_reopen
223 proc xConflict {args} { return "REPLACE" }
224 sqlite3changeset_apply_v2 db $T1 xConflict
225 set S1 [scksum db main]
226
227 db_restore_and_reopen
228 sqlite3changeset_apply_v2 db $T2 xConflict
229 set S2 [scksum db main]
230
231 # if { $tn==7 } { puts [changeset_to_list $T1] }
232
233 do_test 3.1.$tn.2 { set S1 } $S2
234 }
235 }
236
237
238 finish_test
239