[Koha-patches] [PATCH] [SIGNED-OFF] Bug 7493 [REVISED] Deleting a record with comments breaks "Recent Comments"
julian.maurice at biblibre.com
julian.maurice at biblibre.com
Fri Mar 2 14:38:16 CET 2012
From: Owen Leonard <oleonard at myacpl.org>
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 at biblibre.com>
---
installer/data/mysql/kohastructure.sql | 4 +++-
installer/data/mysql/updatedatabase.pl | 10 ++++++++++
.../prog/en/modules/reviews/reviewswaiting.tt | 2 +-
koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tt | 8 ++++----
.../prog/en/modules/opac-showreviews-rss.tt | 2 +-
.../opac-tmpl/prog/en/modules/opac-showreviews.tt | 2 +-
6 files changed, 20 insertions(+), 8 deletions(-)
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 67149c9d..7885867 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1598,7 +1598,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 d6b3fe8..abe474b 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -4719,6 +4719,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-detail.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tt
index 25480c8..e77652d 100644
--- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tt
+++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tt
@@ -878,10 +878,10 @@ YAHOO.util.Event.onContentReady("furtherm", function () {
<img class="avatar" src="[% review.avatarurl %]" height="80" width="80" alt="" />
[% END %]
<h5>
- Comment by
- [% review.title %]
- [% review.firstname %]
- [% review.surname %]
+ Comment [% IF ( review.borrowernumber ) %]by
+ [% review.title %]
+ [% review.firstname %]
+ [% review.surname %][% ELSE %][% END %]
</h5>
[% END %]
<small>[% review.datereviewed %]</small>
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 %] </p>
-<p>[% review.firstname %] [% review.surname %] commented on [% review.datereviewed %]: [% review.review |html %]</p>
+<p>[% IF ( review.borrowernumber && ShowReviewer ) %][% review.firstname %] [% review.surname %] commented[% ELSE %]Comment[% END %] on [% review.datereviewed %]: [% review.review |html %]</p>
]]></description>
<guid>[% OPACBaseURL %]/cgi-bin/koha/opac-detail.pl?biblionumber=[% review.biblionumber %]&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 fdea679..2f75237 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 %]
<span style="font-size:87%;font-color:#CCC;">Added [% review.datereviewed %] [% IF ( review.your_comment ) %] by <strong>you</strong>[% ELSE %]
- [% IF ( ShowReviewer ) %] by
+ [% IF ( review.borrowernumber && ShowReviewer ) %] by
[% review.firstname %] [% review.surname %][% END %][% END %]</span></p>
</td>
<td>
--
1.7.9.1
More information about the Koha-patches
mailing list