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

[Even Rouault]

I see that in the SQLite/Spatialite driver the primary key column is created just as INTEGER 
PRIMARY KEY, whereas in GeoPackage the standard requires that is INTEGER PRIMARY KEY 
AUTOINCREMENT.

The SQLite doc mentions that AUTOINCREMENT is "a little slower" due to maintaining the 
sequence, but not sure if that's dramatic.

https://www.sqlite.org/autoinc.html

So wondering if that makes sense in the SQLite driver to switch to AUTOINCREMENT and/or 
make that an option.

> 
> I have only one detail to add: the MAX+1 policy that is used when
> AUTOINCREMENT is not declared can lead to re-use of previously used ROWIDs
> long before reaching the max possible value for an INTEGER 64bit. User just
> needs to delete the record with the highest ROWID. I trust that triggers in
> SpatiaLite behave right but I just want to emphasize for other readers that
> re-using ROWIDs is not really "an highly unrealistic condition" and it must
> be considered if ROWID is used as a foreign key.  Re-use happens always and
> inevidently for example if table is truncated. Test with this SQL and table
> foo2 will have ROWID=1 (calculated as 0+1=1) while table foo3 has  ROWID=2
> because that value is taken from a sequence by autoincrement.
> 
> create table foo2 (id INTEGER PRIMARY KEY,bar INTEGER);
> create table foo3 (id INTEGER PRIMARY KEY AUTOINCREMENT,bar INTEGER);
> insert into foo2 (bar) values (1);
> insert into foo3 (bar) values (1);
> delete from foo2;
> delete from foo3;
> insert into foo2 (bar) values (2);
> insert into foo3 (bar) values (2);
> 
> 
> 
> -Jukka-
> 
> 
> ________________________________________
> Lähettäjä: a.furieri at lqt.it <a.furieri at lqt.it>
> Lähetetty: 7. maaliskuuta 2017 18:01
> Vastaanottaja: Rahkonen Jukka (MML)
> Kopio: Even Rouault; gdal-dev at lists.osgeo.org
> Aihe: Re: [gdal-dev] sqlite -> spatialite (rowid)
> 
> 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


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170307/7e8372ea/attachment-0001.html>