Prototyping FlyWay to Setup the LSA Database

Start presentation

Slide 1: What are we trying to do (1)

Short answer: GSI wants in a scripted way to create an empty LSA schema from scratch that does not have errors or warnings.

Long answer:

We have the following case:

DEV/NEXT/PRO LSA Databases at CERN, Java development at CERN on https://svn.cern.ch/reps/acc-co/trunk
DEV/INT(egration)/PRO LSA Databases at GSI, Java development at GSI on https://www-acc.gsi.de/svn/lsa

The code/features for the part of the LSA project we are collaborating on are synchronized/merged from time to time.
The problem with this approach is that we do not know which database change belongs to which part of a Java code change. On top of that each institute has different versions of the database (DEV/NEXT/PRO).

For each Java feature implemented in the common (collaboration) core, one or more SQL files should exist that contain the related database changes and should be committed together with the Java code to SVN. Ideally split into Schema and ‘Content’ changes.

To help applying/managing the changes we were thinking about database versioning tools like Flyway or Liquibase.

On top of that the LSA Database contains still a large amount of CERN specific tables/trigger/packages that are not needed by the common lsa-core we are collaborating on, and some of them are not even working at GSI (e.g. those referencing the measurement database or some related to timing information)

Since we want a clean database at GSI to start from when we go PRO (no warnings and compile errors), we wanted to separate the “common core” database tables/trigger etc. from the CERN specific ones and cleanup the scripts.

Slide 2: Flyway (1) - Structuring

Flyway is a tool for database versioning ( https://flywaydb.org/, https://flywaydb.org/getstarted/why ).

Important:
Can I make structure changes to the DB outside of Flyway?

Our folder structure for the flyway tests look like this:

migrations
   ├── cern
   ├── common
   └── gsi

Flyway merges all folders that are specified and sorts the scripts by version number.

The idea would be that we specify for inclusion the folders at GSI "common+gsi" and at CERN it is "common+cern".
So the "common" folder would mainly contain "core" schema changes and data migrations while specific content or tables would be in the institute specific folders.

We decided on markers in the script names to describing the content: SC - Schema change, CO - Content Change, PH - Package header, PB - Package Body

Slide 3: Flyway (2) - Java Migrations

What are Java migrations? https://flywaydb.org/documentation/migration/java

This might be usefull to use the existing persisters or even LSA API to bring information into the LSA database.

Slide 4: Flyway (3) - Progress

For testing Flyway our idea was:

• Setup Flyway to create an empty LSA Schema and fix possible problems
• Separate the scripts into "common" and "cern" by moving the ones we think we do not need at GSI from the "common" into the "cern" folder.

We used the database dump SQLs generated by a CERN tool to initially fill the "commons" folder (after doing a lot of dependency parsing and manual sorting). After that we had a Flyway setup that created the whole LSA schema (obviously empty) from scratch, but we had exceptions and warnings.

At this point we were applying 1754 scripts from CERN, then some fixes we need to look into and then GSI specific tables.

After moving out CERN specific scripts we do not have exceptions anymore so the whole database is created, but there are scripts that (for different reasons) generate warnings and then some packages/triggers/views do not compile.

There are approximatly 1100 scripts from CERN executed at the moment, but there are still a lot of tables that we probably do not need in the "common" folder.

Slide 5: Flyway (4) - Basic Folder Structure for our FlyWay Maven Prototyping Project

.
├── pom.xml (generic config)
├── default (applied to all databases contains most of the scripts)
│    └── src
│        └── migrations
│            ├── cern
│            │   └── base (base folders contain everything from dump in July)
│            ├── common
│            │   └── base
│            └── gsi
│                └── base
├── db-dev (for database development and script prototyping)
│   ├── pom.xml (dev specific config)
│   └── src
│       └── migrations
│           ├── cern
│           ├── common
│           └── gsi
├── db-next-int (only applied to NEXT at CERN or INT(egration) at GSI)
│   ├── pom.xml (next/int specific config)
│   └── src
│       └── migrations
│           └── ...
└── db-pro (only applied to PRO database)
    ├── pom.xml (pro specific config)
    └── src
        └── migrations
            └── ...

Slide 6: For The Future

We hope that clarified what we were looking into.

We guess at CERN you would not create a new LSA DB from scratch, but "baseline" your database to tell flyway the current version of the LSA database. It will then apply only scripts that appear after the baselining.

Log output:

• 2016-08-10_FlywayMavenOutputLog.txt: Log before sorting
• 2016-08-18_FlywayMavenOutputLog.txt: Flyway prototyping, maven output log

The next steps would be:

• Look into the problems we saw during applying the scripts
• Figure out baselining of databases and eventually test it at CERN

Slide 7: Problems

• For Oracle DBs FlyWay supports SQLPlus syntax: When we were looking into the warnings one thing we noticed was, that the SQLPlus terminator "/" is missing from SQLs containing multiple scripts or packages.
• Can the sorting of the scripts be optimized during the export of the scripts? Even with the scripts pre sorted by a simple parser the manual work alone took one preson a whole week.
• Transaction problem for Java/Spring migrations using e.g. LSA API
  • Calling the LSA API openes its own DB connection, not using the connection opened by FlyWay. I am sure this could be worked around or Spring even configured to use the connection of the SpringJdbcMigrationclass. But so far no time to look into this.
• Dependencies on other schemas/databases
  • Worst case: Other schema/database is not versioned
  • Complicated: Synchronizing versions between databases if you start scripts from scratch
• Oracle does not support DDL rollback, failed migrations that contain DDL changes need to be rolled back manually (or you restore a backup)

-- RaphaelMueller - 06 Oct 2016