NAME

DBIx::DataModel::Doc::Misc - Other considerations

DOCUMENTATION CONTEXT

This chapter is part of the DBIx::DataModel manual.

INTERACTION WITH THE DBI LAYER

Transactions and error handling

DBIx::DataModel follows the recommendations of DBI for transactions : it expects the database handle to be opened with RaiseError => 1 and therefore does not check itself for DBI errors ; it is up to the client code to catch the exceptions and deal with errors.

As explained in "Transactions" in DBI, AutoCommit should be set off for databases that support transactions; then atomic operations are enclosed in an eval, followed by either $dbh->commit() (in case of success) or $dbh->rollback() (in case of failure). The do_transaction() method does all this for you automatically.

Calling DBI directly

Maybe you will encounter situations where you need to generate SQL yourself (for example because of clauses specific to your RDBMS), or to interact directly with the DBI layer. This can be encapsulated in additional methods incorporated into the classes generated by DBIx::DataModel. In those methods, you may want to call bless_from_DB() so that the rows returned by DBI may be seen as objects from your client program. Here is an example :

  package MyTable; # switch to namespace 'MyTable'
  
  sub fancyMethod {
  
    # call the DBI API
    my $hash = $dbh->selectall_hashref($fancy_SQL, @key_fields);
  
    # bless results into objects of My::Table
    foreach my $row (values %$hash) {
      $schema->table('MyTable')->bless_from_DB($row);
    }
  
    return $hash;
  }

SELF-REFERENTIAL ASSOCIATIONS

Associations can be self-referential, i.e. describing tree structures :

  $schema->Association([qw/OrganisationalUnit parent   1 ou_id       /],
                       [qw/OrganisationalUnit children * parent_ou_id/],

However, when there are several self-referential associations, we might get into problems : consider

  $schema->Association([qw/Person mother   1 pers_id  /],
                       [qw/Person children * mother_id/])
         ->Association([qw/Person father   1 pers_id  /],
                       [qw/Person children * father_id/]); # BUG: children

This does not work because there are two definitions of the "children" role name in the same class "Person". One solution is to distinguish these roles, and then write by hand a general "children" role :

  $schema->Association([qw/Person mother          1 pers_id  /],
                       [qw/Person mother_children * mother_id/])
         ->Association([qw/Person father          1 pers_id  /],
                       [qw/Person father_children * father_id/]);
  
  package MySchema::Person;
  sub children {
    my $self = shift;
    my $id = $self->{pers_id};
    my $sql = "SELECT * FROM Person WHERE mother_id = $id OR father_id = $id";
    my $children = $self->dbh->selectall_arrayref($sql, {Slice => {}});
    MySchema::Person->bless_from_DB($_) foreach @$children;
    return $children;
  }

Alternatively, since rolenames mother_children and father_children are most probably useless, we might just specify unidirectional associations :

  $schema->Association([qw/Person mother  1 pers_id  /],
                       [qw/Person ---     * mother_id/])
         ->Association([qw/Person father  1 pers_id  /],
                       [qw/Person ---     * father_id/]);

And here is a more sophisticated way to define the "children" method, that will accept additional "where" criteria, like every regular method.

  package MySchema::Person;
  sub children {
    my $self      = shift; # remaining args in @_ will be passed to select()
    my $class     = ref $self;
    my $id        = $self->{pers_id};
    my $statement = $self->schema->table($class)->select(
      -where => [mother_id => $id, 
                 father_id => $id],
      -result_as => 'statement'
    );
    return $statement->select(@_);
  }

This definition forces the join on mother_id or father_id, while leaving open the possibility for the caller to specify additional criteria. For example, all female children of a person (either father or mother) can now be retrieved through

  $person->children(-where => {gender => 'F'})

Observe that mother_id and father_id are inside an arrayref instead of a hashref, so that SQL::Abstract will generate an SQL 'OR'.