[gdal-dev] sqlite -> spatialite (rowid)

[a.furieri at lqt.it]

On Tue, 7 Mar 2017 15:07:04 +0000, Rahkonen Jukka (MML) wrote:
> Hi,
>
> I have a slight feeling that there may be some trouble if attribute
> "ROWID" is INTEGER PRIMARY KEY but it does not AUTOINCREMENT and 
> users
> do lot of deletes and inserts. Maybe not if the triggers in r-tree
> tables are clever enough. Just a slight feeling but perhaps you 
> should
> test also cases where ROWID differs from OID and __ROWID__?
>

Hi Jukka,

we can safely exclude any possible dangerous side-effect.

in SQLite any INTEGER PRIMARY KEY definition will always
ensure that an appropriate value will be automatically
set if not explicitly provided.

what exactly does the AUTOINCREMENT keyword is just
slightly changing the mechanism adopted to automatically
assign PK values.

1. not declaring AUTOINCREMENT
    SQLite will search for the biggest PK value, and
    will adopt this value increased by 1 as the new
    PK value.
    if the max possible value for an INTEGER 64bit
    has already been used (an highly unrealistic
    condition) then SQLite will search if there
    is some free "hole" in the PK sequence, and
    will reuse the first unused value it can find
    note: in this exceptional case it's not
    exactly true that ROWIDs will be generated
    in a regularly ascending order; sometimes
    lower values could be possibly returned.

2. declaring AUTOINCREMENT
    in this case the automatically generated PK
    values can never return back; they can simply
    growth until the maximum value supported
    by an INTEGER 64bit is reached.
    and at this point the auto-sequence will
    definitely stop working.

bye Sandro