search

spring-projects / spring-batch

Spring Batch is a framework for writing batch applications using Java and Spring
http://projects.spring.io/spring-batch/
Apache License 2.0
2.73k stars 2.35k forks source link

DELETE CASCADE on Foreign Keys #4555

Open hannosgit opened 8 months ago

hannosgit commented 8 months ago

Current Behaviour

The current implementation to delete job instances or job executions issues a separate SQL statement for each metadata table. (see https://github.com/spring-projects/spring-batch/pull/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 commented 6 months ago

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

hannosgit commented 6 months ago

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

mayleaf commented 6 months ago

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 commented 6 months ago

@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 commented 6 months ago

@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 commented 5 months ago

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 commented 2 weeks ago

Screenshot 2024-10-20 at 8 22 42 PM 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 commented 2 days ago

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

Thanks!