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

[Benjamin Hitz]

Hartmaier -

Thanks for the suggestion.  We thought of this hack too - but thought  
it was a little ugly, I was hoping I just missed some config option.

Ben

On Jun 21, 2007, at 1:55 AM, Hartmaier Alexander wrote:

> Hi Ben!
>
> I'm using a base class for all table classes:
>
> package NAC::Model::DBIC::Table::Parent::All;
> use strict;
> use warnings;
> use base qw/DBIx::Class/;
>
> __PACKAGE__->load_components(qw/HTMLWidget WebForm Core/);
>
> sub schemaname {
>     return 'nacadm';
> }
>
> 1;
>
> An that’s how a table class looks like:
>
> package NAC::Model::DBIC::Table::Device;
> use strict;
> use warnings;
> use base 'NAC::Model::DBIC::Table::Parent::All';
>
> __PACKAGE__->table(__PACKAGE__->schemaname . '.device');
>
> # primary key column(s)
> __PACKAGE__->add_columns(
>     id_device   => {
>         sequence            => __PACKAGE__->schemaname .  
> '.seq_device',
>         is_auto_increment   => 1,
>     },
> );
>
> # other columns
> __PACKAGE__->add_columns(qw/
>     fk_monitor
>     scan
>     ping
>     fk_device_type
>     fk_division
>     fk_location
>     fk_collector
>     name
>     alias
>     snmplocation
>     hostid
>     fk_device_service_contract
>     service_contract_id
>     service_contract_enddate
>     ipaddr
>     snmpport
>     snmpread
>     serialnumber
>     memory
>     flashmem
>     chassismodel
> /);
>
> # set the primary key
> __PACKAGE__->set_primary_key(qw/
>     id_device
> /);
>
> I hope this helps starting with DBIC!
>
> -Alex
>
> PS: Don't even think about using Class::DBI these days!
>
>
>> -----Original Message-----
>> From: Ben Hitz [mailto:hitz at genome.stanford.edu]
>> Sent: Thursday, June 21, 2007 1:51 AM
>> To: dbix-class at lists.rawmode.org
>> Subject: [Dbix-class] New DBIx::Class oracle user
>>
>> 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.
>>
>> I ran my first test on the new class, and it works great - BUT only
>> if I log in as user $db_schema (in Oracle, users and schemas are
>> synonymous).   In our current API (and older, raw 'bag o DBI SQL
>> code") we basically hard code in $schema.$table.
>>
>> If you log in as a user you get :
>> DBIx::Class::ResultSet::search(): DBI Exception: DBD::Oracle::db
>> prepare_cached failed: ORA-00942: table or view does not exist (DBD
>> ERROR: error possibly near <*> indicator at char 247 in 'SELECT
>> me.reference_no, me.source, me.status, me.pdf_status, me.dbxref_id,
>> me.citation, me.year, me.pubmed, me.date_published, me.date_revised,
>> me.issue, me.page, me.volume, me.title, me.journal_no, me.book_no,
>> me.date_created, me.created_by FROM <*>reference me WHERE ( pubmed
>> = :p1 )') [for Statement "SELECT me.reference_no, me.source,
>> me.status, me.pdf_status, me.dbxref_id, me.citation, me.year,
>> me.pubmed, me.date_published, me.date_revised, me.issue, me.page,
>> me.volume, me.title, me.journal_no, me.book_no, me.date_created,
>> me.created_by FROM reference me WHERE ( pubmed = ? )"] at ./test.pl
>> line 10
>>
>> (no, my user name is not "me")
>>
>> It's very hard to google for this sort of info because the nouns are
>> all "schema" "user" "database" name.
>>
>> I think there is a simple solution to this, but I am a little stuck.
>> Some options:
>> 1) can I pass a schema name indepedent of my user name to DBI
>> connection string?
>> 2) is there some field I can set in the DBIx::Schema or in the
>> ResultSources themselves?
>> 3) Was there some other good way to run DBIx::Class::Schema::Loader
>> (without the hack) that I missed?  Another hack to add?
>>
>> Thanks for your time.
>>
>> Ben
>> --
>> Ben Hitz
>> Senior Scientific Programmer ** Saccharomyces Genome Database ** GO
>> Consortium
>> Stanford University ** hitz at genome.stanford.edu
>>
>>
>>
>>
>> _______________________________________________
>> 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/
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*" 
> *"*"*"*
> T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*" 
> *"*"*"*
> Notice: This e-mail contains information that is confidential and  
> may be privileged.
> If you are not the intended recipient, please notify the sender and  
> then delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*" 
> *"*"*"*
> _______________________________________________
> 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