Project

General

Profile

Feature #1604

Script for database upgrades

Added by Alexander Watzinger 11 months ago. Updated 5 months ago.

Status:
Closed
Priority:
Normal
Category:
Backend
Target version:
Start date:
2021-11-14
Estimated time:

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.

Implementation
A 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

History

#1

Updated by Alexander Watzinger 10 months ago

  • Target version changed from 7.0.0 to 7.1.0
#2

Updated by Alexander Watzinger 9 months ago

  • Target version changed from 7.1.0 to 7.4.0
#3

Updated by Alexander Watzinger 8 months ago

  • Target version changed from 7.4.0 to 7.3.0
#4

Updated by Alexander Watzinger 8 months ago

  • Status changed from Acknowledged to Assigned
  • Assignee set to Alexander Watzinger
#5

Updated by Alexander Watzinger 8 months ago

  • Status changed from Assigned to In Progress
#6

Updated by Alexander Watzinger 8 months ago

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
#7

Updated by Alexander Watzinger 5 months ago

@ 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.

#8

Updated by Alexander Watzinger 5 months ago

  • 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.

#9

Updated by Alexander Watzinger 5 months ago

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