Sparx Systems Enterprise Architect: SQL to get the start / end coordinates of the link connector routing with customized lines

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]

Über Björn Karpenstein

Diplom Informatiker, Programmierer, Musikbegeisterter
Dieser Beitrag wurde unter Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL veröffentlicht. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden .