Skip to content

Create vulnerability read model

Sashi Kumar Kumaresan requested to merge sk/335326-create-vuln-reads into master

What does this MR do and why?

Addresses #335326 (closed)

This MR creates a new read table vulnerability_reads to increase the performance of vulnerabilities filtering.

Database Changes

Migration

Migrate:

$ bundle exec rake db:migrate
== 20211117174209 CreateVulnerabilityReads: migrating =========================
-- create_table(:vulnerability_reads, {})
-- quote_column_name(:location_image)
   -> 0.0000s
   -> 0.0342s
== 20211117174209 CreateVulnerabilityReads: migrated (0.0342s) ================

== 20211118124537 AddForeignKeyToVulnerabilityReadsOnVulnerability: migrating =
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:vulnerability_reads)
   -> 0.0042s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT fk_62736f638f\nFOREIGN KEY (vulnerability_id)\nREFERENCES vulnerabilities (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0064s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT fk_62736f638f;")
   -> 0.0146s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20211118124537 AddForeignKeyToVulnerabilityReadsOnVulnerability: migrated (0.0478s) 

== 20211118124628 AddForeignKeyToVulnerabilityReadsOnProject: migrating =======
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:vulnerability_reads)
   -> 0.0024s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT fk_5001652292\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0028s
-- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT fk_5001652292;")
   -> 0.0067s
== 20211118124628 AddForeignKeyToVulnerabilityReadsOnProject: migrated (0.0160s) 

== 20211118124650 AddForeignKeyToVulnerabilityReadsOnScanner: migrating =======
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:vulnerability_reads)
   -> 0.0025s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT fk_b28c28abf1\nFOREIGN KEY (scanner_id)\nREFERENCES vulnerability_scanners (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0017s
-- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT fk_b28c28abf1;")
   -> 0.0039s
== 20211118124650 AddForeignKeyToVulnerabilityReadsOnScanner: migrated (0.0120s)

Rollback:

$ bundle exec rake db:rollback STEP=4
== 20211118124650 AddForeignKeyToVulnerabilityReadsOnScanner: reverting =======
-- transaction_open?()
   -> 0.0000s
-- remove_foreign_key(:vulnerability_reads, {:column=>:scanner_id})
   -> 0.0048s
== 20211118124650 AddForeignKeyToVulnerabilityReadsOnScanner: reverted (0.0258s) 

== 20211118124628 AddForeignKeyToVulnerabilityReadsOnProject: reverting =======
-- transaction_open?()
   -> 0.0000s
-- remove_foreign_key(:vulnerability_reads, {:column=>:project_id})
   -> 0.0031s
== 20211118124628 AddForeignKeyToVulnerabilityReadsOnProject: reverted (0.0061s) 

== 20211118124537 AddForeignKeyToVulnerabilityReadsOnVulnerability: reverting =
-- transaction_open?()
   -> 0.0000s
-- remove_foreign_key(:vulnerability_reads, {:column=>:vulnerability_id})
   -> 0.0033s
== 20211118124537 AddForeignKeyToVulnerabilityReadsOnVulnerability: reverted (0.0067s) 

== 20211117174209 CreateVulnerabilityReads: reverting =========================
-- drop_table(:vulnerability_reads, {})
   -> 0.0047s
== 20211117174209 CreateVulnerabilityReads: reverted (0.0070s) ================
Table Structure 
Table "public.vulnerability_reads"
           Column           |   Type   | Collation | Nullable |                     Default                     
----------------------------+----------+-----------+----------+-------------------------------------------------
 id                         | bigint   |           | not null | nextval('vulnerability_reads_id_seq'::regclass)
 vulnerability_id           | bigint   |           | not null | 
 project_id                 | bigint   |           | not null | 
 scanner_id                 | bigint   |           | not null | 
 report_type                | smallint |           | not null | 
 severity                   | smallint |           | not null | 
 state                      | smallint |           | not null | 
 has_issues                 | boolean  |           | not null | false
 resolved_on_default_branch | boolean  |           | not null | false
 uuid                       | uuid     |           | not null | 
 location_image             | text     |           |          | 
Indexes:
    "vulnerability_reads_pkey" PRIMARY KEY, btree (id)
    "index_vulnerability_reads_on_vulnerability_id" UNIQUE, btree (vulnerability_id)
    "index_vuln_reads_on_project_id_state_severity_and_vuln_id" btree (project_id, state, severity, vulnerability_id DESC)
    "index_vulnerability_reads_on_location_image" btree (location_image) WHERE report_type = ANY (ARRAY[2, 7])
    "index_vulnerability_reads_on_scanner_id" btree (scanner_id)
Check constraints:
    "check_380451bdbe" CHECK (char_length(location_image) <= 255)
Foreign-key constraints:
    "fk_5001652292" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_62736f638f" FOREIGN KEY (vulnerability_id) REFERENCES vulnerabilities(id) ON DELETE CASCADE
    "fk_b28c28abf1" FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE

Migration queries analysis: console.postgres.ai

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Sashi Kumar Kumaresan

Merge request reports