Data migration after removing obsolete data classes

[GustaafL]

After removing obsolete dataclasses in PR #695 the data migration needs to be done. db migrate creates a large file that is not able to execute because of the data relationships and needs to be manually checked.

"The data migration is still a sizable task. Running flexmeasures db migrate generates a very large auto revision file (~200 lines) that needs to be checked manually. These predominantly deal with the downgrade.

There is also the offchance that the deleted tables contain user data that has not been migrated yet. This should only be possible in case data was added by circumventing our own classes (e.g. initializing an Asset has been leading to initialize a GenericAsset and a Sensor under the hood). However, I can't guarantee that simply removing the old tables wouldn't lead to some loss of data. Therefore, I suggest creating backups of the deleted tables.

Foreseeing this exact discussion, I had moved the database migration into the list of follow-up tasks to this PR (see task list above)."

Originally posted by @Flix6x in #695 (comment)

[Flix6x]

We could make the upgrade command check whether the obsolete tables contain any data. If they do contain data, we warn the user about the potential loss of data and offer to automatically create a dump file. If they don't contain data, we can drop the obsolete tables.

[nhoening]

Yes to the check, no to the automatic dump file creation.
They can do that, if there even is anyone in that need.

[nhoening]

First step: Run flexmeasures db revision to understand what comes at us here. Do as a draft PR first, to discuss.

[Flix6x]

The correct command is flexmeasures db migrate, because revision only creates an empty revision file. Looking forward to provide some insights on the draft PR. I just ran flexmeasures db migrate myself and got to see we will drop the following tables:

Models previously used for devices

• asset_type + 2 indices (asset_type_can_curtail_idx and asset_type_can_shift_idx)
• asset
• power + 2 indices (power_datetime_idx and power_sensor_id_idx)

Models previously used for markets

• market_type
• market
• price + 2 indices (price_datetime_idx and price_sensor_id_idx)

Models previously used for weather sensors

• weather_sensor_type
• weather_sensor
• weather + 2 indices (weather_datetime_idx and weather_sensor_id_idx)

Linked indices

FYI, the following indices are linked to sensor ids (i.e. pointing to a db model currently in use):

• power_sensor_id_idx
• price_sensor_id_idx
• weather_sensor_id_idx

In theory, these could be used to check (perhaps through a checksum of sorts) whether old data had already been copied from the power, price and weather tables to the timed_belief table. However, I suspect those old tables to be empty in most cases. We could also just inform users, in case these old tables are not empty, to reach out to us if they are unsure about whether the data had already been copied.