Get all Attributes connected to an Element

Sparx EA has the ability to “visually” link a connector between two elements to a start and end at specific attributes under those elements.   A few years ago I dug in to the repository and figured out where and how this information is stored so I can include it in my reports.

If you don’t know what I’m referring to:

1. create a class diagram,
2. create 2 classes,
3. create attributes under those two classes,
4. create an association connector between the two classes,
5. right click on that connector
6. select link to element feature
7. select attribute (note the selected parent element is the element that was closest to where you “right clicked” on the connector)
8. select one of the attributes

Connector going from Class 1 to Class 2, linked to Attribute 3

Analysis

Unfortunately Sparx EA’s built in reporting features do not let you use this information, but I found a way to create an SQL script fragment that would let me use this information in my reports.  E.g. a list of “connected” attributes.

First was learning how the information about what attribute to point to is stored in the repository.   After a bit of searching I found that it’s stored as a name = value pair in the .StyleEx field in the t_connector table.

From Class 1 to Class 2, linked to Attribute 3: The StyleEX field contains the GUID for Attribute 3 LFEP={F180DAC8-F318-430c-B339-F7DF39A49BE1}L; as the value for the name LFEP	From Class 1 to Class 2, linking to Attribute 1 to Attribute 3: The StyleEX field contains the GUID: for Attribute 1 LFSP={1D8B1D73-8A14-4476-9DF9-E1858D65F88D}R; for Attribute 3 LFEP={F180DAC8-F318-430c-B339-F7DF39A49BE1}L;
From Class 2 to Class 1, linked to Attribute 3: The StyleEX field contains the GUID for Attribute 3 LFSP={F180DAC8-F318-430c-B339-F7DF39A49BE1}L; as the value for the name LFSP	From Class 2 to Class 1, linking to Attribute 3 to Attribute 1: The StyleEX field contains the GUID: for Attribute 1 LFEP={1D8B1D73-8A14-4476-9DF9-E1858D65F88D}R; for Attribute 3 LFSP={F180DAC8-F318-430c-B339-F7DF39A49BE1}L;

At this time I can ignore the appended L and R that follow the GUIDs, and I can pretty much ignore the LFEP and LFSP too buy filtering out all attributes that have the selected element as their parent.

SQL Query

For this example the query will return all the attributes connected to Class 1, in this case the only attribute returned will be Attribute 3.

• Select (qAttributes)
  • All the connectors starting or ending with the selected element (Class 1, which has the Start_Object_ID = 172 or End_Object_ID = 172) .
  • From each of these connectors get the GUIDs of the referenced Attribute(s) from the StyleEX field.
• Select all the Attributes with GUIDs matching those returned by qAttributes.  Except the attributes that are children selected element (Object_ID = 172)

Using this query:

• Replace 172 with #ObjectID# to use this query in a template fragment.
• Replace 172 with #CurrentElementID# to use this query in a custom search.

For MS Access (.EAP files) and MySQL

SELECT Name FROM t_attribute
INNER JOIN
(
  SELECT 
  MID(StyleEx,INSTR(StyleEx,'LFSP')+5,38) AS ea_guid
  FROM t_connector WHERE Start_Object_ID = 172
  UNION ALL
  SELECT 
  MID(StyleEx,INSTR(StyleEx,'LFEP')+5,38) AS ea_guid
  FROM t_connector WHERE Start_Object_ID = 172
  UNION ALL
  SELECT 
  MID(StyleEx,INSTR(StyleEx,'LFSP')+5,38) AS ea_guid
  FROM t_connector WHERE End_Object_ID = 172
  UNION ALL
  SELECT 
  MID(StyleEx,INSTR(StyleEx,'LFEP')+5,38) AS ea_guid
  FROM t_connector WHERE End_Object_ID = 172
) qAttributes
ON qAttributes.ea_guid = t_attribute.ea_guid
WHERE t_attribute.Object_ID <> 172

Other Data Base Repositories

The MID and/or INSTR function(s) will likely need to be replaced for this query to work with other Data Bases.  For Example:

• Oracle:  Replace the MID function with the SUBSTR function.  E.g. SUBSTR(StyleEx,INSTR(StyleEx,’LFSP’)+5,38)
• MS SQL: Replace the MID function with the SUBSTRING function, and the INSTR function with the CHARINDEX function.  E.g. SUBSTRING(StyleEx,CHARINDEX(‘LFSP’,StyleEx)+5,38)