Differences

This shows you the differences between two versions of the page.

--- development:database_migration_management [2015/04/23 20:25] – [Background] lee
+++ development:database_migration_management [2015/04/24 13:18] (current) – [How the OHDSI WebAPI services use Flyway] lee
@@ Line 8: / Line 8: @@
 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 advantages of this approach are:
+  * It provides a timestamped, checksum verified audit trail of all applied database schema changes
+  * All database schema changes are performed via SQL DDL scripts that are version controlled
+  * Initial creation of tables and all subsequent database migrations are automatically executed dynamically in new and existing databases
 ==== The basics of how Flyway works ====
-The Flyway data migration framework will execute database migration scripts that are provided in a known directory (or sub-directory) in the source code tree and each script name must begin with a database migration version number.  Each script is a SQL source code file containing Database Definition Language (DDL) statements.
+The Flyway data migration framework will execute database migration scripts that are provided in a known directory (or sub-directory) in the source code tree and each script name must begin with a database migration version number.
+Each database migration script is a SQL source code file containing only the set of Database Definition Language (DDL) changes that need to be applied to the existing database schema in order to migrate it to the latest version.
 The scripts are executed in the order of the database migration version number in the script names.
@@ Line 24: / Line 31: @@
 ==== 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.
-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 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 on each subset 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.
+When the Flyway "out of order execution" option is enabled, it means that newly created scripts will also be run (the first time flyway encounters them) even if their version numbers are lower than the highest previously executed version number.
+For the OHDSI WebAPI having this option enabled gives us the flexibility for one developer to e.g. add a new database migration script V1.0.0.1.1_heracles_schema_migration.sql and it will be executed one time, even if the last previous version change was V1.0.0.4_circe_schema_migration.sql. i.e. It allows each developer to flexibly & independently migrate their database tables for the set of WebAPI services they are responsible for, without schema change conflicts, as long as those sets of tables are distinct from the tables used by other WebAPI services.
 ==== 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 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.
@@ Line 36: / Line 47: @@
 ==== Create idempotent database migration scripts ====
 If a script fails half way through then you don't want to have to manually drop the objects that were created.
-This can be avoided by including DDL statements to drop objects if they already exist.  That way a script will clean-up previously created objects automatically when it is re-run.
+This can be avoided by including DDL statements to drop objects if they already exist.  That way a script will clean-up previously created objects automatically if it is re-run.
 ==== Manually rolling back a Flyway migration ====
 It is not encouraged but it is possible to edit a database migration script and re-run it.  You would need to manually delete the row containing the record of the previous execution of the script from the SCHEMA_VERSION table (and if necessary also drop any database objects that had been created).  Care must be taken not to delete any other rows from the SCHEMA_VERSION table.