view release on metacpan or  search on metacpan

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

methods:

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

  $result->update(
    {
      numbers => [1, 2, 3]
    },
    {
      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. 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,
      parser_args          => {
         'DBIx::Schema'    => $schema,
                              },
      producer_args   => {
          'prefix'         => 'My::Schema',
                         },
  );

  $translator->parser('SQL::Translator::Parser::DBIO');
  $translator->producer('SQL::Translator::Producer::DBIO::File');

  my $output = $translator->translate(@args) or die
          "Error: " . $translator->error;

  print $output;

You could use L<Module::Find> to search for all subclasses in the MyDB::*
namespace, which is currently left as an exercise for the reader.

=head1 OVERLOADING METHODS

L<DBIO> uses the L<Class::C3> package, which provides for redispatch of
method calls, useful for things like default values and triggers. You have to
use calls to C<next::method> to overload methods. More information on using
L<Class::C3> with L<DBIO> can be found in
L<DBIO::Manual::Component>.

=head2 Setting default values for a row

It's as simple as overriding the C<new> method.  Note the use of
C<next::method>.

  sub new {
    my ( $class, $attrs ) = @_;

    $attrs->{foo} = 'bar' unless defined $attrs->{foo};

    my $new = $class->next::method($attrs);