DDL implementation details

classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

DDL implementation details

al.psc
Hello Igniters,

I would like to start discussion about implementation of SQL DDL commands.

At the first stage, the most important ones seem to be CREATE TABLE
(that will obviously correspond to creation of a cache) and CREATE
INDEX.

Regarding first one: SQL command for CREATE TABLE does not contain any
hints about cache settings (atomicity, replication, etc.), so these
will probably be defined by some configuration properties (like
ignite.ddl.default_cache_atomiticity, etc).

Also it does not allow to distinguish between key and value columns -
currently it is handled by keyFields property of QueryEntity, but it
is unclear how to declare key fields via CREATE TABLE.

So at a first glance it seems like we should either implement some
sort of custom parsing (I believe Sergi will be against it) or
introduce some kind of name prefix that would tell SQL engine that
certain column is a key field column.

Of course, this problem disappears is key is of SQL type.

Regarding CREATE INDEX: probably at first we will have to implement
this in "stop-the-world" manner, i.e. all cache will be blocked during
the index's initial buildup.

Any thoughts?

Currently I'm working on parsing of those commands as that will be
needed anyway and does not affect further implementation.

- Alex
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Alexey Kuznetsov
Hi, Alex!

As far as I know most RDBMS allow something like: create table t1 (id
integer primary key, ....)
How about to take as key field that marked as "primary key"?

As of atomicity and replication - I think it is a cache properties and with
create table we will create "types" in cache. No?
I thought that cache it is a kind of "schema" in RDBMS.

Could you describe what will be created with CREATE TABLE?

On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
[hidden email]> wrote:

> Hello Igniters,
>
> I would like to start discussion about implementation of SQL DDL commands.
>
> At the first stage, the most important ones seem to be CREATE TABLE
> (that will obviously correspond to creation of a cache) and CREATE
> INDEX.
>
> Regarding first one: SQL command for CREATE TABLE does not contain any
> hints about cache settings (atomicity, replication, etc.), so these
> will probably be defined by some configuration properties (like
> ignite.ddl.default_cache_atomiticity, etc).
>
> Also it does not allow to distinguish between key and value columns -
> currently it is handled by keyFields property of QueryEntity, but it
> is unclear how to declare key fields via CREATE TABLE.
>
> So at a first glance it seems like we should either implement some
> sort of custom parsing (I believe Sergi will be against it) or
> introduce some kind of name prefix that would tell SQL engine that
> certain column is a key field column.
>
> Of course, this problem disappears is key is of SQL type.
>
> Regarding CREATE INDEX: probably at first we will have to implement
> this in "stop-the-world" manner, i.e. all cache will be blocked during
> the index's initial buildup.
>
> Any thoughts?
>
> Currently I'm working on parsing of those commands as that will be
> needed anyway and does not affect further implementation.
>
> - Alex
>



--
Alexey Kuznetsov
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Vladimir Ozerov
I believe custom synthax and parsing is a *must* for us, as well as for any
distributed database. At the very least we need to specify affinity key
column somehow. Any cache property can be specified at the very end of
table definition. Key columns can be determined as the ones with PRIMARY
KEY constraint (Alex K. idea) + affinity column(s):

CREATE TABLE employee (
    id BIGINT PRIMARY KEY,
    dept_id BIGINT AFFINITY KEY,
    name VARCHAR(128),
    address VARCHAR(256)
    BACKUPS 2,
    ATOMICITY_MODE ATOMIC,
);

"id" and "dept_id" form key type, "name" and "address" form value type.

Vladimir.

On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <[hidden email]>
wrote:

> Hi, Alex!
>
> As far as I know most RDBMS allow something like: create table t1 (id
> integer primary key, ....)
> How about to take as key field that marked as "primary key"?
>
> As of atomicity and replication - I think it is a cache properties and with
> create table we will create "types" in cache. No?
> I thought that cache it is a kind of "schema" in RDBMS.
>
> Could you describe what will be created with CREATE TABLE?
>
> On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> [hidden email]> wrote:
>
> > Hello Igniters,
> >
> > I would like to start discussion about implementation of SQL DDL
> commands.
> >
> > At the first stage, the most important ones seem to be CREATE TABLE
> > (that will obviously correspond to creation of a cache) and CREATE
> > INDEX.
> >
> > Regarding first one: SQL command for CREATE TABLE does not contain any
> > hints about cache settings (atomicity, replication, etc.), so these
> > will probably be defined by some configuration properties (like
> > ignite.ddl.default_cache_atomiticity, etc).
> >
> > Also it does not allow to distinguish between key and value columns -
> > currently it is handled by keyFields property of QueryEntity, but it
> > is unclear how to declare key fields via CREATE TABLE.
> >
> > So at a first glance it seems like we should either implement some
> > sort of custom parsing (I believe Sergi will be against it) or
> > introduce some kind of name prefix that would tell SQL engine that
> > certain column is a key field column.
> >
> > Of course, this problem disappears is key is of SQL type.
> >
> > Regarding CREATE INDEX: probably at first we will have to implement
> > this in "stop-the-world" manner, i.e. all cache will be blocked during
> > the index's initial buildup.
> >
> > Any thoughts?
> >
> > Currently I'm working on parsing of those commands as that will be
> > needed anyway and does not affect further implementation.
> >
> > - Alex
> >
>
>
>
> --
> Alexey Kuznetsov
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Sergey Kozlov
Hi

I suppose we should put any ignite cache properties as additional
non-standard attributes after CREATE TABLE () clause as it does Postgress,
MySQL and other RDBMS.
Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for CREATE
TABLE with using PARTITIONS (MySQL).





On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <[hidden email]>
wrote:

> I believe custom synthax and parsing is a *must* for us, as well as for any
> distributed database. At the very least we need to specify affinity key
> column somehow. Any cache property can be specified at the very end of
> table definition. Key columns can be determined as the ones with PRIMARY
> KEY constraint (Alex K. idea) + affinity column(s):
>
> CREATE TABLE employee (
>     id BIGINT PRIMARY KEY,
>     dept_id BIGINT AFFINITY KEY,
>     name VARCHAR(128),
>     address VARCHAR(256)
>     BACKUPS 2,
>     ATOMICITY_MODE ATOMIC,
> );
>
> "id" and "dept_id" form key type, "name" and "address" form value type.
>
> Vladimir.
>
> On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <[hidden email]>
> wrote:
>
> > Hi, Alex!
> >
> > As far as I know most RDBMS allow something like: create table t1 (id
> > integer primary key, ....)
> > How about to take as key field that marked as "primary key"?
> >
> > As of atomicity and replication - I think it is a cache properties and
> with
> > create table we will create "types" in cache. No?
> > I thought that cache it is a kind of "schema" in RDBMS.
> >
> > Could you describe what will be created with CREATE TABLE?
> >
> > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> > [hidden email]> wrote:
> >
> > > Hello Igniters,
> > >
> > > I would like to start discussion about implementation of SQL DDL
> > commands.
> > >
> > > At the first stage, the most important ones seem to be CREATE TABLE
> > > (that will obviously correspond to creation of a cache) and CREATE
> > > INDEX.
> > >
> > > Regarding first one: SQL command for CREATE TABLE does not contain any
> > > hints about cache settings (atomicity, replication, etc.), so these
> > > will probably be defined by some configuration properties (like
> > > ignite.ddl.default_cache_atomiticity, etc).
> > >
> > > Also it does not allow to distinguish between key and value columns -
> > > currently it is handled by keyFields property of QueryEntity, but it
> > > is unclear how to declare key fields via CREATE TABLE.
> > >
> > > So at a first glance it seems like we should either implement some
> > > sort of custom parsing (I believe Sergi will be against it) or
> > > introduce some kind of name prefix that would tell SQL engine that
> > > certain column is a key field column.
> > >
> > > Of course, this problem disappears is key is of SQL type.
> > >
> > > Regarding CREATE INDEX: probably at first we will have to implement
> > > this in "stop-the-world" manner, i.e. all cache will be blocked during
> > > the index's initial buildup.
> > >
> > > Any thoughts?
> > >
> > > Currently I'm working on parsing of those commands as that will be
> > > needed anyway and does not affect further implementation.
> > >
> > > - Alex
> > >
> >
> >
> >
> > --
> > Alexey Kuznetsov
> >
>



--
Sergey Kozlov
GridGain Systems
www.gridgain.com
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

dsetrakyan
Agree with Sergey. We should be able to specify cache properties inside of
SQL statements. Does H2 have any support to process SQL hints? Can we
change it?

Having said that, while we finalize the above, I think we should start
working on DDL implementation to use the default settings, as specified in
Alexander's email.

Also agree with the stop-the-world on the cache for index creation. We can
always improve on it in future.

D.

On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <[hidden email]>
wrote:

> Hi
>
> I suppose we should put any ignite cache properties as additional
> non-standard attributes after CREATE TABLE () clause as it does Postgress,
> MySQL and other RDBMS.
> Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for CREATE
> TABLE with using PARTITIONS (MySQL).
>
>
>
>
>
> On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <[hidden email]>
> wrote:
>
> > I believe custom synthax and parsing is a *must* for us, as well as for
> any
> > distributed database. At the very least we need to specify affinity key
> > column somehow. Any cache property can be specified at the very end of
> > table definition. Key columns can be determined as the ones with PRIMARY
> > KEY constraint (Alex K. idea) + affinity column(s):
> >
> > CREATE TABLE employee (
> >     id BIGINT PRIMARY KEY,
> >     dept_id BIGINT AFFINITY KEY,
> >     name VARCHAR(128),
> >     address VARCHAR(256)
> >     BACKUPS 2,
> >     ATOMICITY_MODE ATOMIC,
> > );
> >
> > "id" and "dept_id" form key type, "name" and "address" form value type.
> >
> > Vladimir.
> >
> > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <[hidden email]
> >
> > wrote:
> >
> > > Hi, Alex!
> > >
> > > As far as I know most RDBMS allow something like: create table t1 (id
> > > integer primary key, ....)
> > > How about to take as key field that marked as "primary key"?
> > >
> > > As of atomicity and replication - I think it is a cache properties and
> > with
> > > create table we will create "types" in cache. No?
> > > I thought that cache it is a kind of "schema" in RDBMS.
> > >
> > > Could you describe what will be created with CREATE TABLE?
> > >
> > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> > > [hidden email]> wrote:
> > >
> > > > Hello Igniters,
> > > >
> > > > I would like to start discussion about implementation of SQL DDL
> > > commands.
> > > >
> > > > At the first stage, the most important ones seem to be CREATE TABLE
> > > > (that will obviously correspond to creation of a cache) and CREATE
> > > > INDEX.
> > > >
> > > > Regarding first one: SQL command for CREATE TABLE does not contain
> any
> > > > hints about cache settings (atomicity, replication, etc.), so these
> > > > will probably be defined by some configuration properties (like
> > > > ignite.ddl.default_cache_atomiticity, etc).
> > > >
> > > > Also it does not allow to distinguish between key and value columns -
> > > > currently it is handled by keyFields property of QueryEntity, but it
> > > > is unclear how to declare key fields via CREATE TABLE.
> > > >
> > > > So at a first glance it seems like we should either implement some
> > > > sort of custom parsing (I believe Sergi will be against it) or
> > > > introduce some kind of name prefix that would tell SQL engine that
> > > > certain column is a key field column.
> > > >
> > > > Of course, this problem disappears is key is of SQL type.
> > > >
> > > > Regarding CREATE INDEX: probably at first we will have to implement
> > > > this in "stop-the-world" manner, i.e. all cache will be blocked
> during
> > > > the index's initial buildup.
> > > >
> > > > Any thoughts?
> > > >
> > > > Currently I'm working on parsing of those commands as that will be
> > > > needed anyway and does not affect further implementation.
> > > >
> > > > - Alex
> > > >
> > >
> > >
> > >
> > > --
> > > Alexey Kuznetsov
> > >
> >
>
>
>
> --
> Sergey Kozlov
> GridGain Systems
> www.gridgain.com
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Sergey Kozlov
As first stage of DDL we can implement following CREATE TABLE statement
support:
 - CREATE TABLE without cache properties (use default cache properties or
cache properties defined in SQL Schema)
 - CREATE TABLE .. LIKE where we can create a cache based on an another
existing cache.

On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <[hidden email]>
wrote:

