Rename services table to integrations (!64562) · Merge requests · GitLab.org / GitLab

Luke Duncalfe requested to merge 201856-rename-services-table into master Jun 21, 2021

What does this MR do?

This MR completes the renaming of the services table to integrations as the second part of the process involved in renaming a table safely after !62113 (merged) marked the table "to be renamed".

The old services table has some triggers that apply to it, migrated in:

This MR copies those triggers by dropping and recreating them.

Migration output

Up

== 20210621043337 RenameServicesToIntegrations: migrating =====================
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_wiki_on_insert ON services")
   -> 0.0033s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_wiki_on_update ON services")
   -> 0.0010s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_wiki_on_delete ON services")
   -> 0.0008s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_insert ON services")
   -> 0.0009s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_update ON services")
   -> 0.0008s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_delete ON services")
   -> 0.0008s
-- rename_table(:services, :integrations)
   -> 0.0280s
-- execute("CREATE VIEW services AS SELECT * FROM integrations")
   -> 0.0075s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_insert\nAFTER INSERT ON integrations\nFOR EACH ROW\nWHEN (NEW.active = TRUE AND NEW.type = 'ExternalWikiService' AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0023s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_update\nAFTER UPDATE ON integrations\nFOR EACH ROW\nWHEN (NEW.type = 'ExternalWikiService' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0012s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_delete\nAFTER DELETE ON integrations\nFOR EACH ROW\nWHEN (OLD.type = 'ExternalWikiService' AND OLD.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0014s
-- execute("CREATE OR REPLACE FUNCTION set_has_external_issue_tracker()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE projects SET has_external_issue_tracker = (\n  EXISTS\n  (\n    SELECT 1\n    FROM integrations\n    WHERE project_id = COALESCE(NEW.project_id, OLD.project_id)\n      AND active = TRUE\n      AND category = 'issue_tracker'\n  )\n)\nWHERE projects.id = COALESCE(NEW.project_id, OLD.project_id);\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0034s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_insert\nAFTER INSERT ON integrations\nFOR EACH ROW\nWHEN (NEW.category = 'issue_tracker' AND NEW.active = TRUE AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0013s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_update\nAFTER UPDATE ON integrations\nFOR EACH ROW\nWHEN (NEW.category = 'issue_tracker' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0015s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_delete\nAFTER DELETE ON integrations\nFOR EACH ROW\nWHEN (OLD.category = 'issue_tracker' AND OLD.active = TRUE AND OLD.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0024s
== 20210621043337 RenameServicesToIntegrations: migrated (0.0672s) ============

== 20210621044000 RenameServicesIndexesToIntegrations: migrating ==============
-- execute("ALTER INDEX IF EXISTS \"index_services_on_project_and_type_where_inherit_null\" RENAME TO \"index_integrations_on_project_and_type_where_inherit_null\"\n")
   -> 0.0029s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_project_id_and_type_unique\" RENAME TO \"index_integrations_on_project_id_and_type_unique\"\n")
   -> 0.0012s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_template\" RENAME TO \"index_integrations_on_template\"\n")
   -> 0.0012s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_type\" RENAME TO \"index_integrations_on_type\"\n")
   -> 0.0013s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_type_and_instance_partial\" RENAME TO \"index_integrations_on_type_and_instance_partial\"\n")
   -> 0.0017s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_type_and_template_partial\" RENAME TO \"index_integrations_on_type_and_template_partial\"\n")
   -> 0.0025s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_type_id_when_active_and_project_id_not_null\" RENAME TO \"index_integrations_on_type_id_when_active_and_project_id_not_null\"\n")
   -> 0.0033s
-- execute("ALTER INDEX IF EXISTS \"index_services_on_unique_group_id_and_type\" RENAME TO \"index_integrations_on_unique_group_id_and_type\"\n")
   -> 0.0020s
== 20210621044000 RenameServicesIndexesToIntegrations: migrated (0.0168s) =====

== 20210621223000 StealBackgroundJobsThatReferenceServices: migrating =========
== 20210621223000 StealBackgroundJobsThatReferenceServices: migrated (0.0087s)

== 20210621223242 FinalizeRenameServicesToIntegrations: migrating =============
-- execute("DROP VIEW IF EXISTS services")
   -> 0.0018s
== 20210621223242 FinalizeRenameServicesToIntegrations: migrated (0.0079s) ====

Down

