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@aasv.org (via mobile)
Barry Warsaw barry@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@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