DBIx::Class::Manual::Intro(3pm)

1DBIx::Class::Manual::InUtsreor(3C)ontributed Perl DocumeDnBtIaxt:i:oCnlass::Manual::Intro(3)
2
3
4

NAME

6       DBIx::Class::Manual::Intro - Introduction to DBIx::Class
7

INTRODUCTION

9       You're bored with SQL, and want a native Perl interface for your
10       database?  Or you've been doing this for a while with Class::DBI, and
11       think there's a better way?  You've come to the right place.
12

THE DBIx::Class WAY

14       Here are a few simple tips that will help you get your bearings with
15       DBIx::Class.
16
17   Tables become Result classes
18       DBIx::Class needs to know what your Table structure looks like.  You do
19       that by defining Result classes. Result classes are defined by calling
20       methods proxied to DBIx::Class::ResultSource.  Each Result class
21       defines one Table, which defines the Columns it has, along with any
22       Relationships it has to other tables.  (And oh, so much more besides)
23       The important thing to understand:
24
25         A Result class == Table
26
27       (most of the time, but just bear with my simplification)
28
29   It's all about the ResultSet
30       So, we've got some ResultSources defined.  Now, we want to actually use
31       those definitions to help us translate the queries we need into handy
32       perl objects!
33
34       Let's say we defined a ResultSource for an "album" table with three
35       columns: "albumid", "artist", and "title".  Any time we want to query
36       this table, we'll be creating a DBIx::Class::ResultSet from its
37       ResultSource.  For example, the results of:
38
39         SELECT albumid, artist, title FROM album;
40
41       Would be retrieved by creating a ResultSet object from the album
42       table's ResultSource, likely by using the "search" method.
43
44       DBIx::Class doesn't limit you to creating only simple ResultSets -- if
45       you wanted to do something like:
46
47         SELECT title FROM album GROUP BY title;
48
49       You could easily achieve it.
50
51       The important thing to understand:
52
53         Any time you would reach for a SQL query in DBI, you are
54         creating a DBIx::Class::ResultSet.
55
56   Search is like "prepare"
57       DBIx::Class tends to wait until it absolutely must fetch information
58       from the database.  If you are returning a ResultSet, the query won't
59       execute until you use a method that wants to access the data. (Such as
60       "next", or "first")
61
62       The important thing to understand:
63
64         Setting up a ResultSet does not execute the query; retrieving
65         the data does.
66
67   Search results are returned as Rows
68       Rows of the search from the database are blessed into DBIx::Class::Row
69       objects.
70

SETTING UP DBIx::Class

