db_cmd.pl 7.81 KB
Newer Older
1 2 3 4 5 6 7 8 9
#!/usr/bin/env perl

use strict;
use warnings;

    # Finding out own path in order to reference own components (including own modules):
use Cwd            ();
use File::Basename ();
BEGIN {
10
    $ENV{'EHIVE_ROOT_DIR'} ||= File::Basename::dirname( File::Basename::dirname( Cwd::realpath($0) ) );
11 12 13 14 15 16
    unshift @INC, $ENV{'EHIVE_ROOT_DIR'}.'/modules';
}

use Getopt::Long;
use Bio::EnsEMBL::Registry;
use Bio::EnsEMBL::Hive::Utils::URL;
17
use Bio::EnsEMBL::Hive::Utils ('script_usage', 'report_versions');
18 19 20


sub main {
21
    my ($reg_conf, $reg_type, $reg_alias, $url, $sqlcmd, $extra, $to_params, $verbose, $help, $report_versions);
22 23 24 25 26 27

    GetOptions(
                # connect to the database:
            'reg_conf=s'        => \$reg_conf,
            'reg_type=s'        => \$reg_type,
            'reg_alias=s'       => \$reg_alias,
28

29 30
            'url=s'             => \$url,

31
            'sqlcmd=s'          => \$sqlcmd,
32
            'extra=s'           => \$extra,
33
            'to_params!'        => \$to_params,
34 35

            'verbose!'          => \$verbose,
36
            'help!'             => \$help,
37
            'v|versions!'       => \$report_versions,
38 39
    );

40
    my $dbc_hash;
41 42

    if($help) {
43

44
        script_usage(0);
45

46 47 48 49 50
    } elsif($report_versions) {

        report_versions();
        exit(0);

51 52
    } elsif($reg_alias) {
        script_usage(1) if $url;
53 54
        Bio::EnsEMBL::Registry->load_all($reg_conf);

55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
        my $species = Bio::EnsEMBL::Registry->get_alias($reg_alias)
            || die "Could not solve the alias '$reg_alias'".($reg_conf ? " via the registry file '$reg_conf'" : "");

        my $dba;
        if ($reg_type) {
            $dba = Bio::EnsEMBL::Registry->get_DBAdaptor($species, $reg_type)
                || die "Could not find any database for '$species' (alias: '$reg_alias') with the type '$reg_type'".($reg_conf ? " via the registry file '$reg_conf'" : "");

        } else {

            my $dbas = Bio::EnsEMBL::Registry->get_all_DBAdaptors(-species => $species);
            if (scalar(@$dbas) == 0) {
                # I think this case cannot happen: if there are no databases, the alias does not exist and get_alias() should have failed
                die "Could not find any database for '$species' (alias: '$reg_alias')".($reg_conf ? " via the registry file '$reg_conf'" : "");

            } elsif (scalar(@$dbas) >= 2) {
                die "There are several databases for '$species' (alias: '$reg_alias'). Please set -reg_type to one of: ".join(", ", map {$_->group} @$dbas);
            };
            $dba = $dbas->[0];
        }

        my $dbc = $dba->dbc();
77 78 79 80 81 82 83 84 85 86 87 88

        $dbc_hash = {
            'driver'    => $dbc->driver,
            'host'      => $dbc->host,
            'port'      => $dbc->port,
            'user'      => $dbc->username,
            'pass'      => $dbc->password,
            'dbname'    => $dbc->dbname,
        };
    } elsif($url) {
        $dbc_hash = Bio::EnsEMBL::Hive::Utils::URL::parse( $url )
            || die "Could not parse URL '$url'";
89 90 91 92
    } else {
        script_usage(1);
    }

93
    my $cmd = dbc_hash_to_cmd( $dbc_hash, $sqlcmd, $extra, $to_params );
94

95 96 97 98
    if($to_params) {
        print "$cmd\n";
    } else {
        warn "\nRunning command:\t$cmd\n\n" if($verbose);
99

100 101
        exec($cmd);
    }
102 103 104
}

