Motivation

In addition o SQL DDL commands, provide a simple, low-feature and type-safe Java API for initial tables generation from POJO. 
Support custom annotations and simple builders. This feature should work seamlessly with existing Mapper interface (keyValueView and
recordView). It will give developers the ability to work in the way they used to - create tables from POJO and build indexes without
forcing them to use SQL for basic operations.

This proposal is successor of QueryEntity and CacheConfiguration.setIndexedTypes() which was supported by AI2

Description

Apache Ignite is very idiomatic in Java, while QueryEntity in AI 2.x is technically low value - it's a convenient way 
to work with tables and pojo mapping. Besides, AI 3.x already has table keyValueViews that maps pojo but lacks features 
to easily instantiate tables.

We are not trying to achieve functionality of altering tables, schema migration tools and track changes, just a simple 
self-sufficient way of initial schema generation from POJO classes (prototyping, testing etc. purposes).

Statements, properties, expressions should be covered:

• CREATE ZONE
• CREATE TABLE
• CREATE INDEX
• DROP ZONE/TABLE/INDEX
• Column definition, length, precision, scale, nullability, defaults.

The Flow 

• The user uses annotations (or builders as an alternative).
• The client (ignite entry point - client or embedded mode) generates the SQL statements.
•  The client runs the SQL as usual.
•  The user uses KeyValueView, recordView or ResultSet as usual (those APIs should support @Column annotation as well).
•  The implementation should be fail-fast and throw exceptions on any ambiguity.

Annotations

JPA-like annotations with some ignite-specific stuff:

• Annotation processing build an SQL and execute multiple statements.
• Mapper of kvView/recordView should consider annotated column names etc.
• Mappers are in ignite-api module, so need to create annotations there as well
• Consider all fields, annotation may override name, length, precision, scale and defaults (behavior can be extended in future).
• Table name annotation
• Table name generation if not provided - use class name.
• ColocateBy - if not declared (default is used) it will be omitted during generation.
• Zone - if not declared (default is used) it will be omitted during generation.
• Annotation alternative is TableDefinition builder.
• Key annotations are redundant for now, Id and Column seems enough (behavior can be extended in future).
• Annotation Id can be used to define keys in single pojo (like recordView)
• Annotation Id can be used to define composite primary key (annotate multiple fields)
• Index annotations - JPA as reference. If define index in pojo class, then need to execute multiple statements upon creation.
• Index name generation if not provided - prefix “ix” concat with “_” separator column_names
• Joins - out of scope; Mappers already can map queries to pojo. API is supposed to create tables one by one. 

API

@Target(TYPE)
@Retention(RUNTIME)
public @interface Table {
    String name() default "";
    Index[] indexes() default {};
    IndexType primaryKeyType() default IndexType.DEFAULT;
    ColocateBy colocateBy() default @ColocateBy(columnList = "");
    Class<?> zone() default DefaultZone.class;
}

public interface DefaultZone { /*marker inerface*/ }

public enum SortOrder {
    DEFAULT, // order declaration in command will be omitted
    ASC,
    ASC_NULLS_FIRST,
    ASC_NULLS_LAST,
    DESC,
    DESC_NULLS_FIRST,
    DESC_NULLS_LAST,
}

@Target({})
@Retention(RUNTIME)
public @interface Col { // Specifies column in other annotations
    String name();
    SortOrder sort() default SortOrder.DEFAULT;
}

@Target({})
@Retention(RUNTIME)
public @interface Index {
    String name() default "";
    Col[] columns();
    IndexType type() default IndexType.DEFAULT;
}

public enum IndexType {
    TREE, HASH, 
    DEFAULT // index type declaration in command will be omitted
}

@Target({})
@Retention(RUNTIME)
public @interface ColocateBy {
    Col[] columns();
}

@Target(FIELD)
@Retention(RUNTIME)
public @interface Id {
    SortOrder sort() default SortOrder.DEFAULT; // order declaration will be omitted
}

@Target({METHOD, FIELD})
@Retention(RUNTIME)
public @interface Column {
    String name() default "";
    boolean nullable() default true;
    int length() default -1;
    int precision() default -1;
    int scale() default -1;
    String columnDefinition() default "";
}

@Target(TYPE)
@Retention(RUNTIME)
public @interface Zone {
    String name();
    int partitions() default -1;
    int replicas() default -1;
    int dataNodesAutoAdjust() default -1;
    int dataNodesAutoAdjustScaleUp() default -1;
    int dataNodesAutoAdjustScaleDown() default -1;
    String filter() default "";
    ZoneEngine engine() default ZoneEngine.DEFAULT;
    String dataregion() default "";
}

public enum ZoneEngine {
    AIMEM, ROCKSDB, 
    DEFAULT // zone engine declaration in dll will be omitted
}

Implementation

Proposed Java API is just a facade for SQL. The easiest way to implement this feature is to just generate SQL queries.

The flow:

