From: Don Armstrong Date: Sat, 6 Jul 2019 04:01:33 +0000 (-0700) Subject: Merge branch 'mouseify' X-Git-Url: https://git.donarmstrong.com/?p=debbugs.git;a=commitdiff_plain;h=b1252b6797aa6a79d00a32165fb2fa8fb1bd9318;hp=-c Merge branch 'mouseify' --- b1252b6797aa6a79d00a32165fb2fa8fb1bd9318 diff --combined Debbugs/CGI.pm index 7cc7f41,d371ffe..dffa8ec --- a/Debbugs/CGI.pm +++ b/Debbugs/CGI.pm @@@ -78,6 -78,7 +78,7 @@@ use Debbugs::User qw() use Mail::Address; use POSIX qw(ceil); use Storable qw(dclone); + use Scalar::Util qw(looks_like_number); use List::AllUtils qw(max); use File::stat; @@@ -409,6 -410,8 +410,6 @@@ sub package_links for my $type (qw(maint owner submitter correspondent)) { next unless exists $param{$type}; for my $target (make_list($param{$type})) { - my $addr = getparsedaddrs($target); - $addr = defined $addr?$addr->address:''; if ($has_options) { push @links, (munge_url('pkgreport.cgi?', @@@ -468,23 -471,24 +469,24 @@@ returning htmlized links =cut sub bug_links { + state $spec = {bug => {type => SCALAR|ARRAYREF, + optional => 1, + }, + links_only => {type => BOOLEAN, + default => 0, + }, + class => {type => SCALAR, + default => '', + }, + separator => {type => SCALAR, + default => ', ', + }, + options => {type => HASHREF, + default => {}, + }, + }; my %param = validate_with(params => \@_, - spec => {bug => {type => SCALAR|ARRAYREF, - optional => 1, - }, - links_only => {type => BOOLEAN, - default => 0, - }, - class => {type => SCALAR, - default => '', - }, - separator => {type => SCALAR, - default => ', ', - }, - options => {type => HASHREF, - default => {}, - }, - }, + spec => $spec, ); my %options = %{$param{options}}; @@@ -501,8 -505,11 +503,11 @@@ $_); } make_list($param{bug}) if exists $param{bug}; } else { - push @links, map {('bugreport.cgi?bug='.uri_escape_utf8($_), - $_)} + push @links, + map {my $b = ceil($_); + ('bugreport.cgi?bug='.$b, + $b)} + grep {looks_like_number($_)} make_list($param{bug}) if exists $param{bug}; } my @return; diff --combined Debbugs/DB/Result/BinPkg.pm index eb4002f,22b4e58..0e0c554 --- a/Debbugs/DB/Result/BinPkg.pm +++ b/Debbugs/DB/Result/BinPkg.pm @@@ -156,8 -156,8 +156,8 @@@ __PACKAGE__->has_many ); - # Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-18 16:55:56 - # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Uoaf3KzTvRYIf33q7tBnZw -# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-01-01 17:29:32 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:4DCmbmHyVSpW1I9vmDz3UA ++# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-07-05 20:56:47 ++# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:VH/9QrwjZx0r7FLaEWGYMg # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --combined Debbugs/DB/Result/BugBinpackage.pm index f4a757c,68a1137..2f2a29d --- a/Debbugs/DB/Result/BugBinpackage.pm +++ b/Debbugs/DB/Result/BugBinpackage.pm @@@ -125,8 -125,8 +125,8 @@@ __PACKAGE__->belongs_to ); - # Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-20 10:29:04 - # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:PJ2U+jVEO2uKfwgCYtho1A -# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-01-01 17:29:32 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gEYqmJfiJJtRYFzIYut3Fg ++# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-07-05 21:00:23 ++# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:STaqCap5Dk4AORK6ghGnPg sub sqlt_deploy_hook { diff --combined Debbugs/DB/Result/BugStatus.pm index 0f33b4e,33fc577..ee3efc8 --- a/Debbugs/DB/Result/BugStatus.pm +++ b/Debbugs/DB/Result/BugStatus.pm @@@ -35,7 -35,7 +35,7 @@@ __PACKAGE__->table_class("DBIx::Class:: =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 ( SELECT string_agg(affects.affects, ','::text ORDER BY affects.affects) AS string_agg\n FROM ( SELECT bp.pkg AS affects\n FROM (bug_affects_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 affects\n FROM (bug_affects_srcpackage bsp\n JOIN src_pkg sp ON ((bsp.src_pkg = sp.id)))\n WHERE (bsp.bug = b.id)) affects) AS affects,\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"); -__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 ( SELECT m.msgid\n FROM (message m\n LEFT JOIN bug_message bm ON ((bm.message = m.id)))\n WHERE (bm.bug = b.id)\n ORDER BY m.sent_date\n LIMIT 1) AS message_id,\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"); ++__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 ( SELECT string_agg(affects.affects, ','::text ORDER BY affects.affects) AS string_agg\n FROM ( SELECT bp.pkg AS affects\n FROM (bug_affects_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 affects\n FROM (bug_affects_srcpackage bsp\n JOIN src_pkg sp ON ((bsp.src_pkg = sp.id)))\n WHERE (bsp.bug = b.id)) affects) AS affects,\n ( SELECT m.msgid\n FROM (message m\n LEFT JOIN bug_message bm ON ((bm.message = m.id)))\n WHERE (bm.bug = b.id)\n ORDER BY m.sent_date\n LIMIT 1) AS message_id,\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 @@@ -69,11 -69,11 +69,16 @@@ data_type: 'text' is_nullable: 1 +=head2 affects + + data_type: 'text' + is_nullable: 1 + + =head2 message_id + + data_type: 'text' + is_nullable: 1 + =head2 originator data_type: 'text' @@@ -139,8 -139,8 +144,10 @@@ __PACKAGE__->add_columns { data_type => "text", is_nullable => 1 }, "package", { data_type => "text", is_nullable => 1 }, + "affects", + { data_type => "text", is_nullable => 1 }, + "message_id", + { data_type => "text", is_nullable => 1 }, "originator", { data_type => "text", is_nullable => 1 }, "log_modified", @@@ -164,8 -164,8 +171,8 @@@ ); - # Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-20 10:29:04 - # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:OPfPxXCqSaz2OeYsZqilAg -# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-01-01 17:29:32 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:AyAg7ebutkuSQCQJDyfa4A ++# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-07-05 20:55:00 ++# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:xkAEshcLIPrG/6hoRbSsrw # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --combined Debbugs/DB/Result/SrcPkg.pm index 76b710d,acd5a0d..26e56a4 --- a/Debbugs/DB/Result/SrcPkg.pm +++ b/Debbugs/DB/Result/SrcPkg.pm @@@ -274,8 -274,8 +274,8 @@@ __PACKAGE__->has_many ); - # Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-18 16:55:56 - # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:fMMA9wnkPIdT5eiUIkLxqg -# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-01-01 17:29:32 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:yiCFtO7j409+ZPBejeWitQ ++# Created by DBIx::Class::Schema::Loader v0.07049 @ 2019-07-05 20:56:47 ++# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:G2uhLQ7coWRoAHFiDkF5cQ sub sqlt_deploy_hook { diff --combined cgi/pkgreport.cgi index 8733d9a,f1d6cec..3855928 --- a/cgi/pkgreport.cgi +++ b/cgi/pkgreport.cgi @@@ -1,4 -1,4 +1,4 @@@ -#!/usr/bin/perl -wT +#!/usr/bin/perl -T # This script is part of debbugs, and is released # under the terms of the GPL version 2, or any later # version at your option. @@@ -37,6 -37,7 +37,7 @@@ BEGIN # if the first directory in @INC is not an absolute directory, assume that # someone has overridden us via -I. if ($INC[0] !~ /^\//) { + undef $debbugs_dir; } } use if defined $debbugs_dir, lib => $debbugs_dir; @@@ -53,6 -54,7 +54,7 @@@ use Debbugs::Common qw(getparsedaddrs m use Debbugs::Bugs qw(get_bugs bug_filter newest_bug); use Debbugs::Packages qw(source_to_binary binary_to_source get_versions); + use Debbugs::Collection::Bug; use Debbugs::Status qw(splitpackages); @@@ -292,8 -294,10 +294,10 @@@ my %bugusertags my %ut; my %seen_users; + my @users; for my $user (map {split /[\s*,\s*]+/} make_list($param{users}||[])) { next unless length($user); + push @users, $user; add_user($user,\%ut,\%bugusertags,\%seen_users,\%cats,\%hidden); } @@@ -304,7 -308,8 +308,8 @@@ if (defined $param{usertag}) Debbugs::User::read_usertags(\%select_ut, $u); unless (defined $t && $t ne "") { $t = join(",", keys(%select_ut)); - } + } + push @users,$u; add_user($u,\%ut,\%bugusertags,\%seen_users,\%cats,\%hidden); push @{$param{tag}}, split /,/, $t; } @@@ -355,6 -360,8 +360,8 @@@ if (defined $config{usertag_package_dom } for my $package (@possible_packages) { next unless defined $package and length $package; + push @users, + $package.'@'.$config{usertag_package_domain}; add_user($package.'@'.$config{usertag_package_domain}, \%ut,\%bugusertags,\%seen_users,\%cats,\%hidden); } @@@ -464,7 -471,17 +471,17 @@@ my %bugs @bugs{@bugs} = @bugs; @bugs = keys %bugs; - my $result = pkg_htmlizebugs(bugs => \@bugs, + my $bugs = Debbugs::Collection::Bug-> + new(bugs => \@bugs, + @schema_arg, + users => [map {my $u = Debbugs::User->new($_); + $u->has_bug_tags()?($u):() + } @users], + ); + + $bugs->load_related_packages_and_versions(); + + my $result = pkg_htmlizebugs(bugs => $bugs, names => \@names, title => \@title, order => \@order, diff --combined sql/debbugs_schema.sql index 3eaeee6,5e37594..3a75bac --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@@ -288,74 -288,6 +288,74 @@@ INSERT INTO column_comments VALUES ('bi INSERT INTO column_comments VALUES ('bin_ver','arch','Architecture id (matches arch)'); INSERT INTO column_comments VALUES ('bin_ver','ver','Binary version'); +CREATE TABLE bin_pkg_src_pkg ( + bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE, + src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE +); +CREATE UNIQUE INDEX bin_pkg_src_pkg_bin_pkg_src_pkg ON bin_pkg_src_pkg (bin_pkg,src_pkg); +CREATE UNIQUE INDEX bin_pkg_src_pkg_src_pkg_bin_pkg ON bin_pkg_src_pkg (src_pkg,bin_pkg); + + +INSERT INTO table_comments VALUES ('bin_pkg_src_pkg', + 'Binary package <-> source package mapping sumpmary table'); +INSERT INTO column_comments VALUES ('bin_pkg_src_pkg','bin_pkg','Binary package id (matches bin_pkg)'); +INSERT INTO column_comments VALUES ('bin_pkg_src_pkg','src_pkg','Source package id (matches src_pkg)'); + +CREATE OR REPLACE FUNCTION bin_ver_to_src_pkg(bin_ver INT) RETURNS INT + AS $src_pkg_from_bin_ver$ + DECLARE + src_pkg int; + BEGIN + SELECT sv.src_pkg INTO STRICT src_pkg + FROM bin_ver bv JOIN src_ver sv ON bv.src_ver=sv.id + WHERE bv.id=bin_ver; + RETURN src_pkg; + END + $src_pkg_from_bin_ver$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION src_ver_to_src_pkg(src_ver INT) RETURNS INT + AS $src_ver_to_src_pkg$ + DECLARE + src_pkg int; + BEGIN + SELECT sv.src_pkg INTO STRICT src_pkg + FROM src_ver sv WHERE sv.id=src_ver; + RETURN src_pkg; + END + $src_ver_to_src_pkg$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION update_bin_pkg_src_pkg_bin_ver () RETURNS TRIGGER + AS $update_bin_pkg_src_pkg_bin_ver$ + DECLARE + src_ver_rows integer; + BEGIN + IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE' ) THEN + -- if there is still a bin_ver with this src_pkg, then do nothing + PERFORM * FROM bin_ver bv JOIN src_ver sv ON bv.src_ver = sv.id + WHERE sv.id = OLD.src_ver LIMIT 2; + GET DIAGNOSTICS src_ver_rows = ROW_COUNT; + IF (src_ver_rows <= 1) THEN + DELETE FROM bin_pkg_src_pkg + WHERE bin_pkg=OLD.bin_pkg AND + src_pkg=src_ver_to_src_pkg(OLD.src_ver); + END IF; + END IF; + IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN + BEGIN + INSERT INTO bin_pkg_src_pkg (bin_pkg,src_pkg) + VALUES (NEW.bin_pkg,src_ver_to_src_pkg(NEW.src_ver)) + ON CONFLICT (bin_pkg,src_pkg) DO NOTHING; + END; + END IF; + RETURN NULL; + END + $update_bin_pkg_src_pkg_bin_ver$ LANGUAGE plpgsql; + +CREATE TRIGGER bin_ver_update_bin_pkg_src_pkg +AFTER INSERT OR UPDATE OR DELETE ON bin_ver +FOR EACH ROW EXECUTE PROCEDURE update_bin_pkg_src_pkg_bin_ver(); + + CREATE TABLE tag ( id SERIAL PRIMARY KEY, tag TEXT NOT NULL UNIQUE, @@@ -406,6 -338,6 +406,7 @@@ CREATE TABLE bug_binpackage bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE ); CREATE UNIQUE INDEX bug_binpackage_id_pkg ON bug_binpackage(bug,bin_pkg); ++CREATE UNIQUE INDEX bug_binpackage_bin_pkg_bug_idx ON bug_binpackage(bin_pkg,bug); INSERT INTO table_comments VALUES ('bug_binpackage','Bug <-> binary package mapping'); INSERT INTO column_comments VALUES ('bug_binpackage','bug','Bug id (matches bug)'); INSERT INTO column_comments VALUES ('bug_binpackage','bin_pkg','Binary package id (matches bin_pkg)'); @@@ -641,17 -573,8 +642,19 @@@ CREATE VIEW bug_status --(id,bug_num,ta JOIN src_pkg sp ON bsp.src_pkg=sp.id WHERE bsp.bug=b.id) AS package ) AS package, + (SELECT string_agg(affects.affects,',' ORDER BY affects) + FROM (SELECT bp.pkg AS affects + FROM bug_affects_binpackage bbp + JOIN bin_pkg bp ON bbp.bin_pkg=bp.id + WHERE bbp.bug=b.id + UNION + SELECT CONCAT('src:',sp.pkg) AS affects + FROM bug_affects_srcpackage bsp + JOIN src_pkg sp ON bsp.src_pkg=sp.id + WHERE bsp.bug=b.id) AS affects + ) AS affects, + (SELECT msgid FROM message m LEFT JOIN bug_message bm ON bm.message=m.id + WHERE bm.bug=b.id ORDER BY m.sent_date ASC limit 1) AS message_id, b.submitter_full AS originator, EXTRACT(EPOCH FROM b.log_modified) AS log_modified, EXTRACT(EPOCH FROM b.creation) AS date,