This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
development:database_migration_management [2015/04/23 20:21] lee [How the OHDSI WebAPI services use Flyway] |
development:database_migration_management [2015/04/23 20:30] lee [How the OHDSI WebAPI services use Flyway] |
||
---|---|---|---|
Line 2: | Line 2: | ||
- | The OHDSI WebAPI services executable creates and maintains some read/write tables in the OHDSI database schema for services that need to persist data. These tables are not part of the Common Data Model. They are used by the OHDSI WebAPI services to persist data such as analytical results, cohort definitions and operational data such as batch job execution statuses. | + | The OHDSI WebAPI services executable creates and maintains some read/write tables in the OHDSI database schema for services that need to persist data. These tables are not part of the Common Data Model. They are used by the OHDSI WebAPI services to save data such as analytical results, cohort definitions and operational data such as batch job execution statuses. |
- | The OHDSI WebAPI may migrate some of these tables (and other database objects like sequences) in the OHDSI schema to a later version when new versions of the WebAPI services are released that require database table changes. | + | The OHDSI WebAPI executable may migrate some of these tables (and other database objects like sequences) in the OHDSI schema to later versions when new versions of the WebAPI services are released that require database table changes. |
The OHDSI WebAPI executable has a dependency on a java framework called "Flyway" to manage the execution of the database schema migration scripts that perform the database migrations. | The OHDSI WebAPI executable has a dependency on a java framework called "Flyway" to manage the execution of the database schema migration scripts that perform the database migrations. | ||
Line 24: | Line 24: | ||
==== How the OHDSI WebAPI services use Flyway ==== | ==== How the OHDSI WebAPI services use Flyway ==== | ||
- | The WebAPI database migration scripts are created in the sub-folders of the https://github.com/OHDSI/WebAPI/tree/master/src/main/resources/db/migration folder. One database migration script must be placed in each subfolde. There is one subfolder for each DBMS. Each DBMS script must have valid SQL in the SQL dialect of that DBMS. e.g. Postgresql SQL in the migration script under the Postgresql sub-directory. | + | The WebAPI database migration scripts are created in the sub-folders of the https://github.com/OHDSI/WebAPI/tree/master/src/main/resources/db/migration folder. A version of the database migration script must be placed in each subfolder. There is one subfolder for each DBMS. Each DBMS script must have valid SQL in the SQL dialect of that DBMS. e.g. Postgresql SQL in the migration script under the Postgresql sub-directory. |
- | The database migration scripts file names are prefixed with "Vn.n.n.n_" where n.n.n.n is the numeric version number. e.g. V1.0.0.0. There is a separate version number assigned to each set of WebAPI services. Use the next available higher unused version number if you have a new set of WebAPI services. Additional database migration changes for the same set of WebAPI services would use a sub version (with an additional .n' on the version suffix. | + | The database migration scripts file names are prefixed with "Vn.n.n.n_" where n.n.n.n is the numeric version number. e.g. V1.0.0.0. There is a separate version number assigned to each set of WebAPI services. Use the next available higher unused version number if you have a new set of WebAPI services. Additional database migration changes for the same set of WebAPI services would use a sub version (with an additional '.n' on the version suffix. |
e.g. The next heracles related database migration script after V1.0.0.1_heracles_schema_migation.sql would be called V1.0.0.1.1_heracles_schema_migration.sql. | e.g. The next heracles related database migration script after V1.0.0.1_heracles_schema_migation.sql would be called V1.0.0.1.1_heracles_schema_migration.sql. | ||
- | OHDSI has the "out of order execution" flyway option enabled so that the rules around database script execution order are relaxed. This provides needed flexibility when we have typically one developer assigned to each set of WebAPI services that are working independently and avoids the more complex alternatives of requiring separate forks for WebAPI database migrations. | + | Note. The OHDSI WebAPI build has the Flyway "out of order execution" option enabled so that the rules around database script execution order are relaxed. This provides needed flexibility when we have typically one developer working per set of WebAPI services. Since we want to enable each developer to be able to work independently in parallel, this approach avoids the more complex alternative of requiring separate forks for WebAPI database migrations. |
==== Testing Flyway migration scripts prior to a pull request ==== | ==== Testing Flyway migration scripts prior to a pull request ==== | ||
- | Ideally you will need access to a local test environment with all three DBMS systems - SQL Server, Postgresql and Oracle. Build a copy of the WebAPI locally with which ever database profile you are most familiar with using. Flyway will then automatically execute the database migration script according to your maven pom settings and you will see the tables are automatically created in your database. You can then test the database scripts for the other DBMSs by manually running them in a matching DBMS. | + | Ideally you will need access to a local test environment with all three DBMS systems - SQL Server, Postgresql and Oracle. Build a copy of the WebAPI locally with whichever database profile you are most familiar with using. Flyway will then automatically execute the database migration script according to your maven build settings and you will see the tables are automatically created in your database. You can then manually test the database scripts for the other DBMSs by manually running them in a matching local DBMS to ensure they execute as expected. |
==== Create idempotent database migration scripts ==== | ==== Create idempotent database migration scripts ==== |