Add cluster_agent_id to vulnerability_reads
What does this MR do and why?
Part of #335326 (closed)
!76640 (merged) introduced a conditional index on agent_id
in location
json column in vulnerability_occurrences
. Ad a part of epic &6903 (closed), we are introducing vulnerability_reads
table to store the ids for faster lookup of vulnerabilities.
!74733 (merged) added the vulnerability_reads
table. This MR adds the cluster_agent_id
column which is used to filter vulnerabilities(!76342 (merged))
The type of the column is string
and not int
because currently the agent_id
is stored as string in location
column in vulnerability_occurrences
table.
cluster_agent_id
will only be present for vulnerabilities with report_type cluster_image_scanning
(7
) and hence we are adding a condition for the index.
Migrate Up
== 20211216133107 AddClusterAgentIdToVulnerabilityReads: migrating ============
-- add_column(:vulnerability_reads, :cluster_agent_id, :text)
-> 0.0023s
== 20211216133107 AddClusterAgentIdToVulnerabilityReads: migrated (0.0024s) ===
== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: migrating ===
-- transaction_open?()
-> 0.0000s
-- current_schema()
-> 0.0002s
-- transaction_open?()
-> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT check_a105eb825a\nCHECK ( char_length(cluster_agent_id) <= 10 )\nNOT VALID;\n")
-> 0.0010s
-- current_schema()
-> 0.0001s
-- execute("SET statement_timeout TO 0")
-> 0.0004s
-- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT check_a105eb825a;")
-> 0.0007s
-- execute("RESET statement_timeout")
-> 0.0004s
== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: migrated (0.0136s)
== 20211216135651 AddIndexToClusterAgentId: migrating =========================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:vulnerability_reads, :cluster_agent_id, {:where=>"report_type = 7", :name=>"index_vulnerability_reads_on_cluster_agent_id", :algorithm=>:concurrently})
-> 0.0041s
-- add_index(:vulnerability_reads, :cluster_agent_id, {:where=>"report_type = 7", :name=>"index_vulnerability_reads_on_cluster_agent_id", :algorithm=>:concurrently})
-> 0.0027s
== 20211216135651 AddIndexToClusterAgentId: migrated (0.0094s) ================
Migrate Down
== 20211216135651 AddIndexToClusterAgentId: reverting =========================
-- transaction_open?()
-> 0.0000s
-- indexes(:vulnerability_reads)
-> 0.0035s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vulnerability_reads_on_cluster_agent_id"})
-> 0.0041s
-- execute("RESET statement_timeout")
-> 0.0005s
== 20211216135651 AddIndexToClusterAgentId: reverted (0.0152s) ================
== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: reverting ===
-- transaction_open?()
-> 0.0000s
-- transaction_open?()
-> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nDROP CONSTRAINT IF EXISTS check_a105eb825a\n")
-> 0.0013s
== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: reverted (0.0130s)
== 20211216133107 AddClusterAgentIdToVulnerabilityReads: reverting ============
-- remove_column(:vulnerability_reads, :cluster_agent_id, :text)
-> 0.0037s
== 20211216133107 AddClusterAgentIdToVulnerabilityReads: reverted (0.0081s) ===
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.