Improve builds to commits migration using sql

Signed-off-by: Dmitriy Zaporozhets <dmitriy.zaporozhets@gmail.com>

3 files changed, 38 insertions, 64 deletions

diff --git a/db/migrate/20140823225019_create_commits_from_builds.rb b/db/migrate/20140823225019_create_commits_from_builds.rb
index 97252a0..15f84b1 100644
--- a/db/migrate/20140823225019_create_commits_from_builds.rb
+++ b/db/migrate/20140823225019_create_commits_from_builds.rb
@@ -10,71 +10,13 @@ class CreateCommitsFromBuilds < ActiveRecord::Migration
       t.timestamps
     end
 
-    reversible do |migration|
-      migration.up { init_commits }
-      migration.down {}
-    end
-
     add_column :builds, :commit_id, :integer
 
-    reversible do |migration|
-      migration.up { associate_builds_with_commit }
-      migration.down { dissociate_builds_with_commit }
-    end
-
     # Remove commit data from builds
-    #  -- don't use change_table, its not very reversible
-    remove_column :builds, :project_id, :integer
-    remove_column :builds, :ref,        :string
-    remove_column :builds, :sha,        :string
-    remove_column :builds, :before_sha, :string
-    remove_column :builds, :push_data,  :text
-  end
-
-  private
-
-  def init_commits
-    Commit.reset_column_information
-
-    # Create one Commit for each unique sha value
-    shas = Build.pluck(:sha).uniq
-    shas.each do |sha|
-      # Get latest build for a particular commit
-      build = Build.where(sha: sha).order('created_at desc').first
-      attributes = {
-        project_id: build.project_id,
-        ref: build.ref,
-        sha: build.sha,
-        before_sha: build.before_sha,
-        push_data: build.push_data,
-        # Original commit status matches the latest build status
-        status: build.status
-      }
-
-      Commit.create!(attributes)
-    end
-  end
-
-  def associate_builds_with_commit
-    Build.reset_column_information
-    Build.find_each do |build|
-      build.commit_id = Commit.find_by_sha(build.sha).id
-      build.save!
-    end
-  end
-
-  def dissociate_builds_with_commit
-    Build.reset_column_information
-    Build.find_each do |build|
-      # Don't assume an assocation exists
-      commit = Commit.find(build.commit_id)
-
-      build.project_id = commit.project_id
-      build.ref = commit.ref
-      build.sha = commit.sha
-      build.before_sha = commit.before_sha
-      build.push_data = commit.push_data
-      build.save!
-    end
+    #remove_column :builds, :project_id, :integer
+    #remove_column :builds, :ref,        :string
+    #remove_column :builds, :sha,        :string
+    #remove_column :builds, :before_sha, :string
+    #remove_column :builds, :push_data,  :text
   end
 end
diff --git a/db/migrate/20141031114419_migrate_build_to_commits.rb b/db/migrate/20141031114419_migrate_build_to_commits.rb
new file mode 100644
index 0000000..dc90ec6
--- /dev/null
+++ b/db/migrate/20141031114419_migrate_build_to_commits.rb
@@ -0,0 +1,21 @@
+class MigrateBuildToCommits < ActiveRecord::Migration
+  def change
+    execute <<eos
+INSERT INTO commits ( sha, project_id, ref, before_sha, push_data )
+SELECT sha, project_id, ref, before_sha, push_data FROM builds
+WHERE id IN (SELECT MAX(id) FROM builds GROUP BY sha)
+eos
+
+
+    if ActiveRecord::Base.connection.adapter_name == 'PostgreSQL'
+      execute <<eos
+UPDATE builds
+SET commit_id = commits.id
+FROM commits
+WHERE commits.sha = builds.sha
+eos
+    else
+      execute "UPDATE builds b, commits c SET b.commit_id = c.id WHERE c.sha = b.sha"
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 5769a3e..0a46f2f 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: 20141028162820) do
+ActiveRecord::Schema.define(version: 20141031114419) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -31,6 +31,7 @@ ActiveRecord::Schema.define(version: 20141028162820) do
     t.text     "push_data"
     t.integer  "runner_id"
     t.float    "coverage"
+    t.integer  "commit_id"
   end
 
   add_index "builds", ["project_id", "sha"], name: "index_builds_on_project_id_and_sha", using: :btree
@@ -38,6 +39,16 @@ ActiveRecord::Schema.define(version: 20141028162820) do
   add_index "builds", ["runner_id"], name: "index_builds_on_runner_id", using: :btree
   add_index "builds", ["sha"], name: "index_builds_on_sha", using: :btree
 
+  create_table "commits", force: true do |t|
+    t.integer  "project_id"
+    t.string   "ref"
+    t.string   "sha"
+    t.string   "before_sha"
+    t.text     "push_data"
+    t.datetime "created_at"
+    t.datetime "updated_at"
+  end
+
   create_table "projects", force: true do |t|
     t.string   "name",                                     null: false
     t.integer  "timeout",                  default: 1800,  null: false