[teiid-issues] [JBoss JIRA] (TEIID-4516) Add support for updating child objects

[Van Halbert (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4516?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Van Halbert updated TEIID-4516:
-------------------------------
    Summary: Add support for updating child objects  (was: JDG Complex POJO update on child table doesn't work)


> Add support for updating child objects
> --------------------------------------
>
>                 Key: TEIID-4516
>                 URL: https://issues.jboss.org/browse/TEIID-4516
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Misc. Connectors
>    Affects Versions: 8.12.7.6_3
>            Reporter: Jan Stastny
>            Assignee: Van Halbert
>            Priority: Blocker
>
> When using complex pojo with JDG 6.6, updating child tables doesn't do anything. It doesn't update the rows, but neither throws exception.
> The problem appears for both usages of JDG cache (annotated POJOs, protobuf+marshaller). I have following pojo structure:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>     
>     /* @IndexedField(index=true, store=false) */
>     required int32 id = 1;
>  
>     /* @IndexedField */
>     required string name = 2;
>     
>     /* @IndexedField */
>     optional string email = 3;
>     
>     /* @IndexedField(index=true, store=false) */
>     repeated PhoneNumber phones = 4;
>     /* @Indexed */
>     message Address {
>  
>         /* @IndexedField */
>         required string Address = 1;
>         
>         /* @IndexedField(index=true, store=false) */
>         required string City = 2;
>         
>         /* @IndexedField(index=true, store=false) */
>         required string State = 3;
>     }
>     /* @IndexedField(index=true, store=false) */
>     optional Address address = 5;
> }
>  
> /* @Indexed */
> message PhoneNumber {
>  
>     /* @IndexedField */
>     required string number = 1;
> }
> {code}
> That is, one top-level POJO class Person, and two child POJO classes: Address(1-to-1) and PhoneNumber(1-to-N). Neither Address nor PhoneNumber tables could be updated. The DDL is following:
> {code:sql}
> CREATE FOREIGN TABLE Address (
>     Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE FALSE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
>     CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
>  
> CREATE FOREIGN TABLE Person (
>     id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
>     name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
>  
> CREATE FOREIGN TABLE PhoneNumber (
>     number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE FALSE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
>     CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> INSERT queries to prepare data:
> {code:sql}
> INSERT INTO Person(id,name,email) VALUES (1,'name1','email1')
> INSERT INTO Address(id,Address,City,State) VALUES (1,'address1','city1','state1')
> INSERT INTO PhoneNumber(id, number) VALUES (1, '001234567')
> INSERT INTO PhoneNumber(id, number) VALUES (1, '001234568')
> {code}
> UPDATE queries and queries for checking results:
> * 1-to-1
> {code:sql}UPDATE Address SET city='newCity' WHERE id=1{code}
> {code:sql}SELECT * FROM Person,Address{code}
> * 1-to-n
> {code:sql}UPDATE PhoneNumber SET number='newNumber' WHERE id=1{code}
> {code:sql}SELECT * FROM Person,PhoneNumber{code}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)