@@ -, +, @@ - 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.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 %]
]]>