1 | #!/usr/bin/env perl |
---|
2 | # |
---|
3 | # Update the DAViCal database by repeatedly applying patches to it |
---|
4 | # in the correct order. |
---|
5 | # |
---|
6 | |
---|
7 | use warnings; |
---|
8 | use strict; |
---|
9 | |
---|
10 | use DBI; |
---|
11 | use POSIX qw(floor); |
---|
12 | use Getopt::Long qw(:config permute); # allow mixed args. |
---|
13 | |
---|
14 | # Options variables |
---|
15 | my $debug = 0; |
---|
16 | my $dbname = "davical"; |
---|
17 | my $dbport = 5432; |
---|
18 | my $dbuser = "davical_dba"; |
---|
19 | my $dbpass = ""; |
---|
20 | my $dbhost = ""; |
---|
21 | my $appuser = "davical_app"; |
---|
22 | my $helpmeplease = 0; |
---|
23 | my $apply_patches = 1; |
---|
24 | my $revoke_list = ""; |
---|
25 | my $force_owner = ""; |
---|
26 | my $config_file = "config/administration.yml"; |
---|
27 | |
---|
28 | my $dbadir = $0; |
---|
29 | $dbadir =~ s#/[^/]*$##; |
---|
30 | my $patchdir = $dbadir . "/patches"; |
---|
31 | |
---|
32 | # |
---|
33 | # We look in a few places for the config file. First relative to |
---|
34 | # where we are, then relative to the code we are running, then we |
---|
35 | # start to look in absolute locations. Then we give up :-) |
---|
36 | if ( ! -f $config_file ) { |
---|
37 | $config_file = $0; |
---|
38 | $config_file =~ s{[^/]+/update-[a-z]+-database}{config/administration.yml}; |
---|
39 | } |
---|
40 | if ( ! -f $config_file ) { |
---|
41 | $config_file = "/etc/davical/administration.yml"; |
---|
42 | } |
---|
43 | if ( -f $config_file ) { |
---|
44 | use YAML qw( LoadFile ); |
---|
45 | |
---|
46 | my ($ycfg) = LoadFile($config_file); |
---|
47 | $dbuser = $ycfg->{'admin_db_user'} if ( defined($ycfg->{'admin_db_user'})); |
---|
48 | $dbpass = $ycfg->{'admin_db_pass'} if ( defined($ycfg->{'admin_db_pass'})); |
---|
49 | $dbhost = $ycfg->{'admin_db_host'} if ( defined($ycfg->{'admin_db_host'})); |
---|
50 | $dbname = $ycfg->{'admin_db_name'} if ( defined($ycfg->{'admin_db_name'})); |
---|
51 | $dbport = $ycfg->{'admin_db_port'} if ( defined($ycfg->{'admin_db_port'})); |
---|
52 | $appuser = $ycfg->{'app_db_user'} if ( defined($ycfg->{'app_db_user'})); |
---|
53 | } |
---|
54 | |
---|
55 | GetOptions ('debug!' => \$debug, |
---|
56 | 'dbname=s' => \$dbname, |
---|
57 | 'dbuser=s' => \$dbuser, |
---|
58 | 'dbpass=s' => \$dbpass, |
---|
59 | 'dbport=s' => \$dbport, |
---|
60 | 'dbhost=s' => \$dbhost, |
---|
61 | 'appuser=s' => \$appuser, |
---|
62 | 'patch!' => \$apply_patches, |
---|
63 | 'owner=s' => \$force_owner, |
---|
64 | 'revoke=s' => \$revoke_list, |
---|
65 | 'help' => \$helpmeplease ); |
---|
66 | |
---|
67 | show_usage() if ( $helpmeplease ); |
---|
68 | |
---|
69 | $revoke_list = ", ". $revoke_list if ( $revoke_list ne "" ); |
---|
70 | |
---|
71 | |
---|
72 | ############################################################ |
---|
73 | # Open database connection. Note that the standard PostgreSQL |
---|
74 | # environment variables will also work with DBD::Pg. |
---|
75 | ############################################################ |
---|
76 | my $dsn = "dbi:Pg:dbname=$dbname"; |
---|
77 | $dsn .= ";host=$dbhost" if ( "$dbhost" ne "" ); |
---|
78 | $dsn .= ";port=$dbport" if ( $dbport != 5432 ); |
---|
79 | |
---|
80 | print "Using database: $dbuser".'%'.$dbpass.'@'.$dsn."\n" if ( $debug ); |
---|
81 | |
---|
82 | my $pg_version = get_postgresql_version(); |
---|
83 | my $current_revision; |
---|
84 | my $last_results = ''; # Will hold the last SQL result from applying a patch |
---|
85 | |
---|
86 | if ( $apply_patches ) { |
---|
87 | $current_revision = get_current_revision(); |
---|
88 | printf( "The database is version %.1lf currently at revision %d.%d.%d.\n", $pg_version, $current_revision->{'schema_major'}, $current_revision->{'schema_minor'}, $current_revision->{'schema_patch'} ); |
---|
89 | |
---|
90 | opendir( PATCHDIR, $patchdir ) or die "Can't open patch directory $patchdir"; |
---|
91 | my @patches = grep { /^([0-9]+)\.([0-9]+)\.([0-9]+)([a-z]?)\.sql$/ } readdir(PATCHDIR); |
---|
92 | closedir(PATCHDIR); |
---|
93 | |
---|
94 | @patches = sort { compare_revisions(revision_hash($a),revision_hash($b), 1); } @patches; |
---|
95 | |
---|
96 | my $applied = 0; |
---|
97 | |
---|
98 | for ( my $i=0; $i <= $#patches; $i++ ) { |
---|
99 | printf( "Looking at patches[%d] (%s)\n", $i, $patches[$i]) if ( $debug ); |
---|
100 | if ( compare_revisions(revision_hash($patches[$i]),$current_revision) > 0 ) { |
---|
101 | print "Applying patch $patches[$i] ... "; |
---|
102 | if ( !apply_patch( $patches[$i] ) ) { |
---|
103 | # Skip to the end unless the next patch is an alternate for the same version. |
---|
104 | if ( defined($patches[$i+1]) && compare_revisions(revision_hash($patches[$i]),revision_hash($patches[$i+1])) == 0 ) { |
---|
105 | print "failed. Attempting next alternative.\n"; |
---|
106 | $applied--; |
---|
107 | } |
---|
108 | else { |
---|
109 | print "failed!\n$last_results ==> No further patches will be attempted!\n"; |
---|
110 | last; |
---|
111 | } |
---|
112 | } |
---|
113 | else { |
---|
114 | print "succeeded.\n"; |
---|
115 | } |
---|
116 | $applied++; |
---|
117 | } |
---|
118 | else { |
---|
119 | print "Patch $patches[$i] has already been applied.\n" if ( $debug ); |
---|
120 | } |
---|
121 | } |
---|
122 | |
---|
123 | if ( $applied ) { |
---|
124 | print "Successfully applied $applied patches.\n"; |
---|
125 | } |
---|
126 | else { |
---|
127 | print "No patches were applied.\n"; |
---|
128 | } |
---|
129 | } |
---|
130 | |
---|
131 | # Ensure the locales data is up to date |
---|
132 | apply_sql_file( $dbadir, "supported_locales.sql" ); |
---|
133 | print "Supported locales updated.\n"; |
---|
134 | |
---|
135 | # update any views |
---|
136 | apply_sql_folder( $dbadir, 'views', "Updated view: " ); |
---|
137 | |
---|
138 | # Ensure the functions are up to date |
---|
139 | apply_sql_file( $dbadir, "caldav_functions.sql" ); |
---|
140 | print "CalDAV functions updated.\n"; |
---|
141 | |
---|
142 | if ( $pg_version >= 8.3 ) { |
---|
143 | apply_sql_file( $dbadir, "rrule_functions.sql" ); |
---|
144 | } |
---|
145 | else { |
---|
146 | apply_sql_file( $dbadir, "rrule_functions-8.1.sql", ); |
---|
147 | } |
---|
148 | print "RRULE functions updated.\n"; |
---|
149 | |
---|
150 | # Ensure the permissions are up to date |
---|
151 | apply_permissions( $dbadir, "appuser_permissions.txt" ); |
---|
152 | print "Database permissions updated.\n"; |
---|
153 | |
---|
154 | # The End! |
---|
155 | exit 0; |
---|
156 | |
---|
157 | |
---|
158 | |
---|
159 | |
---|
160 | ############################################################ |
---|
161 | # Revision Hash - we either have a single parameter, |
---|
162 | # which is of the form "1.2.3" or we have three parameters. |
---|
163 | ############################################################ |
---|
164 | sub revision_hash { |
---|
165 | my $rev = +{ 'schema_major', => 0, 'schema_minor' => 0, 'schema_patch' => 0, 'alternative' => '0' }; |
---|
166 | my $first = shift; |
---|
167 | return $rev unless ( defined($first) ); |
---|
168 | if ( $first =~ /^([0-9]+)\.([0-9]+)\.([0-9]+)([a-z]?)([^0-9]|$)/ ) { |
---|
169 | $rev->{'schema_major'} = $1; |
---|
170 | $rev->{'schema_minor'} = $2; |
---|
171 | $rev->{'schema_patch'} = $3; |
---|
172 | $rev->{'alternative'} = $4; |
---|
173 | } |
---|
174 | else { |
---|
175 | $rev->{'schema_major'} = $first; |
---|
176 | $rev->{'schema_minor'} = shift; |
---|
177 | $rev->{'schema_patch'} = shift; |
---|
178 | $rev->{'alternative'} = '0'; |
---|
179 | } |
---|
180 | return $rev; |
---|
181 | } |
---|
182 | |
---|
183 | |
---|
184 | ############################################################ |
---|
185 | # Compare revisions |
---|
186 | ############################################################ |
---|
187 | sub compare_revisions { |
---|
188 | my $a = shift; |
---|
189 | my $b = shift; |
---|
190 | my $test_alt = shift; |
---|
191 | |
---|
192 | return -1 if ( $a->{'schema_major'} < $b->{'schema_major'} ); |
---|
193 | return 1 if ( $a->{'schema_major'} > $b->{'schema_major'} ); |
---|
194 | |
---|
195 | return -1 if ( $a->{'schema_minor'} < $b->{'schema_minor'} ); |
---|
196 | return 1 if ( $a->{'schema_minor'} > $b->{'schema_minor'} ); |
---|
197 | |
---|
198 | return -1 if ( $a->{'schema_patch'} < $b->{'schema_patch'} ); |
---|
199 | return 1 if ( $a->{'schema_patch'} > $b->{'schema_patch'} ); |
---|
200 | |
---|
201 | if ( defined($test_alt) ) { |
---|
202 | return -1 if ( $a->{'alternative'} lt $b->{'alternative'} ); |
---|
203 | return 1 if ( $a->{'alternative'} gt $b->{'alternative'} ); |
---|
204 | } |
---|
205 | |
---|
206 | return 0; |
---|
207 | |
---|
208 | } |
---|
209 | |
---|
210 | |
---|
211 | |
---|
212 | ############################################################ |
---|
213 | =item folder_ordering() |
---|
214 | Function to allow us to sort folders which may have a number |
---|
215 | prefix. |
---|
216 | =cut |
---|
217 | ############################################################ |
---|
218 | sub folder_ordering { |
---|
219 | my $a = shift; |
---|
220 | my $b = shift; |
---|
221 | |
---|
222 | my $numeric_a = 999999; |
---|
223 | my $numeric_b = 999999; |
---|
224 | if ( $a =~ m{^(\d+)-} ) { $numeric_a = $1; } |
---|
225 | if ( $b =~ m{^(\d+)-} ) { $numeric_b = $1; } |
---|
226 | |
---|
227 | return -1 if ( $numeric_a < $numeric_b ); |
---|
228 | return 1 if ( $numeric_a > $numeric_b ); |
---|
229 | |
---|
230 | # Fall back on alphanumeric comparison |
---|
231 | return -1 if ( $a lt $b ); |
---|
232 | return 1 if ( $a lt $b ); |
---|
233 | |
---|
234 | return 0; |
---|
235 | } |
---|
236 | |
---|
237 | |
---|
238 | |
---|
239 | ############################################################ |
---|
240 | # Get the current version of PostgreSQL |
---|
241 | ############################################################ |
---|
242 | sub get_postgresql_version { |
---|
243 | |
---|
244 | my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { AutoCommit => 0 } ) or die "Can't connect to database $dbname"; |
---|
245 | |
---|
246 | my $current_version = $dbh->prepare( <<EOQ ) or die $dbh->errstr; |
---|
247 | SELECT regexp_replace( split_part( version(), ' ', 2), E'\.[0-9]\$', '') |
---|
248 | EOQ |
---|
249 | |
---|
250 | if ( $current_version->execute() ) { |
---|
251 | my $version = $current_version->fetchrow_arrayref(); |
---|
252 | undef $current_version; |
---|
253 | $dbh->disconnect; |
---|
254 | $version->[0] =~ s/\D+$//; # It seems the regex in SQL is broken in 8.1, at least on CentOS 5.3 |
---|
255 | return $version->[0]; |
---|
256 | } |
---|
257 | else { |
---|
258 | die "ERROR: Cannot read current revision from database."; |
---|
259 | } |
---|
260 | |
---|
261 | } |
---|
262 | |
---|
263 | |
---|
264 | ############################################################ |
---|
265 | # Get the current revision |
---|
266 | ############################################################ |
---|
267 | sub get_current_revision { |
---|
268 | |
---|
269 | my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { AutoCommit => 0 } ) or die "Can't connect to database $dbname"; |
---|
270 | |
---|
271 | my $current_revision = $dbh->prepare( <<EOQ ) or die $dbh->errstr; |
---|
272 | SELECT schema_major, schema_minor, schema_patch FROM awl_db_revision ORDER BY schema_id DESC LIMIT 1 |
---|
273 | EOQ |
---|
274 | |
---|
275 | if ( $current_revision->execute() ) { |
---|
276 | my $revision = $current_revision->fetchrow_hashref(); |
---|
277 | undef $current_revision; |
---|
278 | $dbh->disconnect; |
---|
279 | return $revision; |
---|
280 | } |
---|
281 | else { |
---|
282 | die "ERROR: Cannot read current revision from database."; |
---|
283 | } |
---|
284 | |
---|
285 | } |
---|
286 | |
---|
287 | |
---|
288 | |
---|
289 | ############################################################ |
---|
290 | # Apply a DB Patch File |
---|
291 | ############################################################ |
---|
292 | sub apply_patch { |
---|
293 | |
---|
294 | my $patch = shift; |
---|
295 | |
---|
296 | apply_sql_file( $patchdir, $patch ); |
---|
297 | |
---|
298 | $current_revision = get_current_revision(); |
---|
299 | if ( compare_revisions($current_revision,revision_hash($patch)) != 0 ) { |
---|
300 | printf( "Failed to apply revision %s to the database!\n", $patch ) if ( $debug ); |
---|
301 | return 0; |
---|
302 | } |
---|
303 | return 1; # Success |
---|
304 | } |
---|
305 | |
---|
306 | |
---|
307 | |
---|
308 | ############################################################ |
---|
309 | # Apply SQL File |
---|
310 | # Note that this stuffs the password into an environment |
---|
311 | # variable, which isn't ideal. If you use a .pgpass you |
---|
312 | # can bypass that issue, but you still need it on the command |
---|
313 | # line for this program until I get a patch from someone. |
---|
314 | ############################################################ |
---|
315 | sub apply_sql_file { |
---|
316 | |
---|
317 | my $sqldir = shift; |
---|
318 | my $sqlfile = shift; |
---|
319 | |
---|
320 | my @psql_opts = ( "psql", "-q", "-f", $sqldir."/".$sqlfile ); |
---|
321 | push @psql_opts, "-h", $dbhost if ( $dbhost ne "" ); |
---|
322 | push @psql_opts, "-p", "$dbport" if ( $dbport != 5432 ); |
---|
323 | push @psql_opts, "-U", $dbuser if ( $dbuser ne "" ); |
---|
324 | push @psql_opts, $dbname; # It seems that FreeBSD has a poorer argument parsing library so non-option arguments must be last |
---|
325 | $ENV{'PGPASS'} = $dbpass if ( $dbpass ne "" ); |
---|
326 | |
---|
327 | my $command = join ' ', @psql_opts; |
---|
328 | $last_results = `$command 2>&1 1>/dev/null`; |
---|
329 | |
---|
330 | $last_results =~ s/^.*WARNING: there is no transaction in progress\s$//m; |
---|
331 | $last_results =~ s/^.*NOTICE: //m; |
---|
332 | } |
---|
333 | |
---|
334 | |
---|
335 | ############################################################ |
---|
336 | =item apply_sql_folder |
---|
337 | Applies the SQL files in a folder in order, with some magic to apply |
---|
338 | specifically versioned ones by preference. |
---|
339 | =cut |
---|
340 | sub apply_sql_folder { |
---|
341 | my $dbadir = shift; |
---|
342 | my $folder_name = shift; |
---|
343 | my $announce_prefix = shift; |
---|
344 | |
---|
345 | my $folder = $dbadir . '/' . $folder_name; |
---|
346 | opendir( FOLDER, $folder ) or die "Can't open SQL directory $folder"; |
---|
347 | my @sql_files = grep { /^[^.].*\.sql$/ } readdir(FOLDER); |
---|
348 | closedir(FOLDER); |
---|
349 | @sql_files = grep( !/-\d+.\d+\.sql$/, @sql_files); |
---|
350 | @sql_files = sort { folder_ordering($a,$b); } @sql_files; |
---|
351 | |
---|
352 | for ( my $i=0; $i <= $#sql_files; $i++ ) { |
---|
353 | my $apply_file = $sql_files[$i]; |
---|
354 | my $testfile = $folder . '/' . $apply_file; |
---|
355 | $testfile =~ s{\.sql$}{-$pg_version.sql}; |
---|
356 | $apply_file = $testfile if ( -f $testfile ); |
---|
357 | |
---|
358 | apply_sql_file( $folder, $apply_file ); |
---|
359 | print $announce_prefix, $apply_file, " applied.\n"; |
---|
360 | } |
---|
361 | |
---|
362 | } |
---|
363 | |
---|
364 | |
---|
365 | ############################################################ |
---|
366 | # Apply database permissions from file |
---|
367 | ############################################################ |
---|
368 | sub apply_permissions { |
---|
369 | |
---|
370 | my $sqldir = shift; |
---|
371 | my $permsfile = shift; |
---|
372 | |
---|
373 | open PERMS, '<', $sqldir."/".$permsfile; |
---|
374 | my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { AutoCommit => 1 } ) or die "Can't connect to database $dbname"; |
---|
375 | |
---|
376 | my $sql; |
---|
377 | my $current_grant; |
---|
378 | |
---|
379 | while( <PERMS> ) { |
---|
380 | next if ( /^\s*(#|--)/ ); |
---|
381 | |
---|
382 | /^\s*GRANT\s+(\S.*)\s*$/i && do { |
---|
383 | $current_grant = $1; |
---|
384 | }; |
---|
385 | |
---|
386 | /^\s*ON\s+(\S.*)\s*$/i && do { |
---|
387 | defined($current_grant) or die "No GRANT before ON in $permsfile\n"; |
---|
388 | my $doohickey = $1; |
---|
389 | |
---|
390 | if ( $revoke_list ne "" ) { |
---|
391 | # TODO: we should really loop through the revoke_list so that a single non-existent |
---|
392 | # user doesn't cause this whole statement to fail. |
---|
393 | $sql = sprintf( "REVOKE ALL ON %s FROM %s %s", $doohickey, $appuser, $revoke_list ); |
---|
394 | print $sql, "\n" if ( $debug ); |
---|
395 | $dbh->do($sql); |
---|
396 | } |
---|
397 | |
---|
398 | $sql = sprintf( "GRANT %s on %s to %s", $current_grant, $doohickey, $appuser ); |
---|
399 | print $sql, "\n" if ( $debug ); |
---|
400 | $dbh->do($sql); |
---|
401 | |
---|
402 | if ( $force_owner ne "" ) { |
---|
403 | if ( $doohickey =~ /_seq$/ ) { |
---|
404 | $sql = sprintf( "GRANT ALL on %s to %s", $doohickey, $force_owner ); |
---|
405 | } |
---|
406 | else { |
---|
407 | $sql = sprintf( "ALTER TABLE %s OWNER to %s", $doohickey, $force_owner ); |
---|
408 | } |
---|
409 | print $sql, "\n" if ( $debug ); |
---|
410 | $dbh->do($sql); |
---|
411 | } |
---|
412 | }; |
---|
413 | |
---|
414 | } |
---|
415 | close(PERMS); |
---|
416 | $dbh->disconnect; |
---|
417 | } |
---|
418 | |
---|
419 | |
---|
420 | |
---|
421 | ############################################################ |
---|
422 | # Tell the nice user how we do things. Short and sweet. |
---|
423 | ############################################################ |
---|
424 | sub show_usage { |
---|
425 | print <<OPTHELP; |
---|
426 | |
---|
427 | update-davical-database [options] |
---|
428 | |
---|
429 | Options are: |
---|
430 | --debug Turn on debugging |
---|
431 | --dbname name The database to dig into |
---|
432 | --dbuser name Connect to the database as this user. |
---|
433 | --dbport 5432 Connect to the database on this port. |
---|
434 | --dbhost name Connect to the database on this host. |
---|
435 | --appuser name The database username which the application uses for it's |
---|
436 | database connection. |
---|
437 | --owner name The database username which is used for administrative |
---|
438 | access to the database. This option forces the tables |
---|
439 | to be owned by this user (default: not present). |
---|
440 | --nopatch Don't try and apply any patches |
---|
441 | --revoke name Revoke permissions from this user |
---|
442 | |
---|
443 | The program will apply any patches to the database which have |
---|
444 | not yet been applied, run any desired data patch scripts and set |
---|
445 | the correct minimum permissions for the web application user. |
---|
446 | |
---|
447 | Rather than providing a password on the command-line it is recommended |
---|
448 | that you use a .pgpass file in your home directory to hold the database |
---|
449 | password. This file must be mode 600 to work and should have lines |
---|
450 | like: |
---|
451 | |
---|
452 | hostname:port:database:username:password |
---|
453 | |
---|
454 | Each bit can be replaced by an asterisk, e.g: |
---|
455 | *:*:davical:davical_dba:53cr3t |
---|
456 | |
---|
457 | OPTHELP |
---|
458 | exit 0; |
---|
459 | } |
---|
460 | |
---|