Skip to content

Add and fill foreign key for approval rules

Zamir Martins requested to merge add_and_fill_foreign_key_for_approval_rules into master

What does this MR do and why?

Add and fill foreign key for approval rules for both project and merge request approval rules. Propagating from policy into approval project rules then into approval merge request rules.

This relationship will be used in a follow up MR.

Related issue: #367713 (closed)

Database

Query plan for db/migrate/20220907115806_add_security_orchestration_policy_configuration_id.rb

Raw 
ALTER TABLE approval_merge_request_rules add column security_orchestration_policy_configuration_id BIGINT;

ALTER TABLE approval_project_rules add column security_orchestration_policy_configuration_id BIGINT;

CREATE INDEX index_approval_merge_request_rules_on_security_orchestration_p ON approval_merge_request_rules USING btree (security_orchestration_policy_configuration_id);

CREATE INDEX index_approval_project_rules_on_security_orchestration_policy ON approval_project_rules USING btree (security_orchestration_policy_configuration_id);

ALTER TABLE ONLY approval_merge_request_rules ADD CONSTRAINT fk_5822f009ea FOREIGN KEY (security_orchestration_policy_configuration_id) REFERENCES security_orchestration_policy_configurations(id) ON DELETE CASCADE;

ALTER TABLE ONLY approval_project_rules ADD CONSTRAINT fk_efa5a1e3fb FOREIGN KEY (security_orchestration_policy_configuration_id) REFERENCES security_orchestration_policy_configurations(id) ON DELETE CASCADE;
Migrate/Rollback 
$ bundle exec rails db:migrate:down:main VERSION=20220907115806
main: == 20220907115806 AddSecurityOrchestrationPolicyConfigurationId: reverting ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- column_exists?(:approval_project_rules, :security_orchestration_policy_configuration_id)
main:    -> 0.0063s
main: -- remove_column(:approval_project_rules, :security_orchestration_policy_configuration_id)
main:    -> 0.0015s
main: -- column_exists?(:approval_merge_request_rules, :security_orchestration_policy_configuration_id)
main:    -> 0.0023s
main: -- remove_column(:approval_merge_request_rules, :security_orchestration_policy_configuration_id)
main:    -> 0.0010s
main: -- foreign_keys(:approval_project_rules)
main:    -> 0.0038s
main: -- foreign_keys(:approval_merge_request_rules)
main:    -> 0.0022s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:approval_project_rules)
main:    -> 0.0079s
main: -- current_schema()
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:approval_merge_request_rules)
main:    -> 0.0048s
main: -- current_schema()
main:    -> 0.0003s
main: == 20220907115806 AddSecurityOrchestrationPolicyConfigurationId: reverted (0.0527s)

$ bundle exec rails db:migrate:up:main VERSION=20220907115806
main: == 20220907115806 AddSecurityOrchestrationPolicyConfigurationId: migrating ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- column_exists?(:approval_project_rules, :security_orchestration_policy_configuration_id)
main:    -> 0.0061s
main: -- add_column(:approval_project_rules, :security_orchestration_policy_configuration_id, :bigint)
main:    -> 0.0011s
main: -- column_exists?(:approval_merge_request_rules, :security_orchestration_policy_configuration_id)
main:    -> 0.0020s
main: -- add_column(:approval_merge_request_rules, :security_orchestration_policy_configuration_id, :bigint)
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:approval_project_rules)
main:    -> 0.0047s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE approval_project_rules\nADD CONSTRAINT fk_efa5a1e3fb\nFOREIGN KEY (security_orchestration_policy_configuration_id)\nREFERENCES security_orchestration_policy_configurations (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0019s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE approval_project_rules VALIDATE CONSTRAINT fk_efa5a1e3fb;")
main:    -> 0.0061s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:approval_merge_request_rules)
main:    -> 0.0036s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE approval_merge_request_rules\nADD CONSTRAINT fk_5822f009ea\nFOREIGN KEY (security_orchestration_policy_configuration_id)\nREFERENCES security_orchestration_policy_configurations (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0010s
main: -- execute("ALTER TABLE approval_merge_request_rules VALIDATE CONSTRAINT fk_5822f009ea;")
main:    -> 0.0043s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:approval_project_rules, :security_orchestration_policy_configuration_id, {:name=>"idx_approval_project_rules_on_sec_orchestration_config_id", :algorithm=>:concurrently})
main:    -> 0.0068s
main: -- add_index(:approval_project_rules, :security_orchestration_policy_configuration_id, {:name=>"idx_approval_project_rules_on_sec_orchestration_config_id", :algorithm=>:concurrently})
main:    -> 0.0029s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:approval_merge_request_rules, :security_orchestration_policy_configuration_id, {:name=>"idx_approval_merge_request_rules_on_sec_orchestration_config_id", :algorithm=>:concurrently})
main:    -> 0.0057s
main: -- add_index(:approval_merge_request_rules, :security_orchestration_policy_configuration_id, {:name=>"idx_approval_merge_request_rules_on_sec_orchestration_config_id", :algorithm=>:concurrently})
main:    -> 0.0021s
main: == 20220907115806 AddSecurityOrchestrationPolicyConfigurationId: migrated (0.0780s)

