Re: [Postfixadmin-devel] DB schema question

[Richard H. <ri...@wa...>]

On 5/12/24 10:18, Christian Boltz wrote:
> Hello,
> 
> Am Dienstag, 3. Dezember 2024, 12:09 schrieb Richard Hector:
>> On 1/12/24 01:40, Christian Boltz wrote:
>> > Am Samstag, 30. November 2024, 06:18 schrieb Richard Hector:
> 
>> >> If they are always the same, then the example db query for
>> >> virtual_alias_domain_mailbox_maps could be significantly
>> >> simplified.
>> > 
>> > Alias domains have a few special cases and pitfalls, but
>> > nevertheless, I'm looking forward for your proposal ;-)
>> 
>> So maybe this is where I show my ignorance, but:
>> 
>> SELECT maildir from mailbox m JOIN alias_domain ad ON m.domain =
>> ad.target_domain WHERE ad.alias_domain = '%d' and m.active and
>> ad.active
> 
> This query only checks the domain (%d), but not the mailbox name (%u).
> It will give you a list of maildirs for all mailboxes on that domain,
> but not (only) the maildir for the specified mailbox. It will even give
> you a list of maildirs if you query for a non-existing mailbox.
> (Always assuming that %d is an alias domain.)
> 
>> No concatenations required. 
> 
> Only because your query doesn't specify which mailbox to look for ;-)

Aah ... I shouldn't have done that from memory, in a hurry :-)

Checking %u against m.local_part would be necessary, at least - but I'm 
again short of time, so I'll have another look later. Thank you for your 
patience :-)

> 
> The lookup needs to be done for user@targetdomain, and there's no
> variable for it (%s is user@aliasdomain - which doesn't exist because
> it's meant to be rewritten to user@targetdomain). That's why we concat
> %u (localpart) + the target domain so that the query finds the right
> mailbox.
> 
> (For the records: these variables are explained in man 5 mysql_table.)
> 
>> Get rid of the table aliases if you like.
> 
> There's nothing wrong with specifying which table you want to use ;-)
> 
>> I don't know whether those booleans work like that in mysql/maria -
>> they do in PostgreSQL.
> 
> Good question, I didn't try before.
> 
> I just tested on MariaDB 11.5.2, and it indeed handles the booleans as
> expected.
> (No idea if older MariaDB versions or MySQL also accepts this syntax.)
> 
>> Also, we don't need maildir here, right? "SELECT 1 FROM ..." would
>> work just as well, and avoid confusing people (well, me) as to what
>> it's being used for :-)
> 
> Well, it depends how you deliver mails.
> 
> If you let Postfix write the mail into the maildir, then - surprise - it
> will need to know the maildir.
> 
> If you deliver via Dovecot lmtp, then Postfix of course doesn't need to
> know the maildir.

Yes, I guess this is the right query to provide that info to Postfix. 
And you're right, I'm using dovecot (actually deliver/lda, but planning 
to switch to lmtp), so wasn't fully thinking about that.

>> Feel free to tell me I've got it wrong :-)
> 
> As requested ;-) - I hope my explanation makes it clear why the query is
> written the way it is.

Many thanks (also for your responses on IRC),
Richard