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