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="#name">NAME</a></li>
20 <li><a href="#synopsis">SYNOPSIS</a></li>
21 <li><a href="#why">WHY?</a></li>
22 <li><a href="#how">HOW?</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>
40 <h1><a name="name">NAME</a></h1>
41 <p>DBIx::Class::ResultSource::MultipleTableInheritance -- Use multiple tables to define your classes</p>
45 <h1><a name="synopsis">SYNOPSIS</a></h1>
48 package MyApp::Schema::Result::Coffee;</pre>
50 __PACKAGE__->table_class('DBIx::Class::ResultSource::MultipleTableInheritance');
51 __PACKAGE__->table('coffee');
52 __PACKAGE__->add_columns(
55 data_type => "integer",
56 default_value => "nextval('coffee_seq'::regclass)",
57 is_auto_increment => 1,
58 is_foreign_key => 1,
64 data_type => "text",
65 default_value => "good",
69 __PACKAGE__->set_primary_key("id");</pre>
75 package MyApp::Schema::Result::Sumatra;</pre>
77 use parent 'Coffee';</pre>
79 __PACKAGE__->table('sumatra');</pre>
81 __PACKAGE__->add_columns(
84 data_type => "text",
85 default_value => undef,
95 my $schema = MyApp::Schema->connect($dsn);</pre>
97 my $cup = $schema->resultset('Sumatra')->new;</pre>
99 print STDERR Dumper $cup->columns;</pre>
103 $VAR3 = 'aroma';</pre>
104 <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>writable</strong> and updateable--transparently.</p>
105 <p>This is accomplished through the use of stored functions that map changes written to the view to changes to the underlying concrete tables.</p>
109 <h1><a name="why">WHY?</a></h1>
110 <p>In many applications, many classes are subclasses of others. Let's say you have this schema:</p>
112 # Conceptual domain model
126 <p>That's redundant. Hold on a sec...</p>
134 class Investor isa User {
137 <p>Good idea, but how to put this into code?</p>
138 <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>
140 create table "user" (
141 "id" integer not null primary key autoincrement,
142 "name" text not null,
143 "password" text not null,
144 "investor" tinyint(1),
145 "dollars" integer
147 <p>Let's not discuss that further.</p>
148 <p>A second, better, solution is to break out the two tables into user and investor:</p>
150 create table "user" (
151 "id" integer not null primary key autoincrement,
152 "name" text not null,
153 "password" text not null
156 create table "investor" (
157 "id" integer not null references user("id"),
158 "dollars" integer
160 <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>
163 my $user_rs = $schema->resultset('User');
164 my $new_user = $user_rs->create(
165 name => $args->{name},
166 password => $args->{password},
171 my $new_investor = $schema->resultset('Investor')->create(
172 id => $new_user->id,
173 dollars => $args->{dollars},
175 <p>One can cope well with the second strategy, and it seems to be the most popular smart choice.</p>
179 <h1><a name="how">HOW?</a></h1>
180 <p>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:</p>
182 my $new_investor = $schema->resultset('Investor')->create(
183 name => $args->{name},
184 password => $args->{password},
185 dollars => $args->{dollars},
188 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.</pre>
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>
208 <h1><a name="author">AUTHOR</a></h1>
209 <p>Matt S. Trout, <<a href="mailto:mst@shadowcatsystems.co.uk">mst@shadowcatsystems.co.uk</a>></p>
212 <h2><a name="contributors">CONTRIBUTORS</a></h2>
213 <p>Docs: Amiri Barksdale, <<a href="mailto:amiri@metalabel.com">amiri@metalabel.com</a>></p>
217 <h1><a name="license">LICENSE</a></h1>
218 <p>This library is free software; you can redistribute it and/or modify
219 it under the same terms as Perl itself.</p>
223 <h1><a name="see_also">SEE ALSO</a></h1>
224 <p><a href="/DBIx/Class.html">the DBIx::Class manpage</a>
225 <a href="/DBIx/Class/ResultSource.html">the DBIx::Class::ResultSource manpage</a></p>