Attachment 8011 Details for Bug 7493 – Bug 7493 [REVISED] Deleting a record with comments breaks "Recent Comments"

[patch] Bug 7493 [REVISED] Deleting a record with comments breaks "Recent Comments"

Bug-7493-REVISED-Deleting-a-record-with-comments-b.patch (text/plain), 7.53 KB, created by Owen Leonard on 2012-03-05 14:14:28 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Owen Leonard

Created: 2012-03-05 14:14:28 UTC

Size: 7.53 KB

patch

obsolete

>From 62dbd9b55c06c2d134f0d94b6b56535f0099d969 Mon Sep 17 00:00:00 2001
>From: Owen Leonard <oleonard@myacpl.org>
>Date: Mon, 6 Feb 2012 09:05:18 -0500
>Subject: [PATCH] Bug 7493 [REVISED] Deleting a record with comments breaks "Recent Comments"
>Content-Type: text/plain; charset="utf-8"
>
>Adding foreign key constraint on biblio and borrowers:
>
>- delete reviews when bibliographic records are deleted
>- set set reviews.borrowernumber to NULL when patron records are deleted.
>
>Before these constraints are set the database update script will
>clean up existing instances of these problems by deleting reviews of
>deleted biblios and setting to NULL reviews.borrowernumber for deleted
>patrons.
>
>In comments moderation, the list of comments will indicate "(deleted patron")
>if the review was submitted by a patron who has since been deleted.
>
>In the OPAC display of comments will omit the patron name altogether
>if the patron has since been deleted.
>
>To test:
>
>1. CONFIRM THAT THE DATABASE UPDATE RUNS CORRECTLY
>
>Before applying the patch:
>
> - delete the record for a title which has a review
> - delete a patron who has submitted a review (on a different title).
>
>Viewing /cgi-bin/koha/opac-showreviews.pl should result in an error.
>
>Apply the patch and make sure the database update runs. Viewing
>the recent comments page in the OPAC should no longer result in an
>error. The title with the comment from a deleted patron should
>show no patron name on these pages:
>
> - Recent comments (opac-showreviews.pl)
> - Recent comments RSS (opac-showreviews.pl?format=rss)
> - Detail page (opac-detail.pl)
>
>Comments from other patrons should show correctly.
>
>2. CONFIRM THAT NEW DELETIONS PERFORM CORRECTLY
>
>After applying the patch:
>
> - delete the record for a title which has a review
> - delete a patron who has submitted a review (on a different title).
>
>Viewing /cgi-bin/koha/opac-showreviews.pl should NOT result in an error.
>The review of the title which you deleted should no longer appear in the
>list of recent comments.
>
>The title with the comment from a deleted patron should
>show no patron name on these pages:
>
> - Recent comments (opac-showreviews.pl)
> - Recent comments RSS (opac-showreviews.pl?format=rss)
> - Detail page (opac-detail.pl)
>
>Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
>---
> installer/data/mysql/kohastructure.sql | 4 +++-
> installer/data/mysql/updatedatabase.pl | 10 ++++++++++
> .../prog/en/modules/reviews/reviewswaiting.tt | 2 +-
> .../prog/en/modules/opac-showreviews-rss.tt | 2 +-
> .../opac-tmpl/prog/en/modules/opac-showreviews.tt | 2 +-
> 5 files changed, 16 insertions(+), 4 deletions(-)
>
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 597a8be..14f6122 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1616,7 +1616,9 @@ CREATE TABLE `reviews` ( -- patron opac comments
> `review` text, -- the body of the comment
> `approved` tinyint(4) default NULL, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
> `datereviewed` datetime default NULL, -- the date the comment was left
>- PRIMARY KEY (`reviewid`)
>+ PRIMARY KEY (`reviewid`),
>+ CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>+ CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> --
>diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
>index e540874..691c768 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -4734,6 +4734,16 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
> SetVersion($DBversion);
> }
> 
>+$DBversion = "3.07.00.XXX";
>+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
>+ $dbh->do("DELETE FROM reviews WHERE biblionumber NOT IN (SELECT biblionumber from biblio)");
>+ $dbh->do("UPDATE reviews SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers)");
>+ $dbh->do("ALTER TABLE reviews ADD CONSTRAINT reviews_ibfk_2 FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
>+ $dbh->do("ALTER TABLE reviews ADD CONSTRAINT reviews_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber ) ON UPDATE CASCADE ON DELETE SET NULL");
>+ print "Upgrade to $DBversion done (Bug 7493 - Add constraint linking OPAC comment biblionumber to biblio, OPAC comment borrowernumber to borrowers.)\n";
>+ SetVersion($DBversion);
>+}
>+
> =head1 FUNCTIONS
> 
> =head2 DropAllForeignKeys($table)
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reviews/reviewswaiting.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reviews/reviewswaiting.tt
>index a1dabbd..1283a78 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reviews/reviewswaiting.tt
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reviews/reviewswaiting.tt
>@@ -44,7 +44,7 @@
> <tbody>[% FOREACH review IN reviews %]
> <tr>
> <td>
>- <a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=[% review.borrowernumber %]">[% review.surname %], [% review.firstname %]</a>
>+ [% IF ( review.borrowernumber ) %]<a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=[% review.borrowernumber %]">[% review.surname %], [% review.firstname %]</a>[% ELSE %](deleted patron)[% END %]
> </td>
> <td>
> <a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% review.biblionumber %]">[% review.bibliotitle %]</a>
>diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews-rss.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews-rss.tt
>index 1ed07f6..42e0fc0 100644
>--- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews-rss.tt
>+++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews-rss.tt
>@@ -26,7 +26,7 @@
> [% IF ( review.notes ) %], [% review.notes |html %][% END %]
> [% IF ( review.size ) %] [% review.size |html %]. [% END %]
> [% IF ( review.isbn ) %] [% review.isbn |html %][% END %] 
>-[% review.firstname %] [% review.surname %] commented on [% review.datereviewed %]: [% review.review |html %]
>+[% IF ( review.borrowernumber && ShowReviewer ) %][% review.firstname %] [% review.surname %] commented[% ELSE %]Comment[% END %] on [% review.datereviewed %]: [% review.review |html %]
> 						]]></description>
> <guid>[% OPACBaseURL %]/cgi-bin/koha/opac-detail.pl?biblionumber=[% review.biblionumber %]&amp;reviewid=[% review.reviewid %]</guid>
> <pubDate>[% review.timestamp %]</pubDate>
>diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews.tt
>index d548361..d096ea6 100644
>--- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews.tt
>+++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews.tt
>@@ -54,7 +54,7 @@ $(document).ready(function(){
> [% review.review |html %]
> [% END %]
> Added [% review.datereviewed %] [% IF ( review.your_comment ) %] by you[% ELSE %]
>- [% IF ( ShowReviewer != "none" ) %] by
>+ [% IF ( review.borrowernumber && ShowReviewer != "none" ) %] by
> [% SWITCH ShowReviewer %]
> [% CASE 'full' %]
> [% review.borrtitle %] [% review.firstname %] [% review.surname %]
>-- 
>1.7.3

Actions: View | Diff | Splinter Review

Attachments on bug 7493: 7739 | 7927 | 7983 | 8011