Improve migration framework

[Elrond]

We had a problem with two migrations not working nicely on sqlite, because the second one wanted to do an alter table and sqlite doesn't like to do that in a bigger transaction. Long story: Every migration should say "I'm done" and commit. This happened for the existing migrations.

So. In my opinion, the migration framework should run a rollback after each migration "just in case" to clean up any cruft left over from a (possibly broken) migration.

- migration_func(self.session)
+ try:
+   migration_func(self.session)
+ finally:
+   self.session.rollback()

I have NOT TESTED this patch. This is just to show the idea. I want some feedback on it, and possibly someone to test it.

[Elrond]

Hi Chris,

can you please comment on this? You're the master of the migrations, after all!

(hinted by ShawnRisk)

[Christopher Allan Webber]

Elrond:

I trust your judgement here better than I trust my own. It seems like a good idea.

(However, I think if somehow a previous migration broke things and migrations did not stop, that's a bug!)

[Christopher Allan Webber]

Elrond and I discussed on IRC. We agreed on a better way:

try:
  run_migration()
except:
  rollback()
  raise
else:
  commit()

[Elrond]

Okay, setting this for the "after release" milestone.
I don't want to break the upcoming release.

[Elrond]

Chris Webber and I agreed, that we both improve our migration system after the next release. See #685

Yes, postponing this one again. But really, it's not pressingly needed.

[Christopher Allan Webber]

Hi! I'm moving this ticket from "assigned" to in_progress per our new workflow. Please update the ticket and let us know if you're still working on this. If you are, super great! If not, we'll remove the claim and move it back to "accepted" in a couple of weeks.

Thanks!

[Christopher Allan Webber]

I'm bumping this to 0.8.0 partly, but if it doesn't really make it in 0.8.0, the milestone should be removed so as to avoid milestone-bump spam. Additionally, the python 3 branch may deprecate our current sqlalchemy-migrate based migration setup, in which case we can just close this out.

[Christopher Allan Webber]

I'm closing this. We're going to be moving to Alembic soon anyway.

But hey, for documentation, someone posted this useful advice [here ​https://code.google.com/p/sqlalchemy-migrate/issues/detail?id=143]:

   Hi,
   it always fails in the case of Boolean because by default boolean has the  
   Constraints.
   so first you have to remove the Constraints then you can remove the column..
   
   Internaly your table looks like:
   CREATE TABLE creature (
         CHECK (is_demon IN (0, 1)),
            ....
            ....
   
         )
   
   Regards
   Rakesh

So if we need to, we could make use of that advice.