Advanced queries for the Sparx Enterprise Architect database

This article shows my experiences made in a customer project with the Sparx Enterprise Architect database. It explains how to retrieve all the required information from the Sparx database for UML class diagrams.

Introduction

In the past few months I had to use class diagram related database tables of Sparx Enterprise Architect 11 intensively. This was part of a larger development and architecture project. Retrieving all the required UML-related information from the Sparx database by SQL wasn’t always straightforward. Therefore, I’d like to share my experience so others with similar questions may profit from this.

Note that there are various ways to retrieve and join information from a database. The following explanations follow the use case we were facing in the project. Goal was to efficiently retrieve all UML class diagram related information from the Sparx SQL database for a given Sparx model for further use in Java.

(Compatibility notes: At least since version 9 I’ve been working with the Sparx database scheme and it seems that it didn’t change significantly. So all the query statements in this article should be also valid for older versions.)

Let’s get started

The following ER diagram shows all the Sparx database tables referenced in this article. Columns have been reduced for better visibility and only the ones relevant for this article are shown.

So how do we start?
Let’s assume we have the following initial model in Sparx located in a package „demoPackage“:

This model will serve us as the basis for the next few SQL queries and result sets. Usually we have a GUID (from the Sparx UI) of a package representing the root of a model, e.g. {3D354A08-BC1F-4ae0-B5AE-86375CAC18F4} for our „demoPackage“. As some of the information of a package is in the table t_package and some in t_object we have to join these tables:
select * from t_object as o join t_package as p on o.ea_guid = p.ea_guid where o.ea_guid = ‚{3D354A08-BC1F-4ae0-B5AE-86375CAC18F4}‘;
This gives all the package information we need in a first place (following only the relevant columns are shown):

To retrieve the direct nested packages, the following query uses above Package_ID:
select * from scpmodels.t_object as o join scpmodels.t_package as p on o.ea_guid = p.ea_guid where o.Package_ID = 4295;

Classifiers and attributes

Now let’s get the direct nested classes, interfaces and enumerations for above „demoPackage“:
select * from t_object where Package_ID = 4295 and Object_Type in (‚Class‘, ‚Interface‘, ‚Enumeration‘);
The result for our example (only with relevant columns):

As we now have basic information about the different classifiers we are interested in the classifier’s attributes. All classifiers are identified by an Object_ID. So to get the attributes for the classifier „DemoClass“ we use its Object_ID:
select * from t_attribute where Object_ID = 316623 order by Pos;
(Note that this query will also return literals for enumerations where the column Type will be null.)

In our example we get two attributes for above query:

To get the constraints for an attribute you can use the table t_attributeconstraints, e.g. by joining the table on the attribute’s ID:
select * from t_attribute as a join t_attributeconstraints as c on a.ID = c.ID where a.Object_ID = 166574 order by Pos;
But do we now really have all required information for our classifiers and attributes?
Unfortunately, we don’t. And now it’s starting to get tricky…

Sparx stores all kind of additional information in a general table called t_xref. You search for relevant entries by providing the GUID of the given element. So in our case to get more information about above attribute „demoAttribute1“ we use the attribute’s GUID for the column Client:
select * from t_xref where Client = ‚{55DACEE9-1A54-4cf2-BEC9-12026989A32C}‘ and Type=’attribute property‘;
This gives us some additional information about the attribute in form of a text field in the column Description, e.g. whether this attribute is an ID or not:

So whenever you cannot find some information in the common tables have a look into t_xref. I will come back to the t_xref table later.

Realisations and Generalizations

The following query returns us all realisations and generalizations for a given classifier, in our example for the classifier „DemoClass“:
select * from t_connector where Start_Object_ID = 316623 and Connector_Type in (‚Generalization‘, ‚Realisation‘);

Operations

Now let’s retrieve the operations of our class „DemoClass“. For this we use the Object_ID of „DemoClass“ in the following query:
select * from t_operation where Object_ID = 316623 order by Pos;

To get the operation parameters we use the operation’s OperationID in the following query on the table t_operationparams (we might also join directly on OperationID with the previous query):
select * from t_operationparams where OperationID = 156078 order by Pos;
In our example the operation „demoOperation1“ has the following 3 operation parameters:

To distinguish between the different parameters we use the column Kind. It has the possible values (‚in‘, ‚out‘, ‚return‘).

Note that the return parameter is either directly specified in the table t_operation or as an operation parameter in t_operationparams. In the first case the return type is specified in the column Classifier where the return type is referenced by its Object_ID.

Now you might wonder where the multiplicity of a given operation parameter is specified. This information is held in the table t_xref. There is one „xref-element“ per operation parameter describing further information such as the multiplicity. Again we have to query the xref table by providing the GUID:
select * from t_xref where Client = ‚{967969D5-73D2-4f79-B246-6F2E5DEFAD30}‘ and Type=’parameter property‘;
The description field specifying the multiplicity for the specified operation parameter from our example looks the following:

