view release on metacpan or  search on metacpan

lib/DBIx/Class/Manual/Cookbook.pod  view on Meta::CPAN

    }
  );

=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 L<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 L<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<search|DBIx::Class::ResultSet/search> and L<DBIx::Class::ResultSet/find>,
whereas L<create|DBIx::Class::ResultSet/create> and
L<DBIx::Class::Row/update> (but not L<DBIx::Class::ResultSet/update>) are
L<DBIx::Class::InflateColumn>-aware and will do the right thing when supplied
an inflated L<DateTime> object.

=head2 Using Unicode

When using unicode character data there are two alternatives -
either your database supports unicode characters (including setting
the utf8 flag on the returned string), or you need to encode/decode
data appropriately each time a string field is inserted into or
retrieved from the database. It is better to avoid
encoding/decoding data and to use your database's own unicode
capabilities if at all possible.

The L<DBIx::Class::UTF8Columns> component handles storing selected
unicode columns in a database that does not directly support
unicode. If used with a database that does correctly handle unicode
then strange and unexpected data corrupt B<will> occur.

The Catalyst Wiki Unicode page at
L<http://wiki.catalystframework.org/wiki/tutorialsandhowtos/using_unicode>
has additional information on the use of Unicode with Catalyst and
DBIx::Class.

The following databases do correctly handle unicode data:-

=head3 MySQL

MySQL supports unicode, and will correctly flag utf8 data from the
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
enables character semantics on that string. You will also need to
ensure that your database / table / column is configured to use
UTF8. See Chapter 10 of the mysql manual for details.

See L<DBD::mysql> for further details.

=head3 Oracle

Information about Oracle support for unicode can be found in
L<DBD::Oracle/UNICODE>.

=head3 PostgreSQL

PostgreSQL supports unicode if the character set is correctly set
at database creation time. Additionally the C<pg_enable_utf8>
should be set to ensure unicode data is correctly marked.

  my $schema = My::Schema->connection('dbi:Pg:dbname=test',
                                      $user, $pass,
                                      { pg_enable_utf8 => 1} );

Further information can be found in L<DBD::Pg>.

=head3 SQLite

SQLite version 3 and above natively use unicode internally. To
correctly mark unicode strings taken from the database, the
C<sqlite_unicode> flag should be set at connect time (in versions
of L<DBD::SQLite> prior to 1.27 this attribute was named
C<unicode>).

  my $schema = My::Schema->connection('dbi:SQLite:/tmp/test.db',
                                      '', '',
                                      { sqlite_unicode => 1} );

=head1 BOOTSTRAPPING/MIGRATING

=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<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;

  my $schema = MyDB->schema_instance;

  my $translator           =  SQL::Translator->new(
      debug                => $debug          ||  0,
      trace                => $trace          ||  0,
      no_comments          => $no_comments    ||  0,
      show_warnings        => $show_warnings  ||  0,
      add_drop_table       => $add_drop_table ||  0,
      validate             => $validate       ||  0,