v16 - Database Builder - Why is it deleting and recreating?

[Paolo F Cantoni]

At present, this is not a defect call; it's merely trying to understand how the Database Builder (DBB) make decisions.

When we reverse engineer some of our SQL Server databases, DBB decides to delete tables and views (and other items) and recreate them (even though ostensibly they haven't changed).

Does anybody know what criteria DBB uses to delete and recreate versus merely updating the changed properties?

We may perform the import from more than one machine (i.e. we initially imported from machine A but are now trying to update from machine B).  Does that have anything to do with DBB making a mistake?

Also, one may be with 32-bit EA and another with 64-bit.  Is that a consideration?

TIA,
Paolo

[Geert Bellekens]

Hmm, AFAIK EA doesn't delete and recreate elements with the same name.

That is also a crucial element in our process, as we have mappings from the technical data model (DB model) to your logical data model.
If EA would delete and recreate, we would loose the mappings.

Geert

[Paolo F Cantoni]

Hmm, AFAIK EA doesn't delete and recreate elements with the same name.

That is also a crucial element in our process, as we have mappings from the technical data model (DB model) to your logical data model.
If EA would delete and recreate, we would lose the mappings.

Geert

Thanks, Geert,
I just realised that our Infrastructure group moved the DB between servers since we last reverse-engineered it.  I suspect that's almost certainly the cause, but it would be good for a Sparxian to confirm.

Paolo

[Paolo F Cantoni]

So, after updating the Database Builder model with the live DB, we got "No Differences".
Overnight, we did some background processing, adding new relationships (NOT FK Constraints) between many of the DB components.
We also moved some Views between schemas on the live DB.

Now DBB shows many changes (Count column = 1) in things that don't seem to have changed, and selecting such an object, the details pane shows either blank or "No Action".
However, if I "Set Import All", it will once again delete and recreate the items that have NOT changed (as far as we can tell).

Help!  Where should I start looking?
Paolo

[Paolo F Cantoni]

I have noticed that when we use DBB to Load a Data Model, sometimes the database components are visible on the left-hand pane and sometimes not.  They are ALWAYS available in the Browser on the right.  These Data Models were transferred from another repository.  It seems as though the DBB needs some metadata in the repository to "activate" the DBB process and enable it to perform the differencing mechanism correctly.

Can anyone confirm this?  Does anyone know where the metadata is stored?

It seems as though so long as the components are visible in the left-hand pane, the differencing mechanism works correctly.  But to get them there, you have to perform an import in the current repository.  The metadata doesn't appear to be transferred when the Data Model branch is transferred between repositories.

Paolo

[Paolo F Cantoni]

I have noticed that when we use DBB to Load a Data Model, sometimes the database components are visible on the left-hand pane and sometimes not.  They are ALWAYS available in the Browser on the right.  These Data Models were transferred from another repository.  It seems as though the DBB needs some metadata in the repository to "activate" the DBB process and enable it to perform the differencing mechanism correctly.

This was the key to solving the problem.
Some context...
After we reverse-engineer the database, we post-process it to enhance its usability for our users.  (for example, we scan the schemas and create folders for each schema and place the components under them.  Also, to make the items hold consistent metadata with our other non-database items and to respond to properties with widgets etc., we add an additional stereotype from our profiles to do this.  (It also allows the addition of other properties via the "Synchronise Stereotype" functionality.)  So we have more than one stereotype in StereotypeEx.


The problem is caused by the ordering of the (in this case, two) stereotypes.  The first will be the primary, the other the secondary.
 For example, EAUML::table and MDG::DBTbl.  StereotypeEx is stored in t_xref.Description as @STEREO;Name=table;FQName=EAUML::table;@ENDSTEREO;@STEREO;Name=DBTbl;FQName=MDG::DBTbl;@ENDSTEREO;
If they are this way around (i.e. table is the Primary stereotype - showing in the Properties Window as:
EAUML::table
MDG::DBTbl)  Then DBB will "see" the tables etc.
If however, they are the other way around (i.e. DBTbl is the primary, yielding: @STEREO;Name=DBTbl;FQName=MDG::DBTbl;@ENDSTEREO;@STEREO;Name=table;FQName=EAUML::table;@ENDSTEREO; - in t_xref.Description & appearing in the Properties window as:
MDG::DBTbl|
EAUML::table) Then DBB will NOT "see" the tables. Consequently, it will delete the "tables" that are already there and add new ones (even though they haven't changed)!
NOW since the rest of the database technology operates correctly ^[1] regardless of the order of the stereotypes in StereotypeEx, this is a typical EAUI self-inconsistency (and, therefore, defect).
Reported,
Paolo
^[1]  We've been using these techniques well before DBB was on the scene and haven't found an issue before.

[Eve]

We've been using these techniques well before DBB was on the scene and haven't found an issue before.

Here's at least one place where you did discuss restrictions on the way stereotypes work for database modeling, where I stated your stereotype would only work as a secondary stereotype.

https://sparxsystems.com/forums/smf/index.php/topic,44564.msg262675.html#msg262675

[Paolo F Cantoni]

We've been using these techniques well before DBB was on the scene and haven't found an issue before.

Here's at least one place where you did discuss restrictions on the way stereotypes work for database modeling, where I stated your stereotype would only work as a secondary stereotype.

https://sparxsystems.com/forums/smf/index.php/topic,44564.msg262675.html#msg262675

Indeed, but we found that statement wasn't always so.  With our stereotype as primary, everything appeared to work.
However, if our stereotype is secondary, our shapescript is not triggered - so we can't achieve one of our main requirements.  I have no problems with our stereotype as secondary if it would work, but it doesn't.

If someone could provide a mechanism for overriding the EAUML shape script with the secondary stereotype's shapescript that worked, we'd be happy to conform.

Paolo