Weird QGIS behaviour when modifying a spatialite table

[qgib]

Author Name: Christophe Damour (@sigeal)
Original Redmine Issue: 19598
Affected QGIS version: 3.4.1
Redmine category:attribute_table
Assignee: Julien Cabieces

---

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 (Christophe Damour)

[qgib]

Author Name: Christophe Damour (@sigeal)

---

Also reproduced on Ubuntu 16.04

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• operating_system was changed from W10 to

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: Christophe Damour (@sigeal)

---

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

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• version was changed from 3.2 to 3.4.1
• status_id was changed from Feedback to Open

[qgib]

Author Name: Julien Cabieces (Julien Cabieces)

---

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

---

• assigned_to_id was configured as Julien Cabieces

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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".

[qgib]

Author Name: Mark Johnson (Mark Johnson)

---

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.

[qgib]

Author Name: Julien Cabieces (Julien Cabieces)

---

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 #22236

---

• status_id was changed from Open to Closed

[qgib]

Author Name: Christophe Damour (@sigeal)

---

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).

[qgib]

Author Name: Christophe Damour (@sigeal)

---

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.

[qgib]

Author Name: Mark Johnson (Mark Johnson)

---

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');

[qgib]

Author Name: Mark Johnson (Mark Johnson)

---

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