Bug report #14239

Renaming Spatialite tables in DB Manager makes them disappear!

Added by Saber Razmjooei over 3 years ago. Updated 7 months ago.

Status:Open
Priority:Normal
Assignee:Julien Cabieces
Category:DB Manager
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

Description

To reproduce:
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

Nothing appears
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.

History

#1 Updated by Jukka Rahkonen over 3 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.

#2 Updated by Giovanni Manghi over 2 years ago

  • Easy fix? set to No
  • Regression? set to No

#3 Updated by Julien Cabieces 9 months 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.

#4 Updated by Giovanni Manghi 9 months ago

  • Affected QGIS version changed from master to 3.5(master)

#5 Updated by Kent Stanton 8 months 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.

#6 Updated by Giovanni Manghi 7 months ago

  • Affected QGIS version changed from 3.5(master) to 3.7(master)

Also available in: Atom PDF