MySQLTransfer.pm 6.99 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-2014] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
22 23 24 25 26 27 28 29 30

    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.
31 32 33

=head1 CONTACT

34
    Please contact ehive-users@ebi.ac.uk mailing list with questions/suggestions.
35 36 37 38 39 40 41

=cut


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

use strict;
42 43 44
use warnings;

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

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

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


60 61 62 63 64 65 66 67 68 69 70
=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'

71
    param('where'):         filter for rows to be copied/merged.
72

73
    param('table'):         table name to be copied/merged.
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89

=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);

90 91
    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;
92 93 94

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

96
    my $where = $self->param('where');
97

98
    $self->param('src_before',  $self->get_row_count($src_dbc,  $table, $where) );
99

100
    if($self->param('mode') ne 'overwrite') {
101
        $self->param('dest_before_all', $self->get_row_count($dest_dbc, $table) );
102 103 104 105 106 107 108 109 110 111 112 113 114
    }
}

=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;

115 116
    my $src_dbc     = $self->param('src_dbc');
    my $dest_dbc    = $self->param('dest_dbc');
117

118
    my $mode        = $self->param('mode');
119 120
    my $table       = $self->param('table');
    my $where       = $self->param('where');
121
    my $filter_cmd  = $self->param('filter_cmd');
122 123 124

    my $cmd = 'mysqldump '
                . { 'overwrite' => '', 'topup' => '--no-create-info ', 'insertignore' => '--no-create-info --insert-ignore ' }->{$mode}
125 126
                . $self->mysql_conn_from_dbc($src_dbc)
                . " $table "
127
                . (defined($where) ? "--where '$where' " : '')
128 129
                . '| '
                . ($filter_cmd ? "$filter_cmd | " : '')
130 131
                . 'mysql '
                . $self->mysql_conn_from_dbc($dest_dbc);
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

    if(my $return_value = system($cmd)) {   # NB: unfortunately, this code won't catch many errors because of the pipe
        $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;

149
    my $dest_dbc    = $self->param('dest_dbc');
150

151 152 153
    my $mode        = $self->param('mode');
    my $table       = $self->param('table');
    my $where       = $self->param('where');
154

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

    if($mode eq 'overwrite') {
158
        my $dest_after      = $self->get_row_count($dest_dbc,  $table, $where);
159 160

        if($src_before == $dest_after) {
161
            $self->warning("Successfully copied $src_before '$table' rows");
162 163 164 165 166
        } else {
            die "Could not copy '$table' rows: $src_before rows from source copied into $dest_after rows in target\n";
        }
    } else {

167
        my $dest_row_increase = $self->get_row_count($dest_dbc, $table) - $self->param('dest_before_all');
168 169

        if($mode eq 'topup') {
Matthieu Muffato's avatar
Matthieu Muffato committed
170
            if($src_before <= $dest_row_increase) {
171
                $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)");
172 173 174 175
            } 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') {
176
            $self->warning("Cannot check success/failure in this mode, but the number of '$table' rows in target increased by $dest_row_increase");
177 178 179 180 181 182 183
        }
    }
}

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

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

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

188
    my $sth = $dbc->prepare($sql);
189 190 191 192 193 194 195
    $sth->execute();
    my ($row_count) = $sth->fetchrow_array();
    $sth->finish;

    return $row_count;
}

196 197
sub mysql_conn_from_dbc {
    my ($self, $dbc) = @_;
198

199
    return '--host='.$dbc->host.' --port='.$dbc->port." --user='".$dbc->username."' --password='".$dbc->password."' ".$dbc->dbname;
200 201 202 203
}

1;