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

[Even Rouault]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170307/54106c64/attachment-0001.html>