diff options
Diffstat (limited to 'db/migrate/20220106111958_add_insert_or_update_vulnerability_reads_trigger.rb')
-rw-r--r-- | db/migrate/20220106111958_add_insert_or_update_vulnerability_reads_trigger.rb | 58 |
1 files changed, 58 insertions, 0 deletions
diff --git a/db/migrate/20220106111958_add_insert_or_update_vulnerability_reads_trigger.rb b/db/migrate/20220106111958_add_insert_or_update_vulnerability_reads_trigger.rb new file mode 100644 index 00000000000..0049f4e00a2 --- /dev/null +++ b/db/migrate/20220106111958_add_insert_or_update_vulnerability_reads_trigger.rb @@ -0,0 +1,58 @@ +# frozen_string_literal: true + +class AddInsertOrUpdateVulnerabilityReadsTrigger < Gitlab::Database::Migration[1.0] + include Gitlab::Database::SchemaHelpers + + FUNCTION_NAME = 'insert_or_update_vulnerability_reads' + TRIGGER_NAME = 'trigger_insert_or_update_vulnerability_reads_from_occurrences' + + def up + execute(<<~SQL) + CREATE OR REPLACE FUNCTION #{FUNCTION_NAME}() + RETURNS TRIGGER + LANGUAGE plpgsql + AS $$ + DECLARE + severity smallint; + state smallint; + report_type smallint; + resolved_on_default_branch boolean; + BEGIN + IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN + RETURN NULL; + END IF; + + IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN + RETURN NULL; + END IF; + + SELECT + vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch + INTO + severity, state, report_type, resolved_on_default_branch + FROM + vulnerabilities + WHERE + vulnerabilities.id = NEW.vulnerability_id; + + INSERT INTO vulnerability_reads (vulnerability_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id) + VALUES (NEW.vulnerability_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id') + ON CONFLICT(vulnerability_id) DO NOTHING; + RETURN NULL; + END + $$; + SQL + + execute(<<~SQL) + CREATE TRIGGER #{TRIGGER_NAME} + AFTER INSERT OR UPDATE ON vulnerability_occurrences + FOR EACH ROW + EXECUTE PROCEDURE #{FUNCTION_NAME}(); + SQL + end + + def down + drop_trigger(:vulnerability_occurrences, TRIGGER_NAME) + drop_function(FUNCTION_NAME) + end +end |