dba/update-apms-database

   1 #!/usr/bin/perl -w
   2 #
   3 # Update the APMS database by repeatedly applying patches to it
   4 # in the correct order.
   5 #
   6
   7 use strict;
   8
   9 use DBI;
  10 use POSIX qw(floor);
  11 use Getopt::Long qw(:config permute);  # allow mixed args.
  12
  13 # Options variables
  14 my $debug  = 0;
  15 my $dbname = "apms";
  16 my $dbport = 5432;
  17 my $dbuser = "";
  18 my $dbpass = "";
  19 my $dbhost = "";
  20 my $appuser = "apms_app";
  21 my $helpmeplease = 0;
  22 my $apply_patches = 1;
  23 my $revoke_list = "";
  24 my $force_owner = "";
  25 my $reset_sequences = 0;
  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/apms/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             'resequence!' => \$reset_sequences,
  65             'revoke=s'  => \$revoke_list,
  66             'help'      => \$helpmeplease  );
  67
  68 show_usage() if ( $helpmeplease );
  69
  70 $revoke_list = ", ". $revoke_list if ( $revoke_list ne "" );
  71
  72
  73 ############################################################
  74 # Open database connection. Note that the standard PostgreSQL
  75 # environment variables will also work with DBD::Pg.
  76 ############################################################
  77 my $dsn = "dbi:Pg:dbname=$dbname";
  78 $dsn .= ";host=$dbhost" if ( "$dbhost" ne "" );
  79 $dsn .= ";port=$dbport" if ( $dbport != 5432 );
  80
  81 print "Using database: $dbuser".'%'.$dbpass.'@'.$dsn."\n" if ( $debug );
  82
  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 currently at revision %d.%d.%d.\n", $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 # Ensure the functions are up to date
 136 apply_sql_file( $dbadir, "functions.sql" );
 137 print "APMS functions updated.\n";
 138
 139 # Ensure the permissions are up to date
 140 apply_permissions( $dbadir, "appuser_permissions.txt" );
 141 print "Database permissions updated.\n";
 142
 143 # Ensure the sequence values are reasonable
 144 set_table_sequences( $dbadir, "apms_sequences.txt" );
 145 print "Database sequences updated.\n";
 146
 147 # The End!
 148 exit 0;
 149
 150
 151
 152
 153 ############################################################
 154 # Revision Hash - we either have a single parameter,
 155 # which is of the form "1.2.3" or we have three parameters.
 156 ############################################################
 157 sub revision_hash {
 158   my $rev = +{ 'schema_major', => 0, 'schema_minor' => 0, 'schema_patch' => 0, 'alternative' => '0' };
 159   my $first = shift;
 160   return $rev unless ( defined($first) );
 161   if ( $first =~ /^([0-9]+)\.([0-9]+)\.([0-9]+)([a-z]?)([^0-9]|$)/ ) {
 162     $rev->{'schema_major'} = $1;
 163     $rev->{'schema_minor'} = $2;
 164     $rev->{'schema_patch'} = $3;
 165     $rev->{'alternative'} = $4;
 166   }
 167   else {
 168     $rev->{'schema_major'} = $first;
 169     $rev->{'schema_minor'} = shift;
 170     $rev->{'schema_patch'} = shift;
 171     $rev->{'alternative'} = '0';
 172   }
 173   return $rev;
 174 }
 175
 176
 177 ############################################################
 178 # Compare revisions
 179 ############################################################
 180 sub compare_revisions {
 181   my $a = shift;
 182   my $b = shift;
 183   my $test_alt = shift;
 184
 185   return -1 if ( $a->{'schema_major'} < $b->{'schema_major'} );
 186   return  1 if ( $a->{'schema_major'} > $b->{'schema_major'} );
 187
 188   return -1 if ( $a->{'schema_minor'} < $b->{'schema_minor'} );
 189   return  1 if ( $a->{'schema_minor'} > $b->{'schema_minor'} );
 190
 191   return -1 if ( $a->{'schema_patch'} < $b->{'schema_patch'} );
 192   return  1 if ( $a->{'schema_patch'} > $b->{'schema_patch'} );
 193
 194   if ( defined($test_alt) ) {
 195     return -1 if ( $a->{'alternative'} lt $b->{'alternative'} );
 196     return  1 if ( $a->{'alternative'} gt $b->{'alternative'} );
 197   }
 198
 199   return 0;
 200
 201 }
 202
 203
 204
 205 ############################################################
 206 # Get the current revision
 207 ############################################################
 208 sub get_current_revision {
 209
 210   my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { AutoCommit => 0 } ) or die "Can't connect to database $dbname";
 211
 212   my $current_revision = $dbh->prepare( <<EOQ  ) or die $dbh->errstr;
 213   SELECT schema_major, schema_minor, schema_patch FROM awl_db_revision ORDER BY schema_id DESC LIMIT 1
 214 EOQ
 215
 216   if ( $current_revision->execute() ) {
 217     my $revision = $current_revision->fetchrow_hashref();
 218     undef $current_revision;
 219     $dbh->disconnect;
 220     return $revision;
 221   }
 222   else {
 223     die "ERROR: Cannot read current revision from database.";
 224   }
 225
 226 }
 227
 228
 229
 230 ############################################################
 231 # Apply a DB Patch File
 232 ############################################################
 233 sub apply_patch {
 234
 235   my $patch = shift;
 236
 237   apply_sql_file( $patchdir, $patch );
 238
 239   $current_revision = get_current_revision();
 240   if ( compare_revisions($current_revision,revision_hash($patch)) != 0 ) {
 241     printf( "Failed to apply revision %s to the database!\n", $patch ) if ( $debug );
 242     return 0;
 243   }
 244   return 1;  # Success
 245 }
 246
 247
 248
 249 ############################################################
 250 # Apply SQL File
 251 #  Note that this stuffs the password into an environment
 252 #  variable, which isn't ideal.  If you use a .pgpass you
 253 #  can bypass that issue, but you still need it on the command
 254 #  line for this program until I get a patch from someone.
 255 ############################################################
 256 sub apply_sql_file {
 257
 258   my $sqldir  = shift;
 259   my $sqlfile = shift;
 260
 261   my @psql_opts = ( "psql", "-q", "-f", $sqldir."/".$sqlfile, $dbname );
 262   push @psql_opts, "-h", $dbhost if ( $dbhost ne "" );
 263   push @psql_opts, "-p", "$dbport" if ( $dbport != 5432 );
 264   push @psql_opts, "-U", $dbuser if ( $dbuser ne "" );
 265   $ENV{'PGPASS'} = $dbpass if ( $dbpass ne "" );
 266
 267   my $command = join ' ', @psql_opts;
 268   $last_results = `$command 2>&1 1>/dev/null`;
 269
 270   $last_results =~ s/^.*WARNING:  there is no transaction in progress\s$//m;
 271   $last_results =~ s/^.*NOTICE: //m;
 272 }
 273
 274
 275
 276 ############################################################
 277 # Apply database permissions from file
 278 ############################################################
 279 sub apply_permissions {
 280
 281   my $sqldir  = shift;
 282   my $permsfile = shift;
 283
 284   open PERMS, '<', $sqldir."/".$permsfile;
 285   my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { AutoCommit => 1 } ) or die "Can't connect to database $dbname";
 286
 287   my $sql;
 288   my $current_grant;
 289
 290   while( <PERMS> ) {
 291     next if ( /^\s*(#|--)/ );
 292
 293     /^\s*GRANT\s+(\S.*)\s*$/i && do {
 294       $current_grant = $1;
 295     };
 296
 297     /^\s*ON\s+(\S.*)\s*$/i && do {
 298       defined($current_grant) or die "No GRANT before ON in $permsfile\n";
 299       my $doohickey = $1;
 300
 301       if ( $revoke_list ne "" ) {
 302         # TODO: we should really loop through the revoke_list so that a single non-existent
 303         # user doesn't cause this whole statement to fail.
 304         $sql = sprintf( "REVOKE ALL ON %s FROM %s %s", $doohickey, $appuser, $revoke_list );
 305         print $sql, "\n" if ( $debug );
 306         $dbh->do($sql);
 307       }
 308
 309       $sql = sprintf( "GRANT %s on %s to %s", $current_grant, $doohickey, $appuser );
 310       print $sql, "\n" if ( $debug );
 311       $dbh->do($sql);
 312
 313       if ( $force_owner ne "" ) {
 314         if ( $doohickey =~ /_seq$/ ) {
 315           $sql = sprintf( "GRANT ALL on %s to %s", $doohickey, $force_owner );
 316         }
 317         else {
 318           $sql = sprintf( "ALTER TABLE %s OWNER to %s", $doohickey, $force_owner );
 319         }
 320         print $sql, "\n" if ( $debug );
 321         $dbh->do($sql);
 322       }
 323     };
 324
 325   }
 326   close(PERMS);
 327   $dbh->disconnect;
 328 }
 329
 330
 331
 332 ################################################################
 333 # Set sequences for tables based on a file of table,column pairs
 334 ################################################################
 335 sub set_table_sequences {
 336   my $sqldir  = shift;
 337   my $seqsfile = shift;
 338
 339   open SEQS, '<', $sqldir."/".$seqsfile;
 340   my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { AutoCommit => 1 } ) or die "Can't connect to database $dbname";
 341
 342   my $current_sequence = $dbh->prepare( <<EOQ  ) or die $dbh->errstr;
 343 SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
 344     FROM pg_catalog.pg_attrdef d
 345     JOIN pg_catalog.pg_attribute a ON (d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
 346     JOIN pg_catalog.pg_class c ON (c.oid = a.attrelid)
 347    WHERE c.relname = ? AND a.attname = ?
 348 EOQ
 349
 350   my $maximum_vaue = $dbh->prepare( <<EOQ  ) or die $dbh->errstr;
 351
 352 EOQ
 353
 354   my $sql;
 355   my $current_grant;
 356
 357   while( <SEQS> ) {
 358     next if ( /^\s*(#|--)/ );
 359
 360     if ( m{^\s*(\S+)\s*,\s*(\S+)\s*$} ) {
 361       my $tablename = $1;
 362       my $columname = $2;
 363
 364       $current_sequence->execute( $tablename, $columname ) or die "ERROR: Cannot read current sequence for column $tablename.$columname from database.";
 365       my $seqdef = $current_sequence->fetchall_arrayref();
 366       $seqdef = $seqdef->[0][0];
 367
 368       $sql = "SELECT $columname FROM $tablename ORDER BY 1 DESC LIMIT 1";
 369       print STDERR $sql, "\n" if ( $debug );
 370       my $maxval_sth = $dbh->selectcol_arrayref( $sql ) or die "ERROR: Cannot read current maximum for column $tablename.$columname from database.";
 371       my $maxval;
 372       $maxval = $maxval_sth->[0] if ( defined($maxval_sth) );
 373
 374       if ( $tablename eq 'newbatch' ) {
 375         $sql = "SELECT $columname FROM batch ORDER BY 1 DESC LIMIT 1";
 376         print STDERR $sql, "\n" if ( $debug );
 377         my $maxval_sth = $dbh->selectcol_arrayref( $sql ) or die "ERROR: Cannot read current maximum for column batch.$columname from database.";
 378         my $maxbatch = $maxval_sth->[0] if ( defined($maxval_sth) );
 379         $maxval = $maxbatch if ( !defined($maxval) || $maxbatch > $maxval );
 380       }
 381
 382       if ( defined($seqdef) && $seqdef =~ m{^nextval\('(.+)'} ) {
 383         my $seqname = $1;
 384         $sql = "select last_value FROM $seqname";
 385         print STDERR $sql, "\n" if ( $debug );
 386         my $seq_last_sth = $dbh->selectcol_arrayref( $sql ) or die "ERROR: Cannot read last value of sequence $seqname for ${tablename}.${columname}";
 387         my $seq_last;
 388         $seq_last = $seq_last_sth->[0] if ( defined($seq_last_sth) );
 389         if ( $reset_sequences || !defined($maxval) || !defined($seq_last) || $seq_last < $maxval ) {
 390           if ( defined($maxval) && $maxval > 0 ) {
 391             $sql = "select setval(?,$maxval)";
 392           }
 393           else {
 394             $sql = "select setval(?,1,false)";
 395           }
 396           print STDERR $sql, "\n" if ( $debug );
 397           $dbh->do( $sql, {}, ($seqname) ) or die "ERROR: Cannot set appropriate value for sequence $seqname for ${tablename}.${columname}";
 398         }
 399       }
 400       else {
 401         my $start = "";
 402         $start = "START $maxval" if ( defined($maxval) && $maxval >= 1 );
 403         $sql = "CREATE SEQUENCE ${tablename}_${columname}_seq $start OWNED BY ${tablename}.${columname}";
 404         print STDERR $sql, "\n" if ( $debug );
 405         $dbh->do( $sql ) or die "ERROR: Cannot create sequence ${tablename}_${columname}_seq.";
 406         $sql = "ALTER TABLE ${tablename} ALTER COLUMN ${columname} SET DEFAULT nextval('${tablename}_${columname}_seq')";
 407         print STDERR $sql, "\n" if ( $debug );
 408         $dbh->do( $sql ) or die "ERROR: Cannot assign sequence ${tablename}_${columname}_seq.as default for $tablename.$columname";
 409       }
 410     }
 411   }
 412   close(SEQS);
 413   $dbh->disconnect;
 414
 415 }
 416
 417
 418
 419 ############################################################
 420 # Tell the nice user how we do things.  Short and sweet.
 421 ############################################################
 422 sub show_usage {
 423     print <<OPTHELP;
 424
 425 update-rscds-database [options]
 426
 427 Options are:
 428     --debug           Turn on debugging
 429     --dbname  name    The database to dig into
 430     --dbuser  name    Connect to the database as this user.
 431     --dbport  5432    Connect to the database on this port.
 432     --dbhost  name    Connect to the database on this host.
 433     --appuser name    The database username which the application uses for it's
 434                       database connection.
 435     --owner name      The database username which is used for administrative
 436                       access to the database.  This option forces the tables
 437                       to be owned by this user (default: not present).
 438     --nopatch         Don't try and apply any patches
 439     --revoke  name    Revoke permissions from this user
 440
 441 The program will apply any patches to the database which have
 442 not yet been applied, run any desired data patch scripts and set
 443 the correct minimum permissions for the web application user.
 444
 445 Rather than providing a password on the command-line it is recommended
 446 that you use a .pgpass file in your home directory to hold the database
 447 password.  This file must be mode 600 to work.
 448
 449 OPTHELP
 450     exit 0;
 451 }
 452