From 8e0a420083e8cae52b51b9eb660d7a45797841a9 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. Signed-off-by: Joy Nelson Signed-off-by: Dani Elder --- installer/data/mysql/atomicupdate/bug_13668.sql | 2 ++ installer/data/mysql/kohastructure.sql | 3 ++- .../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 -- 1.7.10.4