Associations

Associations are maybe the most trickiest part. You model an association from a „source“ to a „target“ object. In the Sparx database the „source“ object corresponds to the Start_Object_ID and the „target“ to End_Object_ID. The assignments of modelled association ends are fixed once an association is created and depend on how the association has been drawn. However, as you can change UML notations after initial drawing, you can end up having two identical UML models where start and end objects are reversed. The following diagram illustrates the two possible cases. The model looks the same in both cases but the assignments of start and end object in the database are inverted:

Suppose we want to find all associations in the database where a classifier with the given ID is the source. For above reason in order to catch all associations we have to use two queries.
If your given classifier was set as the start object by Sparx the following query will return the right associations (in our example we use the Object_ID of „DemoClass“):
select * from t_connector where Start_Object_ID = 316623 and Connector_Type in (‚Association‘, ‚Aggregation‘) and Direction in (‚Unspecified‘, ‚Bi-Directional‘, ‚Source -> Destination‘);
If your given classifier was the set as the end object by Sparx the following query will return the right associations:
select * from t_connector where End_Object_ID = 316623 and Connector_Type in (‚Association‘, ‚Aggregation‘) and Direction in (‚Unspecified‘, ‚Bi-Directional‘, ‚Destination -> Source‘);
Combining the two result sets will give us all the associations we are interested in.

To get qualified association member ends we have to use the xref table once again. Suppose we have the following model:

The following query joins the association with the required rows in t_xref:
select * from t_connector as c join t_xref as x on c.ea_guid = x.Client where Name = ‚OwnedMembers‘ and Start_Object_ID = 316623;
(note that again just specifying the Start_Object_ID might give you not the associations you were looking for and you have to possibly consider End_Object_ID as well)

Above query provides us with the following information about the qualifier in the column Description from the xref table:

Stereotypes and tagged values

Retrieving stereotypes for given element and its GUID requires a lookup in our good old friend, the xref table:
select * from t_xref where Name = ‚Stereotypes‘ and Client = ‚{309ABDC7-38F7-4ce1-8ED1-5B8708C8DA75}‘;
You can use above query for any element that can have a stereotype assigned. Just specify the corresponding GUID of the element.

Stereotypes can have a set of tagged values. In Sparx a tagged value must not be directly related to a stereotype, however. You can query any tagged value for an element. Depending on the type of element you have to query a different table.

Tagged Values for classifiers:
select * from t_objectproperties where Object_ID = ?;
Tagged values for attributes (ElementID corresponds to the attribute’s ID):
select * from t_attributetag where ElementID = ?;
or e.g. by joining the tables:
select * from t_attribute as a join t_attributetag as t on a.ID = t.ElementID;
Tagged values for operations (ElementID corresponds to the operation’s OperationID):
select * from t_operationtag where ElementID = ?;
or e.g. by joining the tables:
select * from t_operation as o join t_operationtag as t on o.OperationID = t.ElementID;
Tagged values for associations (ElementID corresponds to the association’s GUID!):
select * from t_taggedvalue where ElementID = ?;
or e.g. by joining the tables:
select * from t_connector as c join t_taggedvalue as t on c.ea_guid = t.ElementID;
Hint: Except for associations (and the table t_taggedvalue) Sparx distinguishes between „simple“ and „complex“ tagged values. The column Property in the respective database table always yields the tagged value’s name. For simple tagged values, the value is stored in the column VALUE. This is different from “complex” values, which carry the string ‚‚ in the column VALUE and define the actual values in the column NOTES.

Template parameters and bindings

If you want to find template parameters for a given classifier by its GUID use the table t_xref:
select * from t_xref where Client = ‚{309ABDC7-38F7-4ce1-8ED1-5B8708C8DA75}‘ and Type=’element property‘;
You will get something like this in the column Description:

To get type constraints for template parameters for a given classifier use the table t_xref:
select * from t_xref where Client = ‚{309ABDC7-38F7-4ce1-8ED1-5B8708C8DA75}‘ and Type=’ownedelement property‘;
In order to get the constraints for a specific template parameter we have to use the template parameter’s GUID from the result set of our first query and match it against the column Supplier. The template parameter’s GUID has to be parsed from the column Description (see above table).
select * from t_xref where Client = ‚{309ABDC7-38F7-4ce1-8ED1-5B8708C8DA75}‘ and Type=’ownedelement property‘ and Supplier = ‚{0BF8DC12-16F7-4231-906A-6639A8C3CF96}‘;
Template bindings are stored in the table t_connector. For a given classifier and its Object_ID you would find them as following:
select * from t_connector where Start_Object_ID = 316623 and Connector_Type = ‚TemplateBinding‘;
The details for a template binding can be found in the t_xref table, e.g. by joining the tables:
select * from t_connector as c join t_xref as x on c.ea_guid = x.Client where Start_Object_ID = 316623 and Connector_Type = ‚TemplateBinding‘;