X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=7fe2058a8e6873b48c31be0aae7d9477e3186446;hb=b1d821deddb9183fb96810d71a046ee8abe71d13;hp=e5f255931f1703534fa0260f75b8002d4469fb19;hpb=ed7ab0f4ce1a9118ea6285ee562ef003085a6b64;p=dbsrgits%2FDBIx-Class.git

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index e5f2559..7fe2058 100644
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -151,7 +151,7 @@ Note that you cannot have bind parameters unless is_virtual is set to true.
 =item * NOTE
 
 If you're using the old deprecated C<< $rsrc_instance->name(\'( SELECT ...') >>
-method for custom SQL execution, you are highly encouraged to update your code 
+method for custom SQL execution, you are highly encouraged to update your code
 to use a virtual view as above. If you do not want to change your code, and just
 want to suppress the deprecation warning when you call
 L<DBIx::Class::Schema/deploy>, add this line to your source definition, so that
@@ -202,7 +202,7 @@ to access the returned value:
   # FROM artist
 
 Note that the C<as> attribute B<has absolutely nothing to do> with the SQL
-syntax C< SELECT foo AS bar > (see the documentation in 
+syntax C< SELECT foo AS bar > (see the documentation in
 L<DBIx::Class::ResultSet/ATTRIBUTES>). You can control the C<AS> part of the
 generated SQL via the C<-as> field attribute as follows:
 
@@ -218,10 +218,10 @@ generated SQL via the C<-as> field attribute as follows:
   );
 
   # Equivalent SQL
-  # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds 
-  #   FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid 
-  # GROUP BY me.artistid, me.name, me.rank, me.charfield 
-  # ORDER BY amount_of_cds DESC 
+  # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
+  #   FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
+  # GROUP BY me.artistid, me.name, me.rank, me.charfield
+  # ORDER BY amount_of_cds DESC
 
 
 If your alias exists as a column in your base class (i.e. it was added with
@@ -349,7 +349,7 @@ from, select, and +select attributes.
   my $rs = $cdrs->search({
     year => {
       '=' => $cdrs->search(
-        { artist_id => { '=' => \'me.artist_id' } },
+        { artist_id => { '=' => { -ident => 'me.artist_id' } } },
         { alias => 'inner' }
       )->get_column('year')->max_rs->as_query,
     },
@@ -413,40 +413,29 @@ Using SQL functions on the left hand side of a comparison is generally not a
 good idea since it requires a scan of the entire table. (Unless your RDBMS
 supports indexes on expressions - including return values of functions - and
 you create an index on the return value of the function in question.) However,
-it can be accomplished with C<DBIx::Class> when necessary.
-
-Your approach for doing so will depend on whether you have turned
-quoting on via the C<quote_char> and C<name_sep> attributes. If you
-explicitly defined C<quote_char> and C<name_sep> in your
-C<connect_info> (see L<DBIx::Class::Storage::DBI/"connect_info">) then
-you are using quoting, otherwise not.
-
-If you do not have quoting on, simply include the function in your search
-specification as you would any column:
-
-  $rs->search({ 'YEAR(date_of_birth)' => 1979 });
-
-With quoting on, or for a more portable solution, use literal SQL values with
-placeholders:
+it can be accomplished with C<DBIx::Class> when necessary by resorting to
+literal SQL:
 
   $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]);
 
   # Equivalent SQL:
   # SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
 
-  $rs->search({
+  $rs->search({ -and => [
     name => 'Bob',
-    -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
-  });
+    \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
+  ]});
 
   # Equivalent SQL:
   # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
 
 Note: the C<plain_value> string in the C<< [ plain_value => 1979 ] >> part
 should be either the same as the name of the column (do this if the type of the
-return value of the function is the same as the type of the column) or
-otherwise it's essentially a dummy string currently (use C<plain_value> as a
-habit). It is used by L<DBIx::Class> to handle special column types.
+return value of the function is the same as the type of the column) or in the
+case of a function it's currently treated as a dummy string (it is a good idea
+to use C<plain_value> or something similar to convey intent). The value is
+currently only significant when handling special column types (BLOBs, arrays,
+etc.), but this may change in the future.
 
 See also L<SQL::Abstract/Literal SQL with placeholders and bind values
 (subqueries)>.
@@ -1280,7 +1269,7 @@ row.
 
   my $schema = MySchema->connect("dbi:Pg:dbname=my_db");
 
-  # Start a transaction. Every database change from here on will only be 
+  # Start a transaction. Every database change from here on will only be
   # committed into the database if the try block succeeds.
   use Try::Tiny;
   my $exception;
@@ -1326,7 +1315,7 @@ row.
           # next $thing
           print "Cannot create thing: $_";
         }
-        # There was no error, so save all changes since the last 
+        # There was no error, so save all changes since the last
         # savepoint.
 
         # SQL: RELEASE SAVEPOINT savepoint_0;
@@ -1669,7 +1658,8 @@ brackets, or a C<"> or C<'>:
 
 Check the documentation of your database for the correct quote
 characters to use. C<name_sep> needs to be set to allow the SQL
-generator to put the quotes the correct place.
+generator to put the quotes the correct place, and defaults to
+C<.> if not supplied.
 
 In most cases you should set these as part of the arguments passed to
 L<DBIx::Class::Schema/connect>:
@@ -1695,24 +1685,6 @@ this, you can also overload the C<connection> method for your schema class:
      return $rv;
  }
 
