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

  • camel1cz
  • camel1cz
  • Member
  • Offline
  • From: Czech Republic
  • Registered: 2013-04-20
  • Posts: 220

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

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,094

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 Reply by camel1cz

  • camel1cz
  • camel1cz
  • Member
  • Offline
  • From: Czech Republic
  • Registered: 2013-04-20
  • Posts: 220

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;