Re: Integrating Flyway for database migrations
Thanks for sharing, Timothy!
This is what I'd do as well.
I've never had good experience with auto-generated DB schemas.
Martin Grigorov
Wicket Training and Consulting
https://twitter.com/mtgrigorov
On Wed, Jun 22, 2016 at 1:10 PM, Simecsek Timothy <
timothy.simec...@nttdata.com> 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
>
> [1] http://www.datanucleus.org/products/datanucleus/jdo/pmf.html
>
> >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
>
> __
> Disclaimer: This email and any attachments are sent in strictest confidence
> for the sole use of the addressee and may contain legally privileged,
> confidential, and proprietary data. If you are not the intended recipient,
> please advise the sender by replying promptly to this email and then delete
> and destroy this email and any attachments without any fur
Integrating Flyway for database migrations
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
[1] http://www.datanucleus.org/products/datanucleus/jdo/pmf.html
>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
__
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.
Re: Integrating Flyway for database migrations
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
> El 21 jun 2016, a las 23:56, Jeroen van der Wal
> escribió:
>
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On 20 June 2016 at 16:27, Kambiz Darabi wrote:
>
>> 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 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
>>
>>
Óscar Bou Bou
Socio - IT & GRC Management Services Director
m: +34 620 267 520
s:
LinkedIn: https://www.linkedin.com/in/oscarbou
Twitter:@oscarbou
Este mensaje y los ficheros anexos son confidenciales. Los mismos contienen
información reservada que no puede ser difundida. Si usted ha recibido este
correo por error, tenga la amabilidad de eliminarlo de su sistema y avisar al
remitente mediante reenvío a su dirección electrónica; no deberá copiar el
mensaje ni divulgar su contenido a ninguna persona.
Su dirección de correo electrónico junto a sus datos personales constan en un
fichero titularidad de GOVERTIS ADVISORY SERVICES, S.L. cuya finalidad es la de
mantener el contacto con Ud. Si quiere saber de qué información disponemos de
Ud., modificarla, y en su caso, cancelarla, puede hacerlo enviando un escrito
al efecto, acompañado de una fotocopia de su D.N.I. a la siguiente dirección:
GOVERTIS ADVISORY SERVICES, S.L. Avda Cortes Valencianas, 58 – 8º - 6ª. 46015 -
Valencia, y Paseo de la Castellana, 153, 28045 - MADRID. Asimismo, es su
res
