From 18d62e8d681fb4d9e9e8a233f3994c30923aafce Mon Sep 17 00:00:00 2001 From: Don Armstrong Date: Fri, 5 Jul 2019 20:14:03 -0700 Subject: [PATCH] add binpkg/srcpkg relation table --- Debbugs/DB/Result/BinPkg.pm | 19 ++- Debbugs/DB/Result/BinPkgSrcPkg.pm | 198 +++++++++++++++++++++++++++++ Debbugs/DB/Result/BugBinpackage.pm | 18 ++- Debbugs/DB/Result/SrcPkg.pm | 19 ++- 4 files changed, 248 insertions(+), 6 deletions(-) create mode 100644 Debbugs/DB/Result/BinPkgSrcPkg.pm diff --git a/Debbugs/DB/Result/BinPkg.pm b/Debbugs/DB/Result/BinPkg.pm index 5747fe0..22b4e58 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.07049 @ 2019-01-01 17:29:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:4DCmbmHyVSpW1I9vmDz3UA # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BinPkgSrcPkg.pm b/Debbugs/DB/Result/BinPkgSrcPkg.pm new file mode 100644 index 0000000..4836b05 --- /dev/null +++ b/Debbugs/DB/Result/BinPkgSrcPkg.pm @@ -0,0 +1,198 @@ +use utf8; +package Debbugs::DB::Result::BinPkgSrcPkg; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Debbugs::DB::Result::BinPkgSrcPkg - Binary package <-> source package mapping sumpmary table + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 COMPONENTS LOADED + +=over 4 + +=item * L + +=item * L + +=back + +=cut + +__PACKAGE__->load_components("InflateColumn::DateTime", "TimeStamp"); + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("bin_pkg_src_pkg"); + +=head1 ACCESSORS + +=head2 bin_pkg + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +Binary package id (matches bin_pkg) + +=head2 src_pkg + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +Source package id (matches src_pkg) + +=cut + +__PACKAGE__->add_columns( + "bin_pkg", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "src_pkg", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, +); + +=head1 UNIQUE CONSTRAINTS + +=head2 C + +=over 4 + +=item * L + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint("bin_pkg_src_pkg_bin_pkg_src_pkg", ["bin_pkg", "src_pkg"]); + +=head2 C + +=over 4 + +=item * L + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint("bin_pkg_src_pkg_src_pkg_bin_pkg", ["src_pkg", "bin_pkg"]); + +=head1 RELATIONS + +=head2 bin_pkg + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "bin_pkg", + "Debbugs::DB::Result::BinPkg", + { id => "bin_pkg" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 src_pkg + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "src_pkg", + "Debbugs::DB::Result::SrcPkg", + { id => "src_pkg" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07048 @ 2018-04-18 16:55:56 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:O/v5RtjJF9SgxXEy76U/xw + +sub sqlt_deploy_hook { + my ($self, $sqlt_table) = @_; + $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 => '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 + ); + $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 + ); + +} + +1; diff --git a/Debbugs/DB/Result/BugBinpackage.pm b/Debbugs/DB/Result/BugBinpackage.pm index d572994..68a1137 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.07049 @ 2019-01-01 17:29:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gEYqmJfiJJtRYFzIYut3Fg sub sqlt_deploy_hook { diff --git a/Debbugs/DB/Result/SrcPkg.pm b/Debbugs/DB/Result/SrcPkg.pm index fc6b2e1..acd5a0d 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.07049 @ 2019-01-01 17:29:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:yiCFtO7j409+ZPBejeWitQ sub sqlt_deploy_hook { -- 2.39.2