forked from sqliteai/sqlite-sync
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path31_alter_table_sync.sql
More file actions
383 lines (312 loc) · 12.9 KB
/
31_alter_table_sync.sql
File metadata and controls
383 lines (312 loc) · 12.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
-- Alter Table Sync Test
-- Tests cloudsync_begin_alter and cloudsync_commit_alter functions.
-- Verifies that schema changes (add column) are handled correctly
-- and data syncs after alteration.
\set testid '31'
\ir helper_test_init.sql
\connect postgres
\ir helper_psql_conn_setup.sql
-- Cleanup and create test databases
DROP DATABASE IF EXISTS cloudsync_test_31a;
DROP DATABASE IF EXISTS cloudsync_test_31b;
CREATE DATABASE cloudsync_test_31a;
CREATE DATABASE cloudsync_test_31b;
-- ============================================================================
-- Setup Database A
-- ============================================================================
\connect cloudsync_test_31a
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE products (
id UUID PRIMARY KEY,
name TEXT NOT NULL DEFAULT '',
price DOUBLE PRECISION NOT NULL DEFAULT 0.0,
quantity INTEGER NOT NULL DEFAULT 0
);
SELECT cloudsync_init('products', 'CLS', false) AS _init_a \gset
INSERT INTO products VALUES ('11111111-1111-1111-1111-111111111111', 'Product A1', 10.99, 100);
INSERT INTO products VALUES ('22222222-2222-2222-2222-222222222222', 'Product A2', 20.50, 200);
-- ============================================================================
-- Setup Database B with same schema
-- ============================================================================
\connect cloudsync_test_31b
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE products (
id UUID PRIMARY KEY,
name TEXT NOT NULL DEFAULT '',
price DOUBLE PRECISION NOT NULL DEFAULT 0.0,
quantity INTEGER NOT NULL DEFAULT 0
);
SELECT cloudsync_init('products', 'CLS', false) AS _init_b \gset
INSERT INTO products VALUES ('33333333-3333-3333-3333-333333333333', 'Product B1', 30.00, 300);
INSERT INTO products VALUES ('44444444-4444-4444-4444-444444444444', 'Product B2', 40.75, 400);
-- ============================================================================
-- Initial Sync: A -> B and B -> A
-- ============================================================================
\echo [INFO] (:testid) === Initial Sync Before ALTER ===
-- Encode payload from A
\connect cloudsync_test_31a
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('products', 'CLS', false) AS _reinit \gset
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload_a_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() \gset
-- Apply A's payload to B, encode B's payload
\connect cloudsync_test_31b
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('products', 'CLS', false) AS _reinit \gset
SELECT cloudsync_payload_apply(decode(:'payload_a_hex', 'hex')) AS apply_a_to_b \gset
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload_b_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() \gset
-- Apply B's payload to A, verify initial sync
\connect cloudsync_test_31a
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('products', 'CLS', false) AS _reinit \gset
SELECT cloudsync_payload_apply(decode(:'payload_b_hex', 'hex')) AS apply_b_to_a \gset
SELECT COUNT(*) AS count_a_initial FROM products \gset
\connect cloudsync_test_31b
\ir helper_psql_conn_setup.sql
SELECT COUNT(*) AS count_b_initial FROM products \gset
SELECT (:count_a_initial = 4 AND :count_b_initial = 4) AS initial_sync_ok \gset
\if :initial_sync_ok
\echo [PASS] (:testid) Initial sync complete - both databases have 4 rows
\else
\echo [FAIL] (:testid) Initial sync failed - A: :count_a_initial, B: :count_b_initial
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- ALTER TABLE on Database A (begin_alter + ALTER + commit_alter on SAME connection)
-- ============================================================================
\echo [INFO] (:testid) === ALTER TABLE on Database A ===
\connect cloudsync_test_31a
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('products', 'CLS', false) AS _reinit \gset
SELECT cloudsync_begin_alter('products') AS begin_alter_a \gset
\if :begin_alter_a
\echo [PASS] (:testid) cloudsync_begin_alter succeeded on Database A
\else
\echo [FAIL] (:testid) cloudsync_begin_alter failed on Database A
SELECT (:fail::int + 1) AS fail \gset
\endif
ALTER TABLE products ADD COLUMN description TEXT NOT NULL DEFAULT '';
SELECT cloudsync_commit_alter('products') AS commit_alter_a \gset
\if :commit_alter_a
\echo [PASS] (:testid) cloudsync_commit_alter succeeded on Database A
\else
\echo [FAIL] (:testid) cloudsync_commit_alter failed on Database A
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Insert and update post-ALTER data on A
INSERT INTO products (id, name, price, quantity, description)
VALUES ('55555555-5555-5555-5555-555555555555', 'New Product A', 55.55, 555, 'Added after alter on A');
UPDATE products SET description = 'Updated on A' WHERE id = '11111111-1111-1111-1111-111111111111';
UPDATE products SET quantity = 150 WHERE id = '11111111-1111-1111-1111-111111111111';
-- Encode post-ALTER payload from A
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload_a2_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() \gset
SELECT (length(:'payload_a2_hex') > 0) AS payload_a2_created \gset
\if :payload_a2_created
\echo [PASS] (:testid) Post-alter payload encoded from Database A
\else
\echo [FAIL] (:testid) Post-alter payload empty from Database A
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- ALTER TABLE on Database B (begin_alter + ALTER + commit_alter on SAME connection)
-- Apply A's payload, insert/update, encode B's payload
-- ============================================================================
\echo [INFO] (:testid) === ALTER TABLE on Database B ===
\connect cloudsync_test_31b
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('products', 'CLS', false) AS _reinit \gset
SELECT cloudsync_begin_alter('products') AS begin_alter_b \gset
\if :begin_alter_b
\echo [PASS] (:testid) cloudsync_begin_alter succeeded on Database B
\else
\echo [FAIL] (:testid) cloudsync_begin_alter failed on Database B
SELECT (:fail::int + 1) AS fail \gset
\endif
ALTER TABLE products ADD COLUMN description TEXT NOT NULL DEFAULT '';
SELECT cloudsync_commit_alter('products') AS commit_alter_b \gset
\if :commit_alter_b
\echo [PASS] (:testid) cloudsync_commit_alter succeeded on Database B
\else
\echo [FAIL] (:testid) cloudsync_commit_alter failed on Database B
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Insert and update post-ALTER data on B
INSERT INTO products (id, name, price, quantity, description)
VALUES ('66666666-6666-6666-6666-666666666666', 'New Product B', 66.66, 666, 'Added after alter on B');
UPDATE products SET description = 'Updated on B' WHERE id = '33333333-3333-3333-3333-333333333333';
UPDATE products SET quantity = 350 WHERE id = '33333333-3333-3333-3333-333333333333';
-- Apply A's post-alter payload to B
SELECT cloudsync_payload_apply(decode(:'payload_a2_hex', 'hex')) AS apply_a2_to_b \gset
SELECT (:apply_a2_to_b >= 0) AS apply_a2_ok \gset
\if :apply_a2_ok
\echo [PASS] (:testid) Post-alter payload from A applied to B
\else
\echo [FAIL] (:testid) Post-alter payload from A failed to apply to B: :apply_a2_to_b
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Encode post-ALTER payload from B
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload_b2_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() \gset
-- ============================================================================
-- Apply B's payload to A, then verify final state
-- ============================================================================
\echo [INFO] (:testid) === Apply B payload to A and verify ===
\connect cloudsync_test_31a
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('products', 'CLS', false) AS _reinit \gset
SELECT cloudsync_payload_apply(decode(:'payload_b2_hex', 'hex')) AS apply_b2_to_a \gset
SELECT (:apply_b2_to_a >= 0) AS apply_b2_ok \gset
\if :apply_b2_ok
\echo [PASS] (:testid) Post-alter payload from B applied to A
\else
\echo [FAIL] (:testid) Post-alter payload from B failed to apply to A: :apply_b2_to_a
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- Verify final state
-- ============================================================================
\echo [INFO] (:testid) === Verify Final State ===
-- Compute hash of Database A
SELECT md5(
COALESCE(
string_agg(
id::text || ':' ||
COALESCE(name, 'NULL') || ':' ||
COALESCE(price::text, 'NULL') || ':' ||
COALESCE(quantity::text, 'NULL') || ':' ||
COALESCE(description, 'NULL'),
'|' ORDER BY id
),
''
)
) AS hash_a_final FROM products \gset
\echo [INFO] (:testid) Database A final hash: :hash_a_final
-- Row count on A
SELECT COUNT(*) AS count_a_final FROM products \gset
-- Verify new row from B exists in A
SELECT COUNT(*) = 1 AS new_row_b_ok
FROM products
WHERE id = '66666666-6666-6666-6666-666666666666'
AND name = 'New Product B'
AND price = 66.66
AND quantity = 666
AND description = 'Added after alter on B' \gset
-- Verify updated row from B synced to A
SELECT COUNT(*) = 1 AS updated_row_b_ok
FROM products
WHERE id = '33333333-3333-3333-3333-333333333333'
AND description = 'Updated on B'
AND quantity = 350 \gset
\connect cloudsync_test_31b
\ir helper_psql_conn_setup.sql
-- Compute hash of Database B
SELECT md5(
COALESCE(
string_agg(
id::text || ':' ||
COALESCE(name, 'NULL') || ':' ||
COALESCE(price::text, 'NULL') || ':' ||
COALESCE(quantity::text, 'NULL') || ':' ||
COALESCE(description, 'NULL'),
'|' ORDER BY id
),
''
)
) AS hash_b_final FROM products \gset
\echo [INFO] (:testid) Database B final hash: :hash_b_final
-- Row count on B
SELECT COUNT(*) AS count_b_final FROM products \gset
-- Verify new row from A exists in B
SELECT COUNT(*) = 1 AS new_row_a_ok
FROM products
WHERE id = '55555555-5555-5555-5555-555555555555'
AND name = 'New Product A'
AND price = 55.55
AND quantity = 555
AND description = 'Added after alter on A' \gset
-- Verify updated row from A synced to B
SELECT COUNT(*) = 1 AS updated_row_a_ok
FROM products
WHERE id = '11111111-1111-1111-1111-111111111111'
AND description = 'Updated on A'
AND quantity = 150 \gset
-- Verify new column exists
SELECT COUNT(*) = 1 AS description_column_exists
FROM information_schema.columns
WHERE table_name = 'products' AND column_name = 'description' \gset
-- ============================================================================
-- Report results
-- ============================================================================
-- Compare final hashes
SELECT (:'hash_a_final' = :'hash_b_final') AS final_hashes_match \gset
\if :final_hashes_match
\echo [PASS] (:testid) Final data integrity verified - hashes match after ALTER
\else
\echo [FAIL] (:testid) Final data integrity check failed - A: :hash_a_final, B: :hash_b_final
SELECT (:fail::int + 1) AS fail \gset
\endif
SELECT (:count_a_final = 6 AND :count_b_final = 6) AS row_counts_ok \gset
\if :row_counts_ok
\echo [PASS] (:testid) Row counts match (6 rows each)
\else
\echo [FAIL] (:testid) Row counts mismatch - A: :count_a_final, B: :count_b_final
SELECT (:fail::int + 1) AS fail \gset
\endif
\if :new_row_a_ok
\echo [PASS] (:testid) New row from A synced to B with new schema
\else
\echo [FAIL] (:testid) New row from A not found or incorrect in B
SELECT (:fail::int + 1) AS fail \gset
\endif
\if :new_row_b_ok
\echo [PASS] (:testid) New row from B synced to A with new schema
\else
\echo [FAIL] (:testid) New row from B not found or incorrect in A
SELECT (:fail::int + 1) AS fail \gset
\endif
\if :updated_row_a_ok
\echo [PASS] (:testid) Updated row from A synced with new column values
\else
\echo [FAIL] (:testid) Updated row from A not synced correctly
SELECT (:fail::int + 1) AS fail \gset
\endif
\if :updated_row_b_ok
\echo [PASS] (:testid) Updated row from B synced with new column values
\else
\echo [FAIL] (:testid) Updated row from B not synced correctly
SELECT (:fail::int + 1) AS fail \gset
\endif
\if :description_column_exists
\echo [PASS] (:testid) Added column 'description' exists
\else
\echo [FAIL] (:testid) Added column 'description' not found
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- Cleanup
-- ============================================================================
\ir helper_test_cleanup.sql
\if :should_cleanup
DROP DATABASE IF EXISTS cloudsync_test_31a;
DROP DATABASE IF EXISTS cloudsync_test_31b;
\endif