Quantcast

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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Martin Landa
Hi,

recently I discovered strange behaviour when converting SQLite
database to Spatialite:

ogr2ogr -f SQLite -dsco SPATIALITE=YES Export_vse.sdb Export_vse.db
...
CreateSpatialIndex() error: a physical column named ROWID shadows the real ROWID

The output table contains rowid column even input table doesn't not
contain such column. It seems that this column is created by GDAL when
converting data into Spatialite DB, is it possible?

Thanks, Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

Even Rouault-2

Martin,

 

This error is emitted by spatialite.

 

See

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Shadowed+ROWID+issues

 

Did you check that the FID column name of your input table wasn't rowid ?

 

I could reproduce by forging first such a DB

 

$ ogrinfo test.db -al -so

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: test

Geometry: Unknown (any)

Feature Count: 1

Layer SRS WKT:

(unknown)

FID Column = rowid <-- see

Geometry Column = GEOMETRY

 

Then:

 

$ ogr2ogr test2.db test.db -dsco spatialite=yes -f sqlite

CreateSpatialIndex() error: a physical column named ROWID shadows the real ROWID

 

Workaround :

 

$ ogr2ogr test2.db test.db -dsco spatialite=yes -f sqlite -lco fid=my_rowid

 

I think that Spatialite's validateRowid() fuction should be patched to check if the rowid column is not of type INTEGER PRIMARY KEY, if so that's acceptable, instead of just checking for rowid. CC'ing Sandro.

 

Even

 

 

On mardi 7 mars 2017 13:10:32 CET Martin Landa wrote:

> Hi,

>

> recently I discovered strange behaviour when converting SQLite

> database to Spatialite:

>

> ogr2ogr -f SQLite -dsco SPATIALITE=YES Export_vse.sdb Export_vse.db

> ...

> CreateSpatialIndex() error: a physical column named ROWID shadows the real

> ROWID

>

> The output table contains rowid column even input table doesn't not

> contain such column. It seems that this column is created by GDAL when

> converting data into Spatialite DB, is it possible?

>

> Thanks, Martin

 

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

jratike80
Even Rouault-2 wrote
I think that Spatialite's validateRowid() fuction should be patched to check if the rowid
column is not of type INTEGER PRIMARY KEY, if so that's acceptable, instead of just checking
for rowid. CC'ing Sandro.
Let's see what Sandro says but I do not believe it is acceptable. The r-tree spatial index is returning rowids to be used as keys to real data and if the main table has an ordinary attribute named as ROWID you can guess what will happen. System works fine if you do not utilize spatial index. If you use it you use you will get crazy results. I remember I had quite a headache because of that some years ago https://groups.google.com/forum/#!searchin/spatialite-users/rowid%7Csort:relevance/spatialite-users/QwRdCiWoVKw/sXqCNZ5wn34J.

-Jukka Rahkonen-
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

Even Rouault-2

On mardi 7 mars 2017 07:21:55 CET jratike80 wrote:

> Even Rouault-2 wrote

>

> > I think that Spatialite's validateRowid() fuction should be patched to

> > check if the rowid

> > column is not of type INTEGER PRIMARY KEY, if so that's acceptable,

> > instead of just checking

> > for rowid. CC'ing Sandro.

>

> Let's see what Sandro says but I do not believe it is acceptable. The r-tree

> spatial index is returning rowids to be used as keys to real data and if

> the main table has an ordinary attribute named as ROWID

 

I agree. But my point is about an explicit column called ROWID and of type INTEGER PRIMARY KEY. In the case, I think it's OK since both the implicit rowid and explicit rowid columns happen to match.

 

I've led a small experiment by patching spatialite to accept creating a spatiali index even if a table has a rowid column.

 

Initial state:

 

$ ogrinfo test.db -sql "select * from sqlite_master where name = 'test'"

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 1

Layer SRS WKT:

(unknown)

type: String (0.0)

name: String (0.0)

tbl_name: String (0.0)

rootpage: Integer (0.0)

sql: String (0.0)

OGRFeature(SELECT):0

type (String) = table

name (String) = test

tbl_name (String) = test

rootpage (Integer) = 5673

