Fix project records with invalid visibility_level values

The AddVisibilityLevelToGroups migration introduced a visibility_level for
namespaces and specified that projects should always have a visibility level
less than or equal to their namespace. However, some invalid rows could have
been created.

This commit introduces a migration that updates the invalid rows, setting the
invalid project to have the same visibility_level as their namespaces. This
will make some projects internal or private when they would previously have
been public or internal, but this is better than silently making an internal
or private group public.

3 files changed, 54 insertions, 1 deletions

diff --git a/changelogs/unreleased/22699-group-permssion-background-migration.yml b/changelogs/unreleased/22699-group-permssion-background-migration.yml
new file mode 100644
index 00000000000..e8c221b6c42
--- /dev/null
+++ b/changelogs/unreleased/22699-group-permssion-background-migration.yml
@@ -0,0 +1,4 @@
+---
+title: Fix project records with invalid visibility_level values
+merge_request: 7391
+author: 
diff --git a/db/post_migrate/20161109150329_fix_project_records_with_invalid_visibility.rb b/db/post_migrate/20161109150329_fix_project_records_with_invalid_visibility.rb
new file mode 100644
index 00000000000..bea1cfa4c5d
--- /dev/null
+++ b/db/post_migrate/20161109150329_fix_project_records_with_invalid_visibility.rb
@@ -0,0 +1,49 @@
+class FixProjectRecordsWithInvalidVisibility < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  BATCH_SIZE = 1000
+  DOWNTIME = false
+
+  # This migration is idempotent and there's no sense in throwing away the
+  # partial result if it's interrupted
+  disable_ddl_transaction!
+
+  def up
+    projects = Arel::Table.new(:projects)
+    namespaces = Arel::Table.new(:namespaces)
+
+    finder =
+      projects.
+      join(namespaces, Arel::Nodes::InnerJoin).
+      on(projects[:namespace_id].eq(namespaces[:id])).
+      where(projects[:visibility_level].gt(namespaces[:visibility_level])).
+      project(projects[:id]).
+      take(BATCH_SIZE)
+
+    # MySQL requires a derived table to perform this query
+    nested_finder =
+      projects.
+      from(finder.as("AS projects_inner")).
+      project(projects[:id])
+
+    valuer =
+      namespaces.
+      where(namespaces[:id].eq(projects[:namespace_id])).
+      project(namespaces[:visibility_level])
+
+    # Update matching rows until none remain. The finder contains a limit.
+    loop do
+      updater = Arel::UpdateManager.new(ActiveRecord::Base).
+        table(projects).
+        set(projects[:visibility_level] => Arel::Nodes::SqlLiteral.new("(#{valuer.to_sql})")).
+        where(projects[:id].in(nested_finder))
+
+      num_updated = connection.exec_update(updater.to_sql, self.class.name, [])
+      break if num_updated == 0
+    end
+  end
+
+  def down
+    # no-op
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 62c325a52d7..64d744d8268 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20161106185620) do
+ActiveRecord::Schema.define(version: 20161109150329) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"