DELETE CASCADE on Foreign Keys

[hannosgit]

Current Behaviour

The current implementation to delete job instances or job executions issues a separate SQL statement for each metadata table. (see #4497)

Suggested Change

If we define the DELETE CASCADE option on each Foreign Key in the metadata table, we could remove all metadata by simply deleting the desired table row and the database will take care of the rest.
As far as I can see each supported database supports the DELETE CASCADE option

[mayleaf]

I made a new PR! #4584
PTAL when you have time 🙇

[hannosgit]

@mayleaf Are you sure that all databases support the DELETE CASCADE option? For example, I am not sure if Sybase does.

[mayleaf]

As far as I had found, there was ON DELETE option. But I confused ASE and IQ. As you said, It seems doesn't support ON DELETE on sybase. Then how about add delete trigger for those DBMSs that doesn't support delete cascade? @hannosgit @fmbenhassine

[hannosgit]

@mayleaf One option would be to create a new separate method in JobRepository that only deletes the job instance and lets the DB do the rest. Developers that use DBs that do not support ON DELETE CASCADE and developers that do not define the DELETE CASCADE can use the existing methods that delete the data on the Java side. All other developers can use the new method.

[mayleaf]

@hannosgit There is already a JobRepository.deleteJobInstance() method. I thought the positive effect of this PR was that it simplifies the SimpleJobRepository implementation, not changes the JobRepository interface. There is no need to create a duplicated signature. If I misunderstood your opinion, please let me know 🙇

[hannosgit]

Yeah that was the idea but if we cannot come up with a solution for these DBs, we have to live with some kind of workaround such as the one I suggested. I have no experience/access with e.g. Sybase, so I cannot help with this. I found this workaround of using triggers to simulate ON DELETE CASCADE but I cannot verify it.

[mayleaf]

https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc32300_1251/html/sqlug/X15877.htm
@fmbenhassine
Hi @hannosgit ! Thanks to your comment, I reviewed trigger methods, and tested them. The trigger method is an option referred in official document. Also, it works!
For your information, I tested it with "datagrip/sybase:16.0" docker image, however it has had a problem.
That issue can be solved by following solutions in the following link

[mayleaf]

Hi @fmbenhassine @hannosgit. I've submitted an update for it.
PTAL when you have a moment 🙇 I'd appreciate any feedback.

Thanks!