[Dbix-class] Values populated by the backend - how to pull?

[Matt S Trout]

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/ +