I'm embarrassed to admit that I didn't realize RENAME COLUMN was actually added in 3.25, almost four years ago.
DROP COLUMN was only just added last year in 3.35.
I'm surprised a database schema lasted 9/12 years without ever renaming or dropping a column.
This changes things! But even now, ALTER TABLE is not transactional. So especially with many concurrent readers there can definitely be situations where you'd still want to rewrite.
sqlite> CREATE TABLE foo(a,b,c);
sqlite> INSERT INTO foo VALUES (1,2,3);
sqlite> BEGIN;
sqlite> ALTER TABLE foo DROP COLUMN b;
sqlite> SELECT * FROM foo;
1|3
sqlite> ROLLBACK;
sqlite> SELECT * FROM foo;
1|2|3
I did have a couple of columns that were no longer needed and would have dropped them, but instead I just set them to null and ignored them. Nulls only take 1 byte of space in a row. I dropped them when DROP COLUMN was added.
`FULL OUTER JOIN` is the secret to diff'ing table sources. `MERGE` is just a diff operation + insert/update/delete statements to make the target table more like the source one (or even completely like the source one).
`FULL OUTER JOIN` is essential to implementing `MERGE`. Granted, one could implement `MERGE` without implementing `FULL OUTER JOIN` as a public feature, but that seems silly.
Sadly, the SQLite3 dev team specifically says they will not implement `FULL OUTER JOIN`[0].
Implementing `MERGE`-like updates without `FULL OUTER JOIN` is possible (using two `LEFT OUTER JOIN`s), but it's an O(N log N) operation instead of O(N).
The lack of `FULL OUTER JOIN` is a serious flaw in SQLite3. IMO.
[0] https://www.sqlite.org/omitted.htmlFinally!
Thank you so much for this Mr. Hipp!
EDIT: Don't forget to edit the `omitted.html` page when you ship it!
I don't know where this idea of having to swap a whole table in SQLite came from, but it simply isn't true. Over the last 13 years I have upgraded production HashBackup databases at customer sites a total of 35 times without rewriting and swapping out tables by using the ALTER statement, just like other databases:
https://www.sqlite.org/lang_altertable.html
For the most recent upgrade, I upgraded to strict tables, which I could also have done without a rebuild/swap. I chose to do a rebuild/swap this one time because I wanted to reorder some columns. Why? Because columns stored with default or null values don't have row space allocated if the column is at the end of the row.