Yes, we need to move on making Ignite work as any usual SQL db.
But please avoid mixing all this stuff together, lets have a separate task
(and discussion if needed) for each item in your list.
Sergi
2017-04-24 16:58 GMT+03:00 Vladimir Ozerov <voze...@gridgain.com>:
> Igniters,
>
> [long read, take a cup of coffee]
>
> Historically every SQL in Ignite must be executed against particular cache:
> SqlQuery requires cache, JDBC and ODBC drivers require cache name. This
> works fine until we add CREATE TABLE. Consider an empty cluster - how do
> you connect to it if you have no caches yet? Nohow.
>
> It looks like we cannot have convenient access to cluster unless we
> properly define and implement *schema* abstraction. ANSI SQL'92 defines
> several abstractions: cluster -> catalog -> schema -> table/view/etc..
> Every "catalog" has *INFORMATION_SCHEMA* schema, containing database
> metadata. Almost all vendors support it (notable exclusion - Oracle). Looks
> like we need to introduce similar concept and finally decouple caches from
> schemas.
>
> High-level proposal from my side
>
> 1) Let's consider Ignite cluster as a single database ("catalog" in ANSI
> SQL'92 terms).
>
> 2) It should be possible to connect to the cluster without a single user
> cache. In this case schema is not defined.
>
> 3) We must have a kind of storage for metadata. It could be either another
> system cache, or something analogous to binary metadata cache, which is
> essentially node-local data structure exchanged on node join. It should be
> aligned well with persistence feature, which is expected in AI 2.1.
>
> 4) Content of this storage will be accessible through INFORMATION_SCHEMA
> abstraction.
>
> 5) We must support "CREATE SCHEMA", "DROP SCHEMA" commands which will
> effectively create records in system cache and invoke relevant commands on
> local H2 engines of every node (now it happens implicitly on cache
> start/stop).
>
> 6) From JDBC/ODBC driver perspective schema will be defined either in
> connection string, or in runtime through "SET SCHEMA" command which is
> already supported by H2.
>
> 7) We must finally introduce new native SQL API, which will not use caches
> directly. Something like "IgniteSql sql()". See *IGNITE-4701*.
>
> Once schema is there, usage of "CREATE TABLE" and "DROP TABLE" commands
> will be simple and convenient, and it will fit naturally in user's past
> experience with conventional RDBMS.
>
> Thoughts?
>
> P.S.: CREATE/DROP TABLE feature is not blocked by this problem. It will
> work, but will be inconvenient for users.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-4701
>
