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/[email protected]
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/[email protected]
