From 8e84acbf2e7e306ba937aaae87cfed35d2632e10 Mon Sep 17 00:00:00 2001 From: Kamil Trzcinski Date: Tue, 12 Apr 2016 19:57:54 +0200 Subject: Optimise CI status accessor --- db/migrate/20160412174954_add_ci_commit_indexes.rb | 7 +++++ db/migrate/20160412175417_update_ci_commit.rb | 31 ++++++++++++++++++++++ 2 files changed, 38 insertions(+) create mode 100644 db/migrate/20160412174954_add_ci_commit_indexes.rb create mode 100644 db/migrate/20160412175417_update_ci_commit.rb (limited to 'db') 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 -- cgit v1.2.1