X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FFAQ.pod;h=a3fe023972d9c7179f9d0c01bb948f8da3b4f3c5;hb=d044935bac610dcd724998254c6eefffd06607eb;hp=928808cb4a37dd61e54458d8f46ee60b45d4e0f9;hpb=6095cb703bcad95a735dcc2a557371fe9060d32e;p=dbsrgits%2FDBIx-Class-Historic.git diff --git a/lib/DBIx/Class/Manual/FAQ.pod b/lib/DBIx/Class/Manual/FAQ.pod index 928808c..a3fe023 100644 --- a/lib/DBIx/Class/Manual/FAQ.pod +++ b/lib/DBIx/Class/Manual/FAQ.pod @@ -77,7 +77,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 +113,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 +239,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,6 +265,18 @@ 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 @@ -255,6 +293,48 @@ 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. + =back =head2 Inserting and updating data @@ -277,7 +357,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? @@ -296,6 +376,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 @@ -329,6 +451,17 @@ 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); + =back =head2 Notes for CDBI users @@ -339,6 +472,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