MySQLTransfer.pm 7.1 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 22
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
    Copyright [2016] 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 57 58 59 60
sub param_defaults {
    return {
        'src_db_conn'   => '',
        'dest_db_conn'  => '',
        'mode'          => 'overwrite',
        'table'         => '',
        'where'         => undef,
        'filter_cmd'    => undef,
    };
}


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

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

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

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

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

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

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

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

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

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

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

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

    my $cmd = 'mysqldump '
                . { 'overwrite' => '', 'topup' => '--no-create-info ', 'insertignore' => '--no-create-info --insert-ignore ' }->{$mode}
126 127
                . $self->mysql_conn_from_dbc($src_dbc)
                . " $table "
128
                . (defined($where) ? "--where '$where' " : '')
129 130
                . '| '
                . ($filter_cmd ? "$filter_cmd | " : '')
131 132
                . 'mysql '
                . $self->mysql_conn_from_dbc($dest_dbc);
133

134
    if(my $return_value = system(bash => (-o => 'pipefail', -c => $cmd))) {
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149
        $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;

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

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

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

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

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

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

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

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

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

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

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

    return $row_count;
}

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

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

1;