bug_status view now returns unix epoch; bump db version

diff --git a/Debbugs/DB.pm b/Debbugs/DB.pm
index 6941a5f3a96564b189f796e2db2b26c23e1a1754..5f6bd040d8215978ce87ccd8a07baa3c4dd26a99 100644 (file)
--- 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 e5311b82a9d24c7aa013565ee2a88250f3e80b60..cd1d54a77af8499fa6f02b38662cf3bbe3a2f313 100644 (file)
--- 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 29bd0d6cfa64f6556b86fc261f13d126b125f567..38d645334affe889c31093a9ab326156ab7a844b 100644 (file)
--- 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 0b5b86e143900dd1146b73c35f0e31474f0feef3..f015d452d8727aac43e67b4bce6f5653bfa91993 100644 (file)
--- 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,