Remove duplicated records and add unique constraint

3 files changed, 68 insertions, 0 deletions

diff --git a/db/migrate/20170622135451_remove_duplicated_variable.rb b/db/migrate/20170622135451_remove_duplicated_variable.rb
new file mode 100644
index 00000000000..bd3aa3f5323
--- /dev/null
+++ b/db/migrate/20170622135451_remove_duplicated_variable.rb
@@ -0,0 +1,45 @@
+class RemoveDuplicatedVariable < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    if Gitlab::Database.postgresql?
+      execute <<~SQL
+        DELETE FROM ci_variables var USING (#{duplicated_ids}) dup
+          #{join_conditions}
+      SQL
+    else
+      execute <<~SQL
+        DELETE var FROM ci_variables var INNER JOIN (#{duplicated_ids}) dup
+          #{join_conditions}
+      SQL
+    end
+  end
+
+  def down
+    # noop
+  end
+
+  def duplicated_ids
+    <<~SQL
+      SELECT MAX(id) AS id, #{key}, project_id
+        FROM ci_variables GROUP BY #{key}, project_id
+    SQL
+  end
+
+  def join_conditions
+    <<~SQL
+      WHERE var.key = dup.key
+        AND var.project_id = dup.project_id
+        AND var.id <> dup.id
+    SQL
+  end
+
+  def key
+    # key needs to be quoted in MySQL
+    quote_column_name('key')
+  end
+end
diff --git a/db/migrate/20170612150426_add_environment_scope_to_ci_variables.rb b/db/migrate/20170622135628_add_environment_scope_to_ci_variables.rb
index 17fe062d8d5..17fe062d8d5 100644
--- a/db/migrate/20170612150426_add_environment_scope_to_ci_variables.rb
+++ b/db/migrate/20170622135628_add_environment_scope_to_ci_variables.rb
diff --git a/db/migrate/20170622135728_add_unique_constraint_to_ci_variables.rb b/db/migrate/20170622135728_add_unique_constraint_to_ci_variables.rb
new file mode 100644
index 00000000000..f953cd66414
--- /dev/null
+++ b/db/migrate/20170622135728_add_unique_constraint_to_ci_variables.rb
@@ -0,0 +1,23 @@
+class AddUniqueConstraintToCiVariables < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    unless index_exists?(:ci_variables, columns)
+      add_concurrent_index(:ci_variables, columns, unique: true)
+    end
+  end
+
+  def down
+    if index_exists?(:ci_variables, columns)
+      remove_concurrent_index(:ci_variables, columns)
+    end
+  end
+
+  def columns
+    @columns ||= [:project_id, :key, :environment_scope]
+  end
+end