Add support for predicates / operators

[michael-simons]

From sql2cypher created by jexp: neo4j-contrib/sql2cypher#14

Conditional Operators / Predicates

https://neo4j.com/docs/cypher-manual/current/clauses/where/#boolean-operations

• [X] AND, OR
• [X] Equals, Greater, Less Than
• [x] Less Than Equal, Greater Than Equal (missing in jooq @lukaseder)
• [x] BETWEEN
• [x] TRUE / FALSE (are they in SQL @lukaseder?)
• [x] NOT
• [x] Null handling (IS NULL etc)
• [x] NULL value
• [ ] Regular expressions https://neo4j.com/docs/cypher-manual/current/clauses/where/#query-where-regex
• [ ] String predicates STARTS WITH, ENDS WITH, CONTAINS -> LIKE ?
• [ ] Predicate (list) functions and predicates? (not sure if there is an equivalent in SQL, perhaps with subqueries)
• [ ] Label / Rel-Type checks ?
• [ ] exists and count expressions

Predicate functions https://neo4j.com/docs/cypher-manual/current/functions/predicate/

[michael-simons]

• Less Than Equal, Greater Than Equal (missing in jooq @lukaseder)

See QOM.Le, QOM.Ge

• TRUE / FALSE (are they in SQL @lukaseder?)

Yes, there are org.jooq.True, org.jooq.False, and org.jooq.Null. They should probably be reflected in the QOM API for better discoverablity: https://github.com/jOOQ/jOOQ/issues/14537

[michael-simons]

Regarding these:

• Regular expressions https://neo4j.com/docs/cypher-manual/current/clauses/where/#query-where-regex
• String predicates STARTS WITH, ENDS WITH, CONTAINS -> LIKE ?

My suggestion here is:

• jOOQ has QOM.StartsWith, etc. which are mere jOOQ convenience. They can't be parsed, currently, as there is not SQL equivalent
• Like can be pattern matched as follows:
  • If the right hand side is a constant (org.jooq.Param), then process that Param in the translator
    • If it's x%: STARTS WITH
    • If it's %x: ENDS WITH
    • If it's %x%: CONTAINS
    • Otherwise, or if x contains % or _ then apply below regex rule
  • Otherwise turn LIKE into a regex: <lhs> ~= replace(replace(quoteForRegex(<rhs>), '%', '.*'), '_', '.'), optionally handling the LIKE .. ESCAPE clause

[michael-simons]

for LIKE: might even make sense to do the replace in cypher, so that non-literal values (parameters) or expressions are also handled correctly.

the only tricky bit is escaping for regexp, perhaps something like:

reduce(s=str, rep IN [['?','\?'], ...['_','.'],['%','.*']] | s = replace(s, rep[0],rep[1])

or alternatively spell it out with a replace cascade

[michael-simons]

might even make sense to do the replace in cypher, so that non-literal values (parameters) or expressions are also handled correctly.

That's what I meant by "Otherwise turn LIKE into a regex: <lhs> ~="

[michael-simons]

Ah you didn't mean replacing the string in the transpiler but in the generated statement. Yep, sgtm.

[michael-simons]

Well, if there's a constant (org.jooq.Param), then that can be processed in the translator. Otherwise, it simply cannot.

[michael-simons]

I've added support for BETWEEN SYMMETRIC too. Dogfooding has shown that the QOM API needed a significant refactoring: https://github.com/jOOQ/jOOQ/issues/14546, so I've moved the tag