View | Details | Raw Unified | Return to bug 20271
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl (-1 / +79 lines)
Line 0 Link Here
0
- 
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do( "ALTER TABLE biblio ADD COLUMN deleted_at datetime DEFAULT NULL" ) or warn $DBI::errstr;
4
    $dbh->do( "ALTER TABLE biblioitems ADD COLUMN deleted_at datetime DEFAULT NULL" ) or warn $DBI::errstr;
5
    $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN deleted_at datetime DEFAULT NULL" ) or warn $DBI::errstr;
6
    $dbh->do( "ALTER TABLE items ADD COLUMN deleted_at datetime DEFAULT NULL" ) or warn $DBI::errstr;
7
8
    # Need to drop foreign keys to avoid cascading deletes from deletedbiblio
9
    # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so we need to delete either of the two
10
    # one would inevitably fail so we ignore any errors
11
    $dbh->do( "ALTER TABLE deletedbiblio_metadata DROP FOREIGN KEY deletedbiblio_metadata_fk_1" );
12
    $dbh->do( "ALTER TABLE deletedbiblio_metadata DROP FOREIGN KEY deletedrecord_metadata_fk_1" );
13
14
    $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_at FROM deletedbiblio" ) or warn $DBI::errstr;
15
    $dbh->do( "INSERT IGNORE INTO biblioitems SELECT *, timestamp AS deleted_at FROM deletedbiblioitems" ) or warn $DBI::errstr;
16
    $dbh->do( "INSERT IGNORE INTO biblio_metadata SELECT *, timestamp AS deleted_at FROM deletedbiblio_metadata" ) or warn $DBI::errstr;
17
    $dbh->do( "INSERT IGNORE INTO items SELECT *, timestamp AS deleted_at FROM deleteditems" ) or warn $DBI::errstr;
18
19
    # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them
20
    {
21
        my $sth = $dbh->prepare("DELETE FROM deleteditems WHERE itemnumber IN (SELECT itemnumber FROM items)");
22
        $sth->execute();
23
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deleteditems");
24
        $sth->execute();
25
        my $row = $sth->fetchrow_hashref;
26
        if ($row->{count}) {
27
            warn "There were $row->{count} deleteditems that could not be moved, please check '_deleteditems'.";
28
            $dbh->do("RENAME TABLE deleteditems TO _deleteditems");
29
        } else {
30
            $dbh->do("DROP TABLE deleteditems");
31
        }
32
    }
33
34
    {
35
        my $sth = $dbh->prepare("DELETE FROM deletedbiblio_metadata WHERE biblionumber IN (SELECT biblionumber FROM biblio_metadata)");
36
        $sth->execute();
37
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio_metadata");
38
        $sth->execute();
39
        my $row = $sth->fetchrow_hashref;
40
        if ($row->{count}) {
41
            warn "There were $row->{count} deletedbiblio_metadata that could not be moved, please check '_deletedbiblio_metadata'.";
42
            $dbh->do("RENAME TABLE deletedbiblio_metadata TO _deletedbiblio_metadata");
43
        } else {
44
            $dbh->do("DROP TABLE deletedbiblio_metadata");
45
        }
46
    }
47
48
    {
49
        my $sth = $dbh->prepare("DELETE FROM deletedbiblio WHERE biblionumber IN (SELECT biblionumber FROM biblio)");
50
        $sth->execute();
51
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio WHERE biblionumber NOT IN (SELECT biblionumber FROM deletedbiblioitems)");
52
        $sth->execute();
53
        my $row = $sth->fetchrow_hashref;
54
        if ($row->{count}) {
55
            warn "There were $row->{count} deletedbiblio that could not be moved, please check '_deletedbiblio'.";
56
            $dbh->do("RENAME TABLE deletedbiblio TO _deletedbiblio");
57
        } else {
58
            $dbh->do("DROP TABLE deletedbiblio");
59
        }
60
    }
61
62
    {
63
        my $sth = $dbh->prepare("DELETE FROM deletedbiblioitems WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)");
64
        $sth->execute();
65
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblioitems");
66
        $sth->execute();
67
        my $row = $sth->fetchrow_hashref;
68
        if ($row->{count}) {
69
            warn "There were $row->{count} deletedbiblioitems that could not be moved, please check '_deletedbiblioitems'.";
70
            $dbh->do("RENAME TABLE deletedbiblioitems TO _deletedbiblioitems");
71
        } else {
72
            $dbh->do("DROP TABLE deletedbiblioitems");
73
        }
74
    }
75
76
77
    SetVersion( $DBversion );
78
    print "Upgrade to $DBversion done (Bug 20271 - Merge deletedbiblio* and deleteitems tables with their alive cousins)\n";
79
}

Return to bug 20271