Add basic Ltree support for Postgresql #5875
Conversation
| jsona_binary_operator ::= '->' | '->>' | '#>' | '#>>' | ||
| jsonb_binary_operator ::= '#-' | ||
| jsonb_boolean_operator ::= '@?' | '??|' | '??&' | '??' | ||
| ltree_binary_operator ::= '?@>' | '?<@' | '?@' | '?~' | ||
| ltree_boolean_operator ::= '<@' | '@>' | '@' | '~' | '^<@' | '^@>' | '^@' | '^~' | '?' |
There was a problem hiding this comment.
The ? is a reserved token for bind arguments - it is escaped as ?? - see jsonb_boolean_operator
as it would be written as
SELECT *
FROM Graph WHERE path ?? ?; -- ? operator is escaped with extra ? in jdbc
| jsona_binary_operator ::= '->' | '->>' | '#>' | '#>>' | ||
| jsonb_binary_operator ::= '#-' | ||
| jsonb_boolean_operator ::= '@?' | '??|' | '??&' | '??' | ||
| ltree_binary_operator ::= '?@>' | '?<@' | '?@' | '?~' | ||
| ltree_boolean_operator ::= '<@' | '@>' | '@' | '~' | '^<@' | '^@>' | '^@' | '^~' | '?' | ||
| contains_operator ::= '@>' | '<@' |
There was a problem hiding this comment.
The issue here is that @> and <@ are already defined as the contains operator - this is used across various data types.
The problem is factoring out the shared operators with JSON etc and making it a general operator
There was a problem hiding this comment.
@griffio I noticed this while trying to implement LTREE. I adjusted the the one ContainsOperatorExpressionMixin to include LTREE. I'm not sure how this may affect usages outside of this.
Could you please advise?
There was a problem hiding this comment.
I have created a basic implementation for the contains operator only as this doesn't require any extra rules
see #5880 - so you can see what is involved in creating a new data type
Adding more operators can be tackled as it will require more trial and error
Note: The LTREE type is a Postgresql extension - but is a trusted extension and still requires CREATE EXTENSION Ltree to use it. (see integration test)
| @@ -0,0 +1,15 @@ | |||
| CREATE TABLE myTable( | |||
There was a problem hiding this comment.
Fixture tests can appear to pass but they don't generate any types and can pass even if it doesn't work
Need Integration tests for actual executing queries against Postgres - the more the better
see https://github.com/sqldelight/sqldelight/blob/master/sqldelight-gradle-plugin/src/test/integration-postgresql/src/test/kotlin/app/cash/sqldelight/postgresql/integration/PostgreSqlTest.kt
Added basic support for ltree postgresql extension.
There are a few operators that are not support due to conflicting mixin expressions that I was unsure of how to navigate. These include
~and||operators.