path: root/bin/update-schema

#!/usr/bin/env perl

=head1 NAME

bin/update-schema - minimalist database upgrades for FixMyStreet

=head1 SYNOPSIS

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.

    # show status and upgrades available
    update-schema
    update-schema --commit   # run all available upgrades

    # upgrade to a given version (NOT YET IMPLEMENTED)
    # update-schema --version=0032 --commit

    # list downgrades, (and run them with --commit)
    update-schema --downgrade
    update-schema --downgrade --commit  # if there is only one available downgrade
    update-schema --downgrade --version=0031 --commit

    # show this help
    update-schema --help

=cut

use strict;
use warnings;

my $bin_dir;
BEGIN {
    use File::Basename qw(dirname);
    use File::Spec;
    $bin_dir = dirname(File::Spec->rel2abs($0));
    require "$bin_dir/../setenv.pl";
}

use FixMyStreet;
use FixMyStreet::Cobrand;
use FixMyStreet::DB;
use mySociety::MaPit;
use Getopt::Long;
use Pod::Usage;

my $db = FixMyStreet::DB->schema->storage;

my ($commit, $version, $downgrade, $help);

GetOptions (
    'commit'    => \$commit,
    'version=s' => \$version,
    'downgrade' => \$downgrade,
    'help|h|?'  => \$help,
);

pod2usage(1) if $help;

$commit = 1 if @ARGV && $ARGV[0] eq '--commit';

my $nothing = 1;
my $current_version;

sub get_and_print_current_version {
    my $new_current_version = get_db_version();
    if ($new_current_version ne ($current_version||'')) {
        print "Current database version = $new_current_version\n";
    }
    $current_version = $new_current_version;
}

get_and_print_current_version();

print "= Dry run =\n" unless $commit;

my $upgrade = !$downgrade;

sub get_statements {
    my $path = shift;
    open(my $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 (or replace )?function/i;
        $in_function = 0 if /language (sql|'plpgsql')/i;
        if ($s =~ /;/ && !$in_function) {
            push @statements, $s;
            $s = '';
        }
    }
    close $FP;
    return @statements;
}

sub run_statements {
    foreach my $st (@_) {
        print ".";
        $db->dbh->do($st);
    }
    print "\n";
}

