diff options
Diffstat (limited to 'bin/update-schema')
| -rw-r--r-- | bin/update-schema | 134 |
1 files changed, 134 insertions, 0 deletions
diff --git a/bin/update-schema b/bin/update-schema new file mode 100644 index 000000000..f728f8c56 --- /dev/null +++ b/bin/update-schema @@ -0,0 +1,134 @@ +#!/usr/bin/perl +# +# This script should hopefully work out at what state the database is and, if +# the commit argument is provided, run the right schema files to bring it up to +# date. Let us know if it doesn't work; as with any upgrade script, do take a +# backup of your database before running. +# +# update-schema [--commit] + +use strict; +use warnings; + +# Horrible boilerplate to set up appropriate library paths. +use FindBin; +use lib "$FindBin::Bin/../commonlib/perllib"; + +use mySociety::Config; +use mySociety::DBHandle qw(dbh); +use mySociety::MaPit; + +mySociety::Config::set_file("$FindBin::Bin/../conf/general"); +mySociety::DBHandle::configure( + Name => mySociety::Config::get('FMS_DB_NAME'), + User => mySociety::Config::get('FMS_DB_USER'), + Password => mySociety::Config::get('FMS_DB_PASS'), + Host => mySociety::Config::get('FMS_DB_HOST', undef), + Port => mySociety::Config::get('FMS_DB_PORT', undef) +); + +my $commit = 0; +$commit = 1 if @ARGV && $ARGV[0] eq '--commit'; + +my $nothing = 1; +my $current_version = get_db_version(); +print "Current database version = $current_version\n"; +print "= Dry run =\n" unless $commit; + +for my $path (glob("$FindBin::Bin/../db/schema_*")) { + my ($name) = $path =~ /schema_(.*)\.sql$/; + next if $name le $current_version; + next if $name =~ /$current_version-/; # For number only match + print "* $name\n"; + $nothing = 0; + next unless $commit; + + open(FP, $path) or die $!; + my @statements; + my $s = ''; + my $in_function = 0; + while(<FP>) { + next if /^--/; # Ignore comments + $s .= $_; + # Functions may have semicolons within them + $in_function = 1 if /create function/i; + $in_function = 0 if /language 'plpgsql'/i; + if ($s =~ /;/ && !$in_function) { + push @statements, $s; + $s = ''; + } + } + close FP; + + foreach my $st (@statements) { + dbh()->do($st); + } +} + +if ( $commit && $current_version lt '0028' ) { + print "Bodies created, fetching names from mapit\n"; + my $area_ids = dbh()->selectcol_arrayref('SELECT area_id FROM body_areas'); + if ( @$area_ids ) { + my $areas = mySociety::MaPit::call('areas', $area_ids); + foreach (values %$areas) { + dbh()->do('UPDATE body SET name=? WHERE id=?', {}, $_->{name}, $_->{id}); + } + dbh()->do('COMMIT'); + } +} + +print "Nothing to do\n" if $nothing; + +# --- + +# By querying the database schema, we can see where we're currently at +# (assuming schema change files are never half-applied, which should be the case) +sub get_db_version { + return '0028' if table_exists('body'); + return '0027' if column_exists('problem', 'subcategory'); + return '0026' if column_exists('open311conf', 'send_extended_statuses'); + return '0025' if column_like('alert_type', "ref='new_problems'", 'item_where', 'duplicate'); + return '0024' if column_exists('contacts', 'non_public'); + return '0023' if column_exists('open311conf', 'can_be_devolved'); + return '0022' if column_exists('problem', 'interest_count'); + return '0021' if column_exists('problem', 'external_source'); + return '0020' if column_exists('open311conf', 'suppress_alerts'); + return '0019' if column_exists('users', 'title'); + return '0018' if column_exists('open311conf', 'comment_user_id'); + return '0017' if column_exists('open311conf', 'send_comments'); + return '0016' if column_exists('comment', 'send_fail_count'); + return '0015-add_send_method_used_column_to_problem' if column_exists('problem', 'send_method_used'); + return '0015-add_extra_to_comment' if column_exists('comment', 'extra'); + return '0014' if column_exists('problem', 'send_fail_count'); + return '0013-add_send_method_column_to_open311conf' if column_exists('open311conf', 'send_method'); + return '0013-add_external_id_to_comment' if column_exists('comment', 'external_id'); + return '0012' if column_exists('problem', 'geocode'); + return '0011' if column_exists('contacts', 'extra'); + return '0010' if table_exists('open311conf'); + return '0009-update_alerts_problem_state_queries' if column_like('alert_type', "ref='new_problems'", 'item_where', 'investigating'); + return '0009-add_extra_to_problem' if column_exists('problem', 'extra'); + return '0008' if 0; + return '0007' if column_exists('comment', 'problem_state'); + return '0006' if 0; + return '0005-add_council_user_flag' if column_exists('users', 'from_council'); + return '0005-add_abuse_flags_to_users_and_reports' if column_exists('problem', 'flagged'); + return '0000'; +} + +# Returns true if a table exists +sub table_exists { + my $table = shift; + return dbh()->selectrow_array('select count(*) from pg_tables where tablename = ?', {}, $table); +} + +# Returns true if a column of table exists +sub column_exists { + my ( $table, $column ) = @_; + return dbh()->selectrow_array('select count(*) from pg_class, pg_attribute WHERE pg_class.relname=? AND pg_attribute.attname=? AND pg_class.oid=pg_attribute.attrelid AND pg_attribute.attnum > 0', {}, $table, $column); +} + +# Returns true if a column of a row in a table contains some text +sub column_like { + my ( $table, $where, $column, $contents ) = @_; + return dbh()->selectrow_array("select count(*) from $table WHERE $where AND $column LIKE ?", {}, "%$contents%"); +} |