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
