Script to Workaround Limitations of Custom Database Types

[sousac]

We are working with databases that are not natively supported in Sparx 14 (e.g., SAP Hana).  A current shortcoming is that the "data type mapping" feature (to express equivalency of data types from one platform to another) does not work with databases that you have added yourself.  I've confirmed this "bug" with Sparx Support. 

Currently when you change the "database" of a table element from one platform to your own custom version, Sparx either maps everything to "varchar" or in the case of going from one custom DB to another custom DB, you lose the datatype and length/precision specifications.

To compensate, I wanted to write my own script to "convert table from database platform x to database platform y", however, I can't seem to find where the "Database" property is exposed in the EA Object Model.


Does anyone have a sample script to manipulate the extended properties associated with EA's data modeling extension (e.g., Database Platform)?

[sousac]

Found it.  I had gone astray looking at custom properties when the "database product" is simply stored in the GenType property of the element.

[Geert Bellekens]

Here's a bunch of wrappers classes for database concepts such as table, columns, etc..
https://github.com/GeertBellekens/Enterprise-Architect-Add-in-Framework/tree/master/EAAddinFramework/Databases
Hope this helps a bit to figure out where EA hides all the goodies.

Geert

[sousac]

Thanks as usual Geert.

I had prepared a script to effectively convert tables from one platform (e.g., SQL Servere) to a custom DB (e.g., Hana) but am seeing some odd behaviour that I can't figure out.

The script flipped the GenType of the Element (table) to the new database and then set the Attribute.Type property to the new target datatype for that platform (which had been configured ahead of time in the repository).

The odd behaviour is seen when creating a new association/foreign key.

It seems that, although the type of the attributes were updated (e.g., from Long to BIGINT for my PKs for example), however, when the PK migrates as an FK it still carries its original type before the script was run (Long in this case) which was the original datatype.  Manually using the Sparx UI to change the PK type to a different value and then back to BIGINT solves the issue.

This would indicate that there is an additional property (in addition to Type) that needs to be updated by my script but I am at a loss to find it (I though maybe classifier but that doesn't seem to be used in this case).

[Paolo F Cantoni]

Thanks as usual Geert.

I had prepared a script to effectively convert tables from one platform (e.g., SQL Servere) to a custom DB (e.g., Hana) but am seeing some odd behaviour that I can't figure out.

The script flipped the GenType of the Element (table) to the new database and then set the Attribute.Type property to the new target datatype for that platform (which had been configured ahead of time in the repository).

The odd behaviour is seen when creating a new association/foreign key.

It seems that, although the type of the attributes were updated (e.g., from Long to BIGINT for my PKs for example), however, when the PK migrates as an FK it still carries its original type before the script was run (Long in this case) which was the original datatype.  Manually using the Sparx UI to change the PK type to a different value and then back to BIGINT solves the issue.

This would indicate that there is an additional property (in addition to Type) that needs to be updated by my script but I am at a loss to find it (I though maybe classifier but that doesn't seem to be used in this case).

Hi Sousac,

IIRC the FK Constraint information is partly held in the relationship.  Have a look at the relationship properties and tags to see where.  You have to transform not just the tables, but the relationships.

Let me know if I'm right as I will need to do something similar in the fairly near future.

HTH,
Paolo

[Geert Bellekens]

FK information is actually kept in three places:

- the column
- the relation
- the constraint operation

All three need to be in sync to make it work correctly.
You might have forgotten to change the type of the operation parameter(s)

Geert

[sousac]

I don't think this is the crux of the issue as the problem materializes when a *new* relationship is added to the model after my script is run

Before Script
Primary Key of a table on SQL Server is of type Long
Existing relationships are in place with that table (i.e., it has one ore more parent-child relationships where it is the parent).

After Script
The table is now SAP Hana
The primary key and existing FKs are of type BIGINT (a Hana data type)
If I create a brand new table (Database set to SAP Hana)  and add a new relationship to the model in which the existing table is the parent, the resulting FK that is created is of type Long (the original data type that was present before the script).

As the relationship is net new, it seems unlikely that this is due to the existing relationship/FKs not being updated (unless Sparx examines existing FKs when creating net new relationships to new tables).

A more likely possibility is that the details of the migrated key is taken from the PK constraint of the table which is also storing the data type redundantly. This could make sense since the key to migrate is based on the members of the PK (in theory it could be a multi-part key).  Why it would also obtain data types from the PK would be odd.  Any thoughts on that hypothesis?

[sousac]

Theory confirmed. Geert was very much correct but what I was noticing was related to the PK constraint and how FKs are created when drawing a new relationship.

Explanation
When a  PK is migrated as a new FK in a child table (upon creating a new relationship), Sparx uses the contents of the PK constraint to migrate/establish the FK.  It retains the type of each member column (parameters of the method) and uses that to establish the type of the newly established FK attributes added to the child table. This makes sense. My error was in assuming the parameter was merely a reference to the member column rather than a separately persisted item.

So when creating a script to convert to/from custom DBs, one has to iterate through and update all of the constraints (PK, AK, FK) and indexes to ensure all the references to the columns (including those within constraints and index parameters) have their types updated to the new data types as well.

Thanks Geert and Paolo, your thoughts/questions helped me solve it.