[
https://issues.apache.org/jira/browse/IGNITE-12807?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alexey Kukushkin reassigned IGNITE-12807:
-----------------------------------------
Assignee: (was: Alexey Kukushkin)
> Key and Value fields with same name and SQL DML
> -----------------------------------------------
>
> Key: IGNITE-12807
> URL: https://issues.apache.org/jira/browse/IGNITE-12807
> Project: Ignite
> Issue Type: Improvement
> Reporter: Alexey Kukushkin
> Priority: Major
> Labels: sbcf
> Attachments: ignite-12807-vs-2.8.patch
>
>
> 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.
> h1. Proposal
> h2. Requirements
> h3. 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 }*
> h3. 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.
> h3. Use Cases
> h4. 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:
> {code:java}
> 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"))
> ));
> {code}
> ** AND an entry is added to the cache with this SQL statement:
> {code:sql}
> INSERT INTO CACHE.Person (ID, PASSPORTNO, NAME) VALUES (1, '11111', 'Name1')
> {code}
> * WHEN the user gets the entity using Cache API:
> {code:java}
> final PersonKey K = new PersonKey(1, "11111");
> Person v = cache.get(K);
> {code}
> * THEN the *passportNo* field is initialized to the same value within the
> key and value entities:
> {code:java}
> assertEquals(K.passportNo, v.passportNo);
> {code}
> h4. 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:
> {code:java}
> final PersonKey K = new PersonKey(1, "11111");
> final Person V = new Person("22222", "Name1");
> cache.put(K, V);
> {code}
> * WHEN the user runs this SQL to get the enty:
> {code:sql}
> SELECT ID, PASSPORTNO, NAME FROM CACHE.Person
> {code}
> * THEN the retrieved PASSPORTNO is that of the Key: "11111"
> h4. 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:
> {code:sql}
> CREATE TABLE Person (
> id int,
> passportNo varchar,
> name varchar,
> PRIMARY KEY(id, passportNo)
> ) WITH "key_type=PersonKey, value_type=Person, key_value_fields=passportNo"
> {code}
> ** AND an entry is added to the cache with this SQL statement:
> {code:sql}
> INSERT INTO CACHE.Person (ID, PASSPORTNO, NAME) VALUES (1, '11111', 'Name1')
> {code}
> * WHEN the user gets the entity using Cache API:
> {code:java}
> final PersonKey K = new PersonKey(1, "11111");
> Person v = cache.get(K);
> {code}
> * THEN the *passportNo* field is initialized to the same value within the
> key and value entities:
> {code:java}
> assertEquals(K.passportNo, v.passportNo);
> {code}
> h4. 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:
> {code:java}
> final PersonKey K = new PersonKey(1, "11111");
> final Person V = new Person("22222", "Name1");
> cache.put(K, V);
> {code}
> * WHEN the user runs this SQL to get the enty:
> {code:sql}
> SELECT ID, PASSPORTNO, NAME FROM CACHE.Person
> {code}
> * THEN the retrieved PASSPORTNO is that of the Key: "11111"
> h2. Implementation
> The attached patch implements the proposed Cache API enhancements and
> includes a {{DuplicateFieldSqlTest}} suite to cover the use cases above.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
