Page History

...

Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys. (https://www.postgresql.org/docs/current/datatype-json.html)

Note

Using PostgreSQL special type as "json" or "jsonb" rather than "text", all applications will lose any compatibility with H2 and MariaDB. They will be mentioned in this document but not recommended.

Requirements

Policy types/Policies/Node Types/Node Templates are first order items
Data Types have a scope of a first order item, so a data type definition only applies in the scope of a policy type or node type definition
We should keep our current APIs, all changes should be internal
We must provide an upgrade path to the new data structure and a rollback to the current structure

Demo Application

To better understand these functionality, it has been created a Demo Application. That it is available here (https://gerrit.nordix.org/c/local/onap/clamp-pilot/+/11966).

Data Transfer Object

ORM Layer using Document Storage

ORM layer using document storage (PostgreSQL or MongoDB) could be organized in two layer:

Document layer (Domain Model to be converted in Json) - implementation has no dependency from DB
Persistence layer - (Domain Model depend of the DB used): Entities for PostgreSQL, Documents for MongoDB

An implementation on the Document layer could be found here: (https://gerrit.nordix.org/c/onap/policy/models/+/13633)

Example

In the example below DocToscaServiceTemplate should be serialized to Json.The Java code shown below, is the data transfer Object used by Rest Api Controller. (Validations and Swagger annotations have been removed for simplicity).

Code Block

language	java
title	Persistence Model
collapse	true

public class ElementDto {

    private String name;

    private List<String> tags;

..........
}

public class DocumentDto {

    private String name;

    private Map<String, ElementDto> elements;

..........
}

public class ItemDto {
    private String key;

    private String text;

    private Map<String, String> properties;

    private DocumentDto document;
    
..........
}

The Json shown below is an example of the model shown before.

Code Block

language	js
title	Json Document
collapse	true

{
  "key": "1",
  "text": "This is an example",
  "properties": {
    "additionalProp1": "Val 10",
    "additionalProp2": "11",
    "additionalProp3": "12"
  },
  "document": {
    "name": "DocumentExample",
    "elements": {
      "additionalProp1": {
        "name": "ElementExample 1",
        "tags": [
          "tag1"
        ]
      },
      "additionalProp2": {
        "name": "ElementExample 2",
        "tags": [
          "tag2"
        ]
      },
      "additionalProp3": {
        "name": "ElementExample 3",
        "tags": [
          "tag3"
        ]
      }
    }
  }
}

Domain model

To store data on PostgreSQL we still have to use Entities, but part of Dto classes could be stored as jsonb type or at least we can create new domain classes that are equal to those DTO classes.

In this example we can use just one class as persistence model as shown below.

Code Block

language	java
title	Persistence Model
collapse	true

@Entity
@Data
public class Item implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    private String key;

    @Column(name = "text", nullable = false)
    private String text;

    @Convert(converter = StringToMapConverter.class)
    @Column(columnDefinition = "jsonb", nullable = false)
    private Map<String, String> properties;

    @Convert(converter = StringToDocumentConverter.class)
    @Column(columnDefinition = "jsonb", nullable = false)
    @NotNull
    private DocumentDto document;
}

Converters

Jakarta and Spring do no support json Type, but we can use Converters to convert an Object to a String. The more generic is a converter, more we can reuse it.

Code Block

language	java
title	Converters
collapse	true

@Slf4j
@Converter(autoApply = true)
public final class StringToMapConverter implements AttributeConverter<Map<String, ? extends Object>, String> {

    private static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(Map<String, ? extends Object> map) {
        try {
            return map == null ? null : MAPPER.writeValueAsString(map);
        } catch (JsonProcessingException ex) {
            throw new IllegalStateException(ex);
        }
    }

    @Override
    public Map<String, ? extends Object> convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            return Map.of();
        }
        try {
            return MAPPER.readValue(dbData, new TypeReference<Map<String, Object>>() {});
        } catch (IOException ex) {
            log.error("Cannot convert String to Map.");
    @Data
@EqualsAndHashCode(callSuper = true)
@NoArgsConstructor
public class DocToscaServiceTemplate extends DocToscaEntity<ToscaServiceTemplate> {

    @SerializedName("data_types")
    private Map<String, DocToscaDataType> dataTypes;

    -------

   public DocToscaServiceTemplate(ToscaServiceTemplate authorativeConcept) {
        return Mapthis.offromAuthorative(authorativeConcept);
        }

    }
}

@Slf4j
@Converter(autoApply = true)
public final class StringToDocumentConverter implements AttributeConverter<DocumentDto, String> {

@Override
    public ToscaServiceTemplate toAuthorative() {
      private static final ObjectMappervar MAPPERtoscaServiceTemplate = new ObjectMapperToscaServiceTemplate();

    @Override
    public String convertToDatabaseColumn(DocumentDto document) {super.setToscaEntity(toscaServiceTemplate);
        try {super.toAuthorative();

        if    return document =(dataTypes != null) ?{
 null : MAPPER.writeValueAsString(document);
        } catch (JsonProcessingException ex) { toscaServiceTemplate.setDataTypes(PfUtils.mapMap(dataTypes, DocToscaDataType::toAuthorative));
        }
    throw new IllegalStateException(ex);
 -------
 
        return }toscaServiceTemplate;
    }

    @Override
    public DocumentDtovoid convertToEntityAttributefromAuthorative(StringToscaServiceTemplate dbDatatoscaServiceTemplate) {
        super.fromAuthorative(toscaServiceTemplate);
 
        if (dbData =toscaServiceTemplate.getDataTypes() != null) {
            returndataTypes new DocumentDto()= DocUtil.mapMap(toscaServiceTemplate.getDataTypes(), DocToscaDataType::new);
        }
 
        ------- 
   try {
            return MAPPER.readValue(dbData, DocumentDto.class); }
}

In the example below the implementation of JpaToscaServiceTemplate for PostgreSQL/MariaDB

Code Block

language	java
title	Persistence Model
collapse	true

@Entity
@Table(name = "ToscaServiceTemplate")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@Data
@EqualsAndHashCode(callSuper = false)
public class JpaToscaServiceTemplate extends PfConcept implements PfAuthorative<ToscaServiceTemplate> {

    @EmbeddedId
    }@VerifyKey
 catch (IOException ex) {@NotNull
    private PfConceptKey key;

    @Lob
  log.error("Cannot convert String@Convert(converter to= MapStringToServiceTemplateConverter."class);
    @NotNull
    private DocToscaServiceTemplate serviceTemplate;

    return new DocumentDto();
  ------- 
 
    @Override
   }
 public ToscaServiceTemplate toAuthorative() }
}

Repository

The java code below shows some examples how to use the functionalities offered by PosgreSLQ about Json Type. We can apply where clauses, extract and manipulate data into the Json data. In order to use these functionalities, all queries have to be native (nativeQuery = true).

Code Block

language	java
title	Repository
collapse	true

@Repository
public interface ItemRepository extends JpaRepository<Item, String> {

    @Query(value = " SELECT * FROM item where document['name'] @> :value ", nativeQuery = true)
    Stream<Item> findByName(@Param("value") String value);

    @Modifying
    @Query(value = " UPDATE item SET document['name']=:value where key=:key ", nativeQuery = true)
    void updateNameByKey(@Param("key") String key, @Param("value") String value);

    @Query(
            value = " SELECT Cast(jsonb_path_query_array(document['elements'], '$[*].keyvalue().value.tags') as varchar) FROM item ",
            nativeQuery = true)
    Stream<String> findTags();
}

Unit Tests

In order to make unit tests, we can not use H2 due of the incompatibility. Spring supports Testcontainers where we can configure a PostgreSQL Container that will up and running during tests.

{
        return serviceTemplate.toAuthorative();
    }

    @Override
    public void fromAuthorative(ToscaServiceTemplate authorativeConcept) {
        serviceTemplate = new DocToscaServiceTemplate(authorativeConcept);
        setKey(serviceTemplate.getKey().asIdentifier().asConceptKey());
    }
         ------- 
 }

In the example below the implementation of JpaToscaServiceTemplate for MongoDB

Code Block

language	java
title	Persistence Model
collapse	true

@Document(collection = "ToscaServiceTemplate")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@Data
@EqualsAndHashCode(callSuper = false)
public class JpaToscaServiceTemplate extends PfConcept implements PfAuthorative<ToscaServiceTemplate> {

    @Id
    @VerifyKey
    @NonNull
    private PfConceptKey key;

    @NonNull
    private DocToscaServiceTemplate serviceTemplate;

     ------- 
 
    @Override
    public ToscaServiceTemplate toAuthorative() {
        return serviceTemplate.toAuthorative();
    }

    @Override
    public void fromAuthorative(ToscaServiceTemplate authorativeConcept) {
        serviceTemplate = new DocToscaServiceTemplate(authorativeConcept);
        setKey(serviceTemplate.getKey().asIdentifier().asConceptKey());
    }

Code Block

language	java
title	Unit Test
collapse	true

@Testcontainers
@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(
        initializers = DemoApplicationTests.Initializer.class,
         ------- 
 }

Converters

Jakarta and Spring do no support json Type, but we can use Converters to convert DocToscaServiceTemplate to a Json String.

Code Block

language	java
title	Converters
collapse	true

@Converter(autoApply = true)
public class StringToServiceTemplateConverter implements AttributeConverter<DocToscaServiceTemplate, String> {

    private static final Coder coder = new StandardCoder();

    @Override
    public String convertToDatabaseColumn(DocToscaServiceTemplate serviceTemplate) {
        try {
            return serviceTemplate == null ? null : coder.encode(serviceTemplate);classes = DemoApplication.class)
@TestPropertySource(locations = {"classpath:application_test.properties"})
class DemoApplicationTests {

    @Test
    void contextLoads() {
    }

    @Container
    public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:latest")
            .withDatabaseName("db_demo").withUsername("sa").withPassword("sa");

    @Configuration
    static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        @Override
        public} voidcatch initialize(ConfigurableApplicationContextCoderException configurableApplicationContexte) {
            throw new PfModelRuntimeException(Response.Status.BAD_REQUEST,  TestPropertyValuese.getMessage(), e);
        }
    }

    @Override
    .of("spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),public DocToscaServiceTemplate convertToEntityAttribute(String dbData) {
        if (dbData ==      null) {
            "spring.datasource.username=" + postgreSQLContainer.getUsername(),return new DocToscaServiceTemplate();
        }
        try {
           "spring.datasource.password=" + postgreSQLContainer.getPassword())
 return coder.decode(dbData, DocToscaServiceTemplate.class);
        } catch (CoderException e) {
            throw new PfModelRuntimeException(Response.Status.BAD_REQUEST, e.applyTogetMessage(configurableApplicationContext.getEnvironment()), e);
        }
    }

}

Item Table

Work in progress ...

db_demo=# \d item
                         Table "public.item"
   Column   |          Type          | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
 key        | character varying(255) |           | not null |
 document   | jsonb                  |           | not null |
 properties | jsonb                  |           | not null |
 text       | character varying(255) |           | not null |
Indexes:
    "item_pkey" PRIMARY KEY, btree (key)

...

Code Block

language	sql

db_demo=# SELECT Cast(jsonb_path_query_array(document['elements'], '$[*].keyvalue().value.tags') as varchar) FROM item;
     jsonb_path_query_array
--------------------------------
 [["tag1"], ["tag2"], ["tag3"]]
(1 row)

"]]
(1 row)

Note

Using document storage, it involves only the ORM layer, it does not change the functionality of the application
After migration to document storage as ORM layer, it will possible to adjust flexibility of Tosca Service Template Handling (POLICY-3236)

Example in Policy Admin models

Work in progress ...

JpaToscaWithTypeAndStringProperties class

...

Type	DataStore	Validation	Support Query	Index	Preserve Order
longtext	MariaDB	SELECT	Yes	No	Yes
longtext	PostgreSQL	No	No	No	Yes
json	PostgreSQL	INSERT / UPDATE	Yes	No	Yes
jsonb	PostgreSQL	INSERT / UPDATE	Yes	Yes	No

Proposals

Work in progress ...

My propose options are shown below:

replace all ElementCollections with Converters:
- Is a fix and not a change of technologies
- Small Json instead of Java binary code
- compatible with H2, MariaDB and PostgreSQL
- it impacts only onap/policy/models, persistence classes used into ElementCollections have to change to a Document oriented classes
- 50% of tables (with postfix as _ATTRIBUTES, _PROPERTIES, _METADATA, _OCCURRENCES, _CONSTRAINTS, _TARGETS, _TRIGGERS and _INPUTS) will be removed
- Unit tests only onap/policy/models
- onap/policy/models still compatible with not Spring Applications
Unique Json String:
- One big Json for each model (e.g. saving the whole ToscaServiceTemplace ToscaServiceTemplace saved as Json)
- compatible only with H2, MariaDB and PostgreSQL
- it impacts onap/policy/models and all applications related (runtime-acm, api and pap), persistence classes have to change to a Document oriented classes
- 90% of tables will be removed
- Unit tests need PostgreSQLContainer (is not compatible with H2)for onap/policy/models and minor impact for all applications
- onap/policy/models still compatible with not Spring Applications
MongoDB/Cassandra
- Document oriented approach full supported by SpringBoot (not needs Converters)
- it impacts onap/policy/models and all applications related (runtime-acm, api and pap), and all repositories and persistence classes have to change to a Document oriented classes
- compatible only with MongoDB/Cassandra (MongoDB and Cassandra are not compatible to each other)
- Unit tests need an Embedded Server (example for cassandra: EmbeddedCassandraServerHelper or CassandraContainer)

...

Space shortcuts

Page tree

Versions Compared

Old Version 40

New Version 41

Key

Note

Requirements

Demo Application

Data Transfer Object

ORM Layer using Document Storage

Example

Domain model

Converters

Repository

Unit Tests

Converters

Item Table

Note

Example in Policy Admin models

JpaToscaWithTypeAndStringProperties class

Proposals