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.