schema_patch.pl 6.42 KB
Newer Older
1
#!/usr/local/ensembl/bin/perl -w
2

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
=head1 NAME

schema_patch.pl - automagically apply schema patches to Ensembl dbs

=head1 SYNOPSIS

schema_patch.pl [arguments]

Required arguments:

  --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
  
  --pattern, --dbpattern=PATTERN      patch databases where name matches PATTERN
19 20
                                      Note that this is a database pattern of
                                      the form %core_41% rather than a regexp
21
  --schema, --dbschema=NUM            patch to schema version NUM
22
  --schema_type                       Schema type to patch e.g. core|variation|funcgen
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

Optional arguments:

  --conffile, --conf=FILE             read parameters from FILE
                                      (default: conf/Conversion.ini)

  --bindir=DIR                        mysql binary directory (default:
                                      /usr/local/ensembl/bin)

  --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)
38
  -n, --dry, --dry_run=0|1            don't write results to database
39 40
  -h, --help, -?                      print help (this message)

41 42 43
Please note that where an argument expects a value, this is true for all
alternative argument styles.

44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
=head1 DESCRIPTION

This is a script to facilitate patching databases to the next schema version.
It will connect to a database server and apply schema patches to all databases
where the name matches a pattern. The pattern is a string that can be used in an
'IN' clause in SQL (e.g. "%_core_%"; if you want to patch only a single
database, use a pattern without % expansion, e.g. "homo_sapiens_core_38_36").

If you only want to check which patches need to be applied, use --dry_run=1
(best done in combination with --interactive=0).

=head1 LICENCE

This code is distributed under an Apache style licence:
Please see http://www.ensembl.org/code_licence.html for details

=head1 AUTHOR

Patrick Meidl <meidl@ebi.ac.uk>, Ensembl core API team

=head1 CONTACT

Please post comments/questions to the Ensembl development list
67
<dev@ensembl.org>
68 69 70

=cut

71 72 73
# Should really add explicit --schema_type param to avoid applying core patches to non-core DBs
# Could also validate this against meta schema.type = core|funcgen|variation

74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
use strict;
use warnings;
no warnings 'uninitialized';

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

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

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

$| = 1;

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

# parse options
$support->parse_common_options(@_);
$support->param('dbname', undef);
$support->parse_extra_options(
99 100 101 102 103
							  'pattern|dbpattern=s',
							  'schema|dbschema=s',
 							  'bindir=s',
							  'schema_type=s',
							 );
104 105 106 107 108 109
my @params = map { $_ unless ($_ =~ /dbname/) } $support->get_common_params;
$support->allowed_params(
  @params,
  'pattern',
  'schema',
  'bindir',
110
  'schema_type'
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130
);

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

unless ($support->param('bindir')) {
  $support->param('bindir', '/usr/local/ensembl/bin');
}

# 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(
  'pattern',
  'schema',
131
  'schema_type'
132 133
);

134 135 136 137 138 139 140 141 142 143 144 145
my $schema_type = $support->param('schema_type');
my %patch_dirs = (
				  'core' => "$SERVERROOT/ensembl/sql",
				  'funcgen' => "$SERVERROOT/ensembl-functgenomics/sql",
				  'variation' => "$SERVERROOT/ensembl-variation/sql",
				 );

#check schema_type is valid
if(! (defined $schema_type && exists $patch_dirs{$schema_type})){
  $support->log_error('You must specify a valid --schema_type parameter e.g. core|variation|funcgen');
}

146 147 148 149 150
# connect to database
my $dbh = $support->get_dbconnection;

# read patches from file
$support->log("Reading patches from file...\n");
151
my $patchdir = $patch_dirs{$schema_type};
152 153 154 155
my $schema = $support->param('schema');
my @patches;

opendir(DIR, $patchdir) or
156
  $support->log_error("Can't opendir $patchdir: $!");
157 158

while (my $file = readdir(DIR)) {
159
  if ($file =~ /^patch_\d+_${schema}.*\.sql$/) {
160 161 162 163 164 165 166 167 168 169 170 171 172 173 174
    $support->log("$file\n", 1);
    push @patches, $file;
  }
}

$support->log("Done.\n\n");

# get all database names that match pattern
my ($sth, $sql);
$sql = "SHOW DATABASES LIKE '".$support->param('pattern')."'";
$sth = $dbh->prepare($sql);
$sth->execute;

# loop over databases
while (my ($dbname) = $sth->fetchrow_array) {
175
  $support->log_stamped("$dbname\n");
176
  
177
  my $failure = 0;
178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193
  if ($support->user_proceed("\nPatch $dbname?")) {

    # check which patches have already been applied
    $sql = qq(SELECT meta_value FROM $dbname.meta WHERE meta_key = 'patch');
    my $sth1 = $dbh->prepare($sql);
    $sth1->execute;
    
    my %applied;
  
    while (my ($val) = $sth1->fetchrow_array) {
      my ($file) = split(/\|/, $val);
      $applied{$file} = 1;
    }

    # apply the missing ones
    foreach my $patch (sort @patches) {
194 195 196
      # if a patch has failed do not continue with other patches as there may be dependencies.
      if(!$failure){
	$support->log("$patch... ", 1);
197
      
198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221
	if ($applied{$patch}) {
	  $support->log("already applied.\n")
	    
	} elsif ($support->param('dry_run')) {
	  $support->log("needs applying.\n")
	    
	} else {
	  $support->log("applying... ");
	  
	  my $cmd = $support->param('bindir')."/mysql".
	    " -h ".$support->param('host').
	      " -P ".$support->param('port').
		" -u ".$support->param('user').
		  " -p".$support->param('pass').
		    " $dbname < $patchdir/$patch";
	  
	  if (system($cmd) == 0) {
	    $support->log("done.\n");
	  } else {
	    $support->log_warning("Error applying patch. Please check patch file.\n", 1);
	    $failure = 1;
	  }
	  
	}
222 223 224 225 226 227 228 229 230 231 232 233 234
      }
    }
    
  } else {
    $support->log("Skipping on user's request.\n", 1);
  }

  $support->log("Done.\n\n");
}

# finish logfile
$support->finish_log;