Skip to content

Migrate project topics from `acts_as_taggable` gem to separate table

Jonas Wälter requested to merge siemens/gitlab:project-topics-data-migration-2 into master

What does this MR do?

This MR is the first of two steps to resolve #335946 (closed): It creates two new tables topics and project_topics and migrates the project topics from the existing tables tags and taggings (acts_as_taggable gem) to the new separate tables. Only the project topics taggings are migrated, the other taggings (used on different CI models) remain.

During the background migration, it is ensured that the "old" and the "new" topics of a project are returned. After the migration is successfully completed, these temporary code fragments will be removed in a follow-up MR so that the project model don't use the acts_as_taggable gem anymore.

database Database Engineering Category:Database groupdatabase

🛠 with ❤ at Siemens

/cc @bufferoverflow

Database Migrations

1) 20210729081351_create_topics

rails db:migrate:up VERSION=20210729081351
== 20210729081351 CreateTopics: migrating =====================================
-- create_table(:topics, {})
-- quote_column_name(:name)
   -> 0.0000s
   -> 0.0386s
-- quote_table_name("check_7a90d4c757")
   -> 0.0001s
-- quote_table_name(:topics)
   -> 0.0000s
-- execute("ALTER TABLE \"topics\"\nADD CONSTRAINT \"check_7a90d4c757\" CHECK (char_length(\"name\") <= 255)\n")
   -> 0.0038s
== 20210729081351 CreateTopics: migrated (0.0519s) ============================
rails db:migrate:down VERSION=20210729081351
== 20210729081351 CreateTopics: reverting =====================================
-- drop_table(:topics, {})
   -> 0.0027s
== 20210729081351 CreateTopics: reverted (0.0108s) ============================

2) 20210729081739_create_project_topics

rails db:migrate:up VERSION=20210729081739
== 20210729081739 CreateProjectTopics: migrating ==============================
-- create_table(:project_topics)
   -> 0.0333s
== 20210729081739 CreateProjectTopics: migrated (0.0334s) =====================
rails db:migrate:down VERSION=20210729081739
== 20210729081739 CreateProjectTopics: reverting ==============================
-- drop_table(:project_topics)
   -> 0.0038s
== 20210729081739 CreateProjectTopics: reverted (0.0057s) =====================

3) 20210729125641_add_foreign_key_to_project_on_project_topic

rails db:migrate:up VERSION=20210729125641
== 20210729125641 AddForeignKeyToProjectOnProjectTopic: migrating =============
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:project_topics)
   -> 0.0049s
-- execute("ALTER TABLE project_topics\nADD CONSTRAINT fk_db13576296\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0042s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- execute("ALTER TABLE project_topics VALIDATE CONSTRAINT fk_db13576296;")
   -> 0.0107s
-- execute("RESET ALL")
   -> 0.0011s
== 20210729125641 AddForeignKeyToProjectOnProjectTopic: migrated (0.0373s) ====
rails db:migrate:down VERSION=20210729125641
== 20210729125641 AddForeignKeyToProjectOnProjectTopic: reverting =============
-- remove_foreign_key(:project_topics, {:column=>:project_id})
   -> 0.0088s
== 20210729125641 AddForeignKeyToProjectOnProjectTopic: reverted (0.0227s) ====

4) 20210729125659_add_foreign_key_to_topic_on_project_topic

rails db:migrate:up VERSION=20210729125659
== 20210729125659 AddForeignKeyToTopicOnProjectTopic: migrating ===============
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:project_topics)
   -> 0.0035s
-- execute("ALTER TABLE project_topics\nADD CONSTRAINT fk_34af9ab07a\nFOREIGN KEY (topic_id)\nREFERENCES topics (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0046s
-- execute("SET statement_timeout TO 0")
   -> 0.0008s
-- execute("ALTER TABLE project_topics VALIDATE CONSTRAINT fk_34af9ab07a;")
   -> 0.0094s
-- execute("RESET ALL")
   -> 0.0009s
== 20210729125659 AddForeignKeyToTopicOnProjectTopic: migrated (0.0328s) ======
rails db:migrate:down VERSION=20210729125659
== 20210729125659 AddForeignKeyToTopicOnProjectTopic: reverting ===============
-- remove_foreign_key(:project_topics, {:column=>:topic_id})
   -> 0.0068s
== 20210729125659 AddForeignKeyToTopicOnProjectTopic: reverted (0.0192s) ======

5) 20210730104800_schedule_extract_project_topics_into_separate_table

rails db:migrate:up VERSION=20210730104800
== 20210730104800 ScheduleExtractProjectTopicsIntoSeparateTable: migrating ====
-- Scheduled 1 ExtractProjectTopicsIntoSeparateTable jobs with a maximum of 30000 records per batch and an interval of 120 seconds.

The migration is expected to take at least 120 seconds. Expect all jobs to have completed after 2021-08-03 09:47:32 UTC."
== 20210730104800 ScheduleExtractProjectTopicsIntoSeparateTable: migrated (0.0333s)
rails db:migrate:down VERSION=20210730104800
== 20210730104800 ScheduleExtractProjectTopicsIntoSeparateTable: reverting ====
== 20210730104800 ScheduleExtractProjectTopicsIntoSeparateTable: reverted (0.0000s)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • [-] 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 Jonas Wälter

Merge request reports