[DISCUSS] Query API for Ignite 3.0

[Taras Ledkov]

Hi,

Ignite 3.0 requires a rethinking of the query API.
We have 'cache.query()' and 'SqlFieldQuery' abstractions at the Ignite
2.x native API and several JDBC implementation for clients.

I propose to think about new query/SQL API for the Ignite 3.0.

My vision is something like this:
Ignite will support two query APIs: standard JDBC (on native) and one of
reactive DB API.

1. Native JDBC API, e.g.:
     Connection conn = node.sql().connection(props);

JDBC is the industrial standard of the DB access and we have to support one.
Also:
1.1. Thin JDBC client will be really thin: provide network communication
layer and transparently map to native API.
1.2. Thick JDBC client implementation will be trivial: start client node
and open JDBC connection on the started node.
1.3. JDBC provides sufficient functionality to implement ODBC (need to
investigate: may be thin protocol may be extended to unify JDBC and ODBC).

2. About reactive DB API.
I don't know of any industrial standard API for DB reactive access now.
There are several candidates:
2.1. R2DBC look like the popular and alive. See [1];
2.2. ADBA (java.sql2 [2]) looks like not alive. Not sure;
2.3. Custom async/reactive API.
e.g. oracle DB use this way [3].

Igniters, WDYT?

[1]. https://github.com/r2dbc/r2dbc-spi
[2].
http://cr.openjdk.java.net/~lancea/apidoc/java/sql2/package-summary.html
[3].
https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/jdbc-reactive-extensions.html#GUID-1C40C43B-3823-4848-8B5A-D2F97A82F79B

--
Taras Ledkov
Mail-To: [hidden email]

[Ivan Daschinsky]

Hi Taras!
>> 1.1. Thin JDBC client will be really thin: provide network communication
layer and transparently map to native API.
But we have already this in AI 2.x
>> 1.2. Thick JDBC client implementation will be trivial: start client node
and open JDBC connection on the started node.
We have already this in AI 2.x and IMHO this functionality is weird and
need to be removed. No one wants to start new node from DBeaver :)
>> 1.3 JDBC provides sufficient functionality to implement ODBC (need to
investigate: may be thin protocol may be extended to unify JDBC and ODBC).
We already have this in AI 2.x (JDBC and ODBC is mostly unified)


вт, 6 апр. 2021 г. в 15:05, Taras Ledkov <[hidden email]>:

> Hi,
>
> Ignite 3.0 requires a rethinking of the query API.
> We have 'cache.query()' and 'SqlFieldQuery' abstractions at the Ignite
> 2.x native API and several JDBC implementation for clients.
>
> I propose to think about new query/SQL API for the Ignite 3.0.
>
> My vision is something like this:
> Ignite will support two query APIs: standard JDBC (on native) and one of
> reactive DB API.
>
> 1. Native JDBC API, e.g.:
>      Connection conn = node.sql().connection(props);
>
> JDBC is the industrial standard of the DB access and we have to support
> one.
> Also:
> 1.1. Thin JDBC client will be really thin: provide network communication
> layer and transparently map to native API.
> 1.2. Thick JDBC client implementation will be trivial: start client node
> and open JDBC connection on the started node.
> 1.3. JDBC provides sufficient functionality to implement ODBC (need to
> investigate: may be thin protocol may be extended to unify JDBC and ODBC).
>
> 2. About reactive DB API.
> I don't know of any industrial standard API for DB reactive access now.
> There are several candidates:
> 2.1. R2DBC look like the popular and alive. See [1];
> 2.2. ADBA (java.sql2 [2]) looks like not alive. Not sure;
> 2.3. Custom async/reactive API.
> e.g. oracle DB use this way [3].
>
> Igniters, WDYT?
>
> [1]. https://github.com/r2dbc/r2dbc-spi
> [2].
> http://cr.openjdk.java.net/~lancea/apidoc/java/sql2/package-summary.html
> [3].
>
> https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/jdbc-reactive-extensions.html#GUID-1C40C43B-3823-4848-8B5A-D2F97A82F79B
>
> --
> Taras Ledkov
> Mail-To: [hidden email]
>
>

--
Sincerely yours, Ivan Daschinskiy

[Taras Ledkov]

