mail-accounts/queries.md

Stalwart queries

name

100 GB quota

SELECT 
    mail as name,
    'individual' as type,
    password as secret, 
    '' as description, 
    107374182400 as quota 
FROM emails 
    JOIN accounts ON emails.id = accounts.id
WHERE 
    type = 'primary'
    AND mail = $1
    AND password IS NOT NULL

members

SELECT NULL as member_of WHERE 1=0

recipients

    SELECT dest as name FROM alias WHERE alias = $1
UNION
    SELECT mail as name 
    FROM emails 
    WHERE id = (SELECT id FROM emails WHERE type = 'list' AND mail = $1) 
        AND type = 'primary'
UNION
    SELECT mail as name FROM emails WHERE mail = $1

emails

SELECT mais as address 
FROM emails
WHERE
    (id = (SELECT id FROM emails WHERE mail = $1) AND type = 'alias')
    OR mail = $1
ORDER BY type, mail

verify

SELECT  mail as address 
FROM emails 
WHERE 
    mail LIKE '%' || $1 || '%' 
    AND type = 'primary' 
ORDER BY address 
LIMIT 5

expand

SELECT recipient as address 
FROM list_recipients 
WHERE list = $1 
ORDER BY address 
LIMIT 50

domains

SELECT 1 FROM emails WHERE mail LIKE '%@' || $1 LIMIT 1