You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Alexey Kukushkin (Jira)" <ji...@apache.org> on 2020/04/09 11:01:00 UTC
[jira] [Updated] (IGNITE-12807) Key and Value fields with same name
and SQL DML
[ https://issues.apache.org/jira/browse/IGNITE-12807?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexey Kukushkin updated IGNITE-12807:
--------------------------------------
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.
h1. Proposal
h2. Requirements
h3. Example Data Model
Consider a business domain entity *Person \{ id: int, passportNo: String, name: String }*
When designing an Ignite app the 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 {{QueryEntity}}
** The method marks Cache API Key and Entity 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'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 the user runs this SQL statement to insert an entry to the cache:
{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 the user adds an entry with different passportNo field values to the cache:
{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 the user runs this SQL statement to insert an entry to the cache:
{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 the user adds an entry with different passportNo field values to the cache:
{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 user cases above.
was:
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.
> 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
> 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 }*
> When designing an Ignite app the 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 {{QueryEntity}}
> ** The method marks Cache API Key and Entity 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'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 the user runs this SQL statement to insert an entry to the cache:
> {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 the user adds an entry with different passportNo field values to the cache:
> {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 the user runs this SQL statement to insert an entry to the cache:
> {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 the user adds an entry with different passportNo field values to the cache:
> {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 user cases above.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)