Optimise CI status accessor

2 files changed, 38 insertions, 0 deletions

diff --git a/db/migrate/20160412174954_add_ci_commit_indexes.rb b/db/migrate/20160412174954_add_ci_commit_indexes.rb
new file mode 100644
index 00000000000..49fbb3e9bdc
--- /dev/null
+++ b/db/migrate/20160412174954_add_ci_commit_indexes.rb
@@ -0,0 +1,7 @@
+class AddCiCommitIndexes < ActiveRecord::Migration
+  def change
+    add_index :ci_commits, [:gl_project_id, :sha]
+    add_index :ci_commits, [:gl_project_id, :status]
+    add_index :ci_commits, [:status]
+  end
+end
diff --git a/db/migrate/20160412175417_update_ci_commit.rb b/db/migrate/20160412175417_update_ci_commit.rb
new file mode 100644
index 00000000000..ebe1d143b1f
--- /dev/null
+++ b/db/migrate/20160412175417_update_ci_commit.rb
@@ -0,0 +1,31 @@
+class UpdateCiCommit < ActiveRecord::Migration
+  def change
+    execute("UPDATE ci_commits SET status=#{status}, ref=#{ref}, tag=#{tag} WHERE status IS NULL")
+  end
+
+  def status
+    builds = '(SELECT COUNT(*) FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id)'
+    success = "(SELECT COUNT(*) FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id AND status='success')"
+    ignored = "(SELECT COUNT(*) FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id AND (status='failed' OR status='canceled') AND allow_failure)"
+    pending = "(SELECT COUNT(*) FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id AND status='pending')"
+    running = "(SELECT COUNT(*) FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id AND status='running')"
+    canceled = "(SELECT COUNT(*) FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id AND status='canceled')"
+
+    "(CASE
+      WHEN #{builds}=0 THEN 'skipped'
+      WHEN #{builds}=#{success}+#{ignored} THEN 'success'
+      WHEN #{builds}=#{pending} THEN 'pending'
+      WHEN #{builds}=#{canceled} THEN 'canceled'
+      WHEN #{running}+#{pending}>0 THEN 'running'
+      ELSE 'failed'
+    END)"
+  end
+
+  def ref
+    '(SELECT ref FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id ORDER BY id DESC LIMIT 1)'
+  end
+
+  def tag
+    '(SELECT tag FROM ci_builds WHERE ci_builds.commit_id=ci_commits.id ORDER BY id DESC LIMIT 1)'
+  end
+end