Bugzilla – Attachment 46592 Details for
Bug 13668
Creator of an SQL report shoudl be NULL when the staff patron is deleted
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 13668: Add foreign key on saved_sql.borrowernumber
Bug-13668-Add-foreign-key-on-savedsqlborrowernumbe.patch (text/plain), 3.52 KB, created by
Jonathan Druart
on 2016-01-13 15:15:33 UTC
(
hide
)
Description:
Bug 13668: Add foreign key on saved_sql.borrowernumber
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2016-01-13 15:15:33 UTC
Size:
3.52 KB
patch
obsolete
>From ad1219141bb074dafa1fcc0dc96303f2ff980628 Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >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.</p> > <td>[% savedreport.groupname %]</td> > <td>[% savedreport.subgroupname %]</td> > <td>[% savedreport.notes %]</td> >- <td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td> >+ <td> >+ [% IF savedreport.borrowernumber %] >+ [% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %]) >+ [% END %] >+ </td> > <td><span title="[% savedreport.date_created %]">[% savedreport.date_created | $KohaDates %]</span></td> > [% IF (savedreport.public) %] > <td>Yes</td> >-- >2.1.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 13668
:
46592
|
46622
|
46623