@@ -, +, @@ - delete reviews when bibliographic records are deleted - set set reviews.borrowernumber to NULL when patron records are deleted. - delete the record for a title which has a review - delete a patron who has submitted a review (on a different title). - Recent comments (opac-showreviews.pl) - Recent comments RSS (opac-showreviews.pl?format=rss) - Detail page (opac-detail.pl) - delete the record for a title which has a review - delete a patron who has submitted a review (on a different title). - Recent comments (opac-showreviews.pl) - Recent comments RSS (opac-showreviews.pl?format=rss) - Detail page (opac-detail.pl) --- 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(-) --- a/installer/data/mysql/kohastructure.sql +++ a/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; -- --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -4712,6 +4712,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 IN (SELECT biblionumber from deletedbiblio)"); + $dbh->do("UPDATE reviews SET borrowernumber = NULL WHERE borrowernumber IN (SELECT borrowernumber FROM deletedborrowers)"); + $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) --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reviews/reviewswaiting.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reviews/reviewswaiting.tt @@ -44,7 +44,7 @@ [% FOREACH review IN reviews %] - [% review.surname %], [% review.firstname %] + [% IF ( review.borrowernumber ) %][% review.surname %], [% review.firstname %][% ELSE %](deleted patron)[% END %] [% review.bibliotitle %] --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tt +++ a/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tt @@ -878,10 +878,10 @@ YAHOO.util.Event.onContentReady("furtherm", function () { [% END %]
- Comment by - [% review.title %] - [% review.firstname %] - [% review.surname %] + Comment [% IF ( review.borrowernumber ) %]by + [% review.title %] + [% review.firstname %] + [% review.surname %][% ELSE %][% END %]
[% END %] [% review.datereviewed %] --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews-rss.tt +++ a/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 %]

]]> [% OPACBaseURL %]/cgi-bin/koha/opac-detail.pl?biblionumber=[% review.biblionumber %]&reviewid=[% review.reviewid %] [% review.timestamp %] --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-showreviews.tt +++ a/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 ) %] by + [% IF ( review.borrowernumber && ShowReviewer ) %] by [% review.firstname %] [% review.surname %][% END %][% END %]

--