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=f80106b5aa44579d927790ba9f1a60bde259dd81 Merge branch 'mouseify' --- diff --git a/Debbugs/CGI.pm b/Debbugs/CGI.pm index d371ffe..dffa8ec 100644 --- a/Debbugs/CGI.pm +++ b/Debbugs/CGI.pm @@ -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?', diff --git a/Debbugs/DB/Result/BinPkg.pm b/Debbugs/DB/Result/BinPkg.pm index 22b4e58..0e0c554 100644 --- a/Debbugs/DB/Result/BinPkg.pm +++ b/Debbugs/DB/Result/BinPkg.pm @@ -156,8 +156,8 @@ __PACKAGE__->has_many( ); -# 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 --git a/Debbugs/DB/Result/BinVer.pm b/Debbugs/DB/Result/BinVer.pm index 30be6c2..9eb144b 100644 --- a/Debbugs/DB/Result/BinVer.pm +++ b/Debbugs/DB/Result/BinVer.pm @@ -203,6 +203,62 @@ sub sqlt_deploy_hook { $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; diff --git a/Debbugs/DB/Result/BugBinpackage.pm b/Debbugs/DB/Result/BugBinpackage.pm index 68a1137..2f2a29d 100644 --- a/Debbugs/DB/Result/BugBinpackage.pm +++ b/Debbugs/DB/Result/BugBinpackage.pm @@ -125,8 +125,8 @@ __PACKAGE__->belongs_to( ); -# 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 --git a/Debbugs/DB/Result/BugStatus.pm b/Debbugs/DB/Result/BugStatus.pm index 33fc577..ee3efc8 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 ( 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,6 +69,11 @@ __PACKAGE__->result_source_instance->view_definition(" SELECT b.id,\n b.id AS data_type: 'text' is_nullable: 1 +=head2 affects + + data_type: 'text' + is_nullable: 1 + =head2 message_id data_type: 'text' @@ -139,6 +144,8 @@ __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", @@ -164,8 +171,8 @@ __PACKAGE__->add_columns( ); -# 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 --git a/Debbugs/DB/Result/SrcPkg.pm b/Debbugs/DB/Result/SrcPkg.pm index acd5a0d..26e56a4 100644 --- a/Debbugs/DB/Result/SrcPkg.pm +++ b/Debbugs/DB/Result/SrcPkg.pm @@ -274,8 +274,8 @@ __PACKAGE__->has_many( ); -# 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 --git a/Debbugs/DB/Result/SrcVer.pm b/Debbugs/DB/Result/SrcVer.pm index 79c39f1..4181c1e 100644 --- a/Debbugs/DB/Result/SrcVer.pm +++ b/Debbugs/DB/Result/SrcVer.pm @@ -263,5 +263,23 @@ __PACKAGE__->has_many( # 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; diff --git a/Debbugs/Status.pm b/Debbugs/Status.pm index 45b01a9..f539781 100644 --- a/Debbugs/Status.pm +++ b/Debbugs/Status.pm @@ -965,20 +965,24 @@ Returns undef on failure. # 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; @@ -1063,6 +1067,7 @@ sub bug_archiveable{ 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 @@ -1073,6 +1078,7 @@ sub bug_archiveable{ 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; @@ -1093,6 +1099,7 @@ sub bug_archiveable{ 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}, @@ -1101,6 +1108,7 @@ sub bug_archiveable{ 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); @@ -1337,7 +1345,7 @@ sub get_bug_statuses { $statuses{$bug_status->{bug_num}} = $bug_status; for my $field (qw(blocks blockedby done), - qw(tags mergedwith) + qw(tags mergedwith affects) ) { $bug_status->{$field} //=''; } @@ -1393,13 +1401,6 @@ sub get_bug_statuses { $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 ''); @@ -1644,6 +1645,9 @@ sub max_buggy{ version_cache => {type => HASHREF, default => {}, }, + schema => {type => OBJECT, + optional => 1, + }, }, ); # Resolve bugginess states (we might be looking at multiple @@ -1706,6 +1710,9 @@ sub buggy { }, version => {type => SCALAR, }, + schema => {type => OBJECT, + optional => 1, + }, }, ); my @found = @{$param{found}}; @@ -1740,8 +1747,10 @@ sub buggy { 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; } diff --git a/bin/debbugs-installsql b/bin/debbugs-installsql index 1ecccb4..2cf023d 100755 --- a/bin/debbugs-installsql +++ b/bin/debbugs-installsql @@ -158,6 +158,59 @@ if ($options{current_version}) { 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 @@ -165,6 +218,7 @@ if ($options{current_version}) { $schema->storage-> dbh_do(sub{my ($s,$dbh) = @_; prepare_execute($dbh,< $_, + 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; @@ -170,7 +179,7 @@ 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"; diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 5e37594..3a75bac 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -288,6 +288,74 @@ INSERT INTO column_comments VALUES ('bin_ver','src_ver','Source version (matchin 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, @@ -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)'); @@ -573,6 +642,17 @@ CREATE VIEW bug_status --(id,bug_num,tags,subject, 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,