From 004b1a2e2a548c2f35f2bcf64a0cb56355f7b506 Mon Sep 17 00:00:00 2001 From: Don Armstrong Date: Mon, 16 Apr 2018 15:01:03 -0700 Subject: [PATCH] bug_status view now returns unix epoch; bump db version --- Debbugs/DB.pm | 2 +- Debbugs/DB/Result/BugStatus.pm | 18 +++++++++--------- Debbugs/Status.pm | 12 ------------ sql/debbugs_schema.sql | 6 +++--- 4 files changed, 13 insertions(+), 25 deletions(-) diff --git a/Debbugs/DB.pm b/Debbugs/DB.pm index 6941a5f..5f6bd04 100644 --- a/Debbugs/DB.pm +++ b/Debbugs/DB.pm @@ -17,7 +17,7 @@ __PACKAGE__->load_namespaces; # This version must be incremented any time the schema changes so that # DBIx::Class::DeploymentHandler can do its work -our $VERSION=11; +our $VERSION=12; # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BugStatus.pm b/Debbugs/DB/Result/BugStatus.pm index e5311b8..cd1d54a 100644 --- a/Debbugs/DB/Result/BugStatus.pm +++ b/Debbugs/DB/Result/BugStatus.pm @@ -35,7 +35,7 @@ __PACKAGE__->table_class("DBIx::Class::ResultSource::View"); =cut __PACKAGE__->table("bug_status"); -__PACKAGE__->result_source_instance->view_definition(" SELECT b.id,\n b.id AS bug_num,\n string_agg(t.tag, ','::text) AS tags,\n b.subject,\n ( SELECT s.severity\n FROM severity s\n WHERE (s.id = b.severity)) AS severity,\n ( SELECT string_agg(package.package, ','::text ORDER BY package.package) AS string_agg\n FROM ( SELECT bp.pkg AS package\n FROM (bug_binpackage bbp\n JOIN bin_pkg bp ON ((bbp.bin_pkg = bp.id)))\n WHERE (bbp.bug = b.id)\n UNION\n SELECT concat('src:', sp.pkg) AS package\n FROM (bug_srcpackage bsp\n JOIN src_pkg sp ON ((bsp.src_pkg = sp.id)))\n WHERE (bsp.bug = b.id)) package) AS package,\n b.submitter_full AS originator,\n b.log_modified,\n b.creation AS date,\n b.last_modified,\n b.done_full AS done,\n string_agg((bb.blocks)::text, ' '::text ORDER BY bb.blocks) AS blocks,\n string_agg((bbb.bug)::text, ' '::text ORDER BY bbb.bug) AS blockedby,\n ( SELECT string_agg((bug.bug)::text, ' '::text ORDER BY bug.bug) AS string_agg\n FROM ( SELECT bm.merged AS bug\n FROM bug_merged bm\n WHERE (bm.bug = b.id)\n UNION\n SELECT bm.bug\n FROM bug_merged bm\n WHERE (bm.merged = b.id)) bug) AS mergedwith,\n ( SELECT string_agg(bv.ver_string, ' '::text) AS string_agg\n FROM bug_ver bv\n WHERE ((bv.bug = b.id) AND (bv.found IS TRUE))) AS found_versions,\n ( SELECT string_agg(bv.ver_string, ' '::text) AS string_agg\n FROM bug_ver bv\n WHERE ((bv.bug = b.id) AND (bv.found IS FALSE))) AS fixed_versions\n FROM ((((bug b\n LEFT JOIN bug_tag bt ON ((bt.bug = b.id)))\n LEFT JOIN tag t ON ((bt.tag = t.id)))\n LEFT JOIN bug_blocks bb ON ((bb.bug = b.id)))\n LEFT JOIN bug_blocks bbb ON ((bbb.blocks = b.id)))\n GROUP BY b.id"); +__PACKAGE__->result_source_instance->view_definition(" SELECT b.id,\n b.id AS bug_num,\n string_agg(t.tag, ','::text) AS tags,\n b.subject,\n ( SELECT s.severity\n FROM severity s\n WHERE (s.id = b.severity)) AS severity,\n ( SELECT string_agg(package.package, ','::text ORDER BY package.package) AS string_agg\n FROM ( SELECT bp.pkg AS package\n FROM (bug_binpackage bbp\n JOIN bin_pkg bp ON ((bbp.bin_pkg = bp.id)))\n WHERE (bbp.bug = b.id)\n UNION\n SELECT concat('src:', sp.pkg) AS package\n FROM (bug_srcpackage bsp\n JOIN src_pkg sp ON ((bsp.src_pkg = sp.id)))\n WHERE (bsp.bug = b.id)) package) AS package,\n b.submitter_full AS originator,\n date_part('epoch'::text, b.log_modified) AS log_modified,\n date_part('epoch'::text, b.creation) AS date,\n date_part('epoch'::text, b.last_modified) AS last_modified,\n b.done_full AS done,\n string_agg((bb.blocks)::text, ' '::text ORDER BY bb.blocks) AS blocks,\n string_agg((bbb.bug)::text, ' '::text ORDER BY bbb.bug) AS blockedby,\n ( SELECT string_agg((bug.bug)::text, ' '::text ORDER BY bug.bug) AS string_agg\n FROM ( SELECT bm.merged AS bug\n FROM bug_merged bm\n WHERE (bm.bug = b.id)\n UNION\n SELECT bm.bug\n FROM bug_merged bm\n WHERE (bm.merged = b.id)) bug) AS mergedwith,\n ( SELECT string_agg(bv.ver_string, ' '::text) AS string_agg\n FROM bug_ver bv\n WHERE ((bv.bug = b.id) AND (bv.found IS TRUE))) AS found_versions,\n ( SELECT string_agg(bv.ver_string, ' '::text) AS string_agg\n FROM bug_ver bv\n WHERE ((bv.bug = b.id) AND (bv.found IS FALSE))) AS fixed_versions\n FROM ((((bug b\n LEFT JOIN bug_tag bt ON ((bt.bug = b.id)))\n LEFT JOIN tag t ON ((bt.tag = t.id)))\n LEFT JOIN bug_blocks bb ON ((bb.bug = b.id)))\n LEFT JOIN bug_blocks bbb ON ((bbb.blocks = b.id)))\n GROUP BY b.id"); =head1 ACCESSORS @@ -76,17 +76,17 @@ __PACKAGE__->result_source_instance->view_definition(" SELECT b.id,\n b.id AS =head2 log_modified - data_type: 'timestamp with time zone' + data_type: 'double precision' is_nullable: 1 =head2 date - data_type: 'timestamp with time zone' + data_type: 'double precision' is_nullable: 1 =head2 last_modified - data_type: 'timestamp with time zone' + data_type: 'double precision' is_nullable: 1 =head2 done @@ -137,11 +137,11 @@ __PACKAGE__->add_columns( "originator", { data_type => "text", is_nullable => 1 }, "log_modified", - { data_type => "timestamp with time zone", is_nullable => 1 }, + { data_type => "double precision", is_nullable => 1 }, "date", - { data_type => "timestamp with time zone", is_nullable => 1 }, + { data_type => "double precision", is_nullable => 1 }, "last_modified", - { data_type => "timestamp with time zone", is_nullable => 1 }, + { data_type => "double precision", is_nullable => 1 }, "done", { data_type => "text", is_nullable => 1 }, "blocks", @@ -157,8 +157,8 @@ __PACKAGE__->add_columns( ); -# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-05 14:41:16 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:GTXrQG4rB9ahrd3zgY6rHA +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-16 14:58:16 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:0my1krU3Y/icl24IUojITw # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/Status.pm b/Debbugs/Status.pm index 29bd0d6..38d6453 100644 --- a/Debbugs/Status.pm +++ b/Debbugs/Status.pm @@ -1339,18 +1339,6 @@ sub get_bug_statuses { } $bug_status->{keywords} = $bug_status->{tags}; - $bug_status->{log_modified} = - DateTime::Format::Pg-> - parse_datetime($bug_status->{log_modified})-> - epoch; - $bug_status->{date} = - DateTime::Format::Pg-> - parse_datetime($bug_status->{date})-> - epoch; - $bug_status->{last_modified} = - DateTime::Format::Pg-> - parse_datetime($bug_status->{last_modified})-> - epoch; $bug_status->{location} = $bug_status->{archived}?'archive':'db-h'; for my $field (qw(found_versions fixed_versions found_date fixed_date)) { $bug_status->{$field} = [split ' ', $bug_status->{$field} // '']; diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 0b5b86e..f015d45 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -574,9 +574,9 @@ CREATE VIEW bug_status --(id,bug_num,tags,subject, WHERE bsp.bug=b.id) AS package ) AS package, b.submitter_full AS originator, - b.log_modified AS log_modified, - b.creation AS date, - b.last_modified AS last_modified, + EXTRACT(EPOCH FROM b.log_modified) AS log_modified, + EXTRACT(EPOCH FROM b.creation) AS date, + EXTRACT(EPOCH FROM b.last_modified) AS last_modified, b.done_full AS done, string_agg(bb.blocks::text,' ' ORDER BY bb.blocks) AS blocks, string_agg(bbb.bug::text,' ' ORDER BY bbb.bug) AS blockedby, -- 2.39.2