Support polymorphic association with LooseForeignKeys, async delete notes when vulnerabilities are deleted using it.
The following discussion from !69165 (merged) should be addressed:
-
@ayufan started a discussion: (+2 comments) Interesting. I think that answer is negative but worth asking: this does not support polymorphic foreign keys.
Changes required
DB Changes
No changes required to the loose_foreign_keys_deleted_records table
LFK definitions Changes
We'll extend the LFK definition to allow adding conditions for the DELETE/UPDATE queries.
This will allow a more generic solution that can be used not only for cases of polymorphic association.
In the case of a polymorphic association, the association type column and the required value are added to the condition.
The following is an example in the case where vulnerabilities is the parent table and notes is the child table
notes:
- table: vulnerabilities
column: noteable_id
conditions:
- column: noteable_type
value: "Vulnerability"
on_delete: async_delete
LFK Cleanup Worker changes
Add support to create a query with added 'WHERE` conditions based on the condition in the LFK definitions The query would be like this:
DELETE FROM notes
WHERE
noteable_id IN (SELECT id FROM vulnerabilities WHERE id IN (...))
AND noteable_type = "Vulnerability"
Future development
Add support to the generate-loose-foreign-key script to detect polymorphic association when updating config/gitlab_loose_foreign_keys.yml and a condition when adding the new loose foreign key