> Agree with Sergey. We should be able to specify cache properties inside of
> SQL statements. Does H2 have any support to process SQL hints? Can we
> change it?
>
> Having said that, while we finalize the above, I think we should start
> working on DDL implementation to use the default settings, as specified in
> Alexander's email.
>
> Also agree with the stop-the-world on the cache for index creation. We can
> always improve on it in future.
>
> D.
>
> On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <[hidden email]>
> wrote:
>
> > Hi
> >
> > I suppose we should put any ignite cache properties as additional
> > non-standard attributes after CREATE TABLE () clause as it does
> Postgress,
> > MySQL and other RDBMS.
> > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
> CREATE
> > TABLE with using PARTITIONS (MySQL).
> >
> >
> >
> >
> >
> > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <[hidden email]>
> > wrote:
> >
> > > I believe custom synthax and parsing is a *must* for us, as well as for
> > any
> > > distributed database. At the very least we need to specify affinity key
> > > column somehow. Any cache property can be specified at the very end of
> > > table definition. Key columns can be determined as the ones with
> PRIMARY
> > > KEY constraint (Alex K. idea) + affinity column(s):
> > >
> > > CREATE TABLE employee (
> > >     id BIGINT PRIMARY KEY,
> > >     dept_id BIGINT AFFINITY KEY,
> > >     name VARCHAR(128),
> > >     address VARCHAR(256)
> > >     BACKUPS 2,
> > >     ATOMICITY_MODE ATOMIC,
> > > );
> > >
> > > "id" and "dept_id" form key type, "name" and "address" form value type.
> > >
> > > Vladimir.
> > >
> > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> [hidden email]
> > >
> > > wrote:
> > >
> > > > Hi, Alex!
> > > >
> > > > As far as I know most RDBMS allow something like: create table t1 (id
> > > > integer primary key, ....)
> > > > How about to take as key field that marked as "primary key"?
> > > >
> > > > As of atomicity and replication - I think it is a cache properties
> and
> > > with
> > > > create table we will create "types" in cache. No?
> > > > I thought that cache it is a kind of "schema" in RDBMS.
> > > >
> > > > Could you describe what will be created with CREATE TABLE?
> > > >
> > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> > > > [hidden email]> wrote:
> > > >
> > > > > Hello Igniters,
> > > > >
> > > > > I would like to start discussion about implementation of SQL DDL
> > > > commands.
> > > > >
> > > > > At the first stage, the most important ones seem to be CREATE TABLE
> > > > > (that will obviously correspond to creation of a cache) and CREATE
> > > > > INDEX.
> > > > >
> > > > > Regarding first one: SQL command for CREATE TABLE does not contain
> > any
> > > > > hints about cache settings (atomicity, replication, etc.), so these
> > > > > will probably be defined by some configuration properties (like
> > > > > ignite.ddl.default_cache_atomiticity, etc).
> > > > >
> > > > > Also it does not allow to distinguish between key and value
> columns -
> > > > > currently it is handled by keyFields property of QueryEntity, but
> it
> > > > > is unclear how to declare key fields via CREATE TABLE.
> > > > >
> > > > > So at a first glance it seems like we should either implement some
> > > > > sort of custom parsing (I believe Sergi will be against it) or
> > > > > introduce some kind of name prefix that would tell SQL engine that
> > > > > certain column is a key field column.
> > > > >
> > > > > Of course, this problem disappears is key is of SQL type.
> > > > >
> > > > > Regarding CREATE INDEX: probably at first we will have to implement
> > > > > this in "stop-the-world" manner, i.e. all cache will be blocked
> > during
> > > > > the index's initial buildup.
> > > > >
> > > > > Any thoughts?
> > > > >
> > > > > Currently I'm working on parsing of those commands as that will be
> > > > > needed anyway and does not affect further implementation.
> > > > >
> > > > > - Alex
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Alexey Kuznetsov
> > > >
> > >
> >
> >
> >
> > --
> > Sergey Kozlov
> > GridGain Systems
> > www.gridgain.com
> >
>



--
Sergey Kozlov
GridGain Systems
www.gridgain.com
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Alexey Goncharuk
Alexander,

Will we keep the old option to have multiple tables in one cache? If so,
how will create table statement know which cache to choose?

It seems to me that to be consistent with the current DML implementation we
should have a CREATE SCHEMA statement which will define the cache and cache
configuration, and CREATE TABLE should specify the schema name.

Otherwise, we should enforce the single type per cache rule at the
configuration level and in runtime.

As for affinity and primary key - agree with Vladimir.

--
AG

2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:

> As first stage of DDL we can implement following CREATE TABLE statement
> support:
>  - CREATE TABLE without cache properties (use default cache properties or
> cache properties defined in SQL Schema)
>  - CREATE TABLE .. LIKE where we can create a cache based on an another
> existing cache.
>
> On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <[hidden email]>
> wrote:
>
> > Agree with Sergey. We should be able to specify cache properties inside
> of
> > SQL statements. Does H2 have any support to process SQL hints? Can we
> > change it?
> >
> > Having said that, while we finalize the above, I think we should start
> > working on DDL implementation to use the default settings, as specified
> in
> > Alexander's email.
> >
> > Also agree with the stop-the-world on the cache for index creation. We
> can
> > always improve on it in future.
> >
> > D.
> >
> > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <[hidden email]>
> > wrote:
> >
> > > Hi
> > >
> > > I suppose we should put any ignite cache properties as additional
> > > non-standard attributes after CREATE TABLE () clause as it does
> > Postgress,
> > > MySQL and other RDBMS.
> > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
> > CREATE
> > > TABLE with using PARTITIONS (MySQL).
> > >
> > >
> > >
> > >
> > >
> > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
> [hidden email]>
> > > wrote:
> > >
> > > > I believe custom synthax and parsing is a *must* for us, as well as
> for
> > > any
> > > > distributed database. At the very least we need to specify affinity
> key
> > > > column somehow. Any cache property can be specified at the very end
> of
> > > > table definition. Key columns can be determined as the ones with
> > PRIMARY
> > > > KEY constraint (Alex K. idea) + affinity column(s):
> > > >
> > > > CREATE TABLE employee (
> > > >     id BIGINT PRIMARY KEY,
> > > >     dept_id BIGINT AFFINITY KEY,
> > > >     name VARCHAR(128),
> > > >     address VARCHAR(256)
> > > >     BACKUPS 2,
> > > >     ATOMICITY_MODE ATOMIC,
> > > > );
> > > >
> > > > "id" and "dept_id" form key type, "name" and "address" form value
> type.
> > > >
> > > > Vladimir.
> > > >
> > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> > [hidden email]
> > > >
> > > > wrote:
> > > >
> > > > > Hi, Alex!
> > > > >
> > > > > As far as I know most RDBMS allow something like: create table t1
> (id
> > > > > integer primary key, ....)
> > > > > How about to take as key field that marked as "primary key"?
> > > > >
> > > > > As of atomicity and replication - I think it is a cache properties
> > and
> > > > with
> > > > > create table we will create "types" in cache. No?
> > > > > I thought that cache it is a kind of "schema" in RDBMS.
> > > > >
> > > > > Could you describe what will be created with CREATE TABLE?
> > > > >
> > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> > > > > [hidden email]> wrote:
> > > > >
> > > > > > Hello Igniters,
> > > > > >
> > > > > > I would like to start discussion about implementation of SQL DDL
> > > > > commands.
> > > > > >
> > > > > > At the first stage, the most important ones seem to be CREATE
> TABLE
> > > > > > (that will obviously correspond to creation of a cache) and
> CREATE
> > > > > > INDEX.
> > > > > >
> > > > > > Regarding first one: SQL command for CREATE TABLE does not
> contain
> > > any
> > > > > > hints about cache settings (atomicity, replication, etc.), so
> these
> > > > > > will probably be defined by some configuration properties (like
> > > > > > ignite.ddl.default_cache_atomiticity, etc).
> > > > > >
> > > > > > Also it does not allow to distinguish between key and value
> > columns -
> > > > > > currently it is handled by keyFields property of QueryEntity, but
> > it
> > > > > > is unclear how to declare key fields via CREATE TABLE.
> > > > > >
> > > > > > So at a first glance it seems like we should either implement
> some
> > > > > > sort of custom parsing (I believe Sergi will be against it) or
> > > > > > introduce some kind of name prefix that would tell SQL engine
> that
> > > > > > certain column is a key field column.
> > > > > >
> > > > > > Of course, this problem disappears is key is of SQL type.
> > > > > >
> > > > > > Regarding CREATE INDEX: probably at first we will have to
> implement
> > > > > > this in "stop-the-world" manner, i.e. all cache will be blocked
> > > during
> > > > > > the index's initial buildup.
> > > > > >
> > > > > > Any thoughts?
> > > > > >
> > > > > > Currently I'm working on parsing of those commands as that will
> be
> > > > > > needed anyway and does not affect further implementation.
> > > > > >
> > > > > > - Alex
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Alexey Kuznetsov
> > > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Sergey Kozlov
> > > GridGain Systems
> > > www.gridgain.com
> > >
> >
>
>
>
> --
> Sergey Kozlov
> GridGain Systems
> www.gridgain.com
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Vladimir Ozerov
I am afraid in this case user will have to define too much schemes -
boilerplate.
Does it make sense at all to pack multiple tuples into a single cache from
user perspective?

On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
[hidden email]> wrote:

> Alexander,
>
> Will we keep the old option to have multiple tables in one cache? If so,
> how will create table statement know which cache to choose?
>
> It seems to me that to be consistent with the current DML implementation we
> should have a CREATE SCHEMA statement which will define the cache and cache
> configuration, and CREATE TABLE should specify the schema name.
>
> Otherwise, we should enforce the single type per cache rule at the
> configuration level and in runtime.
>
> As for affinity and primary key - agree with Vladimir.
>
> --
> AG
>
> 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
>
> > As first stage of DDL we can implement following CREATE TABLE statement
> > support:
> >  - CREATE TABLE without cache properties (use default cache properties or
> > cache properties defined in SQL Schema)
> >  - CREATE TABLE .. LIKE where we can create a cache based on an another
> > existing cache.
> >
> > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
> [hidden email]>
> > wrote:
> >
> > > Agree with Sergey. We should be able to specify cache properties inside
> > of
> > > SQL statements. Does H2 have any support to process SQL hints? Can we
> > > change it?
> > >
> > > Having said that, while we finalize the above, I think we should start
> > > working on DDL implementation to use the default settings, as specified
> > in
> > > Alexander's email.
> > >
> > > Also agree with the stop-the-world on the cache for index creation. We
> > can
> > > always improve on it in future.
> > >
> > > D.
> > >
> > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <[hidden email]>
> > > wrote:
> > >
> > > > Hi
> > > >
> > > > I suppose we should put any ignite cache properties as additional
> > > > non-standard attributes after CREATE TABLE () clause as it does
> > > Postgress,
> > > > MySQL and other RDBMS.
> > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
> > > CREATE
> > > > TABLE with using PARTITIONS (MySQL).
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
> > [hidden email]>
> > > > wrote:
> > > >
> > > > > I believe custom synthax and parsing is a *must* for us, as well as
> > for
> > > > any
> > > > > distributed database. At the very least we need to specify affinity
> > key
> > > > > column somehow. Any cache property can be specified at the very end
> > of
> > > > > table definition. Key columns can be determined as the ones with
> > > PRIMARY
> > > > > KEY constraint (Alex K. idea) + affinity column(s):
> > > > >
> > > > > CREATE TABLE employee (
> > > > >     id BIGINT PRIMARY KEY,
> > > > >     dept_id BIGINT AFFINITY KEY,
> > > > >     name VARCHAR(128),
> > > > >     address VARCHAR(256)
> > > > >     BACKUPS 2,
> > > > >     ATOMICITY_MODE ATOMIC,
> > > > > );
> > > > >
> > > > > "id" and "dept_id" form key type, "name" and "address" form value
> > type.
> > > > >
> > > > > Vladimir.
> > > > >
> > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> > > [hidden email]
> > > > >
> > > > > wrote:
> > > > >
> > > > > > Hi, Alex!
> > > > > >
> > > > > > As far as I know most RDBMS allow something like: create table t1
> > (id
> > > > > > integer primary key, ....)
> > > > > > How about to take as key field that marked as "primary key"?
> > > > > >
> > > > > > As of atomicity and replication - I think it is a cache
> properties
> > > and
> > > > > with
> > > > > > create table we will create "types" in cache. No?
> > > > > > I thought that cache it is a kind of "schema" in RDBMS.
> > > > > >
> > > > > > Could you describe what will be created with CREATE TABLE?
> > > > > >
> > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> > > > > > [hidden email]> wrote:
> > > > > >
> > > > > > > Hello Igniters,
> > > > > > >
> > > > > > > I would like to start discussion about implementation of SQL
> DDL
> > > > > > commands.
> > > > > > >
> > > > > > > At the first stage, the most important ones seem to be CREATE
> > TABLE
> > > > > > > (that will obviously correspond to creation of a cache) and
> > CREATE
> > > > > > > INDEX.
> > > > > > >
> > > > > > > Regarding first one: SQL command for CREATE TABLE does not
> > contain
> > > > any
> > > > > > > hints about cache settings (atomicity, replication, etc.), so
> > these
> > > > > > > will probably be defined by some configuration properties (like
> > > > > > > ignite.ddl.default_cache_atomiticity, etc).
> > > > > > >
> > > > > > > Also it does not allow to distinguish between key and value
> > > columns -
> > > > > > > currently it is handled by keyFields property of QueryEntity,
> but
> > > it
> > > > > > > is unclear how to declare key fields via CREATE TABLE.
> > > > > > >
> > > > > > > So at a first glance it seems like we should either implement
> > some
> > > > > > > sort of custom parsing (I believe Sergi will be against it) or
> > > > > > > introduce some kind of name prefix that would tell SQL engine
> > that
> > > > > > > certain column is a key field column.
> > > > > > >
> > > > > > > Of course, this problem disappears is key is of SQL type.
> > > > > > >
> > > > > > > Regarding CREATE INDEX: probably at first we will have to
> > implement
> > > > > > > this in "stop-the-world" manner, i.e. all cache will be blocked
> > > > during
> > > > > > > the index's initial buildup.
> > > > > > >
> > > > > > > Any thoughts?
> > > > > > >
> > > > > > > Currently I'm working on parsing of those commands as that will
> > be
> > > > > > > needed anyway and does not affect further implementation.
> > > > > > >
> > > > > > > - Alex
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Alexey Kuznetsov
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Sergey Kozlov
> > > > GridGain Systems
> > > > www.gridgain.com
> > > >
> > >
> >
> >
> >
> > --
> > Sergey Kozlov
> > GridGain Systems
> > www.gridgain.com
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Sergi
Hi,

