DBIx::Class::Storage::DBI::Oracle::Generic(3)

NAME
DBIx::Class::Storage::DBI::Oracle::Generic - Oracle Support for DBIx::Class

SYNOPSIS
  # In your result (table) classes
  use base 'DBIx::Class::Core';
  __PACKAGE__->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
  __PACKAGE__->set_primary_key('id');

  # Somewhere in your Code
  # add some data to a table with a hierarchical relationship
  $schema->resultset('Person')->create ({
        firstname => 'foo',
        lastname => 'bar',
        children => [
            {
                firstname => 'child1',
                lastname => 'bar',
                children => [
                    {
                        firstname => 'grandchild',
                        lastname => 'bar',
                    }
                ],
            },
            {
                firstname => 'child2',
                lastname => 'bar',
            },
        ],
    });

  # select from the hierarchical relationship
  my $rs = $schema->resultset('Person')->search({},
    {
      'start_with' => { 'firstname' => 'foo', 'lastname' => 'bar' },
      'connect_by' => { 'parentid' => { '-prior' => { -ident => 'personid' } },
      'order_siblings_by' => { -asc => 'name' },
    };
  );

  # this will select the whole tree starting from person "foo bar", creating
  # following query:
  # SELECT
  #     me.persionid me.firstname, me.lastname, me.parentid
  # FROM
  #     person me
  # START WITH
  #     firstname = 'foo' and lastname = 'bar'
  # CONNECT BY
  #     parentid = prior personid
  # ORDER SIBLINGS BY
  #     firstname ASC

DESCRIPTION
This class implements base Oracle support. The subclass DBIx::Class::Storage::DBI::Oracle::WhereJoins is for "(+)" joins in Oracle versions before 9.

METHODS

  get_autoinc_seq
Returns the sequence name for an autoincrement column

  datetime_parser_type
This sets the proper DateTime::Format module for use with DBIx::Class::InflateColumn::DateTime.

  connect_call_datetime_setup
Used as:

    on_connect_call => 'datetime_setup'

In connect_info to set the session nls date, and timestamp values for use with DBIx::Class::InflateColumn::DateTime and the necessary environment variables for DateTime::Format::Oracle, which is used by it.

Maximum allowable precision is used, unless the environment variables have already been set.

These are the defaults used:

  $ENV{NLS_DATE_FORMAT}         ||= 'YYYY-MM-DD HH24:MI:SS';
  $ENV{NLS_TIMESTAMP_FORMAT}    ||= 'YYYY-MM-DD HH24:MI:SS.FF';
  $ENV{NLS_TIMESTAMP_TZ_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS.FF TZHTZM';

To get more than second precision with DBIx::Class::InflateColumn::DateTime for your timestamps, use something like this:

  use Time::HiRes 'time';
  my $ts = DateTime->from_epoch(epoch => time);

  source_bind_attributes
Handle LOB types in Oracle. Under a certain size (4k?), you can get away with the driver assuming your input is the deprecated LONG type if you encode it as a hex string. That ain't gonna fly at larger values, where you'll discover you have to do what this does.

This method had to be overridden because we need to set ora_field to the actual column, and that isn't passed to the call (provided by Storage) to bind_attribute_by_data_type.

According to DBD::Oracle, the ora_field isn't always necessary, but adding it doesn't hurt, and will save your bacon if you're modifying a table with more than one LOB column.

  relname_to_table_alias
DBIx::Class uses DBIx::Class::Relationship names as table aliases in queries.

Unfortunately, Oracle doesn't support identifiers over 30 chars in length, so the DBIx::Class::Relationship name is shortened and appended with half of an MD5 hash.

See "relname_to_table_alias" in DBIx::Class::Storage.

  with_deferred_fk_checks
Runs a coderef between:

  alter session set constraints = deferred
  ...
  alter session set constraints = immediate

to defer foreign key checks.

Constraints must be declared "DEFERRABLE" for this to work.

ATTRIBUTES
Following additional attributes can be used in resultsets.

  connect_by or connect_by_nocycle

Value: \%connect_by

A hashref of conditions used to specify the relationship between parent rows and child rows of the hierarchy.

  connect_by => { parentid => 'prior personid' }

  # adds a connect by statement to the query:
  # SELECT
  #     me.persionid me.firstname, me.lastname, me.parentid
  # FROM
  #     person me
  # CONNECT BY
  #     parentid = prior persionid

  connect_by_nocycle => { parentid => 'prior personid' }

  # adds a connect by statement to the query:
  # SELECT
  #     me.persionid me.firstname, me.lastname, me.parentid
  # FROM
  #     person me
  # CONNECT BY NOCYCLE
  #     parentid = prior persionid

  start_with

Value: \%condition

A hashref of conditions which specify the root row(s) of the hierarchy.

It uses the same syntax as "search" in DBIx::Class::ResultSet

  start_with => { firstname => 'Foo', lastname => 'Bar' }

  # SELECT
  #     me.persionid me.firstname, me.lastname, me.parentid
  # FROM
  #     person me
  # START WITH
  #     firstname = 'foo' and lastname = 'bar'
  # CONNECT BY
  #     parentid = prior persionid

  order_siblings_by

Value: ($order_siblings_by | \@order_siblings_by)

Which column(s) to order the siblings by.

It uses the same syntax as "order_by" in DBIx::Class::ResultSet

  'order_siblings_by' => 'firstname ASC'

  # SELECT
  #     me.persionid me.firstname, me.lastname, me.parentid
  # FROM
  #     person me
  # CONNECT BY
  #     parentid = prior persionid
  # ORDER SIBLINGS BY
  #     firstname ASC

AUTHOR
See "CONTRIBUTORS" in DBIx::Class.

LICENSE
You may distribute this code under the same terms as Perl itself.