On 25 Dec 2015, at 2:24am, John McKown <john.archie.mckown at gmail.com> wrote:
> DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN > column_file_name; and would be a very nice addition. I hadn't noticed that > it is missing. I wonder why. SQlite stores all the data for a row together in column order. col1, col2, col3, col4 ... If someone dropped column 3 then there are two ways to handle this internally: A) Make a note that col3 no longer exists by changing its name to something unusable and changing its affinity to "DELETED". Existing table data stays the way it is. New rows inserted into that table get a NULL value in that column. Extremely fast but the database now takes up more space than it needs to. B) Do something like the above but then immediately VACUUM that table. No longer extremely fast but now the database file is smaller. Doing this would involve writing code which would implement a selective form of the VACUUM command: VACUUM [schema.]tablename which VACUUMs just that one table. Which might be another good reason to go this way since that could be useful just by itself. C) Choose to do either (A) or (B) depending on a PRAGMA setting. Or maybe just look at the setting of PRAGMA auto_vacuum. The above isn't hard to do. What makes "ALTER TABLE table-name DROP ?COLUMN" hard is checking the schema to make sure that nothing in the schema refers to the dropped column. That column you dropped might be in a CHECK constraint, or a FOREIGN KEY or an index or probably a bunch of things not coming to mind right now. Figuring that out requires SQLite to parse the master table down to the level of column names. That's difficult. Simon.
