[Dbix-class] Cross-schema relationships.

[Aaron Crane]

Rob Kinyon <rob.kinyon at gmail.com> wrote:
> If you're talking about different physical DB instances and there's no
> feature in the DBMS to manage those cross-connections for you, then
> you have to do it by hand.
>
> Suggestions for API improvements welcome.

I've done something similar recently.  My situation involved a
database with, let's say, blog posts, and another database with, say,
account registration data; the blog-post database has per-entry
comments, each of which is associated with an account in the
registration database.  I wanted to be able to say something like

  my $comment = $db->resultset('Comment')->find($comment_id);
  print $comment->user->name;

and have the Comment result class magically do whatever's needed to
get a user row from the foreign database.

That needs to be set up in the result class, of course.  I decided
that treating such things as similar to normal relationships would
work:

  package BlogSchema::Comment;
  use base qw<DBIx::Class>;
  __PACKAGE__->load_components(qw<+DBICx::ExternalRelationships Core>);
  __PACKAGE__->add_columns(user_id => { data_type => 'int' }, ...);
  __PACKAGE__->belongs_to_external(user => 'account', 'User');

That is, I'm saying that there should be a `user` method on this
Comment class, and calling it should look up an object from the `User`
resultset on the database we'll identify as "account".  The
`belongs_to_external` method on the ExternalRelationships component
arranges for the relevant magic to happen.

So what form does that magic take?  I also added some code to the
schema class: a new `externals` field, containing a hashref from
database names (like "account" in this example) to a coderef that
returns a DBIC schema handle for the foreign database in question.
(Some databases might have external foreign keys to multiple
databases.)  The `externals` data could be populated manually after
connecting to the schema class; in my case, it was easier to just
override the connection method.  I also have a `find_external_schema`
method on the schema class, which throws an exception if you try to
invoke an external-relationship method for a database that hasn't been
configured in the current schema instance.

The method created by the belongs_to_external call is then fairly
simple: it calls $self->result_source->schema->find_external_schema
with the external database name supplied, then calls
->resultset($rs_name)->find($self->$column_name) on that.  It also
takes care to cache the external result in the local instance, so that
repeated calls don't cause repeated queries.

There are some obvious and significant flaws with this as a
general-purpose approach, of course:

- Callers need to be aware that the foreign objects don't respect
transactions on the local schema.

- My belongs_to_external method makes unwarranted assumptions about
naming conventions for primary- and foreign-key columns (though those
assumptions were very convenient for me)

- I completely punted on all relationship types other than belongs_to
— I can't do `$account_user->blog_comments`, for example

However, if anyone's interested in working on something similar, let
me know and I'll lend what assistance I can.

-- 
Aaron Crane ** http://aaroncrane.co.uk/