X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=Debbugs%2FDB%2FResult%2FBinVer.pm;h=9eb144b4d1795ed1abcb098738c5b13700acad4a;hb=37356e95886969d5a5a5609f3e2fc95226e05cc5;hp=0ac574580b2687871a9fe3a21f560b87bf06e601;hpb=4700e08d643a97d24574e84560e606c6d3f04e3c;p=debbugs.git diff --git a/Debbugs/DB/Result/BinVer.pm b/Debbugs/DB/Result/BinVer.pm index 0ac5745..9eb144b 100644 --- a/Debbugs/DB/Result/BinVer.pm +++ b/Debbugs/DB/Result/BinVer.pm @@ -6,7 +6,7 @@ package Debbugs::DB::Result::BinVer; =head1 NAME -Debbugs::DB::Result::BinVer +Debbugs::DB::Result::BinVer - Binary versions =cut @@ -21,11 +21,13 @@ use base 'DBIx::Class::Core'; =item * L +=item * L + =back =cut -__PACKAGE__->load_components("InflateColumn::DateTime"); +__PACKAGE__->load_components("InflateColumn::DateTime", "TimeStamp"); =head1 TABLE: C @@ -42,29 +44,39 @@ __PACKAGE__->table("bin_ver"); is_nullable: 0 sequence: 'bin_ver_id_seq' -=head2 bin_pkg_id +Binary version id + +=head2 bin_pkg data_type: 'integer' is_foreign_key: 1 is_nullable: 0 -=head2 src_ver_id +Binary package id (matches bin_pkg) + +=head2 src_ver data_type: 'integer' is_foreign_key: 1 is_nullable: 0 -=head2 arch_id +Source version (matchines src_ver) + +=head2 arch data_type: 'integer' is_foreign_key: 1 is_nullable: 0 +Architecture id (matches arch) + =head2 ver data_type: 'debversion' is_nullable: 0 +Binary version + =cut __PACKAGE__->add_columns( @@ -75,11 +87,11 @@ __PACKAGE__->add_columns( is_nullable => 0, sequence => "bin_ver_id_seq", }, - "bin_pkg_id", + "bin_pkg", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, - "src_ver_id", + "src_ver", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, - "arch_id", + "arch", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, "ver", { data_type => "debversion", is_nullable => 0 }, @@ -103,9 +115,9 @@ __PACKAGE__->set_primary_key("id"); =over 4 -=item * L +=item * L -=item * L +=item * L =item * L @@ -113,24 +125,7 @@ __PACKAGE__->set_primary_key("id"); =cut -__PACKAGE__->add_unique_constraint( - "bin_ver_bin_pkg_id_arch_idx", - ["bin_pkg_id", "arch_id", "ver"], -); - -=head2 C - -=over 4 - -=item * L - -=item * L - -=back - -=cut - -__PACKAGE__->add_unique_constraint("bin_ver_src_ver_id_arch_idx", ["src_ver_id", "arch_id"]); +__PACKAGE__->add_unique_constraint("bin_ver_bin_pkg_id_arch_idx", ["bin_pkg", "arch", "ver"]); =head1 RELATIONS @@ -145,8 +140,8 @@ Related object: L __PACKAGE__->belongs_to( "arch", "Debbugs::DB::Result::Arch", - { id => "arch_id" }, - { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, + { id => "arch" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, ); =head2 bin_associations @@ -175,8 +170,8 @@ Related object: L __PACKAGE__->belongs_to( "bin_pkg", "Debbugs::DB::Result::BinPkg", - { id => "bin_pkg_id" }, - { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, + { id => "bin_pkg" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, ); =head2 src_ver @@ -190,14 +185,80 @@ Related object: L __PACKAGE__->belongs_to( "src_ver", "Debbugs::DB::Result::SrcVer", - { id => "src_ver_id" }, - { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, + { id => "src_ver" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, ); -# Created by DBIx::Class::Schema::Loader v0.07025 @ 2012-11-25 00:09:07 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ID7wx8HJaYZgpdhHqCq8GQ - +# Created by DBIx::Class::Schema::Loader v0.07045 @ 2016-11-24 09:08:27 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:DzTzZbPkilT8WMhXoZv9xw + + +sub sqlt_deploy_hook { + my ($self, $sqlt_table) = @_; + for my $idx (qw(ver bin_pkg src_ver)) { + $sqlt_table->add_index(name => 'bin_ver_'.$idx.'_id_idx', + fields => [$idx]); + } + $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 +# ); +} -# You can replace this text with custom code or comments, and it will be preserved on regeneration 1;