1DBIx::Class::Manual::FAUQs(e3r)Contributed Perl DocumentDaBtIixo:n:Class::Manual::FAQ(3)
2
6 DBIx::Class::Manual::FAQ - Frequently Asked Questions (in theory)
7
9 This document is intended as an anti-map of the documentation. If you
10 know what you want to do, but not how to do it in DBIx::Class, then
11 look here. It does not contain much code or examples, it just gives
12 explanations and pointers to the correct pieces of documentation to
13 read.
14
16 How Do I:
17 Getting started
19 .. create a database to use?
20 First, choose a database. For testing/experimenting, we reccommend
21 DBD::SQLite, which is a self-contained small database (i.e. all you
22 need to do is to install DBD::SQLite from CPAN, and it's usable).
23 Next, spend some time defining which data you need to store, and
25 how it relates to the other data you have. For some help on
26 normalisation, go to <http://b62.tripod.com/doc/dbbase.htm>.
27 Now, decide whether you want to have the database itself be the
29 definitive source of information about the data layout, or your
30 DBIx::Class schema. If it's the former, look up the documentation
31 for your database, eg. <http://sqlite.org/lang_createtable.html>,
32 on how to create tables, and start creating them. For a nice
33 universal interface to your database, you can try DBI::Shell. If
34 you decided on the latter choice, read the FAQ on setting up your
35 classes manually, and the one on creating tables from your schema.
36 .. use DBIx::Class with Catalyst?
38 Install Catalyst::Model::DBIC::Schema from CPAN. See its
39 documentation, or below, for further details.
40 .. set up my DBIx::Class classes automatically from my database?
42 Install DBIx::Class::Schema::Loader from CPAN, and read its
43 documentation.
44 .. set up my DBIx::Class classes manually?
46 Look at the DBIx::Class::Manual::Example and come back here if you
47 get lost.
48 .. create my database tables from my DBIx::Class schema?
50 Create your classes manually, as above. Write a script that calls
51 "deploy" in DBIx::Class::Schema. See there for details, or the
52 DBIx::Class::Manual::Cookbook.
53 .. connect to my database?
55 Once you have created all the appropriate table/source classes, and
56 an overall Schema class, you can start using them in an
57 application. To do this, you need to create a central Schema
58 object, which is used to access all the data in the various tables.
59 See "connect" in DBIx::Class::Schema for details. The actual
60 connection does not happen until you actually request data, so
61 don't be alarmed if the error from incorrect connection details
62 happens a lot later.
63 .. use DBIx::Class across multiple databases?
65 If your database server allows you to run querys across multiple
66 databases at once, then so can DBIx::Class. All you need to do is
67 make sure you write the database name as part of the "table" in
68 DBIx::Class::ResultSource call. Eg:
69 __PACKAGE__->table('mydb.mytablename');
71 And load all the Result classes for both / all databases using one
73 "load_namespaces" in DBIx::Class::Schema call.
74 .. use DBIx::Class across PostgreSQL/DB2/Oracle schemas?
76 Add the name of the schema to the "table" in
77 DBIx::Class::ResultSource as part of the name, and make sure you
78 give the one user you are going to connect with rights to
79 read/write all the schemas/tables as necessary.
80 Relationships
82 .. tell DBIx::Class about relationships between my tables?
83 There are a variety of relationship types that come pre-defined for
84 you to use. These are all listed in DBIx::Class::Relationship. If
85 you need a non-standard type, or more information, look in
86 DBIx::Class::Relationship::Base.
87 .. define a one-to-many relationship?
89 This is called a "has_many" relationship on the one side, and a
90 "belongs_to" relationship on the many side. Currently these need to
91 be set up individually on each side. See DBIx::Class::Relationship
92 for details.
93 .. define a relationship where this table contains another table's
95 primary key? (foreign key)
96 Create a "belongs_to" relationship for the field containing the
97 foreign key. See "belongs_to" in DBIx::Class::Relationship.
98 .. define a foreign key relationship where the key field may contain
100 NULL?
101 Just create a "belongs_to" relationship, as above. If the column is
102 NULL then the inflation to the foreign object will not happen. This
103 has a side effect of not always fetching all the relevant data, if
104 you use a nullable foreign-key relationship in a JOIN, then you
105 probably want to set the "join_type" to "left".
106 .. define a relationship where the key consists of more than one
108 column?
109 Instead of supplying a single column name, all relationship types
110 also allow you to supply a hashref containing the condition across
111 which the tables are to be joined. The condition may contain as
112 many fields as you like. See DBIx::Class::Relationship::Base.
113 .. define a relatiopnship across an intermediate table? (many-to-many)
115 Read the documentation on "many_to_many" in
116 DBIx::Class::Relationship.
117 .. stop DBIx::Class from attempting to cascade deletes on my has_many
119 and might_have relationships?
120 By default, DBIx::Class cascades deletes and updates across
121 "has_many" and "might_have" relationships. You can disable this
122 behaviour on a per-relationship basis by supplying "cascade_delete
123 => 0" in the relationship attributes.
124 The cascaded operations are performed after the requested delete or
126 update, so if your database has a constraint on the relationship,
127 it will have deleted/updated the related records or raised an
128 exception before DBIx::Class gets to perform the cascaded
129 operation.
130 See DBIx::Class::Relationship.
132 .. use a relationship?
134 Use its name. An accessor is created using the name. See examples
135 in "Using relationships" in DBIx::Class::Manual::Cookbook.
136 Searching
138 .. search for data?
139 Create a $schema object, as mentioned above in ".. connect to my
140 database". Find the ResultSet that you want to search in, and call
141 "search" on it. See "search" in DBIx::Class::ResultSet.
142 .. search using database functions?
144 Supplying something like:
145 ->search({'mydatefield' => 'now()'})
147 to search, will probably not do what you expect. It will quote the
149 text "now()", instead of trying to call the function. To provide
150 literal, unquoted text you need to pass in a scalar reference, like
151 so:
152 ->search({'mydatefield' => \'now()'})
154 .. sort the results of my search?
156 Supply a list of columns you want to sort by to the "order_by"
157 attribute. See "order_by" in DBIx::Class::ResultSet.
158 .. sort my results based on fields I've aliased using "as"?
160 You don't. You'll need to supply the same functions/expressions to
161 "order_by", as you did to "select".
162 To get "fieldname AS alias" in your SQL, you'll need to supply a
164 literal chunk of SQL in your "select" attribute, such as:
165 ->search({}, { select => [ \'now() AS currenttime'] })
167 Then you can use the alias in your "order_by" attribute.
169 .. group the results of my search?
171 Supply a list of columns you want to group on, to the "group_by"
172 attribute, see "group_by" in DBIx::Class::ResultSet.
173 .. group my results based on fields I've aliased using "as"?
175 You don't. You'll need to supply the same functions/expressions to
176 "group_by", as you did to "select".
177 To get "fieldname AS alias" in your SQL, you'll need to supply a
179 literal chunk of SQL in your "select" attribute, such as:
180 ->search({}, { select => [ \'now() AS currenttime'] })
182 Then you can use the alias in your "group_by" attribute.
184 .. filter the results of my search?
186 The first argument to "search" is a hashref of accessor names and
187 values to filter them by, for example:
188 ->search({'created_time' => { '>=', '2006-06-01 00:00:00' } })
190 Note that to use a function here you need to make it a scalar
192 reference:
193 ->search({'created_time' => { '>=', \'yesterday()' } })
195 .. search in several tables simultaneously?
197 To search in two related tables, you first need to set up
198 appropriate relationships between their respective classes. When
199 searching you then supply the name of the relationship to the
200 "join" attribute in your search, for example when searching in the
201 Books table for all the books by the author "Fred Bloggs":
202 ->search({'authors.name' => 'Fred Bloggs'}, { join => 'authors' })
204 The type of join created in your SQL depends on the type of
206 relationship between the two tables, see DBIx::Class::Relationship
207 for the join used by each relationship.
208 .. create joins with conditions other than column equality?
210 Currently, DBIx::Class can only create join conditions using
211 equality, so you're probably better off creating a "view" in your
212 database, and using that as your source. A "view" is a stored SQL
213 query, which can be accessed similarly to a table, see your
214 database documentation for details.
215 .. search with an SQL function on the left hand side?
217 To use an SQL function on the left hand side of a comparison:
218 ->search({ -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ] });
220 Note: the "plain_value" string in the "[ plain_value => 1979 ]"
222 part should be either the same as the name of the column (do this
223 if the type of the return value of the function is the same as the
224 type of the column) or otherwise it's essentially a dummy string
225 currently (use "plain_value" as a habit). It is used by DBIx::Class
226 to handle special column types.
227 Or, if you have quoting off:
229 ->search({ 'YEAR(date_of_birth)' => 1979 });
231 .. find more help on constructing searches?
233 Behind the scenes, DBIx::Class uses SQL::Abstract to help construct
234 its SQL searches. So if you fail to find help in the
235 DBIx::Class::Manual::Cookbook, try looking in the SQL::Abstract
236 documentation.
237 .. make searches in Oracle (10gR2 and newer) case-insensitive?
239 To make Oracle behave like most RDBMS use on_connect_do to issue
240 alter session statements on database connection establishment:
241 ->on_connect_do("ALTER SESSION SET NLS_COMP = 'LINGUISTIC'");
243 ->on_connect_do("ALTER SESSION SET NLS_SORT = '<NLS>_CI'");
244 e.g.
245 ->on_connect_do("ALTER SESSION SET NLS_SORT = 'BINARY_CI'");
246 ->on_connect_do("ALTER SESSION SET NLS_SORT = 'GERMAN_CI'");
247 Fetching data
249 .. fetch as much data as possible in as few select calls as possible?
250 See the prefetch examples in the Cookbook.
251 .. fetch a whole column of data instead of a row?
253 Call "get_column" on a DBIx::Class::ResultSet. This returns a
254 DBIx::Class::ResultSetColumn. See its documentation and the
255 Cookbook for details.
256 .. fetch a formatted column?
258 In your table schema class, create a "private" column accessor
259 with:
260 __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' });
262 Then, in the same class, implement a subroutine called "my_column"
264 that fetches the real value and does the formatting you want.
265 See the Cookbook for more details.
267 .. fetch a single (or topmost) row?
269 See "Retrieve_one_and_only_one_row_from_a_resultset" in
270 DBIx::Class::Manual::Cookbook.
271 A less readable way is to ask a regular search to return 1 row,
273 using "slice" in DBIx::Class::ResultSet:
274 ->search->(undef, { order_by => "id DESC" })->slice(0)
276 which (if supported by the database) will use LIMIT/OFFSET to hint
278 to the database that we really only need one row. This can result
279 in a significant speed improvement. The method using "single" in
280 DBIx::Class::ResultSet mentioned in the cookbook can do the same if
281 you pass a "rows" attribute to the search.
282 .. refresh a row from storage?
284 Use "discard_changes" in DBIx::Class::PK.
285 $row->discard_changes
287 Discarding changes and refreshing from storage are two sides fo the
289 same coin. When you want to discard your local changes, just re-
290 fetch the row from storage. When you want to get a new, fresh copy
291 of the row, just re-fetch the row from storage. "discard_changes"
292 in DBIx::Class::PK does just that by re-fetching the row from
293 storage using the row's primary key.
294 .. fetch my data a "page" at a time?
296 Pass the "rows" and "page" attributes to your search, eg:
297 ->search({}, { rows => 10, page => 1});
299 .. get a count of all rows even when paging?
301 Call "pager" on the paged resultset, it will return a Data::Page
302 object. Calling "total_entries" on the pager will return the
303 correct total.
304 "count" on the resultset will only return the total number in the
306 page.
307 Inserting and updating data
309 .. insert a row with an auto incrementing primary key?
310 In versions of DBIx::Class less than 0.07, you need to ensure your
311 table class loads the PK::Auto component. This will attempt to
312 fetch the value of your primary key from the database after the
313 insert has happened, and store it in the created object. In
314 versions 0.07 and above, this component is automatically loaded.
315 .. insert a row with a primary key that uses a sequence?
317 You need to create a trigger in your database that updates your
318 primary key field from the sequence. To help PK::Auto find your
319 inserted key, you can tell it the name of the sequence in the
320 "column_info" supplied with "add_columns".
321 ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
323 .. insert many rows of data efficiently?
325 The "populate" method in DBIx::Class::ResultSet provides efficient
326 bulk inserts.
327 .. update a collection of rows at the same time?
329 Create a resultset using a search, to filter the rows of data you
330 would like to update, then call update on the resultset to change
331 all the rows at once.
332 .. use database functions when updating rows?
334 .. update a column using data from another column?
335 To stop the column name from being quoted, you'll need to supply a
336 scalar reference:
337 ->update({ somecolumn => \'othercolumn' })
339 But note that when using a scalar reference the column in the
341 database will be updated but when you read the value from the
342 object with e.g.
343 ->somecolumn()
345 you still get back the scalar reference to the string, not the new
347 value in the database. To get that you must refresh the row from
348 storage using "discard_changes()". Or chain your function calls
349 like this:
350 ->update->discard_changes
352 to update the database and refresh the object in one step.
354 .. store JSON/YAML in a column and have it deflate/inflate
356 automatically?
357 You can use DBIx::Class::InflateColumn to accomplish YAML/JSON
358 storage transparently.
359 If you want to use JSON, then in your table schema class, do the
361 following:
362 use JSON;
364 __PACKAGE__->add_columns(qw/ ... my_column ../)
366 __PACKAGE__->inflate_column('my_column', {
367 inflate => sub { jsonToObj(shift) },
368 deflate => sub { objToJson(shift) },
369 });
370 For YAML, in your table schema class, do the following:
372 use YAML;
374 __PACKAGE__->add_columns(qw/ ... my_column ../)
376 __PACKAGE__->inflate_column('my_column', {
377 inflate => sub { YAML::Load(shift) },
378 deflate => sub { YAML::Dump(shift) },
379 });
380 This technique is an easy way to store supplemental unstructured
382 data in a table. Be careful not to overuse this capability,
383 however. If you find yourself depending more and more on some data
384 within the inflated column, then it may be time to factor that data
385 out.
386 Custom methods in Result classes
388 You can add custom methods that do arbitrary things, even to unrelated
389 tables. For example, to provide a "$book->foo()" method which searches
390 the cd table, you'd could add this to Book.pm:
391 sub foo {
393 my ($self, $col_data) = @_;
394 return $self->result_source->schema->resultset('cd')->search($col_data);
395 }
396 And invoke that on any Book Result object like so:
398 my $rs = $book->foo({ title => 'Down to Earth' });
400 When two tables ARE related, DBIx::Class::Relationship::Base provides
402 many methods to find or create data in related tables for you. But if
403 you want to write your own methods, you can.
404 For example, to provide a "$book->foo()" method to manually implement
406 what create_related() from DBIx::Class::Relationship::Base does, you
407 could add this to Book.pm:
408 sub foo {
410 my ($self, $relname, $col_data) = @_;
411 return $self->related_resultset($relname)->create($col_data);
412 }
413 Invoked like this:
415 my $author = $book->foo('author', { name => 'Fred' });
417 Misc
419 How do I store my own (non-db) data in my DBIx::Class objects?
420 You can add your own data accessors to your classes.
421 One method is to use the built in mk_group_accessors (via
423 Class::Accessor::Grouped)
424 package MyTable;
426 use parent 'DBIx::Class';
428 __PACKAGE__->table('foo'); #etc
430 __PACKAGE__->mk_group_accessors('simple' => qw/non_column_data/); # must use simple group
431 An another method is to use Moose with your DBIx::Class package.
433 package MyTable;
435 use Moose; # import Moose
437 use Moose::Util::TypeConstraint; # import Moose accessor type constraints
438 extends 'DBIx::Class'; # Moose changes the way we define our parent (base) package
440 has 'non_column_data' => ( is => 'rw', isa => 'Str' ); # define a simple attribute
442 __PACKAGE__->table('foo'); # etc
444 With either of these methods the resulting use of the accesssor
446 would be
447 my $row;
449 # assume that somewhere in here $row will get assigned to a MyTable row
451 $row->non_column_data('some string'); # would set the non_column_data accessor
453 # some other stuff happens here
455 $row->update(); # would not inline the non_column_data accessor into the update
457 How do I use DBIx::Class objects in my TT templates?
459 Like normal objects, mostly. However you need to watch out for TT
460 calling methods in list context. When calling relationship
461 accessors you will not get resultsets, but a list of all the
462 related objects.
463 Starting with version 0.07, you can use "search_rs" in
465 DBIx::Class::ResultSet to work around this issue.
466 See the SQL statements my code is producing?
468 Turn on debugging! See DBIx::Class::Storage for details of how to
469 turn on debugging in the environment, pass your own filehandle to
470 save debug to, or create your own callback.
471 Why didn't my search run any SQL?
473 DBIx::Class runs the actual SQL statement as late as possible, thus
474 if you create a resultset using "search" in scalar context, no
475 query is executed. You can create further resultset refinements by
476 calling search again or relationship accessors. The SQL query is
477 only run when you ask the resultset for an actual row object.
478 How do I deal with tables that lack a primary key?
480 If your table lacks a primary key, DBIx::Class can't work out which
481 row it should operate on, for example to delete or update.
482 However, a UNIQUE constraint on one or more columns allows
483 DBIx::Class to uniquely identify the row, so you can tell
484 DBIx::Class::ResultSource these columns act as a primary key, even
485 if they don't from the database's point of view:
486 $resultset->set_primary_key(@column);
488 How do I make my program start faster?
490 Look at the tips in "STARTUP SPEED" in
491 DBIx::Class::Manual::Cookbook
492 How do I reduce the overhead of database queries?
494 You can reduce the overhead of object creation within DBIx::Class
495 using the tips in "Skip row object creation for faster results" in
496 DBIx::Class::Manual::Cookbook and "Get raw data for blindingly fast
497 results" in DBIx::Class::Manual::Cookbook
498 How do I override a run time method (e.g. a relationship accessor)?
500 If you need access to the original accessor, then you must "wrap
501 around" the original method. You can do that either with
502 Moose::Manual::MethodModifiers or Class::Method::Modifiers. The
503 code example works for both modules:
504 package Your::Schema::Group;
506 use Class::Method::Modifiers;
507 # ... declare columns ...
509 __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
511 __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
512 # if the server group is a "super group", then return all servers
514 # otherwise return only servers that belongs to the given group
515 around 'servers' => sub {
516 my $orig = shift;
517 my $self = shift;
518 return $self->$orig(@_) unless $self->is_super_group;
520 return $self->result_source->schema->resultset('Server')->all;
521 };
522 If you just want to override the original method, and don't care
524 about the data from the original accessor, then you have two
525 options. Either use Method::Signatures::Simple that does most of
526 the work for you, or do it the "dirty way".
527 Method::Signatures::Simple way:
529 package Your::Schema::Group;
531 use Method::Signatures::Simple;
532 # ... declare columns ...
534 __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
536 __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
537 # The method keyword automatically injects the annoying my $self = shift; for you.
539 method servers {
540 return $self->result_source->schema->resultset('Server')->search({ ... });
541 }
542 The dirty way:
544 package Your::Schema::Group;
546 use Sub::Name;
547 # ... declare columns ...
549 __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
551 __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
552 *servers = subname servers => sub {
554 my $self = shift;
555 return $self->result_source->schema->resultset('Server')->search({ ... });
556 };
557 Notes for CDBI users
559 Is there a way to make an object auto-stringify itself as a particular
560 column or group of columns (a-la cdbi Stringfy column group, or
561 stringify_self method) ?
562 See "Stringification" in DBIx::Class::Manual::Cookbook
563 Troubleshooting
565 Help, I can't connect to postgresql!
566 If you get an error such as:
567 DBI connect('dbname=dbic','user',...) failed: could not connect to server:
569 No such file or directory Is the server running locally and accepting
570 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
571 Likely you have/had two copies of postgresql installed
573 simultaneously, the second one will use a default port of 5433,
574 while DBD::Pg is compiled with a default port of 5432.
575 You can change the port setting in "postgresql.conf".
577 I've lost or forgotten my mysql password
579 Stop mysqld and restart it with the --skip-grant-tables option.
580 Issue the following statements in the mysql client.
582 UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
584 FLUSH PRIVILEGES;
585 Restart mysql.
587 Taken from:
589 http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html
591 <http://dev.mysql.com/doc/refman/5.1/en/resetting-
592 permissions.html>.
593perl v5.12.0 2010-05-12 DBIx::Class::Manual::FAQ(3)