Database METADATA - "Foreign Key" vs. "Create Index"

[marius]

I created a First Spirit project which has one database schema (Oracle) with two tables:
parent(1) - (*)child    (1 to many relation).

When I designed the 1:N relation (Step 2) First Spirit displayed me the following information: "Name of relation: PARENT_CHILD_FK (only in database)"

...so far so good (I got this message as on the database METADATA there will be a foreign key constraint added)

After saving in First Spirit I was checking the METADATA of the two tables in Oracle. I was expecting that the METADATA of the Child table would contain meta-information of "CONSTRAINT ... FOREIGN KEY" - (like in the 3.x First Spirit version...please correct me if I'm wrong)...but this meta information is missing and the "CREATE INDEX" is used.

The METADATA information is very important in my case because I'm using reverse engineering for code generation.

Question: is this the way how First Spirit wanted to be (at database level - not to use FOREIGN KEY constraints)? Is this some how configurable - so the "FOREIGN KEY" will be used instead of "CREATE INDEX"?

Thanks,.

Marius

[feddersen]

Hi Marius,

which FirstSpirit and Oracle version are you using?

Christoph

[marius]

FirstSpirit Client 4.2.219.38784

Oracle 11g

[feddersen]

You correct that FirstSpirit 3.x used foreign keys. That behaviour has been changed with FirstSpirit 4.x, for reasons I do not know yet. It should be save to define the foreign keys manually, we already did that.

Btw Oracle11g isn't officially supported at the moment, but will be with FirstSpirit 4.2R4.

[marius]

Oracle 11g / Oracle 10g ... same thing from this discussion point of view.

Manually setting up the Foreign-key for a large number of tables (80 - 100) in about 50 schemas ... I don't think is a good idea.

[feddersen]

You could do it the other way round. Create all schemas with an external tool, connect them to FirstSpirit and enable schema syncing. It may preserve the foreign keys then. Might be worth a try.

Please keep in mind that this information might be lost during a normal project import/export. You can work around that by copying the database yourself.

[marius]

I don't think this is a good approach, since there will be different strategies for:

- primary keys,

- relations (especially for the many to many relations)

- special fields names (for example FS uses the VERSION field name, which some databases don't allow it - since it is a pre-reserved name);

- sequence Id strategy

[feddersen]

A clean approach would be to extend the reverse engineering code to check for the indices instead of foreign keys. I don't know what tool you're using, but Hibernate does allow that with a custom reverse engineering strategy. It shouldn't be much work if you're using the  DelegatingReverseEngineeringStrategy class. Implementing the methods that deal with foreign keys should be enough.

[marius]

Thanks for your answer.

I already extended/customized/patched the hibernate-tools which I use it for reverse engineering.

The biggest issue is related to the fact that there are no foreign keys defined between tables that have relations defined.

Even if I want to define/add the Foreign-Key at the database level by including the First Spirit field (<shortened table name>_FS_ID), this wouldn't work because in the primary key are used timestamps. Just imagine there is a PARENT(1) - (*)CHILDREN relation between the two tables, where the parent record has 3 children related records. Because of the timestamps (which are different for each record from the two tables) the foreign key will not work.

Also, there can not be added a foreign key based on the <shortened tablename>_FS_ID field because there is no matching unique or primary key for it (since the <shortened table name>_FS_ID could appear many times depending of the versioning).

On top of these issues, the filtering must support "release filtering" and "current state filtering".

...based on this your suggestion with DelegatingReverseEngineeringStrategy doesn't fit. Thanks anyway.

So, basically from this point of view the hibernate tool does not support First Spirit standards/style (there are many aspects).

In the end I had to do more customization to the hibernate-tool, including changed and new templates in order to support FS at DB level - for reverse engineering.

Marius

[feddersen]

My understanding was that you don't want to modify the DDL at all and use it to generate model classes only. So you wouldn't need to create foreign keys in the database, which isn't easy because of the temporal aspect of all tables. I don't know if it's possible in Oracle, it might be if you're able to use assertions in foreign keys. That's the primary reason why we switched from foreign keys to a index based approach. Most databases don't support that kind of complex foreign key relationships. Have a look at

So generating the correct mapping in your model classes should be possible if your reverse engineering uses defined indices to detect relationships between tables, or I'm missing something? Developing Time-Oriented Database Applications in SQL provides some insight on the rationale behind this approach. Chapter 2.1 and Chapter 5 describe the concept and the problems with primary/foreign keys.

Implementing the temporal aspect for CRUD operations would be a second task. You basically need to overload all queries to include the time constraints (fs_valid_from, fs_valid_to). Release filtering would use fs_valid_from and fs_release_to.

Marius Cimpean wrote:

In the end I had to do more customization to the hibernate-tool, including changed and new templates in order to support FS at DB level - for reverse engineering.

So do you have already have a working version? I'm curious to know which aspects of Hibernate need to be extended to get it working.