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