[Solved] How to query the parent of an object of a classifier?

[Richard Freggi]

1. I have use cases documenting stakeholders and goals.
2. Each use case has several sequence diagrams documenting user stories.  The sequence diagrams show participant objects needed to perform the user story (As a [actor role] I want to use object 1 to do something to object 2 and object 3 in order to achieve the [use case])
4. The parent classifiers of these participant objects are documented in a separate package as classes (business entities).

I have a SQL query that shows these classes name, definitions, attributes etc., but I want to also show which user stories use these classes

Unfortunately I cannot find a JOIN that would go from class GUID to object participant GUID and from there to the names of the interactions (sequence diagrams) where these participants appear.

Any ideas?

[Uffe]

Hi Richard,


Not sure why you're working with GUIDs, the Object_ID is generally the better option.
That said, and not knowing anything about the specifics of your model structures:

• A lifeline would be an instance of a business entity, so lifeline.Classifier would join entity.Object_ID.
• The interaction would be the parent of the lifeline, so lifeline.ParentID would join interaction.Object_ID.

The classifier is also expressed as t_object.Classifier_guid, so that would work with what you've already got.
However, the ParentID has no GUID equivalent, you have to use the Object_ID for that.

If you haven't kept your interactions separate with one sequence diagram in each interaction, you'll probably need to go through the t_diagram/t_diagramobjects route to find what's used where.

HTH,


/Uffe

[Richard Freggi]

Thank you, your answer helped!
I now have the query but I'm stuck: the  query should show one row for each  business entity and the (multiple) interactions where their participant objects occur should be  listed in a single field using the LIST() function of Firebird.
In below example entity Cl1 is used in 2 interactions.  Since I use LIST(), I should have only 1 row as result, showing both interaction names in a single field separated by a semicolon; instead I get 2 separate rows.  Anyone has idea why the LIST() commend fails here?

p.s. EA version 1310
p.s. the " collation is for clean copy/paste to Excel

The query:

SELECT
t_object.ea_guid AS CLASSGUID,
t_object.Object_Type AS CLASSTYPE,

...

'"' || REPLACE (REPLACE (REPLACE (REPLACE (COALESCE(t_object.Name, ''), ASCII_CHAR(13), ' '), ASCII_CHAR(10), '. '), ASCII_CHAR(9), '   '), ASCII_CHAR(34), '''') || '"' AS "Name",
'"' || (SELECT LIST(t1.Name, '; ') FROM t_object t1 WHERE t1.Object_Type = 'Interaction' AND t1.Object_ID = participant.ParentID) || '"' AS "Interaction",

...

FROM t_object

...

LEFT OUTER JOIN t_object participant ON t_object.Object_ID = participant.Classifier
WHERE t_object.Package_ID IN (#Branch#) ...
ORDER BY t_object.Object_Type, t_object.Name

The result is:

..."Cl1","Ensure material availability based on complete, correct, timely factory site MBOM",...
..."Cl1","Require fully accurate MRP result",...

It should be:
..."Cl1","Ensure material availability based on complete, correct, timely factory site MBOM; Require fully accurate MRP result",...


Thanks for any help!!

[qwerty]

Probably the only one to hop on would be Geert. I'd ask such questions on SO where you find lots of SQL gurus.

q.

[pvickers]

Probably the only one to hop on would be Geert. I'd ask such questions on SO where you find lots of SQL gurus.

q.

Hi Qwerty,
I'm relatively new to this forum and finding my way around...Can you expand on what/where "SO" is?

By the way, I bought your "Inside EA" and "Scripting EA" books.  They've been incredibly helpful.

Regards,
Perry

[qwerty]

Thanks Perry. If you happen to have a finding, just let me know.

SO = StackOverflow. Everyone should know it ;-)

q.

[pvickers]

Thanks Perry. If you happen to have a finding, just let me know.

SO = StackOverflow. Everyone should know it ;-)

q.

Thanks Qwerty - I should have thought of it, especially since I already have a userid at StackOverflow.
 
I'm currently writing an EA utility program in Python so I'll definitely keep StackOverflow in mind.

Perry

[qwerty]

I'm also mainly using Python for scripting (EA). There are not many Python-related EA question. I remember one where I also posted a bit of the framework I've done: https://stackoverflow.com/questions/58758722/python-script-unable-to-save-connector-enterprise-architect

q.

[Richard Freggi]

I also use qwerty's "Inside EA" book - in fact I used it to write this query.  It's a must-read for querying EA!

Probably the only one to hop on would be Geert. I'd ask such questions on SO where you find lots of SQL gurus.

q.

Hi Qwerty,
I'm relatively new to this forum and finding my way around...Can you expand on what/where "SO" is?

By the way, I bought your "Inside EA" and "Scripting EA" books.  They've been incredibly helpful.

Regards,
Perry

[Richard Freggi]

I figured it out by sleeping on it.
JOINing was the mistake, because I need to use LIST() to collate all results into a single field, a triple subquery is needed: find the interaction who is the parent of classifiers of the record being considered.

The corrected DML is:

SELECT
t_object.ea_guid AS CLASSGUID,
t_object.Object_Type AS CLASSTYPE,
...
'"' || REPLACE (REPLACE (REPLACE (REPLACE (COALESCE(t_object.Name, ''), ASCII_CHAR(13), ' '), ASCII_CHAR(10), '. '), ASCII_CHAR(9), '   '), ASCII_CHAR(34), '''') || '"' AS "Name",

(SELECT LIST (interac.Name, ';  ') FROM t_object interac WHERE interac.Object_Type = 'Interaction' AND interac.Object_ID IN (SELECT particip.ParentID FROM t_Object particip WHERE particip.Classifier = t_object.Object_ID)) || '"' AS "Interaction",
...
FROM t_object
ORDER BY t_object.Object_Type, t_object.Name

The result is:
..."Cl1","Ensure material availability based on complete, correct, timely factory site MBOM;  Require fully accurate MRP result",  ...


Thanks everyone!

[pvickers]

I'm also mainly using Python for scripting (EA). There are not many Python-related EA question. I remember one where I also posted a bit of the framework I've done: https://stackoverflow.com/questions/58758722/python-script-unable-to-save-connector-enterprise-architect

q.

Good stuff - This is helpful.

I've worked with EA since 2015, and I have programming experience from long before that, however this is my first attempt at connecting Python code to a running instance of EA.
I'm currently developing code to make the connection and catch exceptions...so your framework/background on this is just what I need.

Thanks,
Perry