Implementing a modifiable adapter

[Mike Hearn <mi...@hydraulic.software>]

Hello,

I'm experimenting with exposing a set of mutable objects via Calcite so I
can connect to it and explore/modify the data with tools that speak JDBC
like DataGrip. Thanks for Calcite! It looks like exactly what's needed.

So far I managed to set up an Avatica server that connects to Calcite using
my custom schema factories and tables. And ... queries work! Hurrah. I can
explore the data in DataGrip.

Now I want to implement DML so the data can be edited. Here, the road seems
less travelled. If anyone knows the answers to these questions I'd be
grateful:

1. Are there any examples of anyone doing this? None of the included
adapters seem to support writing to the data source. CALCITE-2748
<https://issues.apache.org/jira/browse/CALCITE-2748> sounds a bit
discouraging. The built-in support only really supports calling addAll() on
a custom collection, or removeAll(). And indeed when I try UPDATE I hit the
assertion.

2. Therefore I think I need to implement my own TableModify sub-class that
implements EnumerableRel, which will then construct a Java AST that will
emit method calls back into my custom Table class. Is that right?

3. To get my custom TableModify to be used I need to write a planner rule
to substitute LogicalTableModify for my custom rel, I think. The docs show
how to write a rule but there's no info on how to register it for use. The
enumerable rules seem to be hard-coded and registered by a fixed code path.
Does anyone know how to register a custom planner rule to get a custom
TableModify rel into use, starting from a JdbcMeta that connects to the
jdbc:calcite: driver using a connection string? I've spent quite a bit of
time rummaging around but there's no obvious path from a connection object
to anything that sounds like a planner rule registry.

4. When generating the code, it seems like the results of the WHERE clause
come in the form of an Enumerable. Is there a way to pass custom data
through the computed results so by the time a set of rows have been
selected I can map them back to the underlying objects the data originally
came from? The SET expressions are going to be translated to setXxx method
calls so I need the pointer. The objects do have unique IDs so, modifying
the query to include that additional column can also work but it needs to
be transparent to the user.

5. The underlying data layer has a notion of its own transactions. I'm
overriding JdbcMeta to implement creating and committing the underlying
transactions. Is that the right place to do it? There's a lot of different
layers in Calcite/Avatica and I'm not 100% sure I'm doing things the right
way.

Any tips much appreciated!

thanks,
-mike

Re: Implementing a modifiable adapter

[Mike Hearn <mi...@hydraulic.software>]

Hey Gavin,

Yep, that's me!

You say a set of mutable objects, is this something like in-memory
> collections?
>

I'm experimenting with an obscure but fascinating (to me)  framework called
Permazen:

https://github.com/permazen/permazen

It's an alternative take on Java persistence - sort of like an ORM but
where the backing is a KV store rather than an RDBMS. There's no query
language, you just use NavigableSet and a variety of utilities. It's
remarkably mature e.g. it has full support for indexing, transactions,
validation, a variety of KVStore backends, and there's even a full blown
Raft implementation. Plus although it's not mentioned in the repo, it's
actually used in production.

There's a white paper in the repo, a presentation, JavaDocs and the wiki
explains the API.

I've been waiting for a chance to use it for real for some years now. Well,
now I have a chance. The big weakness (IMHO) of Permazen is that whilst it
does provide a CLI for invoking code using Java syntax, and you can do
queries and mutations that way, Java syntax is a bit verbose for that and
anyway existing tools all speak SQL. I'd like to experiment
with implementing my app server persistence with Permazen, and keep SQL off
to the side for interactive query/hotpatching + maybe dashboarding.

Calcite seems ideally suited for this. Permazen gives you a bunch of
objects and collections that can be accessed inside transactions.
Collection operations and bean property read/writes are converted to KV
read/writes behind the scenes.

Sure enough, queries work quite well and it didn't take much code to get
there. But updates, well ... maybe that's where my cunning plan comes
unglued. I don't want to spend a huge amount of time on this. What I'm
really looking for is someone who's done this before so I can fumble my way
through a cargo-cult operation. It doesn't have to be robust or perfect,
because it's an experiment to see if this way of writing apps feels good.

Hopefully that provides enough background info.

thanks,
-mike

Re: Implementing a modifiable adapter

[Gavin Ray <ra...@gmail.com>]

Hey Mike,

I can't offer much in the way of experience on the above -- but you may
also get pointers
if you're able to share an overview of what you're trying to do (if you can
disclose)

You say a set of mutable objects, is this something like in-memory
collections?

Also, are you by chance the same Mike Hearn from the GraalVM Slack that
wrote the Kotlin + Node Graal adapter?
If so, nice to see you here =)

On Tue, May 3, 2022 at 11:56 AM Mike Hearn <mi...@hydraulic.software> wrote:

> Hello,
>
> I'm experimenting with exposing a set of mutable objects via Calcite so I
> can connect to it and explore/modify the data with tools that speak JDBC
> like DataGrip. Thanks for Calcite! It looks like exactly what's needed.
>
> So far I managed to set up an Avatica server that connects to Calcite using
> my custom schema factories and tables. And ... queries work! Hurrah. I can
> explore the data in DataGrip.
>
> Now I want to implement DML so the data can be edited. Here, the road seems
> less travelled. If anyone knows the answers to these questions I'd be
> grateful:
>
> 1. Are there any examples of anyone doing this? None of the included
> adapters seem to support writing to the data source. CALCITE-2748
> <https://issues.apache.org/jira/browse/CALCITE-2748> sounds a bit
> discouraging. The built-in support only really supports calling addAll() on
> a custom collection, or removeAll(). And indeed when I try UPDATE I hit the
> assertion.
>
> 2. Therefore I think I need to implement my own TableModify sub-class that
> implements EnumerableRel, which will then construct a Java AST that will
> emit method calls back into my custom Table class. Is that right?
>
> 3. To get my custom TableModify to be used I need to write a planner rule
> to substitute LogicalTableModify for my custom rel, I think. The docs show
> how to write a rule but there's no info on how to register it for use. The
> enumerable rules seem to be hard-coded and registered by a fixed code path.
> Does anyone know how to register a custom planner rule to get a custom
> TableModify rel into use, starting from a JdbcMeta that connects to the
> jdbc:calcite: driver using a connection string? I've spent quite a bit of
> time rummaging around but there's no obvious path from a connection object
> to anything that sounds like a planner rule registry.
>
> 4. When generating the code, it seems like the results of the WHERE clause
> come in the form of an Enumerable. Is there a way to pass custom data
> through the computed results so by the time a set of rows have been
> selected I can map them back to the underlying objects the data originally
> came from? The SET expressions are going to be translated to setXxx method
> calls so I need the pointer. The objects do have unique IDs so, modifying
> the query to include that additional column can also work but it needs to
> be transparent to the user.
>
> 5. The underlying data layer has a notion of its own transactions. I'm
> overriding JdbcMeta to implement creating and committing the underlying
> transactions. Is that the right place to do it? There's a lot of different
> layers in Calcite/Avatica and I'm not 100% sure I'm doing things the right
> way.
>
> Any tips much appreciated!
>
> thanks,
> -mike
>