This article details the use and interest of a very important feature of administering an EA repository, namely checking the integrity of the repository.
This text was originally written by Piotr Trętowski, in 2012, at the following address
With the explicit agreement of Piotr Trętowski, we have translated the content and adapted it to the most recent version of EA.
Checking the integrity of an EA repository
A repository (project) created in Enterprise Architect is stored as a relational database. Regardless of the database engine used (MS Jet for EAP, MS SQL, MySQL, Oracle or other files), the same database schema is used to store models. The relational database data must be consistent.
This consistency can be ensured in a technical way by introducing specific constraints, then the database engine ensures compliance with the defined rules.
Repository integrity check option
Starting from the business logic related to data processing and basic concepts such as package, element, relationship, attribute or method, a functionality has been implemented from EA to check the consistency of the repository from the user interface level. This function is called "Project Integrity Check" and is available by selecting:
Configure -> Model -> Integrity Check -> Project Integrity in the ribbon menu version 14.x.
The following window appears when this option is called:
By default, Report only is selected, so you can check data integrity without making any changes. If you decide to automatically solve the problems you have encountered, you should choose "Recover/Clean".
In practice, first perform the first execution in Report mode only, and if irregularities occur, then perform the corrective execution of the Report / Cleanup mode. Before that, make a backup.
The scope of the check can be changed by checking and unchecking the options in the "Checks to run" section.
An example of the result is shown in the figure below.
For "recovery /clean", a warning is displayed before the repair operation due to integrity problems is performed.
As a result of cleaning a repository, there may be data that has been found inconsistent and that can be erased or recovered.
The recovered data is visible in the project browser window in a special "root node" named _recovered_.
When should the repository integrity check be applied ?
If a repository contains data that is not consistent with the EA program, EA does not inform us with error messages. EA simply presents the data as best it can. There are probably mechanisms in the code to display or even modify such data.
Inconsistencies in the repository can contribute to a negative impact on performance, which can be felt in more complex operations such as Model Compare, for example.
This integrity check function should be used mainly when there is a shared repository, as well as in the case of a local project developed by a single person. Perhaps only with the difference that the probability of inconsistency is higher in the first case.
Risks associated with using repository integrity checking
Triggering the repair of data integrity problems remains a risky operation.
The repository administrator who presses the OK button assumes responsibility for changes that will be made outside his control.
More importantly, the program does not inform on the model objects that these changes concern! We only receive messages like:
- Invalid connector, and the recommended action (the one to be taken) is - Remove the connector,
- Orphan object with action: Delete the packet object,
- 91 Stereotype orphan element with action: Delete orphan stereotypes.
That sounds pretty destructive.
However, it should be noted that if these are important modelling elements, the problem will naturally have already been detected and identified.
Most often, these are outdated and lost items and objects that will be deleted, that remained in the repository and were invisible at the project browser or diagram level.
To avoid unexpected use of this feature, it is important to enable security and limit repository integrity check permissions to a specific user group.
Before any such operation, you must make a full backup of the repository.
List of requests made for project integrity control (Queries)
In order to have some control over the automatic mechanisms used by the EA program, here is a list of SQL queries that are executed to check the integrity of the repository.
If EA finds irregularities, we can possibly modify the request to return object names or identifiers, for example, instead of returning the count(*) number.
SQL Query | Result | |||
Package structure | ||||
1 | SELECT Count(*) as RecCnt FROM t_package child LEFT JOIN t_package parent on parent.Package_ID = child.Parent_ID where parent.Package_ID Is NULL and child.Parent_ID <> 0 | Returns orphaned packages that do not have a parent or slave package. | Orphaned package | |
Object Structure | ||||
2 | SELECT t_diagram.Diagram_ID, t_diagram.Name, t_diagram.ParentID, t_object.Object_ID FROM t_diagram LEFT OUTER JOIN t_object ON t_diagram.Package_ID = t_object.Package_ID AND t_diagram.ParentID = t_object.Object_ID WHERE (t_object.Object_ID IS NULL) AND (t_diagram.ParentID <> 0) | Returns orphan diagrams that are not attached to any package or element. | Orphaned diagram | |
3 | SELECT t_diagram.Diagram_ID, t_diagram.Name, t_diagram.ParentID, 'NULL' FROM t_diagram WHERE t_diagram.ParentID IS NULL | Another form of diagram search, which must be linked to an element via its parent. | Orphaned diagram | |
4 | select t_object.Name as OName, t_object.Object_ID as ID from t_object left join t_package on t_object.PDATA1 = t_package.Package_ID where t_package.Package_ID is null and t_object.Object_Type = 'Package' | Returns package elements for which there is no equivalent in the t_package table. | Invalid package object | |
5 | select t_diagram.Diagram_ID as DiagramID, t_diagram.Name as DiagramName, t_diagram.Diagram_Type as DiagramType from t_diagram left join t_package on t_package.Package_ID = t_diagram.Package_ID where t_package.Package_ID is null or t_package.Parent_ID = 0 | Also returns orphan diagrams that are not attached to any package or element. | Orphaned diagram | |
6 | SELECT t_package.Package_ID, t_package.Name AS PackageName, t_object.Name AS ObjectName FROM t_package LEFT OUTER JOIN t_object ON (Object_Type='Package' AND t_package.Package_ID = t_object.PDATA1) WHERE t_package.Name <> t_object.Name | Returns packages whose name differs from the package equivalent in table t_object. | Invalid package object | |
7 | SELECT COUNT(*) AS RecCnt FROM t_package WHERE Package_ID = Parent_ID | Returns the number of packages that are self-relatives. | Invalid package object OR Orphaned package | |
8 | Select count(*) as RecCnt from t_object left join t_package on t_package.Package_ID = t_object.Package_ID where t_package.Package_ID is null and (t_object.ParentID is null or t_object.ParentID=0) | Returns the number of packages for which there is no equivalent in table t_object. | Invalid package object | |
9 | Select count(*) as RecCnt FROM t_object child left join t_object parent on child.ParentID = parent.Object_ID where child.ParentID is not null and child.ParentID <> 0 and parent.Object_ID is null | Returns the number of elements that are related by themselves. | Object parent invalid OR Orphaned object | |
10 | Select count(*) as RecCnt FROM t_object where ParentID = Object_ID | Other form for the above request | Object parent invalid OR Orphaned object | |
11 | Select count(*) as RecCnt FROM t_object child left join t_object parent on child.ParentID = parent.Object_ID and parent.ParentID = child.Object_ID where parent.Object_ID is not null and child.Object_ID <> child.ParentID | Other form for the above request | Object parent invalid OR Orphaned object | |
12 | Select count(*) as RecCnt FROM t_object child left join t_object parent on child.ParentID = parent.Object_ID where parent.Object_Type = 'Package' | Another way to search for packages that are related to themselves. | Invalid package object OR Orphaned package | |
Characteristics of the objects | ||||
13 | Select Object_ID, Name FROM t_object where Object_Type is null or Object_Type = '' or ( Name='EA_IMPORT_STUB' and Package_ID=1 and Object_Type='Class') | Returns items that do not have a specific type. | Invalid Object Types | |
14 | select f.Object_ID, f.FileName from t_objectfiles f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns attributes of a'File' element that are not associated with any element. | ||
15 | select f.Object_ID, f.Effort from t_objecteffort f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns attributes of an'Effort' element that are not associated with any element. | ||
16 | select f.Object_ID, f.Metric from t_objectmetrics f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns attributes of a'Metric' element that are not associated with any element. | ||
17 | select f.Object_ID, f.Problem from t_objectproblems f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns the attributes of a'Problem' element that are not assigned to any element. | ||
18 | select f.Object_ID, f.Requirement from t_objectrequires f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns the attributes of an element of type'Requirement'[internal] that are not assigned to any element. | ||
19 | select f.Object_ID, f.`Resource` from t_objectresource f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns attributes of a'Resource' element that are not assigned to any element. | ||
20 | select f.Object_ID, f.Risk from t_objectrisks f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns the attributes of a'Risk' element that are not assigned to any element. | ||
21 | select f.Object_ID, f.Scenario from t_objectscenarios f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns the attributes of a'Scenario' element that are not assigned to any element. | ||
22 | select f.Object_ID, f.Test from t_objecttests f left join t_object o on f.Object_ID = o.Object_ID where o.Object_ID is null | Returns the attributes of a'Test' element that are not assigned to any element. | ||
23 | select Object_ID, ConstraintType from t_objectconstraint where ConstraintType is null or ConstraintType='' | Returns attributes of a'Constraint' element that do not have a specific type. | ||
24 | select Object_ID, `Constraint` from t_objectconstraint where `Constraint` is null or `Constraint`='' | Returns attributes of a constraint element that do not have a specific name. | ||
25 | select Instance_ID, 'Missing Object' as Problem from t_diagramobjects l left join t_object c on l.Object_ID = c.Object_ID where c.Object_ID is null UNION select Instance_ID, 'Missing Diagram' as Problem from t_diagramobjects l left join t_diagram d on l.Diagram_ID = d.Diagram_ID where d.Diagram_ID is null | Returns missing objects to be displayed in missing diagrams and diagrams for which there are objects to be displayed. | Invalid object geometry. ITEM: Missing Diagram AND/OR Invalid object geometry. ITEM: Missing Object | |
26 | Select child.Object_ID as ID, child.Name as ObjectName, child.Object_Type, parent.Package_ID as PkgID FROM t_object child, t_object parent where child.ParentID = parent.Object_ID and child.Package_ID <> parent.Package_ID | Returns orphaned items that have an incorrectly selected parent item. | Orphaned object | |
27 | select op.* from t_operation op left join t_object obj on op.Object_ID = obj.Object_ID where obj.Object_ID is null | Returns operations that are not assigned to any element. | ||
28 | select att.* from t_attribute att left join t_object obj on att.Object_ID = obj.Object_ID where obj.Object_ID is null | Returns attributes that are not assigned to any element. | ||
29 | select p.* from t_objectproperties p left join t_object o on p.Object_ID = o.Object_ID where o.Object_ID is null | Returns tagged values that are not assigned to any element. | ||
About GUID's | ||||
30 | Select * from t_object where ea_guid is null | Returns items that do not have a GUID | ||
31 | Select * from t_attribute where ea_guid is null | Returns attributes that do not have a GUID | ||
32 | Select * from t_operation where ea_guid is null | Returns operations that do not have a GUID. | ||
33 | Select * from t_attributetag where ea_guid is null | Returns tags for attributes that do not have a GUID. | ||
34 | Select * from t_operationtag where ea_guid is null | Returns tags for operations that do not have a GUID. | ||
35 | Select * from t_objectproperties where ea_guid is null | Returns tagged value attributes that do not have a GUID. | ||
36 | Select * from t_diagram where ea_guid is null | Returns diagrams that do not have a GUID. | ||
37 | Select * from t_operationparams where ea_guid is null | Returns the parameters of operations that do not have a GUID. | ||
38 | Select * from t_package where ea_guid is null | Returns packages that do not have a GUID. | ||
39 | Select * from t_stereotypes where ea_guid is null | Returns the names of stereotypes that do not have a GUID. | ||
40 | Select * from t_connector where ea_guid is null | Returns connectors that do not have a GUID. | ||
41 | Select * from t_connectortag where ea_guid is null | Returns tagged values of connectors that do not have a GUID. | ||
42 | Select * from t_taggedvalue where PropertyID is null | Returns tagged value types that do not have a GUID. | ||
43 | select space(40) as ea_guid, space(18) as FieldAlias, space(18) as TableName, space(11) as FieldName, 0 as Rank UNION ALL select ea_guid, 'Package GUID' as FieldAlias, 't_package' as TableName, 'ea_guid' as FieldName, 1 as Rank from t_package UNION ALL select ea_guid, 'Object GUID' as FieldAlias, 't_object' as TableName, 'ea_guid' as FieldName, 2 as Rank from t_object where Object_Type <> 'Package' UNION ALL select ea_guid, 'Diagram GUID' as FieldAlias, 't_diagram' as TableName, 'ea_guid' as FieldName, 3 as Rank from t_diagram UNION ALL select ea_guid, 'Stereotype GUID' as FieldAlias, 't_stereotypes' as TableName, 'ea_guid' as FieldName, 4 as Rank from t_stereotypes UNION ALL select ea_guid, 'Attribute GUID' as FieldAlias, 't_attribute' as TableName, 'ea_guid' as FieldName, 5 as Rank from t_attribute UNION ALL select ea_guid, 'Operation GUID' as FieldAlias, 't_operation' as TableName, 'ea_guid' as FieldName, 6 as Rank from t_operation UNION ALL select ea_guid, 'Parameter GUID' as FieldAlias, 't_operationparams' as TableName, 'ea_guid' as FieldName, 7 as Rank from t_operationparams UNION ALL select ea_guid, 'Connector GUID' as FieldAlias, 't_connector' as TableName, 'ea_guid' as FieldName, 8 as Rank from t_connector UNION ALL select ea_guid, 'Object Tag GUID' as FieldAlias, 't_objectproperties' as TableName, 'ea_guid' as FieldName, 9 as Rank from t_objectproperties UNION ALL select ea_guid, 'Connector Tag GUID' as FieldAlias, 't_connectortag' as TableName, 'ea_guid' as FieldName, 10 as Rank from t_connectortag UNION ALL select ea_guid, 'Attribute Tag GUID' as FieldAlias, 't_attributetag' as TableName, 'ea_guid' as FieldName, 11 as Rank from t_attributetag UNION ALL select ea_guid, 'Operation Tag GUID' as FieldAlias, 't_operationtag' as TableName, 'ea_guid' as FieldName, 12 as Rank from t_operationtag UNION ALL select PropertyID as ea_guid, 'Generic Tag GUID' as FieldAlias, 't_taggedvalue' as TableName, 'PropertyID' as FieldName, 13 as Rank from t_taggedvalue UNION ALL select ea_guid, 'Scenario GUID' as FieldAlias, 't_objectscenarios' as TableName, 'ea_guid' as FieldName, 14 as Rank from t_objectscenarios ORDER BY 1, 5 | To discover ;-) | ||
Cross-reference checking | ||||
44 | Select Client, `Type`, Name, Behavior from t_xref where Name in ('Stereotypes', 'CustomProperties') Group by Client, `Type`, Name, Behavior having count(XRefID) >1 | But maybe someone will tell you what it's all about. | ||
45 | select XrefID from t_xref left outer join t_object on t_xref.Client = t_object.ea_guid where t_xref.`Type`='element property' and t_xref.Name='Stereotypes' and t_object.ea_guid is null UNION select XrefID from t_xref left outer join t_attribute on t_xref.Client = t_attribute.ea_guid where t_xref.`Type`='attribute property' and t_xref.Name='Stereotypes' and t_attribute.ea_guid is null UNION select XrefID from t_xref left outer join t_operation on t_xref.Client = t_operation.ea_guid where t_xref.`Type`='operation property' and t_xref.Name='Stereotypes' and t_operation.ea_guid is null UNION select XrefID from t_xref left outer join t_operationparams on t_xref.Client = t_operationparams.ea_guid where t_xref.`Type`='parameter property' and t_xref.Name='Stereotypes' and t_operationparams.ea_guid is null UNION select XrefID from t_xref left outer join t_connector on t_xref.Client = t_connector.ea_guid where t_xref.`Type`='connector property' and t_xref.Name='Stereotypes' and t_connector.ea_guid is null UNION select XrefID from t_xref left outer join t_connector on t_xref.Client = t_connector.ea_guid where t_xref.`Type`='connectorSrcEnd property' and t_xref.Name='Stereotypes' and t_connector.ea_guid is null UNION select XrefID from t_xref left outer join t_connector on t_xref.Client = t_connector.ea_guid where t_xref.`Type`='connectorDestEnd property' and t_xref.Name='Stereotypes' and t_connector.ea_guid is null | Returns a list of orphan stereotypes that are not used by elements, attributes, methods, method parameters or conneteurs. | Orphaned element stereotypes | |
Connectors | ||||
46 | select count(*) as numrows from t_connector where Connector_Type is null | Returns the number of relationships for which the type is missing. | Invalid connector | |
47 | Select t_connector.Connector_ID, t_connector.Connector_Type from t_connector left join t_object on t_connector.End_Object_ID = t_object.Object_ID where t_object.Object_ID is null or ( t_object.Name='EA_IMPORT_STUB' and t_object.Package_ID=1 and t_object.Object_Type='Class') UNION Select t_connector.Connector_ID, t_connector.Connector_Type from t_connector left join t_object on t_connector.Start_Object_ID = t_object.Object_ID where t_object.Object_ID is null or ( t_object.Name='EA_IMPORT_STUB' and t_object.Package_ID=1 and t_object.Object_Type='Class') | Returns a list of relationships for which the source or target element is missing. | Invalid connector | |
48 | select Instance_ID, 'Missing Connector' as Problem from t_diagramlinks l left join t_connector c on l.ConnectorID = c.Connector_ID where c.Connector_ID is null UNION select Instance_ID, 'Missing Diagram' as Problem from t_diagramlinks l left join t_diagram d on l.DiagramID = d.Diagram_ID where d.Diagram_ID is null | Returns a list of relationships that should be displayed in diagrams, but for which there are no relationships in the model. | Invalid link geometry. ITEM: Missing Connector | |
49 | select count(*) as RecCount, DiagramID, ConnectorID,'Duplicate Link Information' as Problem from t_diagramlinks group by DiagramID, ConnectorID having count(*) > 1 | Returns a list of relationships that are displayed in diagrams for which there is more than one relationship in the model. | Invalid link geometry. ITEM: Missing Connector | |
50 | select * from t_connectorconstraint c left join t_connector o on c.ConnectorID = o.Connector_ID where o.Connector_ID is null | Returns a list of restrictions assigned to relationships for which there is no relationship. | ||
51 | select * from t_connectortag c left join t_connector o on c.ElementID = o.Connector_ID where o.Connector_ID is null | Returns a list of tags assigned to relationships for which there is no relationship. | Invalid connector tag | |
52 | SELECT t_connector.Connector_ID, t_connector.Connector_Type, t_diagram.Diagram_ID, t_connector.DiagramID FROM t_connector LEFT JOIN t_diagram ON t_connector.DiagramID = t_diagram.Diagram_ID WHERE (((t_diagram.Diagram_ID) Is Null) AND ((t_connector.DiagramID)<>0)) | Check the consistency between the relationships in the model and those presented in the diagrams. | Invalid link geometry. ITEM: Missing Connector | |
53 | SELECT t_connector.Connector_ID, t_connector.SourceRole, t_connector.DestRole, t_connector.StyleEx FROM t_connector, t_operation WHERE t_connector.Start_Object_ID = t_operation.Object_ID AND t_operation.Stereotype = 'FK' GROUP BY Connector_ID | Checks foreign key relationships for accuracy. | ||
54 | select Connector_ID from t_connector where Connector_Type='Realization' | Checks for accuracy, because it should be an"Achievement | ||
55 | select Stereotype from t_stereotypes where AppliesTo='realization' | Check for stereotypes that apply to realization instead of realization. | ||
56 | select c.* from t_attributeconstraints c left join t_attribute o on c.ID = o.ID where o.ID is null | Returns the list constraints of attributes for which no attributes have been found. | ||
57 | select * from t_attributetag c left join t_attribute o on c.ElementID = o.ID where o.ID is null | Returns a list of attribute tags for which no attribute has been found. | Invalid attribute tag | |
58 | select * from t_attribute where t_attribute.Name is null | Returns the list of attributes without names. | ||
59 | select o.*, c.OperationID as OpID, c.PostCondition from t_operationposts c left join t_operation o on c.OperationID = o.OperationID where o.OperationID is null | Returns the list of final conditions for methods for which no method has been found. | ||
60 | select o.*, c.OperationID as OpID, c.PreCondition from t_operationpres c left join t_operation o on c.OperationID = o.OperationID where o.OperationID is null | Returns the list of starting conditions for methods for which no method has been found. | ||
61 | select * from t_operationtag c left join t_operation o on c.ElementID = o.OperationID where o.OperationID is null | Returns a list of method tags for which no method has been found. | ||
62 | select c.* from t_operationparams c left join t_operation o on c.OperationID = o.OperationID where o.OperationID is null | Returns the list of method parameters for which the method was not found. | ||
63 | select 0 as tabletype, `Type`, Classifier, ea_guid from t_attribute where Classifier is not null and Classifier <> '0' union select 1 as tabletype, `Type`, Classifier, ea_guid from t_operation where Classifier is not null and Classifier <> '0' union select 2 as tabletype, `Type`, Classifier, ea_guid from t_operationparams where Classifier is not null and Classifier <> '0' | Maybe someone will tell you what it's for. | ||
These queries are compatible with MySQL and may need to be adapted for use on other database technologies.