-=head2 Setting limit dialect for SQL::Abstract::Limit
-
-In some cases, SQL::Abstract::Limit cannot determine the dialect of
-the remote SQL server by looking at the database handle. This is a
-common problem when using the DBD::JDBC, since the DBD-driver only
-know that in has a Java-driver available, not which JDBC driver the
-Java component has loaded.  This specifically sets the limit_dialect
-to Microsoft SQL-server (See more names in SQL::Abstract::Limit
--documentation.
-
-  __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
-
-The JDBC bridge is one way of getting access to a MSSQL server from a platform
-that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
-
-The limit dialect can also be set at connect time by specifying a
-C<limit_dialect> key in the final hash as shown above.
-
 =head2 Working with PostgreSQL array types
 
 You can also assign values to PostgreSQL array columns by passing array
@@ -1748,8 +1720,41 @@ See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with
 placeholders and bind values (subqueries)> for more explanation. Note that
 L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass
 the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in
-arrayrefs together with the column name, like this: C<< [column_name => value]
->>.
+arrayrefs together with the column name, like this:
+C<< [column_name => value] >>.
+
+=head2 Formatting DateTime objects in queries
+
+To ensure C<WHERE> conditions containing L<DateTime> arguments are properly
+formatted to be understood by your RDBMS, you must use the C<DateTime>
+formatter returned by L<DBIx::Class::Storage::DBI/datetime_parser> to format
+any L<DateTime> objects you pass to L<search|DBIx::Class::ResultSet/search>
+conditions. Any L<Storage|DBIx::Class::Storage> object attached to your
+L<Schema|DBIx::Class::Schema> provides a correct C<DateTime> formatter, so
+all you have to do is:
+
+  my $dtf = $schema->storage->datetime_parser;
+  my $rs = $schema->resultset('users')->search(
+    {
+      signup_date => {
+        -between => [
+          $dtf->format_datetime($dt_start),
+          $dtf->format_datetime($dt_end),
+        ],
+      }
+    },
+  );
+
+Without doing this the query will contain the simple stringification of the
+C<DateTime> object, which almost never matches the RDBMS expectations.
+
+This kludge is necessary only for conditions passed to
+L<DBIx::Class::ResultSet/search>, whereas
+L<create|DBIx::Class::ResultSet/create>,
+L<find|DBIx::Class::ResultSet/find>,
+L<DBIx::Class::Row/update> (but not L<DBIx::Class::ResultSet/update>) are all
+L<DBIx::Class::InflateColumn>-aware and will do the right thing when supplied
+an inflated C<DateTime> object.
 
 =head2 Using Unicode
 
@@ -1781,7 +1786,7 @@ database if the C<mysql_enable_utf8> is set in the connect options.
   my $schema = My::Schema->connection('dbi:mysql:dbname=test',
                                       $user, $pass,
                                       { mysql_enable_utf8 => 1} );
-  
+
 
 When set, a data retrieved from a textual column type (char,
 varchar, etc) will have the UTF-8 flag turned on if necessary. This
@@ -1825,9 +1830,9 @@ C<unicode>).
 =head2 Easy migration from class-based to schema-based setup
 
 You want to start using the schema-based approach to L<DBIx::Class>
-(see L<SchemaIntro.pod>), but have an established class-based setup with lots
-of existing classes that you don't want to move by hand. Try this nifty script
-instead:
+(see L<DBIx::Class::Manual::Intro/Setting it up manually>), but have an
+established class-based setup with lots of existing classes that you don't
+want to move by hand. Try this nifty script instead:
 
   use MyDB;
   use SQL::Translator;
@@ -1895,19 +1900,27 @@ just looking for this.
 For example, say that you have three columns, C<id>, C<number>, and
 C<squared>.  You would like to make changes to C<number> and have
 C<squared> be automagically set to the value of C<number> squared.
-You can accomplish this by overriding C<store_column>:
+You can accomplish this by wrapping the C<number> accessor with
+L<Class::Method::Modifiers>:
+
+  around number => sub {
+    my ($orig, $self) = (shift, shift);
 
-  sub store_column {
-    my ( $self, $name, $value ) = @_;
-    if ($name eq 'number') {
-      $self->squared($value * $value);
+    if (@_) {
+      my $value = $_[0];
+      $self->squared( $value * $value );
     }
-    $self->next::method($name, $value);
+
+    $self->next::method(@_);
   }
 
 Note that the hard work is done by the call to C<next::method>, which
 redispatches your call to store_column in the superclass(es).
 
+Generally, if this is a calculation your database can easily do, try
+and avoid storing the calculated value, it is safer to calculate when
+needed, than rely on the data being in sync.
+
 =head2 Automatically creating related objects
 
 You might have a class C<Artist> which has many C<CD>s.  Further, you
@@ -2046,7 +2059,7 @@ mechanism:
   sub query_start {
     my $self = shift();
     my $sql = shift();
-    my $params = @_;
+    my @params = @_;
 
     $self->print("Executing $sql: ".join(', ', @params)."\n");
     $start = time();
@@ -2116,7 +2129,7 @@ time, but not so large that the table is locked for an unacceptably long time.
 
 If using L<create|DBIx::Class::ResultSet/create> instead, use a transaction and
 commit every C<X> rows; where C<X> gives you the best performance without
-locking the table for too long. 
+locking the table for too long.
 
 =item *