[Dbix-class] Values populated by the backend - how to pull?
James FitzGibbon
jfitzgibbon at primustel.ca
Thu Dec 15 17:56:36 CET 2005
Ah, that'll learn me for only reading the source of the subclass.
Thanks
-----Original Message-----
From: dbix-class-bounces at lists.rawmode.org
[mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Matt S Trout
Sent: Thursday, December 15, 2005 11:51 AM
To: dbix-class at lists.rawmode.org
Subject: Re: [Dbix-class] Values populated by the backend - how to pull?
On Thu, Dec 15, 2005 at 11:29:25AM -0500, James FitzGibbon wrote:
> I looked at that: the code in PK::Auto::Oracle is designed to identify
> (based upon a regex scan of the trigger body attached to INSERT on the
> table) the name of the sequence that DBIC should pull from in order to
> populate the primary key before performing an INSERT.
No it isn't :)
The PK::Auto components let the database supply the id and grab it out
*after* the INSERT has run.
PK::Auto::Oracle pulling nextval from the sequence was a bug - the patch
of his that Alexander is referring to fixed this to be currval, which so
far as I can see should give you the behaviour you want.
> But as my message stated, our policy does not allow for the client layer
to
> provide the ID. The trigger body looks like this:
>
> CREATE OR REPLACE TRIGGER
> foo_seq_trig
> BEFORE INSERT ON
> foo
> FOR EACH ROW
> BEGIN
> IF( :new.id IS NOT NULL ) THEN
> raise_application_error(
> -20001,
> 'foo.id is generated for you'
> );
> END IF;
> SELECT main_seq.NEXTVAL INTO :new.id FROM DUAL;
> END;
> /
>
> Any thoughts given that clarification?
>
> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org
> [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Hartmaier
> Alexander
> Sent: Thursday, December 15, 2005 4:48 AM
> To: dbix-class at lists.rawmode.org
> Subject: RE: [Dbix-class] Values populated by the backend - how to pull?
>
> Hi and welcome to DBIC ;-)
>
> You need to load PK::Auto::Oracle in your table classes:
>
> use base qw/DBIx::Class/;
> __PACKAGE__->load_components(qw/PK::Auto::Oracle Core/);
>
> I do this in a Parent::All class i use base for all my table classes to
make
> shared things easier to change.
>
> Also you have to use trunk from svn till the next DBIC version comes out
> because I've fixed a PK::Auto::Oracle bug yesterday.
>
> -Alex
>
>
> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org
> [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of James
FitzGibbon
> Sent: Wednesday, December 14, 2005 9:42 PM
> To: dbix-class at lists.rawmode.org
> Subject: [Dbix-class] Values populated by the backend - how to pull?
>
> I'm relatively new to DBIC, so hopefully there is a simple answer for
this.
>
> Given the following two classes:
>
> package Foo;
> __PACKAGE__->primary_key( 'id' );
> __PACKAGE__->has_many( bars => 'Bar' => 'foo_id' );
>
> package Bar;
> __PACKAGE__->belongs_to( foo => 'Foo' => { 'foreign.id' => 'self.foo_id' }
>
> And the following (truncated) table DDL:
>
> CREATE TABLE foo (
> id NUMERIC(10) NOT NULL PRIMARY KEY,
> );
>
> CREATE TABLE bar (
> id NUMERIC(10) NOT NULL PRIMARY KEY,
> foo_id NUMERIC(10) NOT NULL,
> bar NUMERIC(10) NOT NULL,
> CONSTRAINT
> bar_foo_id_fk
> FOREIGN KEY(foo_id)
> REFERENCES
> foo(id)
> );
>
> The id column on the tables is populated from a sequence *by a trigger*,
not
> by DBIC.
>
> I can create a Foo object without providing any attributes, because of the
> trigger and default values. If I then try to do this:
>
> $foo = Foo->create;
> $foo->add_to_bars( { bar => 1 } );
>
> It fails with an error like this:
>
> DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
> ("BAR"."BAR_AUDIT"."FOO_ID")
>
> This particular error is from my audit trigger, but the underlying cause
is
> that the Foo object I created does not have it's id field populated. When
> Bar->create_related is called, it realizes that the foo_id attribute needs
> to have the value of Foo->id. Because the value of id is only in the
> database and not in the object, it's trying to populate a Bar object whose
> foo_id attribute has an undef value.
>
> My first guess is that I need to invalidate the in-memory version of the
Foo
> object so that when Foo->get_column('id') is called, the value from the
> sequence is pulled from the database. But there doesn't seem to be any
> query code in DBIC::Row. It's all in DBIC::Table.
>
> Except ... I can't even use search() to discard the $foo object I have and
> replace it with one borne of the database, because the in-memory $foo
object
> has no identifying attributes that I can use as criteria to search() or
> search_like().
>
> Am I being dumb here? Local policy requires that the sequence values are
> assigned by the database (as in "if you try to provide your own id value,
> the trigger will throw an exception"), so I can't have DBIC grab the next
> sequence value (which I assume would solve this problem).
>
> I notice that that docs for DBIC::Row->get_column() say "Currently, does
not
> do any queries;" - is this just a shortcoming in the early DBIC releases
> that at some point will be fixed?
>
> Thanks
>
> --
> j.
>
> James FitzGibbon
> Systems Developer, Primus Telecommunications Canada Inc.
> 416.644.6111
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.371 / Virus Database: 267.13.13/199 - Release Date:
12/13/2005
>
>
>
>
> --
>
----------------------------------------------------------------------------
> This electronic message contains information from Primus
Telecommunications
> Canada Inc. ("PRIMUS") , which may be legally privileged and confidential.
> The information is intended to be for the use of the individual(s) or
entity
> named above. If you are not the intended recipient, be aware that any
> disclosure, copying, distribution or use of the contents of this
information
> is prohibited. If you have received this electronic message in error,
please
> notify us by telephone or e-mail (to the number or address above)
> immediately. Any views, opinions or advice expressed in this electronic
> message are not necessarily the views, opinions or advice of PRIMUS.
> It is the responsibility of the recipient to ensure that
> any attachments are virus free and PRIMUS bears no responsibility
> for any loss or damage arising in any way from the use
> thereof.The term "PRIMUS" includes its affiliates.
>
----------------------------------------------------------------------------
> Pour la version en français de ce message, veuillez voir
> http://www.primustel.ca/fr/legal/cs.htm
>
----------------------------------------------------------------------------
>
>
> _______________________________________________
> 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/
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.371 / Virus Database: 267.13.13/199 - Release Date:
12/13/2005
>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.371 / Virus Database: 267.13.13/199 - Release Date:
12/13/2005
>
>
>
>
> --
>
----------------------------------------------------------------------------
> This electronic message contains information from Primus
Telecommunications
> Canada Inc. ("PRIMUS") , which may be legally privileged and confidential.
> The information is intended to be for the use of the individual(s) or
entity
> named above. If you are not the intended recipient, be aware that any
> disclosure, copying, distribution or use of the contents of this
information
> is prohibited. If you have received this electronic message in error,
please
> notify us by telephone or e-mail (to the number or address above)
> immediately. Any views, opinions or advice expressed in this electronic
> message are not necessarily the views, opinions or advice of PRIMUS.
> It is the responsibility of the recipient to ensure that
> any attachments are virus free and PRIMUS bears no responsibility
> for any loss or damage arising in any way from the use
> thereof.The term "PRIMUS" includes its affiliates.
>
----------------------------------------------------------------------------
> Pour la version en français de ce message, veuillez voir
> http://www.primustel.ca/fr/legal/cs.htm
>
----------------------------------------------------------------------------
>
>
> _______________________________________________
> 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/
--
Matt S Trout Offering custom development, consultancy and support
Technical Director contracts for Catalyst, DBIx::Class and BAST.
Contact
Shadowcat Systems Ltd. mst (at) shadowcatsystems.co.uk for more information
+ Help us build a better perl ORM:
http://dbix-class.shadowcatsystems.co.uk/ +
_______________________________________________
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/
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/199 - Release Date: 12/13/2005
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/199 - Release Date: 12/13/2005
--
----------------------------------------------------------------------------
This electronic message contains information from Primus Telecommunications
Canada Inc. ("PRIMUS") , which may be legally privileged and confidential.
The information is intended to be for the use of the individual(s) or entity
named above. If you are not the intended recipient, be aware that any
disclosure, copying, distribution or use of the contents of this information
is prohibited. If you have received this electronic message in error, please
notify us by telephone or e-mail (to the number or address above)
immediately. Any views, opinions or advice expressed in this electronic
message are not necessarily the views, opinions or advice of PRIMUS.
It is the responsibility of the recipient to ensure that
any attachments are virus free and PRIMUS bears no responsibility
for any loss or damage arising in any way from the use
thereof.The term "PRIMUS" includes its affiliates.
----------------------------------------------------------------------------
Pour la version en français de ce message, veuillez voir
http://www.primustel.ca/fr/legal/cs.htm
----------------------------------------------------------------------------
More information about the Dbix-class
mailing list