SqlCmd.pm 5.46 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 34
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
    Copyright [2016] EMBL-European Bioinformatics Institute
35

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

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

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

=head1 CONTACT

47
    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
48 49 50 51 52 53 54 55

=cut


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

use strict;

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

58

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

61
    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
62 63 64 65 66
                  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 {
67 68 69
    return 0;
}

70

Leo Gordon's avatar
Leo Gordon committed
71 72 73 74 75 76
=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')).

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

    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

85 86 87 88 89 90 91 92
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)
93
    or die "Could not find the command defined in param('sql') or input_id()";
94

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

100

Leo Gordon's avatar
Leo Gordon committed
101 102 103 104 105 106 107 108
=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

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

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

115 116 117
    my %output_id;

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

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

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

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

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

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

136

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

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

=cut

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

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

1;
Leo Gordon's avatar
Leo Gordon committed
151