sql (String) = CREATE TABLE 'test' ( rowid INTEGER PRIMARY KEY, "GEOMETRY" GEOMETRY)

 

 

$ ogrinfo test.db -al

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: test

Geometry: Unknown (any)

Feature Count: 3

Extent: (1.000000, 2.000000) - (5.000000, 6.000000)

Layer SRS WKT:

(unknown)

FID Column = rowid

Geometry Column = GEOMETRY

OGRFeature(test):1

POINT (1 2)

 

OGRFeature(test):3

POINT (3 4)

 

OGRFeature(test):5

POINT (5 6)

 

==> 3 records of feature id / row id 1, 3 and 5

 

$ ogrinfo test.db -sql "select * from idx_test_GEOMETRY"

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 3

Layer SRS WKT:

(unknown)

pkid: Integer (0.0)

xmin: Real (0.0)

xmax: Real (0.0)

ymin: Real (0.0)

ymax: Real (0.0)

OGRFeature(SELECT):0

pkid (Integer) = 1

xmin (Real) = 1

xmax (Real) = 1

ymin (Real) = 2

ymax (Real) = 2

 

OGRFeature(SELECT):1

pkid (Integer) = 3

xmin (Real) = 3

xmax (Real) = 3

ymin (Real) = 4

ymax (Real) = 4

 

OGRFeature(SELECT):2

pkid (Integer) = 5

xmin (Real) = 5

xmax (Real) = 5

ymin (Real) = 6

ymax (Real) = 6

 

Now I delete feature with rowid=3:

 

$ ogrinfo test.db -sql "delete from test where rowid = 3"

INFO: Open of `test.db'

using driver `SQLite' successful.

 

 

Let's the state:

 

$ ogrinfo test.db -al

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: test

Geometry: Unknown (any)

Feature Count: 2

Extent: (1.000000, 2.000000) - (5.000000, 6.000000)

Layer SRS WKT:

(unknown)

FID Column = rowid

Geometry Column = GEOMETRY

OGRFeature(test):1

POINT (1 2)

 

OGRFeature(test):5

POINT (5 6)

 

$ ogrinfo test.db -sql "select * from idx_test_GEOMETRY"

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 2

Layer SRS WKT:

(unknown)

pkid: Integer (0.0)

xmin: Real (0.0)

xmax: Real (0.0)

ymin: Real (0.0)

ymax: Real (0.0)

OGRFeature(SELECT):0

pkid (Integer) = 1

xmin (Real) = 1

xmax (Real) = 1

ymin (Real) = 2

ymax (Real) = 2

 

OGRFeature(SELECT):1

pkid (Integer) = 5

xmin (Real) = 5

xmax (Real) = 5

ymin (Real) = 6

ymax (Real) = 6

 

 

==> The spatial index has been properly maintained: the entry corresponding to the deleted feature has been deleted as well.

 

 

> you can guess what

> will happen. System works fine if you do not utilize spatial index. If you

> use it you use you will get crazy results. I remember I had quite a

> headache because of that some years ago

> https://groups.google.com/forum/#!searchin/spatialite-users/rowid%7Csort:rel

> evance/spatialite-users/QwRdCiWoVKw/sXqCNZ5wn34J.

>

> -Jukka Rahkonen-

>

>

>

> --

> View this message in context:

> http://osgeo-org.1560.x6.nabble.com/gdal-dev-sqlite-spatialite-rowid-tp5311

> 181p5311212.html Sent from the GDAL - Dev mailing list archive at

> Nabble.com.

> _______________________________________________

> gdal-dev mailing list

> [hidden email]

> https://lists.osgeo.org/mailman/listinfo/gdal-dev

 

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

Even Rouault-2

Arg, it seems my email has been truncated in text version. Retrying...

 

> Now I delete feature with rowid=3:

>

 

Let's see the state:

 

$ ogrinfo test.db -al

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: test

Geometry: Unknown (any)

Feature Count: 2

Extent: (1.000000, 2.000000) - (5.000000, 6.000000)

Layer SRS WKT:

(unknown)

FID Column = rowid

Geometry Column = GEOMETRY

