misc POD updates related to Storage/Storage::DBI
[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / FAQ.pod
CommitLineData
81791ac3 1=head1 NAME
2
3DBIx::Class::Manual::FAQ - Frequently Asked Questions (in theory)
4
5=head1 DESCRIPTION
6
7This document is intended as an anti-map of the documentation. If you
8know what you want to do, but not how to do it in L<DBIx::Class>, then
b5871402 9look here. It does B<not> contain much code or examples, it just gives
81791ac3 10explanations and pointers to the correct pieces of documentation to
11read.
12
13=head1 FAQs
14
15How Do I:
16
17=head2 Getting started
18
19=over 4
20
21=item .. create a database to use?
22
23First, choose a database. For testing/experimenting, we reccommend
e147365d 24L<DBD::SQLite>, which is a self-contained small database (i.e. all you
25need to do is to install L<DBD::SQLite> from CPAN, and it's usable).
81791ac3 26
27Next, spend some time defining which data you need to store, and how
28it relates to the other data you have. For some help on normalisation,
29go to L<http://b62.tripod.com/doc/dbbase.htm> or
30L<http://209.197.234.36/db/simple.html>.
31
32Now, decide whether you want to have the database itself be the
33definitive source of information about the data layout, or your
34DBIx::Class schema. If it's the former, look up the documentation for
35your database, eg. L<http://sqlite.org/lang_createtable.html>, on how
36to create tables, and start creating them. For a nice universal
37interface to your database, you can try L<DBI::Shell>. If you decided
38on the latter choice, read the FAQ on setting up your classes
39manually, and the one on creating tables from your schema.
40
41=item .. use DBIx::Class with L<Catalyst>?
42
e147365d 43Install L<Catalyst::Model::DBIC::Schema> from CPAN. See its
81791ac3 44documentation, or below, for further details.
45
46=item .. set up my DBIx::Class classes automatically from my database?
47
e147365d 48Install L<DBIx::Class::Schema::Loader> from CPAN, and read its documentation.
81791ac3 49
50=item .. set up my DBIx::Class classes manually?
51
e147365d 52Look 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
56Create your classes manually, as above. Write a script that calls
57L<DBIx::Class::Schema/deploy>. See there for details, or the
58L<DBIx::Class::Manual::Cookbook>.
59
7f613f3a 60=item .. connect to my database?
61
62Once you have created all the appropriate table/source classes, and an
b5871402 63overall L<Schema|DBIx::Class::Schema> class, you can start using
7f613f3a 64them in an application. To do this, you need to create a central
65Schema object, which is used to access all the data in the various
66tables. See L<DBIx::Class::Schema/connect> for details. The actual
67connection does not happen until you actually request data, so don't
68be alarmed if the error from incorrect connection details happens a
69lot 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 80There are a vareity of relationship types that come pre-defined for
81you to use. These are all listed in L<DBIx::Class::Relationship>. If
82you need a non-standard type, or more information, look in
83L<DBIx::Class::Relationship::Base>.
81791ac3 84
85=item .. define a one-to-many relationship?
86
e147365d 87This is called a C<has_many> relationship on the one side, and a
88C<belongs_to> relationship on the many side. Currently these need to
89be set up individually on each side. See L<DBIx::Class::Relationship>
90for details.
81791ac3 91
92=item .. define a relationship where this table contains another table's primary key? (foreign key)
93
e147365d 94Create a C<belongs_to> relationship for the field containing the
95foreign 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 99Just create a C<belongs_to> relationship, as above. If the column is
100NULL then the inflation to the foreign object will not happen. This
101has a side effect of not always fetching all the relevant data, if you
102use a nullable foreign-key relationship in a JOIN, then you probably
103want 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
107Instead of supplying a single column name, all relationship types also
108allow you to supply a hashref containing the condition across which
109the tables are to be joined. The condition may contain as many fields
110as you like. See L<DBIx::Class::Relationship::Base>.
111
112=item .. define a relatiopnship across an intermediate table? (many-to-many)
113
114Read 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
118By default, DBIx::Class cascades deletes and updates across
119C<has_many> relationships. If your database already does this (and
e147365d 120that is probably better), turn it off by supplying C<< cascade_delete => 0 >>
121in the relationship attributes. See L<DBIx::Class::Relationship::Base>.
81791ac3 122
123=item .. use a relationship?
124
e147365d 125Use its name. An accessor is created using the name. See examples in
126L<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 136Create a C<$schema> object, as mentioned above in ".. connect to my
e147365d 137database". Find the L<ResultSet|DBIx::Class::Manual::Glossary/ResultSet>
138that you want to search in, and call C<search> on it. See
7f613f3a 139L<DBIx::Class::ResultSet/search>.
140
81791ac3 141=item .. search using database functions?
142
7f613f3a 143Supplying something like:
144
145 ->search({'mydatefield' => 'now()'})
146
147to search, will probably not do what you expect. It will quote the
148text "now()", instead of trying to call the function. To provide
149literal, unquoted text you need to pass in a scalar reference, like
150so:
151
152 ->search({'mydatefield' => \'now()'})
153
81791ac3 154=item .. sort the results of my search?
155
e147365d 156Supply a list of columns you want to sort by to the C<order_by>
157attribute. See L<DBIx::Class::ResultSet/order_by>.
7f613f3a 158
159=item .. sort my results based on fields I've aliased using C<as>?
160
161You don't. You'll need to supply the same functions/expressions to
e147365d 162C<order_by>, as you did to C<select>.
b5871402 163
e147365d 164To get "fieldname AS alias" in your SQL, you'll need to supply a
165literal chunk of SQL in your C<select> attribute, such as:
b5871402 166
167 ->search({}, { select => [ \'now() AS currenttime'] })
168
169Then you can use the alias in your C<order_by> attribute.
7f613f3a 170
81791ac3 171=item .. group the results of my search?
172
7f613f3a 173Supply a list of columns you want to group on, to the C<group_by>
174attribute, see L<DBIx::Class::ResultSet/group_by>.
175
176=item .. group my results based on fields I've aliased using C<as>?
177
178You don't. You'll need to supply the same functions/expressions to
179C<group_by>, as you did to C<select>.
180
b5871402 181To get "fieldname AS alias" in your SQL, you'll need to supply a
182literal chunk of SQL in your C<select> attribute, such as:
183
184 ->search({}, { select => [ \'now() AS currenttime'] })
185
186Then you can use the alias in your C<group_by> attribute.
187
81791ac3 188=item .. filter the results of my search?
189
b5871402 190The first argument to C<search> is a hashref of accessor names and
191values to filter them by, for example:
192
36d41f4c 193 ->search({'created_time' => { '>=', '2006-06-01 00:00:00' } })
b5871402 194
195Note that to use a function here you need to make the whole value into
196a scalar reference:
197
36d41f4c 198 ->search({'created_time' => \'>= yesterday()' })
b5871402 199
81791ac3 200=item .. search in several tables simultaneously?
201
b5871402 202To search in two related tables, you first need to set up appropriate
203relationships between their respective classes. When searching you
204then supply the name of the relationship to the C<join> attribute in
205your search, for example when searching in the Books table for all the
206books by the author "Fred Bloggs":
207
36d41f4c 208 ->search({'authors.name' => 'Fred Bloggs'}, { join => 'authors' })
b5871402 209
210The type of join created in your SQL depends on the type of
211relationship between the two tables, see L<DBIx::Class::Relationship>
212for the join used by each relationship.
213
7f613f3a 214=item .. create joins with conditions other than column equality?
215
b5871402 216Currently, L<DBIx::Class> can only create join conditions using
f7a90adc 217equality, so you're probably better off creating a C<view> in your
e147365d 218database, and using that as your source. A C<view> is a stored SQL
219query, which can be accessed similarly to a table, see your database
f7a90adc 220documentation for details.
b5871402 221
7f613f3a 222=item .. search using greater-than or less-than and database functions?
7f613f3a 223
b5871402 224To use functions or literal SQL with conditions other than equality
225you need to supply the entire condition, for example:
226
227 my $interval = "< now() - interval '12 hours'";
228 ->search({last_attempt => \$interval})
229
230and 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
237Behind the scenes, DBIx::Class uses L<SQL::Abstract> to help construct
e147365d 238its SQL searches. So if you fail to find help in the
81791ac3 239L<DBIx::Class::Manual::Cookbook>, try looking in the SQL::Abstract
240documentation.
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 250See 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
260In versions of L<DBIx::Class> less than 0.07, you need to ensure your
261table class loads the L<PK::Auto|DBIx::Class::PK::Auto>
262component. This will attempt to fetch the value of your primary key
263from the database after the insert has happened, and store it in the
264created object. In versions 0.07 and above, this component is
265automatically loaded.
266
267=item .. insert a row with a primary key that uses a sequence?
268
269You need to create a trigger in your database that updates your
270primary key field from the sequence. To help PK::Auto find your
271inserted key, you can tell it the name of the sequence in the
272C<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 280Create a resultset using a search, to filter the rows of data you
281would like to update, then call update on the resultset to change all
282the 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 288To stop the column name from being quoted, you'll need to supply a
289scalar 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 301You 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 305Like normal objects, mostly. However you need to watch out for TT
306calling methods in list context. When calling relationship accessors
307you will not get resultsets, but a list of all the related objects.
308
309Starting with version 0.07, you can use L<DBIx::Class::ResultSet/search_rs>
310to work around this issue.
b5871402 311
81791ac3 312=item See the SQL statements my code is producing?
313
85f78622 314Turn on debugging! See L<DBIx::Class::Storage> for details of how
f7a90adc 315to turn on debugging in the environment, pass your own filehandle to
316save debug to, or create your own callback.
b5871402 317
81791ac3 318=item Why didn't my search run any SQL?
319
b5871402 320L<DBIx::Class> runs the actual SQL statement as late as possible, thus
321if you create a resultset using C<search> in scalar context, no query
322is executed. You can create further resultset refinements by calling
323search again or relationship accessors. The SQL query is only run when
e147365d 324you ask the resultset for an actual row object.
81791ac3 325
326=back