[Mailman-Developers] Bug 971013 - schema migrations BLOCKED

[Stephen J. Turnbull]

Barry Warsaw writes:

 > What's blocking progress is SQLite, [which] 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.

So it's the renaming that causes problems, and the issue with renaming
(ie, why SQLite doesn't support it) is that not only do you change the
name of the column, but you need to fix up all references in
relations, is that it?

I still don't see why this is conceptually hard.  Since you're working
with schema changes, you have a list of renamings oldname -> newname,
and row data in the tables whose values do not depend on variable
names.  Then you create a new database (ie, empty tables) with the new
schema, you extend the list of renames with trivial renames (oldname
-> oldname), and you copy the rows in each table from (*oldnames) ->
(*newnames).  What am I missing?

Is there an implicit requirement that this be done quickly? in minimum
space? in place?  Aren't all of those YAGNIs (as David points out more
briefly)?  Except of course for you, since you are testing the
migrations. ;-)  In fact, I would be tempted to say "keep the old
database around, in case you need to reverse migrate" (and for you, it
would allow post-mortems and in general cross-checking of the
correctness of migrations since you have both old and new databases).

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

Two problems I can foresee.  First, name collisions: it's quite
possible you'll add a new column with somewhat different semantics
(and therefore different values) and want to give it the name attached
to a previously deleted column.  Easy to workaround if you're doing
things by hand, but an enthusiasm-sapper for you (wearing your DB
developer hat) because I suspect the only thing an auto-migrate script
can do is fatal here.

Second, if the deleted column is involved in relationships, these
relationships will go unmaintained in the new schema.  I don't know if
this would result in an actual bug, but it's unclean.