From 71b0885babf336843d931afd09233428f6f8f86d 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) Signed-off-by: Aleisha Signed-off-by: Kyle M Hall Signed-off-by: Brendan Gallagher brendan@bywatersolutions.com (cherry picked from commit fb419ba83d8c7b6bf710b42ad3fe61701809fc0e) Signed-off-by: Julian Maurice --- installer/data/mysql/atomicupdate/bug_13534.sql | 6 ++++++ installer/data/mysql/kohastructure.sql | 6 +++--- 2 files changed, 9 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 0000000000..3b59893a13 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_13534.sql @@ -0,0 +1,6 @@ +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 c9c78b96dc..8403803bed 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2227,7 +2227,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 @@ -2236,7 +2236,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; @@ -2255,7 +2255,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.39.5