1 (edited by camel1cz 2014-08-28 20:42:13)

Topic: Suggestion: Improve database integrity

==== Required information ====
- iRedMail version: 0.8.7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PgSQL
- Linux/BSD distribution name and version: Wheezy (Debian 7)
- Related log if you're reporting an issue:
====

Hi Zhang,

after some time of using iredmail, I found out, I have some artifacts in database. I know it's because I had wrong utility scripts, but IMO some database integrity checks cannot hurt.

This code creates reference constraint from table alias to domain (nonexisting domain cannot have aliases)

ALTER TABLE alias ADD CONSTRAINT domain_exists FOREIGN KEY (domain) REFERENCES domain (domain) MATCH FULL;

I have the same on table mailbox, but I'm not sure if it's from official release anymore - I guess so?

Thanks for your great work!
Kamil

2

Re: Suggestion: Improve database integrity

camel1cz wrote:

I have the same on table mailbox, but I'm not sure if it's from official release anymore - I guess so?

No.

camel1cz wrote:

ALTER TABLE alias ADD CONSTRAINT domain_exists FOREIGN KEY (domain) REFERENCES domain (domain) MATCH FULL;

What about alias domains (table 'alias_domain')?

3

Re: Suggestion: Improve database integrity

ZhangHuangbin wrote:
camel1cz wrote:

I have the same on table mailbox, but I'm not sure if it's from official release anymore - I guess so?

No.

OK, here is the code:

ALTER TABLE mailbox ADD CONSTRAINT domain_exists FOREIGN KEY (domain) REFERENCES domain (domain) MATCH FULL;
ZhangHuangbin wrote:

What about alias domains (table 'alias_domain')?

Yes, it's the same... I just don't use domain aliases, thus I missed it.

ALTER TABLE alias_domain ADD CONSTRAINT domain_exists FOREIGN KEY (target_domain) REFERENCES domain (domain) MATCH FULL;