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::Classic to help
217           construct its SQL searches. So if you fail to find help in the
218           DBIx::Class::Manual::Cookbook, try looking in the
219           SQL::Abstract::Classic 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 pager object
321            with an API/behavior identical to that of Data::Page from late
322           2009 through late 2019
323            <https://metacpan.org/pod/release/LBROCARD/Data-
324           Page-2.02/lib/Data/Page.pm>.  Calling "total_entries" on the pager
325           will return the correct total.
326
327           "count" on the resultset will only return the total number in the
328           page.
329
330   Inserting and updating data
331       .. insert a row with an auto incrementing primary key?
332           This happens automatically. After creating a result object, the
333           primary key value created by your database can be fetched by
334           calling "id" (or the access of your primary key column) on the
335           object.
336
337       .. insert a row with a primary key that uses a sequence?
338           You need to create a trigger in your database that updates your
339           primary key field from the sequence. To help PK::Auto find the next
340           key value, you can tell it the name of the sequence in the
341           "column_info" supplied with "add_columns".
342
343            ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
344
345       .. insert many rows of data efficiently?
346           The "populate" method in DBIx::Class::ResultSet provides efficient
347           bulk inserts.
348
349           DBIx::Class::Fixtures provides an alternative way to do this.
350
351       .. update a collection of rows at the same time?
352           Create a resultset using a "search", to filter the rows of data you
353           would like to update, then call "update" on the resultset to change
354           all the rows at once.
355
356       .. use database functions when updating rows?
357       .. update a column using data from another column?
358           To stop the column name from being quoted, you'll need to tell DBIC
359           that the right hand side is an SQL identifier (it will be quoted
360           properly if you have quoting enabled):
361
362            ->update({ somecolumn => { -ident => 'othercolumn' } })
363
364           This method will not retrieve the new value and put it in your Row
365           object. To fetch the new value, use the "discard_changes" method on
366           the Row.
367
368             # will return the scalar reference:
369             $result->somecolumn()
370
371             # issue a select using the PK to re-fetch the row data:
372             $result->discard_changes();
373
374             # Now returns the correct new value:
375             $result->somecolumn()
376
377           To update and refresh at once, chain your calls:
378
379             $result->update({ 'somecolumn' => { -ident => 'othercolumn' } })->discard_changes;
380
381       .. store JSON/YAML in a column and have it deflate/inflate
382       automatically?
383           You can use DBIx::Class::InflateColumn to accomplish YAML/JSON
384           storage transparently.
385
386           If you want to use JSON, then in your table schema class, do the
387           following:
388
389            use JSON;
390
391            __PACKAGE__->add_columns(qw/ ... my_column ../)
392            __PACKAGE__->inflate_column('my_column', {
393                inflate => sub { jsonToObj(shift) },
394                deflate => sub { objToJson(shift) },
395            });
396
397           For YAML, in your table schema class, do the following:
398
399            use YAML;
400
401            __PACKAGE__->add_columns(qw/ ... my_column ../)
402            __PACKAGE__->inflate_column('my_column', {
403                inflate => sub { YAML::Load(shift) },
404                deflate => sub { YAML::Dump(shift) },
405            });
406
407           This technique is an easy way to store supplemental unstructured
408           data in a table. Be careful not to overuse this capability,
409           however. If you find yourself depending more and more on some data
410           within the inflated column, then it may be time to factor that data
411           out.
412
413   Custom methods in Result classes
414       You can add custom methods that do arbitrary things, even to unrelated
415       tables.  For example, to provide a "$book->foo()" method which searches
416       the cd table, you'd could add this to Book.pm:
417
418         sub foo {
419           my ($self, $col_data) = @_;
420           return $self->result_source->schema->resultset('cd')->search($col_data);
421         }
422
423       And invoke that on any Book Result object like so:
424
425         my $rs = $book->foo({ title => 'Down to Earth' });
426
427       When two tables ARE related, DBIx::Class::Relationship::Base provides
428       many methods to find or create data in related tables for you. But if
429       you want to write your own methods, you can.
430
431       For example, to provide a "$book->foo()" method to manually implement
432       what create_related() from DBIx::Class::Relationship::Base does, you
433       could add this to Book.pm:
434
435         sub foo {
436           my ($self, $rel_name, $col_data) = @_;
437           return $self->related_resultset($rel_name)->create($col_data);
438         }
439
440       Invoked like this:
441
442         my $author = $book->foo('author', { name => 'Fred' });
443
444   Misc
445       How do I store my own (non-db) data in my DBIx::Class objects?
446           You can add your own data accessors to your Result classes.
447
448           One method is to use the built in mk_group_accessors (via
449           Class::Accessor::Grouped)
450
451                   package App::Schema::Result::MyTable;
452
453                   use parent 'DBIx::Class::Core';
454
455                   __PACKAGE__->table('foo'); #etc
456                   __PACKAGE__->mk_group_accessors('simple' => qw/non_column_data/); # must use simple group
457
458           And another method is to use Moose with your DBIx::Class package.
459
460                   package App::Schema::Result::MyTable;
461
462                   use Moose; # import Moose
463                   use Moose::Util::TypeConstraint; # import Moose accessor type constraints
464
465                   extends 'DBIx::Class::Core'; # Moose changes the way we define our parent (base) package
466
467                   has 'non_column_data' => ( is => 'rw', isa => 'Str' ); # define a simple attribute
468
469                   __PACKAGE__->table('foo'); # etc
470
471           With either of these methods the resulting use of the accessor
472           would be
473
474                   my $result;
475
476                   # assume that somewhere in here $result will get assigned to a MyTable row
477
478                   $result->non_column_data('some string'); # would set the non_column_data accessor
479
480                   # some other stuff happens here
481
482                   $result->update(); # would not inline the non_column_data accessor into the update
483
484       How do I use DBIx::Class objects in my TT templates?
485           Like normal objects, mostly. However you need to watch out for TT
486           calling methods in list context. When calling relationship
487           accessors you will not get resultsets, but a list of all the
488           related objects.
489
490           Use the "search_rs" in DBIx::Class::ResultSet method, or the
491           relationship accessor methods ending with "_rs" to work around this
492           issue.
493
494           See also "has_many" in DBIx::Class::Relationship.
495
496       See the SQL statements my code is producing?
497           Set the shell environment variable "DBIC_TRACE" to a true value.
498
499           For more info see DBIx::Class::Storage for details of how to turn
500           on debugging in the environment, pass your own filehandle to save
501           debug to, or create your own callback.
502
503       Why didn't my search run any SQL?
504           DBIx::Class runs the actual SQL statement as late as possible, thus
505           if you create a resultset using "search" in scalar context, no
506           query is executed. You can create further resultset refinements by
507           calling search again or relationship accessors. The SQL query is
508           only run when you ask the resultset for an actual result object.
509
510       How do I deal with tables that lack a primary key?
511           If your table lacks a primary key, DBIx::Class can't work out which
512           row it should operate on, for example to delete or update.
513           However, a UNIQUE constraint on one or more columns allows
514           DBIx::Class to uniquely identify the row, so you can tell
515           DBIx::Class::ResultSource these columns act as a primary key, even
516           if they don't from the database's point of view:
517
518            $resultset->set_primary_key(@column);
519
520       How do I make my program start faster?
521           Look at the tips in "STARTUP SPEED" in
522           DBIx::Class::Manual::Cookbook
523
524       How do I reduce the overhead of database queries?
525           You can reduce the overhead of object creation within DBIx::Class
526           using the tips in "Skip result object creation for faster results"
527           in DBIx::Class::Manual::Cookbook and "Get raw data for blindingly
528           fast results" in DBIx::Class::Manual::Cookbook
529
530       How do I override a run time method (e.g. a relationship accessor)?
531           If you need access to the original accessor, then you must "wrap
532           around" the original method.  You can do that either with
533           Moose::Manual::MethodModifiers or Class::Method::Modifiers.  The
534           code example works for both modules:
535
536               package Your::Schema::Group;
537               use Class::Method::Modifiers;
538
539               # ... declare columns ...
540
541               __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
542               __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
543
544               # if the server group is a "super group", then return all servers
545               # otherwise return only servers that belongs to the given group
546               around 'servers' => sub {
547                   my $orig = shift;
548                   my $self = shift;
549
550                   return $self->$orig(@_) unless $self->is_super_group;
551                   return $self->result_source->schema->resultset('Server')->all;
552               };
553
554           If you just want to override the original method, and don't care
555           about the data from the original accessor, then you have two
556           options. Either use Method::Signatures::Simple that does most of
557           the work for you, or do it the "dirty way".
558
559           Method::Signatures::Simple way:
560
561               package Your::Schema::Group;
562               use Method::Signatures::Simple;
563
564               # ... declare columns ...
565
566               __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
567               __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
568
569               # The method keyword automatically injects the annoying my $self = shift; for you.
570               method servers {
571                   return $self->result_source->schema->resultset('Server')->search({ ... });
572               }
573
574           The dirty way:
575
576               package Your::Schema::Group;
577               use Sub::Name;
578
579               # ... declare columns ...
580
581               __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
582               __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
583
584               *servers = subname servers => sub {
585                   my $self = shift;
586                   return $self->result_source->schema->resultset('Server')->search({ ... });
587               };
588
589   Notes for CDBI users
590       Is there a way to make an object auto-stringify itself as a particular
591       column or group of columns (a-la cdbi Stringfy column group, or
592       stringify_self method) ?
593           See "Stringification" in DBIx::Class::Manual::Cookbook
594
595   Troubleshooting
596       Help, I can't connect to postgresql!
597           If you get an error such as:
598
599             DBI connect('dbname=dbic','user',...) failed: could not connect to server:
600             No such file or directory Is the server running locally and accepting
601             connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
602
603           Likely you have/had two copies of postgresql installed
604           simultaneously, the second one will use a default port of 5433,
605           while DBD::Pg is compiled with a default port of 5432.
606
607           You can change the port setting in "postgresql.conf".
608
609       I've lost or forgotten my mysql password
610           Stop mysqld and restart it with the --skip-grant-tables option.
611
612           Issue the following statements in the mysql client.
613
614             UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
615             FLUSH PRIVILEGES;
616
617           Restart mysql.
618
619           Taken from:
620
621           <http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html>.
622

FURTHER QUESTIONS?

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