CREATE TABLE usage from Java API, .NET, C++

[dmagda]

Igniters,

Not sure we discussed this before, so let me start a new thread.

It’s claimed the command is supported from native Java, .NET, C++ APIs but I had hard time trying to use it from there. Imagine this simple statement to be called from Java source code:  

SqlFieldsQuery query = new SqlFieldsQuery(
    "CREATE TABLE City (" +
        " id LONG PRIMARY KEY, name VARCHAR)" +
        " WITH \"template=replicated\"");

*cache*.query(query).getAll();

The CT command will be recognized but the *cache* is not the City cache. It’s some other cache existed prior the query execution.

In my scenario I’ve planned to show how to configure the schema from scratch on an empty cluster and had to apply the following workaround instantiating a dummy cache:

IgniteCache cache = ignite.getOrCreateCache(new CacheConfiguration<>("default").setIndexedTypes(Long.class,
    Long.class).setSqlSchema("PUBLIC"));


How do we want to improve this poor experience? My options are:

1) Introduce some static API (cache-reference-free) method for quiries like that.
2) Deploy the cache prior that call and pass its name into “WITH” statement.

—
Denis

[Valentin Kulichenko]

Ideally, SQL API has to be completely decoupled from cache API. Otherwise
we will keep getting issues like this.

let's introduce top level API (IgniteSql?) and add everything there.

-Val

On Tue, Aug 29, 2017 at 7:47 PM Denis Magda <[hidden email]> wrote:

> Igniters,
>
> Not sure we discussed this before, so let me start a new thread.
>
> It’s claimed the command is supported from native Java, .NET, C++ APIs but
> I had hard time trying to use it from there. Imagine this simple statement
> to be called from Java source code:
>
> SqlFieldsQuery query = new SqlFieldsQuery(
>     "CREATE TABLE City (" +
>         " id LONG PRIMARY KEY, name VARCHAR)" +
>         " WITH \"template=replicated\"");
>
> *cache*.query(query).getAll();
>
> The CT command will be recognized but the *cache* is not the City cache.
> It’s some other cache existed prior the query execution.
>
> In my scenario I’ve planned to show how to configure the schema from
> scratch on an empty cluster and had to apply the following workaround
> instantiating a dummy cache:
>
> IgniteCache cache = ignite.getOrCreateCache(new
> CacheConfiguration<>("default").setIndexedTypes(Long.class,
>     Long.class).setSqlSchema("PUBLIC"));
>
>
> How do we want to improve this poor experience? My options are:
>
> 1) Introduce some static API (cache-reference-free) method for quiries
> like that.
> 2) Deploy the cache prior that call and pass its name into “WITH”
> statement.
>
> —
> Denis

[Vladimir Ozerov]

Folks,

Please see this [1] and this [2].

[1]
http://apache-ignite-developers.2346864.n4.nabble.com/Rethink-native-SQL-API-in-Apache-Ignite-2-0-td14335.html
[2] https://issues.apache.org/jira/browse/IGNITE-4701

On Wed, Aug 30, 2017 at 3:10 AM, Valentin Kulichenko <
[hidden email]> wrote:

> Ideally, SQL API has to be completely decoupled from cache API. Otherwise
> we will keep getting issues like this.
>
> let's introduce top level API (IgniteSql?) and add everything there.
>
> -Val
>
> On Tue, Aug 29, 2017 at 7:47 PM Denis Magda <[hidden email]> wrote:
>
> > Igniters,
> >
> > Not sure we discussed this before, so let me start a new thread.
> >
> > It’s claimed the command is supported from native Java, .NET, C++ APIs
> but
> > I had hard time trying to use it from there. Imagine this simple
> statement
> > to be called from Java source code:
> >
> > SqlFieldsQuery query = new SqlFieldsQuery(
> >     "CREATE TABLE City (" +
> >         " id LONG PRIMARY KEY, name VARCHAR)" +
> >         " WITH \"template=replicated\"");
> >
> > *cache*.query(query).getAll();
> >
> > The CT command will be recognized but the *cache* is not the City cache.
> > It’s some other cache existed prior the query execution.
> >
> > In my scenario I’ve planned to show how to configure the schema from
> > scratch on an empty cluster and had to apply the following workaround
> > instantiating a dummy cache:
> >
> > IgniteCache cache = ignite.getOrCreateCache(new
> > CacheConfiguration<>("default").setIndexedTypes(Long.class,
> >     Long.class).setSqlSchema("PUBLIC"));
> >
> >
> > How do we want to improve this poor experience? My options are:
> >
> > 1) Introduce some static API (cache-reference-free) method for quiries
> > like that.
> > 2) Deploy the cache prior that call and pass its name into “WITH”
> > statement.
> >
> > —
> > Denis
>

[dmagda]

I knew we discussed this some day ago. Put a reference to this discussion into the ticket. It’s a good example of a usability issue we can solve with the new API.

—
Denis

> On Aug 30, 2017, at 2:51 AM, Vladimir Ozerov <[hidden email]> wrote:
>
> Folks,
>
> Please see this [1] and this [2].
>
> [1]
> http://apache-ignite-developers.2346864.n4.nabble.com/Rethink-native-SQL-API-in-Apache-Ignite-2-0-td14335.html
> [2] https://issues.apache.org/jira/browse/IGNITE-4701
>
> On Wed, Aug 30, 2017 at 3:10 AM, Valentin Kulichenko <
> [hidden email]> wrote:
>
>> Ideally, SQL API has to be completely decoupled from cache API. Otherwise
>> we will keep getting issues like this.
>>
>> let's introduce top level API (IgniteSql?) and add everything there.
>>
>> -Val
>>
>> On Tue, Aug 29, 2017 at 7:47 PM Denis Magda <[hidden email]> wrote:
>>
>>> Igniters,
>>>
>>> Not sure we discussed this before, so let me start a new thread.
>>>
>>> It’s claimed the command is supported from native Java, .NET, C++ APIs
>> but
>>> I had hard time trying to use it from there. Imagine this simple
>> statement
>>> to be called from Java source code:
>>>
>>> SqlFieldsQuery query = new SqlFieldsQuery(
>>>    "CREATE TABLE City (" +
>>>        " id LONG PRIMARY KEY, name VARCHAR)" +
>>>        " WITH \"template=replicated\"");
>>>
>>> *cache*.query(query).getAll();
>>>
>>> The CT command will be recognized but the *cache* is not the City cache.
>>> It’s some other cache existed prior the query execution.
>>>
>>> In my scenario I’ve planned to show how to configure the schema from
>>> scratch on an empty cluster and had to apply the following workaround
>>> instantiating a dummy cache:
>>>
>>> IgniteCache cache = ignite.getOrCreateCache(new
>>> CacheConfiguration<>("default").setIndexedTypes(Long.class,
>>>    Long.class).setSqlSchema("PUBLIC"));
>>>
>>>
>>> How do we want to improve this poor experience? My options are:
>>>
>>> 1) Introduce some static API (cache-reference-free) method for quiries
>>> like that.
>>> 2) Deploy the cache prior that call and pass its name into “WITH”
>>> statement.
>>>
>>> —
>>> Denis
>>