sqlite> CREATE TABLE "t" ( "c" );
sqlite> .schema "t"
CREATE TABLE IF NOT EXISTS "t" ( "c" );

This was not what I expected. In further tests, the code returned by the .schema command is correct to the letter (including indexes), however the IF NOT EXISTS addition doesn't look ok to me.

Looking up the source code led me to check-in c7021960 which has the following description (emphasis mine):

In the command-line shell, in the output of the ".dump", ".schema", and ".fullschema" commands, convert CREATE TABLE statements that appear to come from shadow tables into CREATE TABLE IF NOT EXISTS statements.

Also the checked-in code has the following comment:

** This routine converts some CREATE TABLE statements for shadow tables
** in FTS3/4/5 into CREATE TABLE IF NOT EXISTS statements.

It seems to me that the code does not reflect the intention. That is because the code is making the substitution in plain tables, disregarding if it's a "shadow table in FTS3/4/5" or not.

I barely know what shadow tables and FTS are, so I don't think I should be getting that.

Thanks for your time and congratulations for the great product.

The commit you show changes CREATE TABLE statements for tables with yukky names (ie, they are quoted) into CREATE TABLE IF NOT EXISTS statements.

I suppose that FTS and shadow tables might be created with yukky names, but quote lovers can create them too.

The comment does not match the code. The comment should say that tables with quoted table names (specifically, names quoted with identifier quotes "), whether those quotes are necessary or not, will be changed to add an IF EXISTS clause.

Tables with "bare" or other-quoted names will be unmolested.

Right. That's surprising... I've noticed this before, there wasn't any impact for me but it was surprising that the mere existence of properly-quoted identifiers would change the CREATE TABLE statement.

I never used to quote because I too considered them "yukky" (sic) until I encountered columns that had to be named "order" and "rank". After a while of trying to programmatically distinguish keywords from names and quoting them, I decided it was easier to just quote all of them. Now I've kinda grown to like the quotes as they visibly distinguish names.

Quoting is good practice and does help avoid problems. :)