The goal of this document is to show implications for tosca model template to move from Eclipse-Link to Hibernate. Primary focus is to show the database compatibility and eventually schema changes where is necessary.
In all these tests I am using some strategies to make sure that tables and fields name generated by Hibernate will be the same as Eclipse-Link.
Persistence classes scanning
Eclipse-Link
Control Loop runtime uses persistence.xml
file: is the deployment descriptor file for persistence using JPA. It specifies the persistence units and declares the managed persistence classes, the object/relation mapping, and the database connection details.
Hibernate
SpringBoot auto-configuration can automatically scan entity classes. In Control Loop Runtime we can use @EntityScan annotation because entity classes are not placed in the main application package or its sub-packages. In this situation, we need declare the package or list of packages in the main configuration class within @EntityScan annotation.
Table of Contents | ||
---|---|---|
|
Definitions
- ElementCollection: Specifies a collection of instances of a basic type or embeddable class. Must be specified if the collection is to be mapped by means of a collection table.
- OneToMany: Specifies a many-valued association with one-to-many multiplicity. If the collection is defined using generics to specify the element type, the associated target entity type need not be specified; otherwise the target entity class must be specified. If the relationship is bidirectional, the
mappedBy
element must be used to specify the relationship field or property of the entity that is the owner of the relationship. TheOneToMany
annotation may be used within an embeddable class contained within an entity class to specify a relationship to a collection of entities. If the relationship is bidirectional, themappedBy
element must be used to specify the relationship field or property of the entity that is the owner of the relationship. When the collection is ajava.util.Map
, thecascade
element and theorphanRemoval
element apply to the map value.
Persistence classes scanning
Eclipse-Link
Control Loop runtime uses persistence.xml
file: is the deployment descriptor file for persistence using JPA. It specifies the persistence units and declares the managed persistence classes, the object/relation mapping, and the database connection details.
Hibernate
SpringBoot auto-configuration can automatically scan entity classes. In Control Loop Runtime we can use @EntityScan annotation because entity classes are not placed in the main application package or its sub-packages. In this situation, we need declare the package or list of packages in the main configuration class within @EntityScan annotation.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
Code Block | ||||||
| ||||||
....................................... @EntityScan({"org.onap.policy.models.tosca.simple.concepts", "org.onap.policy.clamp.controlloop.models.controlloop.persistence.concepts"}) public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); } } |
...
Schema generation
Boolean and TimeStamp
In the most of the cases, in tosca model template there is compatibility in field types. Only two types (java.util.Date and Boolean) are not compatible between Eclipse-Link and Hibernate.
Note about precision in DATETIME (https://mariadb.com/kb/en/datetime/): MariaDB can store microseconds with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.
Code Block | ||||
---|---|---|---|---|
| ||||
@Embeddable
@Data
| ||||
Code Block | ||||
| ||||
@Embeddable @Data public class ExampleKey implements Serializable { private static final long serialVersionUID = 1L; @Column(name = "name", length = 120) private String name; @Column(name = "version", length = 20) private String version; } @Entity @Table(name = "Example") @Data @EqualsAndHashCode public class JpaExample implements Serializable { private static final long serialVersionUID = 1L; @EmbeddedId @VerifyKey @NotNull private ExampleKey key; @Column private Boolean primed; @Column(name = "timeStamp", precision = 3) @Temporal(TemporalType.TIMESTAMP) @NotNull private Date timeStamp; } |
...
MariaDB [controlloop]> describe Example;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
| primed | bit(1) | YES | | NULL | |
| timeStamp | datetime(6) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
Map of Strings
Boolean and TimeStamp (fixed)
columnDefinitionwill override the sql DDL generated by hibernate for this particular column, but it is non portable.
Code Block | ||||
---|---|---|---|---|
Code Block | language | java|||
| ||||
@Entity @Table(name = "ExampleMapExample") @Data @EqualsAndHashCode public class JpaExampleMapJpaExample implements Serializable { private static final long serialVersionUID = 1L; @EmbeddedId @VerifyKey @NotNull private ExampleKey key; @ElementCollection@Column(columnDefinition ="tinyint(1)") @Lobprivate Boolean primed; @Column(name = "timeStamp", privateprecision Map<@NotNull= String3, @NotNullcolumnDefinition String> attributes; } |
Eclipse-Link
= "datetime(3)")
@Temporal(TemporalType.TIMESTAMP)
@NotNull
private Date timeStamp;
} |
Eclipse-Link
MariaDB [controlloop]> describe Example;
+MariaDB [controlloop]> describe ExampleMap;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PRIMED | tinyint(1) | YES | | NULL | |
| timeStamp | datetime(3) | YES | | NULL | |
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+-----------+--------------+------+-----+---------+-------+
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleMap_ATTRIBUTESdescribe Example;
+-----------+---------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-----------+---------------------+
| Tablename | varchar(120) | NO | PRI | NULL | |
| version | Create Table varchar(20) | NO | PRI | NULL | |
| primed | tinyint(1) | YES | | NULL | |
| timeStamp | datetime(3) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
Map and List of Strings
When @ElementCollection is used properly there is enough compatibility. We can see that in the scenario shown below: there is only a small difference in how the primary keys are build.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleCollection")
@Data
public class JpaExampleCollection implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
@Lob
private Map<@NotNull String, @NotNull String> attributes;
@ElementCollection
private List<String> occurrences;
} |
Eclipse-Link
MariaDB [controlloop]> describe ExampleCollection;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleCollection_ATTRIBUTES;
+----------------------------------------------------------------------------------------------------------------+
| JpaExampleMap_ATTRIBUTES | CREATE TABLE `JpaExampleMap_ATTRIBUTES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`ATTRIBUTES` longtext DEFAULT NULL,
`ATTRIBUTES_KEY` varchar(255) DEFAULT NULL,
KEY `FK_JpaExampleMap_ATTRIBUTES_name` (`name`,`version`),
CONSTRAINT `FK_JpaExampleMap_ATTRIBUTES_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------------------+----------------------------------------------------------------------------------------------------------+
Hibernate
...
MariaDB [controlloop]> describe ExampleMap;
+---------+--------------+------+-----+---------+-------+
| Field | Type+
| Table | Create Table | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+--------+MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleMap_ATTRIBUTES;
+--------------------------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------+----------------------------------------------------------------------------------------------------------+
| JpaExampleMap_ATTRIBUTES | CREATE TABLE `JpaExampleMap_ATTRIBUTES` (JpaExampleCollection_ATTRIBUTES | CREATE TABLE `JpaExampleCollection_ATTRIBUTES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`name` varchar(120) NOT`ATTRIBUTES` longtext DEFAULT NULL,
`version` `ATTRIBUTES_KEY` varchar(20255) NOTDEFAULT NULL,
`attributes` longtext DEFAULT NULL,
`attributes_KEY` varchar(255) NOT NULLKEY `FK_JpaExampleCollection_ATTRIBUTES_name` (`name`,`version`),
PRIMARY KEY (`name`,`version`,`attributes_KEY`),
CONSTRAINT `FKrwvpo54nao070vsy5p23xnps7` CONSTRAINT `FK_JpaExampleCollection_ATTRIBUTES_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleMap``ExampleCollection` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------------------+-------+---------------------------------------------------------------------------------------------------+
JpaToscaCapabilityAssignment_ATTRIBUTES table
MariaDB [controlloop]> SHOW CREATE TABLE JpaToscaCapabilityAssignmentJpaExampleCollection_ATTRIBUTESOCCURRENCES;
+----------------------------------+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table Table |
+ |
+----------------------------------+-------+-------------------------------------------------------------------------------------------+
| JpaToscaCapabilityAssignmentJpaExampleCollection_ATTRIBUTESOCCURRENCES | CREATE TABLE `JpaToscaCapabilityAssignment`JpaExampleCollection_ATTRIBUTES`OCCURRENCES` (
`name``name` varchar(120) DEFAULT NULL,
`version` `version` varchar(20) DEFAULT NULL,
`OCCURRENCES` varchar(255) DEFAULT NULL,
`ATTRIBUTES` longtext DEFAULT NULL,
KEY `FK_JpaExampleCollection_OCCURRENCES_name` (`name`,`version`),
`ATTRIBUTES_KEY` varchar(255) DEFAULT NULL
CONSTRAINT `FK_JpaExampleCollection_OCCURRENCES_name`
FOREIGN KEY (`name`, `version`) REFERENCES `ExampleCollection` (`name`, `version`)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | |
+----------------------------------+-------+-------------------------------------------------------------------------------------------+
Currently in controlloop database, I have not found out why foreign key has not be generated in this table and in all '_ATTRIBUTES', '_META' and '_CONSTRAINTS' tables as well.
Hibernate
After saving an example:
MariaDB [controlloop]> SHOWselect CREATE* TABLEfrom JpaToscaCapabilityAssignment_ATTRIBUTESExampleCollection;
+-------------------+----------------------+
| name | version |
+-------------------+---------+
| ExampleCollection | 1.0.0 |
+-------------------+-------------+
MariaDB [controlloop]> select * from JpaExampleCollection_ATTRIBUTES;
+-------------------+---------+-----------------+----------------+
| Tablename | version | ATTRIBUTES | Create Table ATTRIBUTES_KEY |
+-------------------+----------------------+-----------------+--------------------+
| ExampleCollection | 1.0.0 | ValueAttribute2 | Attribute2 |
| ExampleCollection | 1.0.0 | ValueAttribute1 | Attribute1 |
+-------------------+---------+-----------------+----------------+
MariaDB [controlloop]> select * from JpaExampleCollection_OCCURRENCES;
+-------------------+---------+-------------+
| name | version | OCCURRENCES |
+-------------------+---------+-------------+
| ExampleCollection | 1.0.0 | Occurrence1 |
| ExampleCollection | 1.0.0 | Occurrence2 |
+-------------------+---------+-------------+
Hibernate
MariaDB [controlloop]> describe ExampleCollection;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleCollection_ATTRIBUTES;
+---------------------------------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------------------+---------------------------------------------------------------------------------------------------+
| JpaExampleCollection_ATTRIBUTES | CREATE TABLE `JpaExampleCollection_ATTRIBUTES` (
| JpaToscaCapabilityAssignment_ATTRIBUTES | CREATE TABLE `JpaToscaCapabilityAssignment_ATTRIBUTES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`attributes` longtext DEFAULT NULL,
`attributes_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`attributes_KEY`),
CONSTRAINT `FKjuqj4nashp9jx76h5eh5psp7l` FOREIGN KEY (`name`, `version`) `name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`attributes` longtext DEFAULT NULL,
REFERENCES `ToscaCapabilityAssignment` (`name`, `version`)
`attributes_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`attributes_KEY`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CONSTRAINT `FKrapsbq2h8eev8wstn9x11mfnr` FOREIGN KEY (`name`, `version`)
|
+----- REFERENCES `ExampleCollection` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------------------------------+-----+----------------------------------------------------------------------------------------------+
Implications using Hibernate
To be continued
Map of Objects using OneToMany
Code Block | ||||
---|---|---|---|---|
| ||||
@Entity
@Table(name = "ExampleObjMap")
@Data
@EqualsAndHashCode
public class JpaExampleObjMap implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@Lob
private Map<@NotNull String, @NotNull JpaExample> examples;
} |
Representation of the example in YAML of what we could save using JpaExampleObjMap entity.
Code Block | ||||
---|---|---|---|---|
| ||||
ExampleObjMap:
name: ExampleObjMap
version: 1.0.0
examples:
MyKey1:
name: example1
version: 1.0.0
MyKey2:
name: example2
version: 1.0.0 |
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjMap;
+--MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleCollection_OCCURRENCES;
+----------------------------------+--------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------------------+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EXAMPLESJpaExampleCollection_OCCURRENCES | longblobCREATE TABLE | YES | | NULL | |
| name |`JpaExampleCollection_OCCURRENCES` (
`name` varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+----------+--------------+------+-----+---------+-------+
After saving the example:
...
NOT NULL,
`version` varchar(20) NOT NULL,
`occurrences` varchar(255) DEFAULT NULL,
KEY `FKbvv6rdypcejif8yy1kr43vpob` (`name`,`version`),
CONSTRAINT `FKbvv6rdypcejif8yy1kr43vpob` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleCollection` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
Removing @Lob Annotation
The keys name and version of the JpaExampleObjMap as the same as JpaExample, so Eclipse-Link creates a wrong ExampleObjMap_Example table.
MariaDB [controlloop]> describe ExampleObjMap;
+---------+--------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
After saving an example:
MariaDB [controlloop]> SHOWselect CREATE* TABLEfrom ExampleObjMap_ExampleExampleCollection;
+-----------------------+----------+
| name | version |
+-------------------+---------+
| ExampleCollection | 1.0.0 |
+-------------------+---------+
MariaDB [controlloop]> select * from JpaExampleCollection_ATTRIBUTES;
+-------------------+---------+-----------------+----------------+
| Tablename | version | Createattributes Table | attributes_KEY |
+-------------------+---------+-----------------+----------------+
| ExampleCollection | 1.0.0 | ValueAttribute1 | Attribute1 |
| ExampleCollection | 1.0.0 | ValueAttribute2 | Attribute2 |
+-------------------+---------+-----------------+----------------+
MariaDB [controlloop]> select * from JpaExampleCollection_OCCURRENCES;
+-------------------+---------+-------------+
| name | version | occurrences |
+-------------------+---------+-------------+
| ExampleObjMap_ExampleExampleCollection | 1.0.0 | Occurrence1 |
| ExampleCollection | 1.0.0 | Occurrence2 |
+-------------------+---------+-------------+
JpaToscaCapabilityAssignment_ATTRIBUTES table
Currently in tosca model template, due the complexity of the schema there are some issues in foreign keys using Collections of String. The SQL code below shows the JpaToscaCapabilityAssignment_ATTRIBUTES table.
Eclipse-Link
MariaDB [controlloop]> SHOW CREATE TABLE JpaToscaCapabilityAssignment_ATTRIBUTES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Table CREATE TABLE `ExampleObjMap_Example` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
| Create Table `EXAMPLES_KEY` varchar(255) DEFAULT NULL,
PRIMARY KEY (`name`,`version`),
CONSTRAINT `FK_ExampleObjMap_Example_name` FOREIGN KEY (`name`, `version`)
|
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| JpaToscaCapabilityAssignment_ATTRIBUTES | CREATE TABLE `JpaToscaCapabilityAssignment_ATTRIBUTES` (
`name` varchar(120) DEFAULT NULL,
REFERENCES `ExampleObjMap` `version` (`name`, `version`)
varchar(20) DEFAULT NULL,
)`ATTRIBUTES` ENGINE=InnoDB DEFAULT CHARSET=utf8mb4longtext DEFAULT NULL,
`ATTRIBUTES_KEY` varchar(255) DEFAULT NULL
|
+-----------------------) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------------------+--------------------------------------------------------------------+
Using these tables I have got this error: Cannot add or update a child row: a foreign key constraint fails (`controlloop`.`ExampleObjMap_Example`, CONSTRAINT `FK_ExampleObjMap_Example_name` FOREIGN KEY (`name`, `version`) REFERENCES `ExampleObjMap` (`name`, `version`))
Error Code: 1452
Map of Objects using OneToMany (fixed)
Code Block | ||||
---|---|---|---|---|
| ||||
@Entity
@Table(name = "ExampleObjMap")
@Data
@EqualsAndHashCode
public class JpaExampleObjMap implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(
joinColumns = {@JoinColumn(name = "parent_name", referencedColumnName = "name"),
@JoinColumn(name = "parent_version", referencedColumnName = "version")},
inverseJoinColumns = {@JoinColumn(name = "child_name", referencedColumnName = "name"),
@JoinColumn(name = "child_version", referencedColumnName = "version")})
private Map<@NotNull String, @NotNull JpaExample> examples;
} |
Eclipse-Link
-----------------------+
I have not found out why foreign key has not be generated in this table and in all '_ATTRIBUTES', '_META' and '_CONSTRAINTS' tables as well.
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaToscaCapabilityAssignment_ATTRIBUTES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+------------------+-------------------------------------------------------------------------------------------+
| TableJpaToscaCapabilityAssignment_ATTRIBUTES | CREATE TABLE | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`JpaToscaCapabilityAssignment_ATTRIBUTES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`attributes` longtext DEFAULT NULL,
`parent`attributes_name`KEY` varchar(120255) NOT NULL,
`parent_version` varchar(20) NOT NULL,
PRIMARY KEY (`name`,`version`,`attributes_KEY`),
`child_name` varchar(120) NOT NULL,
`child_version` varchar(20) NOT NULL,
CONSTRAINT `FKjuqj4nashp9jx76h5eh5psp7l` FOREIGN KEY `EXAMPLES_KEY` varchar(255) DEFAULT NULL,
(`name`, `version`)
PRIMARY KEY (`parent_name`,`parent_version`,`child_name`,`child_version`),
REFERENCES KEY `FK_ExampleObjMap_Example_child_name` (`child_name`,`child_version`),`ToscaCapabilityAssignment` (`name`, `version`)
CONSTRAINT `FK_ExampleObjMap_Example_child_name` FOREIGN KEY (`child_name`, `child_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 REFERENCES `Example` (`name`, `version`),
CONSTRAINT `FK_ExampleObjMap_Example_parent_name` FOREIGN KEY (`parent_name`, `parent_version`)
REFERENCES `ExampleObjMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+|
+-------------------------+----------------+---------------------------------------------------------------------------------------------+
After saving the example:
Map of Objects using ElementCollection
ElementCollection should be used with basic type or embeddable class, this example shows side effects using it with entity.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleObjMapEc")
@Data
public class JpaExampleObjMapEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
@Lob
private Map<@NotNull String, @NotNull JpaExample> examples;
} |
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjMapEc;
+---MariaDB [controlloop]> select * from ExampleObjMap;
+------+--------------+------+---+
| name | version |
+-----------+----------+---------+
| ExampleObjMapField | 1.0.0 Type | Null | Key | Default | Extra |
+---------+------+---------+MariaDB [controlloop]> select * from Example;
+--------+-----------+----------+---------+
| PRIMED| name | varchar(120) | NO | PRI | timeStampNULL | name |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+
| NULL | NULL | example1 | 1.0.0 |
| NULL | NULL | example2 | 1.0.0 |
+--------+-----------+----------+---------+MariaDB -------+
MariaDB [controlloop]> selectSHOW *CREATE fromTABLE ExampleObjMapJpaExampleObjMapEc_ExampleEXAMPLES;
+---------------+----------------+------------+---------------+--------------+
| parent_name | parent_version | child_name | child_version | EXAMPLES_KEY |
+---------------+----------------+------------+---------------+--------------+
| ExampleObjMap | 1.0.0Table | Create Table | example1 | 1.0.0 | MyKey1 |
| ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+---------------+----------------+------------+---------------+--------------+
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`EXAMPLES` longblob DEFAULT NULL,
`parent`EXAMPLES_name`KEY` varchar(120255) NOTDEFAULT NULL,
`parent_version` varchar(20) NOT NULL,
`childKEY `FK_JpaExampleObjMapEc_EXAMPLES_name` varchar(120) NOT NULL`name`,`version`),
`child_version` varchar(20) NOT NULL,
CONSTRAINT `FK_JpaExampleObjMapEc_EXAMPLES_name` FOREIGN KEY (`name`, `version`)
`examples_KEY` varchar(255) NOT NULL,
REFERENCES PRIMARY KEY (`parent_name`,`parent_version`,`examples_KEY`),`ExampleObjMapEc` (`name`, `version`)
UNIQUE KEY `UK_ma8t20i58tt5ilqjrk2ged242` (`child_name`,`child_version`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CONSTRAINT `FK2n1x5g38x55wejkvnvhqin980` FOREIGN KEY (`parent_name`, `parent_version`)
REFERENCES `ExampleObjMap` (`name`, `version`),
CONSTRAINT `FKstwyuf69lrj4tjpafwriuwp3h` FOREIGN KEY (`child_name`, `child_version`)
REFERENCES `Example` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+|
+-----------------------------+-------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from ExampleObjMapEc;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+---------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+----------------+----------+---------------------------+--------------+
| name | version | EXAMPLES | EXAMPLES_KEY |
+---------------+---------+---------------------------+-----------------+
After saving the example:
| ExampleObjMap | 1.0.0 | .... <binary code> .... | MyKey1 |
| ExampleObjMap | 1.0.0 | .... <binary code> .... | MyKey2 |MariaDB [controlloop]> select * from ExampleObjMap;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+-------+--------+---------+
The image below shows the binary code.
Hibernate
MariaDB [controlloop]> select * from Exampledescribe ExampleObjMapEc;
+---------+-+-------------+------+-----+---------+-------+
| nameField | version | primedType | Null | Key | Default | timeStampExtra |
+---------+-----+---------+------+-----+---------+-------+
| example1 name | varchar(120) | 1.0.0NO | PRI | NULL | NULL |
| example2 | 1.0.0 version | varchar(20) | NO | PRI | NULL | NULL |
+---------+-+-------------+------+-----+---------+-------+
MariaDB [controlloop]> selectSHOW *CREATE fromTABLE ExampleObjMapJpaExampleObjMapEc_ExampleEXAMPLES;
+---------------+----------------+------------+---------------+--------------+
| parent_name | parent_version | child_name | child_version | examples_KEY |
+---------------+----------------+------------+---------------+--------------+
| ExampleObjMapTable | 1.0.0 | example1 Create Table | 1.0.0 | MyKey1 |
| ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+-----------------------------+--------------------------------------------------------+------------------+---------------+--------------+
Map of Objects using ElementCollection
...
language | java |
---|---|
title | JpaExampleObjMap |
...
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
...
...
...
...
...
...
...
...
...
...
...
`name` varchar(120) NOT NULL,
...
...
...
...
...
...
...
...
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjMapEc;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`version` varchar(20) NOT NULL,
`examples_name` varchar(120) NOT NULL,
`examples_version` varchar(20) NOT NULL,
`examples_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`examples_KEY`),
UNIQUE KEY `UK_o5b5lwcueiue1x7wf2j9l0ohb` (`examples_name`,`examples_version`),
CONSTRAINT `FKeu2s8p9mhstus32uliuwib8rr` FOREIGN KEY (`name`, `version`)
REFERENCES `name``ExampleObjMapEc` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL(`name`, `version`),
CONSTRAINT `EXAMPLES``FKf66pkeal9tdygic4pvspxhph5` longblobFOREIGN DEFAULT NULL,KEY (`examples_name`, `examples_version`)
`EXAMPLES_KEY` varchar(255) DEFAULT NULL,
KEY `FK_JpaExampleObjMapEc_EXAMPLES_name` REFERENCES `Example` (`name`, `version`),
CONSTRAINT `FK_JpaExampleObjMapEc_EXAMPLES_name` FOREIGN KEY (`name`, `version`)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 REFERENCES `ExampleObjMapEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+|
+--------------------------------+-------------------------------------------------------------------------------------------------------+
After saving the example:an example (it needs an extra step: save JpaExample objects before JpaExampleObjMapEc object):
MariaDB [controlloop]> select * from ExampleObjMapEc;
+-----------------+---------+
| name | version |
+-----------------+---------+
| ExampleObjMapExampleObjMapEc | 1.0.0 |
+-----------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLESExample;
+---------------+---------+---------------------------+--------------+
| name | version | EXAMPLES | EXAMPLES_KEYprimed | timeStamp |
+---------------+---------+--------+-------------------+--------------+
| ExampleObjMap+
| example1 | 1.0.0 | NULL .... <binary| code> ....NULL | MyKey1 |
| ExampleObjMapexample2 | 1.0.0 | .... <binary code> .... | MyKey2NULL | NULL |
+----------+-----+----+-----+---+-----------+
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+-------------+----+----------+
Hibernate
MariaDB [controlloop]> describe ExampleObjMapEc;
+---------+------+--------+------+-----+---------+-------+
| Fieldname | Type | Nullversion | Keyexamples_name | Defaultexamples_version | Extraexamples_KEY |
+-----------------+---------+-----+----------+------+------------+--------------+
| nameExampleObjMapEc | varchar(120)1.0.0 | example1 NO | PRI | NULL 1.0.0 | MyKey1 |
| versionExampleObjMapEc | varchar(20)1.0.0 | NOexample2 | PRI | NULL 1.0.0 | MyKey2 |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+-----------------------------+--------------+
Map of Objects using ElementCollection (fixed)
The below example shows ElementCollection with embedded class. In order to avoid collisions with field names ("name" and "version"), it has been used AttributeOverride annotation to change their names.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Data
@Embeddable
public class JpaExampleEmd implements Serializable {
private static final long serialVersionUID = 1L;
@VerifyKey
@NotNull
@AttributeOverride(name = "name", column = @Column(name = "child_name"))
@AttributeOverride(name = "version", column = @Column(name = "child_version"))
private ExampleKey key;
@Column
private Boolean primed;
@Column(name = "timeStamp", precision = 3)
@Temporal(TemporalType.TIMESTAMP)
@NotNull
private Date timeStamp;
}
@Entity
@Table(name = "ExampleObjMapEc")
@Data
public class JpaExampleObjMapEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
private Map<@NotNull String, @NotNull JpaExampleEmd> examples;
} |
Eclipse-Link
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------+------------------------------------+-------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
------------------------------------+
| Table | Create Table `examples_name` varchar(120) NOT NULL,
`examples_version` varchar(20) NOT NULL,
|
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`examples`EXAMPLES_KEY` varchar(255) NOTDEFAULT NULL,
`PRIMED` PRIMARY KEY (`name`,`version`,`examples_KEY`),
tinyint(1) DEFAULT 0,
UNIQUE KEY `UK_o5b5lwcueiue1x7wf2j9l0ohb` (`examples_name`,`examples_version`)`timeStamp` varchar(255) DEFAULT NULL,
CONSTRAINT `FKeu2s8p9mhstus32uliuwib8rr` FOREIGN KEY (`name`, `version`)
`child_name` varchar(255) DEFAULT NULL,
`child_version` varchar(255) DEFAULT NULL,
REFERENCES `ExampleObjMapEc` (`name`, `version`),
`name` varchar(120) DEFAULT NULL,
CONSTRAINT `FKf66pkeal9tdygic4pvspxhph5` FOREIGN KEY (`examples_name`, `examples_version`)
`version` varchar(20) DEFAULT NULL,
REFERENCES `Example`KEY `FK_JpaExampleObjMapEc_EXAMPLES_name` (`name`, `version`),
CONSTRAINT `FK_JpaExampleObjMapEc_EXAMPLES_name` FOREIGN KEY (`name`, `version`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
REFERENCES `ExampleObjMapEc` (`name`, `version`)
|
+--) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
After saving the example (it needs an extra step: save JpaExample objects before JpaExampleObjMapEc object):
MariaDB [controlloop]> select * from ExampleObjMapEc;
+-----------------+---------+
| name | version |
+-----------------+---------+
| ExampleObjMapEcExampleObjMap | 1.0.0 |
+-----------------+---------+
MariaDB [controlloop]> select * from ExampleJpaExampleObjMapEc_EXAMPLES;
+--------------+--------+---+--------+-----------+
| name | version | primed | timeStamp |
+-+-----------+----+-----+--------+--+---------+
| example1EXAMPLES_KEY | 1.0.0 PRIMED | timeStamp | NULL child_name | child_version | NULLname |
| example2 | 1.0.0 | NULL | NULLversion |
+----------+---------+--------+-----------+MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+-----------------+---------+---------------+------------------+--------------+
| nameMyKey2 | NULL | NULL | versionexample2 | examples_name1.0.0 | examples_versionExampleObjMap | examples_KEY |
+-----------------+---------+---------------+------------------+--------------+
| ExampleObjMapEc | 1.0.0 | example1 | 1.0.0 |
| MyKey1 | NULL | NULL | example1 | 1.0.0 | MyKey1 |
| ExampleObjMapEc | ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+
+--------------+--------+-----------+------------+---------------+---------------+---+------+
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`child_name` varchar(255) DEFAULT NULL,
`child_version` varchar(255) DEFAULT NULL,
`primed` bit(1) DEFAULT NULL,
`timeStamp` datetime(6) DEFAULT NULL,
`examples_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`examples_KEY`),
CONSTRAINT `FKeu2s8p9mhstus32uliuwib8rr` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjMapEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from ExampleObjMapEc;
+-----------------+---------+
| name | version |
+-----------------+---------+
| ExampleObjMapEc | 1.0.0 |
+-----------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+-----------------+---------+------------+---------------+--------+-----------+--------------+
| name | version | child_name | child_version | primed | timeStamp | examples_KEY |
+-----------------+---------+------------+---------------+--------+-----------+--------------+
| ExampleObjMapEc | 1.0.0 | example1 | 1.0.0 | NULL | NULL | MyKey1 |
| ExampleObjMapEc | 1.0.0 | example2 | 1.0.0 | NULL | NULL | MyKey2 |
+-----------------+---------+------------+---------------+--------+-----------+--------------+
Map of Objects using ElementCollection (hacked)
In this scenario JpaExampleLob class is not entity and neither embedded.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Data
public class JpaExampleLob implements Serializable {
private static final long serialVersionUID = 1L;
@VerifyKey
@NotNull
private ExampleKey key;
private Boolean primed;
@NotNull
private Date timeStamp;
}
@Entity
@Table(name = "ExampleObjMapEc")
@Data
public class JpaExampleObjMapEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
@Lob
private Map<@NotNull String, @NotNull JpaExampleLob> examples;
} |
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjMapEc_EXAMPLES;
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
| JpaExampleObjMapEc_EXAMPLES | CREATE TABLE `JpaExampleObjMapEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`examples` longblob DEFAULT NULL,
`examples_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`name`,`version`,`examples_KEY`),
CONSTRAINT `FKeu2s8p9mhstus32uliuwib8rr` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjMapEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------------+-------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from JpaExampleObjMapEc_EXAMPLES;
+-----------------+---------+---------------------------+--------------+
| name | version | examples | examples_KEY |
+-----------------+---------+---------------------------+--------------+
| ExampleObjMapEc | 1.0.0 | .... <binary code> .... | MyKey1 |
| ExampleObjMapEc | 1.0.0 | .... <binary code> .... | MyKey2 |
+-----------------+---------+---------------------------+--------------+
JpaToscaDataType_PROPERTIES table
The SQL code below shows JpaToscaDataType_PROPERTIES table.
Eclipse-Link
MariaDB [controlloop]> describe JpaToscaDataType_PROPERTIES;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name | varchar(120) | YES | | NULL | |
| version | varchar(20) | YES | | NULL | |
| PROPERTIES | longblob | YES | | NULL | |
| PROPERTIES_KEY | varchar(255) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SELECT count(1) FROM `JpaToscaDataType_PROPERTIES`;
+----------+
| count(1) |
+----------+
| 41 |
+----------+
MariaDB [controlloop]> describe ToscaProperty;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| DEFAULTVALUE | varchar(255) | YES | | NULL | |
| DESCRIPTION | varchar(255) | YES | | NULL | |
| ENTRYSCHEMA | longblob | YES | | NULL | |
| REQUIRED | tinyint(1) | YES | | 0 | |
| STATUS | int(11) | YES | | NULL | |
| parentLocalName | varchar(120) | NO | PRI | NULL | |
| localName | varchar(120) | NO | PRI | NULL | |
| parentKeyVersion | varchar(15) | NO | PRI | NULL | |
| parentKeyName | varchar(120) | NO | PRI | NULL | |
| name | varchar(120) | YES | | NULL | |
| version | varchar(20) | YES | | NULL | |
+------------------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SELECT count(1) FROM ToscaProperty;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
Note
Right now JpaToscaProperty will generate ToscaProperty table but this one will never used.
List of Objects using ElementCollection
ElementCollection should be used with basic type or embeddable class, this example shows side effects using it with entity.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleObjListEc")
@Data
public class JpaExampleObjListEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
@Lob
private List<@NotNull JpaExample> examples;
} |
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjListEc;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjListEc_EXAMPLES;
+------------------------------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------------+------------------------------------------------------------------------------------------------------+
| JpaExampleObjListEc_EXAMPLES | CREATE TABLE `JpaExampleObjListEc_EXAMPLES` (
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
`EXAMPLES` longblob DEFAULT NULL,
KEY `FK_JpaExampleObjListEc_EXAMPLES_name` (`name`,`version`),
CONSTRAINT `FK_JpaExampleObjListEc_EXAMPLES_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjListEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------------------+------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from ExampleObjListEc;
+-------------+---------+
| name | version |
+-------------+---------+
| ExampleList | 1.0.0 |
+-------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjListEc_EXAMPLES;
+-------------+---------+---------------------------+
| name | version | EXAMPLES |
+-------------+---------+---------------------------+
| ExampleList | 1.0.0 | .... <binary code> .... |
| ExampleList | 1.0.0 | .... <binary code> .... |
+-------------+---------+---------------------------+
Hibernate
MariaDB [controlloop]> describe ExampleObjListEc;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjListEc_EXAMPLES;
+------------------------------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------------+------------------------------------------------------------------------------------------------------+
| JpaExampleObjListEc_EXAMPLES | CREATE TABLE `JpaExampleObjListEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`examples_name` varchar(120) NOT NULL,
`examples_version` varchar(20) NOT NULL,
UNIQUE KEY `UK_8nnxsomci4yiwc8ks6radimiq` (`examples_name`,`examples_version`),
KEY `FK1a58hldurq1910ne2hbm9a7af` (`name`,`version`),
CONSTRAINT `FK1a58hldurq1910ne2hbm9a7af` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjListEc` (`name`, `version`),
CONSTRAINT `FKnj1lga10so090q9wf38k4jf7p` FOREIGN KEY (`examples_name`, `examples_version`)
REFERENCES `Example` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------------------+------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from Example;
+----------+---------+--------+-----------+
| name | version | primed | timeStamp |
+----------+---------+--------+-----------+
| example1 | 1.0.0 | NULL | NULL |
| example2 | 1.0.0 | NULL | NULL |
+----------+---------+--------+-----------+
MariaDB [controlloop]> select * from ExampleObjListEc;
+-------------+---------+
| name | version |
+-------------+---------+
| ExampleList | 1.0.0 |
+-------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjListEc_EXAMPLES;
+-------------+---------+---------------+------------------+
| name | version | examples_name | examples_version |
+-------------+---------+---------------+------------------+
| ExampleList | 1.0.0 | example1 | 1.0.0 |
| ExampleList | 1.0.0 | example2 | 1.0.0 |
+-------------+---------+---------------+------------------+
List of Objects using ElementCollection (fixed)
The below example shows ElementCollection with embedded class.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleObjListEc")
@Data
public class JpaExampleObjListEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
private List<@NotNull JpaExampleEmd> examples;
} |
Eclipse-Link
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjListEc_EXAMPLES;
+------------------------------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------------+------------------------------------------------------------------------------------------------------+
| JpaExampleObjListEc_EXAMPLES | CREATE TABLE `JpaExampleObjListEc_EXAMPLES` (
`PRIMED` tinyint(1) DEFAULT 0,
`timeStamp` varchar(255) DEFAULT NULL,
`child_name` varchar(255) DEFAULT NULL,
`child_version` varchar(255) DEFAULT NULL,
`name` varchar(120) DEFAULT NULL,
`version` varchar(20) DEFAULT NULL,
KEY `FK_JpaExampleObjListEc_EXAMPLES_name` (`name`,`version`),
CONSTRAINT `FK_JpaExampleObjListEc_EXAMPLES_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjListEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------------------+------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from ExampleObjListEc;
+-------------+---------+
| name | version |
+-------------+---------+
| ExampleList | 1.0.0 |
+-------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjListEc_EXAMPLES;
+--------+-----------+------------+---------------+-------------+---------+
| PRIMED | timeStamp | child_name | child_version | name | version |
+--------+-----------+------------+---------------+-------------+---------+
| NULL | NULL | example1 | 1.0.0 | ExampleList | 1.0.0 |
| NULL | NULL | example2 | 1.0.0 | ExampleList | 1.0.0 |
+--------+-----------+------------+---------------+-------------+---------+
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjListEc_EXAMPLES;
+------------------------------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------------+------------------------------------------------------------------------------------------------------+
| JpaExampleObjListEc_EXAMPLES | CREATE TABLE `JpaExampleObjListEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`child_name` varchar(255) DEFAULT NULL,
`child_version` varchar(255) DEFAULT NULL,
`primed` bit(1) DEFAULT NULL,
`timeStamp` datetime(6) DEFAULT NULL,
KEY `FK1a58hldurq1910ne2hbm9a7af` (`name`,`version`),
CONSTRAINT `FK1a58hldurq1910ne2hbm9a7af` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjListEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------------------+------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from ExampleObjListEc;
+-------------+---------+
| name | version |
+-------------+---------+
| ExampleList | 1.0.0 |
+-------------+---------+
MariaDB [controlloop]> select * from JpaExampleObjListEc_EXAMPLES;
+-------------+---------+------------+---------------+--------+-----------+
| name | version | child_name | child_version | primed | timeStamp |
+-------------+---------+------------+---------------+--------+-----------+
| ExampleList | 1.0.0 | example1 | 1.0.0 | NULL | NULL |
| ExampleList | 1.0.0 | example2 | 1.0.0 | NULL | NULL |
+-------------+---------+------------+---------------+--------+-----------+
List of Objects using ElementCollection (hacked)
In this scenario JpaExampleLob class is not entity and neither embedded.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleObjListEc")
@Data
public class JpaExampleObjListEc implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@ElementCollection
@Lob
private List<@NotNull JpaExampleLob> examples;
} |
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE JpaExampleObjListEc_EXAMPLES;
+------------------------------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------------+------------------------------------------------------------------------------------------------------+
| JpaExampleObjListEc_EXAMPLES | CREATE TABLE `JpaExampleObjListEc_EXAMPLES` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`examples` longblob DEFAULT NULL,
KEY `FK1a58hldurq1910ne2hbm9a7af` (`name`,`version`),
CONSTRAINT `FK1a58hldurq1910ne2hbm9a7af` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjListEc` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------------------+------------------------------------------------------------------------------------------------------+
After saving an example:
MariaDB [controlloop]> select * from JpaExampleObjListEc_EXAMPLES;
+-------------+---------+---------------------------+
| name | version | examples |
+-------------+---------+---------------------------+
| ExampleList | 1.0.0 | .... <binary code> .... |
| ExampleList | 1.0.0 | .... <binary code> .... |
+-------------+---------+---------------------------+
Map of Objects using OneToMany
OneToMany is not used in tosca model template. The example below shows an alternative to ElementCollection. In this example there is a field names collision.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleObjMap")
@Data
public class JpaExampleObjMap implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@Lob
private Map<@NotNull String, @NotNull JpaExample> examples;
} |
Representation of the example in YAML of what we could save using JpaExampleObjMap entity.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
ExampleObjMap:
name: ExampleObjMap
version: 1.0.0
examples:
MyKey1:
name: example1
version: 1.0.0
MyKey2:
name: example2
version: 1.0.0 |
Eclipse-Link
MariaDB [controlloop]> describe ExampleObjMap;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EXAMPLES | longblob | YES | | NULL | |
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+----------+--------------+------+-----+---------+-------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMap;
+--------------------------+---------------+---------+
| EXAMPLES | name | version |
+--------------------------+---------------+---------+
| .... <binary code> .... | ExampleObjMap | 1.0.0 |
+--------------------------+---------------+---------+
Removing @Lob Annotation
The keys name and version of the JpaExampleObjMap as the same as JpaExample, so Eclipse-Link creates a wrong ExampleObjMap_Example table.
MariaDB [controlloop]> describe ExampleObjMap;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(120) | NO | PRI | NULL | |
| version | varchar(20) | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`name` varchar(120) NOT NULL,
`version` varchar(20) NOT NULL,
`EXAMPLES_KEY` varchar(255) DEFAULT NULL,
PRIMARY KEY (`name`,`version`),
CONSTRAINT `FK_ExampleObjMap_Example_name` FOREIGN KEY (`name`, `version`)
REFERENCES `ExampleObjMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
Using these tables I have got this error: Cannot add or update a child row: a foreign key constraint fails (`controlloop`.`ExampleObjMap_Example`, CONSTRAINT `FK_ExampleObjMap_Example_name` FOREIGN KEY (`name`, `version`) REFERENCES `ExampleObjMap` (`name`, `version`))
Error Code: 1452
Map of Objects using OneToMany (fixed)
The example below shows an alternative to ElementCollection with the fix to avoid field names collision.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
@Entity
@Table(name = "ExampleObjMap")
@Data
public class JpaExampleObjMap implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
@VerifyKey
@NotNull
private ExampleKey key;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(
joinColumns = {@JoinColumn(name = "parent_name", referencedColumnName = "name"),
@JoinColumn(name = "parent_version", referencedColumnName = "version")},
inverseJoinColumns = {@JoinColumn(name = "child_name", referencedColumnName = "name"),
@JoinColumn(name = "child_version", referencedColumnName = "version")})
private Map<@NotNull String, @NotNull JpaExample> examples;
} |
Eclipse-Link
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`parent_name` varchar(120) NOT NULL,
`parent_version` varchar(20) NOT NULL,
`child_name` varchar(120) NOT NULL,
`child_version` varchar(20) NOT NULL,
`EXAMPLES_KEY` varchar(255) DEFAULT NULL,
PRIMARY KEY (`parent_name`,`parent_version`,`child_name`,`child_version`),
KEY `FK_ExampleObjMap_Example_child_name` (`child_name`,`child_version`),
CONSTRAINT `FK_ExampleObjMap_Example_child_name` FOREIGN KEY (`child_name`, `child_version`)
REFERENCES `Example` (`name`, `version`),
CONSTRAINT `FK_ExampleObjMap_Example_parent_name` FOREIGN KEY (`parent_name`, `parent_version`)
REFERENCES `ExampleObjMap` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMap;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+---------------+---------+
MariaDB [controlloop]> select * from Example;
+--------+-----------+----------+---------+
| PRIMED | timeStamp | name | version |
+--------+-----------+----------+---------+
| NULL | NULL | example1 | 1.0.0 |
| NULL | NULL | example2 | 1.0.0 |
+--------+-----------+----------+---------+
MariaDB [controlloop]> select * from ExampleObjMap_Example;
+---------------+----------------+------------+---------------+--------------+
| parent_name | parent_version | child_name | child_version | EXAMPLES_KEY |
+---------------+----------------+------------+---------------+--------------+
| ExampleObjMap | 1.0.0 | example1 | 1.0.0 | MyKey1 |
| ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+---------------+----------------+------------+---------------+--------------+
Hibernate
MariaDB [controlloop]> SHOW CREATE TABLE ExampleObjMap_Example;
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
| ExampleObjMap_Example | CREATE TABLE `ExampleObjMap_Example` (
`parent_name` varchar(120) NOT NULL,
`parent_version` varchar(20) NOT NULL,
`child_name` varchar(120) NOT NULL,
`child_version` varchar(20) NOT NULL,
`examples_KEY` varchar(255) NOT NULL,
PRIMARY KEY (`parent_name`,`parent_version`,`examples_KEY`),
UNIQUE KEY `UK_ma8t20i58tt5ilqjrk2ged242` (`child_name`,`child_version`),
CONSTRAINT `FK2n1x5g38x55wejkvnvhqin980` FOREIGN KEY (`parent_name`, `parent_version`)
REFERENCES `ExampleObjMap` (`name`, `version`),
CONSTRAINT `FKstwyuf69lrj4tjpafwriuwp3h` FOREIGN KEY (`child_name`, `child_version`)
REFERENCES `Example` (`name`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------------+-------------------------------------------------------------------------------------------------------------+
After saving the example:
MariaDB [controlloop]> select * from ExampleObjMap;
+---------------+---------+
| name | version |
+---------------+---------+
| ExampleObjMap | 1.0.0 |
+---------------+---------+
MariaDB [controlloop]> select * from Example;
+----------+---------+--------+-----------+
| name | version | primed | timeStamp |
+----------+---------+--------+-----------+
| example1 | 1.0.0 | NULL | NULL |
| example2 | 1.0.0 | NULL | NULL |
+----------+---------+--------+-----------+
MariaDB [controlloop]> select * from ExampleObjMap_Example;
+---------------+----------------+------------+---------------+--------------+
| parent_name | parent_version | child_name | child_version | examples_KEY |
+---------------+----------------+------------+---------------+--------------+
| ExampleObjMap | 1.0.0 | example1 | 1.0.0 | MyKey1 |
| ExampleObjMap | 1.0.0 | example2 | 1.0.0 | MyKey2 |
+---------------+----------------+------------+---------------+--------------+
Performance issue
Using current version, for each operation save/read/delete of the service template below, control-loop runtime application access to 14 tables and handles 51 rows.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
name: ToscaServiceTemplateSimple
version: 1.0.1
tosca_definitions_version: tosca_simple_yaml_1_3
data_types:
onap.datatypes.ToscaConceptIdentifier:
derived_from: tosca.datatypes.Root
properties:
name:
type: string
required: true
version:
type: string
required: true
node_types:
org.onap.policy.clamp.controlloop.Participant:
version: 1.0.1
derived_from: tosca.nodetypes.Root
properties:
provider:
type: string
requred: false
org.onap.policy.clamp.controlloop.ControlLoopElement:
version: 1.0.1
derived_from: tosca.nodetypes.Root
properties:
provider:
type: string
requred: false
participantType:
type: onap.datatypes.ToscaConceptIdentifier
requred: true
startPhase:
type: integer
required: false
constraints:
- greater_or_equal: 0
metadata:
common: true
description: check wiki documentation about start phase
stopped simultaneously
org.onap.policy.clamp.controlloop.ControlLoop:
version: 1.0.1
derived_from: tosca.nodetypes.Root
properties:
provider:
type: string
requred: false
elements:
type: list
required: true
entry_schema:
type: onap.datatypes.ToscaConceptIdentifier
org.onap.policy.clamp.controlloop.K8SMicroserviceControlLoopElement:
version: 1.0.1
derived_from: org.onap.policy.clamp.controlloop.ControlLoopElement
properties:
chart:
type: string
required: true
configs:
type: list
required: false
requirements:
type: string
requred: false
templates:
type: list
required: false
entry_schema:
values:
type: string
requred: true
topology_template:
node_templates:
org.onap.k8s.controlloop.K8SControlLoopParticipant:
version: 2.3.4
type: org.onap.policy.clamp.controlloop.Participant
type_version: 1.0.1
description: Participant for K8S
properties:
provider: ONAP
org.onap.domain.database.HelloWorld_K8SMicroserviceControlLoopElement:
# Chart from any chart repository configured on helm client.
version: 1.2.3
type: org.onap.policy.clamp.controlloop.K8SMicroserviceControlLoopElement
type_version: 1.0.0
description: Control loop element for the K8S microservice for Hello World
properties:
provider: ONAP
participantType:
name: org.onap.k8s.controlloop.K8SControlLoopParticipant1
version: 2.3.4
chart:
chartId:
name: dummy
version: 0.1.0
releaseName: test
namespace: test
org.onap.domain.database.PMSH_K8SMicroserviceControlLoopElement:
# Chart from local file system
version: 1.2.3
type: org.onap.policy.clamp.controlloop.K8SMicroserviceControlLoopElement
type_version: 1.0.0
description: Control loop element for the K8S microservice for PMSH
properties:
provider: ONAP
participantType:
name: org.onap.k8s.controlloop.K8SControlLoopParticipant2
version: 2.3.4
startPhase: 1
chart:
chartId:
name: dcae-pmsh
version: 8.0.0
namespace: onap
releaseName: pmshms
repository:
repoName: chartmuseum
protocol: http
address: 10.152.183.120
port: 80
userName: onapinitializer
password: demo123456!
overrideParams:
global.masterPassword: test
org.onap.domain.database.Local_K8SMicroserviceControlLoopElement:
# Chart installation without passing repository name
version: 1.2.3
type: org.onap.policy.clamp.controlloop.K8SMicroserviceControlLoopElement
type_version: 1.0.0
description: Control loop element for the K8S microservice for local chart
properties:
provider: ONAP
participantType:
name: org.onap.k8s.controlloop.K8SControlLoopParticipant
version: 2.3.4
chart:
chartId:
name: nginx-ingress
version: 0.9.1
releaseName: nginxms
namespace: test
repository:
repoName: nginx-stable
org.onap.domain.sample.GenericK8s_ControlLoopDefinition:
version: 1.2.3
type: org.onap.policy.clamp.controlloop.ControlLoop
type_version: 1.0.0
description: Control loop for Hello World
properties:
provider: ONAP
elements:
- name: org.onap.domain.database.HelloWorld_K8SMicroserviceControlLoopElement
version: 1.2.3
- name: org.onap.domain.database.PMSH_K8SMicroserviceControlLoopElement
version: 1.2.3
- name: org.onap.domain.database.Local_K8SMicroserviceControlLoopElement
version: 1.2.3
|
Multi templates
Right now using Spring repositories it is possible to create more than one service template, as service templates in policy-models/tosca.
How to create additional service template (just add different name and version into the yaml file):
Code Block | ||||
---|---|---|---|---|
| ||||
name: ToscaServiceTemplateSimple
version: 1.0.1
tosca_definitions_version: tosca_simple_yaml_1_3
........................... |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
MariaDB [controlloop]> select * from ToscaServiceTemplate;
+-------------+-------------------------+-------------------+----------------------+----------------------------+---------+------------------------+---------------------+----------------------+------------------+------------------+----------------------+-----------------+--------------------+--------------------------+-----------------------+-----------------------------+-------------------------------+----------------------------------+---------------------------------+
| DESCRIPTION | TOSCADEFINITIONSVERSION | derived_from_name | derived_from_version | name | version | capabilityTypesVersion | capabilityTypesName | dataTypesName | dataTypesVersion | nodeTypesVersion | nodeTypesName | policyTypesName | policyTypesVersion | relationshipTypesVersion | relationshipTypesName | topologyTemplateLocalName | topologyTemplateParentKeyName | topologyTemplateParentKeyVersion | topologyTemplateParentLocalName |
+-------------+-------------------------+-------------------+----------------------+----------------------------+---------+------------------------+---------------------+----------------------+------------------+------------------+----------------------+-----------------+--------------------+--------------------------+-----------------------+-----------------------------+-------------------------------+----------------------------------+---------------------------------+
| NULL | tosca_simple_yaml_1_3 | NULL | NULL | ToscaServiceTemplateSimple | 1.0.0 | NULL | NULL | ToscaDataTypesSimple | 1.0.0 | 1.0.0 | ToscaNodeTypesSimple | NULL | NULL | NULL | NULL | ToscaTopologyTemplateSimple | ToscaServiceTemplateSimple | 1.0.0 | NULL |
| NULL | tosca_simple_yaml_1_3 | NULL | NULL | ToscaServiceTemplateSimple | 1.0.1 | NULL | NULL | ToscaDataTypesSimple | 1.0.0 | 1.0.0 | ToscaNodeTypesSimple | NULL | NULL | NULL | NULL | ToscaTopologyTemplateSimple | ToscaServiceTemplateSimple | 1.0.0 | NULL |
+-------------+-------------------------+-------------------+----------------------+----------------------------+---------+------------------------+---------------------+----------------------+------------------+------------------+----------------------+-----------------+--------------------+--------------------------+-----------------------+-----------------------------+-------------------------------+----------------------------------+---------------------------------+
MariaDB [controlloop]> select * from ToscaNodeTemplates;
+--------------------------+---------+
| name | version |
+--------------------------+---------+
| ToscaNodeTemplatesSimple | 1.0.0 |
+--------------------------+---------+ |
In the above example for ToscaNodeTemplate we are using same name and version, so the ToscaNodeTemplates table (and all other tables as well) contains only one record. Using the current entity model definition there are the follow issues:
- There is no way to handle different configuration
- Is impossible to delete a template in cascade, but it needs a Java code to manual delete single records in all tables
At least to avoid the above issues:
- Avoid default name and version (for each template/node/data type, etc.. name and version have to be mandatory)
- Java code to manual check for each table if name and version are already used
Conclusion
- Move to Hibernate with EclipseLink tables is probably possible.
- The presence of longblob types used to store whole objects is an issues, it is readable only by Java language or by any other language compatible with Java binary code. I am not sure if a db-migrator can handle that.
- Document databases store all information for a given object in a single instance in the database, and every stored object can be different from every other. Each operation save/read/delete of a service template, could be done using one access to a document. So, using Cassandra/MongoDB will solve all issues.
JpaRepository
and CassandraRepository
/MongoRepository
extendCrudRepository
and expose the capabilities of the underlying persistence technology in addition to the rather generic persistence technology-agnostic interfaces like e.g.CrudRepository
.