From e2a3e25a56c64741c6ea04072b1cb2aef6aac3a8 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 4 Aug 2015 10:00:51 +0100 Subject: [PATCH] Bug 14205: Deleting an Item/Record does not remove link to course reserve A constraint is missing on the course_reserves.ci_id field. This patch adds it and removes orphan rows. Test plan: 0/ Don't apply the patch 1/ Login to Staff Site. 2/ Go to Course Reserves 3/ Create Course 4/ Add Reserve 5/ Go to item added (delete item/record) 6/ Refresh Course (see no items attached) 7/ Delete Course 8/ See course still listed in the courses (Not deleted) 9/ Apply the patch and execute the updatedatabase entry. 10/ Delete the Course Note that the course has correctly been removed. NOTE: Make sure to set the UseCourseReserves system preference to test. Tested before (problem existed) and after (problem solved) update. Dropped DB, web installed, and put in dummy data and retested. Problem solved. Signed-off-by: Mark Tompsett Signed-off-by: Katrin Fischer Signed-off-by: Tomas Cohen Arazi Signed-off-by: Liz Rea --- .../atomicupdate/bug_14205-add_fk_course_reserves_ci_id.sql | 2 ++ installer/data/mysql/kohastructure.sql | 4 +++- 2 files changed, 5 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_14205-add_fk_course_reserves_ci_id.sql diff --git a/installer/data/mysql/atomicupdate/bug_14205-add_fk_course_reserves_ci_id.sql b/installer/data/mysql/atomicupdate/bug_14205-add_fk_course_reserves_ci_id.sql new file mode 100644 index 0000000000..1a68545840 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_14205-add_fk_course_reserves_ci_id.sql @@ -0,0 +1,2 @@ +DELETE cr.* FROM course_reserves AS cr LEFT JOIN course_items USING(ci_id) WHERE course_items.ci_id IS NULL; +ALTER TABLE course_reserves add CONSTRAINT course_reserves_ibfk_2 FOREIGN KEY (ci_id) REFERENCES course_items (ci_id) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3251034c5b..33f926e67d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -622,7 +622,9 @@ CREATE TABLE `course_reserves` ( -- Constraints for table `course_reserves` -- ALTER TABLE `course_reserves` - ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`); + ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), + ADD CONSTRAINT `course_reserves_ibfk_2` FOREIGN KEY (`ci_id`) REFERENCES `course_items` (`ci_id`) ON DELETE CASCADE ON UPDATE CASCADE; + -- -- Table structure for table `branch_borrower_circ_rules` -- 2.39.5