[ https://issues.apache.org/jira/browse/IGNITE-12807?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexey Kukushkin closed IGNITE-12807. ------------------------------------- Ignite Flags: (was: Docs Required,Release Notes Required) > 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 > Assignee: Alexey Kukushkin > Priority: Major > Labels: sbcf > > 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.20.1#820001)