[0.226.4] Many geometry column references are not removed by the migration

[leendertvanwolfswinkel]

See for example the Boezemmodel__neerslag schematisation

• v2_2d_lateral
• v2_2d_boundary_conditions
• v2_dem_average_area
• v2_exchange_line
• v2_grid_refinement
• v2_grid_refinement_area
• v2_impervious_surface
• v2_surface
• v2_obstacle
• v2_potential_breach

[elisalle]

When I run the migrations with threedi-schema 0.226.4 on Boezemmodel__neerslag, all of the tables you mention are gone after the migration. Can we look at this together when you have time?

[elisalle]

Went through this with Leendert and it appears to work.

[elisalle]

Update: the tables are removed, but the references to the geometry columns in the geometry_columns table are not dropped.
A solution could be cleaning up any orphaned references at the end of the migration:

def drop_deleted_geom_column_triggers():
    geometry_column_references = []
    connection = op.get_bind()
    result = connection.execute(sa.text("SELECT * FROM geometry_columns;")).fetchall()
    for row in result:
        geometry_column_references.append({"table": row[0], "column": row[1]})
    for reference in geometry_column_references:
        try:
            connection.execute(sa.text(f"SELECT {reference['column']} FROM {reference['table']};"))
        except sqlalchemy.exc.OperationalError:
            connection.execute(sa.text(f"SELECT DiscardGeometryColumn('{reference['table']}', '{reference['column']}';"))

[elisalle]

@leendertvanwolfswinkel Margriet says that there is an epic with all the last cleanup things for when the 300 schema migration is almost ready, and suggests that this should go in there.

[margrietpalm]

DiscardGeometryColumn raises an error when there is no valid reference, but does actually remove the column:

sqlite> select * from geometry_columns where f_table_name = 'v2_pipe';
v2_pipe|the_geom|2|2|4326|0
sqlite> SELECT DiscardGeometryColumn('v2_pipe', 'the_geom');
DiscardGeometryColumn() error: not existing Table or Column
0
sqlite> select * from geometry_columns where f_table_name = 'v2_pipe';

So it may be better to modify geometry_columns manually.