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 | |
e147365d |
80 | There are a vareity of relationship types that come pre-defined for |
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 | |
116 | =item .. stop DBIx::Class from attempting to cascade deletes on my has_many relationships? |
117 | |
118 | By default, DBIx::Class cascades deletes and updates across |
119 | C<has_many> relationships. If your database already does this (and |
e147365d |
120 | that is probably better), turn it off by supplying C<< cascade_delete => 0 >> |
121 | in the relationship attributes. See L<DBIx::Class::Relationship::Base>. |
81791ac3 |
122 | |
123 | =item .. use a relationship? |
124 | |
e147365d |
125 | Use its name. An accessor is created using the name. See examples in |
126 | L<DBIx::Class::Manual::Cookbook/Using relationships>. |
81791ac3 |
127 | |
128 | =back |
129 | |
130 | =head2 Searching |
131 | |
132 | =over 4 |
133 | |
134 | =item .. search for data? |
135 | |
7f613f3a |
136 | Create a C<$schema> object, as mentioned above in ".. connect to my |
e147365d |
137 | database". Find the L<ResultSet|DBIx::Class::Manual::Glossary/ResultSet> |
138 | that you want to search in, and call C<search> on it. See |
7f613f3a |
139 | L<DBIx::Class::ResultSet/search>. |
140 | |
81791ac3 |
141 | =item .. search using database functions? |
142 | |
7f613f3a |
143 | Supplying something like: |
144 | |
145 | ->search({'mydatefield' => 'now()'}) |
146 | |
147 | to search, will probably not do what you expect. It will quote the |
148 | text "now()", instead of trying to call the function. To provide |
149 | literal, unquoted text you need to pass in a scalar reference, like |
150 | so: |
151 | |
152 | ->search({'mydatefield' => \'now()'}) |
153 | |
81791ac3 |
154 | =item .. sort the results of my search? |
155 | |
e147365d |
156 | Supply a list of columns you want to sort by to the C<order_by> |
157 | attribute. See L<DBIx::Class::ResultSet/order_by>. |
7f613f3a |
158 | |
159 | =item .. sort my results based on fields I've aliased using C<as>? |
160 | |
161 | You don't. You'll need to supply the same functions/expressions to |
e147365d |
162 | C<order_by>, as you did to C<select>. |
b5871402 |
163 | |
e147365d |
164 | To get "fieldname AS alias" in your SQL, you'll need to supply a |
165 | literal chunk of SQL in your C<select> attribute, such as: |
b5871402 |
166 | |
167 | ->search({}, { select => [ \'now() AS currenttime'] }) |
168 | |
169 | Then you can use the alias in your C<order_by> attribute. |
7f613f3a |
170 | |
81791ac3 |
171 | =item .. group the results of my search? |
172 | |
7f613f3a |
173 | Supply a list of columns you want to group on, to the C<group_by> |
174 | attribute, see L<DBIx::Class::ResultSet/group_by>. |
175 | |
176 | =item .. group my results based on fields I've aliased using C<as>? |
177 | |
178 | You don't. You'll need to supply the same functions/expressions to |
179 | C<group_by>, as you did to C<select>. |
180 | |
b5871402 |
181 | To get "fieldname AS alias" in your SQL, you'll need to supply a |
182 | literal chunk of SQL in your C<select> attribute, such as: |
183 | |
184 | ->search({}, { select => [ \'now() AS currenttime'] }) |
185 | |
186 | Then you can use the alias in your C<group_by> attribute. |
187 | |
81791ac3 |
188 | =item .. filter the results of my search? |
189 | |
b5871402 |
190 | The first argument to C<search> is a hashref of accessor names and |
191 | values to filter them by, for example: |
192 | |
36d41f4c |
193 | ->search({'created_time' => { '>=', '2006-06-01 00:00:00' } }) |
b5871402 |
194 | |
195 | Note that to use a function here you need to make the whole value into |
196 | a scalar reference: |
197 | |
36d41f4c |
198 | ->search({'created_time' => \'>= yesterday()' }) |
b5871402 |
199 | |
81791ac3 |
200 | =item .. search in several tables simultaneously? |
201 | |
b5871402 |
202 | To search in two related tables, you first need to set up appropriate |
203 | relationships between their respective classes. When searching you |
204 | then supply the name of the relationship to the C<join> attribute in |
205 | your search, for example when searching in the Books table for all the |
206 | books by the author "Fred Bloggs": |
207 | |
36d41f4c |
208 | ->search({'authors.name' => 'Fred Bloggs'}, { join => 'authors' }) |
b5871402 |
209 | |
210 | The type of join created in your SQL depends on the type of |
211 | relationship between the two tables, see L<DBIx::Class::Relationship> |
212 | for the join used by each relationship. |
213 | |
7f613f3a |
214 | =item .. create joins with conditions other than column equality? |
215 | |
b5871402 |
216 | Currently, L<DBIx::Class> can only create join conditions using |
f7a90adc |
217 | equality, so you're probably better off creating a C<view> in your |
e147365d |
218 | database, and using that as your source. A C<view> is a stored SQL |
219 | query, which can be accessed similarly to a table, see your database |
f7a90adc |
220 | documentation for details. |
b5871402 |
221 | |
7f613f3a |
222 | =item .. search using greater-than or less-than and database functions? |
7f613f3a |
223 | |
b5871402 |
224 | To use functions or literal SQL with conditions other than equality |
225 | you need to supply the entire condition, for example: |
226 | |
227 | my $interval = "< now() - interval '12 hours'"; |
228 | ->search({last_attempt => \$interval}) |
229 | |
230 | and not: |
231 | |
232 | my $interval = "now() - interval '12 hours'"; |
233 | ->search({last_attempt => { '<' => \$interval } }) |
7f613f3a |
234 | |
81791ac3 |
235 | =item .. find more help on constructing searches? |
236 | |
237 | Behind the scenes, DBIx::Class uses L<SQL::Abstract> to help construct |
e147365d |
238 | its SQL searches. So if you fail to find help in the |
81791ac3 |
239 | L<DBIx::Class::Manual::Cookbook>, try looking in the SQL::Abstract |
240 | documentation. |
241 | |
242 | =back |
243 | |
244 | =head2 Fetching data |
245 | |
246 | =over 4 |
247 | |
248 | =item .. fetch as much data as possible in as few select calls as possible? (prefetch) |
249 | |
b5871402 |
250 | See the prefetch examples in the L<Cookbook|DBIx::Class::Manual::Cookbook>. |
81791ac3 |
251 | |
252 | =back |
253 | |
81791ac3 |
254 | =head2 Inserting and updating data |
255 | |
256 | =over 4 |
257 | |
b5871402 |
258 | =item .. insert a row with an auto incrementing primary key? |
259 | |
260 | In versions of L<DBIx::Class> less than 0.07, you need to ensure your |
261 | table class loads the L<PK::Auto|DBIx::Class::PK::Auto> |
262 | component. This will attempt to fetch the value of your primary key |
263 | from the database after the insert has happened, and store it in the |
264 | created object. In versions 0.07 and above, this component is |
265 | automatically loaded. |
266 | |
267 | =item .. insert a row with a primary key that uses a sequence? |
268 | |
269 | You need to create a trigger in your database that updates your |
270 | primary key field from the sequence. To help PK::Auto find your |
271 | inserted key, you can tell it the name of the sequence in the |
272 | C<column_info> supplied with C<add_columns>. |
273 | |
274 | ->add_columns({ id => { sequence => 'mysequence' } }); |
275 | |
81791ac3 |
276 | =item .. insert many rows of data efficiently? |
277 | |
278 | =item .. update a collection of rows at the same time? |
279 | |
b5871402 |
280 | Create a resultset using a search, to filter the rows of data you |
281 | would like to update, then call update on the resultset to change all |
282 | the rows at once. |
283 | |
81791ac3 |
284 | =item .. use database functions when updating rows? |
285 | |
286 | =item .. update a column using data from another column? |
287 | |
b5871402 |
288 | To stop the column name from being quoted, you'll need to supply a |
289 | scalar reference: |
290 | |
fb5fb63c |
291 | ->update({ somecolumn => \'othercolumn' }) |
b5871402 |
292 | |
81791ac3 |
293 | =back |
294 | |
295 | =head2 Misc |
296 | |
297 | =over 4 |
298 | |
299 | =item How do I store my own (non-db) data in my DBIx::Class objects? |
300 | |
b5871402 |
301 | You can add your own data accessors to your classes. |
302 | |
f7a90adc |
303 | =item How do I use DBIx::Class objects in my TT templates? |
81791ac3 |
304 | |
e147365d |
305 | Like normal objects, mostly. However you need to watch out for TT |
306 | calling methods in list context. When calling relationship accessors |
307 | you will not get resultsets, but a list of all the related objects. |
308 | |
309 | Starting with version 0.07, you can use L<DBIx::Class::ResultSet/search_rs> |
310 | to work around this issue. |
b5871402 |
311 | |
81791ac3 |
312 | =item See the SQL statements my code is producing? |
313 | |
85f78622 |
314 | Turn on debugging! See L<DBIx::Class::Storage> for details of how |
f7a90adc |
315 | to turn on debugging in the environment, pass your own filehandle to |
316 | save debug to, or create your own callback. |
b5871402 |
317 | |
81791ac3 |
318 | =item Why didn't my search run any SQL? |
319 | |
b5871402 |
320 | L<DBIx::Class> runs the actual SQL statement as late as possible, thus |
321 | if you create a resultset using C<search> in scalar context, no query |
322 | is executed. You can create further resultset refinements by calling |
323 | search again or relationship accessors. The SQL query is only run when |
e147365d |
324 | you ask the resultset for an actual row object. |
81791ac3 |
325 | |
326 | =back |