1. For now I'm against inventing any custom SQL syntax and implementing
parsing.
Currently H2 supports the following syntax:

CREATE TABLE test(...) WITH "myCustomParamString"

This is enough for us to pass the needed parameters.

2. Agree with AG, we have to separate cache creation from table creation.
Cache == SQL schema for us. We just have to add the same WITH syntax in H2
for schema creation like this:

CREATE SCHEMA "MyCacheName" WITH "cacheConfig=myCache.xml"

3. If we want to create tables then I suggest to put this functionality to
2.0+PageMemory right away and think where and how we are going to store all
the related metadata.This is especially important for persistent storages.

Sergi


2017-01-12 16:56 GMT+03:00 Vladimir Ozerov <[hidden email]>:

> I am afraid in this case user will have to define too much schemes -
> boilerplate.
> Does it make sense at all to pack multiple tuples into a single cache from
> user perspective?
>
> On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
> [hidden email]> wrote:
>
> > Alexander,
> >
> > Will we keep the old option to have multiple tables in one cache? If so,
> > how will create table statement know which cache to choose?
> >
> > It seems to me that to be consistent with the current DML implementation
> we
> > should have a CREATE SCHEMA statement which will define the cache and
> cache
> > configuration, and CREATE TABLE should specify the schema name.
> >
> > Otherwise, we should enforce the single type per cache rule at the
> > configuration level and in runtime.
> >
> > As for affinity and primary key - agree with Vladimir.
> >
> > --
> > AG
> >
> > 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
> >
> > > As first stage of DDL we can implement following CREATE TABLE statement
> > > support:
> > >  - CREATE TABLE without cache properties (use default cache properties
> or
> > > cache properties defined in SQL Schema)
> > >  - CREATE TABLE .. LIKE where we can create a cache based on an another
> > > existing cache.
> > >
> > > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
> > [hidden email]>
> > > wrote:
> > >
> > > > Agree with Sergey. We should be able to specify cache properties
> inside
> > > of
> > > > SQL statements. Does H2 have any support to process SQL hints? Can we
> > > > change it?
> > > >
> > > > Having said that, while we finalize the above, I think we should
> start
> > > > working on DDL implementation to use the default settings, as
> specified
> > > in
> > > > Alexander's email.
> > > >
> > > > Also agree with the stop-the-world on the cache for index creation.
> We
> > > can
> > > > always improve on it in future.
> > > >
> > > > D.
> > > >
> > > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <
> [hidden email]>
> > > > wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I suppose we should put any ignite cache properties as additional
> > > > > non-standard attributes after CREATE TABLE () clause as it does
> > > > Postgress,
> > > > > MySQL and other RDBMS.
> > > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
> > > > CREATE
> > > > > TABLE with using PARTITIONS (MySQL).
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
> > > [hidden email]>
> > > > > wrote:
> > > > >
> > > > > > I believe custom synthax and parsing is a *must* for us, as well
> as
> > > for
> > > > > any
> > > > > > distributed database. At the very least we need to specify
> affinity
> > > key
> > > > > > column somehow. Any cache property can be specified at the very
> end
> > > of
> > > > > > table definition. Key columns can be determined as the ones with
> > > > PRIMARY
> > > > > > KEY constraint (Alex K. idea) + affinity column(s):
> > > > > >
> > > > > > CREATE TABLE employee (
> > > > > >     id BIGINT PRIMARY KEY,
> > > > > >     dept_id BIGINT AFFINITY KEY,
> > > > > >     name VARCHAR(128),
> > > > > >     address VARCHAR(256)
> > > > > >     BACKUPS 2,
> > > > > >     ATOMICITY_MODE ATOMIC,
> > > > > > );
> > > > > >
> > > > > > "id" and "dept_id" form key type, "name" and "address" form value
> > > type.
> > > > > >
> > > > > > Vladimir.
> > > > > >
> > > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> > > > [hidden email]
> > > > > >
> > > > > > wrote:
> > > > > >
> > > > > > > Hi, Alex!
> > > > > > >
> > > > > > > As far as I know most RDBMS allow something like: create table
> t1
> > > (id
> > > > > > > integer primary key, ....)
> > > > > > > How about to take as key field that marked as "primary key"?
> > > > > > >
> > > > > > > As of atomicity and replication - I think it is a cache
> > properties
> > > > and
> > > > > > with
> > > > > > > create table we will create "types" in cache. No?
> > > > > > > I thought that cache it is a kind of "schema" in RDBMS.
> > > > > > >
> > > > > > > Could you describe what will be created with CREATE TABLE?
> > > > > > >
> > > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> > > > > > > [hidden email]> wrote:
> > > > > > >
> > > > > > > > Hello Igniters,
> > > > > > > >
> > > > > > > > I would like to start discussion about implementation of SQL
> > DDL
> > > > > > > commands.
> > > > > > > >
> > > > > > > > At the first stage, the most important ones seem to be CREATE
> > > TABLE
> > > > > > > > (that will obviously correspond to creation of a cache) and
> > > CREATE
> > > > > > > > INDEX.
> > > > > > > >
> > > > > > > > Regarding first one: SQL command for CREATE TABLE does not
> > > contain
> > > > > any
> > > > > > > > hints about cache settings (atomicity, replication, etc.), so
> > > these
> > > > > > > > will probably be defined by some configuration properties
> (like
> > > > > > > > ignite.ddl.default_cache_atomiticity, etc).
> > > > > > > >
> > > > > > > > Also it does not allow to distinguish between key and value
> > > > columns -
> > > > > > > > currently it is handled by keyFields property of QueryEntity,
> > but
> > > > it
> > > > > > > > is unclear how to declare key fields via CREATE TABLE.
> > > > > > > >
> > > > > > > > So at a first glance it seems like we should either implement
> > > some
> > > > > > > > sort of custom parsing (I believe Sergi will be against it)
> or
> > > > > > > > introduce some kind of name prefix that would tell SQL engine
> > > that
> > > > > > > > certain column is a key field column.
> > > > > > > >
> > > > > > > > Of course, this problem disappears is key is of SQL type.
> > > > > > > >
> > > > > > > > Regarding CREATE INDEX: probably at first we will have to
> > > implement
> > > > > > > > this in "stop-the-world" manner, i.e. all cache will be
> blocked
> > > > > during
> > > > > > > > the index's initial buildup.
> > > > > > > >
> > > > > > > > Any thoughts?
> > > > > > > >
> > > > > > > > Currently I'm working on parsing of those commands as that
> will
> > > be
> > > > > > > > needed anyway and does not affect further implementation.
> > > > > > > >
> > > > > > > > - Alex
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Alexey Kuznetsov
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Sergey Kozlov
> > > > > GridGain Systems
> > > > > www.gridgain.com
> > > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Sergey Kozlov
> > > GridGain Systems
> > > www.gridgain.com
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

al.psc
Sergi, Alexey G.,

I see your point and am rather inclined to agree that we should let
current notion of "single schema - multiple tables" live.

Still, if we create schema with cache config file, what's the whole
point of SQL then if the user anyway has to write XML? This probably
could be useful to propagate configuration to all cluster nodes tho.

And if we skip CREATE TABLE now, it means that we leave user facing
the need to write XML configuration, no other options. Is this what we
want?

Still I must admit that leaving user with his familiar XML stuff looks
attractive - no messing with bunch of unknown new params, just write
your XML and go. Also it's portable and allows to re-use
configurations easily, so undoubtedly is a good approach from some
point.

- Alex

2017-01-12 23:51 GMT+08:00 Sergi Vladykin <[hidden email]>:

> Hi,
>
> 1. For now I'm against inventing any custom SQL syntax and implementing
> parsing.
> Currently H2 supports the following syntax:
>
> CREATE TABLE test(...) WITH "myCustomParamString"
>
> This is enough for us to pass the needed parameters.
>
> 2. Agree with AG, we have to separate cache creation from table creation.
> Cache == SQL schema for us. We just have to add the same WITH syntax in H2
> for schema creation like this:
>
> CREATE SCHEMA "MyCacheName" WITH "cacheConfig=myCache.xml"
>
> 3. If we want to create tables then I suggest to put this functionality to
> 2.0+PageMemory right away and think where and how we are going to store all
> the related metadata.This is especially important for persistent storages.
>
> Sergi
>
>
> 2017-01-12 16:56 GMT+03:00 Vladimir Ozerov <[hidden email]>:
>
>> I am afraid in this case user will have to define too much schemes -
>> boilerplate.
>> Does it make sense at all to pack multiple tuples into a single cache from
>> user perspective?
>>
>> On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
>> [hidden email]> wrote:
>>
>> > Alexander,
>> >
>> > Will we keep the old option to have multiple tables in one cache? If so,
>> > how will create table statement know which cache to choose?
>> >
>> > It seems to me that to be consistent with the current DML implementation
>> we
>> > should have a CREATE SCHEMA statement which will define the cache and
>> cache
>> > configuration, and CREATE TABLE should specify the schema name.
>> >
>> > Otherwise, we should enforce the single type per cache rule at the
>> > configuration level and in runtime.
>> >
>> > As for affinity and primary key - agree with Vladimir.
>> >
>> > --
>> > AG
>> >
>> > 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
>> >
>> > > As first stage of DDL we can implement following CREATE TABLE statement
>> > > support:
>> > >  - CREATE TABLE without cache properties (use default cache properties
>> or
>> > > cache properties defined in SQL Schema)
>> > >  - CREATE TABLE .. LIKE where we can create a cache based on an another
>> > > existing cache.
>> > >
>> > > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
>> > [hidden email]>
>> > > wrote:
>> > >
>> > > > Agree with Sergey. We should be able to specify cache properties
>> inside
>> > > of
>> > > > SQL statements. Does H2 have any support to process SQL hints? Can we
>> > > > change it?
>> > > >
>> > > > Having said that, while we finalize the above, I think we should
>> start
>> > > > working on DDL implementation to use the default settings, as
>> specified
>> > > in
>> > > > Alexander's email.
>> > > >
>> > > > Also agree with the stop-the-world on the cache for index creation.
>> We
>> > > can
>> > > > always improve on it in future.
>> > > >
>> > > > D.
>> > > >
>> > > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <
>> [hidden email]>
>> > > > wrote:
>> > > >
>> > > > > Hi
>> > > > >
>> > > > > I suppose we should put any ignite cache properties as additional
>> > > > > non-standard attributes after CREATE TABLE () clause as it does
>> > > > Postgress,
>> > > > > MySQL and other RDBMS.
>> > > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
>> > > > CREATE
>> > > > > TABLE with using PARTITIONS (MySQL).
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
>> > > [hidden email]>
>> > > > > wrote:
>> > > > >
>> > > > > > I believe custom synthax and parsing is a *must* for us, as well
>> as
>> > > for
>> > > > > any
>> > > > > > distributed database. At the very least we need to specify
>> affinity
>> > > key
>> > > > > > column somehow. Any cache property can be specified at the very
>> end
>> > > of
>> > > > > > table definition. Key columns can be determined as the ones with
>> > > > PRIMARY
>> > > > > > KEY constraint (Alex K. idea) + affinity column(s):
>> > > > > >
>> > > > > > CREATE TABLE employee (
>> > > > > >     id BIGINT PRIMARY KEY,
>> > > > > >     dept_id BIGINT AFFINITY KEY,
>> > > > > >     name VARCHAR(128),
>> > > > > >     address VARCHAR(256)
>> > > > > >     BACKUPS 2,
>> > > > > >     ATOMICITY_MODE ATOMIC,
>> > > > > > );
>> > > > > >
>> > > > > > "id" and "dept_id" form key type, "name" and "address" form value
>> > > type.
>> > > > > >
>> > > > > > Vladimir.
>> > > > > >
>> > > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
>> > > > [hidden email]
>> > > > > >
>> > > > > > wrote:
>> > > > > >
>> > > > > > > Hi, Alex!
>> > > > > > >
>> > > > > > > As far as I know most RDBMS allow something like: create table
>> t1
>> > > (id
>> > > > > > > integer primary key, ....)
>> > > > > > > How about to take as key field that marked as "primary key"?
>> > > > > > >
>> > > > > > > As of atomicity and replication - I think it is a cache
>> > properties
>> > > > and
>> > > > > > with
>> > > > > > > create table we will create "types" in cache. No?
>> > > > > > > I thought that cache it is a kind of "schema" in RDBMS.
>> > > > > > >
>> > > > > > > Could you describe what will be created with CREATE TABLE?
>> > > > > > >
>> > > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
>> > > > > > > [hidden email]> wrote:
>> > > > > > >
>> > > > > > > > Hello Igniters,
>> > > > > > > >
>> > > > > > > > I would like to start discussion about implementation of SQL
>> > DDL
>> > > > > > > commands.
>> > > > > > > >
>> > > > > > > > At the first stage, the most important ones seem to be CREATE
>> > > TABLE
>> > > > > > > > (that will obviously correspond to creation of a cache) and
>> > > CREATE
>> > > > > > > > INDEX.
>> > > > > > > >
>> > > > > > > > Regarding first one: SQL command for CREATE TABLE does not
>> > > contain
>> > > > > any
>> > > > > > > > hints about cache settings (atomicity, replication, etc.), so
>> > > these
>> > > > > > > > will probably be defined by some configuration properties
>> (like
>> > > > > > > > ignite.ddl.default_cache_atomiticity, etc).
>> > > > > > > >
>> > > > > > > > Also it does not allow to distinguish between key and value
>> > > > columns -
>> > > > > > > > currently it is handled by keyFields property of QueryEntity,
>> > but
>> > > > it
>> > > > > > > > is unclear how to declare key fields via CREATE TABLE.
>> > > > > > > >
>> > > > > > > > So at a first glance it seems like we should either implement
>> > > some
>> > > > > > > > sort of custom parsing (I believe Sergi will be against it)
>> or
>> > > > > > > > introduce some kind of name prefix that would tell SQL engine
>> > > that
>> > > > > > > > certain column is a key field column.
>> > > > > > > >
>> > > > > > > > Of course, this problem disappears is key is of SQL type.
>> > > > > > > >
>> > > > > > > > Regarding CREATE INDEX: probably at first we will have to
>> > > implement
>> > > > > > > > this in "stop-the-world" manner, i.e. all cache will be
>> blocked
>> > > > > during
>> > > > > > > > the index's initial buildup.
>> > > > > > > >
>> > > > > > > > Any thoughts?
>> > > > > > > >
>> > > > > > > > Currently I'm working on parsing of those commands as that
>> will
>> > > be
>> > > > > > > > needed anyway and does not affect further implementation.
>> > > > > > > >
>> > > > > > > > - Alex
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > > > --
>> > > > > > > Alexey Kuznetsov
>> > > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > > --
>> > > > > Sergey Kozlov
>> > > > > GridGain Systems
>> > > > > www.gridgain.com
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> > > --
>> > > Sergey Kozlov
>> > > GridGain Systems
>> > > www.gridgain.com
>> > >
>> >
>>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Sergi
The xml config was only for example. We can put in this configuration
string cache config parameters directly like this:

