Quote table name in SQL exporter

[timtomch]

The SQL exporter tries to clean up column names and encloses them in double quotes, but it doesn't appear to do so for the table name. This is an issue e.g. when project names only contain digits, which MySQL won't accept as table names unless quoted.

To Reproduce

Steps to reproduce the behavior:

1. Create a project with a name that includes only digits
2. Export project as SQL

Current Results

The current (OR 3.8.0) SQL export quotes column names but not the table name:

CREATE TABLE 20240503 (
"_____ABC" VARCHAR(255) NULL,
"2211" VARCHAR(255) NULL,
"_22ABC" VARCHAR(255) NULL
);
INSERT INTO 20240503 ("_____ABC","2211","_22ABC") VALUES 
( '111','222','333' ),
( 'zzz','1212 ','1212' )

Expected Behavior

Also quote the table name:

CREATE TABLE "20240503" (
"_____ABC" VARCHAR(255) NULL,
"2211" VARCHAR(255) NULL,
"_22ABC" VARCHAR(255) NULL
);
INSERT INTO "20240503" ("_____ABC","2211","_22ABC") VALUES 
( '111','222','333' ),
( 'zzz','1212 ','1212' )

Additional context

Adding double quotes around the table name would be consistent with the current quoting of column names but wouldn't address the issue of the resulting SQL being rejected by MySQL as MySQL expects backticks instead of double quotes.

A potential solution to this issue would be to add an option in the UI for the user to specify which character to escape table and column names with, as initially proposed in #4716 but subsequently rejected in favour of implementing the SQL standard of double quotes only.

This was discussed on the forum after a user reported having trouble exporting to MySQL.

[thadguidry]

I tend to agree. We should add a toggle to

• Use ANSI QUOTES

which once checked, would wrap double quotes around identifiers.
when unchecked, it would use backticks.

https://dev.mysql.com/doc/refman/8.4/en/identifiers.html

@timtomch And the default should be... unchecked, you think?

[tfmorris]

I changed "escape" to "quote" to follow common terminology.

It looks like there are at least three different conventions for quoting what SQL calls "delimited identifiers":

1. "ANSI Standard"
2. [MS SQL Server]
3. MySQL

In all cases, the quote character can be included in the identifier by doubling (ie escaping) it. I don't know if we have other uses of identifiers besides column names and table names, but, if so, they should follow the same quoting rules.

[timtomch]

Thanks for figuring this out! I think that despite MySQL being quite common, the default should still be the ANSI standard, no?