SQLArchiViews.sql

DROP VIEW VW_Elements_Latest_Model;
CREATE VIEW VW_Elements_Latest_Model AS
SELECT
elements.id AS Element_ID,
elements.name AS Element_Name,
elements.documentation AS Element_Documentation,
elements.class AS Element_Class,
elements.version AS Element_Version,
elements_in_model.model_version
FROM elements_in_model
INNER JOIN elements ON elements_in_model.element_id = elements.id
WHERE elements_in_model.model_version = (SELECT max(version) FROM models)
AND elements_in_model.element_version = elements.version
;

DROP VIEW VW_Relations_Latest_Model;
CREATE VIEW VW_Relations_Latest_Model AS
SELECT
relationships.id AS Relation_ID,
relationships.name AS Relation_Name,
relationships.documentation AS Relation_Documentation,
relationships.class AS Relation_Class,
relationships.source_ID AS Relation_Source_ID,
relationships.target_ID AS Relation_Target_ID,
relationships.version AS Relation_Version,
relationships_in_model.model_version
FROM relationships_in_model
INNER JOIN relationships ON relationships_in_model.relationship_id = relationships.id
WHERE relationships_in_model.model_version = (SELECT max(version) FROM models)
AND relationships_in_model.relationship_version = relationships.version
;

DROP VIEW VW_Properties_Latest_Model;
CREATE VIEW VW_Properties_Latest_Model AS
SELECT
properties.parent_id AS Element_ID
,elements.name AS Element_Name
,properties.parent_version AS Element_Version
,properties.Name AS Properties_Key
,properties.Value AS Properties_Value
,properties.rank AS Properties_Rank
FROM properties
INNER JOIN elements ON 
elements.id = properties.parent_id
AND elements.version = properties.parent_version
INNER JOIN elements_in_model ON elements_in_model.element_id = elements.id
WHERE elements_in_model.model_version = (SELECT max(version) FROM models)
AND elements_in_model.element_version = elements.version
--AND properties.Name NOT like 'ArchiElementID'
;

SQLSparxViews.sql

DROP VIEW VW_Elements_Latest_Model;
DROP VIEW VW_Relations_Latest_Model;

CREATE VIEW VW_Elements_Latest_Model AS
SELECT
CAST (object_id AS VARCHAR(255)) AS Element_ID,
name AS Element_Name,
note AS Element_Documentation,
stereotype AS Element_ClASs,
version AS Element_Version
FROM public.t_object
WHERE stereotype LIKE'ArchiMate_%'
;

CREATE VIEW VW_Relations_Latest_Model AS
SELECT
CAST (connector_id AS VARCHAR(255)) AS Relation_ID,
name AS Relation_Name,
notes AS Relation_Documentation,
stereotype AS Relation_ClASs,
CAST (start_object_id AS VARCHAR(255)) AS Relation_Source_ID,
CAST (end_object_id AS VARCHAR(255)) AS Relation_Target_ID
--relationships.version AS Relation_Version,
--relationships_in_model.model_version
FROM public.t_connector
WHERE stereotype LIKE'ArchiMate_%'
;