MySQLTransfer.pm 7.42 KB
Newer Older
1 2 3 4
=pod 

=head1 NAME

5
    Bio::EnsEMBL::Hive::RunnableDB::MySQLTransfer
6 7 8

=head1 SYNOPSIS

9 10 11
    standaloneJob.pl Bio::EnsEMBL::Hive::RunnableDB::MySQLTransfer --table meta_foo \
                --src_db_conn mysql://ensadmin:${ENSADMIN_PSW}@127.0.0.1:2913/lg4_compara_homology_merged_64 \
                --dest_db_conn mysql://ensadmin:${ENSADMIN_PSW}@127.0.0.1:2912/lg4_compara_families_64
12 13 14

=head1 DESCRIPTION

15 16 17 18 19 20
    This RunnableDB module lets you copy/merge rows from a table in one database into table with the same name in another.
    There are three modes ('overwrite', 'topup' and 'insertignore') that do it very differently.
    Also, 'where' parameter allows to select subset of rows to be copied/merged over.

=head1 LICENSE

21
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
Matthieu Muffato's avatar
Matthieu Muffato committed
22
    Copyright [2016-2018] EMBL-European Bioinformatics Institute
23 24 25 26 27 28 29 30 31

    Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software distributed under the License
    is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and limitations under the License.
32 33 34

=head1 CONTACT

35
    Please subscribe to the Hive mailing list:  http://listserver.ebi.ac.uk/mailman/listinfo/ehive-users  to discuss Hive-related questions or to be notified of our updates
36 37 38 39 40 41 42

=cut


package Bio::EnsEMBL::Hive::RunnableDB::MySQLTransfer;

use strict;
43 44 45
use warnings;

use Bio::EnsEMBL::Hive::Utils ('go_figure_dbc');
46 47 48

use base ('Bio::EnsEMBL::Hive::Process');

49 50 51 52 53 54 55 56
sub param_defaults {
    return {
        'src_db_conn'   => '',
        'dest_db_conn'  => '',
        'mode'          => 'overwrite',
        'table'         => '',
        'where'         => undef,
        'filter_cmd'    => undef,
57
        'lock_tables'   => 0,
58 59 60 61
    };
}


62 63 64 65 66 67 68 69 70 71 72
=head2 fetch_input

    Description : Implements fetch_input() interface method of Bio::EnsEMBL::Hive::Process that is used to read in parameters and load data.
                  Here it parses parameters, creates up to two database handles and finds the pre-execution row counts filtered by '$where'.

    param('src_db_conn'):   connection parameters to the source database (if different from hive_db)

    param('dest_db_conn'):  connection parameters to the destination database (if different from hive_db - at least one of the two will have to be different)

    param('mode'):          'overwrite' (default), 'topup' or 'insertignore'

73
    param('where'):         filter for rows to be copied/merged.
74

75
    param('table'):         table name to be copied/merged.
76

77 78
    param('lock_tables'):   [boolean] when 1, lock tables when dumping the source database. Default if not set (or set to 0) is to not lock (runs mysqldump with --skip-lock-tables) 

79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
=cut

sub fetch_input {
    my $self = shift;

    my $src_db_conn  = $self->param('src_db_conn');
    my $dest_db_conn = $self->param('dest_db_conn');

    $self->input_job->transient_error(0);
    if($src_db_conn eq $dest_db_conn) {
        die "Please either specify 'src_db_conn' or 'dest_db_conn' or make them different\n";
    }
    my $table = $self->param('table') or die "Please specify 'table' parameter\n";
    $self->input_job->transient_error(1);

94 95
    my $src_dbc     = $src_db_conn  ? go_figure_dbc( $src_db_conn )  : $self->data_dbc;
    my $dest_dbc    = $dest_db_conn ? go_figure_dbc( $dest_db_conn ) : $self->data_dbc;
96 97 98

    $self->param('src_dbc',         $src_dbc);
    $self->param('dest_dbc',        $dest_dbc);
99

100
    my $where = $self->param('where');
101

102
    $self->param('src_before',  $self->get_row_count($src_dbc,  $table, $where) );
103

104
    if($self->param('mode') ne 'overwrite') {
105
        $self->param('dest_before_all', $self->get_row_count($dest_dbc, $table) );
106 107 108 109 110 111 112 113 114 115 116 117 118
    }
}

