diff options
Diffstat (limited to 'db/migrate/20220106112085_add_update_vulnerability_reads_location_trigger.rb')
-rw-r--r-- | db/migrate/20220106112085_add_update_vulnerability_reads_location_trigger.rb | 41 |
1 files changed, 41 insertions, 0 deletions
diff --git a/db/migrate/20220106112085_add_update_vulnerability_reads_location_trigger.rb b/db/migrate/20220106112085_add_update_vulnerability_reads_location_trigger.rb new file mode 100644 index 00000000000..a863fe8b7b8 --- /dev/null +++ b/db/migrate/20220106112085_add_update_vulnerability_reads_location_trigger.rb @@ -0,0 +1,41 @@ +# frozen_string_literal: true + +class AddUpdateVulnerabilityReadsLocationTrigger < Gitlab::Database::Migration[1.0] + include Gitlab::Database::SchemaHelpers + + TRIGGER_NAME = 'trigger_update_location_on_vulnerability_occurrences_update' + FUNCTION_NAME = 'update_location_from_vulnerability_occurrences' + + def up + create_trigger_function(FUNCTION_NAME, replace: true) do + <<~SQL + UPDATE + vulnerability_reads + SET + location_image = NEW.location->>'image', + cluster_agent_id = NEW.location->'kubernetes_resource'->>'agent_id' + WHERE + vulnerability_id = NEW.vulnerability_id; + RETURN NULL; + SQL + end + + execute(<<~SQL) + CREATE TRIGGER #{TRIGGER_NAME} + AFTER UPDATE ON vulnerability_occurrences + FOR EACH ROW + WHEN ( + NEW.report_type IN (2, 7) AND ( + OLD.location->>'image' IS DISTINCT FROM NEW.location->>'image' OR + OLD.location->'kubernetes_resource'->>'agent_id' IS DISTINCT FROM NEW.location->'kubernetes_resource'->>'agent_id' + ) + ) + EXECUTE PROCEDURE #{FUNCTION_NAME}(); + SQL + end + + def down + drop_trigger(:vulnerability_occurrences, TRIGGER_NAME) + drop_function(FUNCTION_NAME) + end +end |