View Ticket
Not logged in
Ticket Hash: 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: (text/x-fossil-plain)
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: (text/html)
demo5 does not run correctly in its present form:<br>

./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]

<hr>
To following prevents the demo from running:<br>
<pre>
    if (argc > 1 || argv[0] == NULL)
	argc = 1;		/* silencing stupid compiler warnings */
</pre>
<hr>
After enclosing inside a '#ifdef 0', the demo runs correctly without warnings during compilation.<br>
<pre>
-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]
</pre>
<hr>
results reported by user are returned<br>
<pre>
Field #1) FieldName=id_compteur
          TextValues=7 
	  MaxSize/Length=4
Field #2) FieldName=type_compteur
	  TextValues=7 
	  MaxSize/Length=9

</pre>

The TEXT columns have an extra '<b>eur</b>' added<br>

<pre>
0	id	INTEGER
1	geom	POINT
2	id_compt	TEXT
3	type_compt	TEXT
4	angle	INTEGER
5	id_tmp	INTEGER
</pre>

mj10777 added on 2019-01-11 06:02:07: (text/html)
gaiaGetVectorLayersList calls gaiaGetVectorLayersList_v4<br>
There a SELECT is done against <b>vector_layers_field_infos</b><br>

returning:<br>
<pre>
0	id
1	id_compteur
2	type_compteur
3	angle
4	id_tmp
5	geom
</pre>
<hr>
Cause of problem:<br>
User has changed a TABLE that is administered by spatialite (containing a geometry-column) using a <b>sqlite3</b> command (ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;)<br>
<hr>
<b>sqlite3</b> does not know, nor care's, about administration TABLEs of any extension.<br>
Therefore <b>vector_layers_field_infos</b> was not adapted with the new column names<br>
(in this case the entries of the administration should have been <b>removed</b> before the new TABLE, with the same name, was created)<br>
<hr>

For this scenario, the following should be done, using the present fossil version, with <b>sqlite3</b> version >= 3.25<br>
<ul>
 <li><b>RenameTable</b> (administration TABLEs will be adapted)</li>
 <li>create and fill new table from old table</li>
 <li><b>DropTable</b> old table (administration TABLEs will be adapted)</li>
</ul><br>
another combination would have been:
<ul>
 <li><b>CloneTable</b> create a copy of the TABLE with data</li>
 <li><b>DropGeoTable/DropTable</b> old table (administration TABLEs will be adapted)</li>
</ul>
 <li>create and fill new table from cloned table</li>
 <li><b>DropGeoTable/DropTable</b> cloned table (administration TABLEs will be adapted)</li>
</ul><br>
DropGeoTable has been Deprecated, use DropTable instead.