From 37356e95886969d5a5a5609f3e2fc95226e05cc5 Mon Sep 17 00:00:00 2001 From: Don Armstrong Date: Sun, 19 Aug 2018 13:33:42 -0700 Subject: [PATCH] add bin_pkg to src_pkg convenience linking table with triggers --- Debbugs/DB/Result/BinPkg.pm | 19 ++++++- Debbugs/DB/Result/BinVer.pm | 56 +++++++++++++++++++++ Debbugs/DB/Result/BugBinpackage.pm | 18 ++++++- Debbugs/DB/Result/BugStatus.pm | 13 +++-- Debbugs/DB/Result/SrcPkg.pm | 19 ++++++- Debbugs/DB/Result/SrcVer.pm | 18 +++++++ sql/debbugs_schema.sql | 79 ++++++++++++++++++++++++++++++ 7 files changed, 213 insertions(+), 9 deletions(-) diff --git a/Debbugs/DB/Result/BinPkg.pm b/Debbugs/DB/Result/BinPkg.pm index 5747fe0..eb4002f 100644 --- a/Debbugs/DB/Result/BinPkg.pm +++ b/Debbugs/DB/Result/BinPkg.pm @@ -95,6 +95,21 @@ __PACKAGE__->add_unique_constraint("bin_pkg_pkg_key", ["pkg"]); =head1 RELATIONS +=head2 bin_pkg_src_pkgs + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "bin_pkg_src_pkgs", + "Debbugs::DB::Result::BinPkgSrcPkg", + { "foreign.bin_pkg" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 bin_vers Type: has_many @@ -141,8 +156,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07042 @ 2014-11-30 21:56:51 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:My1zg7yJ4SSXL78poec5ag +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-18 16:55:56 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Uoaf3KzTvRYIf33q7tBnZw # 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 d572994..f4a757c 100644 --- a/Debbugs/DB/Result/BugBinpackage.pm +++ b/Debbugs/DB/Result/BugBinpackage.pm @@ -64,6 +64,20 @@ __PACKAGE__->add_columns( =head1 UNIQUE CONSTRAINTS +=head2 C + +=over 4 + +=item * L + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint("bug_binpackage_bin_pkg_bug_idx", ["bin_pkg", "bug"]); + =head2 C =over 4 @@ -111,8 +125,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07046 @ 2017-03-04 10:59:03 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:wL+pwSCfWe/mMQOjziKSeg +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-20 10:29:04 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:PJ2U+jVEO2uKfwgCYtho1A sub sqlt_deploy_hook { diff --git a/Debbugs/DB/Result/BugStatus.pm b/Debbugs/DB/Result/BugStatus.pm index cd1d54a..0f33b4e 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 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 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 originator data_type: 'text' @@ -134,6 +139,8 @@ __PACKAGE__->add_columns( { data_type => "text", is_nullable => 1 }, "package", { data_type => "text", is_nullable => 1 }, + "affects", + { data_type => "text", is_nullable => 1 }, "originator", { data_type => "text", is_nullable => 1 }, "log_modified", @@ -157,8 +164,8 @@ __PACKAGE__->add_columns( ); -# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-16 14:58:16 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:0my1krU3Y/icl24IUojITw +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-20 10:29:04 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:OPfPxXCqSaz2OeYsZqilAg # 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 fc6b2e1..76b710d 100644 --- a/Debbugs/DB/Result/SrcPkg.pm +++ b/Debbugs/DB/Result/SrcPkg.pm @@ -183,6 +183,21 @@ __PACKAGE__->belongs_to( }, ); +=head2 bin_pkg_src_pkgs + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "bin_pkg_src_pkgs", + "Debbugs::DB::Result::BinPkgSrcPkg", + { "foreign.src_pkg" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 bug_affects_srcpackages Type: has_many @@ -259,8 +274,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07046 @ 2017-03-04 10:59:03 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:j8LGu4eUfNUNxM/jkHUG2A +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-18 16:55:56 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:fMMA9wnkPIdT5eiUIkLxqg 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/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 4b15478..3eaeee6 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, @@ -573,6 +641,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, b.submitter_full AS originator, EXTRACT(EPOCH FROM b.log_modified) AS log_modified, EXTRACT(EPOCH FROM b.creation) AS date, -- 2.39.2