max / goingson

2.7 KB · 69 lines History Blame Raw

1	-- Split email_accounts sync: credentials stay per-device, config syncs.
2	-- Drops the 3 existing triggers (migration 032) and recreates them
3	-- capturing only 16 columns — excludes password, oauth2_access_token,
4	-- oauth2_refresh_token, oauth2_token_expires_at (and last_sync_at as before).
5
6	DROP TRIGGER IF EXISTS sync_trg_email_accounts_insert;
7	DROP TRIGGER IF EXISTS sync_trg_email_accounts_update;
8	DROP TRIGGER IF EXISTS sync_trg_email_accounts_delete;
9
10	-- ── email_accounts (16 cols: config only, no credentials) ──
11
12	CREATE TRIGGER IF NOT EXISTS sync_trg_email_accounts_insert
13	AFTER INSERT ON email_accounts
14	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
15	BEGIN
16	INSERT INTO sync_changelog (table_name, op, row_id, data)
17	VALUES ('email_accounts', 'INSERT', NEW.id, json_object(
18	'id', NEW.id,
19	'user_id', NEW.user_id,
20	'account_name', NEW.account_name,
21	'email_address', NEW.email_address,
22	'imap_server', NEW.imap_server,
23	'imap_port', NEW.imap_port,
24	'smtp_server', NEW.smtp_server,
25	'smtp_port', NEW.smtp_port,
26	'username', NEW.username,
27	'use_tls', NEW.use_tls,
28	'created_at', NEW.created_at,
29	'archive_folder_name', NEW.archive_folder_name,
30	'auth_type', NEW.auth_type,
31	'jmap_session_url', NEW.jmap_session_url,
32	'jmap_account_id', NEW.jmap_account_id,
33	'sync_interval_minutes', NEW.sync_interval_minutes
34	));
35	END;
36
37	CREATE TRIGGER IF NOT EXISTS sync_trg_email_accounts_update
38	AFTER UPDATE ON email_accounts
39	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
40	BEGIN
41	INSERT INTO sync_changelog (table_name, op, row_id, data)
42	VALUES ('email_accounts', 'UPDATE', NEW.id, json_object(
43	'id', NEW.id,
44	'user_id', NEW.user_id,
45	'account_name', NEW.account_name,
46	'email_address', NEW.email_address,
47	'imap_server', NEW.imap_server,
48	'imap_port', NEW.imap_port,
49	'smtp_server', NEW.smtp_server,
50	'smtp_port', NEW.smtp_port,
51	'username', NEW.username,
52	'use_tls', NEW.use_tls,
53	'created_at', NEW.created_at,
54	'archive_folder_name', NEW.archive_folder_name,
55	'auth_type', NEW.auth_type,
56	'jmap_session_url', NEW.jmap_session_url,
57	'jmap_account_id', NEW.jmap_account_id,
58	'sync_interval_minutes', NEW.sync_interval_minutes
59	));
60	END;
61
62	CREATE TRIGGER IF NOT EXISTS sync_trg_email_accounts_delete
63	AFTER DELETE ON email_accounts
64	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
65	BEGIN
66	INSERT INTO sync_changelog (table_name, op, row_id, data)
67	VALUES ('email_accounts', 'DELETE', OLD.id, NULL);
68	END;
69