Squash and fix migrations so they run against an empty schema #24

ansv7779 · 2024-11-22T22:24:20+01:00

ansv7779 commented

2024-11-22 22:24:20 +01:00

This is one requirement in bringing #15 to reality.

Currently there are some 450 migration scripts that have been added over the past 11 years. Unfortunately some of these migration scripts have some defects. Either from the fact that they are very old and from another database engine (MySQL vs currently MariaDB), make assumptions about the database name, or its contents. Due to these defects trying to bring an empty schema up-to-date by running all migrations will fail with 372 being the main blocker.

If it is not possible to bring an empty schema up-to-date it is a major hindrance to the plan of automatically deploying test servers for every pull request (#15). These changes makes it possible to bring an empty schema up to the latest version by squashing all migration scripts to a single new baseline with the necessary fixes to work on an empty schema.

There is a downside with the way it accomplishes this, it requires any non-empty schema to already be at version 392.2. Flyway, the product we use for database migrations, does not support new baseline scripts in the free version, only in the paid edition. To get around this, Flyway is tricked into thinking the database has never used Flyway before by changing which database table stores the information about applied migrations. This is the reason the database has to be at the latest (392.2) version before deploying the new version of SciPro that include this change, because Flyway will have no way to see which of the old migrations have been applied.

An alternative would be to fix the old migrations so they would work on an empty schema. However, since every migration script is checksummed to see that the applied version is the correct one every database would have to be "repaired" to update its checksums. This choice was not taken for two reasons:

It would require manual work in the database before deploying the new version of SciPro with the fixed migrations, similar to the requirement to first deploy the version of SciPro that includes the 392.2 migration.
Running all the migrations taken a lot of time, especially the new 391. Squashing all migrations avoid this and makes spinning up new databases very quick

How to test with an existing schema

Deploy commit ff4c5b58b40db5fcb7754c259c3854194668c1e1 (current develop branch as of 2024-11-22)
Start the system to apply migrations up to and including 392.2
Switch to this branch
Start the system and see that the database will be considered baselined at version 2
Click around in the system and see that it still works

How to test with an empty schema

Empty your database schema
Switch to this branch
Deploy the system
See that it migrates the schema and creates all the necessary tables
Log in as admin@example.com that is created by the DataInitializer

This is one requirement in bringing #15 to reality. Currently there are some 450 migration scripts that have been added over the past 11 years. Unfortunately some of these migration scripts have some defects. Either from the fact that they are very old and from another database engine (MySQL vs currently MariaDB), make assumptions about the database name, or its contents. Due to these defects trying to bring an empty schema up-to-date by running all migrations will fail with [372](https://gitea.dsv.su.se/DMC/scipro/src/commit/ff4c5b58b40db5fcb7754c259c3854194668c1e1/core/src/main/resources/db/migration/V372__update_and_insert_grading_criterion_template_master.sql) being the main blocker. If it is not possible to bring an empty schema up-to-date it is a major hindrance to the plan of automatically deploying test servers for every pull request (#15). These changes makes it possible to bring an empty schema up to the latest version by squashing all migration scripts to a single new baseline with the necessary fixes to work on an empty schema. There is a downside with the way it accomplishes this, it requires any non-empty schema to already be at version [392.2](https://gitea.dsv.su.se/DMC/scipro/src/commit/ff4c5b58b40db5fcb7754c259c3854194668c1e1/core/src/main/resources/db/migration/V392_2__reflection_comment_by_supervisor.sql). [Flyway](https://www.red-gate.com/products/flyway/), the product we use for database migrations, does not support new baseline scripts in the free version, only in the paid edition. To get around this, Flyway is tricked into thinking the database has never used Flyway before by changing which database table stores the information about applied migrations. This is the reason the database has to be at the latest (392.2) version before deploying the new version of SciPro that include this change, because Flyway will have no way to see which of the old migrations have been applied. An alternative would be to fix the old migrations so they would work on an empty schema. However, since every migration script is checksummed to see that the applied version is the correct one every database would have to be ["repaired"](https://documentation.red-gate.com/fd/repair-184127461.html) to update its checksums. This choice was not taken for two reasons: * It would require manual work in the database before deploying the new version of SciPro with the fixed migrations, similar to the requirement to first deploy the version of SciPro that includes the 392.2 migration. * Running all the migrations taken a lot of time, especially the new [391](https://gitea.dsv.su.se/DMC/scipro/src/commit/ff4c5b58b40db5fcb7754c259c3854194668c1e1/core/src/main/resources/db/migration/V391__harmonize_table_attribute_name.sql). Squashing all migrations avoid this and makes spinning up new databases very quick ## How to test with an existing schema 1. Deploy commit [ff4c5b58b40db5fcb7754c259c3854194668c1e1](https://gitea.dsv.su.se/DMC/scipro/commit/ff4c5b58b40db5fcb7754c259c3854194668c1e1) (current `develop` branch as of 2024-11-22) 2. Start the system to apply migrations up to and including 392.2 3. Switch to this branch 4. Start the system and see that the database will be considered baselined at version 2 5. Click around in the system and see that it still works ## How to test with an empty schema 1. Empty your database schema 2. Switch to this branch 3. Deploy the system 4. See that it migrates the schema and creates all the necessary tables 5. Log in as `admin@example.com` that is created by the `DataInitializer`

ansv7779 added 2 commits 2024-11-22 22:24:21 +01:00

Do not create duplicate templates 5476fc82ea

Was caused by a refactor that removed the credits used to differentiate project types while master and one-year master were the same. Now it is different types and the credits were no longer needed and got removed.

Squash all database migrations

Build and test / build-and-test (push) Successful in 6m38s

Details

6aa06ecc1d

There are migrations that do not work on an empty database (372) and many others that give deprecated warnings that will stop working in the future.

This change runs all the migrations (with a fixed 372) and then takes a dump of the result and uses that as a new baseline migration for Flyway. Baselines are meant for existing databases where Flyway is introduced so to fool Flyway into baselining we change the table Flyway uses from the old schema_version to the new flyway_schema_history (default).

The new baseline has no warnings and no invalid migrations and can be run on both empty databases and existing ones.

While Flyway has support for this type of a new baseline (or state scripts), that is only available in the paid editions and not the community version.

ansv7779 requested review from niat8586 2024-11-28 18:08:58 +01:00

ansv7779 requested review from tozh4728 2024-11-28 18:08:59 +01:00

ansv7779 added 1 commit 2024-11-28 18:13:14 +01:00

Merge branch 'develop' into empty-database-migration

Build and test / build-and-test (push) Successful in 12m41s

Details

6adf26e9fa

tozh4728 approved these changes 2024-12-02 10:31:00 +01:00

tozh4728 merged commit a2330ce2d5 into develop

2024-12-02 10:31:21 +01:00

tozh4728 deleted branch empty-database-migration

2024-12-02 10:31:21 +01:00