Schema diagramming?

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...

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

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.

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

Thanks very much Mike

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?

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.

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.

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.

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.

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.

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.

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.