Re: Summary of SQL changes in 2.1

[Pavel Tupitsyn <pt...@apache.org>]

> 3) "CREATE TABLE" creates a cache with special internal property
> "sql=true". Such cache cannot be destroyed through "Ignite.destroyCache".
> It can only be dropped through "DROP TABLE".The opposite is also holds:
> static and dynamic caches cannot be dropped through "DROP TABLE".

Hi Vladimir,
What is the reason for this limitation?

On Thu, Jun 1, 2017 at 12:26 AM, Dmitriy Setrakyan <ds...@apache.org>
wrote:

> Vladmir,
>
> Thanks for the detailed email. My comments are inline...
>
> On Wed, May 31, 2017 at 11:21 AM, Vladimir Ozerov <vo...@gridgain.com>
> wrote:
>
> > Folks,
> >
> > Let me summarize all recent changes to our SQL engine which are important
> > from user perspective. Please think of them and let me know if you have
> any
> > objection and thoughts on how to improve them.
> >
> > 1) Default "PUBLIC" schema added. It always exists and cannot be dropped.
> > Many caches can reside in this schema as opposed to earlier versions,
> where
> > every cache must be in separate schema.
> >
>
> Nice!
>
>
> > 2) Caches are still created in separate schemas by default. We should not
> > change this behavior, because it could break SQL queries of virtually all
> > users.
> >
>
> We should document, however, that this behavior will change in 3.0. Also,
> users should be able to specify that they wish to connect to the PUBLIC
> schema explicitly.
>
>
> > 3) "CREATE TABLE" creates a cache with special internal property
> > "sql=true". Such cache cannot be destroyed through "Ignite.destroyCache".
> > It can only be dropped through "DROP TABLE".The opposite is also holds:
> > static and dynamic caches cannot be dropped through "DROP TABLE".
> >
>
> Agree.
>
>
> >
> > 4) "CREATE INDEX" and "DROP INDEX" can only be executed on "sql" caches.
> >
>
> Ouch! Many of current Ignite users wish to have this functionality enabled
> for API-based caches. Any chance to lift this limitation?
>
>
> >
> > 5) There will be two predefined templates for "CREATE CACHE" command -
> > "REPLICATED" and "PARTITIONED". They are always available on any node.
> >
> > 6) Additional parameters which could be passed to "CREATE TABLE":
> > 6.1) "cacheTemplate" - name of cache template
> > 6.2) "backups" - number of backups
> > 6.3) "atomicityMode" - either "TRANSACTIONAL" or "ATOMIC"
> > 6.4) "AFFINITY KEY" - if key field should be used for affinity.
> >
>
> What are the defaults?
>
>
> >
> > Example:
> > CREATE TABLE Employee (
> >     pk_id BIGINT PRIMARY KEY,
> >     name VARCHAR(255),
> >     org_id BIGINT AFFINITY KEY
> > ) WITH "cacheTemplate=PARTITIONED, backups=1,
> atomicityMode=TRANSACTIONAL"
> >
> > 7) Connetion string of new JDBC driver starts with "jdbc:ignite:thin://",
> > and has only [host] as mandatory parameter.
> >
> > Example: "jdbc:ignite:thin://my_machine"
> >
>
> Why not have "thin" driver by default? Will users even notice?
>
>
> >
> > 8) New bean "SqlConfiguration" will be added to "IgniteConfiguration":
> >
> > class SqlConfiguration {
> >     SqlListenerConfiguration listenerCfg; // Content of this class will
> be
> > copied from OdbcConfiguration;
> >     long longQryWarnTimeout; // Moved from CacheConfiguration
> >
> >     // Will hold more common SQL stuff such as metrics frequency,
> > predefined schemas, etc. in future.
> > }
> >
> > class SqlListenerConfiguration {
> >     String host; // Optional, bind to all interfaces if ommitted;
> >     int port; // Port
> >     // Other stuff copied from OdbcConfiguration
> > }
> >
> > Example of configuration with explicitly enabled listener:
> > new IgniteConfiguration().setSqlConfiguration(new
> > SqlConfiguration().setListenerConfiuration(new
> > SqlListenerConfiguration()));
> >
>
> Seems that there is one-to-one dependency between SqlConfiguration and
> SqlListenerConfiguration. This looks a bit dirty. Why not just have
> SqlConfiguration with all the properties?
>
>
> >
> > 9) SQL listener *will not be enabled by default* as it consumes resources
> > and normally will be require only on small set of nodes.
> >
>
> Again, seems to be very odd. I would like SqlConfiguration to be enabled by
> default, given that many users will now connect to Ignite using the JDBC or
> ODBC drivers.
>
>
> >
> > 10) OdbcConfiguration will be deprecated in favor of
> > SqlListenerConfiguration.
> >
>
> Again, let's just have one SqlConfiguration interface. I am OK with
> deprecating the OdbcConfiguration, assuming that it will still work.
>
>
>
> >
> > Please share your thoughts.
> >
>