X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FFAQ.pod;h=7d4505ec0a09dc2d20ea34b6f0f245f39b30553b;hb=dcdf7b2cd485cc015ddbfd816f6735be075c3386;hp=20983ce3d4d46b818c43e4f87ab84ec16e9cb701;hpb=0547bab13aa1008c751cbb7f72a3743ceed9a5c2;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/FAQ.pod b/lib/DBIx/Class/Manual/FAQ.pod index 20983ce..7d4505e 100644 --- a/lib/DBIx/Class/Manual/FAQ.pod +++ b/lib/DBIx/Class/Manual/FAQ.pod @@ -68,6 +68,24 @@ connection does not happen until you actually request data, so don't be alarmed if the error from incorrect connection details happens a lot later. +=item .. use DBIx::Class across multiple databases? + +If your database server allows you to run querys across multiple +databases at once, then so can DBIx::Class. All you need to do is make +sure you write the database name as part of the +L call. Eg: + + __PACKAGE__->table('mydb.mytablename'); + +And load all the Result classes for both / all databases using one +L call. + +=item .. use DBIx::Class across PostgreSQL/DB2/Oracle schemas? + +Add the name of the schema to the L +as part of the name, and make sure you give the one user you are going +to connect with rights to read/write all the schemas/tables as +necessary. =back @@ -77,7 +95,7 @@ lot later. =item .. tell DBIx::Class about relationships between my tables? -There are a vareity of relationship types that come pre-defined for +There are a variety of relationship types that come pre-defined for you to use. These are all listed in L. If you need a non-standard type, or more information, look in L. @@ -113,12 +131,19 @@ as you like. See L. Read the documentation on L. -=item .. stop DBIx::Class from attempting to cascade deletes on my has_many relationships? +=item .. stop DBIx::Class from attempting to cascade deletes on my has_many and might_have relationships? By default, DBIx::Class cascades deletes and updates across -C relationships. If your database already does this (and -that is probably better), turn it off by supplying C<< cascade_delete => 0 >> -in the relationship attributes. See L. +C and C relationships. You can disable this +behaviour on a per-relationship basis by supplying +C<< cascade_delete => 0 >> in the relationship attributes. + +The cascaded operations are performed after the requested delete or +update, so if your database has a constraint on the relationship, it +will have deleted/updated the related records or raised an exception +before DBIx::Class gets to perform the cascaded operation. + +See L. =item .. use a relationship? @@ -232,6 +257,25 @@ and not: my $interval = "now() - interval '12 hours'"; ->search({last_attempt => { '<' => \$interval } }) +=item .. search with an SQL function on the left hand side? + +To use an SQL function on the left hand side of a comparison: + + ->search({}, { where => \'YEAR(date_of_birth)=1979' }); + +=begin hidden + +(When the bind arg ordering bug is fixed, the previous example can be +replaced with the following.) + + ->search({}, { where => \'YEAR(date_of_birth)=?', bind => [ 1979 ] }); + +=end hidden + +Or, if you have quoting off: + + ->search({ 'YEAR(date_of_birth)' => 1979 }); + =item .. find more help on constructing searches? Behind the scenes, DBIx::Class uses L to help construct @@ -239,16 +283,90 @@ its SQL searches. So if you fail to find help in the L, try looking in the SQL::Abstract documentation. +=item .. make searches in Oracle (10gR2 and newer) case-insensitive? + +To make Oracle behave like most RDBMS use on_connect_do to issue +alter session statements on database connection establishment: + + ->on_connect_do("ALTER SESSION SET NLS_COMP = 'LINGUISTIC'"); + ->on_connect_do("ALTER SESSION SET NLS_SORT = '_CI'"); + e.g. + ->on_connect_do("ALTER SESSION SET NLS_SORT = 'BINARY_CI'"); + ->on_connect_do("ALTER SESSION SET NLS_SORT = 'GERMAN_CI'"); + + =back =head2 Fetching data =over 4 -=item .. fetch as much data as possible in as few select calls as possible? (prefetch) +=item .. fetch as much data as possible in as few select calls as possible? See the prefetch examples in the L. +=item .. fetch a whole column of data instead of a row? + +Call C on a L, this returns a +L, see it's documentation and the +L for details. + +=item .. fetch a formatted column? + +In your table schema class, create a "private" column accessor with: + + __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' }); + +Then, in the same class, implement a subroutine called "my_column" that +fetches the real value and does the formatting you want. + +See the Cookbook for more details. + +=item .. fetch a single (or topmost) row? + +Sometimes you many only want a single record back from a search. A quick +way to get that single row is to first run your search as usual: + + ->search->(undef, { order_by => "id DESC" }) + +Then call L and ask it only to return 1 row: + + ->slice(0) + +These two calls can be combined into a single statement: + + ->search->(undef, { order_by => "id DESC" })->slice(0) + +Why slice instead of L or L? +If supported by the database, slice will use LIMIT/OFFSET to hint to the database that we +really only need one row. This can result in a significant speed improvement. + +=item .. refresh a row from storage? + +Use L. + + $row->discard_changes + +Discarding changes and refreshing from storage are two sides fo the same coin. When you +want to discard your local changes, just re-fetch the row from storage. When you want +to get a new, fresh copy of the row, just re-fetch the row from storage. +L does just that by re-fetching the row from storage +using the row's primary key. + +=item .. fetch my data a "page" at a time? + +Pass the C and C attributes to your search, eg: + + ->search({}, { rows => 10, page => 1}); + +=item .. get a count of all rows even when paging? + +Call C on the paged resultset, it will return a L +object. Calling C on the pager will return the correct +total. + +C on the resultset will only return the total number in the page. + =back =head2 Inserting and updating data @@ -271,7 +389,7 @@ primary key field from the sequence. To help PK::Auto find your inserted key, you can tell it the name of the sequence in the C supplied with C. - ->add_columns({ id => { sequence => 'mysequence' } }); + ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } }); =item .. insert many rows of data efficiently? @@ -290,6 +408,48 @@ scalar reference: ->update({ somecolumn => \'othercolumn' }) +But note that when using a scalar reference the column in the database +will be updated but when you read the value from the object with e.g. + + ->somecolumn() + +you still get back the scalar reference to the string, B the new +value in the database. To get that you must refresh the row from storage +using C. Or chain your function calls like this: + + ->update->discard_changes + + to update the database and refresh the object in one step. + +=item .. store JSON/YAML in a column and have it deflate/inflate automatically? + +You can use L to accomplish YAML/JSON storage transparently. + +If you want to use JSON, then in your table schema class, do the following: + + use JSON; + + __PACKAGE__->add_columns(qw/ ... my_column ../) + __PACKAGE__->inflate_column('my_column', { + inflate => sub { jsonToObj(shift) }, + deflate => sub { objToJson(shift) }, + }); + +For YAML, in your table schema class, do the following: + + use YAML; + + __PACKAGE__->add_columns(qw/ ... my_column ../) + __PACKAGE__->inflate_column('my_column', { + inflate => sub { YAML::Load(shift) }, + deflate => sub { YAML::Dump(shift) }, + }); + +This technique is an easy way to store supplemental unstructured data in a table. Be +careful not to overuse this capability, however. If you find yourself depending more +and more on some data within the inflated column, then it may be time to factor that +data out. + =back =head2 Misc @@ -300,6 +460,41 @@ scalar reference: You can add your own data accessors to your classes. +One method is to use the built in mk_group_accessors (via L) + + package MyTable; + + use parent 'DBIx::Class'; + + __PACKAGE__->table('foo'); #etc + __PACKAGE__->mk_group_accessors('simple' => qw/non_column_data/); # must use simple group + +An another method is to use L with your L package. + + package MyTable; + + use Moose; # import Moose + use Moose::Util::TypeConstraint; # import Moose accessor type constraints + + extends 'DBIx::Class'; # Moose changes the way we define our parent (base) package + + has 'non_column_data' => ( is => 'rw', isa => 'Str' ); # define a simple attribute + + __PACKAGE__->table('foo'); # etc + +With either of these methods the resulting use of the accesssor would be + + my $row; + + # assume that some where in here $row will get assigned to a MyTable row + + $row->non_column_data('some string'); # would set the non_column_data accessor + + # some other stuff happens here + + $row->update(); # would not inline the non_column_data accessor into the update + + =item How do I use DBIx::Class objects in my TT templates? Like normal objects, mostly. However you need to watch out for TT @@ -323,6 +518,27 @@ is executed. You can create further resultset refinements by calling search again or relationship accessors. The SQL query is only run when you ask the resultset for an actual row object. +=item How do I deal with tables that lack a primary key? + +If your table lacks a primary key, DBIx::Class can't work out which row +it should operate on, for example to delete or update. However, a +UNIQUE constraint on one or more columns allows DBIx::Class to uniquely +identify the row, so you can tell L these +columns act as a primary key, even if they don't from the database's +point of view: + + $resultset->set_primary_key(@column); + +=item How do I make my program start faster? + +Look at the tips in L + +=item How do I reduce the overhead of database queries? + +You can reduce the overhead of object creation within L +using the tips in L +and L + =back =head2 Notes for CDBI users @@ -333,6 +549,6 @@ you ask the resultset for an actual row object. particular column or group of columns (a-la cdbi Stringfy column group, or stringify_self method) ? -See L +See L =back