view release on metacpan or  search on metacpan

lib/DBIO/Manual/Cookbook.pod  view on Meta::CPAN

    {
      day => '2008-11-24'
    }
  );

In conditions (e.g. C<\%cond> in the L<DBIO::ResultSet/search> family of
methods) you cannot directly use array references (since this is interpreted as
a list of values to be C<OR>ed), but you can use the following syntax to force
passing them as bind values:

  $resultset->search(
    {
      numbers => { -value => [1, 2, 3] }
    }
  );

=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<DBIO::Storage::DBI/datetime_parser> to format
any L<DateTime> objects you pass to L<search|DBIO::ResultSet/search>
conditions. Any L<Storage|DBIO::Storage> object attached to your
L<Schema|DBIO::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|DBIO::ResultSet/search> and L<DBIO::ResultSet/find>,
whereas L<create|DBIO::ResultSet/create> and
L<DBIO::Row/update> (but not L<DBIO::ResultSet/update>) are
L<DBIO::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.

Most modern databases handle Unicode natively. Ensure your database
connection is configured for UTF-8 (e.g. C<pg_enable_utf8> for
PostgreSQL, C<mysql_enable_utf8mb4> for MySQL).

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<DBIO>
(see L<DBIO::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.

The old SQL::Translator-based bootstrap (the C<SQL::Translator::Parser::DBIO>
parser and C<SQL::Translator::Producer::DBIO::File> producer) is no longer
shipped. Instead, point L<DBIO::Generate> at the database your class-based setup
already connects to and let it emit a fresh schema-based class tree:

  dbiogen -o dump_directory=./lib \
          My::Schema dbi:Pg:dbname=foo username password

See L<DBIO::Manual::Intro/Using DBIO::Generate> and the
L</Creating Schemas From An Existing Database> recipe above for the full set of
options.

=head1 OVERLOADING METHODS