diff options
38 files changed, 140 insertions, 195 deletions
diff --git a/bin/fiksgatami/load-norwegian-contacts b/bin/fiksgatami/load-norwegian-contacts index b73778848..ed382d6fc 100755 --- a/bin/fiksgatami/load-norwegian-contacts +++ b/bin/fiksgatami/load-norwegian-contacts @@ -54,13 +54,13 @@ while (<FP>) { $categories = $defcategories; } # dbh()->do("INSERT INTO contacts (area_id, email, editor, whenedited, note, confirmed, deleted) -# VALUES (?, ?, 'import', ms_current_timestamp(), 'Initial import', 'false', 'false')", +# VALUES (?, ?, 'import', current_timestamp, 'Initial import', 'false', 'false')", # {}, $id, $email); # } else { for my $category (split(/,\s*/, $categories)) { print " Category '$category'\n"; dbh()->do("INSERT INTO contacts (area_id, email, category, editor, whenedited, note, confirmed, deleted) - VALUES (?, ?, ?, 'import', ms_current_timestamp(), 'Initial import', 'true', 'false')", + VALUES (?, ?, ?, 'import', current_timestamp, 'Initial import', 'true', 'false')", {}, $id, $email, $category); } # } diff --git a/bin/fixmystreet.com/import-categories b/bin/fixmystreet.com/import-categories index e9008b93f..fe4c2e027 100755 --- a/bin/fixmystreet.com/import-categories +++ b/bin/fixmystreet.com/import-categories @@ -70,7 +70,7 @@ sub add_categories { dbh()->do("insert into contacts (area_id, category, email, editor, whenedited, note, confirmed, deleted) values - (?, ?, ?, 'import', ms_current_timestamp(), 'Initial copy', ?, 'f')", {}, + (?, ?, ?, 'import', current_timestamp, 'Initial copy', ?, 'f')", {}, $id, $_, $email, ($confirmed ? 1 : 0) ); } diff --git a/bin/fixmystreet.com/load-contacts b/bin/fixmystreet.com/load-contacts index b18699db1..78bb77e8f 100755 --- a/bin/fixmystreet.com/load-contacts +++ b/bin/fixmystreet.com/load-contacts @@ -42,7 +42,7 @@ while (<FP>) { s/\r?\n//g; my ($id, $email) = split /,/; dbh()->do("INSERT INTO contacts (area_id, email, editor, whenedited, note) - VALUES (?, ?, 'import', ms_current_timestamp(), 'Initial import')", + VALUES (?, ?, 'import', current_timestamp, 'Initial import')", {}, $id, $email); } dbh()->commit(); diff --git a/bin/send-comments b/bin/send-comments index fabf2b633..fbbd57891 100755 --- a/bin/send-comments +++ b/bin/send-comments @@ -130,12 +130,12 @@ while ( my $body = $bodies->next ) { if ( $id ) { $comment->update( { external_id => $id, - whensent => \'ms_current_timestamp()', + whensent => \'current_timestamp', } ); } else { $comment->update( { send_fail_count => $comment->send_fail_count + 1, - send_fail_timestamp => \'ms_current_timestamp()', + send_fail_timestamp => \'current_timestamp', send_fail_reason => 'Failed to post over Open311', } ); } diff --git a/bin/update-schema b/bin/update-schema index ce193f29c..57d1d8ad3 100755 --- a/bin/update-schema +++ b/bin/update-schema @@ -95,8 +95,8 @@ sub get_statements { 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; + $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 = ''; @@ -195,6 +195,8 @@ else { # 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 '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'); @@ -254,3 +256,8 @@ sub constraint_exists { return dbh()->selectrow_array('select count(*) from pg_constraint where conname = ?', {}, $constraint); } +# Returns true if a function exists +sub function_exists { + my $fn = shift; + return dbh()->selectrow_array('select count(*) from pg_proc where proname = ?', {}, $fn); +} diff --git a/db/rerun_dbic_loader.pl b/db/rerun_dbic_loader.pl index 152d319b1..d7dfd1c10 100755 --- a/db/rerun_dbic_loader.pl +++ b/db/rerun_dbic_loader.pl @@ -13,7 +13,6 @@ use DBIx::Class::Schema::Loader qw/ make_schema_at /; # create a exclude statement that filters out the table that we are not # interested in my @tables_to_ignore = ( - 'debugdate', # 'flickr_imported', # 'partial_user', # 'textmystreet', # diff --git a/db/schema.sql b/db/schema.sql index ef996fe8c..609a1445d 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -11,27 +11,6 @@ create table secret ( secret text not null ); --- If a row is present, that is date which is "today". Used for debugging --- to advance time without having to wait. -create table debugdate ( - override_today date -); - --- Returns the timestamp of current time, but with possibly overriden "today". -create function ms_current_timestamp() - returns timestamp as ' - declare - today date; - begin - today = (select override_today from debugdate); - if today is not null then - return today + current_time; - else - return current_timestamp; - end if; - end; -' language 'plpgsql'; - -- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC create table sessions ( id char(72) primary key, @@ -171,7 +150,7 @@ create table problem ( external_team text, -- Metadata - created timestamp not null default ms_current_timestamp(), + created timestamp not null default current_timestamp, confirmed timestamp, state text not null check ( state = 'unconfirmed' @@ -195,7 +174,7 @@ create table problem ( service text not null default '', cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site - lastupdate timestamp not null default ms_current_timestamp(), + lastupdate timestamp not null default current_timestamp, whensent timestamp, send_questionnaire boolean not null default 't', extra text, -- extra fields required for open311 @@ -307,7 +286,7 @@ create table comment ( anonymous bool not null, name text, -- null means anonymous website text, - created timestamp not null default ms_current_timestamp(), + created timestamp not null default current_timestamp, confirmed timestamp, text text not null, -- as entered by comment author photo bytea, @@ -355,7 +334,7 @@ create table token ( scope text not null, token text not null, data bytea not null, - created timestamp not null default ms_current_timestamp(), + created timestamp not null default current_timestamp, primary key (scope, token) ); @@ -387,7 +366,7 @@ create table alert ( lang text not null default 'en-gb', cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site - whensubscribed timestamp not null default ms_current_timestamp(), + whensubscribed timestamp not null default current_timestamp, whendisabled timestamp default null ); create index alert_user_id_idx on alert ( user_id ); @@ -399,7 +378,7 @@ create index alert_whensubscribed_confirmed_cobrand_idx on alert(whensubscribed, create table alert_sent ( alert_id integer not null references alert(id), parameter text, -- e.g. Update ID for new updates - whenqueued timestamp not null default ms_current_timestamp() + whenqueued timestamp not null default current_timestamp ); create index alert_sent_alert_id_parameter_idx on alert_sent(alert_id, parameter); @@ -444,7 +423,7 @@ create table admin_log ( ), object_id integer not null, action text not null, - whenedited timestamp not null default ms_current_timestamp(), + whenedited timestamp not null default current_timestamp, user_id int references users(id) null, reason text not null default '' ); @@ -462,7 +441,7 @@ create table moderation_original_data ( anonymous bool not null, -- Metadata - created timestamp not null default ms_current_timestamp() + created timestamp not null default current_timestamp ); create table user_body_permissions ( diff --git a/db/schema_0034-remove-ms_current_timestamp.sql b/db/schema_0034-remove-ms_current_timestamp.sql new file mode 100644 index 000000000..347099877 --- /dev/null +++ b/db/schema_0034-remove-ms_current_timestamp.sql @@ -0,0 +1,16 @@ +begin; + +drop table debugdate; + +ALTER TABLE problem ALTER COLUMN created SET DEFAULT current_timestamp; +ALTER TABLE problem ALTER COLUMN lastupdate SET DEFAULT current_timestamp; +ALTER TABLE comment ALTER COLUMN created SET DEFAULT current_timestamp; +ALTER TABLE token ALTER COLUMN created SET DEFAULT current_timestamp; +ALTER TABLE alert ALTER COLUMN whensubscribed SET DEFAULT current_timestamp; +ALTER TABLE alert_sent ALTER COLUMN whenqueued SET DEFAULT current_timestamp; +ALTER TABLE admin_log ALTER COLUMN whenedited SET DEFAULT current_timestamp; +ALTER TABLE moderation_original_data ALTER COLUMN created SET DEFAULT current_timestamp; + +drop function ms_current_timestamp(); + +commit; diff --git a/perllib/FixMyStreet/App/Controller/Admin.pm b/perllib/FixMyStreet/App/Controller/Admin.pm index 6145a6eb0..44b898743 100644 --- a/perllib/FixMyStreet/App/Controller/Admin.pm +++ b/perllib/FixMyStreet/App/Controller/Admin.pm @@ -359,7 +359,7 @@ sub update_contacts : Private { $contact->deleted( $c->get_param('deleted') ? 1 : 0 ); $contact->non_public( $c->get_param('non_public') ? 1 : 0 ); $contact->note( $c->get_param('note') ); - $contact->whenedited( \'ms_current_timestamp()' ); + $contact->whenedited( \'current_timestamp' ); $contact->editor( $editor ); $contact->endpoint( $c->get_param('endpoint') ); $contact->jurisdiction( $c->get_param('jurisdiction') ); @@ -395,7 +395,7 @@ sub update_contacts : Private { $contacts->update( { confirmed => 1, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'Confirmed', editor => $editor, } @@ -707,7 +707,7 @@ sub report_edit : Path('report_edit') : Args(1) { } elsif ( $c->get_param('mark_sent') ) { $c->forward('check_token'); - $problem->whensent(\'ms_current_timestamp()'); + $problem->whensent(\'current_timestamp'); $problem->update(); $c->stash->{status_message} = '<p><em>' . _('That problem has been marked as sent.') . '</em></p>'; $c->forward( 'log_edit', [ $id, 'problem', 'marked sent' ] ); @@ -787,14 +787,14 @@ sub report_edit : Path('report_edit') : Args(1) { } if ( $problem->is_visible() and $old_state eq 'unconfirmed' ) { - $problem->confirmed( \'ms_current_timestamp()' ); + $problem->confirmed( \'current_timestamp' ); } if ($done) { $problem->discard_changes; } else { - $problem->lastupdate( \'ms_current_timestamp()' ) if $edited || $new_state ne $old_state; + $problem->lastupdate( \'current_timestamp' ) if $edited || $new_state ne $old_state; $problem->update; if ( $new_state ne $old_state ) { @@ -943,10 +943,10 @@ sub update_edit : Path('update_edit') : Args(1) { } if ( $new_state eq 'confirmed' and $old_state eq 'unconfirmed' ) { - $update->confirmed( \'ms_current_timestamp()' ); + $update->confirmed( \'current_timestamp' ); if ( $update->problem_state && $update->created > $update->problem->lastupdate ) { $update->problem->state( $update->problem_state ); - $update->problem->lastupdate( \'ms_current_timestamp()' ); + $update->problem->lastupdate( \'current_timestamp' ); $update->problem->update; } } diff --git a/perllib/FixMyStreet/App/Controller/Questionnaire.pm b/perllib/FixMyStreet/App/Controller/Questionnaire.pm index f9a08e408..035cab3a6 100755 --- a/perllib/FixMyStreet/App/Controller/Questionnaire.pm +++ b/perllib/FixMyStreet/App/Controller/Questionnaire.pm @@ -142,8 +142,8 @@ sub submit_creator_fixed : Private { $questionnaire->ever_reported( $c->stash->{reported} eq 'Yes' ? 1 : 0 ); $questionnaire->old_state( $old_state ); - $questionnaire->whensent( \'ms_current_timestamp()' ); - $questionnaire->whenanswered( \'ms_current_timestamp()' ); + $questionnaire->whensent( \'current_timestamp' ); + $questionnaire->whenanswered( \'current_timestamp' ); $questionnaire->insert; } @@ -173,13 +173,13 @@ sub submit_standard : Private { # Record state change, if there was one if ( $new_state ) { $problem->state( $new_state ); - $problem->lastupdate( \'ms_current_timestamp()' ); + $problem->lastupdate( \'current_timestamp' ); } # If it's not fixed and they say it's still not been fixed, record time update if ( $c->stash->{been_fixed} eq 'No' && FixMyStreet::DB::Result::Problem->open_states->{$old_state} ) { - $problem->lastupdate( \'ms_current_timestamp()' ); + $problem->lastupdate( \'current_timestamp' ); } # Record questionnaire response @@ -189,7 +189,7 @@ sub submit_standard : Private { my $q = $c->stash->{questionnaire}; $q->update( { - whenanswered => \'ms_current_timestamp()', + whenanswered => \'current_timestamp', ever_reported => $reported, old_state => $old_state, new_state => $c->stash->{been_fixed} eq 'Unknown' ? 'unknown' : ($new_state || $old_state), @@ -210,7 +210,7 @@ sub submit_standard : Private { lang => $c->stash->{lang_code}, cobrand => $c->cobrand->moniker, cobrand_data => '', - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', anonymous => $problem->anonymous, } ); diff --git a/perllib/FixMyStreet/App/Controller/Report.pm b/perllib/FixMyStreet/App/Controller/Report.pm index 7b001ee4c..315a5436c 100644 --- a/perllib/FixMyStreet/App/Controller/Report.pm +++ b/perllib/FixMyStreet/App/Controller/Report.pm @@ -242,7 +242,7 @@ sub delete :Local :Args(1) { return $c->res->redirect($uri) unless $p->bodies->{$body->id}; $p->state('hidden'); - $p->lastupdate( \'ms_current_timestamp()' ); + $p->lastupdate( \'current_timestamp' ); $p->update; $c->model('DB::AdminLog')->create( { diff --git a/perllib/FixMyStreet/App/Controller/Report/Update.pm b/perllib/FixMyStreet/App/Controller/Report/Update.pm index 17aec2113..445723fec 100644 --- a/perllib/FixMyStreet/App/Controller/Report/Update.pm +++ b/perllib/FixMyStreet/App/Controller/Report/Update.pm @@ -80,7 +80,7 @@ sub update_problem : Private { $problem->interest_count( \'interest_count + 1' ); } - $problem->lastupdate( \'ms_current_timestamp()' ); + $problem->lastupdate( \'current_timestamp' ); $problem->update; $c->stash->{problem_id} = $problem->id; diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm index 6b0d516a6..4582843bd 100644 --- a/perllib/FixMyStreet/App/Controller/Reports.pm +++ b/perllib/FixMyStreet/App/Controller/Reports.pm @@ -396,20 +396,20 @@ sub load_and_group_problems : Private { my $not_open = [ FixMyStreet::DB::Result::Problem::fixed_states(), FixMyStreet::DB::Result::Problem::closed_states() ]; if ( $type eq 'new' ) { - $where->{confirmed} = { '>', \"ms_current_timestamp() - INTERVAL '4 week'" }; + $where->{confirmed} = { '>', \"current_timestamp - INTERVAL '4 week'" }; $where->{state} = { 'IN', [ FixMyStreet::DB::Result::Problem::open_states() ] }; } elsif ( $type eq 'older' ) { - $where->{confirmed} = { '<', \"ms_current_timestamp() - INTERVAL '4 week'" }; - $where->{lastupdate} = { '>', \"ms_current_timestamp() - INTERVAL '8 week'" }; + $where->{confirmed} = { '<', \"current_timestamp - INTERVAL '4 week'" }; + $where->{lastupdate} = { '>', \"current_timestamp - INTERVAL '8 week'" }; $where->{state} = { 'IN', [ FixMyStreet::DB::Result::Problem::open_states() ] }; } elsif ( $type eq 'unknown' ) { - $where->{lastupdate} = { '<', \"ms_current_timestamp() - INTERVAL '8 week'" }; + $where->{lastupdate} = { '<', \"current_timestamp - INTERVAL '8 week'" }; $where->{state} = { 'IN', [ FixMyStreet::DB::Result::Problem::open_states() ] }; } elsif ( $type eq 'fixed' ) { - $where->{lastupdate} = { '>', \"ms_current_timestamp() - INTERVAL '8 week'" }; + $where->{lastupdate} = { '>', \"current_timestamp - INTERVAL '8 week'" }; $where->{state} = $not_open; } elsif ( $type eq 'older_fixed' ) { - $where->{lastupdate} = { '<', \"ms_current_timestamp() - INTERVAL '8 week'" }; + $where->{lastupdate} = { '<', \"current_timestamp - INTERVAL '8 week'" }; $where->{state} = $not_open; } diff --git a/perllib/FixMyStreet/App/Controller/Tokens.pm b/perllib/FixMyStreet/App/Controller/Tokens.pm index 21c269502..ba15162ce 100644 --- a/perllib/FixMyStreet/App/Controller/Tokens.pm +++ b/perllib/FixMyStreet/App/Controller/Tokens.pm @@ -58,7 +58,7 @@ sub confirm_problem : Path('/P') { # check that this email or domain are not the cause of abuse. If so hide it. if ( $problem->is_from_abuser ) { $problem->update( - { state => 'hidden', lastupdate => \'ms_current_timestamp()' } ); + { state => 'hidden', lastupdate => \'current_timestamp' } ); $c->stash->{template} = 'tokens/abuse.html'; return; } @@ -68,7 +68,7 @@ sub confirm_problem : Path('/P') { if ($c->cobrand->moniker eq 'zurich') { $problem->set_extra_metadata( email_confirmed => 1 ); $problem->update( { - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', } ); if ( $data->{name} || $data->{password} ) { @@ -90,8 +90,8 @@ sub confirm_problem : Path('/P') { $problem->update( { state => 'confirmed', - confirmed => \'ms_current_timestamp()', - lastupdate => \'ms_current_timestamp()', + confirmed => \'current_timestamp', + lastupdate => \'current_timestamp', } ); diff --git a/perllib/FixMyStreet/Cobrand/Zurich.pm b/perllib/FixMyStreet/Cobrand/Zurich.pm index c64fe1177..9ed65c5f5 100644 --- a/perllib/FixMyStreet/Cobrand/Zurich.pm +++ b/perllib/FixMyStreet/Cobrand/Zurich.pm @@ -549,7 +549,7 @@ sub admin_report_edit { } } - $problem->lastupdate( \'ms_current_timestamp()' ); + $problem->lastupdate( \'current_timestamp' ); $problem->update; $c->stash->{status_message} = diff --git a/perllib/FixMyStreet/DB/Result/AdminLog.pm b/perllib/FixMyStreet/DB/Result/AdminLog.pm index 41bc3100a..d26eba7f5 100644 --- a/perllib/FixMyStreet/DB/Result/AdminLog.pm +++ b/perllib/FixMyStreet/DB/Result/AdminLog.pm @@ -29,7 +29,7 @@ __PACKAGE__->add_columns( "whenedited", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, "user_id", diff --git a/perllib/FixMyStreet/DB/Result/Alert.pm b/perllib/FixMyStreet/DB/Result/Alert.pm index c64cb2ff4..21010064b 100644 --- a/perllib/FixMyStreet/DB/Result/Alert.pm +++ b/perllib/FixMyStreet/DB/Result/Alert.pm @@ -37,7 +37,7 @@ __PACKAGE__->add_columns( "whensubscribed", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, "whendisabled", @@ -106,7 +106,7 @@ sub confirm { sub disable { my $self = shift; - $self->whendisabled( \'ms_current_timestamp()' ); + $self->whendisabled( \'current_timestamp' ); $self->update; return 1; diff --git a/perllib/FixMyStreet/DB/Result/AlertSent.pm b/perllib/FixMyStreet/DB/Result/AlertSent.pm index 422e010a9..154f8b42e 100644 --- a/perllib/FixMyStreet/DB/Result/AlertSent.pm +++ b/perllib/FixMyStreet/DB/Result/AlertSent.pm @@ -18,7 +18,7 @@ __PACKAGE__->add_columns( "whenqueued", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, ); diff --git a/perllib/FixMyStreet/DB/Result/Comment.pm b/perllib/FixMyStreet/DB/Result/Comment.pm index 836462ed5..0c1072a78 100644 --- a/perllib/FixMyStreet/DB/Result/Comment.pm +++ b/perllib/FixMyStreet/DB/Result/Comment.pm @@ -31,7 +31,7 @@ __PACKAGE__->add_columns( "created", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, "confirmed", @@ -144,7 +144,7 @@ sub confirm { my $self = shift; $self->state( 'confirmed' ); - $self->confirmed( \'ms_current_timestamp()' ); + $self->confirmed( \'current_timestamp' ); } =head2 get_photo_params diff --git a/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm b/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm index 08d03f94b..13ecf82cd 100644 --- a/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm +++ b/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm @@ -33,7 +33,7 @@ __PACKAGE__->add_columns( "created", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, ); diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm index bed2f160a..caf748f1e 100644 --- a/perllib/FixMyStreet/DB/Result/Problem.pm +++ b/perllib/FixMyStreet/DB/Result/Problem.pm @@ -53,7 +53,7 @@ __PACKAGE__->add_columns( "created", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, "confirmed", @@ -71,7 +71,7 @@ __PACKAGE__->add_columns( "lastupdate", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, "whensent", @@ -441,7 +441,7 @@ sub confirm { return if $self->state eq 'confirmed'; $self->state('confirmed'); - $self->confirmed( \'ms_current_timestamp()' ); + $self->confirmed( \'current_timestamp' ); return 1; } @@ -735,8 +735,8 @@ sub update_from_open311_service_request { { problem_id => $self->id, state => 'confirmed', - created => $updated || \'ms_current_timestamp()', - confirmed => \'ms_current_timestamp()', + created => $updated || \'current_timestamp', + confirmed => \'current_timestamp', text => $status_notes, mark_open => 0, mark_fixed => 0, @@ -789,7 +789,7 @@ sub update_send_failed { $self->update( { send_fail_count => $self->send_fail_count + 1, - send_fail_timestamp => \'ms_current_timestamp()', + send_fail_timestamp => \'current_timestamp', send_fail_reason => $msg } ); } diff --git a/perllib/FixMyStreet/DB/Result/Token.pm b/perllib/FixMyStreet/DB/Result/Token.pm index 0156af137..90715bdd9 100644 --- a/perllib/FixMyStreet/DB/Result/Token.pm +++ b/perllib/FixMyStreet/DB/Result/Token.pm @@ -20,7 +20,7 @@ __PACKAGE__->add_columns( "created", { data_type => "timestamp", - default_value => \"ms_current_timestamp()", + default_value => \"current_timestamp", is_nullable => 0, }, ); @@ -42,8 +42,7 @@ Representation of mySociety::AuthToken in the DBIx::Class world. The 'data' value is automatically inflated and deflated in the same way that the AuthToken would do it. 'token' is set to a new random value by default and the -'created' timestamp is achieved using the database function -ms_current_timestamp. +'created' timestamp is achieved using the database function current_timestamp. =cut @@ -55,7 +54,7 @@ sub new { my ( $class, $attrs ) = @_; $attrs->{token} ||= mySociety::AuthToken::random_token(); - $attrs->{created} ||= \'ms_current_timestamp()'; + $attrs->{created} ||= \'current_timestamp'; my $new = $class->next::method($attrs); return $new; diff --git a/perllib/FixMyStreet/DB/ResultSet/Alert.pm b/perllib/FixMyStreet/DB/ResultSet/Alert.pm index 5848265f1..bb1c61141 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Alert.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Alert.pm @@ -14,7 +14,7 @@ sub timeline_created { return $rs->search( { - whensubscribed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whensubscribed => { '>=', \"current_timestamp-'7 days'::interval" }, confirmed => 1, %{ $restriction }, }, @@ -29,7 +29,7 @@ sub timeline_disabled { return $rs->search( { - whendisabled => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whendisabled => { '>=', \"current_timestamp-'7 days'::interval" }, %{ $restriction }, }, ); diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm index 0b430008a..d2264139a 100644 --- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm +++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm @@ -30,18 +30,19 @@ sub email_alerts ($) { $item_table.name as item_name, $item_table.anonymous as item_anonymous, $item_table.confirmed as item_confirmed, $head_table.* - from alert - inner join $item_table on alert.parameter::integer = $item_table.${head_table}_id - inner join $head_table on alert.parameter::integer = $head_table.id + from alert, $item_table, $head_table + where alert.parameter::integer = $head_table.id + and $item_table.${head_table}_id = $head_table.id "; } else { $query .= " $item_table.*, $item_table.id as item_id - from alert, $item_table"; + from alert, $item_table + where 1 = 1"; } $query .= " - where alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed - and $item_table.confirmed >= ms_current_timestamp() - '7 days'::interval + and alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed + and $item_table.confirmed >= current_timestamp - '7 days'::interval and (select whenqueued from alert_sent where alert_sent.alert_id = alert.id and alert_sent.parameter::integer = $item_table.id) is null and $item_table.user_id <> alert.user_id and " . $alert_type->item_where . " @@ -184,7 +185,7 @@ sub email_alerts ($) { and problem.user_id = users.id and problem.state in ($states) and problem.non_public = 'f' - and problem.confirmed >= ? and problem.confirmed >= ms_current_timestamp() - '7 days'::interval + and problem.confirmed >= ? and problem.confirmed >= current_timestamp - '7 days'::interval and (select whenqueued from alert_sent where alert_sent.alert_id = ? and alert_sent.parameter::integer = problem.id) is null and users.email <> ? order by confirmed desc"; diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm index 70f8027aa..abdc46868 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Comment.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm @@ -15,7 +15,7 @@ sub timeline { return $rs->search( { state => 'confirmed', - created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + created => { '>=', \"current_timestamp-'7 days'::interval" }, %{ $restriction }, }, { diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 7885c28b3..611c1582e 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -168,9 +168,9 @@ sub timeline { return $rs->search( { -or => { - created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, - confirmed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, - whensent => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + created => { '>=', \"current_timestamp-'7 days'::interval" }, + confirmed => { '>=', \"current_timestamp-'7 days'::interval" }, + whensent => { '>=', \"current_timestamp-'7 days'::interval" }, } }, { @@ -433,8 +433,8 @@ sub send_reports { unless ($result) { $row->update( { - whensent => \'ms_current_timestamp()', - lastupdate => \'ms_current_timestamp()', + whensent => \'current_timestamp', + lastupdate => \'current_timestamp', } ); if ( $cobrand->report_sent_confirmation_email && !$h{anonymous_report}) { _send_report_sent_email( $row, \%h, $nomail, $cobrand ); diff --git a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm index 63a91697d..1b89b2e65 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm @@ -22,7 +22,7 @@ sub send_questionnaires_period { whensent => [ '-and', { '!=', undef }, - { '<', \"ms_current_timestamp() - '$period'::interval" }, + { '<', \"current_timestamp - '$period'::interval" }, ], send_questionnaire => 1, }; @@ -34,7 +34,7 @@ sub send_questionnaires_period { } else { $q_params->{'-or'} = [ '(select max(whensent) from questionnaire where me.id=problem_id)' => undef, - '(select max(whenanswered) from questionnaire where me.id=problem_id)' => { '<', \"ms_current_timestamp() - '$period'::interval" } + '(select max(whenanswered) from questionnaire where me.id=problem_id)' => { '<', \"current_timestamp - '$period'::interval" } ]; } @@ -70,7 +70,7 @@ sub send_questionnaires_period { my $questionnaire = FixMyStreet::App->model('DB::Questionnaire')->create( { problem_id => $row->id, - whensent => \'ms_current_timestamp()', + whensent => \'current_timestamp', } ); # We won't send another questionnaire unless they ask for it (or it was @@ -120,8 +120,8 @@ sub timeline { return $rs->search( { -or => { - whenanswered => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, - 'me.whensent' => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whenanswered => { '>=', \"current_timestamp-'7 days'::interval" }, + 'me.whensent' => { '>=', \"current_timestamp-'7 days'::interval" }, }, %{ $restriction }, }, diff --git a/perllib/Open311/PopulateServiceList.pm b/perllib/Open311/PopulateServiceList.pm index e4f0b8357..5f45382e2 100644 --- a/perllib/Open311/PopulateServiceList.pm +++ b/perllib/Open311/PopulateServiceList.pm @@ -143,7 +143,7 @@ sub _handle_existing_contact { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'automatically undeleted by script', } ); @@ -181,7 +181,7 @@ sub _create_contact { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'created automatically by script', } ); @@ -309,7 +309,7 @@ sub _delete_contacts_not_in_service_list { { deleted => 1, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'automatically marked as deleted by script' } ); diff --git a/t/app/controller/admin.t b/t/app/controller/admin.t index 498f1cedc..e2dd5df19 100644 --- a/t/app/controller/admin.t +++ b/t/app/controller/admin.t @@ -88,7 +88,7 @@ subtest 'check summary counts' => sub { FixMyStreet::App->model('DB::Problem')->search( { bodies_str => 2489 } )->update( { bodies_str => 1 } ); my $q = FixMyStreet::App->model('DB::Questionnaire')->find_or_new( { problem => $report, }); - $q->whensent( \'ms_current_timestamp()' ); + $q->whensent( \'current_timestamp' ); $q->in_storage ? $q->update : $q->insert; my $alerts = FixMyStreet::App->model('DB::Alert')->search( { confirmed => { '>' => 0 } } ); diff --git a/t/app/controller/contact.t b/t/app/controller/contact.t index cf8a3161b..4ac69a9f8 100644 --- a/t/app/controller/contact.t +++ b/t/app/controller/contact.t @@ -93,7 +93,7 @@ for my $test ( user => $update_user, state => 'confirmed', text => $update_info->{text}, - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', mark_fixed => 'f', anonymous => 'f', } diff --git a/t/app/controller/questionnaire.t b/t/app/controller/questionnaire.t index 5938acc79..ecac54372 100644 --- a/t/app/controller/questionnaire.t +++ b/t/app/controller/questionnaire.t @@ -96,7 +96,7 @@ foreach my $test ( }, { desc => 'User goes to questionnaire URL for an already answered questionnaire', - answered => \'ms_current_timestamp()', + answered => \'current_timestamp', content => 'already answered this questionnaire', }, ) { diff --git a/t/app/controller/report_updates.t b/t/app/controller/report_updates.t index fa6c44292..3e3e4744a 100644 --- a/t/app/controller/report_updates.t +++ b/t/app/controller/report_updates.t @@ -1339,7 +1339,7 @@ foreach my $test ( { problem_id => $report_id, ever_reported => 'y', - whensent => \'ms_current_timestamp()', + whensent => \'current_timestamp', } ); @@ -1481,7 +1481,7 @@ for my $test ( { problem_id => $report_id, ever_reported => 'y', - whensent => \'ms_current_timestamp()', + whensent => \'current_timestamp', } ); diff --git a/t/app/model/alert_type.t b/t/app/model/alert_type.t index 528c4d354..2620dd68c 100644 --- a/t/app/model/alert_type.t +++ b/t/app/model/alert_type.t @@ -87,7 +87,7 @@ my $comment2 = FixMyStreet::App->model('DB::Comment')->find_or_create( } ); -$comment->confirmed( \"ms_current_timestamp() - '3 days'::interval" ); +$comment->confirmed( \"current_timestamp - '3 days'::interval" ); $comment->update; my $alert = FixMyStreet::App->model('DB::Alert')->find_or_create( diff --git a/t/app/model/problem.t b/t/app/model/problem.t index ad82a62a5..0a5f63b05 100644 --- a/t/app/model/problem.t +++ b/t/app/model/problem.t @@ -524,14 +524,14 @@ foreach my $test ( { { whensent => undef } - )->update( { whensent => \'ms_current_timestamp()' } ); + )->update( { whensent => \'current_timestamp' } ); $problem->discard_changes; $problem->update( { bodies_str => $test->{ body }, state => 'confirmed', - confirmed => \'ms_current_timestamp()', - whensent => $test->{ unset_whendef } ? undef : \'ms_current_timestamp()', + confirmed => \'current_timestamp', + whensent => $test->{ unset_whendef } ? undef : \'current_timestamp', category => $test->{ category } || 'potholes', name => $test->{ name }, cobrand => $test->{ cobrand } || 'fixmystreet', @@ -598,13 +598,13 @@ subtest 'check can set mutiple emails as a single contact' => sub { { whensent => undef } - )->update( { whensent => \'ms_current_timestamp()' } ); + )->update( { whensent => \'current_timestamp' } ); $problem->discard_changes; $problem->update( { bodies_str => $contact->{ body_id }, state => 'confirmed', - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', whensent => undef, category => 'trees', name => 'Test User', @@ -632,13 +632,13 @@ subtest 'check can turn on report sent email alerts' => sub { { whensent => undef } - )->update( { whensent => \'ms_current_timestamp()' } ); + )->update( { whensent => \'current_timestamp' } ); $problem->discard_changes; $problem->update( { bodies_str => $body_ids{2651}, state => 'confirmed', - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', whensent => undef, category => 'potholes', name => 'Test User', @@ -677,14 +677,14 @@ subtest 'check iOS app store test reports not sent' => sub { { whensent => undef } - )->update( { whensent => \'ms_current_timestamp()' } ); + )->update( { whensent => \'current_timestamp' } ); $problem->discard_changes; $problem->update( { bodies_str => $body_ids{2651}, title => 'App store test', state => 'confirmed', - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', whensent => undef, category => 'potholes', send_fail_count => 0, @@ -706,14 +706,14 @@ subtest 'check reports from abuser not sent' => sub { { whensent => undef } - )->update( { whensent => \'ms_current_timestamp()' } ); + )->update( { whensent => \'current_timestamp' } ); $problem->discard_changes; $problem->update( { bodies_str => $body_ids{2651}, title => 'Report', state => 'confirmed', - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', whensent => undef, category => 'potholes', send_fail_count => 0, @@ -728,7 +728,7 @@ subtest 'check reports from abuser not sent' => sub { $problem->update( { state => 'confirmed', - confirmed => \'ms_current_timestamp()', + confirmed => \'current_timestamp', whensent => undef, } ); diff --git a/t/app/model/questionnaire.t b/t/app/model/questionnaire.t index be5b433c1..240d6d050 100644 --- a/t/app/model/questionnaire.t +++ b/t/app/model/questionnaire.t @@ -25,8 +25,8 @@ my $problem = FixMyStreet::App->model('DB::Problem')->create( service => '', cobrand => 'default', cobrand_data => '', - confirmed => \"ms_current_timestamp() - '5 weeks'::interval", - whensent => \"ms_current_timestamp() - '5 weeks'::interval", + confirmed => \"current_timestamp - '5 weeks'::interval", + whensent => \"current_timestamp - '5 weeks'::interval", user => $user, anonymous => 0, } diff --git a/t/app/model/token.t b/t/app/model/token.t index 637477fa3..d72574bb1 100644 --- a/t/app/model/token.t +++ b/t/app/model/token.t @@ -7,17 +7,6 @@ use Test::More; use FixMyStreet; use FixMyStreet::App; -use mySociety::AuthToken; -use mySociety::DBHandle 'dbh'; - -# set things up so that code using mySociety::DBHandle is happy -FixMyStreet->configure_mysociety_dbhandle(); - -# NOTE - remember that you need to explicitly dbh()->commit after making -# database changes with the mySociety::* modules. - -# create a token using DBIC and check we can read it using AuthToken, and vice -# versa my %tests = ( nested_hash => { foo => 'bar', and => [ 'baz', 'bundy' ] }, @@ -27,76 +16,31 @@ my %tests = ( my $token_rs = FixMyStreet::App->model('DB::Token'); -# create using DBIC foreach my $test_data_name ( sort keys %tests ) { my $test_data = $tests{$test_data_name}; - pass "--- testing DBIC create using '$test_data_name'"; + pass "--- testing token creation using '$test_data_name'"; my $dbic_token = $token_rs->create( { scope => 'testing', data => $test_data } ); my $token = $dbic_token->token; ok $token, "stored token '$token'"; - is_deeply $dbic_token->data, $test_data, "data stored correctly using DBIC"; + is_deeply $dbic_token->data, $test_data, "data stored correctly"; - # read back using DBIC + # read back from database is_deeply $token_rs->find( { token => $token, scope => 'testing' } )->data, $test_data, - "data read back correctly with DBIC"; - - # read back using mySociety::AuthToken - is_deeply mySociety::AuthToken::retrieve( 'testing', $token ), - $test_data, "data read back correctly with m::AT"; + "data read back correctly"; # delete token ok $dbic_token->delete, "delete token"; is $token_rs->find( { token => $token, scope => 'testing' } ), undef, - "token gone for DBIC"; - - # read back using mySociety::AuthToken - is mySociety::AuthToken::retrieve( 'testing', $token ), - undef, "token gone with m::AT"; - -} - -# create using m::AT -foreach my $test_data_name ( sort keys %tests ) { - my $test_data = $tests{$test_data_name}; - - pass "--- testing m::AT create using '$test_data_name'"; - - my $token = mySociety::AuthToken::store( 'testing', $test_data ); - dbh->commit(); - ok $token, "stored token '$token'"; - - # read back using DBIC - is_deeply $token_rs->find( { token => $token, scope => 'testing' } )->data, - $test_data, - "data read back correctly with DBIC"; - - # read back using mySociety::AuthToken - is_deeply mySociety::AuthToken::retrieve( 'testing', $token ), - $test_data, "data read back correctly with m::AT"; - - # delete token - ok mySociety::AuthToken::destroy( 'testing', $token ), "destroy token"; - dbh->commit(); - - is $token_rs->find( { token => $token, scope => 'testing' } ), - undef, - "token gone for DBIC"; - - # read back using mySociety::AuthToken - is mySociety::AuthToken::retrieve( 'testing', $token ), - undef, "token gone with m::AT"; - + "token gone"; } - - # Test that the inflation and deflation works as expected { my $token = diff --git a/t/cobrand/fixamingata.t b/t/cobrand/fixamingata.t index 3c818474d..d181d3890 100644 --- a/t/cobrand/fixamingata.t +++ b/t/cobrand/fixamingata.t @@ -71,7 +71,7 @@ my $comment = FixMyStreet::App->model('DB::Comment')->find_or_create({ state => 'confirmed', anonymous => 'f', }); -$comment->confirmed( \"ms_current_timestamp() - '3 days'::interval" ); +$comment->confirmed( \"current_timestamp - '3 days'::interval" ); $comment->update; my $alert = FixMyStreet::App->model('DB::Alert')->find_or_create({ diff --git a/t/open311/populate-service-list.t b/t/open311/populate-service-list.t index 99663030a..1574732fb 100644 --- a/t/open311/populate-service-list.t +++ b/t/open311/populate-service-list.t @@ -55,7 +55,7 @@ subtest 'check non open311 contacts marked as deleted' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -84,7 +84,7 @@ subtest 'check email changed if matching category' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -117,7 +117,7 @@ subtest 'check category name changed if updated' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -151,7 +151,7 @@ subtest 'check conflicting contacts not changed' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -166,7 +166,7 @@ subtest 'check conflicting contacts not changed' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -223,7 +223,7 @@ subtest 'check meta data population' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -403,7 +403,7 @@ for my $test ( confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -478,7 +478,7 @@ subtest 'check attribute ordering' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); @@ -580,7 +580,7 @@ subtest 'check bromely skip code' => sub { confirmed => 1, deleted => 0, editor => $0, - whenedited => \'ms_current_timestamp()', + whenedited => \'current_timestamp', note => 'test contact', } ); |