---- Reported by nahuel.angelinetti@biblibre.com 2009-12-24 13:40:10 ---- If an issues have a borrowernumber set as NULL, all checkouts/checkins of the item will broke circulation for this item. When a document is returned, all issues for itself should be moved to old_issues. --- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:22 UTC --- This bug was previously known as _bug_ 4002 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=4002 Actual time not defined. Setting to 0.0 Setting qa contact to the default for this product. This bug either had no qa contact or an invalid one.
I see two patch submissions with this Bug number: http://lists.koha-community.org/pipermail/koha-patches/2009-December/010764.html http://lists.koha-community.org/pipermail/koha-patches/2009-December/010761.html ...and reference to one of these having been pushed to 3.0.x.
Created attachment 3906 [details] [review] patch obsoletes http://lists.koha-community.org/pipermail/koha-patches/2009-December/010764.html
Created attachment 3917 [details] [review] patch
Created attachment 4120 [details] [review] Signed off Patch
+ $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) + SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE itemnumber = issues.itemnumber)"); + $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)");
Created attachment 4134 [details] [review] patch Corrected the table name. Funny thing is that mysql does not complain.
Yep, totally caught me too, luckily Mike Hafen spotted it. Many eyes make bugs shallow and all that :)
Created attachment 4454 [details] [review] bug_4002: issues referential integrity Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
The patch works fine for fresh install. There is a problem with updates: the change to NOT NULL of some columns requires the contraints to be removed, otherwise, mySQL complains (and don't do anything) The lines $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT"); $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT"); $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL"); $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL"); will issue a message (modify failed). I've moved the DROP FOREIGN KEY just before the ALTER TABLE, and it works fine now. Marking as passed QA, but chris, it's worth a complementary check...
Created attachment 4814 [details] [review] proposed fix signed-off
Created attachment 4815 [details] [review] proposed fix signed-off oups, uploaded a wrong patch, sorry. This one is correct $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_1`"); $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`"); now before : $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT"); $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT"); $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL"); $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL");
Pushed, please test
This appears to be working fine. Closing.