SQLite Forum

full ALTER TABLE support?

full ALTER TABLE support?

(1.1) By tom (younique) on 2020-04-23 18:14:24 edited from 1.0 [source]

Will there be a (more) complete support for ALTER TABLE in the near future? Especially DROP COLUMN and ALTER COLUMN would be very convenient features.

(I know the procedure to manually achieve these kind of changes, but it's a 12-step procedure which ain't trivial at all, especially when more than one index, trigger, or view is involved.)


I'm not asking just because to bother someone, it's got substantial background:

I have an application where the database schema changes every now and then, depending on new features being added. The database files are then being upgraded according to the 12-step "ALTER TABLE recipe". I can handle that, it's annoying, but feasible.

However, users add their own views to make their handling with their database files match their needs. And of course, the views take their data from some source tables. Now, when I want to change a table to which a user-defined view points to, the schema upgrade fails. This is because I only drop and recreate all standard views.

For a fully compliant upgrade procedure, I would have to scan all views and triggers whether they refer to one of the tables I want to upgrade. And I'd have to do it recursively, because view A might get its data from view B which refers to table C. This is very hard to accomplish!

I think the experts on internal table-change-handling are the SQLite-team, not me :) So it would be a great benefit to have ALTER TABLE ALTER COLUMN and ALTER TABLE DROP COLUMN support. The latter would be enough at first, because one could then replace ALTER COLUMN with a sequence of ADD COLUMN, DROP COLUMN, RENAME COLUMN - which is not elegant, but anyway easier than the current cookbook.