Bug report #14239
Renaming Spatialite tables in DB Manager makes them disappear!
|Affected QGIS version:||3.7(master)||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||22236|
1- Open DB Manager
2- Browse to one of your Spatialite layers
3- Right-click to Rename a table
4- Right-click to Add to canvas
No do a query on the renamed table and add the result of query to your map. It has all the features there.
Nothing will be displayed if you try to add it through Add Vector Layer > Add Spatialite.
So, it seems to me, renaming somehow corrupts your table.
#1 Updated by Jukka Rahkonen about 5 years ago
I guess that renaming the table does not update the corresponding row in the geometry_columns and also some or all the triggers in Spatialite are not updated to suit with the new name of the table. And spatial index is lost because the index table still points to the original table name. Reference: http://www.sqlite.org/lang_altertable.html.
Renaming a Spatialite table is rather a tricky task if is should make a perfect result automatically. I almost feel that it would be better to deactivate this feature from the DB manager and wait until some true Spatialite wizard comes and improves it.
#3 Updated by Julien Cabieces over 2 years ago
- Description updated (diff)
- Assignee set to Julien Cabieces
Indeed it corrupts the table. It's because db manager use alter table which doesn't properly rename the table. More explanation on what we should do at the end of this ticket (https://www.gaia-gis.it/fossil/libspatialite/tktview/e8d83559fbc5d492ff1dff86e07751547b485430)
So we should use RenameTable instead of ALTER TABLE but there is no spatialite stable release with this RenameTable method yet. So I propose we wait next stable release.
#5 Updated by Kent Stanton about 2 years ago
You can rename the table back to the original name to restore the ability to view the features. Open the DB in a Sqlite manager such as DB Browser for Sqlite, which displays the triggers and other DB objects if you need to get the exact name. As noted the triggers are not renamed which is part of the issue.