1 package DBIx::Class::ResultSource::MultipleTableInheritance;
5 use parent qw(DBIx::Class::ResultSource::View);
6 use Method::Signatures::Simple;
7 use Carp::Clan qw/^DBIx::Class/;
8 use aliased 'DBIx::Class::ResultSource::Table';
9 use aliased 'DBIx::Class::ResultClass::HashRefInflator';
10 use String::TT qw(strip tt);
11 use Scalar::Util qw(blessed);
12 use namespace::autoclean;
16 __PACKAGE__->mk_group_accessors(simple => qw(parent_source additional_parents));
18 method new ($class: @args) {
19 my $new = $class->next::method(@args);
20 my $rc = $new->result_class;
21 if (my $meth = $rc->can('result_source_instance')) {
22 my $source = $rc->$meth;
23 if ($source->result_class ne $new->result_class
24 && $new->result_class->isa($source->result_class)) {
25 $new->parent_source($source);
31 method add_additional_parents (@classes) {
32 foreach my $class (@classes) {
33 Class::C3::Componentised->ensure_class_loaded($class);
34 $self->add_additional_parent(
35 $class->result_source_instance
40 method add_additional_parent ($source) {
41 my ($our_pk, $their_pk) = map {
42 join('|',sort $_->primary_columns)
45 confess "Can't attach additional parent ${\$source->name} - it has different PKs ($their_pk versus our $our_pk)"
46 unless $their_pk eq $our_pk;
47 $self->additional_parents([
48 @{$self->additional_parents||[]}, $source
52 $_ => # put the extra key first to default it
53 { originally_defined_in => $source->name, %{$source->column_info($_)}, },
54 } grep !$self->has_column($_), $source->columns
56 foreach my $rel ($source->relationships) {
57 my $rel_info = $source->relationship_info($rel);
58 $self->add_relationship(
59 $rel, $rel_info->{source}, $rel_info->{cond},
60 # extra key first to default it
61 {originally_defined_in => $source->name, %{$rel_info->{attrs}}},
65 push(@{$self->result_class.'::ISA'}, $source->result_class);
69 method _source_by_name ($name) {
70 my $schema = $self->schema;
72 grep { $_->name eq $name }
73 map $schema->source($_), $schema->sources;
74 confess "Couldn't find attached source for parent $name - did you use load_classes? This module is only compatible with load_namespaces"
79 method schema (@args) {
80 my $ret = $self->next::method(@args);
82 if ($self->parent_source) {
83 my $parent_name = $self->parent_source->name;
84 $self->parent_source($self->_source_by_name($parent_name));
86 $self->additional_parents([
87 map { $self->_source_by_name($_->name) }
88 @{$self->additional_parents||[]}
94 method attach_additional_sources () {
95 my $raw_name = $self->raw_source_name;
96 my $schema = $self->schema;
98 # if the raw source is already present we can assume we're done
99 return if grep { $_ eq $raw_name } $schema->sources;
101 # our parent should've been registered already actually due to DBIC
102 # attaching subclass sources later in load_namespaces
105 if ($self->parent_source) {
106 my $parent_name = $self->parent_source->name;
108 grep { $_->name eq $parent_name }
109 map $schema->source($_), $schema->sources;
110 confess "Couldn't find attached source for parent $parent_name - did you use load_classes? This module is only compatible with load_namespaces"
112 $self->parent_source($parent); # so our parent is the one in this schema
115 # create the raw table source
117 my $table = Table->new({ name => $self->raw_table_name });
119 # we don't need to add the PK cols explicitly if we're the root table
120 # since they'll get added below
125 foreach my $pri ($self->primary_columns) {
126 my %info = %{$self->column_info($pri)};
127 delete @info{qw(is_auto_increment sequence auto_nextval)};
128 $table->add_column($pri => \%info);
129 $pk_join{"foreign.${pri}"} = "self.${pri}";
131 # have to use source name lookups rather than result class here
132 # because we don't actually have a result class on the raw sources
133 $table->add_relationship('parent', $parent->raw_source_name, \%pk_join);
134 $self->depends_on->{$parent->source_name} = 1;
137 foreach my $add (@{$self->additional_parents||[]}) {
138 $table->add_relationship(
139 'parent_'.$add->name, $add->source_name, \%pk_join
141 $self->depends_on->{$add->source_name} = 1;
144 # add every column that's actually a concrete part of us
147 map { ($_ => { %{$self->column_info($_)} }) }
148 grep { $self->column_info($_)->{originally_defined_in} eq $self->name }
151 $table->set_primary_key($self->primary_columns);
153 # we need to copy our rels to the raw object as well
154 # note that ->add_relationship on a source object doesn't create an
155 # accessor so we can leave that part in the attributes
157 # if the other side is a table then we need to copy any rels it has
158 # back to us, as well, so that they point at the raw table. if the
159 # other side is an MTI view then we need to create the rels to it to
160 # point at -its- raw table; we don't need to worry about backrels because
161 # it's going to run this method too (and its raw source might not exist
162 # yet so we can't, anyway)
164 foreach my $rel ($self->relationships) {
165 my $rel_info = $self->relationship_info($rel);
167 # if we got this from the superclass, -its- raw table will nail this.
168 # if we got it from an additional parent, it's its problem.
169 next unless $rel_info->{attrs}{originally_defined_in} eq $self->name;
171 my $f_source = $schema->source($rel_info->{source});
173 # __PACKAGE__ is correct here because subclasses should be caught
175 my $one_of_us = $f_source->isa(__PACKAGE__);
177 my $f_source_name = $f_source->${\
178 ($one_of_us ? 'raw_source_name' : 'source_name')
181 $table->add_relationship(
182 '_'.$rel, $f_source_name, @{$rel_info}{qw(cond attrs)}
185 unless ($one_of_us) {
187 # we haven't been registered yet, so reverse_ cries
188 # XXX this is evil and will probably break eventually
189 local @{$schema->source_registrations}
190 {map $self->$_, qw(source_name result_class)}
192 $self->reverse_relationship_info($rel);
194 foreach my $rev_rel (keys %$reverse) {
195 $f_source->add_relationship(
196 '_raw_'.$rev_rel, $raw_name, @{$reverse->{$rev_rel}}{qw(cond attrs)}
202 $schema->register_source($raw_name => $table);
205 method set_primary_key (@args) {
206 if ($self->parent_source) {
207 confess "Can't set primary key on a subclass";
209 return $self->next::method(@args);
212 method raw_source_name () {
213 my $base = $self->source_name;
214 confess "Can't generate raw source name for ${\$self->name} when we don't have a source_name"
216 return 'Raw::'.$base;
219 method raw_table_name () {
220 return '_'.$self->name;
223 method add_columns (@args) {
224 my $ret = $self->next::method(@args);
225 $_->{originally_defined_in} ||= $self->name for values %{$self->_columns};
229 method add_relationship ($name, $f_source, $cond, $attrs) {
231 $name, $f_source, $cond,
232 { originally_defined_in => $self->name, %{$attrs||{}}, }
238 # helper routines, constructed as anon subs so autoclean nukes them
242 *argify = sub (@names) {
246 *qualify_with = sub ($source, @names) {
247 my $name = blessed($source) ? $source->name : $source;
248 map join('.', $name, $_), @names;
251 *body_cols = sub ($source) {
252 my %pk; @pk{$source->primary_columns} = ();
253 map +{ %{$source->column_info($_)}, name => $_ },
254 grep !exists $pk{$_}, $source->columns;
257 *pk_cols = sub ($source) {
258 map +{ %{$source->column_info($_)}, name => $_ },
259 $source->primary_columns;
262 *names_of = sub (@cols) { map $_->{name}, @cols };
264 *function_body = sub ($name, $args, $body_parts) {
267 map "_${\$_->{name}} ${\uc($_->{data_type})}",
270 my $body = join("\n", '', map " $_;", @$body_parts);
272 CREATE OR REPLACE FUNCTION [% name %]
274 RETURNS VOID AS $function$
278 $function$ LANGUAGE plpgsql;
287 *arg_hash = sub ($source) {
288 map +($_ => \(argify $_)), names_of body_cols $source;
291 *rule_body = sub ($on, $to, $oldlist, $newlist) {
292 my $arglist = join(', ',
293 (qualify_with 'OLD', names_of @$oldlist),
294 (qualify_with 'NEW', names_of @$newlist),
296 $to = $to->name if blessed($to);
298 CREATE RULE _[% to %]_[% on %]_rule AS
299 ON [% on | upper %] TO [% to %]
301 SELECT [% to %]_[% on %]([% arglist %])
307 method root_table () {
309 ? $self->parent_source->root_table
310 : $self->schema->source($self->raw_source_name)
313 method view_definition () {
314 my $schema = $self->schema;
315 confess "Can't generate view without connected schema, sorry"
316 unless $schema && $schema->storage;
317 my $sqla = $schema->storage->sql_maker;
318 my $table = $self->schema->source($self->raw_source_name);
319 my $super_view = $self->parent_source;
320 my @all_parents = my @other_parents = @{$self->additional_parents||[]};
321 push(@all_parents, $super_view) if defined($super_view);
322 my @sources = ($table, @all_parents);
323 my @body_cols = map body_cols($_), @sources;
324 my @pk_cols = pk_cols $self;
328 my $am_root = !($super_view || @other_parents);
330 my $select = $sqla->select(
333 : ([ # FROM _tbl _tbl
334 { $table->name => $table->name },
338 { $parent->name => $parent->name },
339 # ON _tbl.id = view.id
340 { map +(qualify_with($parent, $_), qualify_with($table, $_)),
346 [ (qualify_with $table, names_of @pk_cols), names_of @body_cols ],
349 my ($now, @next) = grep defined, $super_view, $table, @other_parents;
353 # NOTE: this assumes a single PK col called id with a sequence somewhere
354 # but nothing else -should- so fixing this should make everything work
357 $self->name.'_insert',
360 $sqla->insert( # INSERT INTO tbl/super_view (foo, ...) VALUES (_foo, ...)
365 $sqla->insert( # INSERT INTO parent (id, ...)
366 # VALUES (currval('_root_tbl_id_seq'), ...)
370 id => \"currval('${\$self->root_table->name}_id_seq')",
376 # note - similar to arg_hash but not quite enough to share code sanely
377 my $pk_where = { # id = _id AND id2 = _id2 ...
378 map +($_ => \"= ${\argify $_}"), names_of @pk_cols
385 $self->name.'_update',
386 [ @pk_cols, @body_cols ],
388 $_->name, # UPDATE foo
389 { arg_hash $_ }, # SET a = _a
398 $self->name.'_delete',
400 [ map $sqla->delete($_->name, $pk_where), @sources ];
403 (rule_body insert => $self, [], \@body_cols),
404 (rule_body update => $self, \@pk_cols, \@body_cols),
405 (rule_body delete => $self, \@pk_cols, []),
407 return join("\n\n", $select, $insert_func, $update_func, $delete_func, @rules);
416 # On construction, we hook $self->result_class->result_source_instance
417 # if present to get the superclass' source object
419 # When attached to a schema, we need to add sources to that schema with
420 # appropriate relationships for the foreign keys so the concrete tables
423 # We also generate our own view definition using this class' concrete table
424 # and the view for the superclass, and stored procedures for the insert,
425 # update and delete operations on this view.
427 # deploying the postgres rules through SQLT may be a pain though.
433 DBIx::Class::ResultSource::MultipleTableInheritance -- Use multiple tables to define your classes
439 package MyApp::Schema::Result::Coffee;
441 __PACKAGE__->table_class('DBIx::Class::ResultSource::MultipleTableInheritance');
442 __PACKAGE__->table('coffee');
443 __PACKAGE__->add_columns(
446 data_type => "integer",
447 default_value => "nextval('coffee_seq'::regclass)",
448 is_auto_increment => 1,
456 default_value => "good",
460 __PACKAGE__->set_primary_key("id");
466 package MyApp::Schema::Result::Sumatra;
470 __PACKAGE__->table('sumatra');
472 __PACKAGE__->add_columns(
476 default_value => undef,
486 my $schema = MyApp::Schema->connect($dsn);
488 my $cup = $schema->resultset('Sumatra')->new;
490 print STDERR Dumper $cup->columns;
498 Inherit from this package and you can make a resultset class from a view, but that's more than a little bit misleading: the result is B<transparentlt writable>.
500 This is accomplished through the use of stored functions that map changes written to the view to changes to the underlying concrete tables.
504 In many applications, many classes are subclasses of others. Let's say you have this schema:
506 # Conceptual domain model
521 That's redundant. Hold on a sec...
529 class Investor isa User {
533 Good idea, but how to put this into code?
535 One far-too common and absolutely horrendous solution is to have a "checkbox" in your database: a nullable "investor" column, which entails a nullable "dollars" column, in the user table.
537 create table "user" (
538 "id" integer not null primary key autoincrement,
539 "name" text not null,
540 "password" text not null,
541 "investor" tinyint(1),
545 Let's not discuss that further.
547 A second, better, solution is to break out the two tables into user and investor:
549 create table "user" (
550 "id" integer not null primary key autoincrement,
551 "name" text not null,
552 "password" text not null
555 create table "investor" (
556 "id" integer not null references user("id"),
560 So that investor's PK is just an FK to the user. We can clearly see the class hierarchy here, in which investor is a subclass of user. In DBIx::Class applications, this second strategy looks like:
562 my $user_rs = $schema->resultset('User');
563 my $new_user = $user_rs->create(
564 name => $args->{name},
565 password => $args->{password},
570 my $new_investor = $schema->resultset('Investor')->create(
572 dollars => $args->{dollars},
575 One can cope well with the second strategy, and it seems to be the most popular smart choice.
579 There is a third strategy implemented here. Make the database do more of the work. It'll save us some typing and it'll make for more expressive code. What if we could do this:
581 my $new_investor = $schema->resultset('Investor')->create(
582 name => $args->{name},
583 password => $args->{password},
584 dollars => $args->{dollars},
587 And have it Just Work? The user ( {name => $args->{name}, password => $args->{password} } ) should be created transparently, and the use of either user or investor in your code should require no special handling. Deleting and updating $new_investor should also delete or update the user row.
589 It does. User and investor are both views, their concrete tables abstracted away behind a set of rules and triggers. You would expect the above DBIC create statement to look like this in SQL:
591 INSERT INTO investor ("name","password","dollars") VALUES (...);
593 But using MTI, it is really this:
595 INSERT INTO _user_table ("username","password") VALUES (...);
596 INSERT INTO _investor_table ("id","dollars") VALUES (currval('_user_table_id_seq',...) );
598 For deletes, the triggers fire in reverse, to preserve referential integrity (foreign key constraints). For instance:
600 my $investor = $schema->resultset('Investor')->find({id => $args->{id}});
605 DELETE FROM _investor_table WHERE ("id" = ?);
606 DELETE FROM _user_table WHERE ("id" = ?);
617 Matt S. Trout, E<lt>mst@shadowcatsystems.co.ukE<gt>
621 Docs: Amiri Barksdale, E<lt>amiri@metalabel.comE<gt>
625 This library is free software; you can redistribute it and/or modify
626 it under the same terms as Perl itself.
631 L<DBIx::Class::ResultSource>