[GIS] How to update/refresh fields/attributes after modifying SpatiaLite/SQLite database

qgisqgis-3spatialite

I have modified the columns in a SpatiaLite database table (by creating a new table with a renamed column, populating it with the old table, deleting old and renaming the new table to the old).

I have run VACUUM on the database, re-added the layer anew, refreshed and reconnected to the database, and also tried restarting QGIS, but I am unable to get the renamed (and reordered) columns to show correctly. In both the Attributes Form (and table) and Source Fields the old columns are used. The database manager does show the current structure.

Is there some cache to clear? How can I get QGIS to pick up the changes?

Update: oh, I see there is a table geometry_columns_field_infos which isn't shown in the Database Manager, which contains a list of fields. Is there a way to rebuild it, or do I have to do this manually?

Update 2: well, I found a workaround to update the statistics:

UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics('geometry_table_name');

This indeed updates the geometry_columns_field_infos table, but it doesn't really solve the issue. When I try to use the SpatiaLite layer in a virtual layer, I get Query preparation error on PRAGMA table_info(_tview): no such column: geometry_table_name.columnname.

Best Answer

It feels wrong, but at least it's a workaround, via this issue:

UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics('geometry_table_name');

It seems that in addition to having an updated geometry_columns_field_infos table, I needed to remove and re-add the SpatiaLite connection and/or restart QGIS for the changes to be picked up everywhere.

I'm leaving this as not accepted for now in case a better answer emerges.

Related Question