2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml">
5 <title>DBIx::Class::ResultSource::MultipleTableInheritance -- Use multiple tables to define your classes</title>
6 <meta http-equiv="content-type" content="text/html; charset=utf-8" />
7 <link rev="made" href="mailto:amiri@akbuntu.nonet" />
10 <body style="background-color: white">
15 <p><a name="__index__"></a></p>
19 <li><a href="#synopsis">SYNOPSIS</a></li>
20 <li><a href="#why">WHY?</a></li>
21 <li><a href="#how">HOW?</a></li>
22 <li><a href="#methods">METHODS</a></li>
23 <li><a href="#author">AUTHOR</a></li>
26 <li><a href="#contributors">CONTRIBUTORS</a></li>
29 <li><a href="#license">LICENSE</a></li>
30 <li><a href="#see_also">SEE ALSO</a></li>
39 <h1><a name="synopsis">SYNOPSIS</a></h1>
42 package MyApp::Schema::Result::Coffee;</pre>
44 __PACKAGE__->table_class('DBIx::Class::ResultSource::MultipleTableInheritance');
45 __PACKAGE__->table('coffee');
46 __PACKAGE__->add_columns(
49 data_type => "integer",
50 default_value => "nextval('coffee_seq'::regclass)",
51 is_auto_increment => 1,
52 is_foreign_key => 1,
58 data_type => "text",
59 default_value => "good",
63 __PACKAGE__->set_primary_key("id");</pre>
69 package MyApp::Schema::Result::Sumatra;</pre>
71 use parent 'Coffee';</pre>
73 __PACKAGE__->table('sumatra');</pre>
75 __PACKAGE__->add_columns(
78 data_type => "text",
79 default_value => undef,
89 my $schema = MyApp::Schema->connect($dsn);</pre>
91 my $cup = $schema->resultset('Sumatra')->new;</pre>
93 print STDERR Dumper $cup->columns;</pre>
97 $VAR3 = 'aroma';</pre>
98 <p>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 <strong>transparently writable</strong>.</p>
99 <p>This is accomplished through the use of stored procedures that map changes written to the view to changes to the underlying concrete tables.</p>
103 <h1><a name="why">WHY?</a></h1>
104 <p>In many applications, many classes are subclasses of others. Let's say you have this schema:</p>
106 # Conceptual domain model
120 <p>That's redundant. Hold on a sec...</p>
128 class Investor extends User {
131 <p>Good idea, but how to put this into code?</p>
132 <p>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.</p>
134 create table "user" (
135 "id" integer not null primary key autoincrement,
136 "name" text not null,
137 "password" text not null,
138 "investor" tinyint(1),
139 "dollars" integer
141 <p>Let's not discuss that further.</p>
142 <p>A second, better, solution is to break out the two tables into user and investor:</p>
144 create table "user" (
145 "id" integer not null primary key autoincrement,
146 "name" text not null,
147 "password" text not null
150 create table "investor" (
151 "id" integer not null references user("id"),
152 "dollars" integer
154 <p>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:</p>
157 my $user_rs = $schema->resultset('User');
158 my $new_user = $user_rs->create(
159 name => $args->{name},
160 password => $args->{password},
165 my $new_investor = $schema->resultset('Investor')->create(
166 id => $new_user->id,
167 dollars => $args->{dollars},
169 <p>One can cope well with the second strategy, and it seems to be the most popular smart choice.</p>
173 <h1><a name="how">HOW?</a></h1>
174 <p>There is a third strategy implemented here. Make the database do more of the work: hide the nasty bits so we don't have to handle them unless we really want to. It'll save us some typing and it'll make for more expressive code. What if we could do this:</p>
176 my $new_investor = $schema->resultset('Investor')->create(
177 name => $args->{name},
178 password => $args->{password},
179 dollars => $args->{dollars},
182 And have it Just Work? The user...</pre>
185 name => $args->{name},
186 password => $args->{password},
188 <p>should be created behind the scenes, 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.</p>
189 <p>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:</p>
191 INSERT INTO investor ("name","password","dollars") VALUES (...);</pre>
192 <p>But using MTI, it is really this:</p>
194 INSERT INTO _user_table ("username","password") VALUES (...);
195 INSERT INTO _investor_table ("id","dollars") VALUES (currval('_user_table_id_seq',...) );</pre>
196 <p>For deletes, the triggers fire in reverse, to preserve referential integrity (foreign key constraints). For instance:</p>
198 my $investor = $schema->resultset('Investor')->find({id => $args->{id}});
199 $investor->delete;</pre>
202 DELETE FROM _investor_table WHERE ("id" = ?);
203 DELETE FROM _user_table WHERE ("id" = ?);</pre>
207 <h1><a name="methods">METHODS</a></h1>
209 <dt><strong><a name="new" class="item">new</a></strong></dt>
212 <p>MTI find the parents, if any, of your resultset class and adds them to the list of parent_sources for the table.</p>
214 <dt><strong><a name="add_additional_parents" class="item">add_additional_parents</a></strong></dt>
217 <p>Continuing with coffee:</p>
219 __PACKAGE__->result_source_instance->add_additional_parents(
221 MyApp::Schema::Result::Beverage
222 MyApp::Schema::Result::Liquid
225 <p>This just lets you manually add additional parents beyond the ones MTI finds.</p>
227 <dt><strong><a name="add_additional_parent" class="item">add_additional_parent</a></strong></dt>
231 __PACKAGE__->result_source_instance->add_additional_parent(
232 MyApp::Schema::Result::Beverage
234 <p>You can also add just one.</p>
236 <dt><strong><a name="attach_additional_sources" class="item">attach_additional_sources</a></strong></dt>
239 <p>MTI takes the parents' sources and relationships, creates new DBIx::Class:Table object from them, and registers this as a new, raw, source in the schema, e.g.,</p>
241 use MyApp::Schema;</pre>
243 print STDERR map { "$_\n" } MyApp::Schema->sources;</pre>
250 <p>Raw::Sumatra will be used to generate the view.</p>
252 <dt><strong><a name="view_definition" class="item">view_definition</a></strong></dt>
255 <p>This takes the raw table and generates the view (and stored procedures) you will use.</p>
261 <h1><a name="author">AUTHOR</a></h1>
262 <p>Matt S. Trout, <<a href="mailto:mst@shadowcatsystems.co.uk">mst@shadowcatsystems.co.uk</a>></p>
265 <h2><a name="contributors">CONTRIBUTORS</a></h2>
266 <p>Docs: Amiri Barksdale, <<a href="mailto:amiri@metalabel.com">amiri@metalabel.com</a>></p>
270 <h1><a name="license">LICENSE</a></h1>
271 <p>This library is free software; you can redistribute it and/or modify
272 it under the same terms as Perl itself.</p>
276 <h1><a name="see_also">SEE ALSO</a></h1>
277 <p><a href="/DBIx/Class.html">the DBIx::Class manpage</a>
278 <a href="/DBIx/Class/ResultSource.html">the DBIx::Class::ResultSource manpage</a></p>