db_cmd.pl 7.28 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
#!/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 {
    $ENV{'EHIVE_ROOT_DIR'} = File::Basename::dirname( File::Basename::dirname( Cwd::realpath($0) ) );
    unshift @INC, $ENV{'EHIVE_ROOT_DIR'}.'/modules';
}

use Getopt::Long;
use Bio::EnsEMBL::Registry;
use Bio::EnsEMBL::Hive::Utils::URL;
use Bio::EnsEMBL::Hive::Utils ('script_usage');


sub main {
21
    my ($reg_conf, $reg_type, $reg_alias, $url, $sqlcmd, $extra, $to_params, $verbose, $help);
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 37 38
            'help!'             => \$help,
    );

39
    my $dbc_hash;
40 41 42

    if($help) {
        script_usage(0);
43 44 45

    } elsif($reg_alias) {
        script_usage(1) if $url;
46 47
        Bio::EnsEMBL::Registry->load_all($reg_conf);

48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
        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();
70 71 72 73 74 75 76 77 78 79 80 81

        $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'";
82 83 84 85
    } else {
        script_usage(1);
    }

86
    my $cmd = dbc_hash_to_cmd( $dbc_hash, $sqlcmd, $extra, $to_params );
87

88 89 90 91
    if($to_params) {
        print "$cmd\n";
    } else {
        warn "\nRunning command:\t$cmd\n\n" if($verbose);
92

93 94
        exec($cmd);
    }
95 96 97
}

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

100 101
    my $driver = $dbc_hash->{'driver'} || 'mysql';

102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
    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) {
                $sqlcmd = "$1 $dbname";
                $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) {
                $sqlcmd = "$1 $dbname";
                $dbc_hash->{dbname} = '';
            }
        }
    }

    my $cmd;

126
    if($driver eq 'mysql') {
127

128 129 130
        $cmd = ($to_params ? '' : 'mysql ')
              ."--host=$dbc_hash->{host} "
              .(defined($dbc_hash->{port}) ? "--port=$dbc_hash->{port} " : '')
131
              ."--user=$dbc_hash->{user} --password='$dbc_hash->{pass}' "
132
              .(defined($extra) ? "$extra " : '')
Matthieu Muffato's avatar
Matthieu Muffato committed
133
              .($dbc_hash->{dbname} || '')
134
              .(defined($sqlcmd) ? " -e '$sqlcmd'" : '');
135
    } elsif($driver eq 'pgsql') {
136

137 138 139 140 141 142
        $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
143
              .($dbc_hash->{dbname} || '');
144
    } elsif($driver eq 'sqlite') {
145

146
        die "sqlite requires a database (file) name\n" unless $dbc_hash->{dbname};
147 148 149 150
        $cmd = "sqlite3 "
              .(defined($extra) ? "$extra " : '')
              .$dbc_hash->{dbname}
              .(defined($sqlcmd) ? " '$sqlcmd'" : '');
151
    }
152 153

    return $cmd;
154 155
}

156

157 158 159 160 161 162 163 164
main();

__DATA__

=pod

=head1 NAME

165
    db_cmd.pl
166 167 168

=head1 SYNOPSIS

169
    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 ]
170 171 172

=head1 DESCRIPTION

173 174
    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.
175 176 177

=head1 USAGE EXAMPLES

178 179 180
    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'
181
    eval mysqldump -t `db_cmd.pl -url "mysql://ensadmin:${ENSADMIN_PSW}@localhost:3306/lg4_long_mult" -to_params` worker
182

183
    db_cmd.pl -reg_conf ${ENSEMBL_CVS_ROOT_DIR}/ensembl-compara/scripts/pipeline/production_reg_conf.pl -reg_alias compara_master
184 185
    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'
186

187 188
=head1 LICENSE

189
    Copyright [1999-2014] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
190 191 192 193 194 195 196 197 198 199

    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.

200 201
=head1 CONTACT

202
    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
203 204 205

=cut