Opened 13 years ago

Last modified 13 years ago

#319 new defect

utilize foreign key constraints

Reported by: dmorton Owned by: rjl
Priority: normal Milestone:
Component: General Version:
Severity: normal Keywords:
Cc:

Description

Since mysql and postgresql both support foreign key constraints, we should start using them to help ensure database integrity. This also means certain deletion operations can be made with fewer database calls. Here's a preliminary mysql alteration:

ALTER TABLE `maia_mail_recipients`
  ADD CONSTRAINT `maia_mail_recipients_ibfk_1` FOREIGN KEY (`mail_id`) REFERENCES `maia_mail` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
ALTER TABLE `maia_banned_attachments_found`
  ADD CONSTRAINT `maia_banned_attachments_found_ibfk_1` FOREIGN KEY (`mail_id`) REFERENCES `maia_mail` (`id`) ON UPDATE CASCADE;

ALTER TABLE `maia_viruses_detected` ADD INDEX ( `virus_id` ) 

ALTER TABLE `maia_viruses_detected`
  ADD CONSTRAINT `maia_viruses_detected_ibfk_2` FOREIGN KEY (`virus_id`) REFERENCES `maia_viruses` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `maia_viruses_detected_ibfk_1` FOREIGN KEY (`mail_id`) REFERENCES `maia_mail` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
ALTER TABLE `maia_sa_rules_triggered` ADD INDEX ( `rule_id` ) 
ALTER TABLE `maia_sa_rules_triggered`
  ADD CONSTRAINT `maia_sa_rules_triggered_ibfk_2` FOREIGN KEY (`rule_id`) REFERENCES `maia_sa_rules` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `maia_sa_rules_triggered_ibfk_1` FOREIGN KEY (`mail_id`) REFERENCES `maia_mail` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

DELETE maia_stats from maia_stats LEFT JOIN maia_users on id=user_id WHERE id IS NULL;
ALTER TABLE `maia_stats`
  ADD CONSTRAINT `maia_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `maia_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
DELETE maia_stats_history from maia_stats_history LEFT JOIN maia_users on maia_users.id=user_id WHERE maia_users.id IS NULL;
ALTER TABLE `maia_stats_history` ADD INDEX ( `user_id` ) 
ALTER TABLE `maia_stats_history`
  ADD CONSTRAINT `maia_stats_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `maia_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
ALTER TABLE `maia_domain_admins` ADD INDEX ( `admin_id` ) 
ALTER TABLE `maia_domain_admins`
  ADD CONSTRAINT `maia_domain_admins_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `maia_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `maia_domain_admins_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `maia_domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

To use this, we also have to remove some code in delete_mail, since the DB will do it for us.

Before implementing this, however, it might be good to tackle the per-email stats first, as it changes some of the constraints.

Change History (2)

comment:1 Changed 13 years ago by chunt@…

  • Type changed from defect to enhancement

i'd like to see foreign key restraints ensure that accounts are deleted when a domain is deleted.

comment:2 Changed 13 years ago by anonymous

  • Type changed from enhancement to defect
Note: See TracTickets for help on using tickets.