Re: Auto increment and unique id's

Hello Martin,

Good point about "auto-increment" keys that are only generated at INSERT time.

Normally default auto-increment keys are only generated if no explicit key is provided.  I'm pretty sure that is how MS SQL Server does it.  So you should not have any problems with explicitly specified keys.  I have not tested MySQL.

As to your fix, I have just added a test that provides a better error message if no generator is provided.  I really do not like making SELECT_MAX a default, people need make an explicit decision about that sort of thing.  Please revert your changes.  Sorry.

Insert time generated keys had been impossible with SimpleORM 2.* because it required records to have null keys.  But Frank's work has enable records with null keys to be represented in 3.0 so that they can be created remotely and then later attached to a (PostgreSQL) database.  So it is only one more step to be able to auto generate them at flush time.  This is high on the TODO list.  

But be warned that although only a very few lines of code would need to be changed, it requires considerable thought and care to get right.  It also needs a good test case with a master and detail created in the same transaction (HSQLDB supports generated "Identity" keys http://hsqldb.org/doc/guide/ch09.html).  (So needs to be able fetch the generated key back, @@IDENTITY in MS SQL.)

Nearly all databases support a sequence generator, largely because that is what Oracle does, and because there are issues with not having the key until after the record has been created.  I am surprised that MySQL does not -- have another look.

So I think use SELECT_MAX for now, and then we need to look at generate at insert time keys.

Anthony


At 08:20 PM 11/12/2008, you wrote:

>Hi,
>
>The tables I was using were MySQL tables with auto-increment primary 
>keys. As I have gathered now from earlier discussions, that does not 
>work very well, so I started using the key-generation facilities instead.
>
>* The generator did not notice that my tables had fields that were 
>AUTO_INCREMENT. So, the code 'out of the box' did not really work. When 
>I created fields (with id's that I set myself) the id's I specified were 
>silently ignored. Bug there - should at least give warning at some point.
>
>* Using ses.createWithGeneratedKey(meta) does not work out-of-the box 
>either, despite documentation that it defaults to SSELECT_MAX - 
>nullpointers were thrown (both mode and params are null). To solve that, 
>I added the following to SFieldScalar:
>
>/**
>* If generator is not already configured,
>* this method initalizes this field to use Default generator mode, 
>which is SSELECT_MAX
>* @return the mode
>*/
>
>public SGeneratorMode getGeneratorModeOrDefault()
>{
>if(this.generatorMode == null && this.theGenerator == null)
>this.setGeneratorMode(SGeneratorMode.SSELECT_MAX, 
>this.fieldName.concat("_seq_no").toLowerCase());
>return getGeneratorMode();
>}
>
>And modified the SGenerator thus:
>
>public static SGenerator theGenerator(SFieldScalar fld) {
>SGenerator gen = (SGenerator)fld.getTheGenerator();
>if (gen == null) {
>+ switch (fld.getGeneratorModeOrDefault()) {
>- switch (fld.getGeneratorMode()) {
>case SSELECT_MAX:
>gen = new SGeneratorSelectMax(fld, 
>(String)fld.getGeneratorParameter()[0]); break;
>case SSEQUENCE:
>gen = new SGeneratorSequence(fld, 
>(String)fld.getGeneratorParameter()[0]); break;
>case SINSERT:
>gen = new SGeneratorInsertIdentity(fld, 
>(String)fld.getGeneratorParameter()[0]); break;
>} 
>fld.setTheGenerator(gen);
>}
>return gen;
>}
>
>Should I commit my fix ?
>
>* Remaining question: my fields are still AUTO_INCREMENT. But it works 
>anyway, I guess since auto_increment sets exactly the value that 
>SSELECT_MAX expects - they happen to correlate, which I guess is a prime 
>candidate for future bugs. What is "The Best" way to handle this: Should 
>I remove the AUTO_INCREMENT - property from the fields in MySql and rely 
>only on the generator?
>
>-- 
>Martin Holst Swende ................. MSC Konsult AB
>tel: +46(0)70 9519098 ............... Vasagatan 52
><mailto:martin.holst_swende%40msc.se>[email protected] .......... 111 20 Stockholm
>
> 

Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
Just because it is possible to push twigs along the ground with ones nose
does not necessarily mean that is the best way to collect firewood.

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:[email protected] 
    mailto:[email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/