Bug report #19598

Weird QGIS behaviour when modifying a spatialite table

Added by Christophe Damour almost 6 years ago. Updated over 5 years ago.

Status:Closed
Priority:High
Assignee:Julien Cabieces
Category:Attribute table
Affected QGIS version:3.4.1 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:Yes Copied to github as #:27425

Description

I am trying to modify column names in a SpatiaLite table.
To do that, I run the following SQL statements in the DB Manager :

-- Rename existing table
ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;

-- Create new table with modified column names
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY,
geom POINT,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

-- Insert data from renamed table
INSERT INTO compteur_routier(id, geom, id_compt, type_compt, angle, id_tmp)
SELECT id, geom, id_compteur, type_compteur, angle, id_tmp FROM compteur_routier_sav;

-- Drop renamed table
DROP TABLE compteur_routier_sav;

This works perfectly in the DB manager, column names are modified and datas are kept.
However, when I load the resulting table in QGIS and open the attribute table, I get old column names and datas replaced by column names.
Please have a look at screenshots and test db in attached files.

test_files.zip (445 KB) Christophe Damour, 2018-08-11 10:03 AM

History

#1 Updated by Christophe Damour over 5 years ago

Also reproduced on Ubuntu 16.04

#2 Updated by Giovanni Manghi over 5 years ago

  • Operating System deleted (W10)

#3 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Open to Feedback

Please try on QGIS 3.4.1, if the issue is still valid change the affected version, thanks.

#4 Updated by Christophe Damour over 5 years ago

Problem still there in 3.4.1, but I can't modify the affected version...
Can you do that for me ?
Thanks,

#5 Updated by Giovanni Manghi over 5 years ago

  • Affected QGIS version changed from 3.2 to 3.4.1
  • Status changed from Feedback to Open

#6 Updated by Julien Cabieces over 5 years ago

  • Assignee set to Julien Cabieces

#7 Updated by Giovanni Manghi over 5 years ago

Julien Cabieces wrote:

It seems to be a problem in spatialite. I create a ticket : https://www.gaia-gis.it/fossil/libspatialite/tktview/e8d83559fbc5d492ff1dff86e07751547b485430

should this be confirmed as a SL issue this ticket must be closed as "upstream".

#8 Updated by Mark Johnson over 5 years ago

This is a user error, not a spatialite error.
----
As a general rule, the following should be considered true:
  • sqlite3 does not know, nor care, about administration TABLEs of any extension.
----
In this case the old SpatialTable was renamed using
  • ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;
  • all entries in the administration TABLEs remained, including vector_layers_field_infos which contained the old column names
  • a new SpatialTable, with the same name was incorrectly created (without AddGeometryColumn)
    (this was not the cause of this problem, but never the less is incorrect)

The (closed) Spatialite Issue contains tips on how to deal with this correctly, using spatialite commands that deals with administration TABLEs properly.

#9 Updated by Julien Cabieces over 5 years ago

  • Status changed from Open to Closed

Thank you for the detailed answer and sorry for the useless spatialite ticket.

There is still a bug in QGIS because renaming a table is done wrong in db manager plugin (alter table instead of rename table). I close this issue because there is already another one on the subject #14239

#10 Updated by Christophe Damour over 5 years ago

Thank you all for input on this problem.
I now know what I was doing wrong and look forward to being able to handle it from QGIS (and DB Manager plugin).

#11 Updated by Christophe Damour over 5 years ago

Attempt to summarize what currently works in QGIS 3.4.1 using DB Manager Plugin (special thanks to Mark Johnson for the guidelines) :

-- Create a copy of the table with data
SELECT CloneTable('main', 'compteur_routier', 'compteur_routier_sav', 1);

-- Drop old table (administration tables will be adapted)
SELECT DropGeoTable('compteur_routier');

-- Create and fill new table from cloned table
-- Create new table with modified column names, WITHOUT geom column
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY NOT NULL,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

-- Add geom column
SELECT AddGeometryColumn('compteur_routier', 'geom', 2154, 'POINT', 'XY', 1);

-- Insert data from renamed table
INSERT INTO compteur_routier(id, geom, id_compt, type_compt, angle, id_tmp)
SELECT id, geom, id_compteur, type_compteur, angle, id_tmp FROM compteur_routier_sav;

-- Drop cloned table (administration tables will be adapted)
SELECT DropGeoTable('compteur_routier_sav'); -- QGIS 3.4.1

Note : DropGeoTable should be replaced by DropTable when SpatiaLite lib will be updated in QGIS.

#12 Updated by Mark Johnson over 5 years ago

Add the following to round it off:

-- after AddGeometryColumn [SpatialIndex was removed with DropGeoTable]
SELECT CreateSpatialIndex('compteur_routier', 'geom');
-- after INSERT
SELECT UpdateLayerStatitics('compteur_routier', 'geom');

#13 Updated by Mark Johnson over 5 years ago

For future reference:
When using the fossil version of Spatialite (5.0.0) together with SQLite >= 3.25.0
  • RenameTable and RenameColumn can be used to complete this task

These commands will insure that all administration tasks will be completed correctly including the renaming/adapting of all TRIGGERs and VIEWs, for both 'Normal' and SpatialTables.

Preparations are in the last testing phase for support for GeoPackages and Gdal/Ogr-Fdo Databases.

Due to the historical development of SQLite, care must be taken to create VIEWs with proper field definitions (which are optional).

Full details and background information can be found here:

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.adminstration.html#topic_VIEW_to_SpatialView

Also available in: Atom PDF