Retrieving Relation Information

mikesena

mikesena

New Member
Hi all,

I am working with Progress in OpenEdge 10.2A.

Basically what I'd like to do, is work out what relations a table has to other ones. I'm iterating through the list of tables in the database already, so what I then want to do is say 'Customer' is related to 'Orders, Invoices' etc.

How am I able to do this? I've been mucking around for ages with this but haven't been able to get close to what I want. I suspect it might be in the metaschema, but I'm not sure what I'd be looking for.

Thanks in advance,
Michael
 
Thanks for the reply there,

I know that's one way of doing it, but what I'd like to do is get that information myself in progress. It needs to be dynamic, rather than knowing the relations beforehand.

I.e. where does the data dictionary get the information from?
 
DEF VAR campo AS CHAR FORMAT 'X(30)'.
REPEAT:
UPDATE campo.
FOR EACH _field WHERE _field-name = campo NO-LOCK.
FIND _file OF _field NO-LOCK.
DISPLAY _file._file-name _field._format WITH TITLE campo.
END.
END.

You can see other fields of the _field or _file tables
 
Thanks for that, I hadn't thought of doing it that way

But, is there any way to confirm relationships between tables, rather than comparing common field names between them? I know its bad database programming to call fields by common names (i.e. having a field called 'Name'), but isn't there a way to list which relationships were defined for that database. Like, what specifically links to Customer for example, rather than just has a common field.

I hope that makes sense
 
The last code ask for a field.
The next code asks for a table and looks in the metaschema the fields of the table and what other tables use the same fields.

DEF VAR busca AS CHAR FORMAT 'X(30)'.
DEF BUFFER tabla FOR _file.
DEF BUFFER campo FOR _field.
REPEAT:
UPDATE busca.
FIND _file WHERE _file-name = busca NO-LOCK.
FOR EACH _field OF _file NO-LOCK
BY _field._order.
DISPLAY _field-name.
FOR EACH campo WHERE
campo._field-name = _field._field-name NO-LOCK,
tabla OF campo NO-LOCK.
DISPLAY tabla._file-name.
END.
END.
END.
 
So in a progress database schema, the only way to tell if there's a relationship between two tables, is if there is a field with the same name?

That just seems a bit odd to me.
 
Hi
In a relational model the relations are based on fields with the same name and format.
I agree with you. We don't have to use the field 'name' for customers and employees. In that case use different names, but in the tables where you want relations use the same name an format.
If you use cust_num in the table customer then use cust_num in the table order.
With this the relations are naturals.

Using this you can use for example
FOR EACH order OF customer

otherwise you must use
FOR EACH order WHERE
order.cust_num_ord = customer.cust_num_cust
 
Okay thanks for that, that all makes sense.

I guess I'll have to hope that the relations being made in the application I'm developing are meant to be bound together :)

Thanks for your help
 
Just stepping in ...

In order to be able to an OF instead of WHERE table2.field = table1.field the following must be true:
  • The fields must have the same name in both tables
  • The field must be indexed in the related table.
Therefore, checking if the field is present in the other table might not be sufficient. One would also need to check if the field is indexed.

_Index and _Index-Field needs to be checked too.

HTH, RealHeavyDude.
 
Progress does not formally define relationships between tables. There is only the loose notion of commonly named fields.
 
Thus far, in a OE database, there is no explicit join information. According to the DB design, you may get some hints by finding common field names, but that may or may not be useful in your particular case. E.g., some databases have fields like userid and date-time in almost every table as a audit function and all those joins are meaningless. Or, a QAD style database will have table abbreviations in front of the field names and nothing matches. Even in a more typical database you can miss a lot of connections, e.g., if an order has both a billing customer and a ship-to customer, they can't possibly both have the same field name as the customer record.

Indexes can give you a hint, but in any given OE database there are usually joins in the code which are poorly supported by indexes as well as indexes that are not actually used anywhere.

The only tool I know of that really pulls all this information out of the code itself is ABL2UML (http://www.cintegrity.com/ABL2UML and http://www.oehive.org/ABL2UML). It will build a UML component model of all of the code components and all of the data components, show how all of them connect to each other, show what fields are used where, and even show you the WHERE clauses that are used in each case. That will show you the empirical joins which are actually made in the code.

There is a download on PSDN for use with Enterprise Architect which has a "relationship guesser", but it works by looking for shared field names and there is no source code so all of the cautions in the first paragraph apply.
 
You must log in or register to reply here.
Back
Top