On 27 Oct 2019, at 7:43pm, Richard Damon <rich...@damon-family.org> wrote:

> My one thought about fuller ALTER TABLE support would be that since
> SQLite stores the schema as the simple CREATE TABLE command, that ALTER
> TABLE needs to know how to edit that string to match the changes, where
> simple adds are much easier than other forms of editing.

There are two ways to do it.  One is to try to edit the original CREATE TABLE 
command.  The other is to make the change, then write a new CREATE TABLE 
command from the internal record SQLite maintains of the columns of that table.

The second method would be easier to make crash-free, and far easier to debug.  
It would lose formatting and comments from the original command.  But I'm cool 
with that.

The other problem is deleted data.  Suppose you had a table with five columns 
and someone dropped the third column.  First, you need to check that your 
entire schema, including triggers, to make sure nothing refers to that column.  
I don't know how difficult that would be given how SQLite works.

Then you need to deal with the values that had been stored in that column.  
Again, there are two ways to handle it.  One is to rewrite each row of the 
table without the third value, which would be slow and incorporate a ton of 
I/O.  The other is to just note that column 3 must be ignored.  (Change its 
name and give it affinity IGNORE_ME ?)  That would be extremely fast.  If the 
programmer cares about filespace they can do their own VACUUM at a convenient 
time.

Other ALTER TABLE commands have their own problems.  I don't see anything that 
can't be done, but some of them might be difficult.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to