[Dbix-class] update value of a column when another column changes

[Alexander Hartmaier]

Hi list,
after wasting a day with several approaches to the problem I'm asking here.

I want to update the column accept_datetime to DateTime->now when the
accept column is changed.
My current code has the problem of getting called by create as well and
thus overwriting the accept_datetime if it is included in the hashref
passed to create which is required for testing and also scripts that
create 'active' requests.

I didn't find a way to distinguish between a change and the initial
setting of the column value.
Because the column is nullable an old value of undefined is no
indication that store_column was called by create either.

The different solutions I tried today where:
1. use DBIx::Class::Helper::Row::OnColumnChange:
This updates the column on update which is too late because I want to be
able to instantiate request objects in memory using new_result and
running my tests against that.

2. don't do it in store_column but in the column accessor instead:
This fails because my form code in the app uses the rel_accept
relationship which, when updated, doesn't call the column accessor but
set_column instead. If I move my code from store_column to set_column my
problem persists.
The Cookbook has an example code for that:
https://metacpan.org/module/RIBASUSHI/DBIx-Class-0.08250/lib/DBIx/Class/Manual/Cookbook.pod#Changing-one-field-whenever-another-changes

#######################

package NAC::Model::DBIC::Table::FW_Request;
use Moose;
use namespace::autoclean;

extends 'NAC::Model::DBIC::Table::Parent::Request';

__PACKAGE__->table('fw_request');

__PACKAGE__->add_columns(
  "id_fw_request",
  {
    data_type => "NUMBER",
    default_value => undef,
    is_auto_increment => 1,
    is_nullable => 0,
    size => 6,
    #sequence => __PACKAGE__->schemaname . 'seq_fw_request',
  },
  "req_desc",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 0,
    size => 128,
  },
  "req_dn",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 0,
    size => 128,
  },
  "req_name",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 0,
    size => 128,
  },
  "req_email",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 1,
    size => 128,
  },
  "dst_kst_ktr",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 0,
    size => 19,
  },
  "req_datetime",
  {
    data_type => "DATETIME",
    is_nullable => 0,
    size => 19,
    timezone => "UTC",
    set_on_create => 1,
  },
  "req_status",
  {
    data_type => "NUMBER",
    default_value => 1,
    is_foreign_key => 1,
    is_nullable => 0,
    size => 6,
  },
  "req_status_datetime",
  { data_type => "DATETIME", default_value => undef, is_nullable => 1,
size => 19, timezone => "UTC" },
  "req_agent",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 1,
    size => 128,
  },
  "accept",
  {
    data_type => "NUMBER",
    default_value => undef,
    is_foreign_key => 1,
    is_nullable => 1,
    size => 1,
  },
  "accept_datetime",
  { data_type => "DATETIME", default_value => undef, is_nullable => 1,
size => 19, timezone => "UTC" },
  "accept_agent",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 1,
    size => 128,
  },
  "agent_comment",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 1,
    size => 1024,
  },
  "req_id",
  {
    data_type => "NUMBER",
    default_value => undef,
    is_auto_increment => 1,
    is_nullable => 0,
    size => 6,
  },
  "req_customer",
  {
    data_type => "NUMBER",
    default_value => undef,
    is_foreign_key => 1,
    is_nullable => 1,
    size => 6,
  },
  "req_comment",
  {
    data_type => "VARCHAR2",
    default_value => undef,
    is_nullable => 1,
    size => 1024,
  },
);

__PACKAGE__->set_primary_key("id_fw_request");

__PACKAGE__->belongs_to(
  "rel_accept",
  "NAC::Model::DBIC::Table::Bool_vals",
  { id_bool_vals => "accept" },
  { join_type => "LEFT" },
);
__PACKAGE__->belongs_to(
  "rel_req_status",
  "NAC::Model::DBIC::Table::Request_Status",
  { id_fw_status => "req_status" },
);
__PACKAGE__->belongs_to(
  "rel_req_customer",
  "NAC::Model::DBIC::Table::Customer",
  { id_customer => "req_customer" },
  # can not be changed until all requests have a customer assigned!
  { join_type => "LEFT" },
);

__PACKAGE__->has_many(
    "entries",
    "NAC::Model::DBIC::Table::FW_Rules",
    'fk_fw_request',
    { order_by => 'rule_index' },
);

__PACKAGE__->has_one(
  "view_version",
  "NAC::Model::DBIC::Table::View_FW_Request_Version",
  undef,
  { proxy => [qw/ version /] },
);
__PACKAGE__->has_one(
  "view_latest",
  "NAC::Model::DBIC::Table::View_FW_Request_Latest",
  {
    "foreign.req_id"                => "self.req_id",
    "foreign.req_datetime_latest"   => "self.req_datetime",
  },
  { proxy => [qw/ req_datetime_latest /] },
);

__PACKAGE__->resultset_class('NAC::Model::DBIC::ResultSet::FW_Request');

__PACKAGE__->meta->make_immutable;

1;

# this is actually defined in the base class
NAC::Model::DBIC::Table::Parent::Request

sub store_column {
    my ( $self, $name, $value ) = @_;

    {
        my $now = DateTime->now( time_zone => 'UTC' );

        # $value might be undefined for column accept
        no warnings 'uninitialized';

        # update the datetime when the accept status changes
        if ( $name eq 'accept' && $value ne $self->accept ) {
            $self->accept_datetime($now);
            if ( $value == NO ) {

                # set request status to rejected and update agent and
datetime
                $self->req_status(REQ_STATE_REJECTED);
            }
            elsif ( !defined $value || $value eq '' ) {
                $self->req_status(REQ_STATE_REQUESTED);
            }
        }
        elsif (
               $name eq 'req_status'
            && $value ne $self->req_status
            && (
                # if request isn't in storage it's a newly created one for
                # which we don't need to check if it's the newest version
                !$self->in_storage

                # only update the newest version to keep the original date
                # when the request was activated or rejected
                || $self->is_newest
            )
            )
        {
            $self->req_status_datetime($now);

            # clear the request agent and datetime in case the request was
            # re-opened
            if ( $value eq REQ_STATE_REQUESTED ) {
                $self->req_agent(undef);
                $self->req_status_datetime(undef);
            }
        }
        elsif (
               $name eq 'req_agent'
            && $value eq $self->accept_agent

            # rejected request do have the same accept and realization agent
            && $self->accept == YES
            )
        {
            $self->throw_exception(
                "the accept and request agent must not be the same person");
        }
    }

    $self->next::method( $name, $value );
}

--
Best regards, Alex



*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*