On Dec 24, 2015, at 8:26 AM, Bernardo Sulzbach <mafagafogigante at gmail.com> wrote: > > I don't > know if alter table is used at all in production anywhere (why would > it be? the column names and ordering should not be part of the data).
Requirements change. In the past dozen years, the database schema I?m working on right now has changed about a hundred times. Many of those changes were batched, so that there are ?only? about 40 separate schema versions. But, about a third of those involved changes that SQLite doesn?t support directly, but other SQL DBMSes do. SQLite currently only supports adding new features. (ADD COLUMN, CREATE TABLE.) It doesn?t deal with mutating features (MODIFY/CHANGE COLUMN) or removed features (DROP COLUMN) nearly as well. SQLite?s nearly typeless nature does allow you to paper over many changes that would require an ALTER TABLE in another DBMS. (e.g. extension of an 8 bit integer column to 32 bits, or changing an integer column to a string column) But, some application level changes do still require a bunch of code at the SQLite layer that would be a one-liner in other DBMSes. > In the > end, if you spent enough time in the design phase to prepare all your > schemas, you should not have to drop (or alter) any of the tables at > all. What you?re describing is the old waterfall development dream, where all we need to do is spend more time in the design phase, and we?ll produce perfect software on time, every time. The industry ran on that mantra for decades before the agile movement coalesced, finally providing a better set of coherent philosophies. ALTER TABLE is agile. To the extent that agile is good, stronger ALTER TABLE support is good, too. I am not arguing for an abandonment of up-front design. The software that uses the DBMS I describe above changed tens of thousands of times over that same span, so the fact that the DBMS only had to change about 100 times is a testament to good up front design. Yet, changes still occur, so it?s best if we don?t have to jump through hoops when that happens.
