Bug 10631

Summary: Table alert should have a FK on borrowernumber and entries be deleted with the referenced borrower
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Architecture, internals, and plumbingAssignee: Galen Charlton <gmcharlt>
Status: RESOLVED DUPLICATE QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low    
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:

Description Katrin Fischer 2013-07-23 15:53:09 UTC
The table alert is currently used to store information about email subscriptons for new serial issues. 

The current table defnition is:

2682 DROP TABLE IF EXISTS `alert`;
2683 CREATE TABLE `alert` (
2684   `alertid` int(11) NOT NULL auto_increment,
2685   `borrowernumber` int(11) NOT NULL default 0,
2686   `type` varchar(10) NOT NULL default '',
2687   `externalid` varchar(20) NOT NULL default '',
2688   PRIMARY KEY  (`alertid`),
2689   KEY `borrowernumber` (`borrowernumber`),
2690   KEY `type` (`type`,`externalid`)
2691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2692 

As I see it borrowernumber should be a FK and we are missing some cascades to make sure the table is cleaned up when a borrower is deleted.
Comment 1 Galen Charlton 2013-07-23 15:57:13 UTC
Some relevant commentary from #koha:

<kf> hm wondering... we delete all that, but that will make it impossible to do an 'undelete' bringing borrowers back from deletedborrowers
<kf> we already delete a lot - that would make it more consistent, but I coudl imagine that might be the eaosn it was not done in the first place?
<kf> i am working on a 'concept for deletion of personal data' so that's where those questions come from :)
<gmcharlt> I'd rather have the FKs
<kf> i will file a bug
<kf> and note it for manual deletion for now... *meh* :)
<gmcharlt> and if there's a desire for a more complete patron undelete, add new tables as needed
<gmcharlt> e.g., deleted_borrower_alerts, etc.
Comment 2 Katrin Fischer 2015-01-08 14:41:34 UTC

*** This bug has been marked as a duplicate of bug 13535 ***