1DBIx::Class::Manual::InUtsreor(3C)ontributed Perl DocumeDnBtIaxt:i:oCnlass::Manual::Intro(3)
2
6 DBIx::Class::Manual::Intro - Introduction to DBIx::Class
7
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
14 Here are a few simple tips that will help you get your bearings with
15 DBIx::Class.
16 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 A Result class == Table
26 (most of the time, but just bear with my simplification)
28 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 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 SELECT albumid, artist, title FROM album;
40 Would be retrieved by creating a ResultSet object from the album
42 table's ResultSource, likely by using the "search" method.
43 DBIx::Class doesn't limit you to creating only simple ResultSets -- if
45 you wanted to do something like:
46 SELECT title FROM album GROUP BY title;
48 You could easily achieve it.
50 The important thing to understand:
52 Any time you would reach for a SQL query in DBI, you are
54 creating a DBIx::Class::ResultSet.
55 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 The important thing to understand:
63 Setting up a ResultSet does not execute the query; retrieving
65 the data does.
66 Search results are returned as Rows
68 Rows of the search from the database are blessed into Result objects.
69
71 Let's look at how you can set and use your first native DBIx::Class
72 tree.
73 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 Setting it up manually
79 First, you should create your base schema class, which inherits from
80 DBIx::Class::Schema:
81 package My::Schema;
83 use base qw/DBIx::Class::Schema/;
84 In this class you load your result_source ("table", "model") classes,
86 which we will define later, using the load_namespaces() method:
87 # load My::Schema::Result::* and their resultset classes
89 __PACKAGE__->load_namespaces();
90 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 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 Next, create each of the classes you want to load as specified above:
103 package My::Schema::Result::Album;
105 use base qw/DBIx::Class::Core/;
106 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 __PACKAGE__->load_components(qw/ Ordered /);
112 __PACKAGE__->position_column('rank');
113 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 Set the table for your class:
121 __PACKAGE__->table('album');
123 Add columns to your class:
125 __PACKAGE__->add_columns(qw/ albumid artist title rank /);
127 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 __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 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 See DBIx::Class::ResultSource for more details of the possible column
164 attributes.
165 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 Define a primary key for your class:
171 __PACKAGE__->set_primary_key('albumid');
173 If you have a multi-column primary key, just pass a list instead:
175 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
177 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 # in My::Schema::Result::Artist
184 __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
185 See DBIx::Class::Relationship for more information about the various
187 types of available relationships and how you can design your own.
188 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 The simplest way to use it is via the dbicdump script from the
195 DBIx::Class::Schema::Loader distribution. For example:
196 $ dbicdump -o dump_directory=./lib \
198 -o components='["InflateColumn::DateTime"]' \
199 MyApp::Schema dbi:mysql:mydb user pass
200 If you have a mixed-case database, use the "preserve_case" option,
202 e.g.:
203 $ dbicdump -o dump_directory=./lib -o preserve_case=1 \
205 -o components='["InflateColumn::DateTime"]' \
206 MyApp::Schema dbi:mysql:mydb user pass
207 If you are using Catalyst, then you can use the helper that comes with
209 Catalyst::Model::DBIC::Schema:
210 $ 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 See Catalyst::Helper::Model::DBIC::Schema for more information on this
216 helper.
217 See the DBIx::Class::Schema::Loader and
219 DBIx::Class::Schema::Loader::Base documentation for more information on
220 the many loader options.
221 Connecting
223 To connect to your Schema, you need to provide the connection details
224 or a database handle.
225 Via connection details
227 The arguments are the same as for "connect" in DBI:
229 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
231 You can create as many different schema instances as you need. So if
233 you have a second database you want to access:
234 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
236 Note that DBIx::Class::Schema does not cache connections for you. If
238 you use multiple connections, you need to do this manually.
239 To execute some SQL statements on every connect you can add them as an
241 option in a special fifth argument to connect:
242 my $another_schema = My::Schema->connect(
244 $dsn,
245 $user,
246 $password,
247 $attrs,
248 { on_connect_do => \@on_connect_sql_statments }
249 );
250 See "connect_info" in DBIx::Class::Storage::DBI for more information
252 about this and other special "connect"-time options.
253 Via a database handle
255 The supplied coderef is expected to return a single connected database
257 handle (e.g. a DBI $dbh)
258 my $schema = My::Schema->connect (
260 sub { Some::DBH::Factory->connect },
261 \%extra_attrs,
262 );
263 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 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 The simplest way to get a record is by primary key:
274 my $album = $schema->resultset('Album')->find(14);
276 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 $album->title('Physical Graffiti');
282 my $title = $album->title; # $title holds 'Physical Graffiti'
283 If you prefer, you can use the "set_column" and "get_column" accessors
285 instead:
286 $album->set_column('title', 'Presence');
288 $title = $album->get_column('title');
289 Just like with Class::DBI, you call "update" to save your changes to
291 the database (by executing the actual "UPDATE" statement):
292 $album->update;
294 If needed, you can throw away your local changes:
296 $album->discard_changes if $album->is_changed;
298 As you can see, "is_changed" allows you to check if there are local
300 changes to your object.
301 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 my $new_album = $schema->resultset('Album')->create({
308 title => 'Wish You Were Here',
309 artist => 'Pink Floyd'
310 });
311 Now you can add data to the new record:
313 $new_album->label('Capitol');
315 $new_album->year('1975');
316 $new_album->update;
317 Likewise, you can remove it from the database:
319 $new_album->delete;
321 You can also remove records without retrieving them first, by calling
323 delete directly on a ResultSet object.
324 # Delete all of Falco's albums
326 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
327 Finding your objects
329 DBIx::Class provides a few different ways to retrieve data from your
330 database. Here's one example:
331 # Find all of Santana's albums
333 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
334 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 my $album = $rs->first;
340 print $album->title;
341 You can loop over the albums and update each one:
343 while (my $album = $rs->next) {
345 print $album->artist . ' - ' . $album->title;
346 $album->year(2001);
347 $album->update;
348 }
349 Or, you can update them all at once:
351 $rs->update({ year => 2001 });
353 In list context, the "search" method returns all of the matching rows:
355 # 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 We also provide a handy shortcut for doing a "LIKE" search:
365 # Find albums whose artist starts with 'Jimi'
367 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
368 Or you can provide your own "WHERE" clause:
370 # 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 The preferred way to generate complex queries is to provide a
377 SQL::Abstract construct to "search":
378 my $rs = $schema->resultset('Album')->search({
380 artist => { '!=', 'Janis Joplin' },
381 year => { '<' => 1980 },
382 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
383 });
384 This results in something like the following "WHERE" clause:
386 WHERE artist != 'Janis Joplin'
388 AND year < 1980
389 AND albumid IN (1, 14, 15, 65, 43)
390 For more examples of complex queries, see
392 DBIx::Class::Manual::Cookbook.
393 The search can also be modified by passing another hash with
395 attributes:
396 my @albums = My::Schema->resultset('Album')->search(
398 { artist => 'Bob Marley' },
399 { rows => 2, order_by => { -desc => 'year' } }
400 );
401 @albums then holds the two most recent Bob Marley albums.
403 For more information on what you can do with a DBIx::Class::ResultSet,
405 see "METHODS" in DBIx::Class::ResultSet.
406 For a complete overview of the available attributes, see "ATTRIBUTES"
408 in DBIx::Class::ResultSet.
409
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 __PACKAGE__->set_primary_key(__PACKAGE__->columns);
425 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 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 For example, the following would not work (assuming "People" does not
438 have a declared PK):
439 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 So instead the following should be done:
447 $schema->resultset('People')
449 ->search({ last_name => 'Dantes' })
450 ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2
451 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
459 ยท DBIx::Class::Manual::Cookbook
460
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.
468perl v5.28.0 2017-12-08 DBIx::Class::Manual::Intro(3)