http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 --- Comment #1 from Owen Leonard <oleonard@myacpl.org> 2012-02-06 15:06:45 UTC --- I think the solution to this problem is to add a foreign key constraint so that when bibliographic or patron records are deleted the reviews table is updated. I propose to: - delete reviews when bibliographic records are deleted - set set reviews.borrowernumber to NULL when patron records are deleted. The database update making these changes will not run properly if there are already instances of this bug--reviews with deleted biblios or patrons--so some kind of update will have to be run in addition to setting the constraint. Does anyone have any comments or suggestions about the efficiency/efficacy of using these queries for that purpose? DELETE FROM reviews WHERE biblionumber IN (SELECT biblionumber from deletedbiblio); UPDATE reviews SET borrowernumber = NULL WHERE borrowernumber IN (SELECT borrowernumber FROM deletedborrowers); -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 --- Comment #3 from Owen Leonard <oleonard@myacpl.org> 2012-02-17 16:59:06 UTC --- Created attachment 7739 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=7739 Bug 7493 - Deleting a record with comments breaks "Recent Comments" 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) -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 Julian Maurice <julian.maurice@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |julian.maurice@biblibre.com --- Comment #4 from Julian Maurice <julian.maurice@biblibre.com> 2012-02-23 10:26:37 UTC --- I don't know when or how it happened but I had in database 1 line in biblio and 1 line in deletedbiblio with the same biblionumber, which cause comments on this biblio to be deleted. I'm sure this is not a common case, and it should not happen, but I would prefer that deletions are done with an SQL query like this DELETE FROM reviews WHERE biblionumber **NOT** IN (SELECT biblionumber from **biblio**); (same for patrons) -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 --- Comment #6 from Owen Leonard <oleonard@myacpl.org> 2012-02-28 16:23:58 UTC --- The revised patch takes Julian's suggestion to use "NOT IN" biblio and borrowers. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 Julian Maurice <julian.maurice@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #7983|0 |1 is obsolete| | --- Comment #9 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 8011 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=8011&action=edit Bug 7493 [REVISED] Deleting a record with comments breaks "Recent Comments" 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> -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master CC| |paul.poulain@biblibre.com Version|master |rel_3_6 -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7493 Magnus Enger <magnus@enger.priv.no> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |8149 -- You are receiving this mail because: You are watching all bug changes.