Fix for slow deletion of harvest resources in MySQL/MariaDB

[hannakoppelaar]

In MySQL the Hibernate-generated statements for deleting records from harvest_resource and arc_harvest_resource lead to long running queries which might time-out in the case of large harvests. The fix is to use a simple ANSI-SQL native query and to add a cascaded delete to the schema. A fix without any alteration to the schema seems to be impossible, if the code is to run on all supported database platforms.

[obrienben]

@hannakoppelaar are the code and db changes coupled together? Can I deploy the code without the db schema update or will that cause problems?

[hannakoppelaar]

@hannakoppelaar are the code and db changes coupled together? Can I deploy the code without the db schema update or will that cause problems?

@obrienben I don't think it will work because ARC_HARVEST_RESOURCE will no longer be updated when HARVEST_RESOURCE is updated, which will cause the database to complain about referential integrity.

[leefrank9527]

Tested with Mysql8. It cost 115 seconds to delete 1M Harvest Resources rows. It's fine from my opnion. I'll test it with PostgreSQL and Oracle.

[leefrank9527]

Tested with PostgreSQL13. It cost 113 seconds to delete 1M Harvest Resources rows. It's fine from my opnion.