OGRFeature(test):1

POINT (1 2)

 

OGRFeature(test):5

POINT (5 6)

 

$ ogrinfo test.db -sql "select * from idx_test_GEOMETRY"

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 2

Layer SRS WKT:

(unknown)

pkid: Integer (0.0)

xmin: Real (0.0)

xmax: Real (0.0)

ymin: Real (0.0)

ymax: Real (0.0)

OGRFeature(SELECT):0

pkid (Integer) = 1

xmin (Real) = 1

xmax (Real) = 1

ymin (Real) = 2

ymax (Real) = 2

 

OGRFeature(SELECT):1

pkid (Integer) = 5

xmin (Real) = 5

xmax (Real) = 5

ymin (Real) = 6

ymax (Real) = 6

 

 

-> The spatial index has been properly maintained: the entry corresponding to the deleted feature has been deleted as well.

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

jratike80
In reply to this post by Martin Landa

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_?

 

-Jukka-

 

Lähettäjä: Even Rouault [mailto:[hidden email]]
Lähetetty: 7. maaliskuuta 2017 16:51
Vastaanottaja: [hidden email]
Kopio: Rahkonen Jukka (MML) <[hidden email]>; [hidden email]
Aihe: Re: [gdal-dev] sqlite -> spatialite (rowid)

 

Arg, it seems my email has been truncated in text version. Retrying...

 

> Now I delete feature with rowid=3:

> 

 

Let's see the state:

 

$ ogrinfo test.db -al

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: test

Geometry: Unknown (any)

Feature Count: 2

Extent: (1.000000, 2.000000) - (5.000000, 6.000000)

Layer SRS WKT:

(unknown)

FID Column = rowid

Geometry Column = GEOMETRY

OGRFeature(test):1

POINT (1 2)

 

OGRFeature(test):5

POINT (5 6)

 

$ ogrinfo test.db -sql "select * from idx_test_GEOMETRY"

INFO: Open of `test.db'

using driver `SQLite' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 2

Layer SRS WKT:

(unknown)

pkid: Integer (0.0)

xmin: Real (0.0)

xmax: Real (0.0)

ymin: Real (0.0)

ymax: Real (0.0)

OGRFeature(SELECT):0

pkid (Integer) = 1

xmin (Real) = 1

xmax (Real) = 1

ymin (Real) = 2

ymax (Real) = 2

 

OGRFeature(SELECT):1

pkid (Integer) = 5

xmin (Real) = 5

xmax (Real) = 5

ymin (Real) = 6

ymax (Real) = 6

 

 

-> The spatial index has been properly maintained: the entry corresponding to the deleted feature has been deleted as well.

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

Martin Landa
In reply to this post by Even Rouault-2
Hi Even,

2017-03-07 13:41 GMT+01:00 Even Rouault <[hidden email]>:

[...]

> FID Column = rowid <-- see

right, this exactly my case.

> Workaround :
> $ ogr2ogr test2.db test.db -dsco spatialite=yes -f sqlite -lco fid=my_rowid

Thanks, it works. Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

a.furieri
In reply to this post by jratike80
On Tue, 7 Mar 2017 07:21:55 -0700 (MST), jratike80 wrote:

> Let's see what Sandro says but I do not believe it is acceptable. The
> r-tree
> spatial index is returning rowids to be used as keys to real data and
> if the
> main table has an ordinary attribute named as ROWID you can guess
> what will
> happen. System works fine if you do not utilize spatial index. If you
> use it
> you use you will get crazy results. I remember I had quite a headache
> because of that some years ago
>
> https://groups.google.com/forum/#!searchin/spatialite-users/rowid%7Csort:relevance/spatialite-users/QwRdCiWoVKw/sXqCNZ5wn34J.
>

Hi Jukka,

the SpatiaLite's spatial index is fully based on SQLite's
R*Tree; and an R*Tree isn't at all "an index", it simply
is a distinct VirtualTable.
SQLite's itself has absolutely no idea about the strict
relationship joining the Spatial Index and the indexed
table; and symmetrically SpatiaLite itself is completely
unaware of the actions performed by SQLite.

