Oracle AutoPK and Sequencies

[Doug Scoular]

Hi All,
    I've managed to get thoroughly confused with how to insert
    new records in an ORACLE database using DBIC. I wonder
    if anyone can help.

    Say I have the following model code:

__PACKAGE__->load_components(qw/Core/);
__PACKAGE__->table('standards_baseline_prep');
__PACKAGE__->add_columns(
                         id => {
                                data_type => 'integer',
                                is_auto_increment => 1,
                                sequence => 'hw_stds_baseline_prep_id_seq',
                               },
                         audit_item => {
                                        data_type => 'varchar',
                                        size => 32,
                                       },
.... etc ...

    This allows me to create new records without having
    to worry about the "id". I assume this is being obtained from
    the next val in the sequence (hw_stds_baseline_prep_id_seq). Anyway,
    this lets me call create without specifying the "id":

    $baseline = $schema->resultset('StandardsBaselinePrep')->create({
                                                         type =>
'hardware',
                                                         audit_item =>
'model',
                                                         ...

    However, I've recently discovered that people sometimes
    manually add records to the database without obtaining
    IDs from the sequence. If this happens what is the best
    way to obtain the next ID value ? My suspicion is that
    my use of sequences becomes invalid.

    Can I just ignore the sequence in this case...
    I thought I could just select all IDs in order and add one
    to the highest one and use that... is this sensible ?
    Can DBIC already do this for me ?

    Any thoughts much appreciated...

    Cheers,

    Doug




_______________________________________________
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@...

[Alexander Hartmaier]

Am Donnerstag, den 11.06.2009, 09:38 +0200 schrieb Doug Scoular:

>     However, I've recently discovered that people sometimes
>     manually add records to the database without obtaining
>     IDs from the sequence. If this happens what is the best
>     way to obtain the next ID value ? My suspicion is that
>     my use of sequences becomes invalid.
>
>     Can I just ignore the sequence in this case...
>     I thought I could just select all IDs in order and add one
>     to the highest one and use that... is this sensible ?
>     Can DBIC already do this for me ?

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;

>
>     Any thoughts much appreciated...
>
>     Cheers,
>
>     Doug
--
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@...

[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.

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




Alexander Hartmaier wrote:

> Am Donnerstag, den 11.06.2009, 09:38 +0200 schrieb Doug Scoular:
>  
>>     However, I've recently discovered that people sometimes
>>     manually add records to the database without obtaining
>>     IDs from the sequence. If this happens what is the best
>>     way to obtain the next ID value ? My suspicion is that
>>     my use of sequences becomes invalid.
>>
>>     Can I just ignore the sequence in this case...
>>     I thought I could just select all IDs in order and add one
>>     to the highest one and use that... is this sensible ?
>>     Can DBIC already do this for me ?
>>    
>
> 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;
>
>  
>>     Any thoughts much appreciated...
>>
>>     Cheers,
>>
>>     Doug
>>    
> --
> 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@...
>
>  

_______________________________________________
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@...

[Alexander Hartmaier]

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@...

[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@...
>
>  

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


_______________________________________________
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@...

[Jason Gottshall-2]

Johnny Gebreselassie wrote:
> 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
>    } });"
>
> 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 

Looks like "->sequence($seqname)" is a shortcut for setting the
'sequence' column_info attribute on the column(s) defined by
"->set_primary_key(@pkcols)". So if you define the sequence during the
call to ->add_columns() (as above), you really don't need to call
->sequence() as well.

Incidentally, I'm wondering if one should also be setting
"is_auto_increment => 1" in this situation...

--
Jason Gottshall
[hidden email]


_______________________________________________
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@...