=head2 run

    Description : Implements run() interface method of Bio::EnsEMBL::Hive::Process that is used to perform the main bulk of the job (minus input and output).
                  Here the actual data transfer is attempted.

=cut

sub run {
    my $self = shift;

119 120
    my $src_dbc     = $self->param('src_dbc');
    my $dest_dbc    = $self->param('dest_dbc');
121

122
    my $mode        = $self->param('mode');
123 124
    my $table       = $self->param('table');
    my $where       = $self->param('where');
125
    my $filter_cmd  = $self->param('filter_cmd');
126
    my $lock_tables = $self->param('lock_tables');
127 128 129

    my $cmd = 'mysqldump '
                . { 'overwrite' => '', 'topup' => '--no-create-info ', 'insertignore' => '--no-create-info --insert-ignore ' }->{$mode}
130
                . ($lock_tables ? '' : '--skip-lock-tables ')
131 132
                . $self->mysql_conn_from_dbc($src_dbc)
                . " $table "
133
                . (defined($where) ? "--where '$where' " : '')
134 135
                . '| '
                . ($filter_cmd ? "$filter_cmd | " : '')
136 137
                . 'mysql '
                . $self->mysql_conn_from_dbc($dest_dbc);
138

139
    if(my $return_value = system(bash => (-o => 'pipefail', -c => $cmd))) {
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154
        $return_value >>= 8;
        die "system( $cmd ) failed: $return_value";
    }
}

=head2 write_output

    Description : Implements write_output() interface method of Bio::EnsEMBL::Hive::Process that is used to deal with job's output after the execution.
                  Here we compare the number of rows and detect problems.

=cut

sub write_output {
    my $self = shift;

155
    my $dest_dbc    = $self->param('dest_dbc');
156

157 158 159
    my $mode        = $self->param('mode');
    my $table       = $self->param('table');
    my $where       = $self->param('where');
160

161
    my $src_before  = $self->param('src_before');
162 163

    if($mode eq 'overwrite') {
164
        my $dest_after      = $self->get_row_count($dest_dbc,  $table, $where);
165 166

        if($src_before == $dest_after) {
167
            $self->warning("Successfully copied $src_before '$table' rows");
168 169 170 171 172
        } else {
            die "Could not copy '$table' rows: $src_before rows from source copied into $dest_after rows in target\n";
        }
    } else {

173
        my $dest_row_increase = $self->get_row_count($dest_dbc, $table) - $self->param('dest_before_all');
174 175

        if($mode eq 'topup') {
Matthieu Muffato's avatar
Matthieu Muffato committed
176
            if($src_before <= $dest_row_increase) {
177
                $self->warning("Cannot check success/failure in this mode, but the number of '$table' rows in target increased by $dest_row_increase (higher than $src_before)");
178 179 180 181
            } else {
                die "Could not add rows: $src_before '$table' rows from source copied into $dest_row_increase rows in target\n";
            }
        } elsif($mode eq 'insertignore') {
182
            $self->warning("Cannot check success/failure in this mode, but the number of '$table' rows in target increased by $dest_row_increase");
183 184 185 186 187 188 189
        }
    }
}

########################### private subroutines ####################################

sub get_row_count {
190
    my ($self, $dbc, $table, $where) = @_;
191

192
    my $sql = "SELECT count(*) FROM $table" . (defined($where) ? " WHERE $where" : '');
193

194
    my $sth = $dbc->prepare($sql);
195 196 197 198 199 200 201
    $sth->execute();
    my ($row_count) = $sth->fetchrow_array();
    $sth->finish;

    return $row_count;
}

202 203
sub mysql_conn_from_dbc {
    my ($self, $dbc) = @_;
204

205
    return '--host='.$dbc->host.' --port='.$dbc->port." --user='".$dbc->username."' --password='".$dbc->password."' ".$dbc->dbname;
206 207 208 209
}

1;