Bugzilla – Attachment 76509 Details for
Bug 20271
Merge deleted biblio, biblioitems, biblio_metadata, and items tables
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 20271: (QA follow-up) Make the atomicupdate idempotent
Bug-20271-QA-follow-up-Make-the-atomicupdate-idemp.patch (text/plain), 11.59 KB, created by
Josef Moravec
on 2018-06-27 13:38:59 UTC
(
hide
)
Description:
Bug 20271: (QA follow-up) Make the atomicupdate idempotent
Filename:
MIME Type:
Creator:
Josef Moravec
Created:
2018-06-27 13:38:59 UTC
Size:
11.59 KB
patch
obsolete
>From 3f6fdf215f4bf38373a14c3a58589fddc5d7b9d1 Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@ptfs-europe.com> >Date: Tue, 12 Jun 2018 15:13:40 +0100 >Subject: [PATCH] Bug 20271: (QA follow-up) Make the atomicupdate idempotent > >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> > >Signed-off-by: Josef Moravec <josef.moravec@gmail.com> >--- > ...eleteitems_tables_with_their_alive_cousins.perl | 159 +++++++++++---------- > 1 file changed, 80 insertions(+), 79 deletions(-) > >diff --git a/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl b/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl >index 5b24e99..5a29de7 100644 >--- a/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl >+++ b/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl >@@ -1,96 +1,97 @@ > $DBversion = 'XXX'; # will be replaced by the RM > if( CheckVersion( $DBversion ) ) { >- $dbh->do( "ALTER TABLE biblio ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >- $dbh->do( "ALTER TABLE biblioitems ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >- $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >- $dbh->do( "ALTER TABLE items ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >+ if (TableExists('deletedbiblio') && TableExists('deletedbiblioitems') && TableExists('deletedbiblio_metadata')) { >+ $dbh->do( "ALTER TABLE biblio ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >+ $dbh->do( "ALTER TABLE biblioitems ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >+ $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; >+ $dbh->do( "ALTER TABLE items ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr; > >- # Need to disable foreign keys on deletedbiblio_metadata to avoid cascading deletes from deletedbiblio >- # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so any key must be dropped >- DropAllForeignKeys('deletedbiblio_metadata'); >- $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_on FROM deletedbiblio" ) or warn $DBI::errstr; >- # We also need to make sure foreign keys references are in place, as Mysql < 5.7 aborts on foreign key errors >- $dbh->do( "INSERT IGNORE INTO biblioitems ( >- SELECT *, timestamp AS deleted_on FROM deletedbiblioitems >- WHERE biblionumber IN (SELECT biblionumber FROM biblio) >- )" ) or warn $DBI::errstr; >- # biblio_metadata needs special handling since there is an extra autoincrement id that cannot be moved >- $dbh->do( "INSERT IGNORE INTO biblio_metadata (biblionumber, format, marcflavour, metadata, timestamp, deleted_on) ( >- SELECT biblionumber, format, marcflavour, metadata, timestamp, timestamp AS deleted_on FROM deletedbiblio_metadata >- WHERE biblionumber IN (SELECT biblionumber FROM biblio) >- )" ) or warn $DBI::errstr; >- $dbh->do( "INSERT IGNORE INTO items ( >- SELECT *, timestamp AS deleted_on FROM deleteditems >- WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems) >- AND homebranch IN (SELECT homebranch FROM branches) >- AND holdingbranch IN (SELECT holdingbranch FROM branches) >- AND biblionumber IN (SELECT biblionumber FROM biblio) >- )" ) or warn $DBI::errstr; >+ # Need to disable foreign keys on deletedbiblio_metadata to avoid cascading deletes from deletedbiblio >+ # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so any key must be dropped >+ DropAllForeignKeys('deletedbiblio_metadata'); >+ $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_on FROM deletedbiblio" ) or warn $DBI::errstr; >+ # We also need to make sure foreign keys references are in place, as Mysql < 5.7 aborts on foreign key errors >+ $dbh->do( "INSERT IGNORE INTO biblioitems ( >+ SELECT *, timestamp AS deleted_on FROM deletedbiblioitems >+ WHERE biblionumber IN (SELECT biblionumber FROM biblio) >+ )" ) or warn $DBI::errstr; >+ # biblio_metadata needs special handling since there is an extra autoincrement id that cannot be moved >+ $dbh->do( "INSERT IGNORE INTO biblio_metadata (biblionumber, format, marcflavour, metadata, timestamp, deleted_on) ( >+ SELECT biblionumber, format, marcflavour, metadata, timestamp, timestamp AS deleted_on FROM deletedbiblio_metadata >+ WHERE biblionumber IN (SELECT biblionumber FROM biblio) >+ )" ) or warn $DBI::errstr; >+ $dbh->do( "INSERT IGNORE INTO items ( >+ SELECT *, timestamp AS deleted_on FROM deleteditems >+ WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems) >+ AND homebranch IN (SELECT homebranch FROM branches) >+ AND holdingbranch IN (SELECT holdingbranch FROM branches) >+ AND biblionumber IN (SELECT biblionumber FROM biblio) >+ )" ) or warn $DBI::errstr; > >- # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them >- { >- my $sth = $dbh->prepare("DELETE FROM deleteditems WHERE itemnumber IN (SELECT itemnumber FROM items)"); >- $sth->execute(); >- $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deleteditems"); >- $sth->execute(); >- my $row = $sth->fetchrow_hashref; >- if ($row->{count}) { >- print "Warning to database administrator:\n" >- . "There were $row->{count} deleteditems that could not be moved, please check '_deleteditems'.\n"; >- $dbh->do("RENAME TABLE deleteditems TO _deleteditems"); >- } else { >- $dbh->do("DROP TABLE deleteditems"); >+ # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them >+ { >+ my $sth = $dbh->prepare("DELETE FROM deleteditems WHERE itemnumber IN (SELECT itemnumber FROM items)"); >+ $sth->execute(); >+ $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deleteditems"); >+ $sth->execute(); >+ my $row = $sth->fetchrow_hashref; >+ if ($row->{count}) { >+ print "Warning to database administrator:\n" >+ . "There were $row->{count} deleteditems that could not be moved, please check '_deleteditems'.\n"; >+ $dbh->do("RENAME TABLE deleteditems TO _deleteditems"); >+ } else { >+ $dbh->do("DROP TABLE deleteditems"); >+ } > } >- } > >- { >- my $sth = $dbh->prepare("DELETE FROM deletedbiblio_metadata WHERE biblionumber IN (SELECT biblionumber FROM biblio_metadata)"); >- $sth->execute(); >- $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio_metadata"); >- $sth->execute(); >- my $row = $sth->fetchrow_hashref; >- if ($row->{count}) { >- print "Warning to database administrator:\n" >- . "There were $row->{count} deletedbiblio_metadata that could not be moved, please check '_deletedbiblio_metadata'.\n"; >- $dbh->do("RENAME TABLE deletedbiblio_metadata TO _deletedbiblio_metadata"); >- } else { >- $dbh->do("DROP TABLE deletedbiblio_metadata"); >+ { >+ my $sth = $dbh->prepare("DELETE FROM deletedbiblio_metadata WHERE biblionumber IN (SELECT biblionumber FROM biblio_metadata)"); >+ $sth->execute(); >+ $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio_metadata"); >+ $sth->execute(); >+ my $row = $sth->fetchrow_hashref; >+ if ($row->{count}) { >+ print "Warning to database administrator:\n" >+ . "There were $row->{count} deletedbiblio_metadata that could not be moved, please check '_deletedbiblio_metadata'.\n"; >+ $dbh->do("RENAME TABLE deletedbiblio_metadata TO _deletedbiblio_metadata"); >+ } else { >+ $dbh->do("DROP TABLE deletedbiblio_metadata"); >+ } > } >- } > >- { >- my $sth = $dbh->prepare("DELETE FROM deletedbiblioitems WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)"); >- $sth->execute(); >- $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblioitems"); >- $sth->execute(); >- my $row = $sth->fetchrow_hashref; >- if ($row->{count}) { >- print "Warning to database administrator:\n" >- . "There were $row->{count} deletedbiblioitems that could not be moved, please check '_deletedbiblioitems'.\n"; >- $dbh->do("RENAME TABLE deletedbiblioitems TO _deletedbiblioitems"); >- } else { >- $dbh->do("DROP TABLE deletedbiblioitems"); >+ { >+ my $sth = $dbh->prepare("DELETE FROM deletedbiblioitems WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)"); >+ $sth->execute(); >+ $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblioitems"); >+ $sth->execute(); >+ my $row = $sth->fetchrow_hashref; >+ if ($row->{count}) { >+ print "Warning to database administrator:\n" >+ . "There were $row->{count} deletedbiblioitems that could not be moved, please check '_deletedbiblioitems'.\n"; >+ $dbh->do("RENAME TABLE deletedbiblioitems TO _deletedbiblioitems"); >+ } else { >+ $dbh->do("DROP TABLE deletedbiblioitems"); >+ } > } >- } > >- { >- my $sth = $dbh->prepare("DELETE FROM deletedbiblio WHERE biblionumber IN (SELECT biblionumber FROM biblio)"); >- $sth->execute(); >- $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio"); >- $sth->execute(); >- my $row = $sth->fetchrow_hashref; >- if ($row->{count}) { >- print "Warning to database administrator:\n" >- . "There were $row->{count} deletedbiblio that could not be moved, please check '_deletedbiblio'.\n"; >- $dbh->do("RENAME TABLE deletedbiblio TO _deletedbiblio"); >- } else { >- $dbh->do("DROP TABLE deletedbiblio"); >+ { >+ my $sth = $dbh->prepare("DELETE FROM deletedbiblio WHERE biblionumber IN (SELECT biblionumber FROM biblio)"); >+ $sth->execute(); >+ $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio"); >+ $sth->execute(); >+ my $row = $sth->fetchrow_hashref; >+ if ($row->{count}) { >+ print "Warning to database administrator:\n" >+ . "There were $row->{count} deletedbiblio that could not be moved, please check '_deletedbiblio'.\n"; >+ $dbh->do("RENAME TABLE deletedbiblio TO _deletedbiblio"); >+ } else { >+ $dbh->do("DROP TABLE deletedbiblio"); >+ } > } > } > > { >- my $sth = $dbh->prepare("UPDATE systempreferences SET options='Koha\'s deleted biblios will never be removed (persistent), might be removed (transient), or will always be removed (no)' WHERE variable='OAI-PMH:DeletedRecord'"); >- $sth->execute(); >+ $dbh->do("UPDATE systempreferences SET options='Koha''s deleted biblios will never be removed (persistent), might be removed (transient), or will always be removed (no)' WHERE variable='OAI-PMH:DeletedRecord'") or warn $DBI::errstr; > } > > SetVersion( $DBversion ); >-- >2.1.4
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 20271
:
72300
|
72799
|
72800
|
73091
|
73092
|
73093
|
73535
|
73536
|
73537
|
73538
|
73539
|
73540
|
73597
|
73759
|
73760
|
73761
|
73762
|
73763
|
73764
|
73765
|
74127
|
74128
|
74129
|
74130
|
74131
|
74132
|
74133
|
74134
|
74135
|
74136
|
74137
|
74138
|
74139
|
74140
|
74141
|
74142
|
74182
|
74183
|
74184
|
74185
|
74186
|
74187
|
74188
|
74189
|
74190
|
74223
|
74407
|
74408
|
74409
|
74410
|
74411
|
74412
|
74413
|
74414
|
74415
|
74416
|
75265
|
75347
|
75894
|
75897
|
75898
|
75899
|
75910
|
75977
|
75978
|
75979
|
75980
|
75981
|
75982
|
75983
|
75984
|
75985
|
75986
|
75987
|
75988
|
75989
|
75990
|
75991
|
75992
|
75993
|
76047
|
76048
|
76255
|
76256
|
76257
|
76258
|
76259
|
76260
|
76261
|
76262
|
76263
|
76264
|
76265
|
76266
|
76267
|
76268
|
76269
|
76270
|
76271
|
76272
|
76273
|
76274
|
76275
|
76276
|
76277
|
76493
|
76494
|
76495
|
76496
|
76497
|
76498
|
76499
|
76500
|
76501
|
76502
|
76503
|
76504
|
76505
|
76506
|
76507
|
76508
|
76509
|
76510
|
76511
|
76512
|
76513
|
76514