From 7a76ce4c00a6378e376a2d589695210698728fa2 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 13 Jan 2016 14:34:29 +0000 Subject: [PATCH] Bug 13534: Do not remove tags on deleting a patron On deleting a patron, all the tags approved by this user will be deleted. This can cause data lost. Test plan: 0/ Do not execute the update DB entry 1/ Create 2 patrons A, B 2/ Create some tags with patron A logged in 3/ Approve them with patron B logged in 4/ Delete the 2 patrons => The tags have been deleted 5/ Execute the DB entry 6/ Repeat 1,2,3,4 => The tags have not been deleted and are still shown on the interface (result, detail, tags module) --- installer/data/mysql/atomicupdate/bug_13534.sql | 7 +++++++ installer/data/mysql/kohastructure.sql | 6 +++--- 2 files changed, 10 insertions(+), 3 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_13534.sql diff --git a/installer/data/mysql/atomicupdate/bug_13534.sql b/installer/data/mysql/atomicupdate/bug_13534.sql new file mode 100644 index 0000000..fbe97fc --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_13534.sql @@ -0,0 +1,7 @@ +ALTER TABLE tags_all MODIFY COLUMN borrowernumber INT(11); +ALTER TABLE tags_all drop FOREIGN KEY tags_borrowers_fk_1; +ALTER TABLE tags_all ADD CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE; + +ALTER TABLE tags_approval DROP FOREIGN KEY tags_approval_borrowers_fk_1; +ALTER TABLE tags_approval ADD CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE; + diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index bf28e81..d8b2069 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2242,7 +2242,7 @@ CREATE TABLE `tags` ( DROP TABLE IF EXISTS `tags_all`; CREATE TABLE `tags_all` ( -- all of the tags `tag_id` int(11) NOT NULL auto_increment, -- unique id and primary key - `borrowernumber` int(11) NOT NULL, -- the patron who added the tag (borrowers.borrowernumber) + `borrowernumber` int(11) DEFAULT NULL, -- the patron who added the tag (borrowers.borrowernumber) `biblionumber` int(11) NOT NULL, -- the bib record this tag was left on (biblio.biblionumber) `term` varchar(255) NOT NULL, -- the tag `language` int(4) default NULL, -- the language the tag was left in @@ -2251,7 +2251,7 @@ CREATE TABLE `tags_all` ( -- all of the tags KEY `tags_borrowers_fk_1` (`borrowernumber`), KEY `tags_biblionumber_fk_1` (`biblionumber`), CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`) - REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; @@ -2270,7 +2270,7 @@ CREATE TABLE `tags_approval` ( -- approved tags PRIMARY KEY (`term`), KEY `tags_approval_borrowers_fk_1` (`approved_by`), CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`) - REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE + REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- 2.1.0