Hi, Ivan.

My comments are inline.

In general about JDBC native API:
I see no reasons why a user must use different query APIs on the client
and on the server node (e.g. inside services or jobs).
It is complicate and not user-friendly.


>>> 1.1. Thin JDBC client will be really thin: provide network communication
> layer and transparently map to native API.
> But we have already this in AI 2.x
Not complete.
JDBC thin is a implementation of the JDBC specification. But it uses
Query API `query(SqlFieldsQuery)`.
I see no reason to introduce new internal query API for 3.0 and wrap it
into JDBC again.
May be we try to use JDBC as native query API?
This way doesn't prohibit to use any internal extension if need.

>>> 1.2. Thick JDBC client implementation will be trivial: start client node
> and open JDBC connection on the started node.
> We have already this in AI 2.x and IMHO this functionality is weird and
> need to be removed. No one wants to start new node from DBeaver :)

I guess it will be removed on Ignite 2.x because implementation is weird
(due to legacy).


>>> 1.3 JDBC provides sufficient functionality to implement ODBC (need to
> investigate: may be thin protocol may be extended to unify JDBC and ODBC).
> We already have this in AI 2.x (JDBC and ODBC is mostly unified)
It is absolutely wrong.
ODBC, JDBC and thin client use the same port and unified head of the
handshake message.
The protocols are different a lot.


On 06.04.2021 15:40, Ivan Daschinsky wrote:

> Hi Taras!
>>> 1.1. Thin JDBC client will be really thin: provide network communication
> layer and transparently map to native API.
> But we have already this in AI 2.x
>>> 1.2. Thick JDBC client implementation will be trivial: start client node
> and open JDBC connection on the started node.
> We have already this in AI 2.x and IMHO this functionality is weird and
> need to be removed. No one wants to start new node from DBeaver :)
>>> 1.3 JDBC provides sufficient functionality to implement ODBC (need to
> investigate: may be thin protocol may be extended to unify JDBC and ODBC).
> We already have this in AI 2.x (JDBC and ODBC is mostly unified)
>
>
> вт, 6 апр. 2021 г. в 15:05, Taras Ledkov <[hidden email]>:
>
>> Hi,
>>
>> Ignite 3.0 requires a rethinking of the query API.
>> We have 'cache.query()' and 'SqlFieldQuery' abstractions at the Ignite
>> 2.x native API and several JDBC implementation for clients.
>>
>> I propose to think about new query/SQL API for the Ignite 3.0.
>>
>> My vision is something like this:
>> Ignite will support two query APIs: standard JDBC (on native) and one of
>> reactive DB API.
>>
>> 1. Native JDBC API, e.g.:
>>       Connection conn = node.sql().connection(props);
>>
>> JDBC is the industrial standard of the DB access and we have to support
>> one.
>> Also:
>> 1.1. Thin JDBC client will be really thin: provide network communication
>> layer and transparently map to native API.
>> 1.2. Thick JDBC client implementation will be trivial: start client node
>> and open JDBC connection on the started node.
>> 1.3. JDBC provides sufficient functionality to implement ODBC (need to
>> investigate: may be thin protocol may be extended to unify JDBC and ODBC).
>>
>> 2. About reactive DB API.
>> I don't know of any industrial standard API for DB reactive access now.
>> There are several candidates:
>> 2.1. R2DBC look like the popular and alive. See [1];
>> 2.2. ADBA (java.sql2 [2]) looks like not alive. Not sure;
>> 2.3. Custom async/reactive API.
>> e.g. oracle DB use this way [3].
>>
>> Igniters, WDYT?
>>
>> [1]. https://github.com/r2dbc/r2dbc-spi
>> [2].
>> http://cr.openjdk.java.net/~lancea/apidoc/java/sql2/package-summary.html
>> [3].
>>
>> https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/jdbc-reactive-extensions.html#GUID-1C40C43B-3823-4848-8B5A-D2F97A82F79B
>>
>> --
>> Taras Ledkov
>> Mail-To: [hidden email]
>>
>>

--
Taras Ledkov
Mail-To: [hidden email]

[Ivan Daschinsky]

