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