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 Result objects.
69

SETTING UP DBIx::Class

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

NOTES

411   The Significance and Importance of Primary Keys
412       The concept of a primary key in DBIx::Class warrants special
413       discussion. The formal definition (which somewhat resembles that of a
414       classic RDBMS) is a unique constraint that is least likely to change
415       after initial row creation. However this is where the similarity ends.
416       Any time you call a CRUD operation on a row (e.g.  delete, update,
417       discard_changes, etc.) DBIx::Class will use the values of the primary
418       key columns to populate the "WHERE" clause necessary to accomplish the
419       operation. This is why it is important to declare a primary key on all
420       your result sources even if the underlying RDBMS does not have one.  In
421       a pinch one can always declare each row identifiable by all its
422       columns:
423
424        __PACKAGE__->set_primary_key(__PACKAGE__->columns);
425
426       Note that DBIx::Class is smart enough to store a copy of the PK values
427       before any row-object changes take place, so even if you change the
428       values of PK columns the "WHERE" clause will remain correct.
429
430       If you elect not to declare a "primary key", DBIx::Class will behave
431       correctly by throwing exceptions on any row operation that relies on
432       unique identifiable rows. If you inherited datasets with multiple
433       identical rows in them, you can still operate with such sets provided
434       you only utilize DBIx::Class::ResultSet CRUD methods: search, update,
435       delete
436
437       For example, the following would not work (assuming "People" does not
438       have a declared PK):
439
440        my $result = $schema->resultset('People')
441                          ->search({ last_name => 'Dantes' })
442                           ->next;
443        $result->update({ children => 2 }); # <-- exception thrown because $result isn't
444                                         # necessarily unique
445
446       So instead the following should be done:
447
448        $schema->resultset('People')
449                ->search({ last_name => 'Dantes' })
450                 ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2
451
452   Problems on RHEL5/CentOS5
453       There used to be an issue with the system perl on Red Hat Enterprise
454       Linux 5, some versions of Fedora and derived systems. Further
455       information on this can be found in
456       DBIx::Class::Manual::Troubleshooting
457

SEE ALSO

459       ยท   DBIx::Class::Manual::Cookbook
460

FURTHER QUESTIONS?

462       Check the list of additional DBIC resources.
463
465       This module is free software copyright by the DBIx::Class (DBIC)
466       authors. You can redistribute it and/or modify it under the same terms
467       as the DBIx::Class library.
468
469
470
471perl v5.30.0                      2019-07-26     DBIx::Class::Manual::Intro(3)
Impressum