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.

[RAVITEJA306]

Hi Sousac,

I am a newbie to SPARX. I have a similar requirement to build a data model for SAP HANA Database. Earlier we were using SAP power designer. I understand that SPARX does not support ODBC connection for SAP and HANA Database. Could you share your approach on how data model from SAP HANA database can be imported to SPARX. Thank you for your time.

Thanks,
Ravi

[sousac]

"I am a newbie to SPARX. I have a similar requirement to build a data model for SAP HANA Database. Earlier we were using SAP power designer. I understand that SPARX does not support ODBC connection for SAP and HANA Database. Could you share your approach on how data model from SAP HANA database can be imported to SPARX. Thank you for your time."

I haven't found a way to reverse engineer from SAP Hana, however, you might be able to if you can find a 32-bit ODBC driver.  In my case we were doing custom development so I only needed to generate DDL (not reverse engineer).

I extended Sparx to support basic SQL-based DDL for Hana and also generate entity and constraint declarations for Hana CDS.  This is done by adding a custom database to Sparx and then creating your own versions of the DDL templates. I started with the Sybase ones as a base.

[sousac]

Hi Sousac,

I am a newbie to SPARX. I have a similar requirement to build a data model for SAP HANA Database. Earlier we were using SAP power designer. I understand that SPARX does not support ODBC connection for SAP and HANA Database. Could you share your approach on how data model from SAP HANA database can be imported to SPARX. Thank you for your time.

PS - I can share my custom DDL templates for Hana if you need those.  Our goal was to simply generate basic DDL, FKs, and Unique Constraints (nothing more exotic). Happy to share them when I am back from vacation next week.

[Richard Freggi]

Hello Claudio I'm developing my own DDL templates and could benefit if you share your previous work will send you a pm!  Thanks!