CREATE SCHEMA "MyCacheName" WITH
"cacheMode=REPLICATED;atomicityMode=ATOMIC"

Sergi

2017-01-12 20:21 GMT+03:00 Alexander Paschenko <
[hidden email]>:

> Sergi, Alexey G.,
>
> I see your point and am rather inclined to agree that we should let
> current notion of "single schema - multiple tables" live.
>
> Still, if we create schema with cache config file, what's the whole
> point of SQL then if the user anyway has to write XML? This probably
> could be useful to propagate configuration to all cluster nodes tho.
>
> And if we skip CREATE TABLE now, it means that we leave user facing
> the need to write XML configuration, no other options. Is this what we
> want?
>
> Still I must admit that leaving user with his familiar XML stuff looks
> attractive - no messing with bunch of unknown new params, just write
> your XML and go. Also it's portable and allows to re-use
> configurations easily, so undoubtedly is a good approach from some
> point.
>
> - Alex
>
> 2017-01-12 23:51 GMT+08:00 Sergi Vladykin <[hidden email]>:
> > Hi,
> >
> > 1. For now I'm against inventing any custom SQL syntax and implementing
> > parsing.
> > Currently H2 supports the following syntax:
> >
> > CREATE TABLE test(...) WITH "myCustomParamString"
> >
> > This is enough for us to pass the needed parameters.
> >
> > 2. Agree with AG, we have to separate cache creation from table creation.
> > Cache == SQL schema for us. We just have to add the same WITH syntax in
> H2
> > for schema creation like this:
> >
> > CREATE SCHEMA "MyCacheName" WITH "cacheConfig=myCache.xml"
> >
> > 3. If we want to create tables then I suggest to put this functionality
> to
> > 2.0+PageMemory right away and think where and how we are going to store
> all
> > the related metadata.This is especially important for persistent
> storages.
> >
> > Sergi
> >
> >
> > 2017-01-12 16:56 GMT+03:00 Vladimir Ozerov <[hidden email]>:
> >
> >> I am afraid in this case user will have to define too much schemes -
> >> boilerplate.
> >> Does it make sense at all to pack multiple tuples into a single cache
> from
> >> user perspective?
> >>
> >> On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
> >> [hidden email]> wrote:
> >>
> >> > Alexander,
> >> >
> >> > Will we keep the old option to have multiple tables in one cache? If
> so,
> >> > how will create table statement know which cache to choose?
> >> >
> >> > It seems to me that to be consistent with the current DML
> implementation
> >> we
> >> > should have a CREATE SCHEMA statement which will define the cache and
> >> cache
> >> > configuration, and CREATE TABLE should specify the schema name.
> >> >
> >> > Otherwise, we should enforce the single type per cache rule at the
> >> > configuration level and in runtime.
> >> >
> >> > As for affinity and primary key - agree with Vladimir.
> >> >
> >> > --
> >> > AG
> >> >
> >> > 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
> >> >
> >> > > As first stage of DDL we can implement following CREATE TABLE
> statement
> >> > > support:
> >> > >  - CREATE TABLE without cache properties (use default cache
> properties
> >> or
> >> > > cache properties defined in SQL Schema)
> >> > >  - CREATE TABLE .. LIKE where we can create a cache based on an
> another
> >> > > existing cache.
> >> > >
> >> > > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
> >> > [hidden email]>
> >> > > wrote:
> >> > >
> >> > > > Agree with Sergey. We should be able to specify cache properties
> >> inside
> >> > > of
> >> > > > SQL statements. Does H2 have any support to process SQL hints?
> Can we
> >> > > > change it?
> >> > > >
> >> > > > Having said that, while we finalize the above, I think we should
> >> start
> >> > > > working on DDL implementation to use the default settings, as
> >> specified
> >> > > in
> >> > > > Alexander's email.
> >> > > >
> >> > > > Also agree with the stop-the-world on the cache for index
> creation.
> >> We
> >> > > can
> >> > > > always improve on it in future.
> >> > > >
> >> > > > D.
> >> > > >
> >> > > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <
> >> [hidden email]>
> >> > > > wrote:
> >> > > >
> >> > > > > Hi
> >> > > > >
> >> > > > > I suppose we should put any ignite cache properties as
> additional
> >> > > > > non-standard attributes after CREATE TABLE () clause as it does
> >> > > > Postgress,
> >> > > > > MySQL and other RDBMS.
> >> > > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or
> for
> >> > > > CREATE
> >> > > > > TABLE with using PARTITIONS (MySQL).
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
> >> > > [hidden email]>
> >> > > > > wrote:
> >> > > > >
> >> > > > > > I believe custom synthax and parsing is a *must* for us, as
> well
> >> as
> >> > > for
> >> > > > > any
> >> > > > > > distributed database. At the very least we need to specify
> >> affinity
> >> > > key
> >> > > > > > column somehow. Any cache property can be specified at the
> very
> >> end
> >> > > of
> >> > > > > > table definition. Key columns can be determined as the ones
> with
> >> > > > PRIMARY
> >> > > > > > KEY constraint (Alex K. idea) + affinity column(s):
> >> > > > > >
> >> > > > > > CREATE TABLE employee (
> >> > > > > >     id BIGINT PRIMARY KEY,
> >> > > > > >     dept_id BIGINT AFFINITY KEY,
> >> > > > > >     name VARCHAR(128),
> >> > > > > >     address VARCHAR(256)
> >> > > > > >     BACKUPS 2,
> >> > > > > >     ATOMICITY_MODE ATOMIC,
> >> > > > > > );
> >> > > > > >
> >> > > > > > "id" and "dept_id" form key type, "name" and "address" form
> value
> >> > > type.
> >> > > > > >
> >> > > > > > Vladimir.
> >> > > > > >
> >> > > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> >> > > > [hidden email]
> >> > > > > >
> >> > > > > > wrote:
> >> > > > > >
> >> > > > > > > Hi, Alex!
> >> > > > > > >
> >> > > > > > > As far as I know most RDBMS allow something like: create
> table
> >> t1
> >> > > (id
> >> > > > > > > integer primary key, ....)
> >> > > > > > > How about to take as key field that marked as "primary key"?
> >> > > > > > >
> >> > > > > > > As of atomicity and replication - I think it is a cache
> >> > properties
> >> > > > and
> >> > > > > > with
> >> > > > > > > create table we will create "types" in cache. No?
> >> > > > > > > I thought that cache it is a kind of "schema" in RDBMS.
> >> > > > > > >
> >> > > > > > > Could you describe what will be created with CREATE TABLE?
> >> > > > > > >
> >> > > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> >> > > > > > > [hidden email]> wrote:
> >> > > > > > >
> >> > > > > > > > Hello Igniters,
> >> > > > > > > >
> >> > > > > > > > I would like to start discussion about implementation of
> SQL
> >> > DDL
> >> > > > > > > commands.
> >> > > > > > > >
> >> > > > > > > > At the first stage, the most important ones seem to be
> CREATE
> >> > > TABLE
> >> > > > > > > > (that will obviously correspond to creation of a cache)
> and
> >> > > CREATE
> >> > > > > > > > INDEX.
> >> > > > > > > >
> >> > > > > > > > Regarding first one: SQL command for CREATE TABLE does not
> >> > > contain
> >> > > > > any
> >> > > > > > > > hints about cache settings (atomicity, replication,
> etc.), so
> >> > > these
> >> > > > > > > > will probably be defined by some configuration properties
> >> (like
> >> > > > > > > > ignite.ddl.default_cache_atomiticity, etc).
> >> > > > > > > >
> >> > > > > > > > Also it does not allow to distinguish between key and
> value
> >> > > > columns -
> >> > > > > > > > currently it is handled by keyFields property of
> QueryEntity,
> >> > but
> >> > > > it
> >> > > > > > > > is unclear how to declare key fields via CREATE TABLE.
> >> > > > > > > >
> >> > > > > > > > So at a first glance it seems like we should either
> implement
> >> > > some
> >> > > > > > > > sort of custom parsing (I believe Sergi will be against
> it)
> >> or
> >> > > > > > > > introduce some kind of name prefix that would tell SQL
> engine
> >> > > that
> >> > > > > > > > certain column is a key field column.
> >> > > > > > > >
> >> > > > > > > > Of course, this problem disappears is key is of SQL type.
> >> > > > > > > >
> >> > > > > > > > Regarding CREATE INDEX: probably at first we will have to
> >> > > implement
> >> > > > > > > > this in "stop-the-world" manner, i.e. all cache will be
> >> blocked
> >> > > > > during
> >> > > > > > > > the index's initial buildup.
> >> > > > > > > >
> >> > > > > > > > Any thoughts?
> >> > > > > > > >
> >> > > > > > > > Currently I'm working on parsing of those commands as that
> >> will
> >> > > be
> >> > > > > > > > needed anyway and does not affect further implementation.
> >> > > > > > > >
> >> > > > > > > > - Alex
> >> > > > > > > >
> >> > > > > > >
> >> > > > > > >
> >> > > > > > >
> >> > > > > > > --
> >> > > > > > > Alexey Kuznetsov
> >> > > > > > >
> >> > > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > > --
> >> > > > > Sergey Kozlov
> >> > > > > GridGain Systems
> >> > > > > www.gridgain.com
> >> > > > >
> >> > > >
> >> > >
> >> > >
> >> > >
> >> > > --
> >> > > Sergey Kozlov
> >> > > GridGain Systems
> >> > > www.gridgain.com
> >> > >
> >> >
> >>
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

al.psc
Sergi,

OK, great. Still, what's up with CREATE TABLE? After a bit of code
digging, I currently don't see major obstacles against registering
query entities (i.e. type descriptors) on the fly - CREATE TABLE will
essentially boil down to *GridQueryIndexing#registerType* call.

