Lack or foreign key use in databases could lead to orphan data

[Robin-Van-de-Merghel]

Of all 6 SQL databases:

sandbox_metadata, pilot_agents aren't using foreign keys at all. This could lead to orphan data in the database for two reasons:

1. if a pilot is deleted in the PilotAgents table, all pilot related data like in JobToPilotMapping will not be deleted
2. we could have an unknown pilot_id in PilotOutput because we inserted a non-existent one (so we are obliged to verify that the new pilot_id exists in the database, manually, instead of letting the db verify for us and doing its magic) or because it does not exist anymore (see 1.)

I don't know if this is a choice, or a mistake. If it is a choice, task_queue and job do use foreign keys. And furthermore, even if we check manually, I think it is better to have constraints inside the DB itself, rather than constraints done before.

In the meanwhile, job_logging and auth can't use it, because the client_id is remotely defined in IAM if I recall.

[chaen]

What you can do is to compare with their respective implementations in DIRAC.
As we cannot change the schema for now, if the foreign key is not defined in DIRAC, then it is normal. If it is in DIRAC and not in diracx, then it is definitely a mistake.

[Robin-Van-de-Merghel]

Yeah I compared with Dirac DBs, and there is no foreign keys.

But changing it in Dirac itself (adding foreign keys) would destroy Dirac?

[chaen]

Who knows.... :-) Historically, no DB had foreign keys, because they used MyISAM. When the schema was migrated to InnoDB, foreign keys were introduced, but seemingly not everywhere.
Let me be clear: there definitely should be foreign keys, but it may be trickier to add them now in DIRAC, than to do it in diracx.
At the same time, v9 already contains a lot of schema migration, so if we have to do some more, now is a good time. It's maybe worth checking indeed if:

• the existing data in the DB is consistent
• the code expects the data to be inconsistent for a short period of time

[Robin-Van-de-Merghel]

So if we wanted to use foreign keys in V9, I should open an issue in Dirac itself?

And there is still an issue with another db (job logger if I recall) that is alone, far from his big brother (jobDb). This is quite painful to assure DB logic on diracX (we have to do it manually, and in the db itself we would have to use db triggers). But i guess that implies more changes in Dirac.

[chaen]

So if we wanted to use foreign keys in V9, I should open an issue in Dirac itself?

Yes, but that could be a bit far from your scope. @fstagni do you mind helping him here ?

[fstagni]

There are indeed several issues with these tables, not only the missing foreign keys (e.g. I see one table missing a primary key...). As Christophe points out, historic issues!

The DIRAC code might be making assumptions on these schemas. It is true that "now it is the time" for schema changes, let me see if these are not too complex and/or difficult.

[fstagni]

I started something in DIRACGrid/DIRAC#8097
If the tests do not fail, we can think of including it (then DiracX will need to be updated accordingly).