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

use strict;
use warnings;
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

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

44
45
46
Please note that where an argument expects a value, this is true for all
alternative argument styles.

47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
=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
70
<dev@ensembl.org>
71
72
73

=cut

74
75
76
# 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

77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
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(
102
103
104
105
106
							  'pattern|dbpattern=s',
							  'schema|dbschema=s',
 							  'bindir=s',
							  'schema_type=s',
							 );
107
108
109
110
111
112
my @params = map { $_ unless ($_ =~ /dbname/) } $support->get_common_params;
$support->allowed_params(
  @params,
  'pattern',
  'schema',
  'bindir',
113
  'schema_type'
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
);

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',
134
  'schema_type'
135
136
);

137
138
139
140
141
142
143
144
145
146
147
148
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');
}

149
150
151
152
153
# connect to database
my $dbh = $support->get_dbconnection;

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

opendir(DIR, $patchdir) or
159
  $support->log_error("Can't opendir $patchdir: $!");
160
161

while (my $file = readdir(DIR)) {
162
  if ($file =~ /^patch_\d+_${schema}.*\.sql$/) {
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
    $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) {
178
  $support->log_stamped("$dbname\n");
179
  
180
  my $failure = 0;
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
  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) {
197
198
199
      # if a patch has failed do not continue with other patches as there may be dependencies.
      if(!$failure){
	$support->log("$patch... ", 1);
200
      
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
	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;
	  }
	  
	}
225
226
227
228
229
230
231
232
233
234
235
236
237
      }
    }
    
  } else {
    $support->log("Skipping on user's request.\n", 1);
  }

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

# finish logfile
$support->finish_log;