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