From ad1219141bb074dafa1fcc0dc96303f2ff980628 Mon Sep 17 00:00:00 2001
From: Jonathan Druart
Date: Wed, 13 Jan 2016 15:13:28 +0000
Subject: [PATCH] Bug 13668: Add foreign key on saved_sql.borrowernumber
saved_sql.borrowernumber refers to a borrowernumber, the DB structure should
reflect this constraint.
Test plan:
0/ Do not execute the update DB entry
1/ Add a new patron
2/ Use this patron to create a new report
3/ Delete the patron
4/ Execute the DB entry
5/ The saved_sql.borrowernumber for this report should have been set to NULL
6/ On the reports table, the column 'Author' should be empty.
---
installer/data/mysql/atomicupdate/bug_13668.sql | 2 ++
installer/data/mysql/kohastructure.sql | 3 ++-
.../intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt | 6 +++++-
3 files changed, 9 insertions(+), 2 deletions(-)
create mode 100644 installer/data/mysql/atomicupdate/bug_13668.sql
diff --git a/installer/data/mysql/atomicupdate/bug_13668.sql b/installer/data/mysql/atomicupdate/bug_13668.sql
new file mode 100644
index 0000000..88ff015
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_13668.sql
@@ -0,0 +1,2 @@
+UPDATE saved_sql LEFT JOIN borrowers on saved_sql.borrowernumber=borrowers.borrowernumber SET saved_sql.borrowernumber=NULL WHERE borrowers.borrowernumber IS NULL;
+ALTER TABLE saved_sql ADD FOREIGN KEY (borrowernumber) 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 431a541..df81611 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1910,7 +1910,8 @@ CREATE TABLE saved_sql ( -- saved sql reports
report_subgroup varchar(80) default NULL,
PRIMARY KEY (`id`),
KEY sql_area_group_idx (report_group, report_subgroup),
- KEY boridx (`borrowernumber`)
+ KEY boridx (`borrowernumber`),
+ CONSTRAINT `saved_sql_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
index 6125814..f1bab32 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
@@ -333,7 +333,11 @@ canned reports and writing custom SQL reports.
[% savedreport.groupname %] |
[% savedreport.subgroupname %] |
[% savedreport.notes %] |
- [% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %]) |
+
+ [% IF savedreport.borrowernumber %]
+ [% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])
+ [% END %]
+ |
[% savedreport.date_created | $KohaDates %] |
[% IF (savedreport.public) %]
Yes |
--
2.1.0