SPIKE: MODKBEKBJ-323 Investigate Data Migration Scripts

Owned by Andrii Paias (Deactivated)
Nov 15, 2019

MODKBEKBJ-323 - Getting issue details... STATUS

Database migration process 

According to guide for database migration, every time we update db schema for a table in one of our modules we need to:
1) Add/modify sql script in "customSnippetPath" attribute and make sure that this script can be applied to any version of database, i.e. when adding/removing columns we need to use "IF NOT EXISTS/IF EXISTS" options.

2) Set "fromModuleVersion" to current version of module, .e.g.  "fromModuleVersion": "mod-kb-ebsco-java-3.0.2"

Migration for Spitfire modules:

1) mod-kb-ebsco-java

To simplify changes that we need to do I propose to set fromModuleVersion for every table except holdings_status to "mod-kb-ebsco-java-3.0.1" (the most recently released version). holdings_status was updated after release, so we need to set it to "mod-kb-ebsco-java-3.0.2"

If we do this, there can be two situations:

a) module with version 3.0.0 or earlier is updated to 3.0.2. e.g. 2.6.0 → 3.0.2. In this case all scripts will be run, this is the same behaviour as before. 

b) module with version 3.0.1 updated to 3.0.2. In this case only scripts that we updated/added in 3.0.2 will be run.

2) mod-notes

Similarly to mod-kb-ebsco-java we can set all versions to "mod-notes-2.7.0". And from now on we will update those versions every time we add changes to schema.

mod-notes already has versions set, but it seems that they are not recognized by RMB properly, because they have format "1.0" instead of "mod-notes-1.0". So theoretically we can just keep current versions and change them to correct format, but it is not clear if old versions are correct, so I think it's best to reset everything to mod-notes-2.7.0, and start keeping track of versions from now on.

3) mod-custom-fields

mod-custom-fields schema is applied as part of deployment of other modules, so setting version in schema.json for mod-custom-fields shouldn't affect anything. 

We need to set "fromModuleVersion" to "mod-users-15.7.0" for script create_custom_fields_table.sql  in mod-users.