EA Modelling Tools SQL
Introduction
This page gives an overview of all the model searches and model views defined in EA Modelling Tools SQL. They can be downloaded for import as
- non-editable model searches and model views, as part of an MDG (available in the "EA Modelling Tools SQL" search category in the Find in Project window and in the "EA Modelling Tools SQL Views" root node in the Model Views window, respectively)
- editable model searches (available in the "My Searches" search category in the Find in Project window)
- editable model views (available in the root node "EA Modelling Tools SQL" in the Model Views window)
Model views
SDFI Core rules
| Description | Search | Search term |
|---|---|---|
| attributes and enumeration literals with stereotype enum | see search details | |
| attributes of enumerations | see search details | |
| attributes where the type name does not match the type id | see search details | |
| attributes with a type name but without type id | see search details | |
| attributes without a type | see search details | |
| attributes without explicitly specified multiplicity | see search details | |
| classifiers with association ends with a stereotype not defined in a UML profile | see search details | |
| classifiers with association ends with invalid names | see search details | |
| classifiers with association ends with notes | see search details | |
| classifiers with associations or association ends with duplicate tags | see search details | |
| classifiers with associations with unspecified direction | see search details | |
| classifiers with duplicate names | see search details | |
| classifiers with navigable association ends without explicitly specified multiplicity | see search details | |
| classifiers, attributes and enumeration literals with invalid names | see search details | |
| diagrams with diagram details | see search details | |
| diagrams with diagram notes | see search details | |
| enumeration literals with duplicate names | see search details | |
| enumeration literals with names with two consecutive spaces | see search details | |
| objects not on any diagram | see search details | |
| packages and classifiers that are language-specific | see search details | |
| packages, classifiers and attributes with a scope that is not Public | see search details | |
| packages, classifiers, attributes and enumeration literals with a custom stereotype | see search details | |
| packages, classifiers, attributes and enumeration literals with a stereotype not defined in a UML profile | see search details | |
| packages, classifiers, attributes and enumeration literals with duplicate tags | see search details | |
| packages, classifiers, attributes and enumeration literals with notes | see search details |
SDFI Diagram rules for models in Danish
| Description | Search | Search term |
|---|---|---|
| classes without context diagrams (Danish) | see search details | Kontekstdiagram |
| context diagrams without central classifier (Danish) | see search details | Kontekstdiagram |
| diagrams with invalid names (Danish) | see search details | |
| model without dependency diagram (Danish) | see search details | Pakkeafhængigheder |
SDFI Diagram rules for models in English
| Description | Search | Search term |
|---|---|---|
| classes without context diagrams (English) | see search details | Context diagram |
| context diagrams without central classifier (English) | see search details | Context diagram |
| diagrams with invalid names (English) | see search details | |
| model without dependency diagram (English) | see search details | Package dependencies |
Model searches
- all_attributes_classifier (see search details)
- associations (see search details)
- associations_unspecified_direction (see search details)
- attributes_of_enumerations (see search details)
- attributes_with_conflicting_type (see search details)
- attributes_with_name_like (see search details)
- attributes_with_type_like (see search details)
- attributes_with_type_without_classifier (see search details)
- attributes_without_type (see search details)
- classes_without_context_diagram (see search details)
- classifier_and_ancestors (see search details)
- classifiers_with_association_ends_with_invalid_names_internal (see search details)
- classifiers_with_association_ends_with_notes (see search details)
- classifiers_with_association_ends_with_stereotype_not_from_profile (see search details)
- classifiers_with_associations_or_association_ends_with_duplicate_tags (see search details)
- classifiers_with_associations_with_unspecified_direction (see search details)
- classifiers_with_duplicate_names (see search details)
- classifiers_with_navigable_association_ends_without_explicit_multiplicity (see search details)
- constraints (see search details)
- context_diagrams_without_central_classifier (see search details)
- diagrams_with_diagramdetails (see search details)
- diagrams_with_diagramnotes (see search details)
- diagrams_with_invalid_names_da (see search details)
- diagrams_with_invalid_names_en (see search details)
- duplicate_attributes_classifier (see search details)
- enumeration_literals_attributes_with_stereotype_enum (see search details)
- enumeration_literals_two_consecutive_spaces (see search details)
- enumeration_literals_with_duplicate_names (see search details)
- model_elements_custom_stereotype (see search details)
- model_elements_duplicate_tags (see search details)
- model_elements_invalid_names_internal (see search details)
- model_elements_nonpublic_scope (see search details)
- model_elements_notes (see search details)
- model_elements_notes_not_null_not_empty (see search details)
- model_elements_stereotype_not_from_profile (see search details)
- model_elements_stereotypes (see search details)
- model_elements_tagged_value (see search details)
- model_elements_tagged_value_export (see search details)
- model_without_dependency_diagram_da (see search details)
- multivalued_attributes (see search details)
- navigable_association_ends_not_by_reference (see search details)
- navigable_association_ends_without_role_name (see search details)
- objects_language_not_none (see search details)
- optional_properties (see search details)
- orphans (see search details)
- properties_without_explicit_multiplicity (see search details)
- scripts_and_scriptsgroups_with_scriptgroupname_like (see search details)
- sequence_numbers_classifier (see search details)
- sequence_numbers_package (see search details)
all_attributes_classifier
Find the owned and inherited attributes of the classifier selected in the Project Browser. Association ends are not taken into account. Note: the query contains "level * 2" instead of the usual "level + 1". This is because there is a bug in EA that causes numeric addition not to work, see also https://sparxsystems.com/forums/smf/index.php/topic,48040.0.html.
SELECT
*
FROM
(
WITH self_and_ancestor(object_id,
name,
level) AS (
SELECT
o.object_id,
o.name,
1
FROM
t_object o
WHERE
o.object_id = #CurrentElementID#
UNION ALL
SELECT
o_parent.object_id,
o_parent.name,
s.level * 2
FROM
(self_and_ancestor s
INNER JOIN t_connector c ON
(s.object_id = c.start_object_id
AND c.connector_type = 'Generalization'))
INNER JOIN t_object o_parent ON
c.end_object_id = o_parent.object_id
),
attributes_self_and_ancestor(CLASSGUID,
CLASSTYPE,
property_name,
defining_classifier_object_id,
defining_classifier_name,
level) AS (
SELECT
a.ea_guid,
'Attribute',
a.name,
o.object_id,
o.name,
o.level
FROM
t_attribute a
INNER JOIN
self_and_ancestor o
ON
a.object_id = o.object_id
)
SELECT
CLASSGUID,
CLASSTYPE,
property_name,
defining_classifier_name
FROM
attributes_self_and_ancestor
ORDER BY
LEVEL DESC
);
associations
Find all associations in the selected package and its subpackages.
SELECT
c.ea_guid AS CLASSGUID,
c.connector_type AS CLASSTYPE,
't_connector' AS CLASSTABLE,
c.name,
c.direction,
o_src.name as source_name,
c.sourcerole,
c.sourcecard,
c.sourcestereotype,
o_target.name as target_name,
c.destrole,
c.destcard,
c.deststereotype
FROM
(t_connector c
INNER JOIN t_object o_src ON
c.start_object_id = o_src.object_id)
INNER JOIN t_object o_target ON
c.end_object_id = o_target.object_id
WHERE
((o_src.package_id IN (#Branch#)
AND o_target.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_src.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_target.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
ORDER BY
c.name;
associations_unspecified_direction
Find the associations that have an unspecified direction.
SELECT
c.ea_guid AS CLASSGUID,
c.connector_type AS CLASSTYPE,
't_connector' AS CLASSTABLE,
c.name,
c.direction,
o_src.name as source_name,
c.sourcerole,
o_target.name as target_name,
c.destrole
FROM
(t_connector c
INNER JOIN t_object o_src ON
c.start_object_id = o_src.object_id)
INNER JOIN t_object o_target ON
c.end_object_id = o_target.object_id
WHERE
((o_src.package_id IN (#Branch#)
AND o_target.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_src.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_target.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.Direction = 'Unspecified'
ORDER BY
c.name;
attributes_of_enumerations
Find the attributes that belong to enumerations. Typically, enumerations only have enumeration literals, not attributes. In EA, attributes and enumerations are stored in table t_attribute.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS enumeration_name,
a.name AS attribute_name,
a.styleex
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
WHERE
o.package_id IN (#Branch#)
AND o.object_type = 'Enumeration'
AND instr(a.styleex, 'IsLiteral=1') = 0;
attributes_with_conflicting_type
Find the attributes that have a conflicting type, where the name of the attribute type is not equal to the name of the classifier that is specified as the type. This can for example happen when first a classifier was chosen as type in the dropdown, and then <none> was chosen as type in the drop-down. To resolve this, change the type to a data type defined by the language of the element (in the dropdown) and then change again to <none>.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS attribute_name,
a.type AS type_name,
a.classifier AS type_id
FROM
(((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id))
INNER JOIN t_object o2 ON
o2.object_id = a.classifier
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface')
AND (a.classifier IS NOT NULL
AND a.type != o2.name)
ORDER BY
p.name,
o.name;
attributes_with_name_like
Finds all the attributes with a name like the specified search term. Specify a search term using the syntax for the LIKE operator as defined by the underlying database system. E.g. for SQLite: % matches any sequence of zero or more characters in the string, _ matches any single character in the string.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS attribute_name,
a.type AS type_name,
a.classifier AS type_id
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id
WHERE
o.package_id IN (#Branch#)
AND a.name LIKE '<Search Term>'
ORDER BY
a.name;
attributes_with_type_like
Specify a search term using the syntax for the LIKE operator as defined by the underlying database system. E.g. for SQLite: % matches any sequence of zero or more characters in the string, _ matches any single character in the string.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS attribute_name,
a.type AS type_name,
a.classifier AS type_id
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id
WHERE
o.package_id IN (#Branch#)
AND a.type LIKE '<Search Term>'
ORDER BY
a.type;
attributes_with_type_without_classifier
Find the attributes that have a type specified that is not linked to an element (classifier) in the model.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS attribute_name,
a.type AS type_name,
a.classifier AS type_id
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface')
AND (a.classifier IS NULL
OR a.classifier = 0)
AND a.type IS NOT NULL
ORDER BY
p.name,
o.name;
attributes_without_type
Find the attributes that have no type specified (<none> was chosen as type in the drop-down).
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS attribute_name,
a.type AS type_name,
a.classifier AS type_id
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface')
AND a.classifier = 0
AND a.type IS NULL
ORDER BY
p.name,
o.name;
classes_without_context_diagram
Find the classes that don't have a context diagram. A context diagram must be a class diagram and it must have a name consisting of (1) the term specified as search term (e.g. "Context diagram" or "Kontekstdiagram") (2) a space and (3) the name of the class. This query is intended to be used in a model view, where the search term is fixed and valid only in a given language.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
o.name
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND object_type = 'Class'
AND NOT EXISTS
(select * from t_diagramobjects do inner join t_diagram d on do.diagram_id = d.diagram_id
where d.name = #Concat '<Search Term> ', o.name#);
classifier_and_ancestors
Find (1) the classifier selected in the Project Browser and (2) the ancestors of that classifier.
SELECT
CLASSGUID,
CLASSTYPE,
name
FROM
(
WITH self_and_ancestor(CLASSGUID,
CLASSTYPE,
object_id,
name) AS (
SELECT
o.ea_guid,
o.object_type,
o.object_id,
o.name
FROM
t_object o
WHERE
o.ea_guid = #CurrentElementGUID#
UNION ALL
SELECT
o_parent.ea_guid,
o_parent.object_type,
o_parent.object_id,
o_parent.name
FROM
(self_and_ancestor s
INNER JOIN t_connector c ON
(s.object_id = c.start_object_id
AND c.connector_type = 'Generalization'))
INNER JOIN t_object o_parent ON
c.end_object_id = o_parent.object_id
)
SELECT
*
FROM
self_and_ancestor
);
classifiers_with_association_ends_with_invalid_names_internal
Finds the classifiers for which an opposite association end has a name having characters that are invalid according to the internal rules of the agency. Model views cannot show connectors or connector ends, this query can be used in a model view search folder. See also query model_elements_invalid_names_internal.
SELECT
*
FROM
(
SELECT
o_start.ea_guid AS CLASSGUID,
o_start.object_type AS CLASSTYPE,
o_start.name AS classifier_name,
c.destrole AS property_name,
o_end.name AS type,
c.name AS association_name
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
o_end.ea_guid,
o_end.object_type,
o_end.name,
c.sourcerole,
o_start.name,
c.name
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong')))
WHERE
(property_name GLOB '*[^a-zA-ZæøåéÆØÅÉ0-9]*'
OR property_name NOT GLOB '[a-zA-ZæøåéÆØÅÉ]*')
ORDER BY
classifier_name,
property_name;
classifiers_with_association_ends_with_notes
Finds the classifiers for which an opposite association end has non-null notes. Model views cannot show connectors or connector ends, this query can be used in a model view search folder.
SELECT
*
FROM
(
SELECT
o_start.ea_guid AS CLASSGUID,
o_start.object_type AS CLASSTYPE,
o_start.name AS classifier_name,
c.destrole AS property_name,
c.destrolenote AS notes
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
o_end.ea_guid,
o_end.object_type,
o_end.name,
c.sourcerole,
c.sourcerolenote
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
)
WHERE
notes IS NOT NULL
ORDER BY
classifier_name,
property_name;
classifiers_with_association_ends_with_stereotype_not_from_profile
Finds the classifiers for which an opposite association end has a stereotype not from a UML profile. Model views cannot show connectors or connector ends, this query can be used in a model view search folder. See also query stereotypes_not_from_profile
SELECT
*
FROM
(
SELECT
o_start.ea_guid AS CLASSGUID,
o_start.object_type AS CLASSTYPE,
p_start.name AS package_name,
o_start.name AS classifier_name,
c.destrole AS property_name,
c.deststereotype AS primary_unqualified_stereotype,
x.description AS stereotypes
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
LEFT JOIN t_xref x
ON
x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorDestEnd property'
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
o_end.ea_guid,
o_end.object_type,
p_end.name,
o_end.name,
c.sourcerole,
c.sourcestereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
LEFT JOIN t_xref x
ON
x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorSourceEnd property'
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
)
WHERE
(stereotypes LIKE '%GUID%'
OR (stereotypes NOT LIKE '%GUID%'
AND stereotypes NOT LIKE '%FQNAME%'))
ORDER BY
package_name,
classifier_name,
property_name;
classifiers_with_associations_or_association_ends_with_duplicate_tags
Find the classifiers with association ends and relationships that have more than one tagged value with the same name. Model views cannot show connectors or connector ends, this query can be used in a model view search folder. See also query model_elements_duplicate_tags.
SELECT
o_start.ea_guid AS CLASSGUID,
o_start.object_type AS CLASSTYPE,
o_start.name AS classifier_name,
c.name AS element_name,
'association' AS element_type,
ct.property AS tag_name
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_connectortag ct ON
ct.elementid = c.connector_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
GROUP BY
c.ea_guid,
c.connector_type,
c.name,
ct.property
HAVING
count(ct.property) > 1
UNION ALL
SELECT
o_end.ea_guid,
o_end.object_type,
o_end.name,
c.sourcerole,
'source association end',
tv.tagvalue
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
GROUP BY
c.ea_guid,
c.connector_type,
c.sourcerole,
tv.tagvalue
HAVING
count(tv.tagvalue) > 1
UNION ALL
SELECT
o_start.ea_guid,
o_start.object_type,
o_start.name,
c.destrole,
'target association end',
tv.tagvalue
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
GROUP BY
c.ea_guid,
c.connector_type,
c.destrole,
tv.tagvalue
HAVING
count(tv.tagvalue) > 1;
classifiers_with_associations_with_unspecified_direction
Find the classifiers with associations that have an unspecified direction. Model views cannot show connectors or connector ends, this query can be used in a model view search folder. See also query associations_unspecified_direction.
SELECT
o_start.ea_guid AS CLASSGUID,
o_start.object_type AS CLASSTYPE,
c.name AS association_name,
c.direction,
o_start.name as source_name,
c.sourcerole,
o_end.name as target_name,
c.destrole
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.Direction = 'Unspecified'
ORDER BY
c.name;
classifiers_with_duplicate_names
Find the classifiers that have the same name as another classifier in the given package and its subpackages. This interpretation is stricter than the UML 2.5.1 specification, where a package is a namespace, and its subpackages are other namespaces. This query also finds the classifiers that have the same name but are of a different kind. This interpretation is stricter than the UML 2.5.1 specification, that permits named elements to have the same name if they are of a different kind. See operation isDistinguishableFrom() in clause 7.8.9.7, operation membersAreDistinguishable() in clause 7.8.10.8 and constraint members_distinguisable in clause 7.8.10.7.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
o.name,
o.object_type,
o.stereotype
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND object_type IN ('Class', 'Enumeration', 'Interface', 'DataType')
AND EXISTS
(
SELECT
*
FROM
t_object o2
WHERE
o2.package_id IN (#Branch#)
AND o2.name = o.name
AND o2.ea_guid <> o.ea_guid);
classifiers_with_navigable_association_ends_without_explicit_multiplicity
Find classifiers that have properties in the form of navigable association ends that don't have a multiplicity specified explicitly. If it is not specified, it is assumed to be 1, according to the UML specification. However, having a explicitly specified multiplicity is preferable. Model views cannot show connectors or connector ends, this query can be used in a model view search folder.
SELECT
o_start.ea_guid AS CLASSGUID,
o_start.object_type AS CLASSTYPE,
o_start.name AS classifier_name,
c.destrole AS property_name,
o_end.name AS type,
c.name AS association_name
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional'))
AND c.destcard IS NULL
UNION ALL
SELECT
o_end.ea_guid,
o_end.object_type,
o_end.name,
c.sourcerole,
o_start.name,
c.name
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional'))
AND c.sourcecard IS NULL;
constraints
For more information about constraints in t_objectconstraint, see https://sparxsystems.com/eahelp/constraints.html. For more information about constraints in t_object, see https://sparxsystems.com/eahelp/element_constraint.html. "constraint" is a reserved word, therefore the square brackets are needed for columns with name "Constraint". Not (yet?) implemented are the following: (1) take into account the tables t_attributeconstraints, t_connectorconstraint and t_roleconstraint; (2) check connectors of type NoteLink and check t_object.PDATA4 to find the model elements that are constrained by the constraints in t_object.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
o.name AS constrained_element_name,
oc.[Constraint] AS constraint_name,
oc.notes AS constraint_text
FROM
t_objectconstraint oc
INNER JOIN t_object o ON
o.object_id = oc.object_id
WHERE
o.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
'(see diagram)',
o.name,
o.note
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND o.object_type = 'Constraint';
context_diagrams_without_central_classifier
Find the context diagrams that do not contain the classifier they claim to contain according to the name of the context diagram. This query is intended to be used in a model view, where the search term is fixed and valid only in a given language.
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
WHERE
d.package_id IN (#Branch#)
AND d.name LIKE '<Search Term>%'
AND NOT EXISTS (
SELECT
*
FROM
t_diagramobjects do
INNER JOIN t_object o ON
do.object_id = o.object_id
WHERE
d.diagram_id = do.diagram_id
AND o.package_id IN (#Branch#)
AND d.name = #Concat '<Search Term> ', o.name#);
diagrams_with_diagramdetails
Find the diagrams that show the diagram details (see also https://sparxsystems.com/eahelp/appearance_options_diag.html).
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
WHERE
d.package_id IN (#Branch#)
AND d.showdetails = 1;
diagrams_with_diagramnotes
Find the diagrams that contains diagram notes, also called a diagram properties note (see https://sparxsystems.com/eahelp/addpropertiesnote.html).
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
WHERE
d.package_id IN (#Branch#)
AND EXISTS
(
SELECT
*
FROM
t_diagramobjects do
INNER JOIN t_object o ON
o.object_id = do.object_id
WHERE
do.diagram_id = d.diagram_id
AND o.ntype = 18);
diagrams_with_invalid_names_da
The names of the diagrams have to follow a specific pattern to be able to create a good feature catalogue. The name must start with one of the following (Danish): 'Pakkeafhængigheder', 'Subpakker', 'Oversigtsdiagram' or 'Kontekstdiagram'. The part of the name after 'Kontekstdiagram' must be equal to the name of an existing object in the model that the diagram resides in, if the diagram's name starts with 'Kontekstdiagram'.
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
INNER JOIN t_package p ON
d.package_id = p.package_id
WHERE
d.package_id IN (#Branch#)
AND d.name <> #Concat 'Pakkeafhængigheder ', p.name#
AND d.name <> #Concat 'Subpakker ', p.name#
AND #Substring d.name, 1, 16# <> 'Oversigtsdiagram'
AND #Substring d.name, 1, 15# <> 'Kontekstdiagram'
UNION ALL
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
WHERE
d.package_id IN (#Branch#)
AND d.name LIKE 'Kontekstdiagram%'
AND NOT EXISTS (
SELECT
1
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND #Substring d.name, 17# = o.name)
ORDER BY
2;
diagrams_with_invalid_names_en
The names of the diagrams have to follow a specific pattern to be able to create a good feature catalogue. The name must start with one of the following (English): 'Package dependencies', 'Subpackages', 'Overview diagram' or 'Context diagram'. The part of the name after 'Context diagram' must be equal to the name of an existing object in the model that the diagram resides in, if the diagram's name starts with 'Context diagram'.
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
INNER JOIN t_package p ON
d.package_id = p.package_id
WHERE
d.package_id IN (#Branch#)
AND d.name <> #Concat 'Package dependencies ', p.name#
AND d.name <> #Concat 'Subpackages ', p.name#
AND #Substring d.name, 1, 16# <> 'Overview diagram'
AND #Substring d.name, 1, 15# <> 'Context diagram'
UNION ALL
SELECT
d.ea_guid AS CLASSGUID,
d.diagram_type AS CLASSTYPE,
't_diagram' AS CLASSTABLE,
d.name
FROM
t_diagram d
WHERE
d.package_id IN (#Branch#)
AND d.name LIKE 'Context diagram%'
AND NOT EXISTS (
SELECT
1
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND #Substring d.name, 17# = o.name)
ORDER BY
2;
duplicate_attributes_classifier
Find the owned and inherited attributes of the classifier selected in the Project Browser that have the same name as another attribute of that classifier. Association ends are not taken into account. Note: the query contains "level * 2" instead of the usual "level + 1". This is because there is a bug in EA that causes numeric addition not to work, see also https://sparxsystems.com/forums/smf/index.php/topic,48040.0.html.
SELECT
*
FROM
(
WITH self_and_ancestor(object_id,
name,
level) AS (
SELECT
o.object_id,
o.name,
1
FROM
t_object o
WHERE
o.object_id = #CurrentElementID#
UNION ALL
SELECT
o_parent.object_id,
o_parent.name,
s.level * 2
FROM
(self_and_ancestor s
INNER JOIN t_connector c ON
(s.object_id = c.start_object_id
AND c.connector_type = 'Generalization'))
INNER JOIN t_object o_parent ON
c.end_object_id = o_parent.object_id
),
attributes_self_and_ancestor(CLASSGUID,
CLASSTYPE,
property_name,
defining_classifier_object_id,
defining_classifier_name,
level) AS (
SELECT
a.ea_guid,
'Attribute',
a.name,
o.object_id,
o.name,
o.level
FROM
t_attribute a
INNER JOIN
self_and_ancestor o
ON
a.object_id = o.object_id
)
SELECT
CLASSGUID,
CLASSTYPE,
a1.property_name,
defining_classifier_name
FROM
(
SELECT
*
FROM
attributes_self_and_ancestor) a1
INNER JOIN
(
SELECT
property_name
FROM
attributes_self_and_ancestor
GROUP BY
property_name
HAVING
COUNT(*) > 1) a2 ON
a1.property_name = a2.property_name
)
enumeration_literals_attributes_with_stereotype_enum
Find the enumeration attributes and literals defined that have stereotype "enum". See also https://sparxsystems.com/forums/smf/index.php/topic,39483.msg243694.html#msg243694.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS enumeration_name,
a.name AS attribute_or_literal_name,
a.styleex,
a.stereotype AS primary_unqualified_stereotype,
x.description AS stereotypes
FROM
((t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
a.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type = 'Enumeration'
AND x.description LIKE '%Name=enum;%';
enumeration_literals_two_consecutive_spaces
Find the enumeration literals whose name has two consecutive spaces. The occurrence of two consecutive spaces is very likely an error.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS enumeration_literal_name
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Enumeration')
AND a.name LIKE '% %'
ORDER BY
package_name,
classifier_name,
enumeration_literal_name;
enumeration_literals_with_duplicate_names
Find the enumeration literals that have the same name as another enumeration literal of the same enumeration. See also the UML 2.5.1 specification, clause 10.2.3.3: An EnumerationLiteral has a name that shall be used to identify it within its Enumeration. The EnumerationLiteral name is scoped within and shall be unique within its Enumeration.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS enumeration_literal_name
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Enumeration')
AND EXISTS (
SELECT
*
FROM
t_attribute a2
WHERE
a2.object_id = a.object_id
AND a2.name = a.name
AND a2.ea_guid <> a.ea_guid )
ORDER BY
package_name,
classifier_name,
enumeration_literal_name;
model_elements_custom_stereotype
Show the model elements with a custom stereotype, that is a stereotype that is (or at some point was) defined in the project's reference data. See also https://sparxsystems.com/eahelp/creatingcustomstereotypes.html, table t_stereotypes and query stereotypes. This query is closely related to model_elements_stereotype_not_from_profile. Usually, this query and model_elements_stereotype_not_from_profile will return the same results. However, model elements with a stereotype stored as @STEREO;Name=DKEgenskab;GUID={16570901-9E07-4319-81A7-25B52F03CF74};FQName=Grunddata::DKEgenskab;@ENDSTEREO have been seen in certain models, therefore the split into two queries.
SELECT
*
FROM
(
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.name AS package_name,
NULL AS classifier_name,
NULL AS property_name,
o.stereotype AS primary_unqualified_stereotype,
x.description AS stereotypes
FROM
(t_package p
INNER JOIN t_object o ON
p.ea_guid = o.ea_guid)
LEFT JOIN t_xref x ON
o.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
p.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
p.name,
o.name,
NULL,
o.stereotype,
x.description
FROM
(t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
o.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.stereotype,
x.description
FROM
((t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
a.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.deststereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
LEFT JOIN t_xref x
ON
x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorDestEnd property'
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourcestereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
LEFT JOIN t_xref x
ON
x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorSourceEnd property'
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
)
WHERE
stereotypes LIKE '%GUID%'
ORDER BY
package_name,
classifier_name,
property_name;
model_elements_duplicate_tags
Find the packages, classifiers, properties, enumeration literals and relationships that have more than one tagged value with the same name.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
NULL AS CLASSTABLE,
o.name AS element_name,
op.property AS tag_name
FROM
(t_objectproperties op
INNER JOIN t_object o ON
op.object_id = o.object_id)
INNER JOIN t_package p ON
p.ea_guid = o.ea_guid
WHERE
p.package_id IN (#Branch#)
AND o.object_type IN ('Package')
GROUP BY
o.ea_guid,
o.object_type,
o.name,
op.property
HAVING
count(op.property) > 1
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
o.name,
op.property
FROM
t_objectproperties op
INNER JOIN t_object o ON
op.object_id = o.object_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface', 'Enumeration')
GROUP BY
o.ea_guid,
o.object_type,
o.name,
op.property
HAVING
count(op.property) > 1
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
a.name,
at.property
FROM
(t_attributetag AT
INNER JOIN t_attribute a ON
at.elementid = a.id)
INNER JOIN t_object o ON
a.object_id = o.object_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface', 'Enumeration')
GROUP BY
a.ea_guid,
a.name,
at.property
HAVING
count(at.property) > 1
UNION ALL
SELECT
c.ea_guid,
c.connector_type,
't_connector',
c.name,
ct.property
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_connectortag ct ON
ct.elementid = c.connector_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
GROUP BY
c.ea_guid,
c.connector_type,
c.name,
ct.property
HAVING
count(ct.property) > 1
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
c.sourcerole,
tv.tagvalue
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
GROUP BY
c.ea_guid,
c.connector_type,
c.sourcerole,
tv.tagvalue
HAVING
count(tv.tagvalue) > 1
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
c.destrole,
tv.tagvalue
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
GROUP BY
c.ea_guid,
c.connector_type,
c.destrole,
tv.tagvalue
HAVING
count(tv.tagvalue) > 1;
model_elements_invalid_names_internal
Finds the classifiers, properties and enumeration literals with names having characters that are invalid according to the internal rules of the agency. In addition, in both XML and databases, the first character of a name must be alphabetic, and thus not start with a digit. To ease conversion to XML and database schemas, the first character of a name in the model has to be alphabetic as well. The latter rule does not apply to enumeration literals.
SELECT
*
FROM
(
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
NULL AS CLASSTABLE,
p.name AS package_name,
o.name AS classifier_name,
NULL AS property_or_enumeration_literal_name,
o.name AS element_name
FROM
t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.name
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.destrole
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourcerole
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong')))
WHERE
(element_name GLOB '*[^a-zA-ZæøåéÆØÅÉ0-9_]*'
OR element_name NOT GLOB '[a-zA-ZæøåéÆØÅÉ]*')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.name
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Enumeration')
AND a.name GLOB '*[^a-zA-ZæøåéÆØÅÉ0-9Ωαβ, .():+''>=<&§/_%-]*'
ORDER BY
package_name,
classifier_name,
property_or_enumeration_literal_name;
model_elements_nonpublic_scope
Finds the model elements that do not have their scope set to "Public".
SELECT * FROM (
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.Name AS package_name,
NULL AS classifier_name,
NULL AS property_name,
o.scope AS scope
FROM
t_package p
INNER JOIN t_object o ON
p.ea_guid = o.ea_guid
WHERE
p.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
p.name,
o.name AS classifier_name,
NULL AS property_name,
o.scope
FROM
t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.scope
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.destaccess
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourceaccess
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional')))
WHERE scope <> 'Public'
ORDER BY
package_name,
classifier_name,
property_name;
model_elements_notes
Shows the notes on model elements.
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.Name AS package_name,
NULL AS classifier_name,
NULL AS property_name,
o.note AS notes
FROM
t_package p
INNER JOIN t_object o ON
p.ea_guid = o.ea_guid
WHERE
p.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
p.name,
o.name AS classifier_name,
NULL AS property_name,
o.note AS notes
FROM
t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.notes
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.destrolenote AS notes
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourcerolenote
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
ORDER BY
package_name,
classifier_name,
property_name;
model_elements_notes_not_null_not_empty
Finds the model elements with a note that is not null and not empty. Note: When creating a new attribute (https://sparxsystems.com/eahelp/attributesmainpage.html), its note is null until something is written into it. However, when copying an attribute from another classifier (https://sparxsystems.com/eahelp/copyingattributes.html), the note of the copy is NOT null, it is an empty string. But then again, when importing a model from XMI in another project file, all notes are null, even though the attributes originally were copied. Anyway, this explains the WHERE clause.
SELECT
*
FROM
(
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.Name AS package_name,
NULL AS classifier_name,
NULL AS property_name,
o.note AS notes
FROM
t_package p
INNER JOIN t_object o ON
p.ea_guid = o.ea_guid
WHERE
p.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
p.name,
o.name AS classifier_name,
NULL AS property_name,
o.note AS notes
FROM
t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.notes
FROM
(t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.destrolenote AS notes
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourcerolenote
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
)
WHERE
(notes IS NOT NULL AND LENGTH(notes) > 0)
ORDER BY
package_name,
classifier_name,
property_name;
model_elements_stereotype_not_from_profile
Show the model elements with a stereotype that is not defined in a UML profile. See also query stereotypes.
SELECT
*
FROM
(
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.name AS package_name,
NULL AS classifier_name,
NULL AS property_name,
o.stereotype AS primary_unqualified_stereotype,
x.description AS stereotypes
FROM
(t_package p
INNER JOIN t_object o ON
p.ea_guid = o.ea_guid)
LEFT JOIN t_xref x ON
o.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
p.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
p.name,
o.name,
NULL,
o.stereotype,
x.description
FROM
(t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
o.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.stereotype,
x.description
FROM
((t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
a.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.deststereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
LEFT JOIN t_xref x
ON
x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorDestEnd property'
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourcestereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
LEFT JOIN t_xref x
ON
x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorSourceEnd property'
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
)
WHERE
stereotypes NOT LIKE '%FQNAME%'
ORDER BY
package_name,
classifier_name,
property_name;
model_elements_stereotypes
Shows all the stereotypes of the model elements. The stereotypes column contains all the stereotypes. For each of the stereotypes applied to a model element, a string like one of the following is present: @STEREO;Name=<stereo>;FQName=<profile_name>::<stereo>;@ENDSTEREO; (if the stereotype is defined in a UML profile, possibly as part of an MDG) or @STEREO;Name=<stereo>;GUID=<guid>;@ENDSTEREO; (if the stereotype is a custom stereotype, see https://sparxsystems.com/eahelp/creatingcustomstereotypes.html and see table t_stereotypes).
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.name AS package_name,
NULL AS classifier_name,
NULL AS property_name,
o.stereotype AS primary_unqualified_stereotype,
x.description AS stereotypes
FROM
(t_package p
INNER JOIN t_object o ON
p.ea_guid = o.ea_guid)
LEFT JOIN t_xref x ON
o.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
p.package_id IN (#Branch#)
UNION ALL
SELECT
o.ea_guid,
o.object_type,
NULL,
p.name,
o.name,
NULL,
o.stereotype,
x.description
FROM
(t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
o.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
p.name,
o.name,
a.name,
a.stereotype,
x.description
FROM
((t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id)
INNER JOIN t_package p ON
o.package_id = p.package_id)
LEFT JOIN t_xref x ON
a.ea_guid = x.client
AND x.name = 'Stereotypes'
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
c.deststereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
LEFT JOIN t_xref x
ON x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorDestEnd property'
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional'))
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
c.sourcestereotype,
x.description
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
LEFT JOIN t_xref x
ON x.client = c.ea_guid
AND x.name = 'Stereotypes'
AND x.type = 'connectorSourceEnd property'
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional'))
ORDER BY
package_name,
classifier_name,
property_name;
model_elements_tagged_value
Finds all classifiers and properties with the given tagged value. Works currently only for tagged values that are not of the memo type.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
NULL AS CLASSTABLE,
o.Name AS Name,
o.name AS classifier,
NULL AS property,
op.value AS tagged_value
FROM
t_object o
INNER JOIN t_objectproperties op ON
op.object_id = o.object_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
AND op.property = ('<Search Term>')
UNION ALL
SELECT
a.ea_guid,
'Attribute',
NULL,
a.Name,
o.name,
a.name,
at.value
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
INNER JOIN t_attributetag AT ON
(a.id = at.elementid
AND at.property = '<Search Term>')
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
c.destrole,
o_start.name,
c.destrole,
tv.notes
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET'
AND tv.tagvalue = '<Search Term>')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
c.sourcerole,
o_end.name,
c.sourcerole,
tv.notes
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE'
AND tv.tagvalue = '<Search Term>')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional')
ORDER BY
classifier,
property;
model_elements_tagged_value_export
Finds all classifiers, properties, enumeration literals and connectors with the given tagged value. This query can be used as the starting point for a CSV file to import with script import-data-model-custom-tags (EA Modelling Tools JavaScript): use the "Copy Selected to Clipboard" functionality (see https://sparxsystems.com/eahelp/model_search_context_menu.html), paste in LibreOffice Calc, modify as needed and save as a CSV file. The query only works for tagged values that are not of the memo type.
SELECT
-- for display in EA
o.ea_guid AS CLASSGUID,
-- for import of tags via script import-data-model-custom-tags
o.ea_guid AS GUID,
-- for import of tags via script import-data-model-custom-tags
o.name AS "UML-NAVN",
-- for import of tags via script import-data-model-custom-tags
p.name AS NAMESPACE,
-- for display in EA
o.object_type AS CLASSTYPE,
-- for import of tags via script import-data-model-custom-tags
upper(o.object_type) AS "TYPE",
-- for display in EA
NULL AS CLASSTABLE,
-- for import of tags via script import-data-model-custom-tags
(
SELECT
op.value
FROM
t_objectproperties op
WHERE
op.object_id = o.object_id
AND op.property = ('<Search Term>')) AS "<Search Term>"
FROM
t_object o
INNER JOIN t_package p ON
o.package_id = p.package_id
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
a.ea_guid,
a.ea_guid,
a.Name,
o.Name,
'Attribute',
CASE
WHEN instr(a.styleex, 'IsLiteral=1') = 0 THEN 'ATTRIBUTE'
ELSE 'ENUMERATION_LITERAL'
END,
NULL,
(
SELECT
at.value
FROM
t_attributetag AT
WHERE
a.id = at.elementid
AND at.property = ('<Search Term>'))
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface')
UNION ALL
SELECT
c.ea_guid,
-- substr is 1-based
'{dst' || substr(c.ea_guid, 4),
c.destrole,
o_start.name,
'AssociationEnd',
'ASSOCIATION_END',
't_connector',
(
SELECT
tv.notes
FROM
t_taggedvalue tv
WHERE
tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET'
AND tv.tagvalue = ('<Search Term>'))
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
-- substr is 1-based
'{src' || substr(c.ea_guid, 4),
c.sourcerole,
o_end.name,
'AssociationEnd',
'ASSOCIATION_END',
't_connector',
(
SELECT
tv.notes
FROM
t_taggedvalue tv
WHERE
tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE'
AND tv.tagvalue = ('<Search Term>'))
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
UNION ALL
SELECT
c.ea_guid,
c.ea_guid,
c.name,
NULL,
c.connector_type,
'ASSOCIATION',
't_connector',
(
SELECT
ct.value
FROM
t_connectortag ct
WHERE
ct.elementid = c.connector_id
AND ct.property = ('<Search Term>'))
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'));
model_without_dependency_diagram_da
SELECT
p.ea_guid AS CLASSGUID,
'Package' AS CLASSTYPE,
p.name
FROM
t_package p
WHERE
p.package_id = #Package#
AND NOT EXISTS (
SELECT
*
FROM
t_diagram d
WHERE
d.package_id = #Package#
AND d.Diagram_Type = 'Package'
AND d.name = #Concat 'Pakkeafhængigheder ',p.name#
);multivalued_attributes
Find all multivalued attributes, that is all attributes with a multiplicity with an upper bound greater than one.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS attribute_name,
a.type AS type_name,
a.classifier AS type_id,
a.lowerbound,
a.upperbound
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
WHERE
o.package_id IN (#Branch#)
AND a.upperbound NOT IN ('0', '1')
ORDER BY
p.name,
o.name,
a.name;
navigable_association_ends_not_by_reference
Find the navigable association ends that don't have value inlineOrByReference set to byReference. Aggregations and compositions are not considered in this query.
SELECT
c.ea_guid AS CLASSGUID,
'AssociationEnd' AS CLASSTYPE,
't_connector' AS CLASSTABLE,
o_start.name AS start_classifier_name,
c.name AS association_name,
c.destrole AS end_classifier_role,
o_end.name AS end_classifier_name
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type = 'Association')
OR (o_start.package_id IN (#Branch#)
AND c.connector_type = 'Association'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional')
AND (NOT EXISTS (
SELECT
*
FROM
t_taggedvalue tv
WHERE
tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET'
AND tv.tagvalue = 'inlineOrByReference')
OR (
SELECT
notes
FROM
t_taggedvalue tv
WHERE
tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET'
AND tv.tagvalue = 'inlineOrByReference') <> 'byReference')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
o_end.name,
c.name,
c.sourcerole AS end_classifier_role,
o_start.name
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type = 'Association')
OR (o_start.package_id IN (#Branch#)
AND c.connector_type = 'Association'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional')
AND (NOT EXISTS (
SELECT
*
FROM
t_taggedvalue tv
WHERE
tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE'
AND tv.tagvalue = 'inlineOrByReference')
OR (
SELECT
notes
FROM
t_taggedvalue tv
WHERE
tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE'
AND tv.tagvalue = 'inlineOrByReference') <> 'byReference');
navigable_association_ends_without_role_name
Find the navigable association ends that don't have a role name.
SELECT
c.ea_guid AS CLASSGUID,
'AssociationEnd' AS CLASSTYPE,
't_connector' AS CLASSTABLE,
o_start.name AS start_classifier_name,
c.name AS association_name,
c.destrole AS end_classifier_role,
o_end.name AS end_classifier_name
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional'))
AND c.destrole IS NULL
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
o_end.name,
c.name,
c.sourcerole AS end_classifier_role,
o_start.name
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional'))
AND c.sourcerole IS NULL;
objects_language_not_none
Find the elements that are specified as being language-specific, that is, that have their language not set to "<none>" (see also https://sparxsystems.com/eahelp/generalproperties.html). Script set-language-none can be used to update these elements. Note: the default language can be configured in EA. It is a model-specific option, see https://sparxsystems.com/eahelp/code_generation_options.html. There is no user-specific option to set the default language.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
o.name,
o.object_type,
o.gentype
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Enumeration', 'Interface', 'Package')
AND (o.gentype IS NULL
OR o.gentype <> '<none>');
optional_properties
Find optional properties, that is properties that have a lower bound of 0. Properties that are actually conditional because of a constraint are also returned.
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
NULL AS CLASSTABLE,
o.name AS classifier_name,
a.name AS property,
#Concat a.lowerbound, '..', a.upperbound # AS multiplicity
FROM
t_attribute a
INNER JOIN t_object o ON
a.object_id = o.object_id
WHERE
o.package_id IN (#Branch#)
AND a.lowerbound = '0'
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
o_start.name,
c.destrole,
c.destcard
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction = 'Source -> Destination'
AND #Substring c.destcard,
1,
1# = '0'
UNION ALL
SELECT
c.ea_guid AS CLASSGUID,
'AssociationEnd' AS CLASSTYPE,
't_connector',
o_end.name,
c.sourcerole,
c.sourcecard
FROM
(t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction = 'Destination -> Source'
AND #Substring c.sourcecard,
1,
1# = '0'
ORDER BY
classifier_name,
property;
orphans
Find the objects that are not present on any diagram (in the selected package).
SELECT
o.ea_guid AS CLASSGUID,
o.object_type AS CLASSTYPE,
o.object_type,
o.name,
o.note AS notes,
o.createddate,
o.modifieddate
FROM
t_object o
WHERE
o.package_id IN (#Branch#)
AND NOT EXISTS
(
SELECT
*
FROM
t_diagramobjects
INNER JOIN t_diagram ON
t_diagramobjects.diagram_id = t_diagram.diagram_id
WHERE
t_diagram.package_id IN (#Branch#)
AND t_diagramobjects.object_id = o.object_id);
properties_without_explicit_multiplicity
Find the properties of classifiers (not including non-navigable properties) that don't have a multiplicity specified explicitly. If it is not specified, it is assumed to be one, according to the UML specification. However, having a explicitly specified multiplicity is preferable.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
NULL AS CLASSTABLE,
p.name AS package_name,
o.name AS classifier_name,
a.name AS property_name,
a.type AS type,
NULL AS association_name
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface')
AND (a.lowerbound IS NULL OR a.upperbound IS NULL)
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_start.name,
o_start.name,
c.destrole,
o_end.name,
c.name
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_start ON
o_start.package_id = p_start.package_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional'))
AND c.destcard IS NULL
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
p_end.name,
o_end.name,
c.sourcerole,
o_start.name,
c.name
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
INNER JOIN t_package p_end ON
o_end.package_id = p_end.package_id
WHERE
(((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional'))
AND c.sourcecard IS NULL;
scripts_and_scriptsgroups_with_scriptgroupname_like
Find the script groups that have a name like the given search term. Find also the scripts in those script groups. Use search term `eamt-%` to find the scripts and script groups from EA Modelling Tools JavaScript
SELECT
s.ScriptCategory,
s.ScriptName,
s.ScriptAuthor,
s.Notes,
s.Script
FROM
t_script s
WHERE
s.Script LIKE '<Search Term>'
AND s.Notes LIKE '<Group%'
UNION ALL
SELECT
s.ScriptCategory,
s.ScriptName,
s.ScriptAuthor,
s.Notes,
s.Script
FROM
t_script s
INNER JOIN t_script s1 ON
s1.ScriptName = s.ScriptAuthor
WHERE
s1.script LIKE '<Search Term>'
AND s1.Notes LIKE '<Group%'
ORDER BY
ScriptCategory,
ScriptName;
sequence_numbers_classifier
Find the sequence numbers (tagged value sequenceNumber) of all properties of the classifier selected in the project browser, ordered by (1) sequence number, (2) by ordering position (that is, if the sequence number is not available, and this information is only available for attributes) and (3) by property name.
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
NULL AS CLASSTABLE,
o.name AS classifier_name,
a.name AS property,
at.value AS sequenceNumber,
a.pos AS ordering_position
FROM
(t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
LEFT OUTER JOIN t_attributetag AT ON
(a.id = at.elementid
AND at.property = 'sequenceNumber')
WHERE
o.object_id = #CurrentElementID#
UNION ALL
SELECT
c.ea_guid AS CLASSGUID,
'AssociationEnd' AS CLASSTYPE,
't_connector' AS CLASSTABLE,
o_start.name AS classifier_name,
c.destrole AS property,
tv.notes AS sequenceNumber,
NULL
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
LEFT OUTER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET'
AND tv.tagvalue = 'sequenceNumber')
WHERE
o_start.object_id = #CurrentElementID#
AND c.connector_type IN ('Association', 'Aggregation')
AND c.direction IN ('Source -> Destination', 'Bi-Directional')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
o_end.name,
c.sourcerole,
tv.notes,
NULL
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
LEFT OUTER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE'
AND tv.tagvalue = 'sequenceNumber')
WHERE
o_end.object_id = #CurrentElementID#
AND c.connector_type IN ('Association', 'Aggregation')
AND c.direction IN ('Destination -> Source', 'Bi-Directional')
ORDER BY
classifier_name,
sequenceNumber,
ordering_position,
property;
sequence_numbers_package
Find the sequence numbers (tagged value sequenceNumber) of all properties of all classifiers in the package selected in the project browser, ordered (1) by classifier name, (2) by sequence number, (3) by ordering position (that is, if the sequence number is not available, and this information is only available for attributes) and (4) by property name
SELECT
a.ea_guid AS CLASSGUID,
'Attribute' AS CLASSTYPE,
NULL AS CLASSTABLE,
o.name AS classifier_name,
a.name AS property,
at.value AS sequenceNumber,
a.pos AS ordering_position
FROM
((t_attribute a
INNER JOIN t_object o ON
o.object_id = a.object_id)
INNER JOIN t_package p ON
p.package_id = o.package_id)
LEFT OUTER JOIN t_attributetag AT ON
(a.id = at.elementid
AND at.property = 'sequenceNumber')
WHERE
o.package_id IN (#Branch#)
AND o.object_type IN ('Class', 'DataType', 'Interface')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
o_start.name,
c.destrole,
tv.notes,
NULL
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
LEFT OUTER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_TARGET'
AND tv.tagvalue = 'sequenceNumber')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Source -> Destination', 'Bi-Directional')
UNION ALL
SELECT
c.ea_guid,
'AssociationEnd',
't_connector',
o_end.name,
c.sourcerole,
tv.notes,
NULL
FROM
((t_connector c
INNER JOIN t_object o_start ON
c.start_object_id = o_start.object_id)
INNER JOIN t_object o_end ON
c.end_object_id = o_end.object_id)
LEFT OUTER JOIN t_taggedvalue tv ON
(tv.elementid = c.ea_guid
AND tv.baseclass = 'ASSOCIATION_SOURCE'
AND tv.tagvalue = 'sequenceNumber')
WHERE
((o_start.package_id IN (#Branch#)
AND o_end.package_id IN (#Branch#)
AND c.connector_type IN ('Association', 'Aggregation'))
OR (o_start.package_id IN (#Branch#)
AND (c.connector_type = 'Association'
OR (c.connector_type = 'Aggregation'
AND c.subtype = 'Weak')))
OR (o_end.package_id IN (#Branch#)
AND c.connector_type = 'Aggregation'
AND c.subtype = 'Strong'))
AND c.direction IN ('Destination -> Source', 'Bi-Directional')
ORDER BY
classifier_name,
sequenceNumber,
ordering_position,
property;