sub dbc_hash_to_cmd {
105
    my ($dbc_hash, $sqlcmd, $extra, $to_params) = @_;
106

107 108
    my $driver = $dbc_hash->{'driver'} || 'mysql';

109 110 111 112 113 114 115
    if($sqlcmd) {
        if($sqlcmd =~ /(DROP\s+DATABASE(?:\s+IF\s+EXISTS)?\s*?)(?:\s+(\w+))?/i) {
            my $dbname = $2 || $dbc_hash->{dbname};

            if($driver eq 'sqlite') {
                return "rm -f $dbname";
            } elsif(!$2) {
116 117 118 119 120
                if ($driver eq 'mysql') {
                    $sqlcmd = "$1 \`$dbname\`";
                } else {
                    $sqlcmd = "$1 $dbname";
                }
121 122 123 124 125 126 127 128
                $dbc_hash->{dbname} = '';
            }
        } elsif($sqlcmd =~ /(CREATE\s+DATABASE\s*?)(?:\s+(\w+))?/i ) {
            my $dbname = $2 || $dbc_hash->{dbname};

            if($driver eq 'sqlite') {
                return "touch $dbname";
            } elsif(!$2) {
129 130 131 132 133
                if ($driver eq 'mysql') {
                    $sqlcmd = "$1 \`$dbname\`";
                } else {
                    $sqlcmd = "$1 $dbname";
                }
134 135 136 137 138 139 140
                $dbc_hash->{dbname} = '';
            }
        }
    }

    my $cmd;

141
    if($driver eq 'mysql') {
142

143
        $dbc_hash->{pass} //= '';
144 145 146
        $cmd = ($to_params ? '' : 'mysql ')
              ."--host=$dbc_hash->{host} "
              .(defined($dbc_hash->{port}) ? "--port=$dbc_hash->{port} " : '')
147
              ."--user=$dbc_hash->{user} --password='$dbc_hash->{pass}' "
148
              .(defined($extra) ? "$extra " : '')
Matthieu Muffato's avatar
Matthieu Muffato committed
149
              .($dbc_hash->{dbname} || '')
150
              .(defined($sqlcmd) ? " -e '$sqlcmd'" : '');
151
    } elsif($driver eq 'pgsql') {
152

153 154 155 156 157 158
        $cmd = ($to_params ? '' : "env PGPASSWORD='$dbc_hash->{pass}' psql ")
              ."--host=$dbc_hash->{host} "
              .(defined($dbc_hash->{port}) ? "--port=$dbc_hash->{port} " : '')
              ."--username=$dbc_hash->{user} "
              .(defined($sqlcmd) ? "--command='$sqlcmd' " : '')
              .(defined($extra) ? "$extra " : '')
Matthieu Muffato's avatar
Matthieu Muffato committed
159
              .($dbc_hash->{dbname} || '');
160
    } elsif($driver eq 'sqlite') {
161

162
        die "sqlite requires a database (file) name\n" unless $dbc_hash->{dbname};
163 164 165 166
        $cmd = "sqlite3 "
              .(defined($extra) ? "$extra " : '')
              .$dbc_hash->{dbname}
              .(defined($sqlcmd) ? " '$sqlcmd'" : '');
167
    }
168 169

    return $cmd;
170 171
}

172

173 174 175 176 177 178 179 180
main();

__DATA__

=pod

=head1 NAME

181
    db_cmd.pl
182 183 184

=head1 SYNOPSIS

185
    db_cmd.pl {-url <url> | [-reg_conf <reg_conf>] -reg_alias <reg_alias> [-reg_type <reg_type>] } [ -sql <sql_command> ] [ -extra <extra_params> ] [ -to_params | -verbose ]
186 187 188

=head1 DESCRIPTION

189 190
    db_cmd.pl is a generic script that connects you interactively to your database using either URL or Registry and optionally runs an SQL command.
    -url is exclusive to -reg_alias. -reg_type is only needed if several databases map to that alias / species.
191 192 193

=head1 USAGE EXAMPLES

194 195 196
    db_cmd.pl -url "mysql://ensadmin:${ENSADMIN_PSW}@localhost:3306/" -sql 'CREATE DATABASE lg4_long_mult'
    db_cmd.pl -url "mysql://ensadmin:${ENSADMIN_PSW}@localhost:3306/lg4_long_mult"
    db_cmd.pl -url "mysql://ensadmin:${ENSADMIN_PSW}@localhost:3306/lg4_long_mult" -sql 'SELECT * FROM analysis_base' -extra='--html'
197
    eval mysqldump -t `db_cmd.pl -url "mysql://ensadmin:${ENSADMIN_PSW}@localhost:3306/lg4_long_mult" -to_params` worker
198

199
    db_cmd.pl -reg_conf ${ENSEMBL_CVS_ROOT_DIR}/ensembl-compara/scripts/pipeline/production_reg_conf.pl -reg_alias compara_master
200 201
    db_cmd.pl -reg_conf ${ENSEMBL_CVS_ROOT_DIR}/ensembl-compara/scripts/pipeline/production_reg_conf.pl -reg_alias mus_musculus   -reg_type core
    db_cmd.pl -reg_conf ${ENSEMBL_CVS_ROOT_DIR}/ensembl-compara/scripts/pipeline/production_reg_conf.pl -reg_alias squirrel       -reg_type core -sql 'SELECT * FROM coord_system'
202

203 204
=head1 LICENSE

205
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
Matthieu Muffato's avatar
Matthieu Muffato committed
206
    Copyright [2016-2018] EMBL-European Bioinformatics Institute
207 208 209 210 211 212 213 214 215 216

    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.

217 218
=head1 CONTACT

219
    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
220 221 222

=cut