cleanup_tmp_tables.pl 5.31 KB
Newer Older
1
#!/usr/bin/env perl
Magali Ruffier's avatar
Magali Ruffier committed
2
# Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
Tiago Grego's avatar
Tiago Grego committed
3
# Copyright [2016-2019] EMBL-European Bioinformatics Institute
4 5 6 7 8 9 10 11 12 13 14 15 16
# 
# 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.

17 18 19 20 21 22 23

=head1 NAME

cleanup_tmp_tables.pl - delete temporary and backup tables from a database

=head1 SYNOPSIS

24 25 26 27
  ./cleanup_tmp_tables.pl [arguments]
  
  ./cleanup_tmp_tables.pl --nolog --host localhost --port 3306 --user user --dbname DB --dry_run
  
Andy Yates's avatar
Andy Yates committed
28 29
  ./cleanup_tmp_tables.pl --nolog --host localhost --port 3306 --user user --dbname '%mydbs%' --dry_run
  
30
  ./cleanup_tmp_tables.pl --nolog --host localhost --port 3306 --user user --dbname DB --interactive 0
31 32 33

Required arguments:

Andy Yates's avatar
Andy Yates committed
34
  --dbname, db_name=NAME              database name NAME (can be a pattern)
35 36 37 38 39 40 41
  --host, --dbhost, --db_host=HOST    database host HOST
  --port, --dbport, --db_port=PORT    database port PORT
  --user, --dbuser, --db_user=USER    database username USER
  --pass, --dbpass, --db_pass=PASS    database passwort PASS

Optional arguments:

42 43
  --mart                              Indicates we wish to search for mart
                                      temporary tables which are normally
Andy Yates's avatar
Andy Yates committed
44 45 46
                                      prefixed with MTMP_. 
                                      ONLY RUN IF YOU ARE A MEMBER OF 
                                      THE PRODUCTION TEAM
47

48 49 50 51 52 53 54 55 56 57 58 59 60 61
  --conffile, --conf=FILE             read parameters from FILE
                                      (default: conf/Conversion.ini)

  --logfile, --log=FILE               log to FILE (default: *STDOUT)
  --logpath=PATH                      write logfile to PATH (default: .)
  --logappend, --log_append           append to logfile (default: truncate)

  -v, --verbose=0|1                   verbose logging (default: false)
  -i, --interactive=0|1               run script interactively (default: true)
  -n, --dry_run, --dry=0|1            don't write results to database
  -h, --help, -?                      print help (this message)

=head1 DESCRIPTION

62 63 64 65 66 67 68 69 70 71 72 73 74
A script which looks for any table which we believe could be a temporary
table. This means any table which contains

=over 8

=item bak

=item backup

=item MTMP_ (only used when --mart is specified)

=back

Andy Yates's avatar
Andy Yates committed
75
You can run this over multiple DBs but caution is advised
76 77 78 79


=head1 AUTHOR

Andy Yates's avatar
Andy Yates committed
80
Ensembl core API team
81 82 83 84

=head1 CONTACT

Please post comments/questions to the Ensembl development list
Magali Ruffier's avatar
Magali Ruffier committed
85
<http://lists.ensembl.org/mailman/listinfo/dev>
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111

=cut

use strict;
use warnings;
no warnings 'uninitialized';

use FindBin qw($Bin);
use vars qw($SERVERROOT);

BEGIN {
    $SERVERROOT = "$Bin/../..";
    unshift(@INC, "$SERVERROOT/ensembl/modules");
    unshift(@INC, "$SERVERROOT/bioperl-live");
}

use Getopt::Long;
use Pod::Usage;
use Bio::EnsEMBL::Utils::ConversionSupport;

$| = 1;

my $support = new Bio::EnsEMBL::Utils::ConversionSupport($SERVERROOT);

# parse options
$support->parse_common_options(@_);
112
$support->parse_extra_options(qw/mart!/);
113
$support->allowed_params(
114
  $support->get_common_params, 'mart'
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129
);

if ($support->param('help') or $support->error) {
    warn $support->error if $support->error;
    pod2usage(1);
}

# ask user to confirm parameters to proceed
$support->confirm_params;

# get log filehandle and print heading and parameters to logfile
$support->init_log;

$support->check_required_params;

Andy Yates's avatar
Andy Yates committed
130 131 132
my @databases;

# connect to database
133 134 135 136 137
my $dbh;
my $original_dbname = $support->param('dbname'); 
if($original_dbname =~ /%/) {
  $support->param('dbname', q{});
  $dbh = $support->get_dbconnection('');
Andy Yates's avatar
Andy Yates committed
138
  my $ref = $dbh->selectall_arrayref('show databases like ?', {}, $original_dbname);
Andy Yates's avatar
Andy Yates committed
139 140 141
  push(@databases, map {$_->[0]} @{$ref})
}
else {
142 143
  $dbh = $support->get_dbconnection('');
  push(@databases, $original_dbname);
Andy Yates's avatar
Andy Yates committed
144
}
145

146
my @patterns = map { '%\\_'.$_.'%' } qw/bak backup/;
147 148
if($support->param('mart')) {
  if($support->user_proceed('--mart was specified on the command line. Do not run this during a mart build. Do you wish to continue?')) {
149
    push(@patterns, 'MTMP\\_%');
150 151
  }
}
152

Andy Yates's avatar
Andy Yates committed
153
foreach my $db (@databases) {
154
  my %tables;
Andy Yates's avatar
Andy Yates committed
155
  $support->log('Switching to '.$db."\n");
Andy Yates's avatar
Andy Yates committed
156 157 158
  $dbh->do('use '.$db);
  foreach my $pattern (@patterns) {
    my $ref = $dbh->selectall_arrayref('show tables like ?', {}, $pattern);
159
    $tables{$_->[0]} = 1 for @{$ref};
160
  }
Andy Yates's avatar
Andy Yates committed
161
  
162
  my @tables = sort keys %tables;
Andy Yates's avatar
Andy Yates committed
163 164 165
  
  if ($support->param('dry_run')) {
    # for a dry run, only show which databases would be deleted
166 167 168 169 170
    if(scalar(@tables) > 0) {
      $support->log("Temporary and backup tables found:\n");
      foreach my $table (@tables) {
        $support->log("$table\n", 1);
      }
Andy Yates's avatar
Andy Yates committed
171 172 173 174 175 176 177 178 179 180
    }
  
  } else {
    # delete tables
    foreach my $table (@tables) {
      if ($support->user_proceed("Drop table $table?")) {
        $support->log("Dropping table $table...\n");
        $dbh->do("DROP TABLE $table");
        $support->log("Done.\n");
      }
181 182 183 184 185 186
    }
  }
}
# finish logfile
$support->finish_log;