Glossary

SQL-schema is defined in SQL standard as a named collection of descriptors.
Simple name is the name of a schema object. Different schemas may contain different objects with the same simple names. The simple name always requires a schema name to resolve the named object.
Qualified name is a name, which contains an object name and a schema name the object belongs to. In contrast to a simple name, a qualified name allows one to resolve the named object unambiguously.

Motivation

SQL-schema support is a mandatory feature for standard conformance. Now, the only hardcoded "PUBLIC" is available for storing user objects in catalog.

Requirements

• Support user SQL commands for schema manipulation (create, drop, alter/rename).
• Table API, Compute API, Catalog API should be consistent and support SQL-schemas.
• Schema names are unique.
• System schemas can’t be dropped/created/renamed by a user.
• "PUBLIC" schema is similar to any other user schema and can be dropped

Description

User schemas can be created via SQL-commands described below. But there are predefined names, which are reserved for system schemas and default user schema, which is created during grid initialization.

The reserved names for system schemas are:

• SYSTEM - a system schema for Ignite internal components metadata, can be read by a user.
• INFORMATION_SCHEMA - a system schema defined by SQL standard. The schema provides system-views, which describe Catalog objects, and can be read by a user.
• DEFINITION_SCHEMA - a system schema defined by SQL standard. The schema provides tables/sources for Catalog object’s metadata and can’t be accessed by a user directly.

Name PUBLIC - is not a system or reserved name, but used as default schema name for simple names resolution. In some cases, the default schema can be overridden (see below).
A schema with name PUBLIC - is a predefined schema for holding user objects, which is created automatically at cluster initialization, and can be renamed or dropped later. 

Default schema name can be overridden, when running a query from thin clients, JDBC/ODBC clients, or using SQL Statement object. For these cases, we can pass a defaultSchema option to the SQL execution engine via connection property, or SQL statement property.
For ThinClient and embedded cases there are no such option to specify default schema, because there is no connection/session or any other context, where default schema could be defined/overridden. A hardcoded name PUBLIC shall be used in these cases.
If PUBLIC schema was dropped or renamed, and an API call (ThinClient or embedded), which can be SQL query execution or getting table, implies a default schema is required, then such call will fail with an error due to absence of hardcoded PUBLIC schema.

SQL-schema manipulation commands

Create schema command syntax

CREATE SCHEMA [IF NOT EXISTS] <schema_name_identifier>;

The <schema name identifier> is a simple name and must not clash with system schema name or any other existing schema name. Schemas can’t be nested.

Create-schema command syntax can be optionally extended as.

CREATE SCHEMA [IF NOT EXISTS] <schema_name_identifier> [<schema_element> .... ];
// Schema element as DDL definition of any of supported objects
<schema_element> ::= <table_definition> 
| <index_definition> 
...

The extended syntax looks useful for initial schema creation as a single `atomic` command.

Drop schema command syntax

DROP SCHEMA [IF EXISTS] <schema_name_identifier
> [CASCADE | RESTRICT];

CASCADE - drop behavior that forces dropping all its objects together with the schema at once.

RESTRICT - drop behavior that drops schema if empty or fails otherwise. 

Default behavior: RESTRICT.

Java API

Let’s introduce a QualifiedName class, which represents a named schema object, and provides factory methods for better UX and implements parsing rules for object’s names in a single place. 

/** Class represents a table name and contains factory methods. */
final class QualifiedName {
    /** Parses simple or canonical name, and returns an object, which represents qualified name. */
    static QualifiedName parseof(String name);
    
	/** Normalize schemaName and objectName and returns an object, which represents qualified name.*/
    static QualifiedName of(String schemaName, String objectName);
    
	/** Normalizes given simple name and resolves it against the default schema. */
    static QualifiedName fromSimple(String simpleName) {
        // Effectively the same as next.
        return of(DEFAULT_SCHEMA, simpleName);
    }
    /** Returns normalized schema name. **/
    String schemaName();

    /** Returns normalized schema's object name. **/
    String objectName();

    /** Returns object name in canonical form: {@code [<schema_name>.]<object_name>} **/
    String toCanonicalForm();
}

The object contains and provides method for accessing normalized schema and normalized object names.
A method for converting back to string of canonical form should respect SQL syntax rules: quote name only when needed and escape double quotes correctly.
The class provides static factory methods for creating QualifiedName instance and respects SQL identifier syntax rules: 

• Identifier must starts from any character in the Unicode General Category classes “Lu”, “Ll”, “Lt”, “Lm”, “Lo”, or “Nl”. 
• Identifier character (expect the first one) may be U+00B7 (middle dot), or any character in the Unicode General Category classes “Mn”, “Mc”, “Nd”, “Pc”, or “Cf”.
• Identifier that contains any other characters must be quoted with double-quotes.
• Double-quote inside the identifier must be encoded as 2 consequent double-quote chars.

 Table API consistency

Extend IgniteTable API methods to accept a schema name.

interface IgniteTables {
    /** Gets a list of all tables */
    List<Table> tables();

    /** Gets a table with specified name. */
    @Nullable Table table(QualifiedName tableName);

    @Nullable Table table(String tableName) {
        return table(QualifiedName.parse(tableName));
    }
    /* ... + async methods ... */
}

The methods, which accept String parameters, are shortcuts to the new ones.
Table names should follow the SQL-parser notation.

Compute API consistency

Extend JobTarget.colocate()  methods to accept qualified table names.

interface JobTarget {
    static JobTarget tableColocated(String tableName, Tuple key)
    static JobTarget tableColocated(QualifiedName tableName, Tuple key)
    static <K> JobTarget tableColocated(String tableName, K key, Mapper<K> keyMapper)
    static <K> JobTarget tableColocated(QualifiedName tableName, K key, Mapper<K> keyMapper)
}

The methods, which accept String parameters, are shortcuts to the new ones.
Table names should follow the SQL-parser notation.

Catalog API consistency

Extend IgniteCatalog interface with methods for creating/dropping schemas, and fix table methods signatures, which require table name.

interface IgniteCatalog {
    void createSchema(SchemaDefinition definition);
    void dropSchema(String schema, bool cascade=false);
    SchemaDefinition schemaDefinition(String schemaName);
 
    TableDefinition tableDefinition(QualifiedName tableName);
    TableDefinition tableDefinition(String tableName);

    void dropTable(QualifiedName tableName);
    void dropTable(String tableName);
} 

class SchemaDefinition {
    String schemaName;
}

The methods, which accept String parameters, are shortcuts to the new ones.
Table names should follow the SQL-parser notation.

Risks and Assumptions

• Suggested syntax differs from SQL standard
• Suggested syntax declares IF [NOT] EXISTS clauses, which are not part of SQL standard.
• Unlike SQL standard, schema name identifier is mandatory for CREATE SCHEMA statement.
• Drop behavior is a mandatory option in SQL standard.
• Schema owner is out of scope (AUTHORIZATION clause is omitted in CREATE statement) due to lack of security part design.
• Distribution zones do NOT belong to any schema.

Discussion Links

// Links to discussions on the devlist, if applicable.

Reference Links

// Links to various reference documents, if applicable.

Tickets