SqlCmd.pm 5.32 KB
Newer Older
1 2 3 4
=pod 

=head1 NAME

5
    Bio::EnsEMBL::Hive::RunnableDB::SqlCmd
6

Leo Gordon's avatar
Leo Gordon committed
7 8
=head1 SYNOPSIS

9 10
    standaloneJob.pl Bio::EnsEMBL::Hive::RunnableDB::SqlCmd --db_conn mysql://ensadmin:${ENSADMIN_PSW}@127.0.0.1:2912/lg4_compara_families_64 \
                        --sql "INSERT INTO meta(meta_key,meta_value) VALUES ('Hello', 'world')"
Leo Gordon's avatar
Leo Gordon committed
11

12 13
    standaloneJob.pl Bio::EnsEMBL::Hive::RunnableDB::SqlCmd --db_conn mysql://ensadmin:${ENSADMIN_PSW}@127.0.0.1:2913/lg4_compara_homology_merged_64 \
                        --sql "[ 'CREATE TABLE meta_foo LIKE meta', 'INSERT INTO meta_foo SELECT * FROM meta' ]"
Leo Gordon's avatar
Leo Gordon committed
14

15 16
=head1 DESCRIPTION

17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
    This RunnableDB module acts as a wrapper for an SQL command
    run against either the current hive database (default) or against one specified by 'db_conn' parameter
    (--db_conn becomes obligatory in standalone mode, because there is no hive_db).
    If you behave you may also use parameter substitution.

    The SQL command(s) can be given using two different syntaxes:

    1) Sql command is stored in the input_id() or parameters() as the value corresponding to the 'sql' key.
        THIS IS THE RECOMMENDED WAY as it allows to pass in other parameters and use the parameter substitution mechanism in its full glory.

    2) Sql command is stored in the 'input_id' field of the job table.
        (only works with sql commands shorter than 255 bytes).
        This is a legacy syntax. Most people tend to use it not realizing there are other possiblities.

=head1 LICENSE

33
    Copyright [1999-2014] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
34

35 36
    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
37

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

40 41 42
    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.
43 44 45

=head1 CONTACT

46
    Please contact ehive-users@ebi.ac.uk mailing list with questions/suggestions.
47 48 49 50 51 52 53 54

=cut


package Bio::EnsEMBL::Hive::RunnableDB::SqlCmd;

use strict;

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

57

Leo Gordon's avatar
Leo Gordon committed
58 59
=head2 strict_hash_format

60
    Description : Implements strict_hash_format() interface method of Bio::EnsEMBL::Hive::Process that is used to set the strictness level of the parameters' parser.
Leo Gordon's avatar
Leo Gordon committed
61 62 63 64 65
                  Here we return 0 in order to indicate that neither input_id() nor parameters() is required to contain a hash.

=cut

sub strict_hash_format {
66 67 68
    return 0;
}

69

Leo Gordon's avatar
Leo Gordon committed
70 71 72 73 74 75
=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 deals with finding the sql command(s), doing parameter substitution, storing the result in a predefined place
                  and optionally connecting to another database (see param('db_conn')).

76
    param('sql'): The recommended way of passing in the sql command(s).
Leo Gordon's avatar
Leo Gordon committed
77 78 79 80 81 82 83

    param('db_conn'): An optional hash to pass in connection parameters to the database upon which the sql command(s) will have to be run.

    param('*'):   Any other parameters can be freely used for parameter substitution.

=cut

84 85 86 87 88 89 90 91
sub fetch_input {
    my $self = shift;

        # First, FIND the sql command
        #
    my $sql = ($self->input_id()!~/^\{.*\}$/)
            ? $self->input_id()                 # assume the sql command is given in input_id
            : $self->param('sql')               # or defined as a hash value (in input_id or parameters)
92
    or die "Could not find the command defined in param('sql') or input_id()";
93

94
        #   Store the sql command array:
95
        #
96
    $self->param('sqls', (ref($sql) eq 'ARRAY') ? $sql : [$sql] );  
97 98
}

99

Leo Gordon's avatar
Leo Gordon committed
100 101 102 103 104 105 106 107
=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 it actually runs the sql command(s).  If a list of commands is given, they are run in succession within the same session
                  (so you can create a temporary tables and use it in another command within the same sql command list).

=cut

108 109 110
sub run {
    my $self = shift;

111
    my $sqls = $self->param('sqls');
112
    my $data_dbc  = $self->data_dbc();
113

114 115 116
    my %output_id;

    my $counter = 0;
117
    foreach my $sql (@$sqls) {
118

119 120 121 122
         if($self->debug()) {
             warn qq{sql = "$sql"\n};
         }

123
        $data_dbc->do( $sql ) or die "Could not run '$sql': ".$data_dbc->db_handle->errstr;
124 125

        my $insert_id_name  = '_insert_id_'.$counter++;
126
        my $insert_id_value = $data_dbc->db_handle->last_insert_id(undef, undef, undef, undef);
127

128 129
        $output_id{$insert_id_name} = $insert_id_value;
        $self->param($insert_id_name, $insert_id_value); # for templates
130
    }
131 132

    $self->param('output_id', \%output_id);
133 134
}

135

Leo Gordon's avatar
Leo Gordon committed
136
=head2 write_output
137

Leo Gordon's avatar
Leo Gordon committed
138
    Description : Implements write_output() interface method of Bio::EnsEMBL::Hive::Process that is used to deal with job's output after the execution.
139
                  Here we only flow out the insert_ids.
Leo Gordon's avatar
Leo Gordon committed
140 141 142 143

=cut

sub write_output {
144 145 146
    my $self = shift;

    $self->dataflow_output_id( $self->param('output_id'), 2);
147 148 149
}

1;
Leo Gordon's avatar
Leo Gordon committed
150