Evolutions always drop down tables

[Maurizio Margiotta]

I'm trying to use Evolutions and Ebeam to manage my MySql database, but every time I change something on my models Evolutions generates a script that drops down all my tables and recreate them.

In the documentation is written:

In developement mode however it is often simpler to simply trash your developement database and reapply all evolutions from the beginning. 

But I don't agree with this. To develop and test an application I need data inside my database.

I suppose that in production the behavior is different, so, is it possible to update the database structure in DEV mode without to drop all my tables?

Thanks

[biesior]

NO IT'S NOT DIFFERENT!

(sorry for screeming, but it's important :) ).

I agree with you, it's uncomfortable, however Ebean doesn't support updating DDL, therefore every change causes recreation of the whole table.

In dev you can use /conf/initial-data.yml file to ensure that you have set of initial data for testing (they are included in app/Global.java - see it in for an example zentasks sample app)

In prod you need to switch to manual writing and implementing your evolutions containing ALTERs instead of DROPs and CREATEs
To make it possible remove two commented lines from /conf/evolutions/default/1.sql and write your own code there, next create 2.sql, 3.sql for next alters.

[Maurizio Margiotta]

Hi biesior,

thank you for you answer, but this is really a bad news!

Often I import my production database on development, in this way I can see a more consistent result of my changes.

Plus, to release an update in this way is very slow and there is high risk of errors touching the DB.

Before to use Play, I was using PHP and MySql. I had to change my DB manually in development. But in this way I could test my changes before to reapply them on production.

So what should I use instead of ebeam to have a better support for evolutions? JPA?

Thanks.

[biesior]

Maurizio,

This is good question, but still have no answer: https://groups.google.com/forum/?fromgroups#!topic/play-framework/z4ZYT36kh1M

De facto I'm not sure if Play supports DDL creation other than Ebean. (but notice I can be wrong, as I didn't try it). So it looks like using Ebean and writing custom evolutions is still best idea now.

I was thinking a lot about this, but actually I'am active PHP dev with lot of tasks, and didn't find additional time to finding solutions. Play's project I'm working on is small enough that writing evolutions is just faster than searching the better solution. Buuut (there is always some 'but') it is good idea to discuss it and hopefully find the correct answer. At last this task doesn't require modification of Play's sources as it can be provided as ie. plugin or module.

My last thought was creating full DDL the same way as current EbeanPlugin does (but of course not in the evolutions folder) and then use some external tool like ie. MyBatis Schema Migrations to create proper evolutions. just thoughts, even had no time to consider if the idea is proper :) 

[Guillaume Bort]

Hibernate can manage evolutions for you, including incremental update. But it is not 100% correct, so most serious production deployment will manage database evolutions by hand anyway. I mean, this is your production database, you don't want to have a generated ALTER TABLE to delete a column of your production database because you renamed a java field. 

My advice: start using ebean generated DDL to bootstrap your project, and once you have a production version online, continue managing database evolutions scripts by hand. This is the only way to have something serious. 

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To view this discussion on the web visit https://groups.google.com/d/msg/play-framework/-/RgN1oVYYalsJ.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.

[biesior]

Guillaume,

It's hard to disagree your points. I do not blame Play for the behaviour as it's Ebean's part. What's more most of ORM's I was working with, are pretty similar - changing model's/property's name can be painful.

But in such case I have other suggestion: currently Play's evolutions with enabled DDL generation suggests re-applying the 1.sql evolution which also performs DOWNS by the way and in the effect it creates dangerous possibility to destroy data by the accident. One stupid mouse click in the middle of the night and developer is about to going to suicide himself ;) I know that's not possible to find golden mean without writing custom schema migration tool (and I know that's not a Play's job) however maybe it would be better if Play could prevent accidental data loosing. 

My second idea is change of the Play's EbeanPlugin class. Instead creating the only one 1.sql in evolutions folder it could create full DDL in separate folder which is not auto-assumed as new evolution. Remember that if auto DDL generated is enabled there is no way to use custom evolutions and vice versa.

There could be separate file for each change  (ie. named with current time marker), and to apply this, developer would need to copy its content and paste into his own 'manual' evolution (or prepare proper ALTERS, comparing with previous DDL, manually or with 3-rd party tool).

This solution will be easiest to implement and additionally it fits two goals: enabling coexisting the manual evolutions with auto DDLs + preventing accidents.

This is suggestion to Play's dev team, all I can do here is writing documentation describing the concept, but I think it's worth of considering.

[Maurizio Margiotta]

I agree that in production is dangerous to use any automatic update system. This is the reason why an automated system should be applied first of all on development. In fact that is the right moment to check the ALTER query and change it before to be applied on DEV. Then on production you are safe.

The best solution for me is to generate an sql file for every model update, propose to the developer to check it and, eventually, to change it, than to apply on DEV. If something get wrong, execute the reverse queries and restart the procedure. 

At the end you will have the right sql to execute on Production.

MyBatis Migration is really interesting, and could be used from an external plugin that, based on Java Reflection, could introspect the models and generate ALTER queries.

What do you think?

I'd like to start to develop it, but I'm new to Java. Any help will be really appreciated.