db_cmd.pl 6.5 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
131
132
        $cmd = ($to_params ? '' : 'mysql ')
              ."--host=$dbc_hash->{host} "
              .(defined($dbc_hash->{port}) ? "--port=$dbc_hash->{port} " : '')
              ."--user=$dbc_hash->{user} --pass='$dbc_hash->{pass}' "
              .(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

Matthieu Muffato's avatar
Matthieu Muffato committed
146
        die "sqlite requires a database (file) name\n";
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
189
190
191
192

=head1 CONTACT

    Please contact ehive-users@ebi.ac.uk mailing list with questions/suggestions.

=cut