so we must walk on a very narrow and slippery path in
order to keep properly aligned both the SpatialIndex
and the indexed table:

1. all INSERT, UPDATE and DELETE operations on behalf
    of the indexed table _MUST_ be intercepted by
    appropriate Triggers so to properly synchronize
    the companion R*Tree table.

2. corresponding rows on both tables _MUST_
    declare a common key value allowing to perform
    relational JOINs in the safest way.

the R*Tree exclusively supports an INTEGER (64 bit)
key value uniquely identifying each row.

this fits very well with indexed tables explicitly
declaring a Primary Key of the INTEGER data-type,
but we must carefully consider that several different
alternatives could legitimately exist:

a. the indexed table could completely lack any PK
b. it could eventually declare a PK based on
    a single column of some data-type different from
    INTEGER (e.g. TEXT).
c. and finally it could possibly declare a
    multi-column PK.

none of the above three cases can ever match the
INTEGER value declared by the R*Tree row.
happily enough, SQLite supports a special INTEGER
key  uniquely identifying each single row: ROWID
- as the most general rule, any row has its
   own ROWID corresponding to the positional
   index of that row within its table.
- an exception exists: if the table declares s
   single-column PK and the column is declared
   to be of the INTEGER data-type then the ROWID
   simply is an alias name containing exactly
   the same value stored into the PK column.

short conclusion: using ROWIDs as the common
relational key joining both the R*Tree and
the indexed table is the unique solution
applicable to any table independently by
the exact nature of its Primary Key; and
it works well even in the absence of any PK.

this design choice poses a very strict constraint:
no ordinary column named ROWID should be present
into the indexed table, otherwise a big chaotic
mess will devastate the Spatial Index.
it could be sometimes an unpleasant restriction,
but it is usually well acceptable in the vast
majority of cases.

bye Sandro
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

a.furieri
In reply to this post by Even Rouault-2
On Tue, 07 Mar 2017 13:41:48 +0100, Even Rouault wrote:
> I think that Spatialite's validateRowid() fuction should be patched
> to check if
> the rowid column is not of type INTEGER PRIMARY KEY, if so that's
> acceptable,
> instead of just checking for rowid.
>

Hi Even,

very good point.

SQLite already considers ROWID as an alias name for the
Primary Key column, if the PK is based on a single
column of the INTEGER data-type.

so "ROWID INTEGER PRIMARY KEY" is a perfectly acceptable
condition, and don't poses any risk to the Spatial Index.

thanks for your useful hint; I'll patch ASAP the code
so to distinguish this peculiar case.

bye Sandro

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

a.furieri
In reply to this post by jratike80
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


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

jratike80
Hi Sandro,

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ä: [hidden email] <[hidden email]>
Lähetetty: 7. maaliskuuta 2017 18:01
Vastaanottaja: Rahkonen Jukka (MML)
Kopio: Even Rouault; [hidden email]
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


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

Even Rouault-2

 

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ä: [hidden email] <[hidden email]>

> Lähetetty: 7. maaliskuuta 2017 18:01

> Vastaanottaja: Rahkonen Jukka (MML)

> Kopio: Even Rouault; [hidden email]

> 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


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

a.furieri
On Tue, 07 Mar 2017 20:41:26 +0100, Even Rouault wrote:
> 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.
>


Hi Even,

spatialite_gui and spatialite-tools as well usually declare
INTEGER PRIMARY KEY AUTOINCREMENT (e.g. when importing some
external Shapefile).


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

from my direct experience on the field: no, isn't dramatic at all.
(at least: under the most usual conditions).


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

+1

such a choice will surely contribute to a better and smoother
integration between "vanilla" spatialite and GDAL/OGR, possibly
avoiding to create slight misalignments eventually leading to
puzzling issues very hard to be correctly understood by average
users (that are not usually expected to be high rank pundits
possessing a complete mastership of sqlite).

bye Sandro
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite -> spatialite (rowid)

Even Rouault-2

 

> > So wondering if that makes sense in the SQLite driver to switch to

> > AUTOINCREMENT

 

FYI, I've just switched to declaring as AUTOINCREMENT (no option)

 

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Loading...