[Solar-talk] Oracle adapter

[Laz]

also, an insert trigger is not the only way one could use a sequence for "autoincrement" columns.  though i don't like it, i've seen many apps where the insert statements were like:

insert into table (id, name) values (seq.nextval, 'my name');

in this case, i imagine the only way you could possibly derive useful info would be based on a reliable naming convention for tables and sequences.

determining that a table has a single-column numeric primary key would be one useful piece of info.  i guess you could do that, and check the current max value of the PK column, and check the current value of a sequence that you somehow determine to be a candidate for that table (based on naming?).  if it were me, i think i'd document the adaptor as only being able
to provide that functionality (determining autoincrement columns) when
a known naming convention is used, and optionally allow the user to
provide a mapping of sequence to table/column (in config file or something).

since oracle does not really have an inherent concept of "autoincrement" table columns, i think that proclaiming a column to be autoincrement or not is risky at best (unless i'm forgetting stuff about oracles system catalogs, which is quite possible as it has been quite some time since i had to really dive in there).  

just my 2c.


----- Original Message ----
From: "Kilbride, James P." <James.Kilbride at gd-ais.com>
To: solar-talk at lists.solarphp.com
Sent: Friday, January 18, 2008 2:50:18 PM
Subject: [Solar-talk] Oracle adapter

Oracle adapter



 
 




Folks,



I'm working on an Oracle adapter and trying to figure out the best approach to 'describing' the columns autoincrementiness. For those of you who aren't familiar with Oracle it doesn't have the pretty little 'autoincrement' descriptor for tables. To do autoincrementing tables what you do is create a sequence and a trigger. The trigger replaces, on insert, the column's value with the next value in the sequence. 


Like this:



BEGIN


        SELECT asset_sequence.NEXTVAL INTO :NEW.Asset_ID FROM dual;


    END;



So this might be in a trigger called 'TRG_INSERT_ASSET_PK'. You have to write your own triggers and the sequences for it of course. So given that fact I can do a select * from user_triggers to get the triggers and do a match on table_name to find out if there is a trigger on the table we are interested in, and we can look at the triggering_event to see if it occurs on insert(which an autoincrement would) and could then pull the TRIGGER_BODY to get that code. And I think w could then search for :NEW.* to get the column name and match that against one of the columns in the table. 


So that might all work to set the boolean for autoincrement, but is this the right way to do it? This is the ugly pain of oracle 'autoincrement'. It assumes that people don't muck with it and assumes that just because there is a trigger that we must be using it for auto-increment. the sequence name 'should' probably follow a convention like we see above but it doesn't have to technically. 


Thoughts or ideas folks?



James Kilbride









      ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman-mail3.webfaction.com/pipermail/solar-talk/attachments/20080119/25011e09/attachment.html