SQL Dialects

[LarnuUK]

Language
SQL is an option in Prism, but each dialect/flavour (i.e. MySQL, PL\SQL (Oracle), Transact-SQL (SQL Server)), etc have their own nuances, functions, keywords. Could individual languages be added for these dialects. These would likely be an extend of the sql language.

I'm happy to give T-SQL a go, but don't know if others would be interested in doing so, or if there is interest.

Additional resources
SQL Server technical documentation
MySQL Documentation
Database PL/SQL Language Reference
PostgreSQL

[RunDevelopment]

To my knowledge, the MySQL Doc page actually has its own modification of the base Prism SQL language, so one might be able to get most patterns from them.

And we already have a PL/SQL language, but it might not be perfect/up to date.

[LarnuUK]

And we already have a PL/SQL language, but it might not be perfect/up to date.

Hadn't noticed the PL/SQL was listed. I'll have a look at doing a T-SQL one then.

[IssacEinstein]

I see a lot of Latest Keywords Missing in the MSSQL. Will post them.
Meanwhile, I had raised an issue [https://github.com//issues/3007]

[acarl005]

I want to ask about a strategy here. The problem is that each DB vendor has its own set of keywords, functions, syntax extensions (e.g. dollar-quoted strings in postgres), etc. Even the meaning of the types of quotes (single, double, backtick) differs between vendors. We can try to fit every vendors' peculiarities into a single Prism.languages.sql, but it will never satisfy everyone. Are y'all open to having separate SQL dialects be different languages with their own config object, e.g. Prism.languages.pgsql, Prism.languages.mysql, Prism.languages.mssql, etc? I'm going to be working on a Postgres-specific one anyways for my own project, and I'm wondering if y'all would merge it.

[LarnuUK]

It likely makes sense, if I am honest, while having a "generic" SQL that respects the syntax that is more common to all dialects. Highlight.JS a separate one for PL/SQL and pgsql, and I think they were considering doing one for T-SQL. The dialects, though all called SQL, can be very different (PostgreSQL can be completely different to a lot of other dialects).

I would, however, suggest that for (Microsoft) SQL Server and Oracle's SQL dialect you use their actual names: which are Transact-SQL (T-SQL) and Procedure Language SQL (PL/SQL) respectively. So would be Prism.languages.plsql for example.

[RunDevelopment]

Are y'all open to having separate SQL dialects be different languages with their own config object

Yes, it has to be that way. There is no guarantee that these different dialects are even compatible with each other. Sure, they are all based off vanilla SQL, but the same (or very similar) syntax might be used to denote different things in different dialects.

This also works better for users. If a user knows that they're using a specific dialect, we should probably respect that and give them the best highlighting we can instead of say: "You'll get SQL. Take it, or leave it."

Also, it's just easier to implement them as separate languages. Having x-many languages mushed into one grammar is hellish to maintain. Languages evolve and change over time, so we'll have to adjust them.

I'm wondering if y'all would merge it.

Of course! Language contributions are highly appreciated. We'll also review your language before merging, so we might even be able to improve it a little :)

[karlhorky]

Would also love to have SQL dialects as part of Prism!

For example PostgreSQL GENERATED and ALWAYS keywords:

Prism:

VS Code:

[karlhorky]

Wonder if some open source SQL language datasets could be used to make it faster to create these dialects, eg. the token / keyword data from sql-formatter:

• https://github.com/sql-formatter-org/sql-formatter/tree/master/src/languages