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