[3733] | 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 | |
---|