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 | |
268 | =back |
269 | |
270 | =head2 Fetching data |
271 | |
272 | =over 4 |
273 | |
d6702eab |
274 | =item .. fetch as much data as possible in as few select calls as possible? |
81791ac3 |
275 | |
b5871402 |
276 | See the prefetch examples in the L<Cookbook|DBIx::Class::Manual::Cookbook>. |
81791ac3 |
277 | |
d6702eab |
278 | =item .. fetch a whole column of data instead of a row? |
279 | |
280 | Call C<get_column> on a L<DBIx::Class::ResultSet>, this returns a |
281 | L<DBIx::Class::ResultSetColumn>, see it's documentation and the |
282 | L<Cookbook|DBIx::Class::Manual::Cookbook> for details. |
283 | |
1697e138 |
284 | =item .. fetch a formatted column? |
285 | |
286 | In your table schema class, create a "private" column accessor with: |
287 | |
2acfa83c |
288 | __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' }); |
1697e138 |
289 | |
290 | Then, in the same class, implement a subroutine called "my_column" that |
291 | fetches the real value and does the formatting you want. |
292 | |
293 | See the Cookbook for more details. |
294 | |
2486df86 |
295 | =item .. fetch a single (or topmost) row? |
296 | |
297 | Sometimes you many only want a single record back from a search. A quick |
298 | way to get that single row is to first run your search as usual: |
299 | |
300 | ->search->(undef, { order_by => "id DESC" }) |
301 | |
302 | Then call L<DBIx::Class::ResultSet/slice> and ask it only to return 1 row: |
303 | |
2a2ab6ab |
304 | ->slice(0) |
2486df86 |
305 | |
306 | These two calls can be combined into a single statement: |
307 | |
2a2ab6ab |
308 | ->search->(undef, { order_by => "id DESC" })->slice(0) |
2486df86 |
309 | |
310 | Why slice instead of L<DBIx::Class::ResultSet/first> or L<DBIx::Class::ResultSet/single>? |
311 | If supported by the database, slice will use LIMIT/OFFSET to hint to the database that we |
312 | really only need one row. This can result in a significant speed improvement. |
313 | |
aefa6508 |
314 | =item .. refresh a row from storage? |
315 | |
316 | Use L<DBIx::Class::PK/discard_changes>. |
317 | |
318 | $row->discard_changes |
319 | |
320 | Discarding changes and refreshing from storage are two sides fo the same coin. When you |
321 | want to discard your local changes, just re-fetch the row from storage. When you want |
322 | to get a new, fresh copy of the row, just re-fetch the row from storage. |
323 | L<DBIx::Class::PK/discard_changes> does just that by re-fetching the row from storage |
324 | using the row's primary key. |
325 | |
81791ac3 |
326 | =back |
327 | |
81791ac3 |
328 | =head2 Inserting and updating data |
329 | |
330 | =over 4 |
331 | |
b5871402 |
332 | =item .. insert a row with an auto incrementing primary key? |
333 | |
334 | In versions of L<DBIx::Class> less than 0.07, you need to ensure your |
335 | table class loads the L<PK::Auto|DBIx::Class::PK::Auto> |
336 | component. This will attempt to fetch the value of your primary key |
337 | from the database after the insert has happened, and store it in the |
338 | created object. In versions 0.07 and above, this component is |
339 | automatically loaded. |
340 | |
341 | =item .. insert a row with a primary key that uses a sequence? |
342 | |
343 | You need to create a trigger in your database that updates your |
344 | primary key field from the sequence. To help PK::Auto find your |
345 | inserted key, you can tell it the name of the sequence in the |
346 | C<column_info> supplied with C<add_columns>. |
347 | |
838ef78d |
348 | ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } }); |
b5871402 |
349 | |
81791ac3 |
350 | =item .. insert many rows of data efficiently? |
351 | |
352 | =item .. update a collection of rows at the same time? |
353 | |
b5871402 |
354 | Create a resultset using a search, to filter the rows of data you |
355 | would like to update, then call update on the resultset to change all |
356 | the rows at once. |
357 | |
81791ac3 |
358 | =item .. use database functions when updating rows? |
359 | |
360 | =item .. update a column using data from another column? |
361 | |
b5871402 |
362 | To stop the column name from being quoted, you'll need to supply a |
363 | scalar reference: |
364 | |
fb5fb63c |
365 | ->update({ somecolumn => \'othercolumn' }) |
b5871402 |
366 | |
e338dbec |
367 | But note that when using a scalar reference the column in the database |
368 | will be updated but when you read the value from the object with e.g. |
369 | |
370 | ->somecolumn() |
371 | |
372 | you still get back the scalar reference to the string, B<not> the new |
373 | value in the database. To get that you must refresh the row from storage |
374 | using C<discard_changes()>. Or chain your function calls like this: |
375 | |
376 | ->update->discard_changes |
377 | |
378 | to update the database and refresh the object in one step. |
379 | |
75e75a9f |
380 | =item .. store JSON/YAML in a column and have it deflate/inflate automatically? |
1697e138 |
381 | |
75e75a9f |
382 | You can use L<DBIx::Class::InflateColumn> to accomplish YAML/JSON storage transparently. |
383 | |
384 | If you want to use JSON, then in your table schema class, do the following: |
1697e138 |
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 | |
75e75a9f |
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 data in a table. Be |
405 | careful not to overuse this capability, however. If you find yourself depending more |
406 | and more on some data within the inflated column, then it may be time to factor that |
407 | data out. |
408 | |
81791ac3 |
409 | =back |
410 | |
411 | =head2 Misc |
412 | |
413 | =over 4 |
414 | |
415 | =item How do I store my own (non-db) data in my DBIx::Class objects? |
416 | |
b5871402 |
417 | You can add your own data accessors to your classes. |
418 | |
f7a90adc |
419 | =item How do I use DBIx::Class objects in my TT templates? |
81791ac3 |
420 | |
e147365d |
421 | Like normal objects, mostly. However you need to watch out for TT |
422 | calling methods in list context. When calling relationship accessors |
423 | you will not get resultsets, but a list of all the related objects. |
424 | |
425 | Starting with version 0.07, you can use L<DBIx::Class::ResultSet/search_rs> |
426 | to work around this issue. |
b5871402 |
427 | |
81791ac3 |
428 | =item See the SQL statements my code is producing? |
429 | |
85f78622 |
430 | Turn on debugging! See L<DBIx::Class::Storage> for details of how |
f7a90adc |
431 | to turn on debugging in the environment, pass your own filehandle to |
432 | save debug to, or create your own callback. |
b5871402 |
433 | |
81791ac3 |
434 | =item Why didn't my search run any SQL? |
435 | |
b5871402 |
436 | L<DBIx::Class> runs the actual SQL statement as late as possible, thus |
437 | if you create a resultset using C<search> in scalar context, no query |
438 | is executed. You can create further resultset refinements by calling |
439 | search again or relationship accessors. The SQL query is only run when |
e147365d |
440 | you ask the resultset for an actual row object. |
81791ac3 |
441 | |
e4773415 |
442 | =item How do I deal with tables that lack a primary key? |
443 | |
444 | If your table lacks a primary key, DBIx::Class can't work out which row |
445 | it should operate on, for example to delete or update. However, a |
446 | UNIQUE constraint on one or more columns allows DBIx::Class to uniquely |
447 | identify the row, so you can tell L<DBIx::Class::ResultSource> these |
448 | columns act as a primary key, even if they don't from the database's |
449 | point of view: |
450 | |
451 | $resultset->set_primary_key(@column); |
452 | |
81791ac3 |
453 | =back |
462bb847 |
454 | |
455 | =head2 Notes for CDBI users |
456 | |
457 | =over 4 |
458 | |
459 | =item Is there a way to make an object auto-stringify itself as a |
460 | particular column or group of columns (a-la cdbi Stringfy column |
461 | group, or stringify_self method) ? |
462 | |
c6d52fe4 |
463 | See L<DBIx::Class::Manual::Cookbook/Stringification> |
462bb847 |
464 | |
465 | =back |