recurse_aliases_with_alternatives.sql

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;