1DBIx::Class::Manual::FAUQs(e3r)Contributed Perl DocumentDaBtIixo:n:Class::Manual::FAQ(3)
2
3
4

NAME

6       DBIx::Class::Manual::FAQ - Frequently Asked Questions (in theory)
7

DESCRIPTION

9       This document is intended as an anti-map of the documentation. If you
10       know what you want to do, but not how to do it in DBIx::Class, then
11       look here. It does not contain much code or examples, it just gives
12       explanations and pointers to the correct pieces of documentation to
13       read.
14

FAQs

16       How Do I:
17
18   Getting started
19       .. create a database to use?
20           First, choose a database. For testing/experimenting, we recommend
21           DBD::SQLite, which is a self-contained small database (i.e. all you
22           need to do is to install DBD::SQLite from CPAN, and it works).
23
24           Next, spend some time defining which data you need to store, and
25           how it relates to the other data you have. For some help on
26           normalisation, go to <http://b62.tripod.com/doc/dbbase.htm>.
27
28           Now, decide whether you want to have the database itself be the
29           definitive source of information about the data layout, or your
30           DBIx::Class schema. If it's the former, look up the documentation
31           for your database, eg. <http://sqlite.org/lang_createtable.html>,
32           on how to create tables, and start creating them. For a nice
33           universal interface to your database, you can try DBI::Shell. If
34           you decided on the latter choice, read the FAQ on setting up your
35           classes manually, and the one on creating tables from your schema.
36
37       .. use DBIx::Class with Catalyst?
38           Install Catalyst::Model::DBIC::Schema from CPAN. See its
39           documentation, or below, for further details.
40
41       .. set up my DBIx::Class classes automatically from my database?
42           Install DBIx::Class::Schema::Loader from CPAN, and read its
43           documentation.
44
45       .. set up my DBIx::Class classes manually?
46           Look at the DBIx::Class::Manual::Example and come back here if you
47           get lost.
48
49       .. create my database tables from my DBIx::Class schema?
50           Create your classes manually, as above. Write a script that calls
51           "deploy" in DBIx::Class::Schema. See there for details, or the
52           DBIx::Class::Manual::Cookbook.
53
54       .. store/retrieve Unicode data in my database?
55           Make sure you database supports Unicode and set the connect
56           attributes appropriately - see "Using Unicode" in
57           DBIx::Class::Manual::Cookbook
58
59       .. connect to my database?
60           Once you have created all the appropriate table/source classes, and
61           an overall Schema class, you can start using them in an
62           application. To do this, you need to create a central Schema
63           object, which is used to access all the data in the various tables.
64           See "connect" in DBIx::Class::Schema for details. The actual
65           connection does not happen until you actually request data, so
66           don't be alarmed if the error from incorrect connection details
67           happens a lot later.
68
69       .. use DBIx::Class across multiple databases?
70           If your database server allows you to run queries across multiple
71           databases at once, then so can DBIx::Class. All you need to do is
72           make sure you write the database name as part of the table call.
73           Eg:
74
75             __PACKAGE__->table('mydb.mytablename');
76
77           And load all the Result classes for both / all databases by calling
78           "load_namespaces" in DBIx::Class::Schema.
79
80       .. use DBIx::Class across PostgreSQL/DB2/Oracle schemas?
81           Add the name of the schema to the table name, when invoking table,
82           and make sure the user you are about to connect as has permissions
83           to read/write all the schemas/tables as necessary.
84
85   Relationships
86       .. tell DBIx::Class about relationships between my tables?
87           There are a variety of relationship types that come pre-defined for
88           you to use.  These are all listed in DBIx::Class::Relationship. If
89           you need a non-standard type, or more information, look in
90           DBIx::Class::Relationship::Base.
91
92       .. define a one-to-many relationship?
93           This is called a "has_many" relationship on the one side, and a
94           "belongs_to" relationship on the many side. Currently these need to
95           be set up individually on each side. See DBIx::Class::Relationship
96           for details.
97
98       .. define a relationship where this table contains another table's
99       primary key? (foreign key)
100           Create a "belongs_to" relationship for the field containing the
101           foreign key.  See "belongs_to" in DBIx::Class::Relationship.
102
103       .. define a foreign key relationship where the key field may contain
104       NULL?
105           Just create a "belongs_to" relationship, as above. If the column is
106           NULL then the inflation to the foreign object will not happen. This
107           has a side effect of not always fetching all the relevant data, if
108           you use a nullable foreign-key relationship in a JOIN, then you
109           probably want to set the "join_type" to "left".
110
111       .. define a relationship where the key consists of more than one
112       column?
113           Instead of supplying a single column name, all relationship types
114           also allow you to supply a hashref containing the condition across
115           which the tables are to be joined. The condition may contain as
116           many fields as you like. See DBIx::Class::Relationship::Base.
117
118       .. define a relationship bridge across an intermediate table? (many-to-
119       many)
120           The term 'relationship' is used loosely with many_to_many as it is
121           not considered a relationship in the fullest sense.  For more info,
122           read the documentation on "many_to_many" in
123           DBIx::Class::Relationship.
124
125       .. stop DBIx::Class from attempting to cascade deletes on my has_many
126       and might_have relationships?
127           By default, DBIx::Class cascades deletes and updates across
128           "has_many" and "might_have" relationships. You can disable this
129           behaviour on a per-relationship basis by supplying "cascade_delete
130           => 0" in the relationship attributes.
131
132           The cascaded operations are performed after the requested delete or
133           update, so if your database has a constraint on the relationship,
134           it will have deleted/updated the related records or raised an
135           exception before DBIx::Class gets to perform the cascaded
136           operation.
137
138           See DBIx::Class::Relationship.
139
140       .. use a relationship?
141           Use its name. An accessor is created using the name. See examples
142           in "USING RELATIONSHIPS" in DBIx::Class::Manual::Cookbook.
143
144   Searching
145       .. search for data?
146           Create a $schema object, as mentioned above in ".. connect to my
147           database?". Find the ResultSet that you want to search in, by
148           calling "$schema->resultset('MySource')" and call "search" on it.
149           See "search" in DBIx::Class::ResultSet.
150
151       .. search using database functions?
152           Supplying something like:
153
154            ->search({'mydatefield' => 'now()'})
155
156           to search, will probably not do what you expect. It will quote the
157           text "now()", instead of trying to call the function. To provide
158           literal, unquoted text you need to pass in a scalar reference, like
159           so:
160
161            ->search({'mydatefield' => \'now()'})
162
163       .. sort the results of my search?
164           Supply a list of columns you want to sort by to the "order_by"
165           attribute. See "order_by" in DBIx::Class::ResultSet.
166
167       .. sort my results based on fields I've aliased using "as"?
168           You didn't alias anything, since as has nothing to do with the
169           produced SQL. See "select" in DBIx::Class::ResultSet for details.
170
171       .. group the results of my search?
172           Supply a list of columns you want to group on, to the "group_by"
173           attribute, see "group_by" in DBIx::Class::ResultSet.
174
175       .. group my results based on fields I've aliased using "as"?
176           You don't. See the explanation on ordering by an alias above.
177
178       .. filter the results of my search?
179           The first argument to "search" is a hashref of accessor names and
180           values to filter them by, for example:
181
182            ->search({'created_time' => { '>=', '2006-06-01 00:00:00' } })
183
184           Note that to use a function here you need to make it a scalar
185           reference:
186
187            ->search({'created_time' => { '>=', \'yesterday()' } })
188
189       .. search in several tables simultaneously?
190           To search in two related tables, you first need to set up
191           appropriate relationships between their respective classes. When
192           searching you then supply the name of the relationship to the
193           "join" attribute in your search, for example when searching in the
194           Books table for all the books by the author "Fred Bloggs":
195
196            ->search({'authors.name' => 'Fred Bloggs'}, { join => 'authors' })
197
198           The type of join created in your SQL depends on the type of
199           relationship between the two tables, see DBIx::Class::Relationship
200           for the join used by each relationship.
201
202       .. create joins with conditions other than column equality?
203           Currently, DBIx::Class can only create join conditions using
204           equality, so you're probably better off creating a "view" in your
205           database, and using that as your source. A "view" is a stored SQL
206           query, which can be accessed similarly to a table, see your
207           database documentation for details.
208
209       .. search with an SQL function on the left hand side?
210           To use an SQL function on the left hand side of a comparison you
211           currently need to resort to literal SQL:
212
213            ->search( \[ 'YEAR(date_of_birth) = ?', 1979 ] );
214
215       .. find more help on constructing searches?
216           Behind the scenes, DBIx::Class uses SQL::Abstract to help construct
217           its SQL searches. So if you fail to find help in the
218           DBIx::Class::Manual::Cookbook, try looking in the SQL::Abstract
219           documentation.
220
221       .. make searches in Oracle (10gR2 and newer) case-insensitive?
222           To make Oracle behave like most RDBMS use on_connect_do to issue
223           alter session statements on database connection establishment:
224
225            ->on_connect_do("ALTER SESSION SET NLS_COMP = 'LINGUISTIC'");
226            ->on_connect_do("ALTER SESSION SET NLS_SORT = '<NLS>_CI'");
227            e.g.
228            ->on_connect_do("ALTER SESSION SET NLS_SORT = 'BINARY_CI'");
229            ->on_connect_do("ALTER SESSION SET NLS_SORT = 'GERMAN_CI'");
230
231       .. format a DateTime object for searching?
232           search and find do not take DBIx::Class::InflateColumn into
233           account, and so your DateTime object will not be correctly deflated
234           into a format your RDBMS expects.
235
236           The datetime_parser method on your storage object can be used to
237           return the object that would normally do this, so it's easy to do
238           it manually:
239
240             my $dtf = $schema->storage->datetime_parser;
241             my $rs = $schema->resultset('users')->search(
242               {
243                 signup_date => {
244                   -between => [
245                     $dtf->format_datetime($dt_start),
246                     $dtf->format_datetime($dt_end),
247                   ],
248                 }
249               },
250             );
251
252           With in a Result Class method, you can get this from the
253           "result_source".
254
255             my $dtf = $self->result_source->storage->datetime_parser;
256
257           This kludge is necessary only for conditions passed to search and
258           "find" in DBIx::Class::ResultSet, whereas create and "update" in
259           DBIx::Class::Row (but not "update" in DBIx::Class::ResultSet) are
260           DBIx::Class::InflateColumn-aware and will do the right thing when
261           supplied an inflated DateTime object.
262
263   Fetching data
264       .. fetch as much data as possible in as few select calls as possible?
265           See the prefetch examples in the Cookbook.
266
267       .. fetch a whole column of data instead of a row?
268           Call "get_column" on a DBIx::Class::ResultSet. This returns a
269           DBIx::Class::ResultSetColumn. See its documentation and the
270           Cookbook for details.
271
272       .. fetch a formatted column?
273           In your table schema class, create a "private" column accessor
274           with:
275
276             __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' });
277
278           Then, in the same class, implement a subroutine called "my_column"
279           that fetches the real value and does the formatting you want.
280
281           See the Cookbook for more details.
282
283       .. fetch a single (or topmost) row?
284           Use the "rows" in DBIx::Class::ResultSet and "order_by" in
285           DBIx::Class::ResultSet attributes to order your data and pick off a
286           single row.
287
288           See also "Retrieve one and only one row from a resultset" in
289           DBIx::Class::Manual::Cookbook.
290
291           A less readable way is to ask a regular search to return 1 row,
292           using "slice" in DBIx::Class::ResultSet:
293
294             ->search->(undef, { order_by => "id DESC" })->slice(0)
295
296           which (if supported by the database) will use LIMIT/OFFSET to hint
297           to the database that we really only need one row. This can result
298           in a significant speed improvement. The method using "single" in
299           DBIx::Class::ResultSet mentioned in the cookbook can do the same if
300           you pass a "rows" attribute to the search.
301
302       .. refresh a row from storage?
303           Use "discard_changes" in DBIx::Class::Row.
304
305             $result->discard_changes
306
307           Discarding changes and refreshing from storage are two sides of the
308           same coin.  When you want to discard your local changes, just re-
309           fetch the row from storage.  When you want to get a new, fresh copy
310           of the row, just re-fetch the row from storage.  "discard_changes"
311           in DBIx::Class::Row does just that by re-fetching the row from
312           storage using the row's primary key.
313
314       .. fetch my data a "page" at a time?
315           Pass the "rows" and "page" attributes to your search, eg:
316
317             ->search({}, { rows => 10, page => 1});
318
319       .. get a count of all rows even when paging?
320           Call "pager" on the paged resultset, it will return a Data::Page
321           object. Calling "total_entries" on the pager will return the
322           correct total.
323
324           "count" on the resultset will only return the total number in the
325           page.
326
327   Inserting and updating data
328       .. insert a row with an auto incrementing primary key?
329           This happens automatically. After creating a result object, the
330           primary key value created by your database can be fetched by
331           calling "id" (or the access of your primary key column) on the
332           object.
333
334       .. insert a row with a primary key that uses a sequence?
335           You need to create a trigger in your database that updates your
336           primary key field from the sequence. To help PK::Auto find the next
337           key value, you can tell it the name of the sequence in the
338           "column_info" supplied with "add_columns".
339
340            ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
341
342       .. insert many rows of data efficiently?
343           The "populate" method in DBIx::Class::ResultSet provides efficient
344           bulk inserts.
345
346           DBIx::Class::Fixtures provides an alternative way to do this.
347
348       .. update a collection of rows at the same time?
349           Create a resultset using a "search", to filter the rows of data you
350           would like to update, then call "update" on the resultset to change
351           all the rows at once.
352
353       .. use database functions when updating rows?
354       .. update a column using data from another column?
355           To stop the column name from being quoted, you'll need to tell DBIC
356           that the right hand side is an SQL identifier (it will be quoted
357           properly if you have quoting enabled):
358
359            ->update({ somecolumn => { -ident => 'othercolumn' } })
360
361           This method will not retrieve the new value and put it in your Row
362           object. To fetch the new value, use the "discard_changes" method on
363           the Row.
364
365             # will return the scalar reference:
366             $result->somecolumn()
367
368             # issue a select using the PK to re-fetch the row data:
369             $result->discard_changes();
370
371             # Now returns the correct new value:
372             $result->somecolumn()
373
374           To update and refresh at once, chain your calls:
375
376             $result->update({ 'somecolumn' => { -ident => 'othercolumn' } })->discard_changes;
377
378       .. store JSON/YAML in a column and have it deflate/inflate
379       automatically?
380           You can use DBIx::Class::InflateColumn to accomplish YAML/JSON
381           storage transparently.
382
383           If you want to use JSON, then in your table schema class, do the
384           following:
385
386            use JSON;
387
388            __PACKAGE__->add_columns(qw/ ... my_column ../)
389            __PACKAGE__->inflate_column('my_column', {
390                inflate => sub { jsonToObj(shift) },
391                deflate => sub { objToJson(shift) },
392            });
393
394           For YAML, in your table schema class, do the following:
395
396            use YAML;
397
398            __PACKAGE__->add_columns(qw/ ... my_column ../)
399            __PACKAGE__->inflate_column('my_column', {
400                inflate => sub { YAML::Load(shift) },
401                deflate => sub { YAML::Dump(shift) },
402            });
403
404           This technique is an easy way to store supplemental unstructured
405           data in a table. Be careful not to overuse this capability,
406           however. If you find yourself depending more and more on some data
407           within the inflated column, then it may be time to factor that data
408           out.
409
410   Custom methods in Result classes
411       You can add custom methods that do arbitrary things, even to unrelated
412       tables.  For example, to provide a "$book->foo()" method which searches
413       the cd table, you'd could add this to Book.pm:
414
415         sub foo {
416           my ($self, $col_data) = @_;
417           return $self->result_source->schema->resultset('cd')->search($col_data);
418         }
419
420       And invoke that on any Book Result object like so:
421
422         my $rs = $book->foo({ title => 'Down to Earth' });
423
424       When two tables ARE related, DBIx::Class::Relationship::Base provides
425       many methods to find or create data in related tables for you. But if
426       you want to write your own methods, you can.
427
428       For example, to provide a "$book->foo()" method to manually implement
429       what create_related() from DBIx::Class::Relationship::Base does, you
430       could add this to Book.pm:
431
432         sub foo {
433           my ($self, $rel_name, $col_data) = @_;
434           return $self->related_resultset($rel_name)->create($col_data);
435         }
436
437       Invoked like this:
438
439         my $author = $book->foo('author', { name => 'Fred' });
440
441   Misc
442       How do I store my own (non-db) data in my DBIx::Class objects?
443           You can add your own data accessors to your Result classes.
444
445           One method is to use the built in mk_group_accessors (via
446           Class::Accessor::Grouped)
447
448                   package App::Schema::Result::MyTable;
449
450                   use parent 'DBIx::Class::Core';
451
452                   __PACKAGE__->table('foo'); #etc
453                   __PACKAGE__->mk_group_accessors('simple' => qw/non_column_data/); # must use simple group
454
455           An another method is to use Moose with your DBIx::Class package.
456
457                   package App::Schema::Result::MyTable;
458
459                   use Moose; # import Moose
460                   use Moose::Util::TypeConstraint; # import Moose accessor type constraints
461
462                   extends 'DBIx::Class::Core'; # Moose changes the way we define our parent (base) package
463
464                   has 'non_column_data' => ( is => 'rw', isa => 'Str' ); # define a simple attribute
465
466                   __PACKAGE__->table('foo'); # etc
467
468           With either of these methods the resulting use of the accessor
469           would be
470
471                   my $result;
472
473                   # assume that somewhere in here $result will get assigned to a MyTable row
474
475                   $result->non_column_data('some string'); # would set the non_column_data accessor
476
477                   # some other stuff happens here
478
479                   $result->update(); # would not inline the non_column_data accessor into the update
480
481       How do I use DBIx::Class objects in my TT templates?
482           Like normal objects, mostly. However you need to watch out for TT
483           calling methods in list context. When calling relationship
484           accessors you will not get resultsets, but a list of all the
485           related objects.
486
487           Use the "search_rs" in DBIx::Class::ResultSet method, or the
488           relationship accessor methods ending with "_rs" to work around this
489           issue.
490
491           See also "has_many" in DBIx::Class::Relationship.
492
493       See the SQL statements my code is producing?
494           Set the shell environment variable "DBIC_TRACE" to a true value.
495
496           For more info see DBIx::Class::Storage for details of how to turn
497           on debugging in the environment, pass your own filehandle to save
498           debug to, or create your own callback.
499
500       Why didn't my search run any SQL?
501           DBIx::Class runs the actual SQL statement as late as possible, thus
502           if you create a resultset using "search" in scalar context, no
503           query is executed. You can create further resultset refinements by
504           calling search again or relationship accessors. The SQL query is
505           only run when you ask the resultset for an actual result object.
506
507       How do I deal with tables that lack a primary key?
508           If your table lacks a primary key, DBIx::Class can't work out which
509           row it should operate on, for example to delete or update.
510           However, a UNIQUE constraint on one or more columns allows
511           DBIx::Class to uniquely identify the row, so you can tell
512           DBIx::Class::ResultSource these columns act as a primary key, even
513           if they don't from the database's point of view:
514
515            $resultset->set_primary_key(@column);
516
517       How do I make my program start faster?
518           Look at the tips in "STARTUP SPEED" in
519           DBIx::Class::Manual::Cookbook
520
521       How do I reduce the overhead of database queries?
522           You can reduce the overhead of object creation within DBIx::Class
523           using the tips in "Skip result object creation for faster results"
524           in DBIx::Class::Manual::Cookbook and "Get raw data for blindingly
525           fast results" in DBIx::Class::Manual::Cookbook
526
527       How do I override a run time method (e.g. a relationship accessor)?
528           If you need access to the original accessor, then you must "wrap
529           around" the original method.  You can do that either with
530           Moose::Manual::MethodModifiers or Class::Method::Modifiers.  The
531           code example works for both modules:
532
533               package Your::Schema::Group;
534               use Class::Method::Modifiers;
535
536               # ... declare columns ...
537
538               __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
539               __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
540
541               # if the server group is a "super group", then return all servers
542               # otherwise return only servers that belongs to the given group
543               around 'servers' => sub {
544                   my $orig = shift;
545                   my $self = shift;
546
547                   return $self->$orig(@_) unless $self->is_super_group;
548                   return $self->result_source->schema->resultset('Server')->all;
549               };
550
551           If you just want to override the original method, and don't care
552           about the data from the original accessor, then you have two
553           options. Either use Method::Signatures::Simple that does most of
554           the work for you, or do it the "dirty way".
555
556           Method::Signatures::Simple way:
557
558               package Your::Schema::Group;
559               use Method::Signatures::Simple;
560
561               # ... declare columns ...
562
563               __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
564               __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
565
566               # The method keyword automatically injects the annoying my $self = shift; for you.
567               method servers {
568                   return $self->result_source->schema->resultset('Server')->search({ ... });
569               }
570
571           The dirty way:
572
573               package Your::Schema::Group;
574               use Sub::Name;
575
576               # ... declare columns ...
577
578               __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
579               __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
580
581               *servers = subname servers => sub {
582                   my $self = shift;
583                   return $self->result_source->schema->resultset('Server')->search({ ... });
584               };
585
586   Notes for CDBI users
587       Is there a way to make an object auto-stringify itself as a particular
588       column or group of columns (a-la cdbi Stringfy column group, or
589       stringify_self method) ?
590           See "Stringification" in DBIx::Class::Manual::Cookbook
591
592   Troubleshooting
593       Help, I can't connect to postgresql!
594           If you get an error such as:
595
596             DBI connect('dbname=dbic','user',...) failed: could not connect to server:
597             No such file or directory Is the server running locally and accepting
598             connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
599
600           Likely you have/had two copies of postgresql installed
601           simultaneously, the second one will use a default port of 5433,
602           while DBD::Pg is compiled with a default port of 5432.
603
604           You can change the port setting in "postgresql.conf".
605
606       I've lost or forgotten my mysql password
607           Stop mysqld and restart it with the --skip-grant-tables option.
608
609           Issue the following statements in the mysql client.
610
611             UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
612             FLUSH PRIVILEGES;
613
614           Restart mysql.
615
616           Taken from:
617
618           <http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html>.
619

FURTHER QUESTIONS?

621       Check the list of additional DBIC resources.
622
624       This module is free software copyright by the DBIx::Class (DBIC)
625       authors. You can redistribute it and/or modify it under the same terms
626       as the DBIx::Class library.
627
628
629
630perl v5.28.1                      2018-01-29       DBIx::Class::Manual::FAQ(3)
Impressum