Fix bad data in projects.has_external_wiki

What does this MR do?

This MR fixes historical bad data in projects.has_external_wiki #273574 (closed).

Note: A PG Trigger added in !49916 (merged) will ensure future data is correct.

Migration output

Up

== 20210105030125 CleanupProjectsWithBadHasExternalWikiData: migrating ========
-- execute("WITH project_ids_to_update (id) AS (\n  SELECT \"services\".\"project_id\" FROM \"services\" INNER JOIN \"projects\" ON \"projects\".\"id\" = \"services\".\"project_id\" WHERE \"services\".\"active\" = TRUE AND \"services\".\"type\" = 'ExternalWikiService' AND \"services\".\"project_id\" IS NOT NULL AND \"services\".\"id\" >= 53 AND \"projects\".\"has_external_wiki\" = FALSE AND \"projects\".\"pending_delete\" = FALSE AND \"projects\".\"archived\" = FALSE\n)\nUPDATE projects SET has_external_wiki = true WHERE id IN (SELECT id FROM project_ids_to_update)\n")
   -> 0.0054s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, :id, {:where=>"(\n  \"projects\".\"has_external_wiki\" = TRUE\n)\nAND \"projects\".\"pending_delete\" = FALSE\nAND \"projects\".\"archived\" = FALSE\n", :name=>"tmp_index_projects_on_id_where_has_external_wiki_is_true", :algorithm=>:concurrently})
   -> 0.0157s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:projects, :id, {:where=>"(\n  \"projects\".\"has_external_wiki\" = TRUE\n)\nAND \"projects\".\"pending_delete\" = FALSE\nAND \"projects\".\"archived\" = FALSE\n", :name=>"tmp_index_projects_on_id_where_has_external_wiki_is_true", :algorithm=>:concurrently})
   -> 0.0088s
-- execute("RESET ALL")
   -> 0.0002s
-- execute("WITH project_ids_to_update (id) AS (\n  SELECT \"projects\".\"id\" FROM \"projects\" WHERE ((\n  \"projects\".\"has_external_wiki\" = TRUE\n)\nAND \"projects\".\"pending_delete\" = FALSE\nAND \"projects\".\"archived\" = FALSE\n) AND \"projects\".\"id\" >= 19 AND (NOT EXISTS (SELECT 1 FROM \"services\" WHERE (services.project_id = projects.id) AND \"services\".\"type\" = 'ExternalWikiService' AND \"services\".\"active\" = TRUE))\n)\nUPDATE projects SET has_external_wiki = false WHERE id IN (SELECT id FROM project_ids_to_update)\n")
   -> 0.0010s
-- transaction_open?()
   -> 0.0000s
-- indexes(:projects)
   -> 0.0149s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"tmp_index_projects_on_id_where_has_external_wiki_is_true"})
   -> 0.0032s
== 20210105030125 CleanupProjectsWithBadHasExternalWikiData: migrated (0.1786s)

Down

Note, this is a no-op.

== 20210105030125 CleanupProjectsWithBadHasExternalWikiData: reverting ========
== 20210105030125 CleanupProjectsWithBadHasExternalWikiData: reverted (0.0000s)

SQL queries in CleanupProjectsWithBadHasExternalWikiData

Update projects.has_external_wiki to be TRUE.

Note, query is operated on batches of 100 services.

WITH project_ids_to_update (id) AS 
(
   SELECT
      "services"."project_id" 
   FROM
      "services" 
      INNER JOIN
         "projects" 
         ON "projects"."id" = "services"."project_id" 
   WHERE
      "services"."active" = TRUE 
      AND "services"."type" = 'ExternalWikiService' 
      AND "services"."project_id" IS NOT NULL 
      AND "services"."id" >= 100
      AND "services"."id" < 200 
      AND "projects"."has_external_wiki" = FALSE 
      AND "projects"."pending_delete" = FALSE 
      AND "projects"."archived" = FALSE 
)
UPDATE
   projects 
SET
   has_external_wiki = true 
WHERE
   id IN 
   (
      SELECT
         id 
      FROM
         project_ids_to_update
   )

Temporary index (gets removed during the migration)

CREATE INDEX tmp_index_projects_on_id_where_has_external_wiki_is_true 
ON projects USING btree (id) 
WHERE
   (
(has_external_wiki = true) 
      AND 
      (
         pending_delete = false
      )
      AND 
      (
         archived = false
      )
   )
;

Update projects.has_external_wiki to be FALSE.

Note, query is operated on batches of 100 projects.

WITH project_ids_to_update (id) AS 
(
   SELECT
      "projects"."id" 
   FROM
      "projects" 
   WHERE
      (
( "projects"."has_external_wiki" = TRUE ) 
         AND "projects"."pending_delete" = FALSE 
         AND "projects"."archived" = FALSE 
      )
      AND "projects"."id" >= 100
      AND "projects"."id" < 200 
      AND 
      (
         NOT EXISTS 
         (
            SELECT
               1 
            FROM
               "services" 
            WHERE
               (
                  services.project_id = projects.id
               )
               AND "services"."type" = 'ExternalWikiService' 
               AND "services"."active" = TRUE
         )
      )
)
UPDATE
   projects 
SET
   has_external_wiki = false 
WHERE
   id IN 
   (
      SELECT
         id 
      FROM
         project_ids_to_update
   )

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

Related to #273574 (closed)