DBIx::C...Generic(3)

💜 DBIx::C...Generic(3) — aka DBIx::Class::Storage::DBI::Oracle::Generic 💜

okay so bestie, gather round, because this page is literally the Oracle Support for DBIx::Class ✨ (that's the NAME, and yes it's the whole vibe of this file, no cap).

---

🌸 SYNOPSIS 🌸

here's the group chat of code examples, verbatim, do not touch:

  # 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

(no i did NOT fix the persionid typos, that's literally what the source says, iykyk 💅)

---

🦋 DESCRIPTION 🦋

This class implements base Oracle support — like, the foundational Oracle stuff, the main character energy of Oracle storage 🫶

The subclass DBIx::Class::Storage::DBI::Oracle::WhereJoins is the one for (+) joins in Oracle versions before 9.0. that's its whole personality, and we respect it.

---

🎀 METHODS 🎀

get_autoinc_seq

Returns the sequence name for an autoincrement column. slay, it tells you the sequence ✨

datetime_parser_type

This sets the proper DateTime::Format module for use with DBIx::Class::InflateColumn::DateTime. matching them up like the besties they are 💖

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. (yes it's the module that the module uses, we don't make the rules 🧚)

Maximum allowable precision is used, unless the environment variables have already been set — like, if you already set them, it respects you, lowkey iconic.

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

sub-second precision, she's thriving 💅

relname_to_table_alias

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

Unfortunately — and this is the sad part 😭 — 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. half! of an MD5 hash! truncated name plus hash bits, that's the whole trick 🎀

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

with_deferred_fk_checks

Runs a coderef between:

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

to defer foreign key checks. fancy 😌

⚠️ Constraints must be declared "DEFERRABLE" for this to work. fr fr, if they're not deferrable, this whole situation does not go.

---

🌈 ATTRIBUTES 🌈

Following additional attributes can be used in resultsets. yes, additional, there's more, keep going bestie 👑

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

NOCYCLE is literally the one that says "bestie please, do not loop forever" 💀

start_with

Value: \%condition

A hashref of conditions which specify the root row(s) of the hierarchy. the starting point, the pilot episode, the origin story 🌱

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. one column or a whole arrayref, your call ✨

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

---

🫶 FURTHER QUESTIONS? 🫶

Check the list of additional DBIC resources. that's it, that's the whole answer, go explore 🦋

---

👑 COPYRIGHT AND LICENSE 👑

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library. 💖 open source, we stan.

---

and that's the page, bestie 🎀 sign-off time: this was perl v5.42.0, dated 2025-01-22, and it's DBIx::C...Generic(3) — go forth and build your Oracle hierarchies, you're gonna eat 🌟