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 / +77 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 disable foreign keys on deletedbiblio_metadata to avoid cascading deletes from deletedbiblio
9
    # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so dropping would fail
10
    $dbh->do( "ALTER TABLE deletedbiblio_metadata DISABLE KEYS" );
11
12
    $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_at FROM deletedbiblio" ) or warn $DBI::errstr;
13
    $dbh->do( "INSERT IGNORE INTO biblioitems SELECT *, timestamp AS deleted_at FROM deletedbiblioitems" ) or warn $DBI::errstr;
14
    $dbh->do( "INSERT IGNORE INTO biblio_metadata SELECT *, timestamp AS deleted_at FROM deletedbiblio_metadata" ) or warn $DBI::errstr;
15
    $dbh->do( "INSERT IGNORE INTO items SELECT *, timestamp AS deleted_at FROM deleteditems" ) or warn $DBI::errstr;
16
17
    # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them
18
    {
19
        my $sth = $dbh->prepare("DELETE FROM deleteditems WHERE itemnumber IN (SELECT itemnumber FROM items)");
20
        $sth->execute();
21
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deleteditems");
22
        $sth->execute();
23
        my $row = $sth->fetchrow_hashref;
24
        if ($row->{count}) {
25
            warn "There were $row->{count} deleteditems that could not be moved, please check '_deleteditems'.";
26
            $dbh->do("RENAME TABLE deleteditems TO _deleteditems");
27
        } else {
28
            $dbh->do("DROP TABLE deleteditems");
29
        }
30
    }
31
32
    {
33
        my $sth = $dbh->prepare("DELETE FROM deletedbiblio_metadata WHERE biblionumber IN (SELECT biblionumber FROM biblio_metadata)");
34
        $sth->execute();
35
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio_metadata");
36
        $sth->execute();
37
        my $row = $sth->fetchrow_hashref;
38
        if ($row->{count}) {
39
            warn "There were $row->{count} deletedbiblio_metadata that could not be moved, please check '_deletedbiblio_metadata'.";
40
            $dbh->do("RENAME TABLE deletedbiblio_metadata TO _deletedbiblio_metadata");
41
        } else {
42
            $dbh->do("DROP TABLE deletedbiblio_metadata");
43
        }
44
    }
45
46
    {
47
        my $sth = $dbh->prepare("DELETE FROM deletedbiblio WHERE biblionumber IN (SELECT biblionumber FROM biblio)");
48
        $sth->execute();
49
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio WHERE biblionumber NOT IN (SELECT biblionumber FROM deletedbiblioitems)");
50
        $sth->execute();
51
        my $row = $sth->fetchrow_hashref;
52
        if ($row->{count}) {
53
            warn "There were $row->{count} deletedbiblio that could not be moved, please check '_deletedbiblio'.";
54
            $dbh->do("RENAME TABLE deletedbiblio TO _deletedbiblio");
55
        } else {
56
            $dbh->do("DROP TABLE deletedbiblio");
57
        }
58
    }
59
60
    {
61
        my $sth = $dbh->prepare("DELETE FROM deletedbiblioitems WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)");
62
        $sth->execute();
63
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblioitems");
64
        $sth->execute();
65
        my $row = $sth->fetchrow_hashref;
66
        if ($row->{count}) {
67
            warn "There were $row->{count} deletedbiblioitems that could not be moved, please check '_deletedbiblioitems'.";
68
            $dbh->do("RENAME TABLE deletedbiblioitems TO _deletedbiblioitems");
69
        } else {
70
            $dbh->do("DROP TABLE deletedbiblioitems");
71
        }
72
    }
73
74
75
    SetVersion( $DBversion );
76
    print "Upgrade to $DBversion done (Bug 20271 - Merge deletedbiblio* and deleteitems tables with their alive cousins)\n";
77
}

Return to bug 20271