[Dbix-class] Oracle AutoPK and Sequencies

[Johnny Gebreselassie]

Alexander Hartmaier wrote:
> Am Montag, den 15.06.2009, 15:49 +0200 schrieb Johnny Gebreselassie:
>   
>>     Normally you would create an on-insert trigger that fills the id
>>     column(s) in case none was passed to the query.
>>
>>     CREATE OR REPLACE TRIGGER yourschema.yourtriggername
>>         BEFORE INSERT ON yourschema.yourtablename
>>         FOR EACH ROW WHEN(new.id_column IS NULL) BEGIN
>>             SELECT yourtriggername.nextval INTO :new.id_column FROM dual;
>>     END;
>>
>>
>> I am not a fan of this solution, and this brings up a point/problem that
>> I am going to try to fix real soon.  The problem with this solution is
>> that for every single table you will need to create a trigger.  I think
>> a better solution is to use the sequence defined in the table class,
>> override the insert method, select the next value from dual, populate
>> the row, then call the super insert.  I'm actually going to try this
>> today, my only concern is performance I'll do some primitive benchmarking.
>>     
>
> This solution is for plain Oracle, without DBIC!
> There is no other way in Oracle to accomplish auto-id-generation.
>
> You don't need to override the insert method, thats what PK::Auto is
> for!
> As soon as you specify a sequence name and your storage is Oracle, DBIC
> fetches the next sequence value and uses that for the insert.
> If that doesn't work for you debug it.
>
>   
>> Another benefit of my solution is that it isolates the solution to DBIC,
>> which might be beneficial if you have ETL processes that hit these
>> tables as well as they will not be executing the trigger code on every
>> insert.  I'll be more than happy to share the results of my
>> experiment/process if the user list will tolerate it.
>>
>> Thanks!
>>
>>
>> Johnny Gebreselassie
>>     
> --
> BR Alex
>
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> 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.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>   

OK so this seems to work per the docs here:

http://search.cpan.org/~ribasushi/DBIx-Class-0.08102/lib/DBIx/Class/ResultSource.pm#add_columns

The values for sequence and auto_nextval need to be specified on the 
primary_key column as shown in this copied documentation: 

    "->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1
    } });"


The confusing part about that though is right above it, it says

    "You need to create a trigger in your database that updates your
    primary key field from the sequence. "

The trigger is exactly what I've avoided by specifying the values. 


Also if I look at the docs for PK::Auto there is a method called 
sequence, which does something else, I am not quite sure what:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08102/lib/DBIx/Class/PK/Auto.pm


Thanks and sorry if I created any confusion!


Johnny Gebreselassie