Automatic database upgrades

[osmith42]

From tools/databaseUpgrade/README.md:

Database upgrades are currently limited to semi-automation.

Alembic is used to handle database schema upgades.

This will not give a foolproof upgrade, ensure you read the generated scripts. For best results (and in production environments), read lib/database.py and compare each base object to the table in your database. Types for columns should also be checked.

I think it would be very useful to have automatic upgrades, so users could just upgrade to the next PyHSS version and don't need to worry about manually adjusting the database.

We could implement this as follows:

• Store the current version of the database inside the database (I haven't worked with alembic before, but it doesn't seem to do this on its own.)
• Create a separate DB tool (script) that can be used to:
  • Check if the DB needs to be initialized or upgraded.
  • Initialize the DB by starting with a base and applying all DB upgrades.
  • Upgrade an older version of the DB to the latest version.
• When starting any of the services, check if the DB was initialized, and that the version has the required version or higher. If this is not the case, refuse to start and print an error telling the user how to run the DB tool.
• The DB tool could then be called after pyHSS was installed or upgraded (e.g. in the Dockerfile before starting any of the services, or in Debian packaging as postinst script).

@nickvsnetworking: what do you think, is this something you would like to see implemented?

CC: @Takuto88

EDIT 2025-12-01: instead of having a separate DB upgrade tool, it would be even more convenient if the HSS service of PyHSS just did this automatically, and all other services wait with starting up until the DB was upgraded in case it has a too low number.