Re: [Postfixadmin-devel] DB schema question

[Christian B. <pos...@cb...>]

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 ;-)

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.

> 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.


Regards,

Christian Boltz
-- 
Maybe (as it's just repackaging), it could even run on some ppc64
obs-servers that must be quite depressed and have worn down their thumbs
quite a lot from twiddling them, chronically having nothing to do ...
[David Haller in opensuse-packaging]