But, as you have justly noted, we have to keep nodes joining the
cluster up-to-date about what schemas and tables need to be created in
order for those nodes to participate in distributed queries. And,
correct me if I'm wrong, but this is relevant even outside of context
of 2.0 and page memory and persistent stores, amirite?

- Alex

2017-01-13 1:47 GMT+08:00 Sergi Vladykin <[hidden email]>:

> The xml config was only for example. We can put in this configuration
> string cache config parameters directly like this:
>
> CREATE SCHEMA "MyCacheName" WITH
> "cacheMode=REPLICATED;atomicityMode=ATOMIC"
>
> Sergi
>
> 2017-01-12 20:21 GMT+03:00 Alexander Paschenko <
> [hidden email]>:
>
>> Sergi, Alexey G.,
>>
>> I see your point and am rather inclined to agree that we should let
>> current notion of "single schema - multiple tables" live.
>>
>> Still, if we create schema with cache config file, what's the whole
>> point of SQL then if the user anyway has to write XML? This probably
>> could be useful to propagate configuration to all cluster nodes tho.
>>
>> And if we skip CREATE TABLE now, it means that we leave user facing
>> the need to write XML configuration, no other options. Is this what we
>> want?
>>
>> Still I must admit that leaving user with his familiar XML stuff looks
>> attractive - no messing with bunch of unknown new params, just write
>> your XML and go. Also it's portable and allows to re-use
>> configurations easily, so undoubtedly is a good approach from some
>> point.
>>
>> - Alex
>>
>> 2017-01-12 23:51 GMT+08:00 Sergi Vladykin <[hidden email]>:
>> > Hi,
>> >
>> > 1. For now I'm against inventing any custom SQL syntax and implementing
>> > parsing.
>> > Currently H2 supports the following syntax:
>> >
>> > CREATE TABLE test(...) WITH "myCustomParamString"
>> >
>> > This is enough for us to pass the needed parameters.
>> >
>> > 2. Agree with AG, we have to separate cache creation from table creation.
>> > Cache == SQL schema for us. We just have to add the same WITH syntax in
>> H2
>> > for schema creation like this:
>> >
>> > CREATE SCHEMA "MyCacheName" WITH "cacheConfig=myCache.xml"
>> >
>> > 3. If we want to create tables then I suggest to put this functionality
>> to
>> > 2.0+PageMemory right away and think where and how we are going to store
>> all
>> > the related metadata.This is especially important for persistent
>> storages.
>> >
>> > Sergi
>> >
>> >
>> > 2017-01-12 16:56 GMT+03:00 Vladimir Ozerov <[hidden email]>:
>> >
>> >> I am afraid in this case user will have to define too much schemes -
>> >> boilerplate.
>> >> Does it make sense at all to pack multiple tuples into a single cache
>> from
>> >> user perspective?
>> >>
>> >> On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
>> >> [hidden email]> wrote:
>> >>
>> >> > Alexander,
>> >> >
>> >> > Will we keep the old option to have multiple tables in one cache? If
>> so,
>> >> > how will create table statement know which cache to choose?
>> >> >
>> >> > It seems to me that to be consistent with the current DML
>> implementation
>> >> we
>> >> > should have a CREATE SCHEMA statement which will define the cache and
>> >> cache
>> >> > configuration, and CREATE TABLE should specify the schema name.
>> >> >
>> >> > Otherwise, we should enforce the single type per cache rule at the
>> >> > configuration level and in runtime.
>> >> >
>> >> > As for affinity and primary key - agree with Vladimir.
>> >> >
>> >> > --
>> >> > AG
>> >> >
>> >> > 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
>> >> >
>> >> > > As first stage of DDL we can implement following CREATE TABLE
>> statement
>> >> > > support:
>> >> > >  - CREATE TABLE without cache properties (use default cache
>> properties
>> >> or
>> >> > > cache properties defined in SQL Schema)
>> >> > >  - CREATE TABLE .. LIKE where we can create a cache based on an
>> another
>> >> > > existing cache.
>> >> > >
>> >> > > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
>> >> > [hidden email]>
>> >> > > wrote:
>> >> > >
>> >> > > > Agree with Sergey. We should be able to specify cache properties
>> >> inside
>> >> > > of
>> >> > > > SQL statements. Does H2 have any support to process SQL hints?
>> Can we
>> >> > > > change it?
>> >> > > >
>> >> > > > Having said that, while we finalize the above, I think we should
>> >> start
>> >> > > > working on DDL implementation to use the default settings, as
>> >> specified
>> >> > > in
>> >> > > > Alexander's email.
>> >> > > >
>> >> > > > Also agree with the stop-the-world on the cache for index
>> creation.
>> >> We
>> >> > > can
>> >> > > > always improve on it in future.
>> >> > > >
>> >> > > > D.
>> >> > > >
>> >> > > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <
>> >> [hidden email]>
>> >> > > > wrote:
>> >> > > >
>> >> > > > > Hi
>> >> > > > >
>> >> > > > > I suppose we should put any ignite cache properties as
>> additional
>> >> > > > > non-standard attributes after CREATE TABLE () clause as it does
>> >> > > > Postgress,
>> >> > > > > MySQL and other RDBMS.
>> >> > > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or
>> for
>> >> > > > CREATE
>> >> > > > > TABLE with using PARTITIONS (MySQL).
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
>> >> > > [hidden email]>
>> >> > > > > wrote:
>> >> > > > >
>> >> > > > > > I believe custom synthax and parsing is a *must* for us, as
>> well
>> >> as
>> >> > > for
>> >> > > > > any
>> >> > > > > > distributed database. At the very least we need to specify
>> >> affinity
>> >> > > key
>> >> > > > > > column somehow. Any cache property can be specified at the
>> very
>> >> end
>> >> > > of
>> >> > > > > > table definition. Key columns can be determined as the ones
>> with
>> >> > > > PRIMARY
>> >> > > > > > KEY constraint (Alex K. idea) + affinity column(s):
>> >> > > > > >
>> >> > > > > > CREATE TABLE employee (
>> >> > > > > >     id BIGINT PRIMARY KEY,
>> >> > > > > >     dept_id BIGINT AFFINITY KEY,
>> >> > > > > >     name VARCHAR(128),
>> >> > > > > >     address VARCHAR(256)
>> >> > > > > >     BACKUPS 2,
>> >> > > > > >     ATOMICITY_MODE ATOMIC,
>> >> > > > > > );
>> >> > > > > >
>> >> > > > > > "id" and "dept_id" form key type, "name" and "address" form
>> value
>> >> > > type.
>> >> > > > > >
>> >> > > > > > Vladimir.
>> >> > > > > >
>> >> > > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
>> >> > > > [hidden email]
>> >> > > > > >
>> >> > > > > > wrote:
>> >> > > > > >
>> >> > > > > > > Hi, Alex!
>> >> > > > > > >
>> >> > > > > > > As far as I know most RDBMS allow something like: create
>> table
>> >> t1
>> >> > > (id
>> >> > > > > > > integer primary key, ....)
>> >> > > > > > > How about to take as key field that marked as "primary key"?
>> >> > > > > > >
>> >> > > > > > > As of atomicity and replication - I think it is a cache
>> >> > properties
>> >> > > > and
>> >> > > > > > with
>> >> > > > > > > create table we will create "types" in cache. No?
>> >> > > > > > > I thought that cache it is a kind of "schema" in RDBMS.
>> >> > > > > > >
>> >> > > > > > > Could you describe what will be created with CREATE TABLE?
>> >> > > > > > >
>> >> > > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
>> >> > > > > > > [hidden email]> wrote:
>> >> > > > > > >
>> >> > > > > > > > Hello Igniters,
>> >> > > > > > > >
>> >> > > > > > > > I would like to start discussion about implementation of
>> SQL
>> >> > DDL
>> >> > > > > > > commands.
>> >> > > > > > > >
>> >> > > > > > > > At the first stage, the most important ones seem to be
>> CREATE
>> >> > > TABLE
>> >> > > > > > > > (that will obviously correspond to creation of a cache)
>> and
>> >> > > CREATE
>> >> > > > > > > > INDEX.
>> >> > > > > > > >
>> >> > > > > > > > Regarding first one: SQL command for CREATE TABLE does not
>> >> > > contain
>> >> > > > > any
>> >> > > > > > > > hints about cache settings (atomicity, replication,
>> etc.), so
>> >> > > these
>> >> > > > > > > > will probably be defined by some configuration properties
>> >> (like
>> >> > > > > > > > ignite.ddl.default_cache_atomiticity, etc).
>> >> > > > > > > >
>> >> > > > > > > > Also it does not allow to distinguish between key and
>> value
>> >> > > > columns -
>> >> > > > > > > > currently it is handled by keyFields property of
>> QueryEntity,
>> >> > but
>> >> > > > it
>> >> > > > > > > > is unclear how to declare key fields via CREATE TABLE.
>> >> > > > > > > >
>> >> > > > > > > > So at a first glance it seems like we should either
>> implement
>> >> > > some
>> >> > > > > > > > sort of custom parsing (I believe Sergi will be against
>> it)
>> >> or
>> >> > > > > > > > introduce some kind of name prefix that would tell SQL
>> engine
>> >> > > that
>> >> > > > > > > > certain column is a key field column.
>> >> > > > > > > >
>> >> > > > > > > > Of course, this problem disappears is key is of SQL type.
>> >> > > > > > > >
>> >> > > > > > > > Regarding CREATE INDEX: probably at first we will have to
>> >> > > implement
>> >> > > > > > > > this in "stop-the-world" manner, i.e. all cache will be
>> >> blocked
>> >> > > > > during
>> >> > > > > > > > the index's initial buildup.
>> >> > > > > > > >
>> >> > > > > > > > Any thoughts?
>> >> > > > > > > >
>> >> > > > > > > > Currently I'm working on parsing of those commands as that
>> >> will
>> >> > > be
>> >> > > > > > > > needed anyway and does not affect further implementation.
>> >> > > > > > > >
>> >> > > > > > > > - Alex
>> >> > > > > > > >
>> >> > > > > > >
>> >> > > > > > >
>> >> > > > > > >
>> >> > > > > > > --
>> >> > > > > > > Alexey Kuznetsov
>> >> > > > > > >
>> >> > > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > > --
>> >> > > > > Sergey Kozlov
>> >> > > > > GridGain Systems
>> >> > > > > www.gridgain.com
>> >> > > > >
>> >> > > >
>> >> > >
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Sergey Kozlov
>> >> > > GridGain Systems
>> >> > > www.gridgain.com
>> >> > >
>> >> >
>> >>
>>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

al.psc
BTW, I have also did some H2 guts inspection, and, as I see it now,
it's not impossible to do custom parsing without (an awful lot) of
ugliness. What we would have to do is basically spoof private
singleton org.h2.engine.Engine#INSTANCE on node start via reflection
with our custom implementation that could invoke custom parsing when
needed.

Not that we need it now - just in case.

- Alex

2017-01-13 2:16 GMT+08:00 Alexander Paschenko <[hidden email]>:

