2

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.

  • Try using the ChangeVectorLayer plugin... we use it a lot to refresh qgis layers with back-end schema changes – DPSSpatial Mar 17 '18 at 16:49
  • @DPSSpatial, could you post a link to the ChangeVectorLayer plugin? Can't seem to find it? – Simbamangu Mar 18 '18 at 11:41
  • Sorry, it's called changeDataSource - should be in the main plugin repository in QGIS – DPSSpatial Mar 18 '18 at 14:46
  • Looks like a useful plugin. But, I just tried it and it doesn't update geometry_columns_field_infos. – arjan Mar 18 '18 at 16:01
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.