From 3fa514133b33aca3727111d2ddfdd0bdb3d5d639 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Wed, 6 Apr 2016 11:34:26 -0300 Subject: [PATCH] Bug 14598: (QA followup) use deleted{items|biblioitems} during upgrade If the 'statistics' table contains circulation information about items that are no longer on the DB it will raise a warning. This patch makes the updatedatabase.pl script use the deleteditems and deletedbiblioitems tables to get information for those items on the statistics table. To reproduce: - Have your sample DB contain some circulation data on the statistics table - Make sure some of them have NULL itemtype: > UPDATE statistics SET itemtype = NULL WHERE type='return'; - Check the upgrade query catches them: > SELECT s.itemnumber, i.itype, b.itemtype FROM ( SELECT DISTINCT itemnumber FROM statistics WHERE ( type = "return" OR type = "localuse" ) AND itemtype IS NULL ) s LEFT JOIN ( SELECT itemnumber,biblionumber, itype FROM items UNION SELECT itemnumber,biblionumber, itype FROM deleteditems ) i ON (s.itemnumber=i.itemnumber) LEFT JOIN ( SELECT biblionumber, itemtype FROM biblioitems UNION SELECT biblionumber, itemtype FROM deletedbiblioitems ) b ON (i.biblionumber=b.biblionumber); +------------+-------+----------+ | itemnumber | itype | itemtype | +------------+-------+----------+ | 732 | BK | BK | | 731 | BK | BK | +------------+-------+----------+ 2 rows in set (0.00 sec) - Delete the items, and some biblio too. - Re-run the query => SUCCESS: Same results - Go reset to NULL the itemtypes > UPDATE statistics SET itemtype = NULL WHERE type='return'; - Run the updatedatabase.pl script: $ sudo koha-shell koahdev ; cd kohaclone $ perl installer/data/mysql/updatedatabase.pl => SUCCESS: No warnings Note: It is possible that on production sites, if the sysadmin is cleaning the deleted{items|biblioitems|biblio} tables, there will be warnings. This is expected as they need to know some data lacks information. Signed-off-by: Tomas Cohen Arazi Signed-off-by: Marcel de Rooy Signed-off-by: Kyle M Hall --- installer/data/mysql/updatedatabase.pl | 35 +++++++++++++++++--------- 1 file changed, 23 insertions(+), 12 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 43986b1802..5aadb156cc 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -13348,18 +13348,29 @@ $DBversion = "3.23.00.XXX"; if ( CheckVersion($DBversion) ) { $sth = $dbh->prepare(q{ - SELECT s.itemnumber, i.itype, b.itemtype FROM - ( SELECT DISTINCT itemnumber - FROM statistics - WHERE ( type = "return" OR type = "localuse" ) AND itemtype IS NULL ) s - LEFT JOIN - ( SELECT itemnumber,biblionumber, itype - FROM items ) i - ON (s.itemnumber=i.itemnumber) - LEFT JOIN - ( SELECT biblionumber, itemtype - FROM biblioitems ) b - ON (i.biblionumber=b.biblionumber) + SELECT s.itemnumber, i.itype, b.itemtype + FROM + ( SELECT DISTINCT itemnumber + FROM statistics + WHERE ( type = "return" OR type = "localuse" ) AND + itemtype IS NULL + ) s + LEFT JOIN + ( SELECT itemnumber,biblionumber, itype + FROM items + UNION + SELECT itemnumber,biblionumber, itype + FROM deleteditems + ) i + ON (s.itemnumber=i.itemnumber) + LEFT JOIN + ( SELECT biblionumber, itemtype + FROM biblioitems + UNION + SELECT biblionumber, itemtype + FROM deletedbiblioitems + ) b + ON (i.biblionumber=b.biblionumber); }); $sth->execute(); -- 2.39.5