[Mailman-Developers] Bug 971013 - schema migrations BLOCKED

David H. Brown dave at aasv.org
Sat Jul 7 04:30:48 CEST 2012


It would be slow and consume disk, but couldn´t you copy all the data to a temporary table, delete the entire sqlite table with the old column names, create a new table with the correct column names, copy the data back, and delete the temporary table? Not like you're doing this every hour.
--
David Brown, dave at aasv.org (via mobile)

Barry Warsaw <barry at list.org> wrote:

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.
_____________________________________________

Mailman-Developers mailing list
Mailman-Developers at python.org
http://mail.python.org/mailman/listinfo/mailman-developers
Mailman FAQ: http://wiki.list.org/x/AgA3
Searchable Archives: http://www.mail-archive.com/mailman-developers%40python.org/
Unsubscribe: http://mail.python.org/mailman/options/mailman-developers/dave%40aasv.org

Security Policy: http://wiki.list.org/x/QIA9



More information about the Mailman-Developers mailing list