> Sergi,
>
> OK, great. Still, what's up with CREATE TABLE? After a bit of code
> digging, I currently don't see major obstacles against registering
> query entities (i.e. type descriptors) on the fly - CREATE TABLE will
> essentially boil down to *GridQueryIndexing#registerType* call.
>
> But, as you have justly noted, we have to keep nodes joining the
> cluster up-to-date about what schemas and tables need to be created in
> order for those nodes to participate in distributed queries. And,
> correct me if I'm wrong, but this is relevant even outside of context
> of 2.0 and page memory and persistent stores, amirite?
>
> - Alex
>
> 2017-01-13 1:47 GMT+08:00 Sergi Vladykin <[hidden email]>:
>> The xml config was only for example. We can put in this configuration
>> string cache config parameters directly like this:
>>
>> CREATE SCHEMA "MyCacheName" WITH
>> "cacheMode=REPLICATED;atomicityMode=ATOMIC"
>>
>> Sergi
>>
>> 2017-01-12 20:21 GMT+03:00 Alexander Paschenko <
>> [hidden email]>:
>>
>>> Sergi, Alexey G.,
>>>
>>> I see your point and am rather inclined to agree that we should let
>>> current notion of "single schema - multiple tables" live.
>>>
>>> Still, if we create schema with cache config file, what's the whole
>>> point of SQL then if the user anyway has to write XML? This probably
>>> could be useful to propagate configuration to all cluster nodes tho.
>>>
>>> And if we skip CREATE TABLE now, it means that we leave user facing
>>> the need to write XML configuration, no other options. Is this what we
>>> want?
>>>
>>> Still I must admit that leaving user with his familiar XML stuff looks
>>> attractive - no messing with bunch of unknown new params, just write
>>> your XML and go. Also it's portable and allows to re-use
>>> configurations easily, so undoubtedly is a good approach from some
>>> point.
>>>
>>> - Alex
>>>
>>> 2017-01-12 23:51 GMT+08:00 Sergi Vladykin <[hidden email]>:
>>> > Hi,
>>> >
>>> > 1. For now I'm against inventing any custom SQL syntax and implementing
>>> > parsing.
>>> > Currently H2 supports the following syntax:
>>> >
>>> > CREATE TABLE test(...) WITH "myCustomParamString"
>>> >
>>> > This is enough for us to pass the needed parameters.
>>> >
>>> > 2. Agree with AG, we have to separate cache creation from table creation.
>>> > Cache == SQL schema for us. We just have to add the same WITH syntax in
>>> H2
>>> > for schema creation like this:
>>> >
>>> > CREATE SCHEMA "MyCacheName" WITH "cacheConfig=myCache.xml"
>>> >
>>> > 3. If we want to create tables then I suggest to put this functionality
>>> to
>>> > 2.0+PageMemory right away and think where and how we are going to store
>>> all
>>> > the related metadata.This is especially important for persistent
>>> storages.
>>> >
>>> > Sergi
>>> >
>>> >
>>> > 2017-01-12 16:56 GMT+03:00 Vladimir Ozerov <[hidden email]>:
>>> >
>>> >> I am afraid in this case user will have to define too much schemes -
>>> >> boilerplate.
>>> >> Does it make sense at all to pack multiple tuples into a single cache
>>> from
>>> >> user perspective?
>>> >>
>>> >> On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
>>> >> [hidden email]> wrote:
>>> >>
>>> >> > Alexander,
>>> >> >
>>> >> > Will we keep the old option to have multiple tables in one cache? If
>>> so,
>>> >> > how will create table statement know which cache to choose?
>>> >> >
>>> >> > It seems to me that to be consistent with the current DML
>>> implementation
>>> >> we
>>> >> > should have a CREATE SCHEMA statement which will define the cache and
>>> >> cache
>>> >> > configuration, and CREATE TABLE should specify the schema name.
>>> >> >
>>> >> > Otherwise, we should enforce the single type per cache rule at the
>>> >> > configuration level and in runtime.
>>> >> >
>>> >> > As for affinity and primary key - agree with Vladimir.
>>> >> >
>>> >> > --
>>> >> > AG
>>> >> >
>>> >> > 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
>>> >> >
>>> >> > > As first stage of DDL we can implement following CREATE TABLE
>>> statement
>>> >> > > support:
>>> >> > >  - CREATE TABLE without cache properties (use default cache
>>> properties
>>> >> or
>>> >> > > cache properties defined in SQL Schema)
>>> >> > >  - CREATE TABLE .. LIKE where we can create a cache based on an
>>> another
>>> >> > > existing cache.
>>> >> > >
>>> >> > > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
>>> >> > [hidden email]>
>>> >> > > wrote:
>>> >> > >
>>> >> > > > Agree with Sergey. We should be able to specify cache properties
>>> >> inside
>>> >> > > of
>>> >> > > > SQL statements. Does H2 have any support to process SQL hints?
>>> Can we
>>> >> > > > change it?
>>> >> > > >
>>> >> > > > Having said that, while we finalize the above, I think we should
>>> >> start
>>> >> > > > working on DDL implementation to use the default settings, as
>>> >> specified
>>> >> > > in
>>> >> > > > Alexander's email.
>>> >> > > >
>>> >> > > > Also agree with the stop-the-world on the cache for index
>>> creation.
>>> >> We
>>> >> > > can
>>> >> > > > always improve on it in future.
>>> >> > > >
>>> >> > > > D.
>>> >> > > >
>>> >> > > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <
>>> >> [hidden email]>
>>> >> > > > wrote:
>>> >> > > >
>>> >> > > > > Hi
>>> >> > > > >
>>> >> > > > > I suppose we should put any ignite cache properties as
>>> additional
>>> >> > > > > non-standard attributes after CREATE TABLE () clause as it does
>>> >> > > > Postgress,
>>> >> > > > > MySQL and other RDBMS.
>>> >> > > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess) or
>>> for
>>> >> > > > CREATE
>>> >> > > > > TABLE with using PARTITIONS (MySQL).
>>> >> > > > >
>>> >> > > > >
>>> >> > > > >
>>> >> > > > >
>>> >> > > > >
>>> >> > > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
>>> >> > > [hidden email]>
>>> >> > > > > wrote:
>>> >> > > > >
>>> >> > > > > > I believe custom synthax and parsing is a *must* for us, as
>>> well
>>> >> as
>>> >> > > for
>>> >> > > > > any
>>> >> > > > > > distributed database. At the very least we need to specify
>>> >> affinity
>>> >> > > key
>>> >> > > > > > column somehow. Any cache property can be specified at the
>>> very
>>> >> end
>>> >> > > of
>>> >> > > > > > table definition. Key columns can be determined as the ones
>>> with
>>> >> > > > PRIMARY
>>> >> > > > > > KEY constraint (Alex K. idea) + affinity column(s):
>>> >> > > > > >
>>> >> > > > > > CREATE TABLE employee (
>>> >> > > > > >     id BIGINT PRIMARY KEY,
>>> >> > > > > >     dept_id BIGINT AFFINITY KEY,
>>> >> > > > > >     name VARCHAR(128),
>>> >> > > > > >     address VARCHAR(256)
>>> >> > > > > >     BACKUPS 2,
>>> >> > > > > >     ATOMICITY_MODE ATOMIC,
>>> >> > > > > > );
>>> >> > > > > >
>>> >> > > > > > "id" and "dept_id" form key type, "name" and "address" form
>>> value
>>> >> > > type.
>>> >> > > > > >
>>> >> > > > > > Vladimir.
>>> >> > > > > >
>>> >> > > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
>>> >> > > > [hidden email]
>>> >> > > > > >
>>> >> > > > > > wrote:
>>> >> > > > > >
>>> >> > > > > > > Hi, Alex!
>>> >> > > > > > >
>>> >> > > > > > > As far as I know most RDBMS allow something like: create
>>> table
>>> >> t1
>>> >> > > (id
>>> >> > > > > > > integer primary key, ....)
>>> >> > > > > > > How about to take as key field that marked as "primary key"?
>>> >> > > > > > >
>>> >> > > > > > > As of atomicity and replication - I think it is a cache
>>> >> > properties
>>> >> > > > and
>>> >> > > > > > with
>>> >> > > > > > > create table we will create "types" in cache. No?
>>> >> > > > > > > I thought that cache it is a kind of "schema" in RDBMS.
>>> >> > > > > > >
>>> >> > > > > > > Could you describe what will be created with CREATE TABLE?
>>> >> > > > > > >
>>> >> > > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
>>> >> > > > > > > [hidden email]> wrote:
>>> >> > > > > > >
>>> >> > > > > > > > Hello Igniters,
>>> >> > > > > > > >
>>> >> > > > > > > > I would like to start discussion about implementation of
>>> SQL
>>> >> > DDL
>>> >> > > > > > > commands.
>>> >> > > > > > > >
>>> >> > > > > > > > At the first stage, the most important ones seem to be
>>> CREATE
>>> >> > > TABLE
>>> >> > > > > > > > (that will obviously correspond to creation of a cache)
>>> and
>>> >> > > CREATE
>>> >> > > > > > > > INDEX.
>>> >> > > > > > > >
>>> >> > > > > > > > Regarding first one: SQL command for CREATE TABLE does not
>>> >> > > contain
>>> >> > > > > any
>>> >> > > > > > > > hints about cache settings (atomicity, replication,
>>> etc.), so
>>> >> > > these
>>> >> > > > > > > > will probably be defined by some configuration properties
>>> >> (like
>>> >> > > > > > > > ignite.ddl.default_cache_atomiticity, etc).
>>> >> > > > > > > >
>>> >> > > > > > > > Also it does not allow to distinguish between key and
>>> value
>>> >> > > > columns -
>>> >> > > > > > > > currently it is handled by keyFields property of
>>> QueryEntity,
>>> >> > but
>>> >> > > > it
>>> >> > > > > > > > is unclear how to declare key fields via CREATE TABLE.
>>> >> > > > > > > >
>>> >> > > > > > > > So at a first glance it seems like we should either
>>> implement
>>> >> > > some
>>> >> > > > > > > > sort of custom parsing (I believe Sergi will be against
>>> it)
>>> >> or
>>> >> > > > > > > > introduce some kind of name prefix that would tell SQL
>>> engine
>>> >> > > that
>>> >> > > > > > > > certain column is a key field column.
>>> >> > > > > > > >
>>> >> > > > > > > > Of course, this problem disappears is key is of SQL type.
>>> >> > > > > > > >
>>> >> > > > > > > > Regarding CREATE INDEX: probably at first we will have to
>>> >> > > implement
>>> >> > > > > > > > this in "stop-the-world" manner, i.e. all cache will be
>>> >> blocked
>>> >> > > > > during
>>> >> > > > > > > > the index's initial buildup.
>>> >> > > > > > > >
>>> >> > > > > > > > Any thoughts?
>>> >> > > > > > > >
>>> >> > > > > > > > Currently I'm working on parsing of those commands as that
>>> >> will
>>> >> > > be
>>> >> > > > > > > > needed anyway and does not affect further implementation.
>>> >> > > > > > > >
>>> >> > > > > > > > - Alex
>>> >> > > > > > > >
>>> >> > > > > > >
>>> >> > > > > > >
>>> >> > > > > > >
>>> >> > > > > > > --
>>> >> > > > > > > Alexey Kuznetsov
>>> >> > > > > > >
>>> >> > > > > >
>>> >> > > > >
>>> >> > > > >
>>> >> > > > >
>>> >> > > > > --
>>> >> > > > > Sergey Kozlov
>>> >> > > > > GridGain Systems
>>> >> > > > > www.gridgain.com
>>> >> > > > >
>>> >> > > >
>>> >> > >
>>> >> > >
>>> >> > >
>>> >> > > --
>>> >> > > Sergey Kozlov
>>> >> > > GridGain Systems
>>> >> > > www.gridgain.com
>>> >> > >
>>> >> >
>>> >>
>>>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

dsetrakyan
In reply to this post by al.psc
On Thu, Jan 12, 2017 at 10:16 AM, Alexander Paschenko <
[hidden email]> wrote:

> Sergi,
>
> OK, great. Still, what's up with CREATE TABLE? After a bit of code
> digging, I currently don't see major obstacles against registering
> query entities (i.e. type descriptors) on the fly - CREATE TABLE will
> essentially boil down to *GridQueryIndexing#registerType* call.
>

I think this makes sense.


>
> But, as you have justly noted, we have to keep nodes joining the
> cluster up-to-date about what schemas and tables need to be created in
> order for those nodes to participate in distributed queries. And,
> correct me if I'm wrong, but this is relevant even outside of context
> of 2.0 and page memory and persistent stores, amirite?
>

This should already be supported in Ignite. Otherwise, how are we able to
run queries today?


