makes search_related on extended rels without the optimized version work. involves...
[dbsrgits/DBIx-Class-Historic.git] / lib / DBIx / Class / Manual / Intro.pod
CommitLineData
9c82c181 1=head1 NAME
2
3b44ccc6 3DBIx::Class::Manual::Intro - Introduction to DBIx::Class
9c82c181 4
40dbc108 5=head1 INTRODUCTION
076652e8 6
d53178fd 7You're bored with SQL, and want a native Perl interface for your database? Or
8you've been doing this for a while with L<Class::DBI>, and think there's a
9better way? You've come to the right place.
4b0779f4 10
11=head1 THE DBIx::Class WAY
12
d53178fd 13Here are a few simple tips that will help you get your bearings with
d56c3191 14DBIx::Class.
4b0779f4 15
2f0790c4 16=head2 Tables become Result classes
4b0779f4 17
2f0790c4 18DBIx::Class needs to know what your Table structure looks like. You
19do that by defining Result classes. Result classes are defined by
20calling methods proxied to L<DBIx::Class::ResultSource>. Each Result
21class defines one Table, which defines the Columns it has, along with
22any Relationships it has to other tables. (And oh, so much more
23besides) The important thing to understand:
d53178fd 24
2f0790c4 25 A Result class == Table
d53178fd 26
4b0779f4 27(most of the time, but just bear with my simplification)
28
29=head2 It's all about the ResultSet
30
d53178fd 31So, we've got some ResultSources defined. Now, we want to actually use those
d56c3191 32definitions to help us translate the queries we need into handy perl objects!
d53178fd 33
34Let's say we defined a ResultSource for an "album" table with three columns:
35"albumid", "artist", and "title". Any time we want to query this table, we'll
36be creating a L<DBIx::Class::ResultSet> from its ResultSource. For example, the
37results of:
4b0779f4 38
d53178fd 39 SELECT albumid, artist, title FROM album;
4b0779f4 40
d53178fd 41Would be retrieved by creating a ResultSet object from the album table's
d56c3191 42ResultSource, likely by using the "search" method.
4b0779f4 43
d53178fd 44DBIx::Class doesn't limit you to creating only simple ResultSets -- if you
45wanted to do something like:
46
47 SELECT title FROM album GROUP BY title;
4b0779f4 48
d56c3191 49You could easily achieve it.
4b0779f4 50
d56c3191 51The important thing to understand:
4b0779f4 52
d56c3191 53 Any time you would reach for a SQL query in DBI, you are
d53178fd 54 creating a DBIx::Class::ResultSet.
4b0779f4 55
56=head2 Search is like "prepare"
57
d53178fd 58DBIx::Class tends to wait until it absolutely must fetch information from the
59database. If you are returning a ResultSet, the query won't execute until you
60use a method that wants to access the data. (Such as "next", or "first")
4b0779f4 61
62The important thing to understand:
63
d53178fd 64 Setting up a ResultSet does not execute the query; retrieving
65 the data does.
4b0779f4 66
2f0790c4 67=head2 Search results are returned as Rows
68
69Rows of the search from the database are blessed into
70L<DBIx::Class::Row> objects.
71
4b0779f4 72=head1 SETTING UP DBIx::Class
73
d53178fd 74Let's look at how you can set and use your first native L<DBIx::Class> tree.
076652e8 75
d53178fd 76First we'll see how you can set up your classes yourself. If you want them to
77be auto-discovered, just skip to the next section, which shows you how to use
78L<DBIx::Class::Schema::Loader>.
076652e8 79
80=head2 Setting it up manually
81
5cc9fa32 82First, you should create your base schema class, which inherits from
83L<DBIx::Class::Schema>:
076652e8 84
5cc9fa32 85 package My::Schema;
86 use base qw/DBIx::Class::Schema/;
87
d53178fd 88In this class you load your result_source ("table", "model") classes, which we
da7372ac 89will define later, using the load_namespaces() method:
076652e8 90
da7372ac 91 # load My::Schema::Result::* and their resultset classes
92 __PACKAGE__->load_namespaces();
076652e8 93
da7372ac 94By default this loads all the Result (Row) classes in the
95My::Schema::Result:: namespace, and also any resultset classes in the
96My::Schema::ResultSet:: namespace (if missing, the resultsets are
97defaulted to be DBIx::Class::ResultSet objects). You can change the
98result and resultset namespaces by using options to the
99L<DBIx::Class::Schema/load_namespaces> call.
076652e8 100
da7372ac 101It is also possible to do the same things manually by calling
102C<load_classes> for the Row classes and defining in those classes any
103required resultset classes.
076652e8 104
5cc9fa32 105Next, create each of the classes you want to load as specified above:
076652e8 106
da7372ac 107 package My::Schema::Result::Album;
d88ecca6 108 use base qw/DBIx::Class::Core/;
35d4fe78 109
d88ecca6 110Load any additional components you may need with the load_components() method,
111and provide component configuration if required. For example, if you want
112automatic row ordering:
076652e8 113
d88ecca6 114 __PACKAGE__->load_components(qw/ Ordered /);
115 __PACKAGE__->position_column('rank');
076652e8 116
81aa4300 117Ordered will refer to a field called 'position' unless otherwise directed. Here you are defining
118the ordering field to be named 'rank'. (NOTE: Insert errors may occur if you use the Ordered
119component, but have not defined a position column or have a 'position' field in your row.)
120
5cc9fa32 121Set the table for your class:
076652e8 122
35d4fe78 123 __PACKAGE__->table('album');
076652e8 124
5cc9fa32 125Add columns to your class:
126
d88ecca6 127 __PACKAGE__->add_columns(qw/ albumid artist title rank /);
5cc9fa32 128
d53178fd 129Each column can also be set up with its own accessor, data_type and other pieces
130of information that it may be useful to have -- just pass C<add_columns> a hash:
5cc9fa32 131
132 __PACKAGE__->add_columns(albumid =>
133 { accessor => 'album',
134 data_type => 'integer',
135 size => 16,
136 is_nullable => 0,
137 is_auto_increment => 1,
138 default_value => '',
139 },
140 artist =>
141 { data_type => 'integer',
142 size => 16,
143 is_nullable => 0,
144 is_auto_increment => 0,
145 default_value => '',
146 },
d56c3191 147 title =>
5cc9fa32 148 { data_type => 'varchar',
149 size => 256,
150 is_nullable => 0,
151 is_auto_increment => 0,
152 default_value => '',
d88ecca6 153 },
154 rank =>
155 { data_type => 'integer',
156 size => 16,
157 is_nullable => 0,
158 is_auto_increment => 0,
159 default_value => '',
5cc9fa32 160 }
161 );
162
d53178fd 163DBIx::Class doesn't directly use most of this data yet, but various related
164modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you to
165create your database tables from your Schema, instead of the other way around.
d88ecca6 166See L<DBIx::Class::Schema/deploy> for details.
5cc9fa32 167
168See L<DBIx::Class::ResultSource> for more details of the possible column
169attributes.
170
da7372ac 171Accessors are created for each column automatically, so My::Schema::Result::Album will
5cc9fa32 172have albumid() (or album(), when using the accessor), artist() and title()
173methods.
174
175Define a primary key for your class:
076652e8 176
5cc9fa32 177 __PACKAGE__->set_primary_key('albumid');
076652e8 178
5cc9fa32 179If you have a multi-column primary key, just pass a list instead:
076652e8 180
5cc9fa32 181 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
076652e8 182
d53178fd 183Define this class' relationships with other classes using either C<belongs_to>
184to describe a column which contains an ID of another Table, or C<has_many> to
185make a predefined accessor for fetching objects that contain this Table's
186foreign key:
5cc9fa32 187
4ae94ded 188 # in My::Schema::Result::Artist
189 __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
076652e8 190
d53178fd 191See L<DBIx::Class::Relationship> for more information about the various types of
192available relationships and how you can design your own.
076652e8 193
5cc9fa32 194=head2 Using L<DBIx::Class::Schema::Loader>
076652e8 195
d53178fd 196This is an external module, and not part of the L<DBIx::Class> distribution.
5fe8a42e 197It inspects your database, and automatically creates classes for all the tables
198in your database.
076652e8 199
5fe8a42e 200The simplest way to use it is via the L<dbicdump> script from the
201L<DBIx::Class::Schema::Loader> distribution. For example:
202
203 $ dbicdump -o dump_directory=./lib MyApp::Schema dbi:mysql:mydb user pass
204
205If you have a mixed-case database, use the C<preserve_case> option, e.g.:
206
207 $ dbicdump -o dump_directory=./lib -o preserve_case=1 MyApp::Schema \
208 dbi:mysql:mydb user pass
40dbc108 209
5fe8a42e 210If you are using L<Catalyst>, then you can use the helper that comes with
211L<Catalyst::Model::DBIC::Schema>:
076652e8 212
5fe8a42e 213 $ script/myapp_create.pl model MyDB DBIC::Schema MyDB::Schema \
214 create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \
215 on_connect_do='PRAGMA foreign_keys=ON' quote_char='"'
076652e8 216
5fe8a42e 217See L<Catalyst::Helper::Model::DBIC::Schema> for more information on this
218helper.
3f073ddf 219
5fe8a42e 220See the L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base>
221documentation for more information on the many loader options.
076652e8 222
5cc9fa32 223=head2 Connecting
224
6ba55998 225To connect to your Schema, you need to provide the connection details or a
226database handle.
227
228=head3 Via connection details
229
230The arguments are the same as for L<DBI/connect>:
5cc9fa32 231
232 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
233
d53178fd 234You can create as many different schema instances as you need. So if you have a
235second database you want to access:
5cc9fa32 236
237 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
238
d53178fd 239Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
240multiple connections, you need to do this manually.
5cc9fa32 241
48580715 242To execute some SQL statements on every connect you can add them as an option in
d53178fd 243a special fifth argument to connect:
3f073ddf 244
245 my $another_schema = My::Schema->connect(
246 $dsn,
247 $user,
248 $password,
249 $attrs,
250 { on_connect_do => \@on_connect_sql_statments }
251 );
5cc9fa32 252
e0b505d4 253See L<DBIx::Class::Storage::DBI/connect_info> for more information about
d53178fd 254this and other special C<connect>-time options.
5cc9fa32 255
6ba55998 256=head3 Via a database handle
257
258The supplied coderef is expected to return a single connected database handle
259(e.g. a L<DBI> C<$dbh>)
260
261 my $schema = My::Schema->connect (
262 sub { Some::DBH::Factory->connect },
263 \%extra_attrs,
264 );
265
40dbc108 266=head2 Basic usage
076652e8 267
35d4fe78 268Once you've defined the basic classes, either manually or using
5cc9fa32 269L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
270
d53178fd 271To access your database using your $schema object, you can fetch a
272L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
273calling the C<resultset> method.
5cc9fa32 274
35d4fe78 275The simplest way to get a record is by primary key:
076652e8 276
5cc9fa32 277 my $album = $schema->resultset('Album')->find(14);
076652e8 278
d53178fd 279This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
da7372ac 280return an instance of C<My::Schema::Result::Album> that represents this row. Once you
d53178fd 281have that row, you can access and update columns:
076652e8 282
35d4fe78 283 $album->title('Physical Graffiti');
284 my $title = $album->title; # $title holds 'Physical Graffiti'
076652e8 285
d53178fd 286If you prefer, you can use the C<set_column> and C<get_column> accessors
287instead:
076652e8 288
35d4fe78 289 $album->set_column('title', 'Presence');
290 $title = $album->get_column('title');
076652e8 291
18bb9eca 292Just like with L<Class::DBI>, you call C<update> to save your changes to the
293database (by executing the actual C<UPDATE> statement):
40dbc108 294
35d4fe78 295 $album->update;
076652e8 296
d53178fd 297If needed, you can throw away your local changes:
076652e8 298
35d4fe78 299 $album->discard_changes if $album->is_changed;
076652e8 300
d53178fd 301As you can see, C<is_changed> allows you to check if there are local changes to
302your object.
076652e8 303
40dbc108 304=head2 Adding and removing rows
076652e8 305
d53178fd 306To create a new record in the database, you can use the C<create> method. It
da7372ac 307returns an instance of C<My::Schema::Result::Album> that can be used to access the data
d53178fd 308in the new record:
076652e8 309
d56c3191 310 my $new_album = $schema->resultset('Album')->create({
35d4fe78 311 title => 'Wish You Were Here',
312 artist => 'Pink Floyd'
313 });
dfeba824 314
315Now you can add data to the new record:
316
35d4fe78 317 $new_album->label('Capitol');
318 $new_album->year('1975');
319 $new_album->update;
076652e8 320
d53178fd 321Likewise, you can remove it from the database:
076652e8 322
35d4fe78 323 $new_album->delete;
076652e8 324
d53178fd 325You can also remove records without retrieving them first, by calling delete
326directly on a ResultSet object.
076652e8 327
35d4fe78 328 # Delete all of Falco's albums
5cc9fa32 329 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
076652e8 330
40dbc108 331=head2 Finding your objects
076652e8 332
d53178fd 333L<DBIx::Class> provides a few different ways to retrieve data from your
334database. Here's one example:
35d4fe78 335
336 # Find all of Santana's albums
5cc9fa32 337 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
35d4fe78 338
d53178fd 339In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
340object. It can be used to peek at the first album returned by the database:
35d4fe78 341
342 my $album = $rs->first;
343 print $album->title;
076652e8 344
5cc9fa32 345You can loop over the albums and update each one:
076652e8 346
35d4fe78 347 while (my $album = $rs->next) {
348 print $album->artist . ' - ' . $album->title;
349 $album->year(2001);
350 $album->update;
351 }
a3c5e7e3 352
5cc9fa32 353Or, you can update them all at once:
354
355 $rs->update({ year => 2001 });
356
d53178fd 357In list context, the C<search> method returns all of the matching rows:
a3c5e7e3 358
35d4fe78 359 # Fetch immediately all of Carlos Santana's albums
5cc9fa32 360 my @albums = $schema->resultset('Album')->search(
361 { artist => 'Carlos Santana' }
362 );
35d4fe78 363 foreach my $album (@albums) {
364 print $album->artist . ' - ' . $album->title;
365 }
076652e8 366
40dbc108 367We also provide a handy shortcut for doing a C<LIKE> search:
076652e8 368
35d4fe78 369 # Find albums whose artist starts with 'Jimi'
24d34a80 370 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
076652e8 371
d53178fd 372Or you can provide your own C<WHERE> clause:
35d4fe78 373
374 # Find Peter Frampton albums from the year 1986
375 my $where = 'artist = ? AND year = ?';
376 my @bind = ( 'Peter Frampton', 1986 );
5cc9fa32 377 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
40dbc108 378
d53178fd 379The preferred way to generate complex queries is to provide a L<SQL::Abstract>
380construct to C<search>:
40dbc108 381
5cc9fa32 382 my $rs = $schema->resultset('Album')->search({
35d4fe78 383 artist => { '!=', 'Janis Joplin' },
384 year => { '<' => 1980 },
1aec4bac 385 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
35d4fe78 386 });
387
388This results in something like the following C<WHERE> clause:
40dbc108 389
35d4fe78 390 WHERE artist != 'Janis Joplin'
391 AND year < 1980
392 AND albumid IN (1, 14, 15, 65, 43)
393
d53178fd 394For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
40dbc108 395
396The search can also be modified by passing another hash with
397attributes:
398
24d34a80 399 my @albums = My::Schema->resultset('Album')->search(
35d4fe78 400 { artist => 'Bob Marley' },
401 { rows => 2, order_by => 'year DESC' }
402 );
403
404C<@albums> then holds the two most recent Bob Marley albums.
40dbc108 405
d53178fd 406For more information on what you can do with a L<DBIx::Class::ResultSet>, see
407L<DBIx::Class::ResultSet/METHODS>.
408
40dbc108 409For a complete overview of the available attributes, see
410L<DBIx::Class::ResultSet/ATTRIBUTES>.
076652e8 411
11736b4c 412=head1 NOTES
413
ef8f6e19 414=head2 The Significance and Importance of Primary Keys
415
416The concept of a L<primary key|DBIx::Class::ResultSource/set_primary_key> in
417DBIx::Class warrants special discussion. The formal definition (which somewhat
418resembles that of a classic RDBMS) is I<a unique constraint that is least
419likely to change after initial row creation>. However this is where the
d6988be8 420similarity ends. Any time you call a CRUD operation on a row (e.g.
ef8f6e19 421L<delete|DBIx::Class::Row/delete>,
422L<update|DBIx::Class::Row/update>,
423L<discard_changes|DBIx::Class::Row/discard_changes>,
d6988be8 424etc.) DBIx::Class will use the values of of the
ef8f6e19 425L<primary key|DBIx::Class::ResultSource/set_primary_key> columns to populate
d6988be8 426the C<WHERE> clause necessary to accomplish the operation. This is why it is
427important to declare a L<primary key|DBIx::Class::ResultSource/set_primary_key>
428on all your result sources B<even if the underlying RDBMS does not have one>.
429In a pinch one can always declare each row identifiable by all its columns:
ef8f6e19 430
431 __PACKAGE__->set_primary_keys (__PACKAGE__->columns);
432
d6988be8 433Note that DBIx::Class is smart enough to store a copy of the PK values before
434any row-object changes take place, so even if you change the values of PK
435columns the C<WHERE> clause will remain correct.
436
ef8f6e19 437If you elect not to declare a C<primary key>, DBIx::Class will behave correctly
438by throwing exceptions on any row operation that relies on unique identifiable
439rows. If you inherited datasets with multiple identical rows in them, you can
440still operate with such sets provided you only utilize
441L<DBIx::Class::ResultSet> CRUD methods:
442L<search|DBIx::Class::ResultSet/search>,
443L<update|DBIx::Class::ResultSet/update>,
444L<delete|DBIx::Class::ResultSet/delete>
445
d6988be8 446For example, the following would not work (assuming C<People> does not have
447a declared PK):
63ec9705 448
449 my $row = $schema->resultset('People')
d6988be8 450 ->search({ last_name => 'Dantes' })
451 ->next;
63ec9705 452 $row->update({ children => 2 }); # <-- exception thrown because $row isn't
453 # necessarily unique
454
455So instead the following should be done:
456
d6988be8 457 $schema->resultset('People')
458 ->search({ last_name => 'Dantes' })
459 ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2
ef8f6e19 460
11736b4c 461=head2 Problems on RHEL5/CentOS5
462
dc253b77 463There used to be an issue with the system perl on Red Hat Enterprise
464Linux 5, some versions of Fedora and derived systems. Further
465information on this can be found in L<DBIx::Class::Manual::Troubleshooting>
11736b4c 466
40dbc108 467=head1 SEE ALSO
076652e8 468
40dbc108 469=over 4
076652e8 470
40dbc108 471=item * L<DBIx::Class::Manual::Cookbook>
076652e8 472
40dbc108 473=back
076652e8 474
475=cut