>> The protocols are different a lot.
Оf course they are different. And handlersfers, because format different.
are different. But NIO server is the same. This is normal.
If you meant that it was probably possible to create one handler for JDBC
thin and ODBC, it would be probably possible.
But is there any profit from this?
>> May be we try to use JDBC as native query API?
So you suggests something like
```
Connection conn = ignite.getJdbcConnection(<some_params>);
Statement ps = connection.createStatement();
stmt.executeQuery(<sql>);
```
and so on on thick clients?
As for me, jdbc is the most ugly thing I've ever seen. Current API is a way
better.

Do you think about some low level API that can be used to implement JDBC
thin/ ODBC/ thin client handlers, and thick client handlers also?
And over this protocol, it is possible to implement whatever you want.

>> R2DBC look like the popular and alive
Yes, it is great, as for me.




вт, 6 апр. 2021 г. в 15:58, Taras Ledkov <[hidden email]>:

> Hi, Ivan.
>
> My comments are inline.
>
> In general about JDBC native API:
> I see no reasons why a user must use different query APIs on the client
> and on the server node (e.g. inside services or jobs).
> It is complicate and not user-friendly.
>
>
> >>> 1.1. Thin JDBC client will be really thin: provide network
> communication
> > layer and transparently map to native API.
> > But we have already this in AI 2.x
> Not complete.
> JDBC thin is a implementation of the JDBC specification. But it uses
> Query API `query(SqlFieldsQuery)`.
> I see no reason to introduce new internal query API for 3.0 and wrap it
> into JDBC again.
> May be we try to use JDBC as native query API?
> This way doesn't prohibit to use any internal extension if need.
>
> >>> 1.2. Thick JDBC client implementation will be trivial: start client
> node
> > and open JDBC connection on the started node.
> > We have already this in AI 2.x and IMHO this functionality is weird and
> > need to be removed. No one wants to start new node from DBeaver :)
>
> I guess it will be removed on Ignite 2.x because implementation is weird
> (due to legacy).
>
>
> >>> 1.3 JDBC provides sufficient functionality to implement ODBC (need to
> > investigate: may be thin protocol may be extended to unify JDBC and
> ODBC).
> > We already have this in AI 2.x (JDBC and ODBC is mostly unified)
> It is absolutely wrong.
> ODBC, JDBC and thin client use the same port and unified head of the
> handshake message.
> The protocols are different a lot.
>
>
> On 06.04.2021 15:40, Ivan Daschinsky wrote:
> > Hi Taras!
> >>> 1.1. Thin JDBC client will be really thin: provide network
> communication
> > layer and transparently map to native API.
> > But we have already this in AI 2.x
> >>> 1.2. Thick JDBC client implementation will be trivial: start client
> node
> > and open JDBC connection on the started node.
> > We have already this in AI 2.x and IMHO this functionality is weird and
> > need to be removed. No one wants to start new node from DBeaver :)
> >>> 1.3 JDBC provides sufficient functionality to implement ODBC (need to
> > investigate: may be thin protocol may be extended to unify JDBC and
> ODBC).
> > We already have this in AI 2.x (JDBC and ODBC is mostly unified)
> >
> >
> > вт, 6 апр. 2021 г. в 15:05, Taras Ledkov <[hidden email]>:
> >
> >> Hi,
> >>
> >> Ignite 3.0 requires a rethinking of the query API.
> >> We have 'cache.query()' and 'SqlFieldQuery' abstractions at the Ignite
> >> 2.x native API and several JDBC implementation for clients.
> >>
> >> I propose to think about new query/SQL API for the Ignite 3.0.
> >>
> >> My vision is something like this:
> >> Ignite will support two query APIs: standard JDBC (on native) and one of
> >> reactive DB API.
> >>
> >> 1. Native JDBC API, e.g.:
> >>       Connection conn = node.sql().connection(props);
> >>
> >> JDBC is the industrial standard of the DB access and we have to support
> >> one.
> >> Also:
> >> 1.1. Thin JDBC client will be really thin: provide network communication
> >> layer and transparently map to native API.
> >> 1.2. Thick JDBC client implementation will be trivial: start client node
> >> and open JDBC connection on the started node.
> >> 1.3. JDBC provides sufficient functionality to implement ODBC (need to
> >> investigate: may be thin protocol may be extended to unify JDBC and
> ODBC).
> >>
> >> 2. About reactive DB API.
> >> I don't know of any industrial standard API for DB reactive access now.
> >> There are several candidates:
> >> 2.1. R2DBC look like the popular and alive. See [1];
> >> 2.2. ADBA (java.sql2 [2]) looks like not alive. Not sure;
> >> 2.3. Custom async/reactive API.
> >> e.g. oracle DB use this way [3].
> >>
> >> Igniters, WDYT?
> >>
> >> [1]. https://github.com/r2dbc/r2dbc-spi
> >> [2].
> >>
> http://cr.openjdk.java.net/~lancea/apidoc/java/sql2/package-summary.html
> >> [3].
> >>
> >>
> https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/jdbc-reactive-extensions.html#GUID-1C40C43B-3823-4848-8B5A-D2F97A82F79B
> >>
> >> --
> >> Taras Ledkov
> >> Mail-To: [hidden email]
> >>
> >>
> --
> Taras Ledkov
> Mail-To: [hidden email]
>
>

