[Buildbot] #2590: Use Alembic instead of SQLAlchemy-Migrate

[Buildbot trac]

#2590: Use Alembic instead of SQLAlchemy-Migrate
-------------------------+--------------------
Reporter:  dustin        |       Owner:
    Type:  project-idea  |      Status:  new
Priority:  major         |   Milestone:  0.9.+
 Version:                |  Resolution:
Keywords:  database      |
-------------------------+--------------------

Comment (by stibbons):

 Hi.

 I work on it with Pierre on cactus.

 Several side of the story:
 1. Is Alembic the best choice? Even if it really useful for us, it is an
 external program you will have to setup everywhere buildbot is installed.
 I clearly dislike this (the buildbot package should be self containing).
 2. Migration point: does buildbot keep the old "migration" script, to
 support old database migration, or do we start from scratch?
 3. Alembic comes with the autogenerate feature that is able, up to a
 **certain** point, to find out the difference between the model described
 in your python files (so, with you new development) and the current
 database state. update and downgrade functions are automatically
 generated. But it is able to only find some classical differences, you
 still need to write code for complex data migration,...
 4. But at the end, I usually just write the direct SQL statements to
 execute. It easy to do on our system since we have the control of our
 database, but this may be quite difficult to validate against the large
 variety of database.
 5. On the numbering problem, the issue will be a bit easier. We use <iso
 date>-description.py as naming scheme for file, it is very powerful. The
 real thing that "compute" the dependencies between script is the revision
 / down_revision reference each script has. So it should be easy to insert
 a script in between two existing script with minor modification.

 I have some questions:
 A. what is the use of having a dedicated command to update the database
 (upgrade-master). For me it is useless, should be done automatically at
 startup, every time. The only advantage to have a different command, is if
 the user that performs the SQL structure alteration is different that the
 user that uses the database.
 B. How can the user lose data? user should never loose data. Never.
 Upgrade cannot fail since the database is managed by buildbot and only it.
 Alembic should not add more "security" that alchemy-migrate. Review and
 heavy unit tests should ensure migration scripts are fully validated.
 Alembic has a rollback mechanism, but I can't figure out how to guarantee
 it.

--
Ticket URL: <http://trac.buildbot.net/ticket/2590#comment:5>
Buildbot <http://buildbot.net/>
Buildbot: build/test automation