Only quote non-syntactic names and reserved words in dbQuoteIdentifier

[hadley]

Using https://www.sqlite.org/c3ref/keyword_check.html to capture all reserved words.

This isn't very important, but it does help dbplyr generate more natural SQL.

[krlmlr]

Interesting. Should we experiment with an sqliteQuoteIdentifier()?

[hadley]

Why wouldn't this just be a method of dbQuoteIdentifier()?

[krlmlr]

dbplyr will have to special-case anyway, a separate new experimental entry point is easier to deprecate/change later on.

IIUC, sqlite3_keyword_check() only checks if a string is a keyword, not if it is syntactic. The only reliable reference seems to be the tokenizer, https://github.com/mackyle/sqlite/blob/master/src/tokenize.c (with character classes defined elsewhere). The rules don't seem to be that complex, though.

[krlmlr]

The other thing to consider -- if we aim to build SQL that is "pretty enough" to be used permanently and remain unchanged for years, what happens if SQLite introduces a keyword later on? If it's only for aesthetic purposes, I agree it's useful.

Should we do this for duckdb too?

[hadley]

I think adding reserved words would be pretty rare, as it would potentially break lots of existing SQL.

For duckdb, see duckdb/duckdb#3718