View Ticket
Not logged in
Ticket UUID: e8d83559fbc5d492ff1dff86e07751547b485430
Title: gaiaGetVectorLayersList returns wrong field name
Status: Closed Type: Documentation
Severity: Cosmetic Priority: Zero
Subsystem: Resolution: Not_A_Bug
Last Modified: 2019-01-11 06:02:07
Version Found In: 5.0.0
User Comments:
anonymous added on 2019-01-09 15:20:34:
Hi, 

Method gaiaGetVectorLayersList returns wrong fields names.

If I use spatialite binary, I've got the correct fields names (id_compt, type_compt):
spatialite> .schema compteur_routier
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY,
geom POINT,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

But If I use gaiaGetVectorLayersList method (thanks to demo5 example), I get wrong field name (id_compteur and type_compteur):
./demo5 -d ~/work/tmp/test_files/test_spl.sqlite -t compteur_routier       
SQLite version: 3.25.3
SpatiaLite version: 5.0.0-beta1



****** VectorLayersList (mode=OPTIMISTIC) *********
VectorLayer: Type=BasedOnSqlTable TableName=compteur_routier
	GeometryName=geom SRID=2154 GeometryType=POINT Dims=XY
	RowCount=7
	ExtentMin 848046.525865 / 6425228.263184
	ExtentMax 852051.441057 / 6429544.178129
	ReadOnly=FALSE Hidden=FALSE
		Field #0) FieldName=id
			IntegerValues=7 
			IntRange 1 / 7
		Field #1) FieldName=id_compteur
			TextValues=7 
			MaxSize/Length=4
		Field #2) FieldName=type_compteur
			TextValues=7 
			MaxSize/Length=9
		Field #3) FieldName=angle
			IntegerValues=7 
			IntRange 15 / 180
		Field #4) FieldName=id_tmp
			IntegerValues=7 
			IntRange 1 / 7
		Field #5) FieldName=geom
			BlobValues=7 
			MaxSize/Length=60



sample successfully terminated

You can download test files here : https://issues.qgis.org/attachments/download/13111/test_files.zip

At first, it was a reported bug in QGIS (https://issues.qgis.org/issues/19598) : the problem seems to come from the fact that the user rename the table, create a new one with new fields name, then populate new one with the old one data.

QGIS reads field name thanks to gaiaGetVectorLayersList method.

Regards,
Julien

mj10777 added on 2019-01-11 04:40:08:

demo5 does not run correctly in its present form:
./demo5 -d test_spl.sqlite -t compteur_routier -I-> main argc[5] argv[0][/home/mj10777/000_links/libspatialite_fossil/libspatialite.dustbin/examples/.libs/lt-demo5]


To following prevents the demo from running:
    if (argc > 1 || argv[0] == NULL)
	argc = 1;		/* silencing stupid compiler warnings */

After enclosing inside a '#ifdef 0', the demo runs correctly without warnings during compilation.
-I-> main argc[5] 
-I-> main argv i[1] value[-d]
-I-> main argv i[2] value[test_spl.sqlite]
-I-> main argv i[3] value[-t]
-I-> main argv i[4] value[compteur_routier]

results reported by user are returned
Field #1) FieldName=id_compteur
          TextValues=7 
	  MaxSize/Length=4
Field #2) FieldName=type_compteur
	  TextValues=7 
	  MaxSize/Length=9

The TEXT columns have an extra 'eur' added
0	id	INTEGER
1	geom	POINT
2	id_compt	TEXT
3	type_compt	TEXT
4	angle	INTEGER
5	id_tmp	INTEGER


mj10777 added on 2019-01-11 06:02:07:

gaiaGetVectorLayersList calls gaiaGetVectorLayersList_v4
There a SELECT is done against vector_layers_field_infos
returning:

0	id
1	id_compteur
2	type_compteur
3	angle
4	id_tmp
5	geom

Cause of problem:
User has changed a TABLE that is administered by spatialite (containing a geometry-column) using a sqlite3 command (ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;)

sqlite3 does not know, nor care's, about administration TABLEs of any extension.
Therefore vector_layers_field_infos was not adapted with the new column names
(in this case the entries of the administration should have been removed before the new TABLE, with the same name, was created)

For this scenario, the following should be done, using the present fossil version, with sqlite3 version >= 3.25
  • RenameTable (administration TABLEs will be adapted)
  • create and fill new table from old table
  • DropTable old table (administration TABLEs will be adapted)

another combination would have been:
  • CloneTable create a copy of the TABLE with data
  • DropGeoTable/DropTable old table (administration TABLEs will be adapted)
  • create and fill new table from cloned table
  • DropGeoTable/DropTable cloned table (administration TABLEs will be adapted)

DropGeoTable has been Deprecated, use DropTable instead.