Merge 'trunk' into 'DBIx-Class-current'
[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
40dbc108 7So, you are bored with SQL, and want a native Perl interface for your
8database? Or you've been doing this for a while with L<Class::DBI>,
35d4fe78 9and think there's a better way? You've come to the right place.
10Let's look at how you can set and use your first native L<DBIx::Class>
11tree.
076652e8 12
35d4fe78 13First we'll see how you can set up your classes yourself. If you want
40dbc108 14them to be auto-discovered, just skip to the next section, which shows
5cc9fa32 15you how to use L<DBIx::Class::Schema::Loader>.
076652e8 16
17=head2 Setting it up manually
18
5cc9fa32 19First, you should create your base schema class, which inherits from
20L<DBIx::Class::Schema>:
076652e8 21
5cc9fa32 22 package My::Schema;
23 use base qw/DBIx::Class::Schema/;
24
25In this class you load your result_source ("table", "model") classes, which
26we will define later, using the load_classes() method. You can specify which
27classes to load manually:
076652e8 28
5cc9fa32 29 # load My::Schema::Album and My::Schema::Artist
30 __PACKAGE__->load_classes(qw/ Album Artist /);
076652e8 31
5cc9fa32 32Or load classes by namespace:
076652e8 33
5cc9fa32 34 # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes
35 __PACKAGE__->load_classes(
36 {
37 'My::Schema' => [qw/ Album Artist /],
38 'My::OtherSchema' => [qw/ LinerNotes /]
39 }
40 );
41
42Or let your schema class load all classes in its namespace automatically:
076652e8 43
5cc9fa32 44 # load My::Schema::*
45 __PACKAGE__->load_classes();
076652e8 46
5cc9fa32 47Next, create each of the classes you want to load as specified above:
076652e8 48
5cc9fa32 49 package My::Schema::Album;
50 use base qw/DBIx::Class/;
35d4fe78 51
5cc9fa32 52Load any components required by each class with the load_components() method.
53This should consist of "Core" plus any additional components you want to use.
54For example, if you want serial/auto-incrementing primary keys:
076652e8 55
5cc9fa32 56 __PACKAGE__->load_components(qw/ PK::Auto Core /);
076652e8 57
5cc9fa32 58C<PK::Auto> is supported for many databases; see
59L<DBIx::Class::Storage::DBI> for more information.
076652e8 60
5cc9fa32 61Set the table for your class:
076652e8 62
35d4fe78 63 __PACKAGE__->table('album');
076652e8 64
5cc9fa32 65Add columns to your class:
66
67 __PACKAGE__->add_columns(qw/ albumid artist title /);
68
69Each column can also be set up with its own accessor, data_type and other
70pieces of information that it may be useful to have, just pass C<add_columns>
71a hash such as:
72
73 __PACKAGE__->add_columns(albumid =>
74 { accessor => 'album',
75 data_type => 'integer',
76 size => 16,
77 is_nullable => 0,
78 is_auto_increment => 1,
79 default_value => '',
80 },
81 artist =>
82 { data_type => 'integer',
83 size => 16,
84 is_nullable => 0,
85 is_auto_increment => 0,
86 default_value => '',
87 },
88 title =>
89 { data_type => 'varchar',
90 size => 256,
91 is_nullable => 0,
92 is_auto_increment => 0,
93 default_value => '',
94 }
95 );
96
97Most of this data isn't yet used directly by DBIx::Class, but various related
98modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you
99to create your database tables from your Schema, instead of the other way
100around. See L<SQL::Translator> for details.
101
102See L<DBIx::Class::ResultSource> for more details of the possible column
103attributes.
104
105Accessors are created for each column automatically, so My::Schema::Album will
106have albumid() (or album(), when using the accessor), artist() and title()
107methods.
108
109Define a primary key for your class:
076652e8 110
5cc9fa32 111 __PACKAGE__->set_primary_key('albumid');
076652e8 112
5cc9fa32 113If you have a multi-column primary key, just pass a list instead:
076652e8 114
5cc9fa32 115 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
076652e8 116
5cc9fa32 117Define relationships that the class has with any other classes by using
118either C<belongs_to> to describe a column which contains an ID of another
119table, or C<has_many> to make a predefined accessor for fetching objects
120that contain this tables foreign key in one of their columns:
121
122 __PACKAGE__->has_many('albums', 'My::Schema::Artist', 'album_id');
076652e8 123
5cc9fa32 124More information about the various types of relationships available, and
125how you can design your own, can be found in L<DBIx::Class::Relationship>.
076652e8 126
076652e8 127
5cc9fa32 128=head2 Using L<DBIx::Class::Schema::Loader>
076652e8 129
5cc9fa32 130This is an external module, and not part of the L<DBIx::Class>
40dbc108 131distribution. Like L<Class::DBI::Loader>, it inspects your database,
35d4fe78 132and automatically creates classes for all the tables in your database.
133Here's a simple setup:
076652e8 134
24d34a80 135 package My::Schema;
136 use base qw/DBIx::Class::Schema::Loader/;
40dbc108 137
24d34a80 138 __PACKAGE__->load_from_connection(
139 connect_info = [ 'dbi:SQLite:/home/me/myapp/my.db' ]
35d4fe78 140 );
076652e8 141
35d4fe78 142 1;
076652e8 143
35d4fe78 144This should be equivalent to the manual setup in the section above.
5cc9fa32 145L<DBIx::Class::Schema::Loader> takes lots of other options. For more
40dbc108 146information, consult its documentation.
076652e8 147
5cc9fa32 148=head2 Connecting
149
150L<DBIx::Class::Schema::Loader> already contains the connection info for the
151database, so to get started all you need to do is create an instance of your
152class:
153
24d34a80 154 my $schema = My::Schema->new();
5cc9fa32 155
156To connect to your manually created Schema, you also need to provide the
157connection details:
158
159 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
160
161You can create as many different schema instances as you need. So if you have
162a second database you want to access:
163
164 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
165
880a1a0c 166Note that L<DBIx::Class::Schema> does not cache connections for you. If you
5cc9fa32 167use multiple connections, you need to do this manually.
168
169To execute some sql statements on every connect you can pass them to your schema after the connect:
170
171 $schema->storage->on_connect_do(\@on_connect_sql_statments);
172
40dbc108 173=head2 Basic usage
076652e8 174
35d4fe78 175Once you've defined the basic classes, either manually or using
5cc9fa32 176L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
177
178To access your database using your $schema object, you can fetch a L<DBIx::Class::Manual::Glossary/"ResultSet">
179representing each of your tables by calling the ->resultset method.
180
35d4fe78 181The simplest way to get a record is by primary key:
076652e8 182
5cc9fa32 183 my $album = $schema->resultset('Album')->find(14);
076652e8 184
35d4fe78 185This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause,
24d34a80 186and return an instance of C<My::Schema::Album> that represents this
35d4fe78 187row. Once you have that row, you can access and update columns:
076652e8 188
35d4fe78 189 $album->title('Physical Graffiti');
190 my $title = $album->title; # $title holds 'Physical Graffiti'
076652e8 191
40dbc108 192If you prefer, you can use the C<set_column> and C<get_column>
193accessors instead:
076652e8 194
35d4fe78 195 $album->set_column('title', 'Presence');
196 $title = $album->get_column('title');
076652e8 197
5cc9fa32 198Just like with L<Class::DBI>, you call C<update> to commit your
40dbc108 199changes to the database:
200
35d4fe78 201 $album->update;
076652e8 202
35d4fe78 203If needed, you can throw away your local changes like this:
076652e8 204
35d4fe78 205 $album->discard_changes if $album->is_changed;
076652e8 206
40dbc108 207As you can see, C<is_changed> allows you to check if there are local
208changes to your object.
076652e8 209
40dbc108 210=head2 Adding and removing rows
076652e8 211
40dbc108 212To create a new record in the database, you can use the C<create>
24d34a80 213method. It returns an instance of C<My::Schema::Album> that can be
35d4fe78 214used to access the data in the new record:
076652e8 215
5cc9fa32 216 my $new_album = $schema->resultset('Album')->create({
35d4fe78 217 title => 'Wish You Were Here',
218 artist => 'Pink Floyd'
219 });
dfeba824 220
221Now you can add data to the new record:
222
35d4fe78 223 $new_album->label('Capitol');
224 $new_album->year('1975');
225 $new_album->update;
076652e8 226
35d4fe78 227Likewise, you can remove it from the database like this:
076652e8 228
35d4fe78 229 $new_album->delete;
076652e8 230
5cc9fa32 231You can also remove records without retrieving them first, by calling
232delete directly on a ResultSet object.
076652e8 233
35d4fe78 234 # Delete all of Falco's albums
5cc9fa32 235 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
076652e8 236
40dbc108 237=head2 Finding your objects
076652e8 238
40dbc108 239L<DBIx::Class> provides a few different ways to retrieve data from
35d4fe78 240your database. Here's one example:
241
242 # Find all of Santana's albums
5cc9fa32 243 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
35d4fe78 244
245In scalar context, as above, C<search> returns a
246L<DBIx::Class::ResultSet> object. It can be used to peek at the first
247album returned by the database:
248
249 my $album = $rs->first;
250 print $album->title;
076652e8 251
5cc9fa32 252You can loop over the albums and update each one:
076652e8 253
35d4fe78 254 while (my $album = $rs->next) {
255 print $album->artist . ' - ' . $album->title;
256 $album->year(2001);
257 $album->update;
258 }
a3c5e7e3 259
5cc9fa32 260Or, you can update them all at once:
261
262 $rs->update({ year => 2001 });
263
35d4fe78 264For more information on what you can do with a
265L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>.
a3c5e7e3 266
35d4fe78 267In list context, the C<search> method returns all of the matching
a3c5e7e3 268rows:
269
35d4fe78 270 # Fetch immediately all of Carlos Santana's albums
5cc9fa32 271 my @albums = $schema->resultset('Album')->search(
272 { artist => 'Carlos Santana' }
273 );
35d4fe78 274 foreach my $album (@albums) {
275 print $album->artist . ' - ' . $album->title;
276 }
076652e8 277
40dbc108 278We also provide a handy shortcut for doing a C<LIKE> search:
076652e8 279
35d4fe78 280 # Find albums whose artist starts with 'Jimi'
24d34a80 281 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
076652e8 282
5cc9fa32 283Or you can provide your own C<WHERE> clause, like:
35d4fe78 284
285 # Find Peter Frampton albums from the year 1986
286 my $where = 'artist = ? AND year = ?';
287 my @bind = ( 'Peter Frampton', 1986 );
5cc9fa32 288 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
40dbc108 289
290The preferred way to generate complex queries is to provide a
291L<SQL::Abstract> construct to C<search>:
292
5cc9fa32 293 my $rs = $schema->resultset('Album')->search({
35d4fe78 294 artist => { '!=', 'Janis Joplin' },
295 year => { '<' => 1980 },
296 albumid => [ 1, 14, 15, 65, 43 ]
297 });
298
299This results in something like the following C<WHERE> clause:
40dbc108 300
35d4fe78 301 WHERE artist != 'Janis Joplin'
302 AND year < 1980
303 AND albumid IN (1, 14, 15, 65, 43)
304
305For more examples of complex queries, see
40dbc108 306L<DBIx::Class::Manual::Cookbook>.
307
308The search can also be modified by passing another hash with
309attributes:
310
24d34a80 311 my @albums = My::Schema->resultset('Album')->search(
35d4fe78 312 { artist => 'Bob Marley' },
313 { rows => 2, order_by => 'year DESC' }
314 );
315
316C<@albums> then holds the two most recent Bob Marley albums.
40dbc108 317
318For a complete overview of the available attributes, see
319L<DBIx::Class::ResultSet/ATTRIBUTES>.
076652e8 320
40dbc108 321=head1 SEE ALSO
076652e8 322
40dbc108 323=over 4
076652e8 324
40dbc108 325=item * L<DBIx::Class::Manual::Cookbook>
076652e8 326
40dbc108 327=back
076652e8 328
329=cut