• The user uses annotations (or builders as an alternative).
• The client (ignite entry point - client or embedded mode) generates the SQL statements.
• The client runs the SQL as usual.
• The user uses KeyValueView, recordView or ResultSet as usual (those APIs should consider Column annotation as well).
• The implementation should be fail-fast and throw exceptions on any ambiguity.

Pros:

• Simplicity and code reuse - proposed API just generates SQL, the execution path parsing, planning, execution - relying on existing mechanisms
• Can be reused in thin clients (TcpIgniteClient doesn’t have CatalogCommand support yet)

Cons:

• SQL overhead (minor, almost irrelevant) - even though API will generate and parse SQL every time, DDL statements are significantly less frequent than DML statements.

Future improvements

Following features seem out of scope and can be done later if needed.

SQL generation as well as parsing steps can be omitted by generating native CatalogCommand which is executed by CatalogManager (not supported by thin TcpIgniteClient yet). But performance is not the goal though, because DDL is far less frequent than DML.

Implicit auto table creation on startup from annotated classes in classpath with option to include/exclude certain packages (smth. like micronaut bean definition and code generation).

If table already exists but does not match pojo - give verbose message to user (compare consisting columns with describe table or smth. similar).

Extend Column annotation behavior by using it on nested non-primitive objects, then flatten nested fields into table declaration. Consider inheritance as well.

Entry point ignite.catalog() may expose more specific methods with fluent builders like ignite.catalog().create(ZoneDefinition.builder("zone1").partitions(1).replicas(1).build()).execute()

Examples

// kvView example

@Zone(
        name = "zone_test",
        partitions = 2,
        engine = ZoneEngine.ROCKSDB
)
class ZoneTest {}

class PojoKey {
    @Id
    Integer id;
    
    @Id(sort = DESC)
    @Column(name = "id_str", length = 20)
    String idStr;
}

@Table(
    name = "kv_pojo_test",
        zone = ZoneTest.class,
        colocateBy = @ColocateBy(columns = {
                    @Col(name = "f_name"),
                    @Col(name = "l_name") }),
    indexes = { @Index(name = "ix_test", columns = {
@Col(name = "f_name"), 
                    @Col(name = "l_name", sort = DESC_NULLS_LAST) })
    }
)
class PojoValue {
    @Column(name = "f_name")
    String firstName;

    @Column(name = "l_name")
    String lastName;

    String str;
}

// execute to create table
ignite.catalog().create(PojoKey.class, PojoValue.class).execute();

// example defining KV pojo, compatible with keyValueView
ignite.tables().table("kv_pojo_test").keyValueView(PojoKey.class, PojoValue.class)

// is equivalent to multi-statement
CREATE ZONE IF NOT EXISTS zone_test ENGINE ROCKSDB WITH PARTITIONS=2;

CREATE TABLE IF NOT EXISTS kv_pojo_test (
    id int,
    id_str varchar(20),
    f_name varchar,
    l_name varchar,
    str varchar,
    PRIMARY KEY (id, id_str desc)
)
COLOCATE BY (f_name, l_name)
WITH PRIMARY_ZONE='ZONE_TEST';

CREATE INDEX ix_test (f_name, l_name desc nulls last);

// recordView example
@Table(
        name = "pojo_test",
        zone = ZoneTest.class,
        colocateBy = @ColocateBy(columns = {
                @Col(name = "f_name"),
                @Col(name = "l_name") }),
        indexes = {
                @Index(name = "ix_test", columns = {
                        @Col(name = "f_name"),
                        @Col(name = "l_name", sort = DESC_NULLS_LAST)
                    }
                }
                )
class Pojo {
    @Id
    Integer id;
    
    @Id(sort = DESC)
    @Column(name = "id_str", length = 20)
    String idStr;
    
    @Column(name = "f_name")
    String firstName;
    
    @Column(name = "l_name")
    String lastName;
    
    String str;
}

// execute to create table
ignite.catalog().create(Pojo.class).execute();

// example defining single pojo, compatible with recordView
ignite.tables().table("kv_pojo_test").recordView(Pojo.class)

// example of using builder alternative to @Table annotation

class Pojo {
    @Id
        Integer id;

    @Id(sort = DESC)
    @Column(name = "id_str", length = 20)
    String idStr;

    @Column(name = "f_name")
    String firstName;

    @Column(name = "l_name")
    String lastName;

    String str;
}

ignite.catalog()
  .create(ZoneDefinition.builder("zone_test")
        .partitions(2))
  .execute();

ignite.catalog()
  .create(TableDefinition.builder("pojo_test")
        .ifNotExists()
        .colocateBy("id", "id_str")
        .zone("zone_test")
        .recordView(Pojo.class) // .keyValueView(Key.class, Value.class)
        .build())
  .execute();

Risks and Assumptions

No any risks

Reference Links

[AI2 Query Entities](https://ignite.apache.org/docs/latest/SQL/indexes) 

Tickets

- [AI3 Epic IGNITE-21410]( IGNITE-21410 - Getting issue details... STATUS )