A script to bring a FixMyStreet database up to date.

It will examine the current database to see at what state it is,
and optionally run the newer schema SQL files in order to bring
the database up to date.

At the 'adding bodies' stage, it will also query MapIt to fill the
name column of the new body table.

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%");
+}