How do I add relationships at runtime using DBIx::Class and Catalyst?

Question

In the application I am building, users can specify relationships between tables.

Since I only determine this at runtime, I can't specify has_many or belongs_to relationships in the schema modules for startup.

So given two tables; system and place, I would like to add the relationship to join records between them.

I have part of the solution below:

$rs = $c->model('DB::system')->result_source;
$rs->add_relationship('locations','DB::place',{'foreign.fk0' => 'self.id'});

So the column fk0 would be the foreign key mapping to the location primary key id.

I know there must be a re-registration to allow future access to the relationship but I can't figure it out.

Formate

Are you sure your users really change the database schema? That is exactly equivalent to allowing them to edit the source code. You don't necessarily need to model your "user interface" with DBIx::Class. You probably want to develop some intermediate representation that provides the functionality that you want, and keep your code and database schema fixed. – Amused

There is also DBIx::Class::Schema::Loader, if you are tracking a changing database you don't control, or something like that. But keep in mind, database schema changes will change the behavior of your application. If your code is static and your database schema is dynamic, there is almost certainly something wrong. – Amused

I'm exploring the possibility of using my own schema/data-dictionary overlaid on the actual database. I have a 'columns' table and a 'tables' table. These are used to map user selected table and column names and attributes onto an underlying generic tablespace. As such, users can elect to add relationships between tables at run-time. I would like to access this relationship via DBIx. I'n building an intermediate interface to hide the generic schema and need this capability. Thanks, -Joe – Formate

... or maybe I should just use Catalyst::Model::DBI – Formate

Even with DBIC in Catalyst, you can easily access the underlying DBI object ($c->model->storage->dbh). We do this all the time in our Catalyst/DBIC app (in fact, these days we may do it more often than we use DBIC directly--I've come to hate DBIC for most things :) – Whitcomb

@Flimz: don't do that. Use $storage->dbh_do instead. – Amused

@jrockway: dbh_do isn't nearly as flexible as interacting with a DBH object directly. – Whitcomb

@Flimz: But you get the dbh object in the callback. – Amused

@jrockway, It's perfectly reasonable to want to add an arbitrary left join. What if it's a cut down SQL frontend or a data warehousing tool? You have to throw away Perl's only ORM because it lacks to complexity to allow you do dynamically specify an ON clause in a left join? – Blunder

Just because you want to do something doesn't mean you should do it wrong. – Amused

Answer 1

I don't believe you can re-define these relationships after an application is already running. At least not without discarding any existing DBIC objects, and re-creating them all from scratch. At that point, it would be easier to just re-start your application, I suspect.

If you're content defining these things dynamically at compile time, that is possible... we do something similar in one of our applications.

If that would be useful to you, I can provide some sample code.

The DBIx::Class::ResultSet::View module might provide a rough approximation of what you're looking for, by letting you execute arbitrary code, but retrieving the results as DBIx objects.

My general opinion on things like this, is that any abstraction layer (and an ORM is an abstraction layer), is intended to make life easier. When it gets in the way of making your application do what it wants, it's no longer making life easier, and ought to be discarded (for that specific use--not necessarily for every use). For this reason, I would suggest using DBI, as you suggested in one of your comments. I suspect it will make your life much easier in this case.

Whitcomb

+1 for "when it gets in the way..." comment. One of the reasons I still prefer Class::DBI over DBIC - I don't find it gets in the way. – Wampum

Answer 2

I've done this by calling the appropriate methods on the relevant result sources, e.g. $resultset->result_source-><relationship method>. It does work even in an active application.

Lully