Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
WITH RECURSIVE AliasTree AS (
SELECT
CASE WHEN alternative.domain_name IS NULL
THEN destination
ELSE SUBSTR(destination,0,instr(destination,'@'))||'@'||alternative.domain_name
END AS destination,
email,
wildcard,
localpart,
localpart||'@'||a2.name AS alt_email,
0 AS level
FROM alias
LEFT JOIN alternative
ON SUBSTR(destination,instr(destination,'@')+1) = alternative.name
LEFT JOIN alternative a2 ON alias.domain_name = a2.domain_name
UNION ALL
SELECT
child.destination AS destination,
p.email AS email,
p.wildcard AS wildcard,
p.localpart AS localpart,
p.alt_email AS alt_email,
level+1 AS level
FROM alias child
INNER JOIN AliasTree p ON p.destination=child.email
WHERE child.wildcard=0
LIMIT 4096
) SELECT destination FROM (
SELECT destination, email, wildcard, localpart, alt_email, level FROM AliasTree
UNION
SELECT
CASE WHEN forward_enabled=1
THEN (CASE WHEN forward_keep=1 THEN email||',' ELSE '' END)||forward_destination ELSE email
END AS destination,
email,
0 as wildcard,
localpart,
localpart||'@'||alternative.name as alt_email,
0 as level
FROM user LEFT JOIN alternative ON user.domain_name = alternative.domain_name
UNION
SELECT
'@'||domain_name as destination,
'@'||name as email,
0 as wildcard,
'' as localpart,
'' AS alt_email,
0 as level
FROM alternative
GROUP BY alt_email, email
ORDER BY level DESC
) WHERE (
wildcard = 0
AND
(email = '%s' OR alt_email = '%s')
) OR (
wildcard = 1
AND
'%s' LIKE email
)
ORDER BY
wildcard ASC,
length(localpart) DESC
LIMIT 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment