Reverse Engineering - 3 issues

[Mustanshir Hussain]

Hi,
I have reversed engineered around 2000 Oracle tables into EA 7.1.828

1. When I drag and drop a table onto a diagram, the "VARCHAR2" datatype is displayed alongwith the "BYTE" keyword. for e.g. firstname VARCHAR2(50) is displayed as firstname: VARCHAR2(50 BYTE). The generated DDL also contains this BYTE keyword. This is ok, but NVARCHAR2 datatypes are displayed with "CHAR" keyword. i.e. firstname NVARCHAR2(50) is displayed as firstname: VARCHAR2(50 CHAR). Because of this, the generated DDL does not execute unless the CHAR keyword is removed. "CHAR" & "NCHAR" datatypes also have the same problem. Can someone please tell me how to prevent EA from inserting this unwanted BYTE & CHAR keywords while reverse engineering?

2. From the project browser, when I try to "Generate Code" for a table, EA opens the correct dialog window for generating the code. Whereas, when I try to "Generate Code" for the same table from a diagram, EA always opens the "Generate DDL" dialog window only. Is this a feature or a bug?

3. Using the Security feature, I created a user having only 2 permissions viz., Generate Documents & View Locks. When I right-click a table, both in diagram as well as in the project browser, the "Generate Code/DDL" options are disabled as expected. However, when I go into the properties of a table, navigate to "Table Detail" & click on "Generate DDL" button. The "Generate DDL" window opens and I can generate the DDL script from there. Is this also a feature or a bug?

Regards,
Mustanshir

[«Midnight»]

I suspect - without proof - the following:

1. Sounds like recent changes might have introduced a bug. I think Sparx had done some work on Oracle data types with the BYTE keyword. Perhaps they inadvertently introduced the CHAR keyword where it should not have appeared. Send an immediate message - this evening our time will be Friday in Australia, so if you don't send the message early they won't read it until next week - to Sparx support asking about this. The address is on the About Us page near the bottom. [Use the link at the top left of any forum page.]

If I'm correct about the cause, submit a bug report to Sparx. There's a link near the bottom of any forum page, or at the top of the Registered Users page. The latter is preferable, since you can attach sample files to show them the problem. This is a user forum, so it is important that you submit the bug report to Sparx directly.

2. DDL code can be generated on a per-package or per-table basis (and only for these two options). If you have a package selected in the Project Browser, and no table selected on a diagram, you'll get the entire package. Otherwise you probably go to the per-table default. [This latter might let you select and generate multiple tables, but the idea is the same; it is not generating the rest of the stuff in the package.] AFAIK there is no per-diagram option.

3. Sorry, no solid ideas on this. Sounds like a bug to me.

HTH, David

[Mustanshir Hussain]

Thanks for your help David.

I have logged a bug report with Sparx Systems.

In addition to the 3 issues mentioned above, I have noticed that Unique Indexes are also not reverse engineered. They are simply not picked up by the EA, while reverse engineering. Please note that I am referring to Indexes which are defined as Unique and NOT "Unique Keys".

One more thing, regarding point no. 2, I think I was not clear enough. I will quote again with some minor addition... "From the project browser, when I try to "Generate Code" for a table, EA opens the correct dialog window for generating the code. i.e. I can generate the table as a Java code. Whereas, when I try to "Generate Code" for the same table from a diagram, EA always opens the "Generate DDL" dialog window only. From here, I cannot generate it as a  Java code. Is this a feature or a bug?"

Anyway, I have reported all 4 points as bugs to Sparx Systems. Lets see what they reply.

--Mustanshir

[«Midnight»]

Mustanshir,

I remember there has been some discussion regarding Unique constraints - how and where they are (and are not) captured and applied by EA. Seems this still needs some work. Thanks for submitting the report, which will get the issue on Sparx' radar. [In case it got lost in the clutter.]

As to the code generation, I see your point. [I should have picked this up the first time, so I apologize for the miss.] My understanding - based solely on use of the product - is that EA 'thinks' in terms of generating code when it sees a 'normal' class, and of generating DDL when it sees the «table» stereotype. The data modeling facility in EA is pretty much hard wired - this is why we cannot tweak the DDL engine the way we can the code generation subsystem - so we are extremely limited here.

