[Dbix-class] New DBIx::Class oracle user

Benjamin Hitz hitz at genome.stanford.edu
Thu Jun 21 17:36:26 GMT 2007


Brandon -

Well, thanks for getting this into CPAN.

Had some funny behavior with 'db_schema' parameter.
If I supplied the correct schema, then the Loader ran ... but  
couldn't find any tables.  It created the base Schema class  
correctly, however (well, all 3 lines of it).

If I supplied the incorrect db_schema, or no db_schema, then the  
Loader found all the tables, but couldn't execute SQL on them.

So, my work around was to use the (correct) db_schema, and hack the  
_tables_columns method to use $self->db_schema.

I think you are talking about this method:
sub _tables_list {
     my $self = shift;

     my $dbh = $self->schema->storage->dbh;

     my @tables;
     for my $table ( $dbh->tables(undef, $self->db_schema, '%',  
'TABLE,VIEW') ) { #catalog, schema, table, type
         my $quoter = $dbh->get_info(29);
         $table =~ s/$quoter//g;

         # remove "user." (schema) prefixes
         $table =~ s/\w+\.//;
         next if $table eq 'PLAN_TABLE';
         $table = lc $table;
         push @tables, $1
           if $table =~ /\A(\w+)\z/;
     }
     return @tables;

I did try to remove the regex sub - but since _tables_columns found  
no tables, it never got here ( I think, I did not really extensively  
trace).

Perhaps if both changes were made, it would create ResultSource  
packages with the schema name like Hartmaier's?? (obv. with  
schemaname not a variable.

__PACKAGE__->table(__PACKAGE__->schemaname . '.device');



Since it's short - here is my version of _tables_columns:

sub _table_columns {
     my ($self, $table) = @_;

     my $dbh = $self->schema->storage->dbh;
     $table = $self->db_schema.'.'.$table if $self->db_schema;
     # hack to make this work with schema name
     my $sth = $dbh->prepare($self->schema->storage->sql_maker->select 
($table, undef, \'1 = 0'));
     $sth->execute;
     return \@{$sth->{NAME_lc}};
}


> Another thing you may want to look at is putting an "on_connect_do"
> into the options of your connect_info, which has a command to set the
> current schema to whatever you like.  See the docs for this in
> DBIx::Class::Storage::DBI.

Yeah, but what would the SQL be?  In mysql, something like 'use  
$schema' might work, but there is no equivalent in oracle.
(At least according to our DBA).

Ben



On Jun 20, 2007, at 7:07 PM, Brandon Black wrote:

> On 6/20/07, Ben Hitz <hitz at genome.stanford.edu> wrote:
>> Hi -
>>
>> We have a well-developed Oracle database but are refactoring our per
>> API for it.  So we are checking out Class::DBI and DBIx::Class (at
>> Matt's prompting).
>>
>> I did get the DBIx::Class::Schema::Loader to work, BUT I had to put
>> in a little hack to use "$db_schema.$tablename" instead of  
>> $tablename in
>> DBIx::Schema::Loader::DBI::Oracle.
>>
>
> First off, DBIx::Class::Schema::Loader::DBI::Oracle was actually
> written by Tsunoda Kazuya, I just imported his work into the main
> distribution (at his request some time ago), so I'm not terribly
> familiar with it (or Oracle).
>
> In general, Schema::Loader supports an option "db_schema" that you use
> to specify a specific "schema" (in Oracle's sense of the word) to load
> table definitions from.  However, the DBI::Oracle driver seems to be
> explicitly removing this information from the table names, which is
> probably wrong (see the line in there that has a comment above it that
> says 'remove "user." (schema) prefixes").  This is quite likely the
> culprit.  What happens if you remove that line of code?
>
> Another thing you may want to look at is putting an "on_connect_do"
> into the options of your connect_info, which has a command to set the
> current schema to whatever you like.  See the docs for this in
> DBIx::Class::Storage::DBI.
>
> -- Brandon
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix- 
> class at lists.rawmode.org/

--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu






More information about the Dbix-class mailing list