Option for specifying case for identifiers

[chrjorgensen]

@nene My personal preference in SQL formatting is to have keywords in lowercase and identifiers in uppercase, like this:

select COLUMN1
     , COLUMN2
  from TABLE

Currently there is no way to specify that identifiers should be in uppercase.
So I'm thinking of creating a PR which adds this functionality.

What are your thoughts on this? I won't bother to code this if you're against such functionality... 😉

[nene]

Have to admit that I've never seen such style before. I guess this stems from DB2 which internally keeps all identifiers in uppercase. One the flip-side, there's currently also no feature for enforcing identifiers to be in lowercase.

The problem with implementing this is that SQL Formatter isn't able to accurately identify which words are actually keywords and which are not. For example in your example query, SQL Formatter will currently mistakenly identify TABLE as a keyword, not as an identifier.

I suspect that in DB2i a query like SELECT having AS from FROM where; is a valid one, like in the LUW-variant of DB2. This complicates the problem of telling identifiers and keywords apart even more than in other dialects where a lot of keywords are fully reserved.

This whole thing also relates to lots of existing issues:

• User-defined functions and table-valued functions have an extra space  #444
• Feature Request: Config for uppercase / lowercase data types #653
• Feature Request: Add datatypes for Snowflake Keywords  #641
• Allow uppercasing of function names separately from keywords #237
• [FORMATTING] Confusion between LIMIT keyword and column name #302

I'm not really against adding this functionality, but I think it would be better to first tackle some existing keyword-case related problems. Like, I personally would first like to separate the upper/lower-casing of keywords from upper/lower-casing of function names. (Several dialects treat built-in function names e.g. COUNT as keywords, while others do not. And the formatter currently lumps these together, which isn't really good.)

[chrjorgensen]

Yeah, as said it is a personal style of mine... 😄

DB2i keeps identifiers in uppercase - unless you create them as quoted, like "having". So it IS possible to have identifiers in mixed case, but not by default.

My idea is to copy the functionality for the keywords, where one can specify preserve, lower and upper. So the user can select to force identifiers in lowercase (e.g. if keywords are in uppercase), uppercase (e.g. if keywords are in lowercase - my style)... or preserve the case (don't touch it).

SELECT having AS from FROM where; is valid syntax in DB2i - I just tested this on our system.

I don't think it's an issue that the formatter can't distinguish between identifiers and reserved keywords. If the user creates identifiers the same as a reserved keyword, it's his/hers problem. And the user just has to live with it...

A separate option for function name casing is a great idea, which I fully support.

[chrjorgensen]

The reason for my preference of SQL keywords in lowercase is that uppercase is "SCREAMING" to me - almost hurts my eyes when reading code in uppercase.

DB2 has identifiers stored in uppercase by default, but it is also a great contrast to the SQL keywords and makes it easier to quickly spot the identifiers in the SQL code. I guess most people and SQL dialects has the opposite casing of keywords and identifiers to visually keep them apart?

[nene]

Just some thoughts...

While maintaining this formatter over the years I've encountered:

• keywords in uppercase
• everything in lowercase
• occasionally there's also something like Select foo From bar.

Nowadays it would seem to make sense to simply write it all in lowercase and let the syntax highlighting in your editor handle the difference between keywords and identifiers. But I think the reason these uppercase keywords still persist (besides tradition) is that SQL is often used in contexts where syntax highlighting is not available: through some primitive command-line interface, inside strings in various programming languages, in various templating systems.

There's also the case that SQL keywords are always case-insensitive (at least as far as I know). But identifiers can also be case-sensitive depending on the dialect or some configuration options. So blindly uppercasing or lowercasing the identifiers is not always a safe thing to do.

[nene]

Also.... the syntax highlighting often fails once you get out of the most basic SQL territory. You'd need to have a specific highlighter for your specific dialect... which likely still won't work for 100% of the cases, because the SQL grammar is just so damn insane. And do your colleagues also have it installed? The safest bet might be to do the highlighting manually.

[chrjorgensen]

SQL formatting and syntax highlighting will always be difficult because of the many dialects... but this tool does a great job anyway, IMHO.

FWIW, I have just created PR #663 to implement my idea... I hope it's useful anyway...?