--
Sincerely yours, Ivan Daschinskiy

[Andrew Mashenkov]

Hi Taras.

1. AFAIK, only Thin clients will be available in 3.0.
However, yes, Native JDBC API is still "must have" on servers.

2. We won't have other projects in dependencies if possible. Unless we/they
are Jigsaw compatible though.

2.1  I think it makes sense to be an R2DBC-compatible as it is a widely
used framework. E.g. Spring supports R2DBC [1].
Having an extension with R2DBC support will make Spring integration with
Ignite easy in the future.

If it will be enough to have a dependency on just reactive-streams API [2],
then adding this API to dependencies looks ok to me, as there are only a
few interfaces and converters to/from JDK Flow API.
And the question is would we go with Flow API or Reactive-Streams?

If we need some more than reactive-streams API, then I would suggest to go
with Flow API.

2.2 ADBA is compatible with R2DBC but was discontinued [3].

2.3 I don't think custom API is ever useful. We will need
converters/adapters to Flow or Reactive-streams for easy integration with
other APIs/frameworks.
Otherwise, uses will have difficulties interacting with Ignite in a
reactive way.

[1]
https://spring.io/blog/2018/12/07/reactive-programming-and-relational-databases
[2] https://github.com/reactive-streams/reactive-streams-jvm
[3]
https://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2019-September/000529.html

On Tue, Apr 6, 2021 at 3:05 PM Taras Ledkov <[hidden email]> wrote:

> Hi,
>
> Ignite 3.0 requires a rethinking of the query API.
> We have 'cache.query()' and 'SqlFieldQuery' abstractions at the Ignite
> 2.x native API and several JDBC implementation for clients.
>
> I propose to think about new query/SQL API for the Ignite 3.0.
>
> My vision is something like this:
> Ignite will support two query APIs: standard JDBC (on native) and one of
> reactive DB API.
>
> 1. Native JDBC API, e.g.:
>      Connection conn = node.sql().connection(props);
>
> JDBC is the industrial standard of the DB access and we have to support
> one.
> Also:
> 1.1. Thin JDBC client will be really thin: provide network communication
> layer and transparently map to native API.
> 1.2. Thick JDBC client implementation will be trivial: start client node
> and open JDBC connection on the started node.
> 1.3. JDBC provides sufficient functionality to implement ODBC (need to
> investigate: may be thin protocol may be extended to unify JDBC and ODBC).
>
> 2. About reactive DB API.
> I don't know of any industrial standard API for DB reactive access now.
> There are several candidates:
> 2.1. R2DBC look like the popular and alive. See [1];
> 2.2. ADBA (java.sql2 [2]) looks like not alive. Not sure;
> 2.3. Custom async/reactive API.
> e.g. oracle DB use this way [3].
>
> Igniters, WDYT?
>
> [1]. https://github.com/r2dbc/r2dbc-spi
> [2].
> http://cr.openjdk.java.net/~lancea/apidoc/java/sql2/package-summary.html
> [3].
>
> https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/jdbc-reactive-extensions.html#GUID-1C40C43B-3823-4848-8B5A-D2F97A82F79B
>
> --
> Taras Ledkov
> Mail-To: [hidden email]
>
>

--
Best regards,
Andrey V. Mashenkov