[Dbix-class] Weird things you'd like to do with DBIx::Class.

[Matt S Trout]

On Thu, Jul 28, 2005 at 04:47:13PM -0700, Todd Holbrook wrote:
> <snip />
>
> Relationship module for Class::DBI that adds extra fields to a table
> object based on field/value pairs in another table.  This can be handy
> if you have some base information that belongs to most records and
> extra information that belong to a small subset but you want to treat
> them the same when using the object.  It is also handy if you are
> working with data where new fields show up regularly that belong to a
> subset of the data and you don't want to have to make regular schema
> changes.  It's not as useful when it's data you want to search on
> often, use as a foreign key, etc.  I call these bits of information
> "details".

This is also probably good for "user fields" in an app. Or for providing
slightly more sane access to the RT table structure (DBIx::SearchBuilder
isn't to my taste. If it's to yours, please enjoy :).

> These fields work pretty much the same as a native table field if you
> use the generated accessors on them.  It does not override set() or
> get() to allow for accessing them that way.  I haven't tried setting
> triggers or validation or anything like that either, though it may
> work.

It almost certainly won't; get/set do lots of work in cdbi and pretty much
all the magic's in there - the number of method calls involved per accessor
operation is staggering and one of the things I've tried to do in dbic is
to keep get/set/store_column as lightweight as possible in the core; this
is why the stuff that affects them wraps them via MI.

To xthread evilly, if anybody fancies writing some classes for delegation
APIs for stuff I'd be happy to include them in the distribution :)

I'd also note that dbic doesn't use Class::Accessor, it uses a somewhat
hacked-up copy (DBIx::Class::AccessorGroup) that allows you to set a group
- the accessors fall through to get_$group and set_$group instead of just
get and set.

Currently the only groups I've used are column and hasa (for the cdbi compat
 - not sure this is the general case yet hence just a compat implementation)

> Here's an example based on the included test file.
> 
> You have a basic Movie table with the title and release year of the
> movie.  You don't know what extra information you might want to store
> later (genre, tagline, price, etc.).  So you create an attached
> details table where you can add these extra fields without changing
> the database code, though it does require adding it to the field list
> in the CDBI table class, obviously.
> 
> So you create the basic Movie table:
> 
>          movie
>          -------------------------
>          id    INTEGER PRIMARY KEY
>          title VARCHAR(1024)
>          year  INTEGER
> 
> and a details table:
> 
>          movie_details
>          -------------------------
>          id       INTEGER PRIMARY KEY
>          movie    INTEGER
>          field    VARCHAR(1024)
>          value    VARCHAR(1024)

Hmm, is there any reason why the DBIx::Class version shouldn't use a
composite PK on 'movie' and 'field', given you're treating them as
kinda-sorta-columns anyway?
 
> and use CDBI::R::HasDetails to link them:
> 
>          package My::Movie;
> 
>          use base 'My::DBI';
> 
>          use Class::DBI::Relationship::HasDetails;
>          __PACKAGE__->table('Movies');
>          __PACKAGE__->columns(All => qw/id title year/);
> 
>          __PACKAGE__->has_details('details', 'My::MovieDetails' => 'movie');
>          __PACKAGE__->details_columns(qw(tagline genre price));
> 
> More example code can be found in the attached test file.  The
> HasDetails::Relationship code is also attached, though I don't
> recommend anyone actually try it.  Hopefully attachments work for the
> list, otherwise I'll resend with code in the body.

Hmmm.

This smells like a restricted might_have. In fact, with a little faffing
you could almost do it as three might_haves with mapped method pull-ups
(except might_have won't let you map the method names, will it?)

The thing is, might_have smells to me like an updatable view, and so does this.
The select would be like -

SELECT movies.id as id, movies.title as title, movies.year as year,
 tagline.value as tagline, genre.value as genre, price.value as price
FROM movies
  LEFT JOIN movie_details tagline
    ON movies.id = tagline.movie AND details_1.field = 'tagline'
  ...
WHERE movies.id = ?

Which probably means something like

__PACKAGE__->add_relationship('tagline', 'My::MovieDetails' =>
                                { 'foreign.movie' => 'self.id',
                                  'foreign.field' => \'tagline' });

(and lather, rinse, repeat as needed) is in order on the table class, and we
need a nice way to setup a view. I'm still mulling that one over; ideas
welcome. Meantime, this lets us do

sub tagline { ($obj->search_related('tagline', { }, { } ))[0]->value; }
  # Hashes are search and attrs and could probably be left out

which gets us a read-only accessor. There's also a create_related, but I
haven't added find_or_create_related yet and I guess we should have an
update_related and delete_related that produce class updates/deletes on
the foreign class. I'd note that currently (I think) delete can handle
being used as a class method but update can't.

Another question: (sorry, twelve things in one thread, can people please
change subject lines and reply to one bit at once to make up for my inability
to keep to the point) What do we do about triggers in the case of a class
update/delete? Class::DBI seemed to be working on deprecating class
update/delete altogether, which I don't think is good - is it easiest just
to find a way for a triggers package to disable class update/delete (or
convert it into a search + map { $_->delete }) or are we better off
providing some way for the trigger to register a 'class version' as well as
an 'object version'.

Possibly more to the point, does anybody do anything with their triggers such
that we need to care about this at all, and if so what?

-- 
     Matt S Trout           Website: http://www.shadowcatsystems.co.uk
  Technical Director        E-mail:  mst (at) shadowcatsystems.co.uk
Shadowcat Systems Ltd.