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
-
Changelog entry - [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
- [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
- [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
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