add parser-based validation for JPQL, SQL, HQL snippets

[martinlippert]

Add validation logic to the language support for JPQL (etc.) wherever those languages are being used (annotations in Java source code, named query files, etc.).

The validation should report messages from the parser to the user in the form of diagnostics, as usual. The more detailed and fine-grained, the better - depending on that is possible using the parser at hand.

[BoykoAlex]

I'd factor out SQL is a separate issue as we don't have SQL parser and semantic token syntax highlighting for SQL. At the moment all SQL support comes from upstream extensions and syntax highlighting for SQL is somewhat restricted by what upstream extensions/plugins provide. There is no semantic tokens syntax highlighting from Boot LS provided for native SQL (but we can change that of course)

[BoykoAlex]

JPQL and HQL are in with 385c35e832a0af23c6ac6e9da54dc6e4f800c3a2

[BoykoAlex]

SQL was the last one. Went in with fe953bde5b0291c49f7b6c58f0f1b63cfb46d21f

[pcdavid]

It looks like the SQL parser only knows about MsySQL's syntax (from the file name)?

In our case we use PostreSQL-specific syntax and with the update to 4.23 I now have an error marker as the parser does not like/understand PG's type-cast syntax using :::

SQL: no viable alternative at input 'DELETE FROM Representation representation
            WHERE representation.project_id=?1
            AND NOT EXISTS (
                SELECT * FROM Document document
                WHERE document.project_id=?1
                AND jsonb_path_exists(document.content:'

Is there a way to disable these checks?

[BoykoAlex]

@pcdavid I hate to say that but the release doesn't have a setting to switch this validation off unfortunately. The setting didn't get generated and it has gone unnoticed. I have pushed the preferences/settings for SQL, HQL, JPQL syntax validation. Please try updating from the nightly build p2 repo: for Eclipse 2024-06 (4.32): https://cdn.spring.io/spring-tools/snapshot/TOOLS/sts4/nightly/e4.32 for Eclipse 2024-03 (4.31): https://cdn.spring.io/spring-tools/snapshot/TOOLS/sts4/nightly/e4.31 for Eclipse 2023-12 (4.30): https://cdn.spring.io/spring-tools/snapshot/TOOLS/sts4/nightly/e4.30

It should be here. The drop down has an option IGNORE which should turn the validation off:

Please let us know how it goes.

Otherwise, it might be best either don't syntax validate native query or looks at the classpath perhaps and figure out whether it is expected to be MySQL or PostGres or some other flavour of SQL... there are a couple of flavours of SQL... wonder if it makes sense to support all of them or none or some...

[BoykoAlex]

For the MySQL parser issue: #1273

[pcdavid]

Thanks for the fast response! I've updated my install using the nightly (for 2024-06) and indeed, I can now disable this validation and get rid of the error (or turn it into a warning/info).

Strangely, I now see two such syntax issues, while there was only one with STS 4.23.0. The second is in our IDocumentRepository interface (whose code did not change recently.

Anyway, the new setting fixes the issue for me. Thanks again.

[BoykoAlex]

Thanks for reporting the issue. We'll look into MySQL parser thing and see if we can provider something for Postgres SQL flavour

[BoykoAlex]

@pcdavid I'm circling back to PostgreSQL support in STS and I see :::: in your queries... The ANTLR grammar doesn't support the 4 colons syntax... isn't supposed to be :: instead of ::::? Are you sure those queries are working fine? (adding @schauder from spring data team)

[pcdavid]

I think we don't use them anymore, but they were working fine for a long time.

The actual PostgreSQL syntax only uses two for type conversion (content::jsonb, where content is plain text but we needed to parse it as jsonb to apply JSON operators on it).

The doubling was required by the context, I think to avoid ambiguity with named parameters' syntax which also use colons.