if ($upgrade && $current_version eq 'EMPTY') {
    print "* Database empty, loading in whole schema\n";
    $nothing = 0;
    if ($commit) {
        run_statements(get_statements("$bin_dir/../db/schema.sql"));
        run_statements(get_statements("$bin_dir/../db/generate_secret.sql"));
        run_statements(get_statements("$bin_dir/../db/fixture.sql"));
    }
} elsif ($upgrade) {
    if ($version) {
        die "Not currently implemented";
    }
    for my $path (glob("$bin_dir/../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;

        my @statements = get_statements($path);

        if (@statements) {
            run_statements(@statements);
        }
    }

    if ( $commit && $current_version lt '0028' ) {
        $nothing = 0;
        print "Bodies created, fetching names from mapit\n";
        my $area_ids = $db->dbh->selectcol_arrayref('SELECT area_id FROM body_areas');
        if ( @$area_ids ) {
            my $areas = mySociety::MaPit::call('areas', $area_ids);
            $db->txn_begin;
            foreach (values %$areas) {
                $db->dbh->do('UPDATE body SET name=? WHERE id=?', {}, $_->{name}, $_->{id});
            }
            $db->txn_commit;
        }
    }

    if ( $commit && $current_version lt '0054' ) {
        $nothing = 0;
        print "States created, importing names\n";
        my @avail = FixMyStreet::Cobrand->available_cobrand_classes;
        # Pick first available cobrand and language for database name import
        my $cobrand = $avail[0] ? FixMyStreet::Cobrand::class($avail[0]) : 'FixMyStreet::Cobrand::Default';
        my $lang = $cobrand->new->set_lang_and_domain(undef, 1, FixMyStreet->path_to('locale')->stringify);
        my $names = $db->dbh->selectcol_arrayref('SELECT name FROM state');
        $db->txn_begin;
        foreach (@$names) {
            $db->dbh->do('UPDATE state SET name=? WHERE name=?', {}, _($_), $_);
        }
        $db->txn_commit;
    }
}

if ($downgrade) {
    my %downgrades;
    for my $path (glob("$bin_dir/../db/downgrade_*")) {
        my ($from, $to) = $path =~ /downgrade_(.*)---(.*)\.sql$/;
        next unless $from eq $current_version;
        $downgrades{$to} = $path;
    }
    if (keys %downgrades) {
        if (scalar keys %downgrades == 1) {
            ($version) = (keys %downgrades) unless $version;
        }

        if (my $path = $downgrades{$version}) {
            print "Downgrade to $version\n";
            $nothing = 0;

            if ($commit) {
                my @statements = get_statements($path);
                run_statements(@statements);
            }
        } else {
            warn "No downgrade to $version\n";
        }

        if ($nothing) {
            for my $version (sort keys %downgrades) {
                print "* $version\n";
            }
        }
    }
    else {
        print "No downgrades available for this version\n";
    }
}

if ($nothing) {
    print "Nothing to do\n" if $nothing;
}
else {
    get_and_print_current_version();
}

# ---

# 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 'EMPTY' if ! table_exists('problem');
    return '0056' if column_exists('users', 'email_verified');
    return '0055' if column_exists('response_priorities', 'is_default');
    return '0054' if table_exists('state');
    return '0053' if table_exists('report_extra_fields');
    return '0052' if table_exists('translation');
    return '0051' if column_exists('contacts', 'state');
    return '0050' if table_exists('defect_types');
    return '0049' if column_exists('response_priorities', 'external_id');
    return '0048' if column_exists('response_templates', 'state');
    return '0047' if column_exists('response_priorities', 'description');
    return '0046' if column_exists('users', 'extra');
    return '0045' if table_exists('response_priorities');
    return '0044' if table_exists('contact_response_templates');
    return '0043' if column_exists('users', 'area_id');
    return '0042' if table_exists('user_planned_reports');
    return '0041' if column_exists('users', 'is_superuser') && ! constraint_exists('user_body_permissions_permission_type_check');
    return '0040' if column_exists('users', 'is_superuser');
    return '0039' if column_exists('users', 'facebook_id');
    return '0038' if column_exists('admin_log', 'time_spent');
    return '0037' if table_exists('response_templates');
    return '0036' if constraint_contains('problem_cobrand_check', 'a-z0-9_');
    return '0035' if column_exists('problem', 'bodies_missing');
    return '0034' if ! function_exists('ms_current_timestamp');
    return '0033' if ! function_exists('angle_between');
    return '0032' if table_exists('moderation_original_data');
    return '0031' if column_exists('body', 'external_url');
    return '0030' if ! constraint_exists('admin_log_action_check');
    return '0029' if column_exists('body', 'deleted');
    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 '0004' if column_exists('comment', 'user_id');
    return '0003' if column_exists('alert', 'user_id');
    return '0002' if column_exists('problem', 'user_id');
    return '0001' if table_exists('sessions');
    return '0000' if table_exists('problem');
    die "Database schema issue!";
}

# Returns true if a table exists
sub table_exists {
    my $table = shift;
    return $db->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 $db->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 $db->dbh->selectrow_array("select count(*) from $table WHERE $where AND $column LIKE ?", {}, "%$contents%");
}

# Returns true if a check constraint on a table exists
sub constraint_exists {
    my ( $constraint ) = @_;
    return $db->dbh->selectrow_array('select count(*) from pg_constraint where conname = ?', {}, $constraint);
}

# Returns true if a check constraint contains a certain string
sub constraint_contains {
    my ( $constraint, $check ) = @_;
    my ($consrc) = $db->dbh->selectrow_array('select consrc from pg_constraint where conname = ?', {}, $constraint);
    return unless $consrc;
    return $consrc =~ /$check/;
}

# Returns true if a function exists
sub function_exists {
    my $fn = shift;
    return $db->dbh->selectrow_array('select count(*) from pg_proc where proname = ?', {}, $fn);
}