forked from sqliteai/sqlite-sync
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path25_boolean_type_issue.sql
More file actions
241 lines (190 loc) · 7.65 KB
/
25_boolean_type_issue.sql
File metadata and controls
241 lines (190 loc) · 7.65 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
-- Test: BOOLEAN Type Roundtrip
-- This test verifies that BOOLEAN columns sync correctly.
-- BOOLEAN values are encoded as INT8 in sync payloads. The cloudsync extension
-- provides a custom cast (bigint AS boolean) to enable this.
--
-- See plans/ANALYSIS_BOOLEAN_TYPE_CONVERSION.md for details.
\set testid '25'
\ir helper_test_init.sql
\connect postgres
\ir helper_psql_conn_setup.sql
DROP DATABASE IF EXISTS cloudsync_test_25a;
DROP DATABASE IF EXISTS cloudsync_test_25b;
CREATE DATABASE cloudsync_test_25a;
CREATE DATABASE cloudsync_test_25b;
-- Setup Database A
\connect cloudsync_test_25a
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE bool_test (
id TEXT PRIMARY KEY NOT NULL,
flag BOOLEAN,
name TEXT
);
SELECT cloudsync_init('bool_test', 'CLS', true) AS _init_a \gset
-- Setup Database B
\connect cloudsync_test_25b
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE bool_test (
id TEXT PRIMARY KEY NOT NULL,
flag BOOLEAN,
name TEXT
);
SELECT cloudsync_init('bool_test', 'CLS', true) AS _init_b \gset
-- ============================================================================
-- STEP 1: Insert NULL BOOLEAN first (triggers SPI plan caching)
-- ============================================================================
\echo [INFO] (:testid) === STEP 1: NULL BOOLEAN ===
\connect cloudsync_test_25a
INSERT INTO bool_test (id, flag, name) VALUES ('row1', NULL, 'null_flag');
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload1_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() \gset
SELECT max(db_version) AS db_version FROM bool_test_cloudsync \gset
\connect cloudsync_test_25b
SELECT cloudsync_payload_apply(decode(:'payload1_hex', 'hex')) AS apply1 \gset
SELECT (SELECT flag IS NULL AND name = 'null_flag' FROM bool_test WHERE id = 'row1') AS step1_ok \gset
\if :step1_ok
\echo [PASS] (:testid) Step 1: NULL BOOLEAN preserved
\else
\echo [FAIL] (:testid) Step 1: NULL BOOLEAN not preserved
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- STEP 2: Insert TRUE BOOLEAN (tests INT8 -> BOOLEAN cast after NULL)
-- ============================================================================
\echo [INFO] (:testid) === STEP 2: TRUE BOOLEAN after NULL ===
\connect cloudsync_test_25a
INSERT INTO bool_test (id, flag, name) VALUES ('row2', true, 'true_flag');
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload2_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() AND db_version > :db_version \gset
SELECT max(db_version) AS db_version FROM bool_test_cloudsync \gset
\connect cloudsync_test_25b
SELECT cloudsync_payload_apply(decode(:'payload2_hex', 'hex')) AS apply2 \gset
SELECT (SELECT flag = true AND name = 'true_flag' FROM bool_test WHERE id = 'row2') AS step2_ok \gset
\if :step2_ok
\echo [PASS] (:testid) Step 2: TRUE BOOLEAN preserved after NULL
\else
\echo [FAIL] (:testid) Step 2: TRUE BOOLEAN not preserved
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- STEP 3: Insert FALSE BOOLEAN
-- ============================================================================
\echo [INFO] (:testid) === STEP 3: FALSE BOOLEAN ===
\connect cloudsync_test_25a
INSERT INTO bool_test (id, flag, name) VALUES ('row3', false, 'false_flag');
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload3_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() AND db_version > :db_version \gset
SELECT max(db_version) AS db_version FROM bool_test_cloudsync \gset
\connect cloudsync_test_25b
SELECT cloudsync_payload_apply(decode(:'payload3_hex', 'hex')) AS apply3 \gset
SELECT (SELECT flag = false AND name = 'false_flag' FROM bool_test WHERE id = 'row3') AS step3_ok \gset
\if :step3_ok
\echo [PASS] (:testid) Step 3: FALSE BOOLEAN preserved
\else
\echo [FAIL] (:testid) Step 3: FALSE BOOLEAN not preserved
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- STEP 4: Update TRUE to FALSE
-- ============================================================================
\echo [INFO] (:testid) === STEP 4: Update TRUE to FALSE ===
\connect cloudsync_test_25a
UPDATE bool_test SET flag = false WHERE id = 'row2';
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload4_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() AND db_version > :db_version \gset
SELECT max(db_version) AS db_version FROM bool_test_cloudsync \gset
\connect cloudsync_test_25b
SELECT cloudsync_payload_apply(decode(:'payload4_hex', 'hex')) AS apply4 \gset
SELECT (SELECT flag = false FROM bool_test WHERE id = 'row2') AS step4_ok \gset
\if :step4_ok
\echo [PASS] (:testid) Step 4: Update TRUE to FALSE synced
\else
\echo [FAIL] (:testid) Step 4: Update TRUE to FALSE not synced
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- STEP 5: Update NULL to TRUE
-- ============================================================================
\echo [INFO] (:testid) === STEP 5: Update NULL to TRUE ===
\connect cloudsync_test_25a
UPDATE bool_test SET flag = true WHERE id = 'row1';
SELECT encode(
cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq),
'hex'
) AS payload5_hex
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() AND db_version > :db_version \gset
SELECT max(db_version) AS db_version FROM bool_test_cloudsync \gset
\connect cloudsync_test_25b
SELECT cloudsync_payload_apply(decode(:'payload5_hex', 'hex')) AS apply5 \gset
SELECT (SELECT flag = true FROM bool_test WHERE id = 'row1') AS step5_ok \gset
\if :step5_ok
\echo [PASS] (:testid) Step 5: Update NULL to TRUE synced
\else
\echo [FAIL] (:testid) Step 5: Update NULL to TRUE not synced
SELECT (:fail::int + 1) AS fail \gset
\endif
-- ============================================================================
-- STEP 6: Verify final state with hash comparison
-- ============================================================================
\echo [INFO] (:testid) === STEP 6: Verify data integrity ===
\connect cloudsync_test_25a
SELECT md5(
COALESCE(
string_agg(
id || ':' || COALESCE(flag::text, 'NULL') || ':' || COALESCE(name, 'NULL'),
'|' ORDER BY id
),
''
)
) AS hash_a FROM bool_test \gset
\connect cloudsync_test_25b
SELECT md5(
COALESCE(
string_agg(
id || ':' || COALESCE(flag::text, 'NULL') || ':' || COALESCE(name, 'NULL'),
'|' ORDER BY id
),
''
)
) AS hash_b FROM bool_test \gset
SELECT (:'hash_a' = :'hash_b') AS hashes_match \gset
\if :hashes_match
\echo [PASS] (:testid) Data integrity verified - hashes match
\else
\echo [FAIL] (:testid) Data integrity check failed
SELECT (:fail::int + 1) AS fail \gset
\endif
SELECT COUNT(*) AS count_b FROM bool_test \gset
SELECT (:count_b = 3) AS count_ok \gset
\if :count_ok
\echo [PASS] (:testid) Row count correct (3 rows)
\else
\echo [FAIL] (:testid) Row count incorrect - expected 3, got :count_b
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Cleanup
\ir helper_test_cleanup.sql
\if :should_cleanup
\connect postgres
DROP DATABASE IF EXISTS cloudsync_test_25a;
DROP DATABASE IF EXISTS cloudsync_test_25b;
\endif