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