Recently down on a client site I had the opportunity to work with Sparx Enterprise Architect on a project.  For those of you not familar with the product the easiest way to describe it would be to imagine a coffee drinker used to simple old joe walking into a Starbucks – the sheer range of options available can be overwhelming.  It is a very powerful modelling tool and allows you to model many different situations with a variety of techniques and styles.
For the purposes of this blog post I’m going to look at a small subset of the products capabilites, and that is databases.  I’m not even going to go over the use of the database builder part of the toolset (that may be a later post) but will instead dive into how database structures are held in the internal object model.  “Wait!” you say – “the what?” – well when I started working with Sparx EA I needed to find a way that I could quickly and easily add additional fields to a large number of database tables that had been imported into Sparx EA from a source database.  In order to do this I started making use of the scripting capabilities but quickly fell foul of the fact that because Sparx EA was developed to handle a lot of different concepts and approaches it has a very generic object model (the round hole of the title) and fitting a database (square peg) into that has some very confusing decisions.  One of my pet peeves I had when scripting was that I couldn’t find a nice simple resource that pointed out where everything was in the object model for a database, so I thought that I should create one.
Without further ado lets dive on in.  The following image (from EA’s online documentation) outlines the majority of the API objects that Sparx EA will use when modelling a database table,  but as some of the documentation gets a little thin on the ground I created the following table that outlines in greater detail how the different components of a database table map to these objects.

Database Table PartSparx EA ClassPropertyCollectionKeyName <=> ValueTableElementNameFieldAttributeNameSchema (Table Owner)ElementTaggedValuesOwnerField DatatypeAttributeTypeField Default ValueAttributeDefaultField AutoNumAttributeTaggedValuesAutoNumField IncrementAttributeTaggedValuesIncrementField StartNumAttributeTaggedValuesStartNumField NullabilityAttributeAllowDuplicatesField used in PK?AttributeIsOrderedPrimary Key FillFactorMethodTaggedValuesFillFactorPrimary Key NonClusteredMethodTaggedValuesNonClustered

Now a few caveats – this work was initially done in Sparx EA v12 and hasn’t been revised with any changes for the new v14 version.  I was focusing on a SQL Server 2012 database model so if you are using a different type of database there may be some differences involved.  Also this is not an exhaustive list of the difference database properties – for example calculated fields aren’t covered as I was only dealing with the specific properties that we had on our sample databases.
You may have noticed the method object(s) in the above image – this is where Sparx EA stores details about database constraints (such as primary keys, unique indexes, and foreign keys).  To differentiate between the different types of constraint you need to examine the Stereotype property on the Method object.  The following table has some examples:

Database Table PartSparx EA ClassCollectionSingular EntityPropertyValuePrimary KeyElementMethodsMethodStereotypePKForeign KeyElementMethodsMethodStereotypeFKIndexElementMethodsMethodStereotypeindexUnique constraintElementMethodsMethodStereotypeunique

If you want to play around with Constraints (and let’s be honest who wouldn’t) then you need to work with the MethodConstraint and MethodTag collections.  These hold all of the different properties required for defining each type of constraint.  I’ll expand on the Primary Key and Foreign Key constraints below.
Primary Key
To create a primary key first create a Method for your Element with the Stereotype = “PK”.  Once a primary key has been created, all of the standard database properties that you’d associate with it are added as MethodTags to the parent Method, for example FillFactor and NonClustered.  To associate the desired table fields with the primary key you need to add them in as MethodParameters.  This is done by adding the field name and data type as a new Parameter to the MethodParameters collection.
Foreign Key
Sparx EA represents a foreign key as an association relationship (which makes sense), but the process of defining it in the API requires a bit more effort than the Primary Key.
First you need to create a Connector object (not shown in the picture) with the Stereotype = Association, and then provide all of the property values in the following table.  In this situation Supplier refers to the table with the primary key that the foreign key table (Client) is referencing.

SupplierID<<Supplier table id from EA database>>StyleExFKINFO=<<Foreign Key Name>>:DST=<<Reference Table PrimaryKey Name>>:;StereotypeExEAUML::FKClientEnd.Role<<ForeignKeyName>>ClientEnd.Cardinality0..*SupplierEnd.Role<<Supplier Table PrimaryKey Name>>SupplierEnd.Cardinality1DirectionSource -> Destination

NOTE: In order to get the <<Supplier table id from EA database>> you need to query the underlying database for the OBJECT_ID in the t_object table for your desired table name for your current EA package.  You will have to look on the net for guides how to do this.
So that’s about it, you’ll notice I haven’t put any example scripts in here, as the local examples that come with Sparx EA are quite comprehensive (typical Create, List, Update, and Delete examples) and in a variety of languages.  Perhaps in a later post I’ll work through a scripting example.
Until de next time,
Bork,bork,bork. Brent.