Tiny POD formatting fix
[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
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
32definitions to help us translate the queries we need into handy perl objects!
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
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
4b0779f4 49You could easily achieve it.
50
51The important thing to understand:
52
d53178fd 53 Any time you would reach for a SQL query in DBI, you are
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;
5cc9fa32 108 use base qw/DBIx::Class/;
35d4fe78 109
5cc9fa32 110Load any components required by each class with the load_components() method.
111This should consist of "Core" plus any additional components you want to use.
112For example, if you want serial/auto-incrementing primary keys:
076652e8 113
5cc9fa32 114 __PACKAGE__->load_components(qw/ PK::Auto Core /);
076652e8 115
d53178fd 116C<PK::Auto> is supported for many databases; see L<DBIx::Class::Storage::DBI>
117for more information.
076652e8 118
5cc9fa32 119Set the table for your class:
076652e8 120
35d4fe78 121 __PACKAGE__->table('album');
076652e8 122
5cc9fa32 123Add columns to your class:
124
125 __PACKAGE__->add_columns(qw/ albumid artist title /);
126
d53178fd 127Each column can also be set up with its own accessor, data_type and other pieces
128of information that it may be useful to have -- just pass C<add_columns> a hash:
5cc9fa32 129
130 __PACKAGE__->add_columns(albumid =>
131 { accessor => 'album',
132 data_type => 'integer',
133 size => 16,
134 is_nullable => 0,
135 is_auto_increment => 1,
136 default_value => '',
137 },
138 artist =>
139 { data_type => 'integer',
140 size => 16,
141 is_nullable => 0,
142 is_auto_increment => 0,
143 default_value => '',
144 },
145 title =>
146 { data_type => 'varchar',
147 size => 256,
148 is_nullable => 0,
149 is_auto_increment => 0,
150 default_value => '',
151 }
152 );
153
d53178fd 154DBIx::Class doesn't directly use most of this data yet, but various related
155modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you to
156create your database tables from your Schema, instead of the other way around.
157See L<SQL::Translator> for details.
5cc9fa32 158
159See L<DBIx::Class::ResultSource> for more details of the possible column
160attributes.
161
da7372ac 162Accessors are created for each column automatically, so My::Schema::Result::Album will
5cc9fa32 163have albumid() (or album(), when using the accessor), artist() and title()
164methods.
165
166Define a primary key for your class:
076652e8 167
5cc9fa32 168 __PACKAGE__->set_primary_key('albumid');
076652e8 169
5cc9fa32 170If you have a multi-column primary key, just pass a list instead:
076652e8 171
5cc9fa32 172 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
076652e8 173
d53178fd 174Define this class' relationships with other classes using either C<belongs_to>
175to describe a column which contains an ID of another Table, or C<has_many> to
176make a predefined accessor for fetching objects that contain this Table's
177foreign key:
5cc9fa32 178
da7372ac 179 __PACKAGE__->has_many('albums', 'My::Schema::Result::Artist', 'album_id');
076652e8 180
d53178fd 181See L<DBIx::Class::Relationship> for more information about the various types of
182available relationships and how you can design your own.
076652e8 183
5cc9fa32 184=head2 Using L<DBIx::Class::Schema::Loader>
076652e8 185
d53178fd 186This is an external module, and not part of the L<DBIx::Class> distribution.
187Like L<Class::DBI::Loader>, it inspects your database, and automatically creates
188classes for all the tables in your database. Here's a simple setup:
076652e8 189
24d34a80 190 package My::Schema;
191 use base qw/DBIx::Class::Schema::Loader/;
40dbc108 192
3f073ddf 193 __PACKAGE__->loader_options( relationships => 1 );
076652e8 194
35d4fe78 195 1;
076652e8 196
d53178fd 197The actual autoloading process will occur when you create a connected instance
198of your schema below.
3f073ddf 199
d53178fd 200See the L<DBIx::Class::Schema::Loader> documentation for more information on its
201many options.
076652e8 202
5cc9fa32 203=head2 Connecting
204
d53178fd 205To connect to your Schema, you need to provide the connection details. The
206arguments are the same as for L<DBI/connect>:
5cc9fa32 207
208 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
209
d53178fd 210You can create as many different schema instances as you need. So if you have a
211second database you want to access:
5cc9fa32 212
213 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
214
d53178fd 215Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
216multiple connections, you need to do this manually.
5cc9fa32 217
d53178fd 218To execute some sql statements on every connect you can add them as an option in
219a special fifth argument to connect:
3f073ddf 220
221 my $another_schema = My::Schema->connect(
222 $dsn,
223 $user,
224 $password,
225 $attrs,
226 { on_connect_do => \@on_connect_sql_statments }
227 );
5cc9fa32 228
d53178fd 229See L<DBIx::Class::Schema::Storage::DBI/connect_info> for more information about
230this and other special C<connect>-time options.
5cc9fa32 231
40dbc108 232=head2 Basic usage
076652e8 233
35d4fe78 234Once you've defined the basic classes, either manually or using
5cc9fa32 235L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
236
d53178fd 237To access your database using your $schema object, you can fetch a
238L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
239calling the C<resultset> method.
5cc9fa32 240
35d4fe78 241The simplest way to get a record is by primary key:
076652e8 242
5cc9fa32 243 my $album = $schema->resultset('Album')->find(14);
076652e8 244
d53178fd 245This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
da7372ac 246return an instance of C<My::Schema::Result::Album> that represents this row. Once you
d53178fd 247have that row, you can access and update columns:
076652e8 248
35d4fe78 249 $album->title('Physical Graffiti');
250 my $title = $album->title; # $title holds 'Physical Graffiti'
076652e8 251
d53178fd 252If you prefer, you can use the C<set_column> and C<get_column> accessors
253instead:
076652e8 254
35d4fe78 255 $album->set_column('title', 'Presence');
256 $title = $album->get_column('title');
076652e8 257
d53178fd 258Just like with L<Class::DBI>, you call C<update> to commit your changes to the
259database:
40dbc108 260
35d4fe78 261 $album->update;
076652e8 262
d53178fd 263If needed, you can throw away your local changes:
076652e8 264
35d4fe78 265 $album->discard_changes if $album->is_changed;
076652e8 266
d53178fd 267As you can see, C<is_changed> allows you to check if there are local changes to
268your object.
076652e8 269
40dbc108 270=head2 Adding and removing rows
076652e8 271
d53178fd 272To create a new record in the database, you can use the C<create> method. It
da7372ac 273returns an instance of C<My::Schema::Result::Album> that can be used to access the data
d53178fd 274in the new record:
076652e8 275
5cc9fa32 276 my $new_album = $schema->resultset('Album')->create({
35d4fe78 277 title => 'Wish You Were Here',
278 artist => 'Pink Floyd'
279 });
dfeba824 280
281Now you can add data to the new record:
282
35d4fe78 283 $new_album->label('Capitol');
284 $new_album->year('1975');
285 $new_album->update;
076652e8 286
d53178fd 287Likewise, you can remove it from the database:
076652e8 288
35d4fe78 289 $new_album->delete;
076652e8 290
d53178fd 291You can also remove records without retrieving them first, by calling delete
292directly on a ResultSet object.
076652e8 293
35d4fe78 294 # Delete all of Falco's albums
5cc9fa32 295 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
076652e8 296
40dbc108 297=head2 Finding your objects
076652e8 298
d53178fd 299L<DBIx::Class> provides a few different ways to retrieve data from your
300database. Here's one example:
35d4fe78 301
302 # Find all of Santana's albums
5cc9fa32 303 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
35d4fe78 304
d53178fd 305In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
306object. It can be used to peek at the first album returned by the database:
35d4fe78 307
308 my $album = $rs->first;
309 print $album->title;
076652e8 310
5cc9fa32 311You can loop over the albums and update each one:
076652e8 312
35d4fe78 313 while (my $album = $rs->next) {
314 print $album->artist . ' - ' . $album->title;
315 $album->year(2001);
316 $album->update;
317 }
a3c5e7e3 318
5cc9fa32 319Or, you can update them all at once:
320
321 $rs->update({ year => 2001 });
322
d53178fd 323In list context, the C<search> method returns all of the matching rows:
a3c5e7e3 324
35d4fe78 325 # Fetch immediately all of Carlos Santana's albums
5cc9fa32 326 my @albums = $schema->resultset('Album')->search(
327 { artist => 'Carlos Santana' }
328 );
35d4fe78 329 foreach my $album (@albums) {
330 print $album->artist . ' - ' . $album->title;
331 }
076652e8 332
40dbc108 333We also provide a handy shortcut for doing a C<LIKE> search:
076652e8 334
35d4fe78 335 # Find albums whose artist starts with 'Jimi'
24d34a80 336 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
076652e8 337
d53178fd 338Or you can provide your own C<WHERE> clause:
35d4fe78 339
340 # Find Peter Frampton albums from the year 1986
341 my $where = 'artist = ? AND year = ?';
342 my @bind = ( 'Peter Frampton', 1986 );
5cc9fa32 343 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
40dbc108 344
d53178fd 345The preferred way to generate complex queries is to provide a L<SQL::Abstract>
346construct to C<search>:
40dbc108 347
5cc9fa32 348 my $rs = $schema->resultset('Album')->search({
35d4fe78 349 artist => { '!=', 'Janis Joplin' },
350 year => { '<' => 1980 },
1aec4bac 351 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
35d4fe78 352 });
353
354This results in something like the following C<WHERE> clause:
40dbc108 355
35d4fe78 356 WHERE artist != 'Janis Joplin'
357 AND year < 1980
358 AND albumid IN (1, 14, 15, 65, 43)
359
d53178fd 360For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
40dbc108 361
362The search can also be modified by passing another hash with
363attributes:
364
24d34a80 365 my @albums = My::Schema->resultset('Album')->search(
35d4fe78 366 { artist => 'Bob Marley' },
367 { rows => 2, order_by => 'year DESC' }
368 );
369
370C<@albums> then holds the two most recent Bob Marley albums.
40dbc108 371
d53178fd 372For more information on what you can do with a L<DBIx::Class::ResultSet>, see
373L<DBIx::Class::ResultSet/METHODS>.
374
40dbc108 375For a complete overview of the available attributes, see
376L<DBIx::Class::ResultSet/ATTRIBUTES>.
076652e8 377
11736b4c 378=head1 NOTES
379
380=head2 Problems on RHEL5/CentOS5
381
dc253b77 382There used to be an issue with the system perl on Red Hat Enterprise
383Linux 5, some versions of Fedora and derived systems. Further
384information on this can be found in L<DBIx::Class::Manual::Troubleshooting>
11736b4c 385
40dbc108 386=head1 SEE ALSO
076652e8 387
40dbc108 388=over 4
076652e8 389
40dbc108 390=item * L<DBIx::Class::Manual::Cookbook>
076652e8 391
40dbc108 392=back
076652e8 393
394=cut