In the last part of data materialization series, we take a look at building an automated process for materialization of all (or part) of metadata stored in MMX.

The proposed automated process in a cycle over object types that utilizes results from steps 2, 3 and 4.

But definition, in MMX only instances of non-abstract classes can exist, so, when looking for objects types to create views for we should exclude those.

SELECT 
	object_type_cd, 
	object_type_nm
FROM
	MD_OBJECT_TYPE
WHERE
	abstract_ind = 0

Additionally, we would like to exclude from materialization process all enumeration type object type. Identifying an enumeration object type is trickier so let's agree on the following definition of enumeration: enumeration object type does not have properties nor relations types and descends directly from Metamodel types class and it does not have any child class types, i.e.

...
AND object_type_cd NOT IN (
	SELECT 
		thisType.object_type_cd
	FROM 
		MD_OBJECT_TYPE thisType
	JOIN
		MD_OBJECT_TYPE parentType 
		ON parentType.object_type_cd = thisType.parent_object_type_cd
		AND parentType.parent_object_type_cd = mmxmd.object_type_code('MMXFramework', 'Metamodel')
	LEFT OUTER JOIN
			(SELECT 
				object_type_cd, 
				count(property_type_cd) prop_count
			FROM
				MD_PROPERTY_TYPE
			GROUP BY object_type_cd) propTypes
		ON propTypes.object_type_cd = thisType.object_type_cd
		AND propTypes.prop_count = 0
	LEFT OUTER JOIN
			(SELECT 
				object_type_cd, 
				count(relation_type_cd) rel_count
			FROM
				MD_RELATION_TYPE
			GROUP BY object_type_cd) fromRelTypes
		ON fromRelTypes.object_type_cd = thisType.object_type_cd
		AND fromRelTypes.rel_count = 0
	LEFT OUTER JOIN
			(SELECT 
				related_object_type_cd object_type_cd, 
				count(relation_type_cd) rel_count
			FROM
				MD_RELATION_TYPE
			GROUP BY related_object_type_cd) toRelTypes
		ON toRelTypes.object_type_cd = thisType.object_type_cd
		AND toRelTypes.rel_count = 0 
	LEFT OUTER JOIN 
			(SELECT 
				parent_object_type_cd object_type_cd,
				count(object_type_cd) child_count
			FROM 
				MD_OBJECT_TYPE
			GROUP BY parent_object_type_cd) childTypes
 		ON childTypes.object_type_cd = thisType.object_type_cd
		AND childTypes.child_count = 0 
	WHERE 
		  propTypes.object_type_cd IS NULL
		  AND fromRelTypes.object_type_cd IS NULL
		  AND toRelTypes.object_type_cd IS NULL
		AND childTypes.object_type_cd IS NULL)

Complete list of articles in the materialization series:

Part 1: Materialization choices
Part 2: Materializing objects and properties
Part 3: Materializing objects and properties: what was left behind
Part 4: Materializing many-to-many relations
Part 5: Builing an automated materialization engine

One of the main usage for xdtl:for is looping thought a rowset that came from xdtl:fetch. Observe

<xdtl:fetch source="SELECT object_id, object_nm FROM MD_OBJECT WHERE object_type_cd = 1" rowset="metamodels" connection="$cn" />
<xdtl:for item="metamodel" rowset="metamodels">
	<xdtl:log msg="Found metamodel named ${metamodel[1]} with id ${metamodel[0]}" />
</xdtl:for>

Before we get to the point, one little thing. Check if returned rowset is not empty:

<xdtl:if expr="${metamodel.isEmpty()">
	<xdtl:error msg="No metamodels found" />
</xdtl:if>

Often the rowset may be defined in a input parameter as a coma-separated list. In that case you still can loop through it by using Java split function. Observe, that you have to convert the splited array into list of arrays:

<xdtl:send source="Metamodel1,Metamodel2,Metamodel3" target="metamodel_list" overwrite="1" />
<xdtl:for rowset="${java.util.Arrays.asList(metamodel_list.split(/,/))}">
	<xdtl:log msg="Found metamodel named ${metamodel[0]}" />
</xdtl:for>

In the previous part of these series we build a view generator for multiple properties. Quite analogues to that is the work we do in this part of the series.

There is only one correct way of presenting many-to-many relations and that is MD_RELATION table. But MD_RELATION column names are quite ambiguous to a novice user or system being integrated, so the sole purpose of such a view would be add semantic labels to the columns and objects.

Continuing with materialization of object type $ObjectType with object type code $ObjectTypeCd:

FOR EACH $MultRelation IN 
SELECT
	relType1.relation_type_cd,
	relType1.relation_type_nm,
	relType1.related_relation_type_nm,
	objType1.object_type_nm,
	objType2.object_type_nm as related_object_type_cd
FROM
	MD_RELATION_TYPE relType1
JOIN
	MD_OBJECT_TYPE objType1
	ON objType1.object_type_cd = relType1.object_type_cd
JOIN
	MD_OBJECT_TYPE objType2
	ON objType2.object_type_cd = relType1.related_object_type_cd
WHERE
	relType1.object_type_cd = $ObjectTypeCd
	AND relType1.multiplicity_ind = 1

We build view:

CREATE VIEW v_$MultRelation.relation_type_nm_$MultRelation.related_relation_type_nm AS
SELECT
	rel1.object_id AS $MultRelation.object_type_nm_id,
	rel1.related_object_id AS $MultRelation.related_object_type_cd,
	rel1.changed_dt,
	rel1.changed_by
FROM
	MD_RELATION rel1
WHERE
	relation_type_cd = $MultRelation.relation_type_cd
	AND state_ind = 1

In the next, and the last part of the series, we will look at what we need to know to build an automated process for materializing all or part of MMX metadata.