qgis - How can I properly rename a table in a SpatiaLite DB?

Spatial layers (tables) in SpatiaLite include various supporting objects and metadata, including triggers, indices, and entries in (at minimum) the geometry_columns table. I'm looking for (preferably) a GUI that would handle all of the necessary changes in one go, or (fallback) documentation of all required changes so as not to break the spatial layers.

Tables have:

1. Entry in geometry_columns.f_table_name.



2. Five triggers named [prefix]_[table_name]_geometry, where prefix indicates whether it is a transaction rollback or index update.


3. Three spatial indices named idx_[table_name]_geometry[_suffix]

I have tried this in two applications, QGIS DB Manager and SpatiaLite-GUI.

QGIS DB Manager appears have the following effects:

1. Correctly updates geometry_columns with new table name


2. Does not rename triggers. Trigger definition is partially modified so that BEFORE [INSERT|UPDATE|DELETE] refers to new table name, but condition still searches for old name in geometry_columns.


3. Does not rename spatial indices. Not sure if this matters because triggers still refer to old index names.

When you choose Maintenance→Rename table in SpatiaLite-GUI, you just get the stub of a SQL ALTER TABLE statement. This is straightforward SQL and does even less than QGIS DB Manager. If you fill in the new table name, the table gets renamed. Other effects:

1. Table does not get renamed in geometry_columns.f_table_name, which means many GISes won't see the table as a spatial layer.


2. Does not rename triggers. Trigger definition is partially modified so that BEFORE [INSERT|UPDATE|DELETE] refers to new table name, but condition still searches for old name in geometry_columns.


3. Does not rename spatial indices. Not sure if this matters because triggers still refer to old index names.

Note that Spatialite-GUI does give the option to recover a geometry column which adds the correct entry to geometry_columns (but requires you to specify SRID, geometry type, and dimensions), to build or rebuild a spatial index, and to recover triggers, but none of these functions remove old table rows, triggers, or indices, leading to a DB with a lot of (possibly harmless but annoying) schmutz in it.