Skip to content

Add date column into vulnerability statistics table

What does this MR do?

The vulnerability_statistics table will store the current vulnerability statistics of a project as well as the historical ones. We are planning to store historical statistics on daily basis so we need a date column to have record per day.

Current statistics for a project will have epoch date as date. The reason for not using NULL column is that NULL values are not comparable which means NULL = NULL returns false therefore unique constraint does not work properly.

I am planning to use the UPSERT feature of PostgreSQL to create/update current statistics for projects on !35052 (merged) therefore the unique index introduced in the MR is important.

I've also updated the model accordingly.

Database migration output

Up

== 20200629074951 AddDateIntoVulnerabilityStatistics: migrating ===============
-- add_column(:vulnerability_statistics, :date, :date)
   -> 0.0009s
-- add_index(:vulnerability_statistics, :project_id, {:name=>"index_vulnerability_statistics_on_project_id_when_date_is_null", :unique=>true, :where=>"date IS NULL"})
   -> 0.0027s
-- add_index(:vulnerability_statistics, [:project_id, :date], {:name=>"index_vulnerability_statistics_on_project_id_and_date", :unique=>true, :where=>"date IS NOT NULL"})
   -> 0.0017s
== 20200629074951 AddDateIntoVulnerabilityStatistics: migrated (0.0054s) ======

Down

== 20200629074951 AddDateIntoVulnerabilityStatistics: reverting ===============
-- remove_index(:vulnerability_statistics, {:name=>"index_vulnerability_statistics_on_project_id_when_date_is_null"})
   -> 0.0010s
-- remove_index(:vulnerability_statistics, {:name=>"index_vulnerability_statistics_on_project_id_and_date"})
   -> 0.0002s
-- remove_column(:vulnerability_statistics, :date)
   -> 0.0003s
== 20200629074951 AddDateIntoVulnerabilityStatistics: reverted (0.0016s) ======

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Mehmet Emin INAC

Merge request reports