Merge branch 'feature/gb/migrate-pipeline-stages' into feature/gb/persist-pipeline-stages

* feature/gb/migrate-pipeline-stages:
  Use the latest migration version as a schema version
  Remove stage index concurrently on migration rollback
  Disable timeouts in foreign keys for stages migration
  Remove obsolete stages/build before adding foreign keys
  Create indexes on pipeline stages before migration
  Migrate stage_id only it job does not have it already
  Migrate pipeline stages only when not migrated already

5 files changed, 26 insertions, 6 deletions

diff --git a/db/post_migrate/20170526190948_create_index_in_pipeline_stages.rb b/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb
index d049f87578a..ec9ff33b6b7 100644
--- a/db/post_migrate/20170526190948_create_index_in_pipeline_stages.rb
+++ b/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb
@@ -10,6 +10,6 @@ class CreateIndexInPipelineStages < ActiveRecord::Migration
   end
 
   def down
-    remove_index(:ci_stages, [:pipeline_id, :name])
+    remove_concurrent_index(:ci_stages, [:pipeline_id, :name])
   end
 end
diff --git a/db/post_migrate/20170526185842_migrate_pipeline_stages.rb b/db/post_migrate/20170526185842_migrate_pipeline_stages.rb
index 05e095f07cb..382791c4664 100644
--- a/db/post_migrate/20170526185842_migrate_pipeline_stages.rb
+++ b/db/post_migrate/20170526185842_migrate_pipeline_stages.rb
@@ -11,9 +11,9 @@ class MigratePipelineStages < ActiveRecord::Migration
     execute <<-SQL.strip_heredoc
       INSERT INTO ci_stages (project_id, pipeline_id, name)
         SELECT project_id, commit_id, stage FROM ci_builds
-          WHERE stage IS NOT NULL
-          GROUP BY project_id, commit_id, stage, stage_idx
-          ORDER BY stage_idx
+          WHERE stage IS NOT NULL AND stage_id IS NULL
+          GROUP BY project_id, commit_id, stage
+          ORDER BY MAX(stage_idx)
     SQL
   end
 
diff --git a/db/post_migrate/20170526185921_migrate_build_stage_reference.rb b/db/post_migrate/20170526185921_migrate_build_stage_reference.rb
index 8f453b8cd82..3c30dfd6dde 100644
--- a/db/post_migrate/20170526185921_migrate_build_stage_reference.rb
+++ b/db/post_migrate/20170526185921_migrate_build_stage_reference.rb
@@ -10,7 +10,9 @@ class MigrateBuildStageReference < ActiveRecord::Migration
                          'WHERE ci_stages.pipeline_id = ci_builds.commit_id ' \
                          'AND ci_stages.name = ci_builds.stage)')
 
-    update_column_in_batches(:ci_builds, :stage_id, stage_id)
+    update_column_in_batches(:ci_builds, :stage_id, stage_id) do |table, query|
+      query.where(table[:stage_id].eq(nil))
+    end
   end
 
   def down
diff --git a/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb b/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb
index 40060d12d74..5ae3c1ae098 100644
--- a/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb
+++ b/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb
@@ -6,6 +6,24 @@ class CreateForeignKeysForPipelineStages < ActiveRecord::Migration
   disable_ddl_transaction!
 
   def up
+    disable_statement_timeout
+
+    execute <<~SQL
+      DELETE FROM ci_stages
+        WHERE NOT EXISTS (
+          SELECT true FROM projects
+            WHERE projects.id = ci_stages.project_id
+        )
+    SQL
+
+    execute <<~SQL
+      DELETE FROM ci_builds
+        WHERE NOT EXISTS (
+          SELECT true FROM ci_stages
+            WHERE ci_stages.id = ci_builds.stage_id
+        )
+    SQL
+
     add_concurrent_foreign_key :ci_stages, :projects, column: :project_id, on_delete: :cascade
     add_concurrent_foreign_key :ci_builds, :ci_stages, column: :stage_id, on_delete: :cascade
   end
diff --git a/db/schema.rb b/db/schema.rb
index 47484e6b8fe..23c3ecdd517 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: 20170526190948) do
+ActiveRecord::Schema.define(version: 20170526190708) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"