EaSqlMacros - Helmut-Ortmann/EnterpriseArchitect_hoTools GitHub Wiki

EA SQL Macros

General

In SQL Templates you see macros at work.

Macros are an efficient way to use EA selected items in SQL like:

  • Item (Element, Package, Attribute, Operation, #CurrentItemID#, #CurrentItemGUID#)
  • Package (#Branch#, #Branch={....guid}#, #PackageID#,#Package#)
  • Diagram Elements (#DiagramElements_IDS#, #DiagramSelectedElements_IDS#)
  • Attribute (#CurrentItemID#, #CurrentItemGUID#)
  • Operation (#CurrentItemID#, #CurrentItemGUID#)
  • Browser (#TreeSelectedGUIDS#)
  • Design time constants (#Branch={....guid}#, ID or GUID of an EA item (Package, Diagram, Element, Operation, Attribute)

Also:

  • Comments
  • DB specific
  • Wild Cards

With hoTools Templates you get runnable SQL statements for almost every macro and major EA SQL task, just a few clicks!

Insert Macros

List of Macros

Macro Select Replaced by Example
// no empty // my comment
<Search Term> no Search Term of input field (like EA) where name like '%'
#Branch# Package List of package IDs (selected + nested(recursive)) where Package_ID in (#Branch#)
#Branch={...guid...}# Package at design time or guid List of package IDs (package according to guid + nested(recursive)) where pkg.package_ID in (#Branch={0E702249-BFFA-43cd-86C9-41D3324A7617}#)
#ConnectorID# Connector ID where Connector_ID = #ConnectorID#
#ConveyedItemIDS# Connector List of conveyed items where Object_ID in ( #ConveyedItemIDS# )
#CurrentElementGUID# Element GUID where ea_guid = #CurrentElementGUID#
#CurrentElementID# Element ID where Object_ID = #CurrentElementID#
#CurrentItemGUID# Item GUID (Note: Alias to #CurrentElementGUID#) where ea_guid = #CurrentItemGUID#
#CurrentItemID# Item ID (Note: Alias to #CurrentElementID#) where Object_ID = #CurrentItemID#
#DiagramElements_IDS# Diagram List of all Diagram Element where Object_ID in (#DiagramElements_IDS#)
#DiagramSelectedElements_IDS# Diagram Elements List of selected Diagram Elements where Object_ID in (#DiagramSelectedElements_IDS#)
#Package# Package, sub item Package ID from Package, Diagram, Element, Attribut, Operation where Package_ID = #Package#
#PackageID# Package, sub item Package ID from Package, Diagram, Element, Attribut, Operation where Package_ID = #PackageID#
#TreeSelectedGUIDS# Browser Items List of GUIDs (Diagram, Package, Element, Operation, Attribut where ea_guid in (#TREE_SELECTED_GUIDS#)
#WC# NA SQL Wildcard like '%','*', db specific, not needed any more where name like '#WC#'
* NA SQL Wildcard, transformed into the correct DB format '*' or '%' where name like '*'
% NA SQL Wildcard, transformed into the correct DB format '*' or '%' where name like '%'
_ NA SQL Wildcard, transformed into the correct DB format '?' or '_' where name like '__'
? NA SQL Wildcard, transformed into the correct DB format '?' or '_' where name like '??'
'^' or '!' NA transformed into the correct DB format '^' or '!'
#DB=ACCESS2007# NA ACCESS2007 specific between #DB=ACCESS2007#..#DB=ACCESS2007# #DB=ACCESS2007#..#DB=ACCESS2007#
#DB=ASA# NA ASA specific between #DB=ASA# ..#DB=ASA# #DB=ASA# ..#DB=ASA#
#DB=FIREBIRD# NA FIREBIRD specific between #DB=FIREBIRD# ..#DB=FIREBIRD# #DB=FIREBIRD# ..#DB=FIREBIRD#
#DB=JET# NA JET specific between #DB=JET# ..#DB=JET# #DB=JET# ..#DB=JET#
#DB=MYSQL# NA MYSQL specific between #DB=MYSQL# ..#DB=MYSQL# #DB=MYSQL# ..#DB=MYSQL#
#DB=ORACLE# NA ORACLE specific between #DB=ORACLE# ..#DB=ORACLE# #DB=ORACLE# ..#DB=ORACLE#
#DB=POSTGRES# NA POSTGRES specific between #DB=POSTGRES# ..#DB=POSTGRES# #DB=POSTGRES# ..#DB=POSTGRES#
#DB=SQLSVR# NA SQL Server specific between #DB=SQLSVR# ..#DB=SQLSVR# #DB=SQLSVR# ..#DB=SQLSVR#

Helper

There are some helper functions integrated into macro functionality to get some EA values at design time.

Function Select Replaced by Example
Insert ID Package, Element, Diagram, Operation, Attribute ID of the EA item at design time (constant), It copies the value also to clipboard 145
Insert GUID Package, Element, Diagram, Operation, Attribute ID of the EA item at design time (constant), It copies the value also to clipboard {0E702249-BFFA-43cd-86C9-41D3324A7617}
#Branch={...guid...}# Package Branch Macro with the constant GUID at design time. In fact a constant Package #Branch={0E702249-BFFA-43cd-86C9-41D3324A7617}#

Configuration