Reverse & foward engineering of database schemas - Forum - OpenEdge Development - Progress Community

Reverse & foward engineering of database schemas

 Forum

Reverse & foward engineering of database schemas

  • I would be interested in the hearing what people think about the need (or otherwise) of reverse & foward engineering of OpenEdge/Progress database schemas into UML design tools. The UML/MDA demos I just posted include some basic functionality in this regard. If there is a genuine need, what other functionality needs to be included to make it a viable/usable approach?

    Phil

  • I haven't tried out this version yet, but there were a couple of limitations in the version I did try where information in the dictionary did not seem to get imported, notably initial values and validation. Has this changed?

    The really obvious weakness is the lack of JOIN information in the OE schema. While you have the tool for "guessing" about relationships, this is going to work poorly or not at all with some schemas and is likely to have some notable issues with most schemas. Any chance we can twist the arms of the engine crew to add a JOIN table? It would be a pain to build it the first time, but at least then it would be done once and it would be over. With a guess step, one has a problem of potentially having to re-do a lot of work if one ever does a fresh import.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I haven't tried out this version yet, but there were

    a couple of limitations in the version I did try

    where information in the dictionary did not seem to

    get imported, notably initial values and validation.

    Has this changed?

    OpenEdge-specific dictionary elements are captured as TaggedValues upon import of the .df. To open the TaggedValues Window in EA, it's just Ctrl-Shift-6 (or from the main menu, View->Tagged Values). Any field with an 'INITIAL' value should have a tagged value called 'INITIAL' with the corresponding value. Same with VALEXP and VALMSG or any other dictionary element. Of course, re: VALEXP and VALMSG, this is just data attached to a field and not a constraint/rule expressed in the model. But that should be reasonably simple to achieve. It's just figuring out the most appropriate way to express these rules.

    The really obvious weakness is the lack of JOIN

    information in the OE schema. While you have the

    tool for "guessing" about relationships, this is

    going to work poorly or not at all with some schemas

    and is likely to have some notable issues with most

    schemas. Any chance we can twist the arms of the

    engine crew to add a JOIN table? It would be a pain

    to build it the first time, but at least then it

    would be done once and it would be over. With a

    guess step, one has a problem of potentially having

    to re-do a lot of work if one ever does a fresh

    import.

    I'm pretty sure a JOIN table is not under any current consideration so we're pretty much stuck with implicit relationships. Perhaps it might be possible to add more options or parameters to make the guesses. But yes, this is a real bother to work around.

  • Well based on the earlier version of the code, I'm not seeing these tagged values. If I go to a column which I know has an initial value and a validation expession, the Initial box is blank and Ctrl-Shift-6 (lovely affordance!) gets me only a tagged value for dump-name ... oops, interesting ... I initially opened the table, then the detail, then the columns and I get the tagged value for the table, but if I expand the table to the columns and click on that directly, I get the same display for the column, but now there are a bunch of tagged values, including the initial and validation ones. There really should be a more obvious way to find the tagged values than CTRL-SHIFT-6.

    I understand that translating the value expression into a constraint may have some tricky aspects ... not the least of which are the ones that are includes! ... but if there is a bucket for initial and an initial value, I don't see why that shouldn't be easy to populate.

    As for the joins, I think it is a tough problem. I wonder if we couldn't do something external to the core dictionary. I.e., a table or two with a .df we import, populate it, then export text and read that in as a part of the load?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • Tagged values are really very useful. They can be predefined so that only certain tags apply to specific class/element types and stereotypes. As well, as setting them up to be assigned using drop-downs for boolean, lists, etc, and file dialogs, color dialogs, long description dialog, etc. They really are quite handy.

    I usually keep the Tagged Values window open all the time and docked in the lower right of the EA window, in addition to the new Properties window and the Relationships window.

    As for the joins, I think it is a tough problem. I

    wonder if we couldn't do something external to the

    core dictionary. I.e., a table or two with a .df we

    import, populate it, then export text and read that

    in as a part of the load?

    Sure. There are probably a nunber of ways you could do it. It's just difficult arriving at a standard approach and building on top of that.

  • I'm pretty sure a JOIN table is not under any current

    consideration so we're pretty much stuck with

    implicit relationships. Perhaps it might be possible

    to add more options or parameters to make the

    guesses. But yes, this is a real bother to work

    around.

    Are you serious? This feature pops up year after year and it's still not considered as being valuable? Everything you do in a data centric application has to do with relationships between entities. As long as there will be no object-relational mapping framework for ABL, we have to deal with ProDataSets, temp-tables, buffers, queries, etc. Why duplicate join-information in ProDataSet's, queries and triggers, while you could refer to a declaration?

  • Are you serious? This feature pops up year after year

    and it's still not considered as being valuable?

    Yes. I'm serious. And I certainly did not imply that such a feature was not considered useful or valuable.

    Only that it is something that is not going to be available in the short-term.

    Everything you do in a data centric application has

    to do with relationships between entities. As long as

    there will be no object-relational mapping framework

    for ABL, we have to deal with ProDataSets,

    temp-tables, buffers, queries, etc. Why duplicate

    join-information in ProDataSet's, queries and

    triggers, while you could refer to a declaration?

    I understand the issues. But it's not my place to make promises or commitments on this forum in an area with which I have very little involvement.

  • On the surface, it would seem like this was a simple request ... but it's not. Merely creating a couple of tables and providing dictionary code to maintain them would be a couple of days work. But, then what? For the limited purpose of exporting to UML, yes, it would be keen ... assuming that it had actually been fully and accurately maintained. But, if one were putting something like this in the product, one would expect the product to do something with it. That's where it gets difficult and interesting. I can imagine some things that I might expect to happen, but they seem difficult and unlikely to assist with existing code, unless it would be in some kind of SQL optimization.

    What might actually be more useful, thinking a bit, would be if the prospective XREF tool created an empirical table of joins and we could access that for loading the UML. This would probably also identify some suspect joins!

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • At Exchange 2006 one of the engine guys mentioned referential integrity as something that's on the roadmap...

  • Finally!!! Having that concept (dare I call it a foreign key) would make reverse eng. and other tools (e.g. Database visualisation) and reporting (e.g. crystal) sooooo much easier!!

  • Of course, "on the roadmap" and "getting there soon" are not necessarily the same thing!

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • True. Personally I wrote a 4GL program to transfer the Progress schema to MySQL. Its got a little TempTable of "1 to many" relationships and builds the foreign-keys on the fly. then I put the whole thing into the new Hibernate "reverse engineer" and had it build me a full SEAM application based on that. It also fully documents the schema as it goes (in HTML ) which is cool.

    On the UML front, I've been putting the Schema into XML and using that to generate XMI maps and putting them into AndroMDA (http://www.andomda.org) - thats ok but its a LONG way from finished.

    I then took the first concept a little further and turned my MySQL database into a code mapping tool so that each ".p" was a table and each call a "foreign key". Then I could use DB reverse engineering tools to produce a code map. This is definately NOT for the faint hearted and you need lots of RAM.

    Muz

    PS - BTW your spell checker doesn't recognise 4GL as a word ....

  • On the UML front, I've been putting the Schema into XML and using that to generate

    XMI maps and putting them into AndroMDA (http://www.andomda.org) - thats ok but

    its a LONG way from finished.

    You should check out Enterprise Architect. From what I have been able to tell so far, its MDA potential is equal or greater than AndroMDA except for the pre-existing "cartridges" in AndroMDA, assuming that any of those are relevent.

    PS - BTW your spell checker doesn't recognise 4GL as a word ....

    That's because you are supposed to use ABL instead!

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • Do you mean the borland one? If so I just got it installed today

  • No, see http://www.sparxsystems.com/

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com