These seem reasonable:

• ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...
• ALTER TABLE ... DROP COLUMN IF EXISTS ...

But it does not seem like an "IF EXISTS" clause is appropriate for the RENAME variations of ALTER TABLE. Do you concur?

Edit: I see that PG supports ALTER TABLE IF EXISTS RENAME. Perhaps that is not as unreasonable as I originally thought....

Sounds sensible to me. But RENAME might be useful for some types of schema change; just not the ones I've a use for in my own migrations (where I've got to do a lot more besides if it isn't adding or killing a column).

I've no idea about the technical difficulty of determining whether the change should go ahead, but there's obviously something there right now that's currently just throwing errors on failure; it's making that go “welp, nothing to do” instead that I'd like.

Rename is a difficult one... Would "IF EXISTS" refer to the old name or the new name?

In what possible logic frame of reference could that refer to the new name?

The old one, obviously. For testing the new name, you'd insert a IF NOT EXISTS after the RENAME TO.

https://www.sqlite.org/src/timeline?c=460abf93ac6c47b0

I suggest to not only review the syntax diagrams for the new IF EXISTS / IF NOT EXISTS elements but also to review for example section 3.4 The GENERATED ALWAYS AS clause for typo errors.

A column that includes a GENERATED **ALWAYS** AS clause is a generated column.

Generated columns are supported beginning with SQLite **version** 3.31.0 (2020-01-22).

Also it might be important to emphasize again that GENERATED ALWAYS AS (expr) VIRTUAL columns can be added but not the STORED ones, see chapter 2.1 VIRTUAL versus STORED columns and 2.3. Limitations item 7. It would be great if this restriction could fall and the statement invalidated. I would highly welcome the ability to add also GENERATED ALWAYS AS (expr) STORED columns.

Regarding the GENERATED columns it could be also good to allow easy conversion into regular columns, disjoining them from the calculation expression and thus allowing in a next step to drop the columns which were required by the expression before -or- to use the database again with earlier versions of SQLite3 before version 3.31.0; see chapter 3. Compatibility.

"Regarding the GENERATED columns it could be also good to allow easy conversion into regular columns, disjoining them from the calculation expression..."

MBL, I may not be understanding correctly, but isn't this directly and easily accomplished by an ordinary update -- one that is presumably as efficient as any new syntax?

E.g.

    ALTER TABLE MyTable ADD COLUMN newCol TEXT;
    UPDATE MyTable SET newCol = existingColumn || ' hurray!';

"..but isn't this directly and easily accomplished by an ordinary update"

It is the only way for generated as VIRTUAL columns as the result does not exist in stored format.

However, it is different for generated as STORED columns, where the content is already stored and would not need to be recalculated with the side effect of having the new column in addition in the schema and in the storage space with same duplicated content. - Changing the schema definition of the table for the STORED column does not help but makes things even worse: The calculation will STILL be done but the schema does not tell you anymore, a no-go. See also the result of my experiment below.

The efficiency of my proposal for native supported conversion from generated to regular would only become visible on huge databases where time and storage plays a bigger role, when already stored data could be reused without recalculation.

D:\Sandbox>sqlite3 Generated.sqlite
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .mode box
sqlite> create table INFO( Base text, DevStored text AS(Base||' stored') STORED, DevVirtual text AS(Base||' virtual') );
sqlite> insert into INFO VALUES('myFirstEntry'),('my2ndEntry');
sqlite> select * from INFO;
┌──────────────┬─────────────────────┬──────────────────────┐
│     Base     │      DevStored      │      DevVirtual      │
├──────────────┼─────────────────────┼──────────────────────┤
│ myFirstEntry │ myFirstEntry stored │ myFirstEntry virtual │
│ my2ndEntry   │ my2ndEntry stored   │ my2ndEntry virtual   │
└──────────────┴─────────────────────┴──────────────────────┘
sqlite> select * from sqlite_master;
┌───────┬──────┬──────────┬──────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type  │ name │ tbl_name │ rootpage │                                                       sql                                                       │
├───────┼──────┼──────────┼──────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ table │ INFO │ INFO     │ 2        │ CREATE TABLE INFO( Base text, DevStored text AS(Base||' stored') STORED, DevVirtual text AS(Base||' virtual') ) │
└───────┴──────┴──────────┴──────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
sqlite> pragma writable_schema=on;
sqlite> update sqlite_master set sql='CREATE TABLE INFO( Base text, DevStored text, DevVirtual text AS(Base||'' virtual'') )' where tbl_name='INFO';
sqlite> pragma writeable_schema=RESET;
sqlite> select * from sqlite_master;
┌───────┬──────┬──────────┬──────────┬──────────────────────────────────────────────────────────────────────────────────────┐
│ type  │ name │ tbl_name │ rootpage │                                         sql                                          │
├───────┼──────┼──────────┼──────────┼──────────────────────────────────────────────────────────────────────────────────────┤
│ table │ INFO │ INFO     │ 2        │ CREATE TABLE INFO( Base text, DevStored text, DevVirtual text AS(Base||' virtual') ) │
└───────┴──────┴──────────┴──────────┴──────────────────────────────────────────────────────────────────────────────────────┘
sqlite> select * from INFO;
┌──────────────┬─────────────────────┬──────────────────────┐
│     Base     │      DevStored      │      DevVirtual      │
├──────────────┼─────────────────────┼──────────────────────┤
│ myFirstEntry │ myFirstEntry stored │ myFirstEntry virtual │
│ my2ndEntry   │ my2ndEntry stored   │ my2ndEntry virtual   │
└──────────────┴─────────────────────┴──────────────────────┘
sqlite> insert into INFO(Base) Values('my3rdEntry');
sqlite> insert into INFO Values('my4thEntry');
sqlite> select * from INFO;
┌──────────────┬─────────────────────┬──────────────────────┐
│     Base     │      DevStored      │      DevVirtual      │
├──────────────┼─────────────────────┼──────────────────────┤
│ myFirstEntry │ myFirstEntry stored │ myFirstEntry virtual │
│ my2ndEntry   │ my2ndEntry stored   │ my2ndEntry virtual   │
│ my3rdEntry   │ my3rdEntry stored   │ my3rdEntry virtual   │
│ my4thEntry   │ my4thEntry stored   │ my4thEntry virtual   │
└──────────────┴─────────────────────┴──────────────────────┘
sqlite> insert into INFO(Base,DevStored) Values('my5thEntry','new stored 5');
Error: in prepare, cannot INSERT into generated column "DevStored" (1)
sqlite>

