Problem

The Images of a specific diagram in the data structure of the Sparx Systems Enterprise Architect should be read by diagram name.

Approach

Usage of MDB Plus and Data Mining

Solution

This is SQL for the Access / .eap-File – it should be rewritten for other SQL Servers (i.e. the brackets can be removed and Functions like InStr/Mid shall be replaced)

SELECT	dia.Name As Diagram,
		IIF(Trim(objstart.[Name]) = 'Text', 'Text', objstart.stereotype) As stereotype, 
		diaobj.RectLeft As x,
		diaobj.RectTop As y,
		diaobj.RectRight-diaobj.RectLeft As Width,
		Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
		objstart.Object_ID,
		IIF(Trim(objstart.[Name]) = 'Text', 'Text-'& objstart.Object_ID ,objstart.Alias) As [key],
		IIF(objstart.[Name] = 'Text', objstart.Note, objstart.[Name]) As phaseName,
		objstart.[ea_guid] As [guid],
		diaobj.ObjectStyle,
		IIF(
                    InStr(diaobj.ObjectStyle, "ImageID")>0, 
                    Mid(diaobj.ObjectStyle, InStr(diaobj.ObjectStyle, "ImageID")+8 , Len(diaobj.ObjectStyle)- (InStr( diaobj.ObjectStyle, "ImageID")+8) ),
                    ''
        ) As ImageId     
FROM
((
		[t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom, ObjectStyle from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE objstart.Object_Type IN ('Text','Boundary') AND IIF(InStr(diaobj.ObjectStyle, "ImageID")>0, 
                                                                Mid(diaobj.ObjectStyle, InStr(diaobj.ObjectStyle, "ImageID")+8 , 
                                                                Len(diaobj.ObjectStyle)- (InStr( diaobj.ObjectStyle, "ImageID")+8) ),
                                                                '') <> ''
AND dia.Name='0' 
ORDER BY 12 DESC,1,2,3,4,5,6,7,8,9       

Problem

The order of tree elements in the project browser should be queried in SQL.
Die Reihenfolge der Objekte im Projekt-Browser soll über SQL abgefragt werden

Approach – Ansatz

Usage of the TPos column within the tables t_object and t_package.
Verwendung der TPos Spalte in den Tabellen t_object und t_package.

Solution – Lösung

SELECT b.TPos as Package_Position, 
       b.Name as Package_Name, 
       a.TPos as Object_Tree_Position, 
       a.Object_ID, 
       a.Name as Object_Description
FROM t_object a INNER JOIN t_package b ON a.Package_ID=b.Package_ID 
WHERE Stereotype='Process Step'
ORDER BY 1,2,3

Problem

A SQL statement is needed, that returns a list of all link relations with start and end object name. Additionally a tagged value (here it is called ‚Object identifier‘) should be appended

Approach

Usage of MDB Plus

Solution

Note: If you do not need tagged values, you can customize the following Access / .eap-File Statement in the way that you reduce it by leaving out the last 2 LEFT JOINs (and don’t forget to remove the Columns from the Select clause).

SELECT      objstart.[Object_ID] As EAIDSource,
            objstart.[Name] AS EANameSource,
            objpropstart.[Value] AS DoorsSoruceObjectID,
            tconn.[Connector_ID] AS EAConnectorID,
            tconn.[Direction] AS EADirection,
            tconn.[Connector_Type] AS EAConnectorType,
            tconn.[Stereotype] AS EAStereoType,
            objend.[Object_ID] AS EAIDTarget,
            objend.[Name] AS EANameTarget,
            objpropende.[Value] AS DoorsTargetObjectID
FROM                                                                                          
(((                                                              
[t_connector] tconn LEFT JOIN [t_object] objstart ON tconn.[Start_Object_ID]=objstart.[Object_ID])
                    LEFT JOIN [t_object] objend ON tconn.[End_Object_ID]=objend.[Object_ID])                                                                           
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropstart ON tconn.[Start_Object_ID]=objpropstart.[Object_ID] )
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropende ON tconn.[End_Object_ID]=objpropende.[Object_ID]

Problem

A SQL Statement is needed to get the coordinates of Diagram Object like Activities, Classes, Lanes and so on…
In this statement only the stereotypes ‚Project Phase‘, ‚Gate‘, ‚Product Maturity‘, ‚Lane‘ of the diagram ‚Product Creation Process‘ will be selected

Approach

Usage of MDB Plus or another SQL Tool

Solution

This example is for Access / .eap-Files

SELECT  dia.Name As Diagram,
                objstart.stereotype,
                diaobj.RectLeft As x,
                diaobj.RectTop As y,
                diaobj.RectRight-diaobj.RectLeft As Width,
                Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
                objstart.Object_ID,                               
                objstart.Alias,
                objstart.[Name] As ProcessStep
FROM                                                 
((
                [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE dia.Name='Product Creation Process'
AND objstart.stereotype IN ('Project Phase', 'Gate', 'Product Maturity', 'Lane')
ORDER BY 1,2,3,4,5,6,7,8,9                                                                                                           

Problem

A SQL-Statement is needed (here Access / .eap) that gets all Coordinate-, Routing- and Position information of links in a diagram of the Sparx Systems Enterprise Architect.

Approach

Data Mining and the Usage of MDB Plus

Solution

The link routing is diagram dependent. In the following diagram the diagram „PLM Framework“ has been used. Further more the stereotype „BusinessProcess“ is requested (you have to replace these String according to your needs.

SELECT bp.[Object_ID] as [start_object_id],
       bp.[Name] as [start_object_name],
       tconn.[Connector_ID] as [conn_id],
       tconn.[Start_Edge] as [conn_start_edge],
       tconn.[End_Edge] as [conn_end_edge],
       tconn.[PtStartX] as [conn_pt_start_x],
       tconn.[PtStartY] as [conn_pt_start_y],
       tconn.[PtEndX] as [conn_pt_end_x],
       tconn.[PtEndY] as [conn_pt_end_y],
       tconn.[RouteStyle] as [conn_route_style],
       linkpath.[Path] as [conn_path],
       bpdest.[Object_ID] as [end_object_id],
       bpdest.[Name] as [end_object_name],
       bp.[Alias] as [start_key],
       bpdest.[Alias] as [end_key],
       startobjcoord.x as [start_obj_x],
       startobjcoord.y as [start_obj_y],
       endobjcoord.x as [end_obj_x],
       endobjcoord.y as [end_obj_y],
       linkpath.Geometry as [geometry] 
FROM
((((( 
   SELECT Object_ID, [Alias], Name 
   FROM t_object
   WHERE Stereotype='BusinessProcess'
)bp
INNER JOIN 
(  SELECT [Connector_ID],[Start_Object_ID], [End_Object_ID], [Start_Edge], [End_Edge], [PtStartX], [PtStartY], [PtEndX], [PtEndY], [RouteStyle] 
   FROM [t_connector] 
)tconn
ON tconn.[Start_Object_ID]=bp.[Object_ID])
INNER JOIN 
( 
   SELECT Object_ID, [Alias],Name 
   FROM  [t_object] 
)bpdest ON tconn.[End_Object_ID]=bpdest.Object_ID)
INNER JOIN
(
  SELECT ConnectorID, Path, Geometry
  FROM t_diagramlinks WHERE DiagramID IN (SELECT Diagram_ID FROM t_diagram WHERE Name='PLM Framework')
) linkpath ON linkpath.[ConnectorID]=tconn.[Connector_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) startobjcoord ON startobjcoord.[Object_ID]=bp.[Object_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) endobjcoord ON endobjcoord.[Object_ID]=bpdest.[Object_ID]