Query plan for db/post_migrate/20220907122648_populate_security_orchestration_policy_configuration_id.rb

Raw 
UPDATE approval_merge_request_rules SET security_orchestration_policy_configuration_id = ids.security_orchestration_policy_configuration_id FROM (SELECT approval_merge_request_rules.id AS approval_merge_request_rules_id, security_orchestration_policy_configurations.id AS security_orchestration_policy_configuration_id FROM approval_merge_request_rules INNER JOIN approval_merge_request_rule_sources ON approval_merge_request_rule_sources.approval_merge_request_rule_id = approval_merge_request_rules.id INNER JOIN approval_project_rules ON approval_merge_request_rule_sources.approval_project_rule_id = approval_project_rules.id INNER JOIN security_orchestration_policy_configurations ON approval_project_rules.project_id = security_orchestration_policy_configurations.project_id WHERE approval_merge_request_rules.report_type = 4 AND approval_merge_request_rules.id BETWEEN <START_ID> AND <STOP_ID>) AS ids WHERE approval_merge_request_rules.id = ids.approval_merge_request_rules_id;

UPDATE approval_project_rules SET security_orchestration_policy_configuration_id = ids.security_orchestration_policy_configurations_id FROM (SELECT approval_project_rules.id AS approval_project_rules_id, security_orchestration_policy_configurations.id AS security_orchestration_policy_configurations_id FROM approval_project_rules INNER JOIN security_orchestration_policy_configurations ON security_orchestration_policy_configurations.project_id = approval_project_rules.project_id WHERE approval_project_rules.report_type = 4 AND approval_project_rules.id BETWEEN <START_ID> AND <STOP_ID>) AS ids WHERE approval_project_rules.id = ids.approval_project_rules_id;
Migrate/Rollback 
$ bundle exec rails db:migrate:down:main VERSION=20220907122648
main: == 20220907122648 PopulateSecurityOrchestrationPolicyConfigurationId: reverting
main: == 20220907122648 PopulateSecurityOrchestrationPolicyConfigurationId: reverted (0.0612s)

$ bundle exec rails db:migrate:up:main VERSION=20220907122648
main: == 20220907122648 PopulateSecurityOrchestrationPolicyConfigurationId: migrating
main: == 20220907122648 PopulateSecurityOrchestrationPolicyConfigurationId: migrated (0.1731s)
Performance

MERGE REQUEST RULES
1000 batch size
Time per batch: 102.819 ms
Total existing records: 18294 records
18294 / 1000 = ~19 times
19 * 102.819 ms / 1000 = 1.953561 seconds (total time)

PROJECT RULES
1000 batch size
Time per batch: 148.003 ms
Total existing records: 3394 records
3394 / 1000 = ~4 times
4 * 148.003 ms / 1000 = 0.592012 seconds (total time)

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 Zamir Martins

Merge request reports