In 3rd line was expected to get only the Base and Virtual generated columns filled.

Trying to fill something into the 2nd column fails even when the schema text in sqlite_master does not tell you anymore about its generated nature.

This might be one good example showing how easy it is to corrupt a schema definition by using non-standard supported methods.

Turning off writable schema does not reload the schema (although the documentation indicates otherwise).

After you reset the writeable_schema you have to increment the schema_version and then force a reload. Inserting this will make it work:

pragma schema_version=5;
analyze sqlite_master;

Turning off writable schema does not reload the schema (although the documentation indicates otherwise).

I suspect you have misread that section of the documentation. The reload is not done upon mere resumption of the schema write protection, or claimed to be.

The documentation in question says: "If the argument is 'RESET' then schema writing is disabled (as with 'PRAGMA writable_schema=OFF') and, in addition, the schema is reloaded."

There is code to make that reload happen when the argument is "RESET" (or its casing variations), and a few test cases that exercise it. If you have some input which causes the reload to not happen upon executing "pragma writable_schema=reset;", please share it.

I confirm this has solved my issue: DevStored column now behaves like a regular column and not any longer as a GENERATED AS (expr) STORED one.

sqlite> insert into INFO(Base) Values('my5thEntry');
sqlite> select * from INFO;
┌──────────────┬─────────────────────┬──────────────────────┐
│     Base     │      DevStored      │      DevVirtual      │
├──────────────┼─────────────────────┼──────────────────────┤
│ myFirstEntry │ myFirstEntry stored │ myFirstEntry virtual │
│ my2ndEntry   │ my2ndEntry stored   │ my2ndEntry virtual   │
│ my3rdEntry   │ my3rdEntry stored   │ my3rdEntry virtual   │
│ my4thEntry   │ my4thEntry stored   │ my4thEntry virtual   │
│ my5thEntry   │                     │ my5thEntry virtual   │
└──────────────┴─────────────────────┴──────────────────────┘
sqlite> insert into INFO(Base,DevStored) Values('my6thEntry','new content');
sqlite> select * from INFO;
┌──────────────┬─────────────────────┬──────────────────────┐
│     Base     │      DevStored      │      DevVirtual      │
├──────────────┼─────────────────────┼──────────────────────┤
│ myFirstEntry │ myFirstEntry stored │ myFirstEntry virtual │
│ my2ndEntry   │ my2ndEntry stored   │ my2ndEntry virtual   │
│ my3rdEntry   │ my3rdEntry stored   │ my3rdEntry virtual   │
│ my4thEntry   │ my4thEntry stored   │ my4thEntry virtual   │
│ my5thEntry   │                     │ my5thEntry virtual   │
│ my6thEntry   │ new content         │ my6thEntry virtual   │
└──────────────┴─────────────────────┴──────────────────────┘
sqlite>

Is there any news on this? Is this implemented at last?

I handled this in an Oracle-based application by storing a SCHEMA_VERSION_NUMBER in the database. The result of each change had its own version number.

Unfortunately, Oracle did not let me do schema updates within a larger transaction. But SQLite will let you do it. So you can start a transaction, do your CREATE or ALTER statement, and update your version number, all within the same transaction. If anything goes wrong, it all rolls back, and the version number remains reliable.

But you can't write SQL that conditionally updates the schema based upon the schema version (unless you turn on having a directly writable schema) so you need to put additional logic in the application level, at which point there's all sorts of possibilities. That's what I do right now, but it seems inelegant. It also seems inconsistent to me to allow conditionally defining a table but not conditionally adding a column to it.