[INFO] Autoincrement

[Ruslan Zasukhin]

On 5/7/05 4:45 PM, "Ivan Smahin" <IvanSmahin at public.kherson.ua> wrote:

> Hello All,
> 
>   Some database have such feature as autoincremental fields.
>   Oracle has sequences. This object can be used just for getting
>   unique value for a number purposes (including storing this value in
>   any field).
> 
>   Common features of autoincremental fields:
> 
>   1. It can be INTEGER field only.
>   2. Usually it is denied to write value to such field directly, but
>      many DBMS allow this on your own risk (global DB parameter)
>   3. Table may have only single autoincremental field. This is not
>     strict requirement but it is no sense to have more.
>   4. Usually only uniqueness is guaranteed. It can be gaps due to the
>     delete operations. Some DB trying to reuse such gaps, but most of
>     them just increase max value.
>   5. If there is overflow - error generated.
> 
>   THE HUGE COMMON PROBLEM:
> 
>   Assume we have some dump - never mind in which format - xml or
>   another. AutoIncremental field values also presented here as usual
>   fields values.
> 
>   If we plan to continue using IDENTITY field we must notice LoadDump
>   algorithm about this field. Its values must be recalculated very
>   similar to the ObjectPtr values. ( Found all "ptrs" which use this
>   autoincremental field and correct their values.

And this task is described by all: mySQL, Oracle, SQL Lite ?
They all can resolve this task of import ?

>   Specifics:
> 
>   SQLite:
> 
>   They have ROWID service field ( analogy of our RecID ).
>   Autoincrement can be applied only as part of PRIMARY KEY clause.
>   (Actually any INTEGER PRIMARY KEY is an alias of ROWID field.
>   They even allow to use both field names to point one.)
> 
>   No values reusing. Generate SQLITE_FULL on overflow.
>   Last used autoincrement values keeps in service table
>   SQLITE_SEQUENCE.
> 
> 
>   MySQL:
> 
>   Allows to use start autoincrement value.
> 
> 
>   Oracle:
>   
>   A lot of differences - there is no such terms as autoincrement
>   (identity) at all. Sequences are used instead.
>   It is special kind of object which main purpose is returning new value
>   of some sequence. This value can be used for imitation of identity
>   field functionality.
>   
>     
> 
>   Valentina:
> 
>   First possible approach:
>   --------------------------
>   
>   We have reserved already schema table-property Last_AutoID. So we
>   can store in schema identity for each table if needed.
>   Also we need to have some method like
> 
>   I_TableEx::get_AutoID(). Each call will increase Last_AutoID after
>   return one and store new value to the schema. If max_ullong is
>   reached - some appropriate exception must be thrown.
> 
> 
>   Also we need new flag - fIdentity ( similar to fNullable and others
>   ). This flag must be marked in ALWAYSOFF method for all fields
>   except ullong.
    
why only ullong. I think ulong also can used for this.

>   Field which is marked by such flag must be read only in some way.
>   Such field must be registered in the table as identity field.
>   
>   So if Table::AddRecord() called - Table put incremented value self
>   to this field - just before WriteRecord().
> 
>   
>   Second possible approach:
>   --------------------------
>   I don't see any reason to introduce identity fields. We have the
>   structure very similar to Oracle - RecID. It is always unique and
>   autogenerated. It's even reusable.
> 
>   The only one thing which can't be done in Oracle is make its ROWID
>   pseudo-field as Primary key. So you can't use it in this way.
> 
>   But it seems we can use our RecID in such a way. A lot of tests is
>   all that we need.
> 
>   And this approach is only for those who want to use RDB link instead of
>   more natural ObjectPtr in this situation.
> 
>   The common tip must be next: Use ObjectPtr wherever you need some
>   IDENTITY.

Ivan, so you claim that Valentina's RecID is more than enough ?

And you say that user can in Relational model to use RecID field as KEY
field to be used by some FOREIGN KEY? I.e. This will work for 2.0:

    CREATE TABLE T2(
        T1_PTR ULONG FOREIGN KEY T1(recID)
    )

Okay, taking this into account may be we really even do not need this
AutoPtr fields. Just use our cool RecID?


Guys, what you think?

Anybody want to play with such combination:
        RecID as key field,
    and FOREIGN KEY as link



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]