Add bug_status view and corresponding DBIx::Class class

diff --git a/Debbugs/DB.pm b/Debbugs/DB.pm
index 4e6d3cba7477fdf20cef72ae7f4d45796338cc9b..856c6e773d1fe993dde053a1e25815c692e79847 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=8;
+our $VERSION=9;
 
 # 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
new file mode 100644 (file)
index 0000000..df7fc29
--- /dev/null
+++ b/Debbugs/DB/Result/BugStatus.pm
@@ -0,0 +1,158 @@
+use utf8;
+package Debbugs::DB::Result::BugStatus;
+
+# Created by DBIx::Class::Schema::Loader
+# DO NOT MODIFY THE FIRST PART OF THIS FILE
+
+=head1 NAME
+
+Debbugs::DB::Result::BugStatus
+
+=cut
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+=head1 COMPONENTS LOADED
+
+=over 4
+
+=item * L<DBIx::Class::InflateColumn::DateTime>
+
+=item * L<DBIx::Class::TimeStamp>
+
+=back
+
+=cut
+
+__PACKAGE__->load_components("InflateColumn::DateTime", "TimeStamp");
+__PACKAGE__->table_class("DBIx::Class::ResultSource::View");
+
+=head1 TABLE: C<bug_status>
+
+=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    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 b.id AS bug\n                UNION\n                 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 merged,\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 TRUE))) 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
+
+=head2 id
+
+  data_type: 'integer'
+  is_nullable: 1
+
+=head2 bug_num
+
+  data_type: 'integer'
+  is_nullable: 1
+
+=head2 tags
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 subject
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 severity
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 package
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 originator
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 log_modified
+
+  data_type: 'timestamp with time zone'
+  is_nullable: 1
+
+=head2 date
+
+  data_type: 'timestamp with time zone'
+  is_nullable: 1
+
+=head2 last_modified
+
+  data_type: 'timestamp with time zone'
+  is_nullable: 1
+
+=head2 blocks
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 blockedby
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 merged
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 found_versions
+
+  data_type: 'text'
+  is_nullable: 1
+
+=head2 fixed_versions
+
+  data_type: 'text'
+  is_nullable: 1
+
+=cut
+
+__PACKAGE__->add_columns(
+  "id",
+  { data_type => "integer", is_nullable => 1 },
+  "bug_num",
+  { data_type => "integer", is_nullable => 1 },
+  "tags",
+  { data_type => "text", is_nullable => 1 },
+  "subject",
+  { data_type => "text", is_nullable => 1 },
+  "severity",
+  { data_type => "text", is_nullable => 1 },
+  "package",
+  { data_type => "text", is_nullable => 1 },
+  "originator",
+  { data_type => "text", is_nullable => 1 },
+  "log_modified",
+  { data_type => "timestamp with time zone", is_nullable => 1 },
+  "date",
+  { data_type => "timestamp with time zone", is_nullable => 1 },
+  "last_modified",
+  { data_type => "timestamp with time zone", is_nullable => 1 },
+  "blocks",
+  { data_type => "text", is_nullable => 1 },
+  "blockedby",
+  { data_type => "text", is_nullable => 1 },
+  "merged",
+  { data_type => "text", is_nullable => 1 },
+  "found_versions",
+  { data_type => "text", is_nullable => 1 },
+  "fixed_versions",
+  { data_type => "text", is_nullable => 1 },
+);
+
+
+# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-03-30 14:33:57
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Ao8wSsd8rmagJY0KoncYRg
+
+
+# You can replace this text with custom code or comments, and it will be preserved on regeneration
+1;

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index f2a420d3a2f027b733980171894935af9878f8a0..fc480d4bfa00a40d7d3f735cb1e8af31143c4e7b 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -2,6 +2,7 @@
 DROP TABLE bug_status_cache CASCADE;
 DROP VIEW bug_package CASCADE;
 DROP VIEW binary_versions CASCADE;
+DROP VIEW bug_status CASCADE;
 DROP TABLE bug_tag CASCADE;
 DROP TABLE tag CASCADE;
 DROP TABLE bug_user_tag CASCADE;
@@ -546,3 +547,46 @@ INSERT INTO column_comments VALUES ('bug_message','message_number','Message numb
 INSERT INTO column_comments VALUES ('bug_message','bug_log_offset','Byte offset in the bug log');
 INSERT INTO column_comments VALUES ('bug_message','offset_valid','Time offset was valid');
 
+CREATE VIEW bug_status --(id,bug_num,tags,subject,
+--        severity,package,originator,log_modified,date,
+--        last_modified, blocks, blockedby, mergedwith,
+--        fixed_versions,found_versions)
+       AS
+       SELECT b.id AS id,
+       b.id AS bug_num,
+       string_agg(t.tag,',') AS tags,
+       b.subject AS subject,
+       (SELECT s.severity FROM severity s WHERE s.id=b.severity) AS severity,
+       (SELECT string_agg(package.package,',' ORDER BY package)
+        FROM (SELECT bp.pkg AS package
+                    FROM bug_binpackage bbp
+                     JOIN bin_pkg bp ON bbp.bin_pkg=bp.id
+                     WHERE bbp.bug=b.id
+             UNION
+             SELECT CONCAT('src:',sp.pkg) AS package
+                    FROM bug_srcpackage bsp
+                    JOIN src_pkg sp ON bsp.src_pkg=sp.id
+                     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,
+       string_agg(bb.blocks::text,' ' ORDER BY bb.blocks) AS blocks,
+       string_agg(bbb.bug::text,' ' ORDER BY bbb.bug) AS blockedby,
+       (SELECT string_agg(bug.bug::text,' ' ORDER BY bug.bug)
+        FROM (SELECT b.id AS bug
+                         UNION
+                  SELECT bm.merged AS bug FROM bug_merged bm WHERE bm.bug=b.id
+                         UNION
+                  SELECT bm.bug AS bug FROM bug_merged bm WHERE bm.merged=b.id) AS bug) AS merged,
+       (SELECT string_agg(bv.ver_string,' ') FROM bug_ver bv WHERE bv.bug=b.id AND bv.found IS TRUE)
+               AS found_versions,
+       (SELECT string_agg(bv.ver_string,' ') FROM bug_ver bv WHERE bv.bug=b.id AND bv.found IS TRUE)
+               AS fixed_versions
+       FROM bug b
+       LEFT JOIN bug_tag bt ON bt.bug=b.id
+       LEFT JOIN tag t ON bt.tag=t.id
+       LEFT JOIN bug_blocks bb ON bb.bug=b.id
+       LEFT JOIN bug_blocks bbb ON bbb.blocks=b.id
+       GROUP BY b.id;