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 (-85 / +85 lines)
Lines 1-96 Link Here
1
$DBversion = 'XXX';  # will be replaced by the RM
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do( "ALTER TABLE biblio ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
3
    if (TableExists('deletedbiblio') && TableExists('deletedbiblioitems') && TableExists('deletedbiblio_metadata')) {
4
    $dbh->do( "ALTER TABLE biblioitems ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
4
        $dbh->do( "ALTER TABLE biblio ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
5
    $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
5
        $dbh->do( "ALTER TABLE biblioitems ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
6
    $dbh->do( "ALTER TABLE items ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
6
        $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
7
7
        $dbh->do( "ALTER TABLE items ADD COLUMN deleted_on datetime DEFAULT NULL" ) or warn $DBI::errstr;
8
    # Need to disable foreign keys on deletedbiblio_metadata to avoid cascading deletes from deletedbiblio
8
    
9
    # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so any key must be dropped
9
        # Need to disable foreign keys on deletedbiblio_metadata to avoid cascading deletes from deletedbiblio
10
    DropAllForeignKeys('deletedbiblio_metadata');
10
        # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so any key must be dropped
11
    $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_on FROM deletedbiblio" ) or warn $DBI::errstr;
11
        DropAllForeignKeys('deletedbiblio_metadata');
12
    # We also need to make sure foreign keys references are in place, as Mysql < 5.7 aborts on foreign key errors
12
        $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_on FROM deletedbiblio" ) or warn $DBI::errstr;
13
    $dbh->do( "INSERT IGNORE INTO biblioitems (
13
        # We also need to make sure foreign keys references are in place, as Mysql < 5.7 aborts on foreign key errors
14
        SELECT *, timestamp AS deleted_on FROM deletedbiblioitems
14
        $dbh->do( "INSERT IGNORE INTO biblioitems (
15
        WHERE biblionumber IN (SELECT biblionumber FROM biblio)
15
            SELECT *, timestamp AS deleted_on FROM deletedbiblioitems
16
    )" ) or warn $DBI::errstr;
16
            WHERE biblionumber IN (SELECT biblionumber FROM biblio)
17
    # biblio_metadata needs special handling since there is an extra autoincrement id that cannot be moved
17
        )" ) or warn $DBI::errstr;
18
    $dbh->do( "INSERT IGNORE INTO biblio_metadata (biblionumber, format, marcflavour, metadata, timestamp, deleted_on) (
18
        # biblio_metadata needs special handling since there is an extra autoincrement id that cannot be moved
19
        SELECT biblionumber, format, marcflavour, metadata, timestamp, timestamp AS deleted_on FROM deletedbiblio_metadata
19
        $dbh->do( "INSERT IGNORE INTO biblio_metadata (biblionumber, format, marcflavour, metadata, timestamp, deleted_on) (
20
        WHERE biblionumber IN (SELECT biblionumber FROM biblio)
20
            SELECT biblionumber, format, marcflavour, metadata, timestamp, timestamp AS deleted_on FROM deletedbiblio_metadata
21
    )" ) or warn $DBI::errstr;
21
            WHERE biblionumber IN (SELECT biblionumber FROM biblio)
22
    $dbh->do( "INSERT IGNORE INTO items (
22
        )" ) or warn $DBI::errstr;
23
        SELECT *, timestamp AS deleted_on FROM deleteditems
23
        $dbh->do( "INSERT IGNORE INTO items (
24
        WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)
24
            SELECT *, timestamp AS deleted_on FROM deleteditems
25
        AND homebranch IN (SELECT homebranch FROM branches)
25
            WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)
26
        AND holdingbranch IN (SELECT holdingbranch FROM branches)
26
            AND homebranch IN (SELECT homebranch FROM branches)
27
        AND biblionumber IN (SELECT biblionumber FROM biblio)
27
            AND holdingbranch IN (SELECT holdingbranch FROM branches)
28
    )" ) or warn $DBI::errstr;
28
            AND biblionumber IN (SELECT biblionumber FROM biblio)
29
29
        )" ) or warn $DBI::errstr;
30
    # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them
30
    
31
    {
31
        # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them
32
        my $sth = $dbh->prepare("DELETE FROM deleteditems WHERE itemnumber IN (SELECT itemnumber FROM items)");
32
        {
33
        $sth->execute();
33
            my $sth = $dbh->prepare("DELETE FROM deleteditems WHERE itemnumber IN (SELECT itemnumber FROM items)");
34
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deleteditems");
34
            $sth->execute();
35
        $sth->execute();
35
            $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deleteditems");
36
        my $row = $sth->fetchrow_hashref;
36
            $sth->execute();
37
        if ($row->{count}) {
37
            my $row = $sth->fetchrow_hashref;
38
            print "Warning to database administrator:\n"
38
            if ($row->{count}) {
39
                . "There were $row->{count} deleteditems that could not be moved, please check '_deleteditems'.\n";
39
                print "Warning to database administrator:\n"
40
            $dbh->do("RENAME TABLE deleteditems TO _deleteditems");
40
                    . "There were $row->{count} deleteditems that could not be moved, please check '_deleteditems'.\n";
41
        } else {
41
                $dbh->do("RENAME TABLE deleteditems TO _deleteditems");
42
            $dbh->do("DROP TABLE deleteditems");
42
            } else {
43
                $dbh->do("DROP TABLE deleteditems");
44
            }
43
        }
45
        }
44
    }
46
    
45
47
        {
46
    {
48
            my $sth = $dbh->prepare("DELETE FROM deletedbiblio_metadata WHERE biblionumber IN (SELECT biblionumber FROM biblio_metadata)");
47
        my $sth = $dbh->prepare("DELETE FROM deletedbiblio_metadata WHERE biblionumber IN (SELECT biblionumber FROM biblio_metadata)");
49
            $sth->execute();
48
        $sth->execute();
50
            $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio_metadata");
49
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio_metadata");
51
            $sth->execute();
50
        $sth->execute();
52
            my $row = $sth->fetchrow_hashref;
51
        my $row = $sth->fetchrow_hashref;
53
            if ($row->{count}) {
52
        if ($row->{count}) {
54
                print "Warning to database administrator:\n"
53
            print "Warning to database administrator:\n"
55
                    . "There were $row->{count} deletedbiblio_metadata that could not be moved, please check '_deletedbiblio_metadata'.\n";
54
                . "There were $row->{count} deletedbiblio_metadata that could not be moved, please check '_deletedbiblio_metadata'.\n";
56
                $dbh->do("RENAME TABLE deletedbiblio_metadata TO _deletedbiblio_metadata");
55
            $dbh->do("RENAME TABLE deletedbiblio_metadata TO _deletedbiblio_metadata");
57
            } else {
56
        } else {
58
                $dbh->do("DROP TABLE deletedbiblio_metadata");
57
            $dbh->do("DROP TABLE deletedbiblio_metadata");
59
            }
58
        }
60
        }
59
    }
61
    
60
62
        {
61
    {
63
            my $sth = $dbh->prepare("DELETE FROM deletedbiblioitems WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)");
62
        my $sth = $dbh->prepare("DELETE FROM deletedbiblioitems WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems)");
64
            $sth->execute();
63
        $sth->execute();
65
            $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblioitems");
64
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblioitems");
66
            $sth->execute();
65
        $sth->execute();
67
            my $row = $sth->fetchrow_hashref;
66
        my $row = $sth->fetchrow_hashref;
68
            if ($row->{count}) {
67
        if ($row->{count}) {
69
                print "Warning to database administrator:\n"
68
            print "Warning to database administrator:\n"
70
                    . "There were $row->{count} deletedbiblioitems that could not be moved, please check '_deletedbiblioitems'.\n";
69
                . "There were $row->{count} deletedbiblioitems that could not be moved, please check '_deletedbiblioitems'.\n";
71
                $dbh->do("RENAME TABLE deletedbiblioitems TO _deletedbiblioitems");
70
            $dbh->do("RENAME TABLE deletedbiblioitems TO _deletedbiblioitems");
72
            } else {
71
        } else {
73
                $dbh->do("DROP TABLE deletedbiblioitems");
72
            $dbh->do("DROP TABLE deletedbiblioitems");
74
            }
73
        }
75
        }
74
    }
76
    
75
77
        {
76
    {
78
            my $sth = $dbh->prepare("DELETE FROM deletedbiblio WHERE biblionumber IN (SELECT biblionumber FROM biblio)");
77
        my $sth = $dbh->prepare("DELETE FROM deletedbiblio WHERE biblionumber IN (SELECT biblionumber FROM biblio)");
79
            $sth->execute();
78
        $sth->execute();
80
            $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio");
79
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM deletedbiblio");
81
            $sth->execute();
80
        $sth->execute();
82
            my $row = $sth->fetchrow_hashref;
81
        my $row = $sth->fetchrow_hashref;
83
            if ($row->{count}) {
82
        if ($row->{count}) {
84
                print "Warning to database administrator:\n"
83
            print "Warning to database administrator:\n"
85
                . "There were $row->{count} deletedbiblio that could not be moved, please check '_deletedbiblio'.\n";
84
            . "There were $row->{count} deletedbiblio that could not be moved, please check '_deletedbiblio'.\n";
86
                $dbh->do("RENAME TABLE deletedbiblio TO _deletedbiblio");
85
            $dbh->do("RENAME TABLE deletedbiblio TO _deletedbiblio");
87
            } else {
86
        } else {
88
                $dbh->do("DROP TABLE deletedbiblio");
87
            $dbh->do("DROP TABLE deletedbiblio");
89
            }
88
        }
90
        }
89
    }
91
    }
90
92
91
    {
93
    {
92
        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'");
94
        $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;
93
        $sth->execute();
94
    }
95
    }
95
96
96
    SetVersion( $DBversion );
97
    SetVersion( $DBversion );
97
- 

Return to bug 20271