bundle exec rake db:migrate:down VERSION=20210621223242

== 20210621223242 FinalizeRenameServicesToIntegrations: reverting =============
-- execute("CREATE VIEW services AS SELECT * FROM integrations")
   -> 0.0266s
== 20210621223242 FinalizeRenameServicesToIntegrations: reverted (0.0388s) ====

bundle exec rake db:migrate:down VERSION=20210621223000

(Note, this is a no-op).

== 20210621223000 StealBackgroundJobsThatReferenceServices: reverting =========
== 20210621223000 StealBackgroundJobsThatReferenceServices: reverted (0.0000s)

bundle exec rake db:migrate:down VERSION=20210621044000

== 20210621044000 RenameServicesIndexesToIntegrations: reverting ==============
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_project_and_type_where_inherit_null\" RENAME TO \"index_services_on_project_and_type_where_inherit_null\"\n")
   -> 0.0047s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_project_id_and_type_unique\" RENAME TO \"index_services_on_project_id_and_type_unique\"\n")
   -> 0.0021s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_template\" RENAME TO \"index_services_on_template\"\n")
   -> 0.0025s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_type\" RENAME TO \"index_services_on_type\"\n")
   -> 0.0024s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_type_and_instance_partial\" RENAME TO \"index_services_on_type_and_instance_partial\"\n")
   -> 0.0029s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_type_and_template_partial\" RENAME TO \"index_services_on_type_and_template_partial\"\n")
   -> 0.0018s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_type_id_when_active_and_project_id_not_null\" RENAME TO \"index_services_on_type_id_when_active_and_project_id_not_null\"\n")
   -> 0.0012s
-- execute("ALTER INDEX IF EXISTS \"index_integrations_on_unique_group_id_and_type\" RENAME TO \"index_services_on_unique_group_id_and_type\"\n")
   -> 0.0013s
== 20210621044000 RenameServicesIndexesToIntegrations: reverted (0.0200s) =====

bundle exec rake db:migrate:down VERSION=20210621043337

== 20210621043337 RenameServicesToIntegrations: reverting =====================
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_wiki_on_insert ON integrations")
   -> 0.0028s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_wiki_on_update ON integrations")
   -> 0.0011s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_wiki_on_delete ON integrations")
   -> 0.0010s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_insert ON integrations")
   -> 0.0009s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_update ON integrations")
   -> 0.0013s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_delete ON integrations")
   -> 0.0011s
-- execute("DROP VIEW IF EXISTS services")
   -> 0.0023s
-- rename_table(:integrations, :services)
   -> 0.0371s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_insert\nAFTER INSERT ON services\nFOR EACH ROW\nWHEN (NEW.active = TRUE AND NEW.type = 'ExternalWikiService' AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0024s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_update\nAFTER UPDATE ON services\nFOR EACH ROW\nWHEN (NEW.type = 'ExternalWikiService' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0028s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_delete\nAFTER DELETE ON services\nFOR EACH ROW\nWHEN (OLD.type = 'ExternalWikiService' AND OLD.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0019s
-- execute("CREATE OR REPLACE FUNCTION set_has_external_issue_tracker()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE projects SET has_external_issue_tracker = (\n  EXISTS\n  (\n    SELECT 1\n    FROM services\n    WHERE project_id = COALESCE(NEW.project_id, OLD.project_id)\n      AND active = TRUE\n      AND category = 'issue_tracker'\n  )\n)\nWHERE projects.id = COALESCE(NEW.project_id, OLD.project_id);\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0054s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_insert\nAFTER INSERT ON services\nFOR EACH ROW\nWHEN (NEW.category = 'issue_tracker' AND NEW.active = TRUE AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0016s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_update\nAFTER UPDATE ON services\nFOR EACH ROW\nWHEN (NEW.category = 'issue_tracker' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0015s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_delete\nAFTER DELETE ON services\nFOR EACH ROW\nWHEN (OLD.category = 'issue_tracker' AND OLD.active = TRUE AND OLD.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0028s
== 20210621043337 RenameServicesToIntegrations: reverted (0.0799s) ============

Does this MR meet the acceptance criteria?

Conformity

I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
[-] I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.
This change is backwards compatible across updates, or this does not apply.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
[-] I have tested this MR in all supported browsers, or it's not needed.
[-] I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Related to #201856 (closed)

Edited Jul 07, 2021 by Luke Duncalfe

Rename services table to integrations