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?',
);
-# 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
$sqlt_table->add_index(name => 'bin_ver_src_ver_id_arch_idx',
fields => [qw(src_ver arch)]
);
+ $sqlt_table->schema->
+ add_procedure(name => 'bin_ver_to_src_pkg',
+ sql => <<'EOF',
+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;
+EOF
+ );
+ $sqlt_table->schema->
+ add_procedure(name => 'update_bin_pkg_src_pkg_bin_ver',
+ sql => <<'EOF',
+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;
+EOF
+ );
+# $sqlt_table->schema->
+# add_trigger(name => 'bin_ver_update_bin_pkg_src_pkg',
+# perform_action_when => 'after',
+# database_events => [qw(INSERT UPDATE DELETE)],
+# on_table => 'bin_ver',
+# action => <<'EOF',
+# FOR EACH ROW EXECUTE PROCEDURE update_bin_pkg_src_pkg_bin_ver();
+# EOF
+# );
}
1;
);
-# 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 {
=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 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
data_type: 'text'
is_nullable: 1
+=head2 affects
+
+ data_type: 'text'
+ is_nullable: 1
+
=head2 message_id
data_type: 'text'
{ 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",
);
-# 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
);
-# 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 {
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gY5LidUaQeuJ5AnN06CfKQ
+sub sqlt_deploy_hook {
+ my ($self, $sqlt_table) = @_;
+ $sqlt_table->schema->
+ add_procedure(name => 'src_ver_to_src_pkg',
+ sql => <<'EOF',
+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;
+EOF
+ );
+}
# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;
# This will eventually need to be fixed before we start using mod_perl
our $version_cache = {};
sub bug_archiveable{
+ state $spec = {bug => {type => SCALAR,
+ regex => qr/^\d+$/,
+ },
+ status => {type => HASHREF,
+ optional => 1,
+ },
+ days_until => {type => BOOLEAN,
+ default => 0,
+ },
+ ignore_time => {type => BOOLEAN,
+ default => 0,
+ },
+ schema => {type => OBJECT,
+ optional => 1,
+ },
+ };
my %param = validate_with(params => \@_,
- spec => {bug => {type => SCALAR,
- regex => qr/^\d+$/,
- },
- status => {type => HASHREF,
- optional => 1,
- },
- days_until => {type => BOOLEAN,
- default => 0,
- },
- ignore_time => {type => BOOLEAN,
- default => 0,
- },
- },
+ spec => $spec,
);
# This is what we return if the bug cannot be archived.
my $cannot_archive = $param{days_until}?-1:0;
my @sourceversions = get_versions(package => $status->{package},
dist => [keys %dists],
source => 1,
+ hash_slice(%param,'schema'),
);
@source_versions{@sourceversions} = (1) x @sourceversions;
# If the bug has not been fixed in the versions actually
fixed => $status->{fixed_versions},
version_cache => $version_cache,
package => $status->{package},
+ hash_slice(%param,'schema'),
)) {
print STDERR "Cannot archive $param{bug} because it's found\n" if $DEBUG;
return $cannot_archive;
dist => [keys %dists],
source => 1,
time => 1,
+ hash_slice(%param,'schema'),
);
for my $version (sort {$time_versions{$b} <=> $time_versions{$a}} keys %time_versions) {
my $buggy = buggy(bug => $param{bug},
fixed => $status->{fixed_versions},
version_cache => $version_cache,
package => $status->{package},
+ hash_slice(%param,'schema'),
);
last if $buggy eq 'found';
$min_fixed_time = min($time_versions{$version},$min_fixed_time);
$statuses{$bug_status->{bug_num}} =
$bug_status;
for my $field (qw(blocks blockedby done),
- qw(tags mergedwith)
+ qw(tags mergedwith affects)
) {
$bug_status->{$field} //='';
}
$status->{package} = '' if not defined $status->{package};
$status->{"package"} =~ s/\s*$//;
- $status->{source} = binary_to_source(binary=>[split /\s*,\s*/, $status->{package}],
- source_only => 1,
- cache => $bin_to_src_cache,
- defined $param{schema}?
- (schema => $param{schema}):(),
- );
-
$status->{"package"} = 'unknown' if ($status->{"package"} eq '');
$status->{"severity"} = 'normal' if (not defined $status->{severity} or $status->{"severity"} eq '');
version_cache => {type => HASHREF,
default => {},
},
+ schema => {type => OBJECT,
+ optional => 1,
+ },
},
);
# Resolve bugginess states (we might be looking at multiple
},
version => {type => SCALAR,
},
+ schema => {type => OBJECT,
+ optional => 1,
+ },
},
);
my @found = @{$param{found}};
my $version_fh = IO::File->new("$config{version_packages_dir}/$srchash/$source", 'r');
if (not defined $version_fh) {
# We only want to warn if it's a package which actually has a maintainer
- my $maints = getmaintainers();
- next if not exists $maints->{$source};
+ my @maint = package_maintainer(source => $source,
+ hash_slice(%param,'schema'),
+ );
+ next unless @maint;
warn "Bug $param{bug}: unable to open $config{version_packages_dir}/$srchash/$source: $!";
next;
}
exit 0;
} elsif ($options{install}) {
$dh->prepare_install;
+ $schema->storage->
+ dbh_do(sub {my ($s,$dbh) = @_;
+ prepare_execute($dbh,<<'SQL');});
+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;
+SQL
$dh->install;
## this is lame, but because the current release of DeploymentHandler does
## not support WHERE or quoted indexes properly (fixed in git), we create
$schema->storage->
dbh_do(sub{my ($s,$dbh) = @_;
prepare_execute($dbh,<<SQL);
+
CREATE UNIQUE INDEX bug_status_cache_bug_col_suite_col_arch_idx ON
bug_status_cache(bug,COALESCE(suite,0),COALESCE(arch,0));
CREATE UNIQUE INDEX bug_status_cache_bug_suite_idx ON
-#!/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.
use Debbugs::CGI qw(htmlize_packagelinks html_escape cgi_parameters munge_url :cache);
use Debbugs::Versions;
use Debbugs::Versions::Dpkg;
-use Debbugs::Packages qw(get_versions makesourceversions);
+use Debbugs::Packages qw(get_versions make_source_versions);
use HTML::Entities qw(encode_entities);
use File::Temp qw(tempdir);
use IO::File;
# then figure out which are affected.
# turn found and fixed into full versions
-@{$cgi_var{found}} = map {makesourceversions($_,undef,@{$cgi_var{found}})} split/\s*,\s*/, $cgi_var{package};
-@{$cgi_var{fixed}} = map {makesourceversions($_,undef,@{$cgi_var{fixed}})} split/\s*,\s*/, $cgi_var{package};
-my @interesting_versions = map {makesourceversions($_,undef,keys %version_to_dist)} split/\s*,\s*/, $cgi_var{package};
+@{$cgi_var{found}} =
+ map {make_source_versions(package => $_,
+ versions => $cgi_var{found})}
+ split/\s*,\s*/, $cgi_var{package};
+@{$cgi_var{fixed}} =
+ map {make_source_versions(package =>$_,
+ versions => $cgi_var{fixed})}
+ split/\s*,\s*/, $cgi_var{package};
+my @interesting_versions =
+ map {make_source_versions(package => $_,
+ versions => [keys %version_to_dist])}
+ split/\s*,\s*/, $cgi_var{package};
# We need to be able to rip out leaves which the versions that do not affect the current versions of unstable/testing
my %sources;
# at this point, we know enough to calculate the etag.
my @versions_files;
-foreach my $source (keys %sources) {
+foreach my $source (sort keys %sources) {
my $srchash = substr $source, 0, 1;
next unless -e "$config{version_packages_dir}/$srchash/$source";
push @versions_files, "$config{version_packages_dir}/$srchash/$source";
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,
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)');
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,
projects / debbugs.git / commitdiff