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

Question

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.

Answer 1

You need to copy the table with a new table name and rename the columns when you're copying the data over. A table manager function would be ideal for this but hasn't been created for the gui yet. The link below shows some formal information for this with sample code in SQLite. SpatiaLite is a part of SQLite so the coding is the same. Good luck!

https://blog.xojo.com/2013/12/04/renaming-columns-in-sqlite-tables/