Am 12.05.2011 um 13:19 schrieb Arnt Gulbrandsen: > lower(addresses.localpart)||'@'||lower(addresses.domain) must be unique when > joined against aliases. I've not the faintest idea how to express that in > SQL, though. > >> Then we need a cleanup script to purge inconsistent aliases from the db >> (disabling constraint / delete / re-enable constraint). > > I don't think we can write that. It's easy to detect, not so easy to fix. > > Maybe an alternative is to write a trigger to make sure no new badness is > added.
The attached sql
trigger.sql
Description: Binary data
contains a trigger for inserting/updating addresses:
1.1 Make (localpart,domain) unique if name is NULL
1.2 Does not allow an address which distinguishes only in case of another
address, which is referenced by aliases.address
and another trigger for inserting/updating aliases:
2.1 Make sure aliases.address has no counterpart in addresses, which
distinguishes only in case.
Tests:
1.1
insert into addresses(localpart,domain) values('localpart','chaos1.de');
ERROR: ?Duplicate ddress localp...@chaos1.de
1.2 aox utility does not return, but sql works:
development=# select ADL.id,ADL.localpart || '@' || ADL.domain AS ADR, ADD.id,
ADD.localpart || '@' || ADD.domain AS DST FROM aliases AL JOIN addresses ADL ON
ADL.id = AL.address JOIN addresses ADD ON ADD.id = AL.mailbox;
id | adr | id | dst
----+---------------------------+----+---------------------
82 | localpa...@do.main | 1 | axel....@chaos1.de
72 | localpa...@do.main | 1 | axel....@chaos1.de
70 | localp...@do.main | 1 | axel....@chaos1.de
evelopment=# insert into addresses(localpart,domain)
values('LOcalPart','Do.Main');
ERROR: ?Address localp...@do.main conflicts with alias
2.1
development=# select * from addresses where localpart = 'Hermann.xyz';
id | name | localpart | domain
----+----------------+----------------+-------------
12 | | Hermann.xyz | domain.DE
15 | Hermann xyz | Hermann.xyz | domain.DE
59 | Hermann xyz | Hermann.xyz | domain1.DE
aox add alias 'hermann....@domain.de' '/users/axel....@chaos1.de/INBOX' ;
aox: Couldn't create alias: PostgreSQL Server: ?Alias conflicts with existing
address
Hope this fits in your picture,
Axel
---
PGP-Key:29E99DD6 ☀ +49 151 2300 9283 ☀ computing @ chaos claudius
