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

1DBIx::Class::Manual::FeUasteurreCso(n3t)ributed Perl DocDuBmIexn:t:aCtliaosns::Manual::Features(3)
2
3
4

NAME

6       DBIx::Class::Manual::Features - A boatload of DBIx::Class features with
7       links to respective documentation
8

General ORM

26       These are things that are in most other ORMs, but are still reasons to
27       use DBIC over raw SQL.
28
29   Cross DB
30       The vast majority of code should run on all databases without needing
31       tweaking
32
33   Basic CRUD
34       C - Create
35       R - Retrieve
36       U - Update
37       D - Delete
38
39   SQL: Create
40        my $sth = $dbh->prepare('
41           INSERT INTO books
42           (title, author_id)
43           values (?,?)
44        ');
45
46        $sth->execute( 'A book title', $author_id );
47
48   DBIC: Create
49        my $book = $book_rs->create({
50           title     => 'A book title',
51           author_id => $author_id,
52        });
53
54       See "create" in DBIx::Class::ResultSet
55
56       No need to pair placeholders and values
57       Automatically gets autoincremented id for you
58       Transparently uses INSERT ... RETURNING for databases that support it
59
60   SQL: Read
61        my $sth = $dbh->prepare('
62           SELECT title,
63           authors.name as author_name
64           FROM books, authors
65           WHERE books.author = authors.id
66        ');
67
68        while ( my $book = $sth->fetchrow_hashref ) {
69          say "Author of $book->{title} is $book->{author_name}";
70        }
71
72   DBIC: Read
73        my $book = $book_rs->find($book_id);
74
75       or
76
77        my $book = $book_rs->search({ title => 'A book title' }, { rows => 1 })->next;
78
79       or
80
81        my @books = $book_rs->search({ author => $author_id })->all;
82
83       or
84
85        while( my $book = $books_rs->next ) {
86          printf "Author of %s is %s\n", $book->title, $book->author->name;
87        }
88
89       See "find" in DBIx::Class::ResultSet, "search" in
90       DBIx::Class::ResultSet, "next" in DBIx::Class::ResultSet, and "all" in
91       DBIx::Class::ResultSet
92
93       TMTOWTDI!
94
95   SQL: Update
96        my $update = $dbh->prepare('
97           UPDATE books
98           SET title = ?
99           WHERE id = ?
100        ');
101
102        $update->execute( 'New title', $book_id );
103
104   DBIC: Update
105        $book->update({ title => 'New title' });
106
107       See "update" in DBIx::Class::Row
108
109       Will not update unless value changes
110
111   SQL: Delete
112        my $delete = $dbh->prepare('DELETE FROM books WHERE id = ?');
113
114        $delete->execute($book_id);
115
116   DBIC: Delete
117        $book->delete
118
119       See "delete" in DBIx::Class::Row
120
121   SQL: Search
122        my $sth = $dbh->prepare('
123          SELECT title,
124          authors.name as author_name
125          FROM books
126          WHERE books.name LIKE "%monte cristo%" AND
127          books.topic = "jailbreak"
128        ');
129
130   DBIC: Search
131        my $book = $book_rs->search({
132           'me.name'  => { -like => '%monte cristo%' },
133           'me.topic' => 'jailbreak',
134        })->next;
135
136       See SQL::Abstract, "next" in DBIx::Class::ResultSet, and "search" in
137       DBIx::Class::ResultSet
138       (kinda) introspectible
139       Prettier than SQL
140
141   OO Overridability
142       Override new if you want to do validation
143       Override delete if you want to disable deletion
144       and on and on
145
146   Convenience Methods
147       "find_or_create" in DBIx::Class::ResultSet
148       "update_or_create" in DBIx::Class::ResultSet
149
150   Non-column methods
151       Need a method to get a user's gravatar URL?  Add a "gravatar_url"
152       method to the Result class
153
154   RELATIONSHIPS
155       "belongs_to" in DBIx::Class::Relationship
156       "has_many" in DBIx::Class::Relationship
157       "might_have" in DBIx::Class::Relationship
158       "has_one" in DBIx::Class::Relationship
159       "many_to_many" in DBIx::Class::Relationship
160       SET AND FORGET
161

DBIx::Class Specific Features

163       These things may be in other ORM's, but they are very specific, so
164       doubtful
165
166   ->deploy
167       Create a database from your DBIx::Class schema.
168
169        my $schema = Frew::Schema->connect( $dsn, $user, $pass );
170
171        $schema->deploy
172
173       See "deploy" in DBIx::Class::Schema.
174
175       See also: DBIx::Class::DeploymentHandler
176
177   Schema::Loader
178       Create a DBIx::Class schema from your database.
179
180        package Frew::Schema;
181
182        use strict;
183        use warnings;
184
185        use base 'DBIx::Class::Schema::Loader';
186
187        __PACKAGE__->loader_options({
188           naming => 'v7',
189           debug  => $ENV{DBIC_TRACE},
190        });
191
192        1;
193
194        # elsewhere...
195
196        my $schema = Frew::Schema->connect( $dsn, $user, $pass );
197
198       See DBIx::Class::Schema::Loader and "CONSTRUCTOR OPTIONS" in
199       DBIx::Class::Schema::Loader::Base.
200
201   Populate
202       Made for inserting lots of rows very quickly into database
203
204        $schema->populate([ Users =>
205           [qw( username password )],
206           [qw( frew     >=4char$ )],
207           [qw(      ...          )],
208           [qw(      ...          )],
209        );
210
211       See "populate" in DBIx::Class::Schema
212
213       I use populate here <http://blog.afoolishmanifesto.com/archives/1255>
214       to export our whole (200M~) db to SQLite
215
216   Multicreate
217       Create an object and its related objects all at once
218
219        $schema->resultset('Author')->create({
220           name => 'Stephen King',
221           books => [{ title => 'The Dark Tower' }],
222           address => {
223              street => '123 Turtle Back Lane',
224              state  => { abbreviation => 'ME' },
225              city   => { name => 'Lowell'     },
226           },
227        });
228
229       See "create" in DBIx::Class::ResultSet
230
231       books is a has_many
232       address is a belongs_to which in turn belongs to state and city each
233       for this to work right state and city must mark abbreviation and name
234       as unique
235
236   Extensible
237       DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is
238       made to allow extensions to nearly every part of it.
239
240   Extensibility example: DBIx::Class::Helpers
241       DBIx::Class::Helper::ResultSet::IgnoreWantarray
242       DBIx::Class::Helper::ResultSet::Random
243       DBIx::Class::Helper::ResultSet::SetOperations
244       DBIx::Class::Helper::Row::JoinTable
245       DBIx::Class::Helper::Row::NumifyGet
246       DBIx::Class::Helper::Row::SubClass
247       DBIx::Class::Helper::Row::ToJSON
248       DBIx::Class::Helper::Row::StorageValues
249       DBIx::Class::Helper::Row::OnColumnChange
250
251   Extensibility example: DBIx::Class::TimeStamp
252       See DBIx::Class::TimeStamp
253       Cross DB
254       set_on_create
255       set_on_update
256
257   Extensibility example: Kioku
258       See DBIx::Class::Schema::KiokuDB
259       Kioku is the new hotness
260       Mix RDBMS with Object DB
261
262   Result vs ResultSet
263       Result == Row
264       ResultSet == Query Plan
265        Internal Join Optimizer for all DB's (!!!)
266       (less important but...)
267       ResultSource == Queryable collection of rows (Table, View, etc)
268       Storage == Database
269       Schema == associates a set of ResultSources with a Storage
270
271   ResultSet methods
272        package MyApp::Schema::ResultSet::Book;
273
274        use strict;
275        use warnings;
276
277        use base 'DBIx::Class::ResultSet';
278
279        sub good {
280           my $self = shift;
281           $self->search({
282              $self->current_source_alias . '.rating' => { '>=' => 4 }
283           })
284        };
285
286        sub cheap {
287           my $self = shift;
288           $self->search({
289              $self->current_source_alias . '.price' => { '<=' => 5}
290           })
291        };
292
293        # ...
294
295        1;
296
297       See "Predefined searches" in DBIx::Class::Manual::Cookbook
298
299       All searches should be ResultSet methods
300       Name has obvious meaning
301       "current_source_alias" in DBIx::Class::ResultSet helps things to work
302       no matter what
303
304   ResultSet method in Action
305        $schema->resultset('Book')->good
306
307   ResultSet Chaining
308        $schema->resultset('Book')
309           ->good
310           ->cheap
311           ->recent
312
313   search_related
314        my $score = $schema->resultset('User')
315           ->search({'me.userid' => 'frew'})
316           ->related_resultset('access')
317           ->related_resultset('mgmt')
318           ->related_resultset('orders')
319           ->telephone
320           ->search_related( shops => {
321              'shops.datecompleted' => {
322                 -between => ['2009-10-01','2009-10-08']
323              }
324           })->completed
325           ->related_resultset('rpt_score')
326           ->search(undef, { rows => 1})
327           ->get_column('raw_scores')
328           ->next;
329
330       The SQL that this produces (with placeholders filled in for clarity's
331       sake) on our system (Microsoft SQL) is:
332
333        SELECT raw_scores
334          FROM (
335            SELECT raw_scores, ROW_NUMBER() OVER (
336                ORDER BY (
337                    SELECT (1)
338                  )
339              ) AS rno__row__index
340              FROM (
341                SELECT rpt_score.raw_scores
342                  FROM users me
343                  JOIN access access
344                    ON access.userid = me.userid
345                  JOIN mgmt mgmt
346                    ON mgmt.mgmtid = access.mgmtid
347                  JOIN [order] orders
348                    ON orders.mgmtid = mgmt.mgmtid
349                  JOIN shop shops
350                    ON shops.orderno = orders.orderno
351                  JOIN rpt_scores rpt_score
352                    ON rpt_score.shopno = shops.shopno
353                WHERE (
354                  datecompleted IS NOT NULL AND
355                  (
356                    (shops.datecompleted BETWEEN '2009-10-01' AND '2009-10-08')  AND
357                    (type = '1' AND me.userid = 'frew')
358                  )
359                )
360              ) rpt_score
361          ) rpt_score
362        WHERE rno__row__index BETWEEN 1 AND 1
363
364       See: "related_resultset" in DBIx::Class::ResultSet, "search_related" in
365       DBIx::Class::ResultSet, and "get_column" in DBIx::Class::ResultSet.
366
367   bonus rel methods
368        my $book = $author->create_related(
369           books => {
370              title => 'Another Discworld book',
371           }
372        );
373
374        my $book2 = $pratchett->add_to_books({
375           title => 'MOAR Discworld book',
376        });
377
378       See "create_related" in DBIx::Class::Relationship::Base and
379       "add_to_$rel" in DBIx::Class::Relationship::Base
380
381       Note that it automatically fills in foreign key for you
382
383   Excellent Transaction Support
384        $schema->txn_do(sub {
385           ...
386        });
387
388        $schema->txn_begin; # <-- low level
389        # ...
390        $schema->txn_commit;
391
392       See "txn_do" in DBIx::Class::Schema, "txn_begin" in
393       DBIx::Class::Schema, and "txn_commit" in DBIx::Class::Schema.
394
395   InflateColumn
396        package Frew::Schema::Result::Book;
397
398        use strict;
399        use warnings;
400
401        use base 'DBIx::Class::Core';
402
403        use DateTime::Format::MySQL;
404
405        # Result code here
406
407        __PACKAGE__->load_components('InflateColumn');
408
409        __PACKAGE__->inflate_column(
410           date_published => {
411              inflate => sub { DateTime::Format::MySQL->parse_date( shift ) },
412              deflate => sub { shift->ymd },
413           },
414        );
415
416       See DBIx::Class::InflateColumn, "inflate_column" in
417       DBIx::Class::InflateColumn, and DBIx::Class::InflateColumn::DateTime.
418
419   InflateColumn: deflation
420        $book->date_published(DateTime->now);
421        $book->update;
422
423   InflateColumn: inflation
424        say $book->date_published->month_abbr; # Nov
425
426   FilterColumn
427        package Frew::Schema::Result::Book;
428
429        use strict;
430        use warnings;
431
432        use base 'DBIx::Class::Core';
433
434        # Result code here
435
436        __PACKAGE__->load_components('FilterColumn');
437
438        __PACKAGE__->filter_column(
439           length => {
440              to_storage   => 'to_metric',
441              from_storage => 'to_imperial',
442           },
443        );
444
445        sub to_metric   { $_[1] * .305 }
446        sub to_imperial { $_[1] * 3.28 }
447
448       See DBIx::Class::FilterColumn and "filter_column" in
449       DBIx::Class::FilterColumn
450
451   ResultSetColumn
452        my $rsc = $schema->resultset('Book')->get_column('price');
453        $rsc->first;
454        $rsc->all;
455        $rsc->min;
456        $rsc->max;
457        $rsc->sum;
458
459       See DBIx::Class::ResultSetColumn
460
461   Aggregates
462        my @res = $rs->search(undef, {
463           select   => [
464              'price',
465              'genre',
466              { max => price },
467              { avg => price },
468           ],
469           as       => [
470              qw(price genre max_price avg_price)
471           ],
472           group_by => [qw(price genre)],
473        });
474        for (@res) {
475           say $_->price . ' ' . $_->genre;
476           say $_->get_column('max_price');
477           say $_->get_column('avg_price');
478        }
479
480       See "select" in DBIx::Class::ResultSet, "as" in DBIx::Class::ResultSet,
481       and "group_by" in DBIx::Class::ResultSet
482
483       Careful, get_column can basically mean three things
484       private in which case you should use an accessor
485       public for what there is no accessor for
486       public for get resultset column (prev example)
487
488   HRI
489        $rs->search(undef, {
490          result_class => 'DBIx::Class::ResultClass::HashRefInflator',
491        });
492
493       See "result_class" in DBIx::Class::ResultSet and
494       DBIx::Class::ResultClass::HashRefInflator.
495
496       Easy on memory
497       Mega fast
498       Great for quick debugging
499       Great for performance tuning (we went from 2m to < 3s)
500
501   Subquery Support
502        my $inner_query = $schema->resultset('Artist')
503           ->search({
504            name => [ 'Billy Joel', 'Brittany Spears' ],
505        })->get_column('id')->as_query;
506
507        my $rs = $schema->resultset('CD')->search({
508            artist_id => { -in => $inner_query },
509        });
510
511       See "Subqueries" in DBIx::Class::Manual::Cookbook
512
513   Bare SQL w/ Placeholders
514        $rs->update({
515           # !!! SQL INJECTION VECTOR
516           price => \"price + $inc", # DON'T DO THIS
517        });
518
519       Better:
520
521        $rs->update({
522           price => \['price + ?', [inc => $inc]],
523        });
524
525       See "Literal SQL with placeholders and bind values (subqueries)" in
526       SQL::Abstract
527

FURTHER QUESTIONS?

529       Check the list of additional DBIC resources.
530

COPYRIGHT AND LICENSE

532       This module is free software copyright by the DBIx::Class (DBIC)
533       authors. You can redistribute it and/or modify it under the same terms
534       as the DBIx::Class library.
535
536
537
538perl v5.30.1                      2020-01-29  DBIx::Class::Manual::Features(3)

NAME

META

General ORM

DBIx::Class Specific Features

FURTHER QUESTIONS?

COPYRIGHT AND LICENSE