Details

Type: Improvement
Status: Closed
Priority: Major
Resolution: Won't Fix
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- sbcf

Description

Key/Value API allows both the Key and Value have fields with same name. This is a very popular arrangement since most users are ready to sacrifice extra memory footprint for the sake of having a self-sufficient value entity.

Using SQL DML to update such an entry will update only the key field, leaving the value field unchanged. This is a huge usability issue for the mixed K/V and SQL API apps.

Proposal

Requirements

Example Data Model

Consider a business domain entity Person { id: int, passportNo: String, name: String }
Suppose an Ignite application development team decided to map the Person entity to Ignite data model as:

PersonKey { id: int, passportNo: String }
Person { passportNo: String, name: String }

Public API

Cache API: add new method setKeyValueFields(keyValueFields: Set<String>): QueryEntity to class QueryEntity
- The method marks Cache API Key and Value fields that SQL API must initialize (on INSERT/MERGE) and update (on UPDATE/MERGE) together.
- It is still possible to use Cache API to initialize the fields marked with setKeyValueFields to different values. SQL SELECT statement returns value of such a field from the Key entity.
- The method accepts a set of field names and returns the declaring class instance for chaining.
- The method throws ArgumentException if the Key and Value types are available and the field types are different within the Key and Value entities.
SQL API: add KEY_VALUE_FIELDS parameter to CREATE TABLE statement's additional parameters list.
- The parameter's value is a space-separated list of field names with the semantics equivalent to that of the setKeyValueFields method described above.
- The parameter can be specified only if both the KEY_TYPE and VALUE_TYPE parameters are specified.

Use Cases

Inserting Into Key and Value Fields With Same Name Initializes Both Fields in QueryEntity-Defined Cache

GIVEN a Person cache from the example data model configured like this in Ignite:

new CacheConfiguration<PersonKey, Person>("CACHE")
            .setQueryEntities(Collections.singleton(
                new QueryEntity(PersonKey.class, Person.class)
                    .addQueryField("id", int.class.getName(), null)
                    .addQueryField("passportNo", String.class.getName(), null)
                    .addQueryField("name", String.class.getName(), null)
                    .setKeyFields(Collections.singleton("id"))
                    .setKeyValueFields(Collections.singleton("passportNo"))
            ));

AND an entry is added to the cache with this SQL statement:

 INSERT INTO CACHE.Person (ID, PASSPORTNO, NAME) VALUES (1, '11111', 'Name1')

WHEN the user gets the entity using Cache API:

final PersonKey K = new PersonKey(1, "11111");
Person v = cache.get(K);

THEN the passportNo field is initialized to the same value within the key and value entities:
```
assertEquals(K.passportNo, v.passportNo);
```

Querying Key and Value Fields With Same Name and Different Values Returns Value from the Key in QueryEntity-Defined Cache

GIVEN a Person cache from the previous use case
- AND an entry with different passportNo is added to the cache with this Cache API:
```
final PersonKey K = new PersonKey(1, "11111");
final Person V = new Person("22222", "Name1");
cache.put(K, V);
```

WHEN the user runs this SQL to get the enty:

 SELECT ID, PASSPORTNO, NAME FROM CACHE.Person

THEN the retrieved PASSPORTNO is that of the Key: "11111"

Inserting Into Key and Value Fields With Same Name Initializes Both Fields in SQL-Defined Cache

GIVEN a Person cache from the example data model configured like this in Ignite:

CREATE TABLE Person (
  id int,
  passportNo varchar,
  name varchar,
  PRIMARY KEY(id, passportNo)
) WITH "key_type=PersonKey, value_type=Person, key_value_fields=passportNo"

AND an entry is added to the cache with this SQL statement:

 INSERT INTO CACHE.Person (ID, PASSPORTNO, NAME) VALUES (1, '11111', 'Name1')

WHEN the user gets the entity using Cache API:

final PersonKey K = new PersonKey(1, "11111");
Person v = cache.get(K);

THEN the passportNo field is initialized to the same value within the key and value entities:
```
assertEquals(K.passportNo, v.passportNo);
```

Querying Key and Value Fields With Same Name and Different Values Returns Value from the Key in SQL-Defined Cache

GIVEN a Person cache from the previous use case
- AND an entry with different passportNo is added to the cache with this Cache API:
```
final PersonKey K = new PersonKey(1, "11111");
final Person V = new Person("22222", "Name1");
cache.put(K, V);
```

WHEN the user runs this SQL to get the enty:

 SELECT ID, PASSPORTNO, NAME FROM CACHE.Person

THEN the retrieved PASSPORTNO is that of the Key: "11111"

Implementation

The attached patch implements the proposed Cache API enhancements and includes a DuplicateFieldSqlTest suite to cover the use cases above.

Key and Value fields with same name and SQL DML