Schema diagramming?

Posted by ericg on 15-Jan-2009 17:55

Hi there. Anyone have suggestions on a Progress database schema diagramming ability? I would like to document my database schema. An Erd for table relationships would be nice.

I am using OpenEdge 10.1C and Architect.

Thanks for your time.

All Replies

Posted by Thomas Mercer-Hursh on 15-Jan-2009 18:33

You have a couple of options ... with one very large qualification.

There is an ERD tool which supports Progress whose name is eluding me at the moment.

It is also possible to model your database with Enterprise Architect. See information on PSDN and here http://www.oehive.org/EA . Of course, EA can be used for modeling a whole lot more than just the data structure and you can read about that on OE Hive too, if you are interested.

The big limitation, regardless of tool, is that Progress has never stored join information, i.e., foreign key data. Without that, all you have is a bunch of tables and it is left to you to figure out what the relationships between them are. Phil Magnay's MDG technology described at the link above does include a "relationship guesser", but depending on your schema it can fail spectacularly since it guesses at relationships based on like field names.

If your database has table prefixes on every field name, it won't find anything. If you have like-named audit fields in every table, it will merrily connect everything. I have been trying to convince PSC to release the source so that people could code in exceptions to handle such things, but so far no go.

With my ABL2UML tool ( http://www.oehive.org/ABL2UML ) the model is built from the dictionary itself and is richer than the MDG version and the analysis of the code provides empirical information on how the tables are being accessed. That could save reading a lot of code. Some day someone might ask me to build a tool that will build relationships out of that data and then we will have a pretty good idea not just of how the schema is designed, but how it is actually used.

Of course, this also serves to point out that the design is full of intent and intent is not recorded in the code ... with the exception of the rare comment. So, a designer may have intended to tables to be related in a certain way, but that is no guarantee that is what is happening in practice. I have seen some interesting examples over the years...

Posted by ericg on 15-Jan-2009 19:10

Thanks Thomas. I heard that Tugboat's Roundtable does some diagramming. But I don't have access to it.

Posted by Admin on 15-Jan-2009 23:24

ERD Tools for Progress that I know:

- ErWin http://www.ca.com/us/products/product.aspx?id=260

- PCase from IAP http://www.tools4progress.com

(specialized on Progress).

The two above can be pricy...

- Enterprise Architect as referenced by Thomas

If it's just for documenting and not designing, check Microsoft Visio. They can connect to a Progress DB via ODBC and automatically draw shapes for each table. You need to add the references between tables manually, but at least you got a good starting point.

I believe there are different versions of Visio. I use the Professional edition, that has these ESD capabilities.

Posted by Admin on 15-Jan-2009 23:25

Roundtable is great at managing and versioning the schema. But AFAIK diagramming or other design features are out of scope of the SCM tool.

Posted by ericg on 16-Jan-2009 17:48

Thanks very much Mike

Posted by ericg on 04-Mar-2009 18:10

Hi Mike. I was wondering how I can connect to my Progress DB via ODBC from Visio? Do I need to create a new data source? Do I need to set up anything with the Progress DB?

Posted by kevin_saunders on 05-Mar-2009 01:44

Yes, you need to create a data source to Progress (it will be something like Progress OpenEdge 10.1x Driver).

If the DB is set to accept SQL connections (i.e you are not using the -ServerType 4GL parameter) then there is nothing you need to do on that side.

Posted by Thomas Mercer-Hursh on 05-Mar-2009 11:09

But, is it going to do any good? There aren't many tools out there which will deal with the OpenEdge schema. One can attach with SQL and make queries, but that isn't the same as getting it to draw diagrams.

Posted by ericg on 05-Mar-2009 12:18

So I am choosing the ODBC Generic Driver and tried to create a new data source with this:

DRIVER=Progress OpenEdge 10.1C Driver

server=myserver

database=mydatabase

But I think this is incorrect and at the end, the ODBC Text Setup dialog box has blank information.

On the server I see that it has the Progress OpenEdge 10.1C Driver driver installed.

Posted by kevin_saunders on 09-Mar-2009 04:10

In the ODBC Data Source Administrator, select the 'System DSN' tab, then click Add. Select the Progress driver, then select 'Finish'. Fill in the information on the General tab and on the Advanced tab, select 'READ UNCOMMITTED' as the Default Isolation Level.

Posted by Thomas Mercer-Hursh on 09-Mar-2009 11:06

Kevin's recommended procedure is described in some detail here http://www.oehive.org/node/1025 except that connection is for one which will be updated. If yours won't be, use Read Uncommitted as he suggests.

Posted by ojfoggin on 10-Mar-2009 16:47

From my half day of playing around with an evaluation copy of Architect I have seen the DB Navigator Perspective and the table data it holds.

There is a tab about foreign keys. Our database has none (like you said). It is basically a database of individual tables.

Is there any way (even manually) of populating this data from the current table structure?

i.e. our item table has a key field kditem that is used in a lot of tables as a link. BUT we have a field description in many tables that is unique to each table (i.e. definition.description is not relevant to item.description)

The guesser that you mentioned might get most of the links but being able to show Architect that kditem is a foreign key in these other tables would help a lot. Is anything like that possible without completely rebuilding the database?

Thanks for any help.

Oliver

P.S. I wouldn't normally hijack a thread but it is on a very similar topic.

Posted by Thomas Mercer-Hursh on 10-Mar-2009 17:24

With OE Architect no ... with Enterprise Architect, sort of or could be. The problem with this in OpenEdge on its own that there is simply no place for it in the database. You could create your own tables and populate them of course, but that isn't going to articulate with any standard tools. There has been some talk of this getting added in a future release, but no promises, of course.

There is a tool that is part of Phil Magnay's tools for Enterprise Architect which reads a .df to create a UML model of the database. It includes a "relationship finder" which will guess at building relationships based on common field names. Of course, how well this works will depend on your naming standards. If you have QAD type fields that each have a table prefix, it will find nothing. If you have userid and modify-date fields in every table for auditing, it will join every table. If you have Customer.Number, but Order.Cust-num, it is going to miss the link. I have begged and begged and begged for them to release the source for this tool so that people could put in patches for their own needs, but no luck.

Your other option is to use my ABL2UML tool. That builds the UML model directly from the dictionary and creates links from the code to the table so that you have an empirical model of how the tables are used. I could easily see doing some reporting on that and/or actually building relationships from it after some review. That, of course, tells you what is really going on, not just what you think is going on.

This thread is closed