Clarification cascade_* attribute defaults documentation
[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.
197Like L<Class::DBI::Loader>, it inspects your database, and automatically creates
198classes for all the tables in your database. Here's a simple setup:
076652e8 199
24d34a80 200 package My::Schema;
201 use base qw/DBIx::Class::Schema::Loader/;
40dbc108 202
3f073ddf 203 __PACKAGE__->loader_options( relationships => 1 );
076652e8 204
35d4fe78 205 1;
076652e8 206
d53178fd 207The actual autoloading process will occur when you create a connected instance
208of your schema below.
3f073ddf 209
d53178fd 210See the L<DBIx::Class::Schema::Loader> documentation for more information on its
211many options.
076652e8 212
5cc9fa32 213=head2 Connecting
214
6ba55998 215To connect to your Schema, you need to provide the connection details or a
216database handle.
217
218=head3 Via connection details
219
220The arguments are the same as for L<DBI/connect>:
5cc9fa32 221
222 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
223
d53178fd 224You can create as many different schema instances as you need. So if you have a
225second database you want to access:
5cc9fa32 226
227 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
228
d53178fd 229Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
230multiple connections, you need to do this manually.
5cc9fa32 231
48580715 232To execute some SQL statements on every connect you can add them as an option in
d53178fd 233a special fifth argument to connect:
3f073ddf 234
235 my $another_schema = My::Schema->connect(
236 $dsn,
237 $user,
238 $password,
239 $attrs,
240 { on_connect_do => \@on_connect_sql_statments }
241 );
5cc9fa32 242
e0b505d4 243See L<DBIx::Class::Storage::DBI/connect_info> for more information about
d53178fd 244this and other special C<connect>-time options.
5cc9fa32 245
6ba55998 246=head3 Via a database handle
247
248The supplied coderef is expected to return a single connected database handle
249(e.g. a L<DBI> C<$dbh>)
250
251 my $schema = My::Schema->connect (
252 sub { Some::DBH::Factory->connect },
253 \%extra_attrs,
254 );
255
40dbc108 256=head2 Basic usage
076652e8 257
35d4fe78 258Once you've defined the basic classes, either manually or using
5cc9fa32 259L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
260
d53178fd 261To access your database using your $schema object, you can fetch a
262L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
263calling the C<resultset> method.
5cc9fa32 264
35d4fe78 265The simplest way to get a record is by primary key:
076652e8 266
5cc9fa32 267 my $album = $schema->resultset('Album')->find(14);
076652e8 268
d53178fd 269This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
da7372ac 270return an instance of C<My::Schema::Result::Album> that represents this row. Once you
d53178fd 271have that row, you can access and update columns:
076652e8 272
35d4fe78 273 $album->title('Physical Graffiti');
274 my $title = $album->title; # $title holds 'Physical Graffiti'
076652e8 275
d53178fd 276If you prefer, you can use the C<set_column> and C<get_column> accessors
277instead:
076652e8 278
35d4fe78 279 $album->set_column('title', 'Presence');
280 $title = $album->get_column('title');
076652e8 281
18bb9eca 282Just like with L<Class::DBI>, you call C<update> to save your changes to the
283database (by executing the actual C<UPDATE> statement):
40dbc108 284
35d4fe78 285 $album->update;
076652e8 286
d53178fd 287If needed, you can throw away your local changes:
076652e8 288
35d4fe78 289 $album->discard_changes if $album->is_changed;
076652e8 290
d53178fd 291As you can see, C<is_changed> allows you to check if there are local changes to
292your object.
076652e8 293
40dbc108 294=head2 Adding and removing rows
076652e8 295
d53178fd 296To create a new record in the database, you can use the C<create> method. It
da7372ac 297returns an instance of C<My::Schema::Result::Album> that can be used to access the data
d53178fd 298in the new record:
076652e8 299
d56c3191 300 my $new_album = $schema->resultset('Album')->create({
35d4fe78 301 title => 'Wish You Were Here',
302 artist => 'Pink Floyd'
303 });
dfeba824 304
305Now you can add data to the new record:
306
35d4fe78 307 $new_album->label('Capitol');
308 $new_album->year('1975');
309 $new_album->update;
076652e8 310
d53178fd 311Likewise, you can remove it from the database:
076652e8 312
35d4fe78 313 $new_album->delete;
076652e8 314
d53178fd 315You can also remove records without retrieving them first, by calling delete
316directly on a ResultSet object.
076652e8 317
35d4fe78 318 # Delete all of Falco's albums
5cc9fa32 319 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
076652e8 320
40dbc108 321=head2 Finding your objects
076652e8 322
d53178fd 323L<DBIx::Class> provides a few different ways to retrieve data from your
324database. Here's one example:
35d4fe78 325
326 # Find all of Santana's albums
5cc9fa32 327 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
35d4fe78 328
d53178fd 329In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
330object. It can be used to peek at the first album returned by the database:
35d4fe78 331
332 my $album = $rs->first;
333 print $album->title;
076652e8 334
5cc9fa32 335You can loop over the albums and update each one:
076652e8 336
35d4fe78 337 while (my $album = $rs->next) {
338 print $album->artist . ' - ' . $album->title;
339 $album->year(2001);
340 $album->update;
341 }
a3c5e7e3 342
5cc9fa32 343Or, you can update them all at once:
344
345 $rs->update({ year => 2001 });
346
d53178fd 347In list context, the C<search> method returns all of the matching rows:
a3c5e7e3 348
35d4fe78 349 # Fetch immediately all of Carlos Santana's albums
5cc9fa32 350 my @albums = $schema->resultset('Album')->search(
351 { artist => 'Carlos Santana' }
352 );
35d4fe78 353 foreach my $album (@albums) {
354 print $album->artist . ' - ' . $album->title;
355 }
076652e8 356
40dbc108 357We also provide a handy shortcut for doing a C<LIKE> search:
076652e8 358
35d4fe78 359 # Find albums whose artist starts with 'Jimi'
24d34a80 360 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
076652e8 361
d53178fd 362Or you can provide your own C<WHERE> clause:
35d4fe78 363
364 # Find Peter Frampton albums from the year 1986
365 my $where = 'artist = ? AND year = ?';
366 my @bind = ( 'Peter Frampton', 1986 );
5cc9fa32 367 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
40dbc108 368
d53178fd 369The preferred way to generate complex queries is to provide a L<SQL::Abstract>
370construct to C<search>:
40dbc108 371
5cc9fa32 372 my $rs = $schema->resultset('Album')->search({
35d4fe78 373 artist => { '!=', 'Janis Joplin' },
374 year => { '<' => 1980 },
1aec4bac 375 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
35d4fe78 376 });
377
378This results in something like the following C<WHERE> clause:
40dbc108 379
35d4fe78 380 WHERE artist != 'Janis Joplin'
381 AND year < 1980
382 AND albumid IN (1, 14, 15, 65, 43)
383
d53178fd 384For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
40dbc108 385
386The search can also be modified by passing another hash with
387attributes:
388
24d34a80 389 my @albums = My::Schema->resultset('Album')->search(
35d4fe78 390 { artist => 'Bob Marley' },
391 { rows => 2, order_by => 'year DESC' }
392 );
393
394C<@albums> then holds the two most recent Bob Marley albums.
40dbc108 395
d53178fd 396For more information on what you can do with a L<DBIx::Class::ResultSet>, see
397L<DBIx::Class::ResultSet/METHODS>.
398
40dbc108 399For a complete overview of the available attributes, see
400L<DBIx::Class::ResultSet/ATTRIBUTES>.
076652e8 401
11736b4c 402=head1 NOTES
403
ef8f6e19 404=head2 The Significance and Importance of Primary Keys
405
406The concept of a L<primary key|DBIx::Class::ResultSource/set_primary_key> in
407DBIx::Class warrants special discussion. The formal definition (which somewhat
408resembles that of a classic RDBMS) is I<a unique constraint that is least
409likely to change after initial row creation>. However this is where the
d6988be8 410similarity ends. Any time you call a CRUD operation on a row (e.g.
ef8f6e19 411L<delete|DBIx::Class::Row/delete>,
412L<update|DBIx::Class::Row/update>,
413L<discard_changes|DBIx::Class::Row/discard_changes>,
d6988be8 414etc.) DBIx::Class will use the values of of the
ef8f6e19 415L<primary key|DBIx::Class::ResultSource/set_primary_key> columns to populate
d6988be8 416the C<WHERE> clause necessary to accomplish the operation. This is why it is
417important to declare a L<primary key|DBIx::Class::ResultSource/set_primary_key>
418on all your result sources B<even if the underlying RDBMS does not have one>.
419In a pinch one can always declare each row identifiable by all its columns:
ef8f6e19 420
421 __PACKAGE__->set_primary_keys (__PACKAGE__->columns);
422
d6988be8 423Note that DBIx::Class is smart enough to store a copy of the PK values before
424any row-object changes take place, so even if you change the values of PK
425columns the C<WHERE> clause will remain correct.
426
ef8f6e19 427If you elect not to declare a C<primary key>, DBIx::Class will behave correctly
428by throwing exceptions on any row operation that relies on unique identifiable
429rows. If you inherited datasets with multiple identical rows in them, you can
430still operate with such sets provided you only utilize
431L<DBIx::Class::ResultSet> CRUD methods:
432L<search|DBIx::Class::ResultSet/search>,
433L<update|DBIx::Class::ResultSet/update>,
434L<delete|DBIx::Class::ResultSet/delete>
435
d6988be8 436For example, the following would not work (assuming C<People> does not have
437a declared PK):
63ec9705 438
439 my $row = $schema->resultset('People')
d6988be8 440 ->search({ last_name => 'Dantes' })
441 ->next;
63ec9705 442 $row->update({ children => 2 }); # <-- exception thrown because $row isn't
443 # necessarily unique
444
445So instead the following should be done:
446
d6988be8 447 $schema->resultset('People')
448 ->search({ last_name => 'Dantes' })
449 ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2
ef8f6e19 450
11736b4c 451=head2 Problems on RHEL5/CentOS5
452
dc253b77 453There used to be an issue with the system perl on Red Hat Enterprise
454Linux 5, some versions of Fedora and derived systems. Further
455information on this can be found in L<DBIx::Class::Manual::Troubleshooting>
11736b4c 456
40dbc108 457=head1 SEE ALSO
076652e8 458
40dbc108 459=over 4
076652e8 460
40dbc108 461=item * L<DBIx::Class::Manual::Cookbook>
076652e8 462
40dbc108 463=back
076652e8 464
465=cut