72       Let's look at how you can set and use your first native DBIx::Class
73       tree.
74
75       First we'll see how you can set up your classes yourself.  If you want
76       them to be auto-discovered, just skip to the next section, which shows
77       you how to use DBIx::Class::Schema::Loader.
78
79   Setting it up manually
80       First, you should create your base schema class, which inherits from
81       DBIx::Class::Schema:
82
83         package My::Schema;
84         use base qw/DBIx::Class::Schema/;
85
86       In this class you load your result_source ("table", "model") classes,
87       which we will define later, using the load_namespaces() method:
88
89         # load My::Schema::Result::* and their resultset classes
90         __PACKAGE__->load_namespaces();
91
92       By default this loads all the Result (Row) classes in the
93       My::Schema::Result:: namespace, and also any resultset classes in the
94       My::Schema::ResultSet:: namespace (if missing, the resultsets are
95       defaulted to be DBIx::Class::ResultSet objects). You can change the
96       result and resultset namespaces by using options to the
97       "load_namespaces" in DBIx::Class::Schema call.
98
99       It is also possible to do the same things manually by calling
100       "load_classes" for the Row classes and defining in those classes any
101       required resultset classes.
102
103       Next, create each of the classes you want to load as specified above:
104
105         package My::Schema::Result::Album;
106         use base qw/DBIx::Class::Core/;
107
108       Load any additional components you may need with the load_components()
109       method, and provide component configuration if required. For example,
110       if you want automatic row ordering:
111
112         __PACKAGE__->load_components(qw/ Ordered /);
113         __PACKAGE__->position_column('rank');
114
115       Set the table for your class:
116
117         __PACKAGE__->table('album');
118
119       Add columns to your class:
120
121         __PACKAGE__->add_columns(qw/ albumid artist title rank /);
122
123       Each column can also be set up with its own accessor, data_type and
124       other pieces of information that it may be useful to have -- just pass
125       "add_columns" a hash:
126
127         __PACKAGE__->add_columns(albumid =>
128                                   { accessor  => 'album',
129                                     data_type => 'integer',
130                                     size      => 16,
131                                     is_nullable => 0,
132                                     is_auto_increment => 1,
133                                     default_value => '',
134                                   },
135                                 artist =>
136                                   { data_type => 'integer',
137                                     size      => 16,
138                                     is_nullable => 0,
139                                     is_auto_increment => 0,
140                                     default_value => '',
141                                   },
142                                 title  =>
143                                   { data_type => 'varchar',
144                                     size      => 256,
145                                     is_nullable => 0,
146                                     is_auto_increment => 0,
147                                     default_value => '',
148                                   },
149                                 rank =>
150                                   { data_type => 'integer',
151                                     size      => 16,
152                                     is_nullable => 0,
153                                     is_auto_increment => 0,
154                                     default_value => '',
155                                   }
156                                );
157
158       DBIx::Class doesn't directly use most of this data yet, but various
159       related modules such as DBIx::Class::WebForm make use of it. Also it
160       allows you to create your database tables from your Schema, instead of
161       the other way around.  See "deploy" in DBIx::Class::Schema for details.
162
163       See DBIx::Class::ResultSource for more details of the possible column
164       attributes.
165
166       Accessors are created for each column automatically, so
167       My::Schema::Result::Album will have albumid() (or album(), when using
168       the accessor), artist() and title() methods.
169
170       Define a primary key for your class:
171
172         __PACKAGE__->set_primary_key('albumid');
173
174       If you have a multi-column primary key, just pass a list instead:
175
176         __PACKAGE__->set_primary_key( qw/ albumid artistid / );
177
178       Define this class' relationships with other classes using either
179       "belongs_to" to describe a column which contains an ID of another
180       Table, or "has_many" to make a predefined accessor for fetching objects
181       that contain this Table's foreign key:
182
183         # in My::Schema::Result::Artist
184         __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
185
186       See DBIx::Class::Relationship for more information about the various
187       types of available relationships and how you can design your own.
188
189   Using DBIx::Class::Schema::Loader
190       This is an external module, and not part of the DBIx::Class
191       distribution.  Like Class::DBI::Loader, it inspects your database, and
192       automatically creates classes for all the tables in your database.
193       Here's a simple setup:
194
195         package My::Schema;
196         use base qw/DBIx::Class::Schema::Loader/;
197
198         __PACKAGE__->loader_options( relationships => 1 );
199
200         1;
201
202       The actual autoloading process will occur when you create a connected
203       instance of your schema below.
204
205       See the DBIx::Class::Schema::Loader documentation for more information
206       on its many options.
207
208   Connecting
209       To connect to your Schema, you need to provide the connection details
210       or a database handle.
211
212       Via connection details
213
214       The arguments are the same as for "connect" in DBI:
215
216         my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
217
218       You can create as many different schema instances as you need. So if
219       you have a second database you want to access:
220
221         my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
222
223       Note that DBIx::Class::Schema does not cache connections for you. If
224       you use multiple connections, you need to do this manually.
225
226       To execute some SQL statements on every connect you can add them as an
227       option in a special fifth argument to connect:
228
229         my $another_schema = My::Schema->connect(
230             $dsn,
231             $user,
232             $password,
233             $attrs,
234             { on_connect_do => \@on_connect_sql_statments }
235         );
236
237       See "connect_info" in DBIx::Class::Schema::Storage::DBI for more
238       information about this and other special "connect"-time options.
239
240       Via a database handle
241
242       The supplied coderef is expected to return a single connected database
243       handle (e.g. a DBI $dbh)
244
245         my $schema = My::Schema->connect (
246           sub { Some::DBH::Factory->connect },
247           \%extra_attrs,
248         );
249
250   Basic usage
251       Once you've defined the basic classes, either manually or using
252       DBIx::Class::Schema::Loader, you can start interacting with your
253       database.
254
255       To access your database using your $schema object, you can fetch a
256       "ResultSet" in DBIx::Class::Manual::Glossary representing each of your
257       tables by calling the "resultset" method.
258
259       The simplest way to get a record is by primary key:
260
261         my $album = $schema->resultset('Album')->find(14);
262
263       This will run a "SELECT" with "albumid = 14" in the "WHERE" clause, and
264       return an instance of "My::Schema::Result::Album" that represents this
265       row.  Once you have that row, you can access and update columns:
266
267         $album->title('Physical Graffiti');
268         my $title = $album->title; # $title holds 'Physical Graffiti'
269
270       If you prefer, you can use the "set_column" and "get_column" accessors
271       instead:
272
273         $album->set_column('title', 'Presence');
274         $title = $album->get_column('title');
275
276       Just like with Class::DBI, you call "update" to save your changes to
277       the database (by executing the actual "UPDATE" statement):
278
279         $album->update;
280
281       If needed, you can throw away your local changes:
282
283         $album->discard_changes if $album->is_changed;
284
285       As you can see, "is_changed" allows you to check if there are local
286       changes to your object.
287
288   Adding and removing rows
289       To create a new record in the database, you can use the "create"
290       method.  It returns an instance of "My::Schema::Result::Album" that can
291       be used to access the data in the new record:
292
293         my $new_album = $schema->resultset('Album')->create({
294           title  => 'Wish You Were Here',
295           artist => 'Pink Floyd'
296         });
297
298       Now you can add data to the new record:
299
300         $new_album->label('Capitol');
301         $new_album->year('1975');
302         $new_album->update;
303
304       Likewise, you can remove it from the database:
305
306         $new_album->delete;
307
308       You can also remove records without retrieving them first, by calling
309       delete directly on a ResultSet object.
310
311         # Delete all of Falco's albums
312         $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
313
314   Finding your objects
315       DBIx::Class provides a few different ways to retrieve data from your
316       database.  Here's one example:
317
318         # Find all of Santana's albums
319         my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
320
321       In scalar context, as above, "search" returns a DBIx::Class::ResultSet
322       object.  It can be used to peek at the first album returned by the
323       database:
324
325         my $album = $rs->first;
326         print $album->title;
327
328       You can loop over the albums and update each one:
329
330         while (my $album = $rs->next) {
331           print $album->artist . ' - ' . $album->title;
332           $album->year(2001);
333           $album->update;
334         }
335
336       Or, you can update them all at once:
337
338         $rs->update({ year => 2001 });
339
340       In list context, the "search" method returns all of the matching rows:
341
342         # Fetch immediately all of Carlos Santana's albums
343         my @albums = $schema->resultset('Album')->search(
344           { artist => 'Carlos Santana' }
345         );
346         foreach my $album (@albums) {
347           print $album->artist . ' - ' . $album->title;
348         }
349
350       We also provide a handy shortcut for doing a "LIKE" search:
351
352         # Find albums whose artist starts with 'Jimi'
353         my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
354
355       Or you can provide your own "WHERE" clause:
356
357         # Find Peter Frampton albums from the year 1986
358         my $where = 'artist = ? AND year = ?';
359         my @bind  = ( 'Peter Frampton', 1986 );
360         my $rs    = $schema->resultset('Album')->search_literal( $where, @bind );
361
362       The preferred way to generate complex queries is to provide a
363       SQL::Abstract construct to "search":
364
365         my $rs = $schema->resultset('Album')->search({
366           artist  => { '!=', 'Janis Joplin' },
367           year    => { '<' => 1980 },
368           albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
369         });
370
371       This results in something like the following "WHERE" clause:
372
373         WHERE artist != 'Janis Joplin'
374           AND year < 1980
375           AND albumid IN (1, 14, 15, 65, 43)
376
377       For more examples of complex queries, see
378       DBIx::Class::Manual::Cookbook.
379
380       The search can also be modified by passing another hash with
381       attributes:
382
383         my @albums = My::Schema->resultset('Album')->search(
384           { artist => 'Bob Marley' },
385           { rows => 2, order_by => 'year DESC' }
386         );
387
388       @albums then holds the two most recent Bob Marley albums.
389
390       For more information on what you can do with a DBIx::Class::ResultSet,
391       see "METHODS" in DBIx::Class::ResultSet.
392
393       For a complete overview of the available attributes, see "ATTRIBUTES"
394       in DBIx::Class::ResultSet.
395

NOTES

397   Problems on RHEL5/CentOS5
398       There used to be an issue with the system perl on Red Hat Enterprise
399       Linux 5, some versions of Fedora and derived systems. Further
400       information on this can be found in
401       DBIx::Class::Manual::Troubleshooting
402

NAME

INTRODUCTION

THE DBIx::Class WAY

SETTING UP DBIx::Class

NOTES

SEE ALSO