This is a discussion of the issues raised in the following github issue:
@Frank wrote:
I have personally been working on a solution to this issue which I’ve completed in the cohort-comparison branch. If you look at the modifications to the source service you will find that it will now also properly configure the RESULT schema for each configured results daimon. If we were to refactor the way in which we use Flyway (to command line and not part of application startup) then I would also need to refactor this change and likely run the command line Flyway installation for each RESULT schema individually (less convenient but also less required permissions for the connection string configured in the “source” table in the OHDSI schema.
The core question at hand is where the migrations should be validated for a given source. We have a couple of choices:
- When WebAPI starts, iterate through all sources and validate() the migration for each source (that has a results daimon). If any fail we can either A: mark the source as disabled and continue, or B: stop webapi from starting
- When a service call is made that uses a source daimon with results, you validate() the migration at that time. We could do it once per invocation, or do it once and cache the result so you only validate the first time.
- Other options?
For those environments where they want to have limited access to the flyway account, a command-line interface to migrating the results schema would need to be applied manually.
Note: Frank is not describing explicitly if the credentials the results flyway migration uses the credentials configured in the SOURCE table or not. I’d recommend NOT using the credentials in the SORUCE table (that credential should just be for simple CRUD operations supporting the analysis tasks), rather when a migration / validation is checked, the connection string of the source should be used, but the flyway credentials of the username/password should be set based on the flyway configuration. That way, all results schema are granted the same flyway account permissions to do their work. Note on this: the current source table makes you put the user/password credentials in the connection string, this should probably be refactored to only include the database connection info and not the user context, and new columns added for username/password. This would also let us encrypt credentials separately from the connection string (but that’s another topic entirely).