49 lines
1.9 KiB
MySQL
49 lines
1.9 KiB
MySQL
|
-- Migration 156: Backfill email thread_keys from parent emails
|
||
|
|
-- Ensures replies inherit the same thread_key as their parent so they group together visually.
|
||
|
|
|
||
|
|
-- Step 1: For emails that have in_reply_to or email_references pointing to an existing
|
||
|
|
-- email with a thread_key, adopt the parent's thread_key.
|
||
|
|
UPDATE email_messages child
|
||
|
|
SET thread_key = parent.thread_key,
|
||
|
|
updated_at = CURRENT_TIMESTAMP
|
||
|
|
FROM email_messages parent
|
||
|
|
WHERE child.deleted_at IS NULL
|
||
|
|
AND parent.deleted_at IS NULL
|
||
|
|
AND parent.thread_key IS NOT NULL
|
||
|
|
AND TRIM(parent.thread_key) != ''
|
||
|
|
AND (
|
||
|
|
-- Match via in_reply_to -> parent message_id
|
||
|
|
(
|
||
|
|
child.in_reply_to IS NOT NULL
|
||
|
|
AND TRIM(child.in_reply_to) != ''
|
||
|
|
AND LOWER(REGEXP_REPLACE(parent.message_id, '[<>\s]', '', 'g'))
|
||
|
|
= LOWER(REGEXP_REPLACE(
|
||
|
|
(REGEXP_SPLIT_TO_ARRAY(TRIM(child.in_reply_to), E'[\\s,]+'))[1],
|
||
|
|
'[<>\s]', '', 'g'
|
||
|
|
))
|
||
|
|
)
|
||
|
|
OR
|
||
|
|
-- Match via first reference -> parent message_id
|
||
|
|
(
|
||
|
|
child.email_references IS NOT NULL
|
||
|
|
AND TRIM(child.email_references) != ''
|
||
|
|
AND LOWER(REGEXP_REPLACE(parent.message_id, '[<>\s]', '', 'g'))
|
||
|
|
= LOWER(REGEXP_REPLACE(
|
||
|
|
(REGEXP_SPLIT_TO_ARRAY(TRIM(child.email_references), E'[\\s,]+'))[1],
|
||
|
|
'[<>\s]', '', 'g'
|
||
|
|
))
|
||
|
|
)
|
||
|
|
)
|
||
|
|
-- Only update if the thread_key would actually change
|
||
|
|
AND (
|
||
|
|
child.thread_key IS NULL
|
||
|
|
OR TRIM(child.thread_key) = ''
|
||
|
|
OR LOWER(REGEXP_REPLACE(child.thread_key, '[<>\s]', '', 'g'))
|
||
|
|
!= LOWER(REGEXP_REPLACE(parent.thread_key, '[<>\s]', '', 'g'))
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Step 2: REMOVED - was incorrectly forcing all emails in a SAG to share one thread_key.
|
||
|
|
-- Each SAG can have multiple independent email threads (different recipients/subjects).
|
||
|
|
-- Thread grouping is based on actual RFC 5322 threading headers, not SAG membership.
|
||
|
|
-- See migration 157 for the fix.
|