What I suggest you do is create your own generation system to get program code (Java and such) from your data elements. This would be based on EA's code generation system - with transformation scripts and the like - instead of the DDL system. What you would be doing is massaging scripts to recognize the data-related stereotypes and react accordingly. There would be some work involved, but it should be possible without too much effort. The type of code involved should be somewhat straightforward, since it is based on a data model; there should be much less room for variation than 'ordinary' class models.

Oh, and don't forget to send in (YA) feature request for opening up DDL generation, and whatever else you encounter. Sooner or later the number of unanswered feature requests will get large enough that Sparx will get the message...

HTH, David

[Mustanshir Hussain]

Hi David,
I have already tried creating my own DDL language, using EA's Code Generation Template feature (this was the reason, how I landed up with my bug no. 2). Unfortunately, I hit an obstacle over there regarding certain attribute variables. To be more precise, it was "Precision" & "Scale". Through my DDL language I tried to generate something like this "some_number_field NUMBER(8,2)". But I could not find %attPrecision% or %attScale% or anything similar to that. Sparx has provided quite a few variables such as %attName%, %attType%, %attLength%, etc. but some like Precision and Scale are missing or else maybe I am unable to find it in their EA Guide.
As a temporary solution, I generated the DDL scripts with BYTE & CHAR included, using EA's DDL generator, then removed those annoying keywords using "replace all" in all the generated scripts using a text editor.

As for the Unique Index bug, I have found out 2 more interesting things. I created a table with PK, UK, FK and other additional columns. Then I manually created 3 indexes. First was a Normal index on a general column. Second was a Unique Index on the FK column. And Third was again a Unique Index but on a general column(non-FK). Now, when I reverse engineered this table....
a) The Normal Index AND the FK Unique Index were captured by EA. Only the Third Unique Index was skipped out.
b) Although the FK Unique Index was picked by EA, it did not mark it as Unique. i.e. Under Index Operation properties --> Extended properties, the Unique Checkbox was unticked. And because of that when I generated the DDL, the original Unique Index on FK came out as a Normal Index.

I can't even add the missing Unique Indexes manually, because as soon as I sync it with the database, my changes are overwritten by what EA finds in database.

For me, the Unique Index bug seems to have taken a priority. I hope sparx systems resolves the same asap.

--Mustanshir

[«Midnight»]

The problem with precision and scale seems odd. These are actually fields in t_attribute; they are not constrained to data modeling, so could theoretically be used throughout the rest of the product.

However, the only mentions of these things seem to be in the API reference, and a brief note in transformation templates. The focus in the latter case seems to be oriented only toward creating tables and columns, not to engineering them.

Perhaps you can capture the results of a custom query and work on the resulting data set. If not, you'll likely need to write an add-in. The good news is that these properties are exposed to the API, so the complexity of the add-in should be limited.

I see some ideas for additional feature requests. If you make them, please post a new thread in the Suggestions section for each request. If you start the thread before making the request, you can paste in the URL of the thread, to let Sparx more easily participate in the discussion.

David

[Mustanshir Hussain]

Hi David,
Many thanks for telling me about writing a custom query and working on the resultset. Spooling out a DDL script from repository itself is much convenient for me than writing the DDL Code Generation Template. I have analysed their "t_" tables and they seem quite straightforward to work upon.

I have received a reply from support. They will be addressing the issues I have raised as soon as possible. I have also told them, both through email as well as through Feature Request form to open up their DDL Generation Template so that we can tune it the way we want to.

I have also given them this thread's URL link, as most of my bugs have already been published here.

Here's one more feature request I would like to have: In the EA repository, I have observed that most of the tables are having PKs. And they are related to other tables for e.g. t_object is the parent of t_attribute & t_operation, etc. But the child tables do not have FKs. It is left to the imagination of the analyst to figure out the parent-child relationship. for e.g. t_operationtag.elementid = t_operation.operationid. It took me some time to figure out this join condition. It will be nice if Sparx Systems updates their repository creation DDL script and adds the FKs in it. I can thus reverse engineer the repository itself, layout the diagram, print it and pin it up for my handy reference.

Regards,
Mustanshir

[«Midnight»]

I've been hoping for some time that Sparx would both update and document their schema. The current one dates back to EA 4.0 with only limited change.

Of course this would be a major effort, with quite a few breaking changes for current automation users and such. To counter that, we are really stretching the limits of what the current schema will accommodate, and the technology behind the EA 'engine' is getting very dated.

So far my hopes have been in vain.

[But that's just my personal opinion, and should be taken with an appropriate quantity and type of seasoning.]