>
> - Alex
>
> 2017-01-13 1:47 GMT+08:00 Sergi Vladykin <[hidden email]>:
> > The xml config was only for example. We can put in this configuration
> > string cache config parameters directly like this:
> >
> > CREATE SCHEMA "MyCacheName" WITH
> > "cacheMode=REPLICATED;atomicityMode=ATOMIC"
> >
> > Sergi
> >
> > 2017-01-12 20:21 GMT+03:00 Alexander Paschenko <
> > [hidden email]>:
> >
> >> Sergi, Alexey G.,
> >>
> >> I see your point and am rather inclined to agree that we should let
> >> current notion of "single schema - multiple tables" live.
> >>
> >> Still, if we create schema with cache config file, what's the whole
> >> point of SQL then if the user anyway has to write XML? This probably
> >> could be useful to propagate configuration to all cluster nodes tho.
> >>
> >> And if we skip CREATE TABLE now, it means that we leave user facing
> >> the need to write XML configuration, no other options. Is this what we
> >> want?
> >>
> >> Still I must admit that leaving user with his familiar XML stuff looks
> >> attractive - no messing with bunch of unknown new params, just write
> >> your XML and go. Also it's portable and allows to re-use
> >> configurations easily, so undoubtedly is a good approach from some
> >> point.
> >>
> >> - Alex
> >>
> >> 2017-01-12 23:51 GMT+08:00 Sergi Vladykin <[hidden email]>:
> >> > Hi,
> >> >
> >> > 1. For now I'm against inventing any custom SQL syntax and
> implementing
> >> > parsing.
> >> > Currently H2 supports the following syntax:
> >> >
> >> > CREATE TABLE test(...) WITH "myCustomParamString"
> >> >
> >> > This is enough for us to pass the needed parameters.
> >> >
> >> > 2. Agree with AG, we have to separate cache creation from table
> creation.
> >> > Cache == SQL schema for us. We just have to add the same WITH syntax
> in
> >> H2
> >> > for schema creation like this:
> >> >
> >> > CREATE SCHEMA "MyCacheName" WITH "cacheConfig=myCache.xml"
> >> >
> >> > 3. If we want to create tables then I suggest to put this
> functionality
> >> to
> >> > 2.0+PageMemory right away and think where and how we are going to
> store
> >> all
> >> > the related metadata.This is especially important for persistent
> >> storages.
> >> >
> >> > Sergi
> >> >
> >> >
> >> > 2017-01-12 16:56 GMT+03:00 Vladimir Ozerov <[hidden email]>:
> >> >
> >> >> I am afraid in this case user will have to define too much schemes -
> >> >> boilerplate.
> >> >> Does it make sense at all to pack multiple tuples into a single cache
> >> from
> >> >> user perspective?
> >> >>
> >> >> On Thu, Jan 12, 2017 at 4:40 PM, Alexey Goncharuk <
> >> >> [hidden email]> wrote:
> >> >>
> >> >> > Alexander,
> >> >> >
> >> >> > Will we keep the old option to have multiple tables in one cache?
> If
> >> so,
> >> >> > how will create table statement know which cache to choose?
> >> >> >
> >> >> > It seems to me that to be consistent with the current DML
> >> implementation
> >> >> we
> >> >> > should have a CREATE SCHEMA statement which will define the cache
> and
> >> >> cache
> >> >> > configuration, and CREATE TABLE should specify the schema name.
> >> >> >
> >> >> > Otherwise, we should enforce the single type per cache rule at the
> >> >> > configuration level and in runtime.
> >> >> >
> >> >> > As for affinity and primary key - agree with Vladimir.
> >> >> >
> >> >> > --
> >> >> > AG
> >> >> >
> >> >> > 2017-01-12 11:41 GMT+03:00 Sergey Kozlov <[hidden email]>:
> >> >> >
> >> >> > > As first stage of DDL we can implement following CREATE TABLE
> >> statement
> >> >> > > support:
> >> >> > >  - CREATE TABLE without cache properties (use default cache
> >> properties
> >> >> or
> >> >> > > cache properties defined in SQL Schema)
> >> >> > >  - CREATE TABLE .. LIKE where we can create a cache based on an
> >> another
> >> >> > > existing cache.
> >> >> > >
> >> >> > > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
> >> >> > [hidden email]>
> >> >> > > wrote:
> >> >> > >
> >> >> > > > Agree with Sergey. We should be able to specify cache
> properties
> >> >> inside
> >> >> > > of
> >> >> > > > SQL statements. Does H2 have any support to process SQL hints?
> >> Can we
> >> >> > > > change it?
> >> >> > > >
> >> >> > > > Having said that, while we finalize the above, I think we
> should
> >> >> start
> >> >> > > > working on DDL implementation to use the default settings, as
> >> >> specified
> >> >> > > in
> >> >> > > > Alexander's email.
> >> >> > > >
> >> >> > > > Also agree with the stop-the-world on the cache for index
> >> creation.
> >> >> We
> >> >> > > can
> >> >> > > > always improve on it in future.
> >> >> > > >
> >> >> > > > D.
> >> >> > > >
> >> >> > > > On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <
> >> >> [hidden email]>
> >> >> > > > wrote:
> >> >> > > >
> >> >> > > > > Hi
> >> >> > > > >
> >> >> > > > > I suppose we should put any ignite cache properties as
> >> additional
> >> >> > > > > non-standard attributes after CREATE TABLE () clause as it
> does
> >> >> > > > Postgress,
> >> >> > > > > MySQL and other RDBMS.
> >> >> > > > > Take a look on CREATE TABLE with using TABLESPACE (Postgess)
> or
> >> for
> >> >> > > > CREATE
> >> >> > > > > TABLE with using PARTITIONS (MySQL).
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > > On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
> >> >> > > [hidden email]>
> >> >> > > > > wrote:
> >> >> > > > >
> >> >> > > > > > I believe custom synthax and parsing is a *must* for us, as
> >> well
> >> >> as
> >> >> > > for
> >> >> > > > > any
> >> >> > > > > > distributed database. At the very least we need to specify
> >> >> affinity
> >> >> > > key
> >> >> > > > > > column somehow. Any cache property can be specified at the
> >> very
> >> >> end
> >> >> > > of
> >> >> > > > > > table definition. Key columns can be determined as the ones
> >> with
> >> >> > > > PRIMARY
> >> >> > > > > > KEY constraint (Alex K. idea) + affinity column(s):
> >> >> > > > > >
> >> >> > > > > > CREATE TABLE employee (
> >> >> > > > > >     id BIGINT PRIMARY KEY,
> >> >> > > > > >     dept_id BIGINT AFFINITY KEY,
> >> >> > > > > >     name VARCHAR(128),
> >> >> > > > > >     address VARCHAR(256)
> >> >> > > > > >     BACKUPS 2,
> >> >> > > > > >     ATOMICITY_MODE ATOMIC,
> >> >> > > > > > );
> >> >> > > > > >
> >> >> > > > > > "id" and "dept_id" form key type, "name" and "address" form
> >> value
> >> >> > > type.
> >> >> > > > > >
> >> >> > > > > > Vladimir.
> >> >> > > > > >
> >> >> > > > > > On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> >> >> > > > [hidden email]
> >> >> > > > > >
> >> >> > > > > > wrote:
> >> >> > > > > >
> >> >> > > > > > > Hi, Alex!
> >> >> > > > > > >
> >> >> > > > > > > As far as I know most RDBMS allow something like: create
> >> table
> >> >> t1
> >> >> > > (id
> >> >> > > > > > > integer primary key, ....)
> >> >> > > > > > > How about to take as key field that marked as "primary
> key"?
> >> >> > > > > > >
> >> >> > > > > > > As of atomicity and replication - I think it is a cache
> >> >> > properties
> >> >> > > > and
> >> >> > > > > > with
> >> >> > > > > > > create table we will create "types" in cache. No?
> >> >> > > > > > > I thought that cache it is a kind of "schema" in RDBMS.
> >> >> > > > > > >
> >> >> > > > > > > Could you describe what will be created with CREATE
> TABLE?
> >> >> > > > > > >
> >> >> > > > > > > On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> >> >> > > > > > > [hidden email]> wrote:
> >> >> > > > > > >
> >> >> > > > > > > > Hello Igniters,
> >> >> > > > > > > >
> >> >> > > > > > > > I would like to start discussion about implementation
> of
> >> SQL
> >> >> > DDL
> >> >> > > > > > > commands.
> >> >> > > > > > > >
> >> >> > > > > > > > At the first stage, the most important ones seem to be
> >> CREATE
> >> >> > > TABLE
> >> >> > > > > > > > (that will obviously correspond to creation of a cache)
> >> and
> >> >> > > CREATE
> >> >> > > > > > > > INDEX.
> >> >> > > > > > > >
> >> >> > > > > > > > Regarding first one: SQL command for CREATE TABLE does
> not
> >> >> > > contain
> >> >> > > > > any
> >> >> > > > > > > > hints about cache settings (atomicity, replication,
> >> etc.), so
> >> >> > > these
> >> >> > > > > > > > will probably be defined by some configuration
> properties
> >> >> (like
> >> >> > > > > > > > ignite.ddl.default_cache_atomiticity, etc).
> >> >> > > > > > > >
> >> >> > > > > > > > Also it does not allow to distinguish between key and
> >> value
> >> >> > > > columns -
> >> >> > > > > > > > currently it is handled by keyFields property of
> >> QueryEntity,
> >> >> > but
> >> >> > > > it
> >> >> > > > > > > > is unclear how to declare key fields via CREATE TABLE.
> >> >> > > > > > > >
> >> >> > > > > > > > So at a first glance it seems like we should either
> >> implement
> >> >> > > some
> >> >> > > > > > > > sort of custom parsing (I believe Sergi will be against
> >> it)
> >> >> or
> >> >> > > > > > > > introduce some kind of name prefix that would tell SQL
> >> engine
> >> >> > > that
> >> >> > > > > > > > certain column is a key field column.
> >> >> > > > > > > >
> >> >> > > > > > > > Of course, this problem disappears is key is of SQL
> type.
> >> >> > > > > > > >
> >> >> > > > > > > > Regarding CREATE INDEX: probably at first we will have
> to
> >> >> > > implement
> >> >> > > > > > > > this in "stop-the-world" manner, i.e. all cache will be
> >> >> blocked
> >> >> > > > > during
> >> >> > > > > > > > the index's initial buildup.
> >> >> > > > > > > >
> >> >> > > > > > > > Any thoughts?
> >> >> > > > > > > >
> >> >> > > > > > > > Currently I'm working on parsing of those commands as
> that
> >> >> will
> >> >> > > be
> >> >> > > > > > > > needed anyway and does not affect further
> implementation.
> >> >> > > > > > > >
> >> >> > > > > > > > - Alex
> >> >> > > > > > > >
> >> >> > > > > > >
> >> >> > > > > > >
> >> >> > > > > > >
> >> >> > > > > > > --
> >> >> > > > > > > Alexey Kuznetsov
> >> >> > > > > > >
> >> >> > > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > > --
> >> >> > > > > Sergey Kozlov
> >> >> > > > > GridGain Systems
> >> >> > > > > www.gridgain.com
> >> >> > > > >
> >> >> > > >
> >> >> > >
> >> >> > >
> >> >> > >
> >> >> > > --
> >> >> > > Sergey Kozlov
> >> >> > > GridGain Systems
> >> >> > > www.gridgain.com
> >> >> > >
> >> >> >
> >> >>
> >>
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

dsetrakyan
In reply to this post by Sergi
On Thu, Jan 12, 2017 at 9:47 AM, Sergi Vladykin <[hidden email]>
wrote:

> The xml config was only for example. We can put in this configuration
> string cache config parameters directly like this:
>
> CREATE SCHEMA "MyCacheName" WITH
> "cacheMode=REPLICATED;atomicityMode=ATOMIC"
>

This approach makes sense, if it can be easily supported with H2.
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

dmagda

> On Jan 12, 2017, at 12:35 PM, Dmitriy Setrakyan <[hidden email]> wrote:
>
> On Thu, Jan 12, 2017 at 9:47 AM, Sergi Vladykin <[hidden email]>
> wrote:
>
>> The xml config was only for example. We can put in this configuration
>> string cache config parameters directly like this:
>>
>> CREATE SCHEMA "MyCacheName" WITH
>> "cacheMode=REPLICATED;atomicityMode=ATOMIC"
>>
>
> This approach makes sense, if it can be easily supported with H2.

What’s for affinity keys? Can we make an exception for them by defining in this part of the statement

CREATE TABLE employee (
   id BIGINT PRIMARY KEY,
   dept_id BIGINT AFFINITY KEY,
   name VARCHAR(128),
);

or that l

CREATE TABLE employee (
   id BIGINT PRIMARY KEY,
   dept_id BIGINT,
   name VARCHAR(128),
   CONSTRAINT affKey AFFINITY KEY(dept_id)
);

?


Denis

Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Sergey Kozlov
Denis

The affinity key term is close to the partition key definition for MySQL:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html

On Thu, Jan 12, 2017 at 11:46 PM, Denis Magda <[hidden email]> wrote:

>
> > On Jan 12, 2017, at 12:35 PM, Dmitriy Setrakyan <[hidden email]>
> wrote:
> >
> > On Thu, Jan 12, 2017 at 9:47 AM, Sergi Vladykin <
> [hidden email]>
> > wrote:
> >
> >> The xml config was only for example. We can put in this configuration
> >> string cache config parameters directly like this:
> >>
> >> CREATE SCHEMA "MyCacheName" WITH
> >> "cacheMode=REPLICATED;atomicityMode=ATOMIC"
> >>
> >
> > This approach makes sense, if it can be easily supported with H2.
>
> What’s for affinity keys? Can we make an exception for them by defining in
> this part of the statement
>
> CREATE TABLE employee (
>    id BIGINT PRIMARY KEY,
>    dept_id BIGINT AFFINITY KEY,
>    name VARCHAR(128),
> );
>
> or that l
>
> CREATE TABLE employee (
>    id BIGINT PRIMARY KEY,
>    dept_id BIGINT,
>    name VARCHAR(128),
>    CONSTRAINT affKey AFFINITY KEY(dept_id)
> );
>
> ?
>
> —
> Denis
>
>


--
Sergey Kozlov
GridGain Systems
www.gridgain.com
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

Vladimir Ozerov
In reply to this post by dmagda
I am not quite sure I understand the idea of "SCHEMA == cache". Consider
some small database with, say, ~30 tables. And user wants to migrate to
Ignite. How is he supposed to do so? 30 schemas leading to rewrite of all
his SQL scripts? Or 30 key-value pairs in a single cache leading to lack of
flexibility and performance problems?

Another example is how to deal with referene tables? Lots database has
small reference tables which is best to fit REPLICATED cache, while others
are usually bound to PARTITIONED mode. "SCHEMA == cache" will force users
to split them into separate schemes leading to poor user experience.

I understand that we may have some implementation details around it at the
moment. But from user perspective "SCHEMA == cache" doesn't make sense. As
we are going towards AI 2.0 we'd better to rethink this approach.

On Thu, Jan 12, 2017 at 11:46 PM, Denis Magda <[hidden email]> wrote:

