feat(postgres): ltree support

halfmatthalfcat commented 2 years ago

This PR enables Postgres ltree support in quaint. ltree is a Postgres column datatype that allows n-depth traversable trees to be used in filtering/querying rows. ltree is a native extension that ships with Postgres (binary support 13+) but must be enabled manually by the user.

Reference material:

rust-postgres PR to enable support
Discussion in prisma/prisma on including ltree
Postgres ltree docs
Prisma issue on ltree

halfmatthalfcat commented 2 years ago

@pimeys @garrensmith @tomhoule @janpio Looks like this was slated for 3.12.0 but that release was closed yesterday. Is there a plan to review this and get it merged? I've got the prisma-engines work as a fast follow to this. Thanks!

janpio commented 2 years ago

Our "milestones" are more loose buckets where we put things we want to look at in a sprint, and seems @pimeys did not find the time to finally do that last sprint - so I moved it to the next one (and also put on an internal board to give it more visibility)

(Side note: We are pushing to get GiST and GIN indexes added to our extendedIndexes preview, which I think ltree will benefit from)

halfmatthalfcat commented 2 years ago

Our "milestones" are more loose buckets where we put things we want to look at in a sprint, and seems @pimeys did not find the time to finally do that last sprint - so I moved it to the next one (and also put on an internal board to give it more visibility)

(Side note: We are pushing to get GiST and GIN indexes added to our extendedIndexes preview, which I think ltree will benefit from)

Excellent! Sounds great, thank you for the context. Excited to see this all land <3

halfmatthalfcat commented 2 years ago

@janpio @pimeys - Just checking in on this again. Know you guys are busy but would like to land this soon so I can get the prisma-engines PR up.

TrumanRu commented 1 year ago

Ping

halfmatthalfcat commented 1 year ago

Ping

I'm going to be picking this back up again here soon(ish). @pimeys @janpio how close are we to extension support in Prisma? I've been out of the loop for a while now.

pimeys commented 1 year ago

We do have extensions in preview now:

https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions

What would really help here is a writeup that considers the ltree support from the point of view of the Prisma schema language. It's probably going to be @db.Ltree, and we might want to validate the extension with that. What would be the type we give back to the client? Is it a string, or something fancier? How would the ltree be used in the client queries. Are there some operations we would need to add.

The implementation here could come in phases. First just query raw, then more.

halfmatthalfcat commented 1 year ago

We do have extensions in preview now:

https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions

What would really help here is a writeup that considers the ltree support from the point of view of the Prisma schema language. It's probably going to be @db.Ltree, and we might want to validate the extension with that. What would be the type we give back to the client? Is it a string, or something fancier? How would the ltree be used in the client queries. Are there some operations we would need to add.

The implementation here could come in phases. First just query raw, then more.

A good chunk of those questions are answered here: https://github.com/prisma/prisma/discussions/12306#discussioncomment-2433474

The results are returned as strings (that's how Postgres more or less treats them as well).

halfmatthalfcat commented 1 year ago

Just to clarify, this PR enables support for ltree and the following functions (as well as their negations):

ancestor_of (@>)
descendant_of (<@)
matches (?)
matches_fulltext (@)
all equality operations (=, <>, <, >, <=, >=)

So the work is already done, its just rebasing this PR and getting my prisma-engines PR rebased/updated a bit.

Would it maybe be easier if we all jump on a call at some point and discuss a unified path forward? I feel like some things are getting lost through the various issues, PRs and discussions.

halfmatthalfcat commented 1 year ago

What would really help here is a writeup that considers the ltree support from the point of view of the Prisma schema language.

@pimeys I could do this write up too if it would be ultimately helpful. Is there another similar writeup I could use as a base of reference?

prisma / quaint

feat(postgres): ltree support #352