Re: [Postfixadmin-devel] DB schema question

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

On 1/12/24 01:40, Christian Boltz wrote:
> Hello,
> 
> Am Samstag, 30. November 2024, 06:18 schrieb Richard Hector:
>> I notice that the mailbox table has username and domain columns, where
>> the username is the full email address including the domain name. Are
>> the domain names in both places guaranteed to be the same?
> 
> Nobody can stop you from doing a silly INSERT or UPDATE query directly
> in the database.
> 
> That said - as long as you only use PostfixAdmin to modify the database,
> the domain column and the domain in the mailbox name are guaranteed to
> be the same.
> 
>> 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

No concatenations required. Get rid of the table aliases if you like.

I don't know whether those booleans work like that in mysql/maria - they 
do in PostgreSQL.

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

Feel free to tell me I've got it wrong :-)

Cheers,
Richard