Make the index on environment name and project id unique, fixing up any duplicates

2 files changed, 71 insertions, 0 deletions

diff --git a/db/migrate/20161207231620_fixup_environment_name_uniqueness.rb b/db/migrate/20161207231620_fixup_environment_name_uniqueness.rb
new file mode 100644
index 00000000000..b74552e762d
--- /dev/null
+++ b/db/migrate/20161207231620_fixup_environment_name_uniqueness.rb
@@ -0,0 +1,53 @@
+class FixupEnvironmentNameUniqueness < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = true
+  DOWNTIME_REASON = 'Renaming non-unique environments'
+
+  def up
+    environments = Arel::Table.new(:environments)
+
+    # Get all [project_id, name] pairs that occur more than once
+    finder_sql = environments.
+      group(environments[:project_id], environments[:name]).
+      having(Arel.sql("COUNT(1)").gt(1)).
+      project(environments[:project_id], environments[:name]).
+      to_sql
+
+    conflicting = connection.exec_query(finder_sql)
+
+    conflicting.rows.each do |project_id, name|
+      fix_duplicates(project_id, name)
+    end
+  end
+
+  def down
+    # Nothing to do
+  end
+
+  # Rename conflicting environments by appending "-#{id}" to all but the first
+  def fix_duplicates(project_id, name)
+    environments = Arel::Table.new(:environments)
+    finder_sql = environments.
+      where(environments[:project_id].eq(project_id)).
+      where(environments[:name].eq(name)).
+      order(environments[:id].asc).
+      project(environments[:id], environments[:name]).
+      to_sql
+
+    # Now we have the data for all the conflicting rows
+    conflicts = connection.exec_query(finder_sql).rows
+    conflicts.shift # Leave the first row alone
+
+    conflicts.each do |id, name|
+      update_sql =
+        Arel::UpdateManager.new(ActiveRecord::Base).
+        table(environments).
+        set(environments[:name] => name + "-" + id.to_s).
+        where(environments[:id].eq(id)).
+        to_sql
+
+      connection.exec_update(update_sql, self.class.name, [])
+    end
+  end
+end
diff --git a/db/migrate/20161207231621_create_environment_name_unique_index.rb b/db/migrate/20161207231621_create_environment_name_unique_index.rb
new file mode 100644
index 00000000000..ac680c8d10f
--- /dev/null
+++ b/db/migrate/20161207231621_create_environment_name_unique_index.rb
@@ -0,0 +1,18 @@
+class CreateEnvironmentNameUniqueIndex < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  DOWNTIME = true
+  DOWNTIME_REASON = 'Making a non-unique index into a unique index'
+
+  def up
+    remove_index :environments, [:project_id, :name]
+    add_concurrent_index :environments, [:project_id, :name], unique: true
+  end
+
+  def down
+    remove_index :environments, [:project_id, :name], unique: true
+    add_concurrent_index :environments, [:project_id, :name]
+  end
+end