Sparx Systems Forum

Enterprise Architect => General Board => Topic started by: Richard Freggi on January 17, 2020, 09:03:35 pm

Title: [Solved] How to query the parent of an object of a classifier?
Post by: Richard Freggi on January 17, 2020, 09:03:35 pm
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?
Title: Re: How to query the parent of an object of a classifier?
Post by: Uffe on January 17, 2020, 11:25:15 pm
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:

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
Title: Re: How to query the parent of an object of a classifier?
Post by: Richard Freggi on January 27, 2020, 12:05:33 am
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!!
Title: Re: How to query the parent of an object of a classifier?
Post by: qwerty on January 27, 2020, 01:59:35 am
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.
Title: Re: How to query the parent of an object of a classifier?
Post by: pvickers on January 27, 2020, 03:43:28 am
Quote from: qwerty on January 27, 2020, 01:59:35 am
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
Title: Re: How to query the parent of an object of a classifier?
Post by: qwerty on January 27, 2020, 05:41:07 am
Thanks Perry. If you happen to have a finding, just let me know.

SO = StackOverflow. Everyone should know it ;-)

q.
Title: Re: How to query the parent of an object of a classifier?
Post by: pvickers on January 27, 2020, 06:11:43 am
Quote from: qwerty on January 27, 2020, 05:41:07 am
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
Title: Re: How to query the parent of an object of a classifier?
Post by: qwerty on January 27, 2020, 06:52:48 am
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 (https://stackoverflow.com/questions/58758722/python-script-unable-to-save-connector-enterprise-architect)

q.
Title: Re: How to query the parent of an object of a classifier?
Post by: Richard Freggi on January 27, 2020, 01:36:05 pm
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!

Quote from: pvickers on January 27, 2020, 03:43:28 am
Quote from: qwerty on January 27, 2020, 01:59:35 am
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
Title: Re: [Solved] How to query the parent of an object of a classifier?
Post by: Richard Freggi on January 27, 2020, 02:21:08 pm
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!
Title: Re: How to query the parent of an object of a classifier?
Post by: pvickers on January 28, 2020, 03:47:45 am
Quote from: qwerty on January 27, 2020, 06:52:48 am
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 (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