Feature #1604
closed
Script for database upgrades
Added by Alexander Watzinger almost 3 years ago.
Updated over 2 years ago.
Description
Since we track current and needed database version we will write a script that checks and does database upgrades needed by the software.
In the first version it will have to be started manually because I'm not sure how far we want to automate this. Better test it a while first.
ImplementationA script (install/update_database.py) that contains a dict {database version: upgrade_file_path} and a function that:
- Checks if current database version is the right one
- Checks if the software version is supported for database upgrades (not sure how far we want to go back)
- Makes a backup (like the SQL one you can do manually)
- For each database upgrade check if the file exists and try executing the SQL
- Target version changed from 7.0.0 to 7.1.0
- Target version changed from 7.1.0 to 7.4.0
- Target version changed from 7.4.0 to 7.3.0
- Status changed from Acknowledged to Assigned
- Assignee set to Alexander Watzinger
- Status changed from Assigned to In Progress
Plan discussed with Bernhard:
- Print software, database and required database version (also include production.py)
- Test if database and required database version are same (exit if yes)
- Created database version list
- Check if database version in version list
- Check which updates needed in which order and if corresponding SQL files exists
- Backup database to export/sql (like done manually from backend)
- Loop files and execute SQLs
- Test, clean up, ... before merging in develop
@ @Bernhard Koschiček-Krombholz
After successfully implementing the database update script together yesterday I did some refinements:
- Replaced software/database version dict with just a database version list in config/database_versions because we don't need the software version after all
- Instead setting the database version in config.py it is now taken from config/database_versions to prevent possible conflicts
- Test and coverage work fine again (after fixing some Path related stuff)
- Added Mypy checks
- Improved output messages (more consistent, printing path to backup, ...) and noted in script comment that it is still experimental
- Tested it with a 6.6.0 database version
We still should go over it together next week but works really great so far.
- Status changed from In Progress to Closed
I merged this feature branch in develop so that the path/config/app changes get a little tested in between too.
Closing this issue but we still should look over it together next week.
While updating the online versions I run into some issues with the database update script. I first made a ticket but than decided to solve this immediately:
- Check about file permissions before backing up the database, otherwise the script ends with an ugly error message (fixed)
- When setting the software version in develop to 7.4.0 the script said it isn't supported. This shouldn't happen when there is no database upgrade needed (fixed)
Also available in: Atom
PDF