Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4516

Add support for updating child objects

XMLWordPrintable

    • Icon: Feature Request Feature Request
    • Resolution: Obsolete
    • Icon: Major Major
    • None
    • 8.12.7.6_3, 9.2
    • JDG Connector
    • None

      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:

      Unable to find source-code formatter for language: plain. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      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;
}

      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:

      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);

      INSERT queries to prepare data:

      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')

      UPDATE queries and queries for checking results:

      • 1-to-1 
        UPDATE Address SET city='newCity' WHERE id=1
        SELECT * FROM Person,Address
      • 1-to-n 
        UPDATE PhoneNumber SET number='newNumber' WHERE id=1
        SELECT * FROM Person,PhoneNumber

              van.halbert Van Halbert (Inactive)
              jstastny@redhat.com Jan Stastny
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: