Questions & Answers

[ndegoeij]

We are running FirstSpirit 4.2.489 with an Oracle database. Within FirstSpirit we created a database scheme. Now I have made a connection to the Oracle database with SQL Developer and I have reverse engineered a diagram of the database. I see all the tables as created in FirstSpirit, however I do not see any foreign key (FK) relations between those tables.

The question is where and how does FirstSpirit stores its FK's and how can I see them in SQL Developer?

[MichaelaReydt]

Hello,

is this still an open question or did you already found a solution by yourself? If so it would be very kind of you, if you posted it here.

Else maybe this blogposting helps you: click

Best regards

Michaela

[ndegoeij]

Hi Michaela,

Sorry, but there is no answer yet. If I would have an answer I would post it here of course.

The question still stands. Where does FirstSpirit stores it's foreign key relation. When I look in the database no foreign key has been defined and every table looks as if it were a single non related table. However when I look in FirstSpirit itself it does "see" the relations. Therefore it looks almost as if the relations are stored as some sort of metadata somewhere else (which, if this is true, kind of gives me the creeps since there would be no restriction at all if another system also connects to the same database for example).

Best regards,

Nando

[Peter_Jodeleit]

Tables created by FirstSpirit are "temporal", hence foreign key constraints wouldn't be valid.

[ndegoeij]

The tables in the Oracle database are as far as I can see, certainly not temporal. These are tables in which user input is stored. The way I see it is as follows:

Within FirstSpirit we can create a datamodel in the template store -> database scheme section. Here we can add tables and define relations between those tables. These foreign keys are not defines in Oracle in the way they should be defined.

I would like to know where FirstSpirit remembers which foreign key is defined for which table relation and how to retrieve that informaiton, and why is it not defined like it should in Oracle?

[Peter_Jodeleit]

The tables in the Oracle database are as far as I can see, certainly not temporal. These are tables in which user input is stored. The way I see it is as follows:

They do not contain columns like "valid_from", "valid_to", and "relase_to"?

I would like to know where FirstSpirit remembers which foreign key is defined for which table relation and how to retrieve that informaiton, and why is it not defined like it should in Oracle?

These are part of the data in the corresponding schema node. It's not defined like you expect because this is not possible for the way FirstSpirit stores the data.

[ndegoeij]

Hi Peter,

I understand what you are saying. Basically it is stored as metadata in Firstspirit itself. Although I understand the reason (probably because eSpirit wants to offer only generic functionality which can be used for every database the same way) I am not really happy with the solution.

This means that I could have another system integrated in our IT landscape which wants to connect to the database directly and insert data without having any garantuee the database will check for integrity.

So I have another question. If I would want to redesign the database in order to have foreign keys defined in the database itself and have FirstSpirit to work with a fully designed Oracle database, what everything do I have to do in order to make this work?

I can think about a few things myself; redesign Oracle database, define most of the (or maybe even all of the) special FirstSpirit columns per table, etc. And i guess rewrite an Oracle connector which would implement all the functionality. I there a manual/tutorial which explains how to set this up and make it work?

Thanks in advance.

Best regards,

Nando

[Peter_Jodeleit]

You already can integrate "external" Databases in FirstSpirit. But this has some drawbacks (you cannot use the schema editor, the content is not temporal, is read-only, and updating of the search index has to be done manually).