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

3 files changed, 74 insertions, 3 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
diff --git a/db/schema.rb b/db/schema.rb
index 4711b7873af..83c8ad48537 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: 20161212142807) do
+ActiveRecord::Schema.define(version: 20161207231621) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -430,7 +430,7 @@ ActiveRecord::Schema.define(version: 20161212142807) do
     t.string "state", default: "available", null: false
   end
 
-  add_index "environments", ["project_id", "name"], name: "index_environments_on_project_id_and_name", using: :btree
+  add_index "environments", ["project_id", "name"], name: "index_environments_on_project_id_and_name", unique: true, using: :btree
 
   create_table "events", force: :cascade do |t|
     t.string "target_type"
@@ -1290,4 +1290,4 @@ ActiveRecord::Schema.define(version: 20161212142807) do
   add_foreign_key "subscriptions", "projects", on_delete: :cascade
   add_foreign_key "trending_projects", "projects", on_delete: :cascade
   add_foreign_key "u2f_registrations", "users"
-end
\ No newline at end of file
+end