Hello,
I am trying to use Hibernate to build mapping files against an existing Progress database. I'm told that there are established foreign key relationships between tables. However, when I run the hibernate-tools against the database I do not see any of these relationships.
I've also tried something similar to the following, which should return something but does not:
Connection dbConn = DriverManager.getConnection('jdbc:datadirect:openedge://my.server.local:2600;databaseName=name;DIL=READ UNCOMMITTED;AS=500','user','pass');
DatabaseMetaData meta = dbConn.getMetaData();
ResultSet pks = meta.getPrimaryKeys('name','pub','table'); // this returns stuff
ResultSet ifks = meta.getImportedKeys('name','pub','table'); // this does not
ResultSet efks = meta.getExportedKeys('name','pub','table'); // neither does this
What I end up getting w/ Hibernate are mapping files describing each table, including the primary key for each table, but I don't get any of the one-to-many or many-to-many relations. Adding them by hand works but this defeats the purpose.
The driver version I am using is whatever comes with 11.1.
Any ideas?
-Will
Hi Will,
The JDBC driver should return the relationships from getImportedKeys() and getExportedKeys() as you expect. As you suspected, that is also how Hibernate would identify the table relationships. Can you include the DDL used to create the tables? That should make it fairly straight-forward for me to identify the problem.
Thanks,
Brody
I cannot.
I forgot I posted this question. Subsequent to the original post, I talked w/ our development staff again and determined that we're enforcing these relationships via triggers, not via real key relationships in the db. I will have to find another way to generate Hibernate (or some other ORM) mappings.
Thanks for your reply.
Yes, this relationship information is not maintained in the OpenEdge database the way it is in other databases. As you can imagine, there has been a lot of kvetching about this over the years for people who are approaching OpenEdge from the SQL side ... although in practical terms it seems to have remarkably little impact on the ABL users. ABL is also different from SQL in that it does compile-time optimization instead of real time optimization ... arguments can be made both ways, but compile time does allow one to check whether the choice the compiler has made is a good one. There are a couple of ways to get this information, sort of. Ultimately, there is no substitute for going through all of your code and finding all the empirical relationships ... including the ones not supported by indexes. You might want to check out http://www.oehive.org/ABL2UML and http://www.oehive.org/EA In the latter, there is reference to work by Phil Magnay of a plug-in for Enterprise Architect which will read a .df and build a table model. It includes a "relationship guesser" based on matching field names. This fails miserably on QAD which has table prefixes on field names and on schema where there are common audit trail fields in every file. I am working on a new version of ABL2UML which will provide an open source way to build the input and will allow use for schema only. I plan on putting a relationship guesser in it for which there will be source code so that you can tune it for your schema. That should be out in a month or two.
He's not using the 4GL, just going straight to the database... one can build the mapping schema for hibernate manually but usually there are tools that generate that from the database schema, proved the rdbms supports foreign-key constraints which is not the case for Progress (unless you create the tables from SQL DDL but think there is some 4GL app that owns the database in the first place).
I know he is not using the ABL. I was advising on how to find the relationships to code in Hibernate.