[Dbix-class] Oracle - Cross schema relationships

[Cliff Green]

On 10/11/2010 12:02 PM, Benjamin Martin wrote:
> Hello All,
>
> I am using ::Schema::Loader to build my Result files from an Oracle DB.
>
> I am new to Oracle, but from what I got told it would seem a 'user' is a
> 'schema' in Oracle.
>
> I have some cross-schema relationships in the DB, but schema loader is
> not building these into my Result files... I guess because I connect as
> a certain user/schema.
>
> I was wondering if anyone knew if it was possible (or not) to get
> ::Schema::Loader to create my cross-schema relationships? ... or do have
> to add these all manually.

I don't think you can do this with DBIx::Class::Schema::Loader (keep in 
mind, I don't use it).  From the documentation for 
DBIx::Class::Schema::Loader::Base:

  db_schema:
  Set the name of the schema to load (schema in the sense that your
  database vendor means it). Does not currently support loading more
  than one schema name.

Note the last sentence.


On the other hand, I do use DBIx::Class::Schema and manually created 
class files with Oracle.  In my schema file, I use:

<schema example begins>
package DB::[my instance name];
use base qw/DBIx::Class::Schema/;

__PACKAGE__->load_classes();

1;
<schema example ends>

[Actually, I usually explicitly load the classes I want use (qw/list of 
class files/), but this works, too].

Then, in each class file:

<class example begins>
package DB::[my instance name]::[tablename];
use strict;
use warnings;
use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("[schema.][tablename]");
__PACKAGE__->add_columns(
.....
};
[custom stuff here, like relationships or accessors...]
1;
<class example ends>

So, in the __PACKAGE__->table(); line, an alternate schema (or user, per 
Oracle) is prepended to the tablename with a dot, much as if you were 
using SQLPlus.

Hope that helps, and I haven't completely mangled the terminology...

c
-- 
Cliff Green
Business Systems & Technologies - UMDNJ