[Mailman-Developers] Bug 971013 - schema migrations BLOCKED

[Barry Warsaw]

I've been working on and off for quite some time on bug 971013

https://bugs.launchpad.net/mailman/+bug/971013

This bug is about providing orderly schema migrations.  I now admit that I'm
blocked and I'm looking for suggestions.

As you know, I made a promise that after beta 1, we'd only change the schema
if we can provide automatic migrations.  Or IOW, I won't make incompatible
schema changes that break existing mm3 installations.

We had some discussion on this list a while ago about mechanisms to do this,
and trunk has some rudimentary support for it.  In the meantime, I found out
about a nice tool called Alembic

http://alembic.readthedocs.org/en/latest/index.html

which provides a better framework for doing migrations.  Even though it's
primarily geared toward SQLAlchemy, I've had some off-line discussions with
Alembic's author Michael Bayer about how we could utilize Alembic without
pulling in all of SQLAlchemy[1].  I experimented with a branch and it looked
promising, but that's not what's blocking progress on this.

What's blocking progress is SQLite, as described in this FAQ:

http://sqlite.org/faq.html#q11

tl;dr: SQLite has a very limited ALTER TABLE command, and specifically does
not support column renaming or deleting.  This really sucks for implementing
any kind of schema migration, regardless of the framework.  The little example
shown in the FAQ doesn't really scale well when you have lots of relationships
between the tables, as is the case for the `mailinglist` table as currently
defined.  Here's where I am blocked.

There are a couple of options, none of which I like very much.

 - I could break the beta1 schema migration promise.  I'd just make the schema
   changes and all your existing systems would break (all three of them
   <wink>).  As unfortunate as that is, going this route is really a cop out
   because as soon as 3.0 final is out, we can almost guarantee there will be
   changes in 3.1 so we'll have to face this issue eventually.

 - We could drop support for SQLite.  The other database we (semi-)officially
   support is Postgres (still waiting on a MySQL branch), which has a
   sufficiently powerful ALTER TABLE for our needs.  Some people would claim
   that SQLite isn't really a viable database anyway, since it's prone to
   deadlocks in multithreaded/processing applications such as mm3.  While
   true, especially for large, busy sites, I think SQLite can work for smaller
   sites, and it does make installation much easier since SQLite support comes
   with Python.  It certainly makes testing much easier.

 - As has been suggested before, we could convert the mailinglist table to
   key/value pairs, which is actually how the pendings table works.  This
   might be a good idea *anyway* to better support extensions, but of course
   I'd have to break the beta1 promise (just this once <wink>) to make this
   change, and it wouldn't help for any other table.  I think it would also
   make queries trickier, and heck if we go that route why even use a
   relational database?

 - We could try to only add columns in SQLite and try to smack the migration
   scripts into only copying data from the old columns to the new ones.  This
   might actually be the most feasible option, but it does leave column
   detritus in the SQLite database.  Who cares though, right?  There may be
   other problems with this approach I'm not yet aware of.

I'm sure there are things I've overlooked, so I welcome your comments and
feedback.  I'd love to break the logjam on this bug so that we can get moving
again toward the 3.0 final release.

Cheers,
-Barry

FTR, the two existing WIP branches:

https://code.launchpad.net/~barry/mailman/bug-971013
https://code.launchpad.net/~barry/mailman/alembic

[1] Though, if Storm never gets ported to Python 3, I might just re-evaluate
the decision to use it as the ORM after mm3.0.  SQLAlchemy supports Python 3.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/mailman-developers/attachments/20120706/62f951e0/attachment-0001.pgp>