forked from sqliteai/sqlite-sync
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path45_pg_specific_types.sql
More file actions
176 lines (154 loc) · 4.58 KB
/
45_pg_specific_types.sql
File metadata and controls
176 lines (154 loc) · 4.58 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
-- Test PostgreSQL-specific type roundtrips
-- Covers JSONB, TIMESTAMPTZ, NUMERIC with precision, BYTEA
\set testid '45'
\ir helper_test_init.sql
\connect postgres
\ir helper_psql_conn_setup.sql
DROP DATABASE IF EXISTS cloudsync_test_45_a;
DROP DATABASE IF EXISTS cloudsync_test_45_b;
CREATE DATABASE cloudsync_test_45_a;
CREATE DATABASE cloudsync_test_45_b;
-- Setup Database A
\connect cloudsync_test_45_a
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE typed_tbl (
id TEXT PRIMARY KEY,
json_col JSONB,
ts_col TIMESTAMPTZ,
num_col NUMERIC(18, 6),
bin_col BYTEA
);
SELECT cloudsync_init('typed_tbl', 'CLS', true) AS _init_a \gset
INSERT INTO typed_tbl VALUES (
'row1',
'{"key": "value", "nested": {"arr": [1, 2, 3]}}',
'2025-01-15 10:30:00+00',
123456.789012,
'\x48656c6c6f'
);
INSERT INTO typed_tbl VALUES (
'row2',
'[1, "two", null, true, false]',
'2024-06-30 23:59:59.999999+05:30',
-999999.123456,
'\xdeadbeef'
);
INSERT INTO typed_tbl VALUES (
'row3',
'null',
'1970-01-01 00:00:00+00',
0.000000,
'\x'
);
-- Setup Database B
\connect cloudsync_test_45_b
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE typed_tbl (
id TEXT PRIMARY KEY,
json_col JSONB,
ts_col TIMESTAMPTZ,
num_col NUMERIC(18, 6),
bin_col BYTEA
);
SELECT cloudsync_init('typed_tbl', 'CLS', true) AS _init_b \gset
-- Encode and apply A -> B
\connect cloudsync_test_45_a
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('typed_tbl', 'CLS', true) 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
FROM cloudsync_changes
WHERE site_id = cloudsync_siteid() \gset
\connect cloudsync_test_45_b
\ir helper_psql_conn_setup.sql
SELECT cloudsync_init('typed_tbl', 'CLS', true) AS _reinit \gset
SELECT cloudsync_payload_apply(decode(:'payload_a', 'hex')) AS apply_a_to_b \gset
-- Verify row count
SELECT COUNT(*) AS count_b FROM typed_tbl \gset
SELECT (:count_b::int = 3) AS count_ok \gset
\if :count_ok
\echo [PASS] (:testid) All 3 rows synced to B
\else
\echo [FAIL] (:testid) Expected 3 rows, got :count_b
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Verify JSONB roundtrip
SELECT COUNT(*) = 1 AS jsonb_ok
FROM typed_tbl
WHERE id = 'row1'
AND json_col @> '{"key": "value"}'
AND json_col -> 'nested' -> 'arr' = '[1, 2, 3]'::jsonb \gset
\if :jsonb_ok
\echo [PASS] (:testid) JSONB roundtrip correct
\else
\echo [FAIL] (:testid) JSONB data mismatch
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Verify TIMESTAMPTZ roundtrip
SELECT COUNT(*) = 1 AS ts_ok
FROM typed_tbl
WHERE id = 'row1'
AND ts_col = '2025-01-15 10:30:00+00'::timestamptz \gset
\if :ts_ok
\echo [PASS] (:testid) TIMESTAMPTZ roundtrip correct
\else
\echo [FAIL] (:testid) TIMESTAMPTZ data mismatch
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Verify NUMERIC roundtrip
SELECT COUNT(*) = 1 AS num_ok
FROM typed_tbl
WHERE id = 'row1'
AND num_col = 123456.789012 \gset
\if :num_ok
\echo [PASS] (:testid) NUMERIC(18,6) roundtrip correct
\else
\echo [FAIL] (:testid) NUMERIC data mismatch
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Verify BYTEA roundtrip
SELECT COUNT(*) = 1 AS bytea_ok
FROM typed_tbl
WHERE id = 'row1'
AND bin_col = '\x48656c6c6f'::bytea \gset
\if :bytea_ok
\echo [PASS] (:testid) BYTEA roundtrip correct
\else
\echo [FAIL] (:testid) BYTEA data mismatch
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Verify full hash match
\connect cloudsync_test_45_a
\ir helper_psql_conn_setup.sql
SELECT md5(COALESCE(string_agg(
id || ':' ||
COALESCE(json_col::text, 'NULL') || ':' ||
COALESCE(ts_col::text, 'NULL') || ':' ||
COALESCE(num_col::text, 'NULL') || ':' ||
COALESCE(encode(bin_col, 'hex'), 'NULL'),
'|' ORDER BY id
), '')) AS hash_a FROM typed_tbl \gset
\connect cloudsync_test_45_b
\ir helper_psql_conn_setup.sql
SELECT md5(COALESCE(string_agg(
id || ':' ||
COALESCE(json_col::text, 'NULL') || ':' ||
COALESCE(ts_col::text, 'NULL') || ':' ||
COALESCE(num_col::text, 'NULL') || ':' ||
COALESCE(encode(bin_col, 'hex'), 'NULL'),
'|' ORDER BY id
), '')) AS hash_b FROM typed_tbl \gset
SELECT (:'hash_a' = :'hash_b') AS hash_match \gset
\if :hash_match
\echo [PASS] (:testid) Full data hash matches for PG-specific types
\else
\echo [FAIL] (:testid) Hash mismatch - A: :hash_a, B: :hash_b
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Cleanup
\ir helper_test_cleanup.sql
\if :should_cleanup
DROP DATABASE IF EXISTS cloudsync_test_45_a;
DROP DATABASE IF EXISTS cloudsync_test_45_b;
\endif