1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
#!/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 '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 '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%");
}
|