>
> > On Jan 12, 2017, at 12:35 PM, Dmitriy Setrakyan <[hidden email]>
> wrote:
> >
> > On Thu, Jan 12, 2017 at 9:47 AM, Sergi Vladykin <
> [hidden email]>
> > wrote:
> >
> >> The xml config was only for example. We can put in this configuration
> >> string cache config parameters directly like this:
> >>
> >> CREATE SCHEMA "MyCacheName" WITH
> >> "cacheMode=REPLICATED;atomicityMode=ATOMIC"
> >>
> >
> > This approach makes sense, if it can be easily supported with H2.
>
> What’s for affinity keys? Can we make an exception for them by defining in
> this part of the statement
>
> CREATE TABLE employee (
>    id BIGINT PRIMARY KEY,
>    dept_id BIGINT AFFINITY KEY,
>    name VARCHAR(128),
> );
>
> or that l
>
> CREATE TABLE employee (
>    id BIGINT PRIMARY KEY,
>    dept_id BIGINT,
>    name VARCHAR(128),
>    CONSTRAINT affKey AFFINITY KEY(dept_id)
> );
>
> ?
>
> —
> Denis
>
>
Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

dmagda
In reply to this post by Sergey Kozlov
Guys,

As for the stages I would propose the following three creating separate JIRA tickets for them.

Stage 1:
- CREATE/DROP SCHEMA.
- CREATE/DROP TABLE.

Stage 2:
- CREATE/DROP INDEX.
- indexes are updated in the ‘lock-the-world mode'

Stage 3:
- CREATE/DROP INDEX.
- indexes are updated concurrently.

Going further we might need to make up a command like ‘CREATE CLUSTER’ that will be mapped to IgniteConfiguration. Using the command the user will fill in the whole cluster configuration from DDL without a need to go to XML at all.

Thoughts?


Denis

> On Jan 12, 2017, at 12:41 AM, Sergey Kozlov <[hidden email]> wrote:
>
> As first stage of DDL we can implement following CREATE TABLE statement
> support:
> - CREATE TABLE without cache properties (use default cache properties or
> cache properties defined in SQL Schema)
> - CREATE TABLE .. LIKE where we can create a cache based on an another
> existing cache.
>
> On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <[hidden email]>
> wrote:
>
>> Agree with Sergey. We should be able to specify cache properties inside of
>> SQL statements. Does H2 have any support to process SQL hints? Can we
>> change it?
>>
>> Having said that, while we finalize the above, I think we should start
>> working on DDL implementation to use the default settings, as specified in
>> Alexander's email.
>>
>> Also agree with the stop-the-world on the cache for index creation. We can
>> always improve on it in future.
>>
>> D.
>>
>> On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <[hidden email]>
>> wrote:
>>
>>> Hi
>>>
>>> I suppose we should put any ignite cache properties as additional
>>> non-standard attributes after CREATE TABLE () clause as it does
>> Postgress,
>>> MySQL and other RDBMS.
>>> Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
>> CREATE
>>> TABLE with using PARTITIONS (MySQL).
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <[hidden email]>
>>> wrote:
>>>
>>>> I believe custom synthax and parsing is a *must* for us, as well as for
>>> any
>>>> distributed database. At the very least we need to specify affinity key
>>>> column somehow. Any cache property can be specified at the very end of
>>>> table definition. Key columns can be determined as the ones with
>> PRIMARY
>>>> KEY constraint (Alex K. idea) + affinity column(s):
>>>>
>>>> CREATE TABLE employee (
>>>>    id BIGINT PRIMARY KEY,
>>>>    dept_id BIGINT AFFINITY KEY,
>>>>    name VARCHAR(128),
>>>>    address VARCHAR(256)
>>>>    BACKUPS 2,
>>>>    ATOMICITY_MODE ATOMIC,
>>>> );
>>>>
>>>> "id" and "dept_id" form key type, "name" and "address" form value type.
>>>>
>>>> Vladimir.
>>>>
>>>> On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
>> [hidden email]
>>>>
>>>> wrote:
>>>>
>>>>> Hi, Alex!
>>>>>
>>>>> As far as I know most RDBMS allow something like: create table t1 (id
>>>>> integer primary key, ....)
>>>>> How about to take as key field that marked as "primary key"?
>>>>>
>>>>> As of atomicity and replication - I think it is a cache properties
>> and
>>>> with
>>>>> create table we will create "types" in cache. No?
>>>>> I thought that cache it is a kind of "schema" in RDBMS.
>>>>>
>>>>> Could you describe what will be created with CREATE TABLE?
>>>>>
>>>>> On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
>>>>> [hidden email]> wrote:
>>>>>
>>>>>> Hello Igniters,
>>>>>>
>>>>>> I would like to start discussion about implementation of SQL DDL
>>>>> commands.
>>>>>>
>>>>>> At the first stage, the most important ones seem to be CREATE TABLE
>>>>>> (that will obviously correspond to creation of a cache) and CREATE
>>>>>> INDEX.
>>>>>>
>>>>>> Regarding first one: SQL command for CREATE TABLE does not contain
>>> any
>>>>>> hints about cache settings (atomicity, replication, etc.), so these
>>>>>> will probably be defined by some configuration properties (like
>>>>>> ignite.ddl.default_cache_atomiticity, etc).
>>>>>>
>>>>>> Also it does not allow to distinguish between key and value
>> columns -
>>>>>> currently it is handled by keyFields property of QueryEntity, but
>> it
>>>>>> is unclear how to declare key fields via CREATE TABLE.
>>>>>>
>>>>>> So at a first glance it seems like we should either implement some
>>>>>> sort of custom parsing (I believe Sergi will be against it) or
>>>>>> introduce some kind of name prefix that would tell SQL engine that
>>>>>> certain column is a key field column.
>>>>>>
>>>>>> Of course, this problem disappears is key is of SQL type.
>>>>>>
>>>>>> Regarding CREATE INDEX: probably at first we will have to implement
>>>>>> this in "stop-the-world" manner, i.e. all cache will be blocked
>>> during
>>>>>> the index's initial buildup.
>>>>>>
>>>>>> Any thoughts?
>>>>>>
>>>>>> Currently I'm working on parsing of those commands as that will be
>>>>>> needed anyway and does not affect further implementation.
>>>>>>
>>>>>> - Alex
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Alexey Kuznetsov
>>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Sergey Kozlov
>>> GridGain Systems
>>> www.gridgain.com
>>>
>>
>
>
>
> --
> Sergey Kozlov
> GridGain Systems
> www.gridgain.com

Reply | Threaded
Open this post in threaded view
|

Re: DDL implementation details

dsetrakyan
Alexander, not sure what is the point of creating a table without indexes.
I would combine stage 1 and 2.

On Thu, Jan 12, 2017 at 12:57 PM, Denis Magda <[hidden email]> wrote:

> Guys,
>
> As for the stages I would propose the following three creating separate
> JIRA tickets for them.
>
> Stage 1:
> - CREATE/DROP SCHEMA.
> - CREATE/DROP TABLE.
>
> Stage 2:
> - CREATE/DROP INDEX.
> - indexes are updated in the ‘lock-the-world mode'
>
> Stage 3:
> - CREATE/DROP INDEX.
> - indexes are updated concurrently.
>
> Going further we might need to make up a command like ‘CREATE CLUSTER’
> that will be mapped to IgniteConfiguration. Using the command the user will
> fill in the whole cluster configuration from DDL without a need to go to
> XML at all.
>
> Thoughts?
>
> —
> Denis
>
> > On Jan 12, 2017, at 12:41 AM, Sergey Kozlov <[hidden email]>
> wrote:
> >
> > As first stage of DDL we can implement following CREATE TABLE statement
> > support:
> > - CREATE TABLE without cache properties (use default cache properties or
> > cache properties defined in SQL Schema)
> > - CREATE TABLE .. LIKE where we can create a cache based on an another
> > existing cache.
> >
> > On Thu, Jan 12, 2017 at 5:54 AM, Dmitriy Setrakyan <
> [hidden email]>
> > wrote:
> >
> >> Agree with Sergey. We should be able to specify cache properties inside
> of
> >> SQL statements. Does H2 have any support to process SQL hints? Can we
> >> change it?
> >>
> >> Having said that, while we finalize the above, I think we should start
> >> working on DDL implementation to use the default settings, as specified
> in
> >> Alexander's email.
> >>
> >> Also agree with the stop-the-world on the cache for index creation. We
> can
> >> always improve on it in future.
> >>
> >> D.
> >>
> >> On Wed, Jan 11, 2017 at 11:28 AM, Sergey Kozlov <[hidden email]>
> >> wrote:
> >>
> >>> Hi
> >>>
> >>> I suppose we should put any ignite cache properties as additional
> >>> non-standard attributes after CREATE TABLE () clause as it does
> >> Postgress,
> >>> MySQL and other RDBMS.
> >>> Take a look on CREATE TABLE with using TABLESPACE (Postgess) or for
> >> CREATE
> >>> TABLE with using PARTITIONS (MySQL).
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> On Wed, Jan 11, 2017 at 10:05 PM, Vladimir Ozerov <
> [hidden email]>
> >>> wrote:
> >>>
> >>>> I believe custom synthax and parsing is a *must* for us, as well as
> for
> >>> any
> >>>> distributed database. At the very least we need to specify affinity
> key
> >>>> column somehow. Any cache property can be specified at the very end of
> >>>> table definition. Key columns can be determined as the ones with
> >> PRIMARY
> >>>> KEY constraint (Alex K. idea) + affinity column(s):
> >>>>
> >>>> CREATE TABLE employee (
> >>>>    id BIGINT PRIMARY KEY,
> >>>>    dept_id BIGINT AFFINITY KEY,
> >>>>    name VARCHAR(128),
> >>>>    address VARCHAR(256)
> >>>>    BACKUPS 2,
> >>>>    ATOMICITY_MODE ATOMIC,
> >>>> );
> >>>>
> >>>> "id" and "dept_id" form key type, "name" and "address" form value
> type.
> >>>>
> >>>> Vladimir.
> >>>>
> >>>> On Wed, Jan 11, 2017 at 9:08 PM, Alexey Kuznetsov <
> >> [hidden email]
> >>>>
> >>>> wrote:
> >>>>
> >>>>> Hi, Alex!
> >>>>>
> >>>>> As far as I know most RDBMS allow something like: create table t1 (id
> >>>>> integer primary key, ....)
> >>>>> How about to take as key field that marked as "primary key"?
> >>>>>
> >>>>> As of atomicity and replication - I think it is a cache properties
> >> and
> >>>> with
> >>>>> create table we will create "types" in cache. No?
> >>>>> I thought that cache it is a kind of "schema" in RDBMS.
> >>>>>
> >>>>> Could you describe what will be created with CREATE TABLE?
> >>>>>
> >>>>> On Thu, Jan 12, 2017 at 12:54 AM, Alexander Paschenko <
> >>>>> [hidden email]> wrote:
> >>>>>
> >>>>>> Hello Igniters,
> >>>>>>
> >>>>>> I would like to start discussion about implementation of SQL DDL
> >>>>> commands.
> >>>>>>
> >>>>>> At the first stage, the most important ones seem to be CREATE TABLE
> >>>>>> (that will obviously correspond to creation of a cache) and CREATE
> >>>>>> INDEX.
> >>>>>>
> >>>>>> Regarding first one: SQL command for CREATE TABLE does not contain
> >>> any
> >>>>>> hints about cache settings (atomicity, replication, etc.), so these
> >>>>>> will probably be defined by some configuration properties (like
> >>>>>> ignite.ddl.default_cache_atomiticity, etc).
> >>>>>>
> >>>>>> Also it does not allow to distinguish between key and value
> >> columns -
> >>>>>> currently it is handled by keyFields property of QueryEntity, but
> >> it
> >>>>>> is unclear how to declare key fields via CREATE TABLE.
> >>>>>>
> >>>>>> So at a first glance it seems like we should either implement some
> >>>>>> sort of custom parsing (I believe Sergi will be against it) or
> >>>>>> introduce some kind of name prefix that would tell SQL engine that
> >>>>>> certain column is a key field column.
> >>>>>>
> >>>>>> Of course, this problem disappears is key is of SQL type.
> >>>>>>
> >>>>>> Regarding CREATE INDEX: probably at first we will have to implement
> >>>>>> this in "stop-the-world" manner, i.e. all cache will be blocked
> >>> during
> >>>>>> the index's initial buildup.
> >>>>>>
> >>>>>> Any thoughts?
> >>>>>>
> >>>>>> Currently I'm working on parsing of those commands as that will be
> >>>>>> needed anyway and does not affect further implementation.
> >>>>>>
> >>>>>> - Alex
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> Alexey Kuznetsov
> >>>>>
> >>>>
> >>>
> >>>
> >>>
> >>> --
> >>> Sergey Kozlov
> >>> GridGain Systems
> >>> www.gridgain.com
> >>>
> >>
> >
> >
> >
> > --
> > Sergey Kozlov
> > GridGain Systems
> > www.gridgain.com
>
>
123