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