Uploaded image for project: 'Isis'
  1. Isis
  2. ISIS-1471

Integrate Flyway or Liquibase or something similar

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.13.0
    • Fix Version/s: 1.13.2
    • Component/s: Core
    • Labels:
      None

      Description

      from Kambiz:

      Hi,

      in our non-Isis projects, we use FlyWay [1] for DB migrations and I
      would like to integrate it into our Isis workflow. The simplest path to
      do so would be a DomainService with a PostConstruct annotated init
      method:

      @PostConstruct
      public void init(final Map<String, String> properties) {
      Flyway flyway = new Flyway();

      // Point it to the database
      String jdbcUrl = properties.get("isis.persistor.datanucleus.impl.javax.jdo.option.ConnectionURL");
      String user = properties.get("isis.persistor.datanucleus.impl.javax.jdo.option.ConnectionUserName");
      String password = properties.get("isis.persistor.datanucleus.impl.javax.jdo.option.ConnectionPassword");

      flyway.setDataSource(jdbcUrl, user, password);
      flyway.setLocations("classpath:db/migrations");
      // Start the migration
      flyway.migrate();
      }

      but this isn't a viable solution, as IsisSessionFactoryBuilder's
      buildSessionFactory() method initialises the DataNucleus (DN)
      PersistenceSessionFactory before the services are constructed [2].

      So DN has already found the mismatch between the JDO annotations and the
      database before we enter the init method of our DB migration
      bootstrap/seed service.

      I could contribute a patch, if someone could hint on the preferred way
      of implementing the functionality.

      Thank you

      Kambiz

      [1] https://flywaydb.org/

      [2] https://github.com/apache/isis/blob/master/core/runtime/src/main/java/org/apache/isis/core/runtime/system/session/IsisSessionFactoryBuilder.java#L184

        Activity

        Hide
        danhaywood Dan Haywood added a comment -

        Implemented as an isisaddon, see https://github.com/isisaddons/isis-module-flywaydb

        Show
        danhaywood Dan Haywood added a comment - Implemented as an isisaddon, see https://github.com/isisaddons/isis-module-flywaydb
        Hide
        danhaywood Dan Haywood added a comment -

        should be "per module"

        Show
        danhaywood Dan Haywood added a comment - should be "per module"
        Hide
        danhaywood Dan Haywood added a comment -

        Timothy wrote:

        Hello Kambiz, Hello Jeroen,

        My colleague pointed me to your mails at the mailing list as we were also interested in that topic. In datanucleus documentation see [1] I found a hook that can be used. Please note that I implemented that yesterday, so it is not well tested but the application starts and all integration tests are green.

        In persistor_datanucleus.properties add this:
        isis.persistor.datanucleus.impl.javax.jdo.PersistenceManagerFactoryClass=com.example.FlywayJdoPersistenceManagerFactory

        and turn off the auto generation:
        isis.persistor.datanucleus.impl.datanucleus.schema.autoCreateAll=false

        Also I turned off validation because I had issues with LONGVARBINARY as HSQL replaced it to VARBINARY which datanucleus was not very happy about that
        isis.persistor.datanucleus.impl.datanucleus.schema.validateAll=false
        isis.persistor.datanucleus.impl.datanucleus.schema.validateTables=false
        isis.persistor.datanucleus.impl.datanucleus.schema.validateColumns=false
        isis.persistor.datanucleus.impl.datanucleus.schema.validateConstraints=false

        Here the implementation of the class:

        import org.datanucleus.api.jdo.JDOPersistenceManagerFactory;
        import org.datanucleus.metadata.PersistenceUnitMetaData;
        import org.flywaydb.core.Flyway;

        public class FlywayJdoPersistenceManagerFactory extends JDOPersistenceManagerFactory {

        public FlywayJdoPersistenceManagerFactory()

        { super(); }

        public FlywayJdoPersistenceManagerFactory(final PersistenceUnitMetaData pumd, final Map overrideProps)

        { super(pumd, overrideProps); migrateDatabase(); }

        public FlywayJdoPersistenceManagerFactory(final Map props)

        { super(props); migrateDatabase(); }

        private void migrateDatabase() {

        //String driverName = (String)this.getProperties().get("javax.jdo.option.ConnectionDriverName"); Flyway uses auto detection...
        // String url = (String)this.getProperties().get("javax.jdo.option.ConnectionURL"); don't use, as the propertie names are changed during initialization of superclass
        // String userName = (String)this.getProperties().get("javax.jdo.option.ConnectionUserName");
        // String password = (String)this.getProperties().get("javax.jdo.option.ConnectionPassword");

        try

        { Flyway flyway = new Flyway(); flyway.setDataSource(this.getConnectionURL(), this.getConnectionUserName(), this.getConnectionPassword()); flyway.migrate(); }

        catch (Exception e)

        { e.printStackTrace(); }

        }
        }

        Hope that helps you!

        Regards Timothy

        Show
        danhaywood Dan Haywood added a comment - Timothy wrote: Hello Kambiz, Hello Jeroen, My colleague pointed me to your mails at the mailing list as we were also interested in that topic. In datanucleus documentation see [1] I found a hook that can be used. Please note that I implemented that yesterday, so it is not well tested but the application starts and all integration tests are green. In persistor_datanucleus.properties add this: isis.persistor.datanucleus.impl.javax.jdo.PersistenceManagerFactoryClass=com.example.FlywayJdoPersistenceManagerFactory and turn off the auto generation: isis.persistor.datanucleus.impl.datanucleus.schema.autoCreateAll=false Also I turned off validation because I had issues with LONGVARBINARY as HSQL replaced it to VARBINARY which datanucleus was not very happy about that isis.persistor.datanucleus.impl.datanucleus.schema.validateAll=false isis.persistor.datanucleus.impl.datanucleus.schema.validateTables=false isis.persistor.datanucleus.impl.datanucleus.schema.validateColumns=false isis.persistor.datanucleus.impl.datanucleus.schema.validateConstraints=false Here the implementation of the class: import org.datanucleus.api.jdo.JDOPersistenceManagerFactory; import org.datanucleus.metadata.PersistenceUnitMetaData; import org.flywaydb.core.Flyway; public class FlywayJdoPersistenceManagerFactory extends JDOPersistenceManagerFactory { public FlywayJdoPersistenceManagerFactory() { super(); } public FlywayJdoPersistenceManagerFactory(final PersistenceUnitMetaData pumd, final Map overrideProps) { super(pumd, overrideProps); migrateDatabase(); } public FlywayJdoPersistenceManagerFactory(final Map props) { super(props); migrateDatabase(); } private void migrateDatabase() { //String driverName = (String)this.getProperties().get("javax.jdo.option.ConnectionDriverName"); Flyway uses auto detection... // String url = (String)this.getProperties().get("javax.jdo.option.ConnectionURL"); don't use, as the propertie names are changed during initialization of superclass // String userName = (String)this.getProperties().get("javax.jdo.option.ConnectionUserName"); // String password = (String)this.getProperties().get("javax.jdo.option.ConnectionPassword"); try { Flyway flyway = new Flyway(); flyway.setDataSource(this.getConnectionURL(), this.getConnectionUserName(), this.getConnectionPassword()); flyway.migrate(); } catch (Exception e) { e.printStackTrace(); } } } Hope that helps you! Regards Timothy
        Hide
        danhaywood Dan Haywood added a comment - - edited

        Jeroen wrote:

        Hi Oscar,

        We're dropping the constraints because it makes it easier to handle migrations and were then 100% sure that no retired constraint stays behind. I've not seen the error that you are referring to. We're also running some other Apache Isis applications on PostgreSQL so it might be something else that is causing your issue.

        Cheers,

        Jeroen

        Show
        danhaywood Dan Haywood added a comment - - edited Jeroen wrote: Hi Oscar, We're dropping the constraints because it makes it easier to handle migrations and were then 100% sure that no retired constraint stays behind. I've not seen the error that you are referring to. We're also running some other Apache Isis applications on PostgreSQL so it might be something else that is causing your issue. Cheers, Jeroen
        Hide
        danhaywood Dan Haywood added a comment - - edited

        Oscar wrote:

        Hi, Jeroen.

        Nice to know you drop the constraints when upgrading.

        I’ve also noticed that they get duplicated. Seems DN does not detect they’re already created and re-create them.
        In my case the db is PostgreSQL. As you’re using MS SQL Server in Estatio, seems it can be a generic issue.

        It’s strange DN does not have addressed it.

        Please, confirm me if that’s the issue causing you to delete the constraints and I would create a issue in the DN ticketing system.

        Thanks,

        Oscar

        Show
        danhaywood Dan Haywood added a comment - - edited Oscar wrote: Hi, Jeroen. Nice to know you drop the constraints when upgrading. I’ve also noticed that they get duplicated. Seems DN does not detect they’re already created and re-create them. In my case the db is PostgreSQL. As you’re using MS SQL Server in Estatio, seems it can be a generic issue. It’s strange DN does not have addressed it. Please, confirm me if that’s the issue causing you to delete the constraints and I would create a issue in the DN ticketing system. Thanks, Oscar
        Hide
        danhaywood Dan Haywood added a comment - - edited

        Jeroen wrote:

        Hi Kambiz,

        There's currently not a nice hook that I can think of to execute Flyway
        migrations. I would create a separate "upgrade" mode to start Isis that
        bootstraps with an in-memory db and allows you to do the Flyway stuff. But
        Dan probably has other ideas

        I've looked into Flyway for exactly the same purpose but was not really
        enthousiast about it. What I disliked the most is that you have to maintain
        every single db change in scripts. For me, the domain model is the source
        and persistence should be derived from that. And Datanucleus does an
        excellent job in creating all database artifacts so I want to keep
        leveraging that.

        What we currently do (manually) is roughly this:
        1. stop Isis;
        2. drop all db constraints;
        3. apply db upgrade script (for the changes that cannot be handled by
        Datanucleus);
        4. start Isis;
        5. execute upgrade service (for programmatic changes).

        We are also trying to crack the nut on how to automate this but encounter a
        few hurdles and I am not sure if Flyway can tackle those:

        • we have applications that consist of multiple modules, each with its own
          db schema and that change independently and the application should
          orchestrate the right order of upgrading;
        • a lot of times data is migrated, even between schemas and we sometimes
          use temporary views to do a pre and post check;

        Any ideas are welcome.

        Cheers,

        Jeroen

        Show
        danhaywood Dan Haywood added a comment - - edited Jeroen wrote: Hi Kambiz, There's currently not a nice hook that I can think of to execute Flyway migrations. I would create a separate "upgrade" mode to start Isis that bootstraps with an in-memory db and allows you to do the Flyway stuff. But Dan probably has other ideas I've looked into Flyway for exactly the same purpose but was not really enthousiast about it. What I disliked the most is that you have to maintain every single db change in scripts. For me, the domain model is the source and persistence should be derived from that. And Datanucleus does an excellent job in creating all database artifacts so I want to keep leveraging that. What we currently do (manually) is roughly this: 1. stop Isis; 2. drop all db constraints; 3. apply db upgrade script (for the changes that cannot be handled by Datanucleus); 4. start Isis; 5. execute upgrade service (for programmatic changes). We are also trying to crack the nut on how to automate this but encounter a few hurdles and I am not sure if Flyway can tackle those: we have applications that consist of multiple modules, each with its own db schema and that change independently and the application should orchestrate the right order of upgrading; a lot of times data is migrated, even between schemas and we sometimes use temporary views to do a pre and post check; Any ideas are welcome. Cheers, Jeroen

          People

          • Assignee:
            Unassigned
            Reporter:
            danhaywood Dan Haywood
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development