Use a new table for user conribution statsleibo/gitlab-ce-user-contribution-table

Create a new table containing a count of contributions for users per date. This
will be used for the contriution stats instead of the events table.

15 files changed, 199 insertions, 52 deletions

diff --git a/app/models/user_contribution.rb b/app/models/user_contribution.rb
new file mode 100644
index 00000000000..2159abc2d1c
--- /dev/null
+++ b/app/models/user_contribution.rb
@@ -0,0 +1,32 @@
+class UserContribution < ActiveRecord::Base
+  belongs_to :user
+
+  def self.calculate_for(date)
+    columns = %w(user_id date contributions).map { |column| connection.quote_column_name(column) }
+
+    UserContribution.connection.execute <<-EOF
+      INSERT INTO user_contributions (#{columns.join(', ')})
+      SELECT author_id, #{connection.quote(date)}, COUNT(*) AS contributions
+      FROM events
+      WHERE created_at >= #{connection.quote(date.beginning_of_day)}
+      AND created_at <= #{connection.quote(date.end_of_day)}
+      AND author_id IS NOT NULL
+      AND (
+        (
+          target_type in ('MergeRequest', 'Issue')
+          AND action in (
+            #{Event::CREATED},
+            #{Event::CLOSED},
+            #{Event::MERGED}
+          )
+        )
+        OR (target_type = 'Note' AND action = #{Event::COMMENTED})
+        OR action = #{Event::PUSHED}
+      )
+      GROUP BY author_id
+    EOF
+  rescue ActiveRecord::RecordNotUnique
+    # If we violated the unique constraint, then we've already inserted this
+    # day's rows.
+  end
+end
diff --git a/app/workers/user_contribution_worker.rb b/app/workers/user_contribution_worker.rb
new file mode 100644
index 00000000000..e2ae0a0c782
--- /dev/null
+++ b/app/workers/user_contribution_worker.rb
@@ -0,0 +1,11 @@
+class UserContributionWorker
+  include Sidekiq::Worker
+  include CronjobQueue
+
+  def perform(date = Date.today)
+    date = Date.parse(date) if date.is_a?(String)
+    Rails.logger.info("Calculating user contributions for #{date}")
+
+    UserContribution.calculate_for(date)
+  end
+end
diff --git a/changelogs/unreleased/user-contribution-table.yml b/changelogs/unreleased/user-contribution-table.yml
new file mode 100644
index 00000000000..b6717a8bf29
--- /dev/null
+++ b/changelogs/unreleased/user-contribution-table.yml
@@ -0,0 +1,5 @@
+---
+title: Create and populate a user conribution table, and change the user contribution
+  calendar to use it
+merge_request: 7310
+author: leibo
diff --git a/config/initializers/1_settings.rb b/config/initializers/1_settings.rb
index a23b3208dab..02d37dfb099 100644
--- a/config/initializers/1_settings.rb
+++ b/config/initializers/1_settings.rb
@@ -359,6 +359,9 @@ Settings.cron_jobs['stuck_ci_jobs_worker']['job_class'] = 'StuckCiJobsWorker'
 Settings.cron_jobs['pipeline_schedule_worker'] ||= Settingslogic.new({})
 Settings.cron_jobs['pipeline_schedule_worker']['cron'] ||= '19 * * * *'
 Settings.cron_jobs['pipeline_schedule_worker']['job_class'] = 'PipelineScheduleWorker'
+Settings.cron_jobs['user_contribution_worker'] ||= Settingslogic.new({})
+Settings.cron_jobs['user_contribution_worker']['cron'] ||= '0 1 * * *'
+Settings.cron_jobs['user_contribution_worker']['job_class'] = 'UserContributionWorker'
 Settings.cron_jobs['expire_build_artifacts_worker'] ||= Settingslogic.new({})
 Settings.cron_jobs['expire_build_artifacts_worker']['cron'] ||= '50 * * * *'
 Settings.cron_jobs['expire_build_artifacts_worker']['job_class'] = 'ExpireBuildArtifactsWorker'
diff --git a/config/sidekiq_queues.yml b/config/sidekiq_queues.yml
index 8235e3853dc..6343903321f 100644
--- a/config/sidekiq_queues.yml
+++ b/config/sidekiq_queues.yml
@@ -53,6 +53,7 @@
   - [project_export, 1]
   - [web_hook, 1]
   - [repository_check, 1]
+  - [user_contribution, 1]
   - [git_garbage_collect, 1]
   - [reactive_caching, 1]
   - [cronjob, 1]
diff --git a/db/migrate/20161105212938_create_user_contribution.rb b/db/migrate/20161105212938_create_user_contribution.rb
new file mode 100644
index 00000000000..18c454e6004
--- /dev/null
+++ b/db/migrate/20161105212938_create_user_contribution.rb
@@ -0,0 +1,18 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class CreateUserContribution < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  # Set this constant to true if this migration requires downtime.
+  DOWNTIME = false
+
+  def change
+    create_table :user_contributions, id: false do |t|
+      t.date :date, null: false
+      t.references :user, index: false, foreign_key: { on_delete: :cascade }, null: false
+      t.integer :contributions, null: false
+      t.index [:user_id, :date], unique: true
+    end
+  end
+end
diff --git a/db/migrate/20161122203926_populate_user_contribution_table.rb b/db/migrate/20161122203926_populate_user_contribution_table.rb
new file mode 100644
index 00000000000..d2bd6f63c33
--- /dev/null
+++ b/db/migrate/20161122203926_populate_user_contribution_table.rb
@@ -0,0 +1,22 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class PopulateUserContributionTable < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def up
+    base_delay = Time.now + 15.minutes
+
+    (Date.today - 1.year).upto(Date.today).each_with_index do |date, index|
+      job_time = base_delay + index.minutes
+
+      Sidekiq::Client.enqueue_to_in(:cronjob, job_time, UserContributionWorker, date)
+    end
+  end
+
+  def down
+    execute 'TRUNCATE user_contributions'
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index c238efbedbe..a565ad34b02 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -1538,6 +1538,14 @@ ActiveRecord::Schema.define(version: 20170928100231) do
 
   add_index "user_agent_details", ["subject_id", "subject_type"], name: "index_user_agent_details_on_subject_id_and_subject_type", using: :btree
 
+  create_table "user_contributions", id: false, force: :cascade do |t|
+    t.date "date", null: false
+    t.integer "user_id", null: false
+    t.integer "contributions", null: false
+  end
+
+  add_index "user_contributions", ["user_id", "date"], name: "index_user_contributions_on_user_id_and_date", unique: true, using: :btree
+
   create_table "user_custom_attributes", force: :cascade do |t|
     t.datetime "created_at", null: false
     t.datetime "updated_at", null: false
@@ -1775,6 +1783,7 @@ ActiveRecord::Schema.define(version: 20170928100231) do
   add_foreign_key "todos", "projects", name: "fk_45054f9c45", on_delete: :cascade
   add_foreign_key "trending_projects", "projects", on_delete: :cascade
   add_foreign_key "u2f_registrations", "users"
+  add_foreign_key "user_contributions", "users", on_delete: :cascade
   add_foreign_key "user_custom_attributes", "users", on_delete: :cascade
   add_foreign_key "user_synced_attributes_metadata", "users", on_delete: :cascade
   add_foreign_key "users_star_projects", "projects", name: "fk_22cd27ddfc", on_delete: :cascade
diff --git a/lib/gitlab/contributions_calendar.rb b/lib/gitlab/contributions_calendar.rb
index 0735243e021..ddb6bdf8511 100644
--- a/lib/gitlab/contributions_calendar.rb
+++ b/lib/gitlab/contributions_calendar.rb
@@ -13,24 +13,8 @@ module Gitlab
     def activity_dates
       return @activity_dates if @activity_dates.present?
 
-      # Can't use Event.contributions here because we need to check 3 different
-      # project_features for the (currently) 3 different contribution types
-      date_from = 1.year.ago
-      repo_events = event_counts(date_from, :repository)
-        .having(action: Event::PUSHED)
-      issue_events = event_counts(date_from, :issues)
-        .having(action: [Event::CREATED, Event::CLOSED], target_type: "Issue")
-      mr_events = event_counts(date_from, :merge_requests)
-        .having(action: [Event::MERGED, Event::CREATED, Event::CLOSED], target_type: "MergeRequest")
-      note_events = event_counts(date_from, :merge_requests)
-        .having(action: [Event::COMMENTED], target_type: "Note")
-
-      union = Gitlab::SQL::Union.new([repo_events, issue_events, mr_events, note_events])
-      events = Event.find_by_sql(union.to_sql).map(&:attributes)
-
-      @activity_dates = events.each_with_object(Hash.new {|h, k| h[k] = 0 }) do |event, activities|
-        activities[event["date"]] += event["total_amount"]
-      end
+      contributions_data = UserContributionCalendar.new(contributor).calculate
+      @activity_events = contributions_data
     end
 
     def events_by_date(date)
@@ -50,36 +34,5 @@ module Gitlab
     def starting_month
       Date.current.month
     end
-
-    private
-
-    def event_counts(date_from, feature)
-      t = Event.arel_table
-
-      # re-running the contributed projects query in each union is expensive, so
-      # use IN(project_ids...) instead. It's the intersection of two users so
-      # the list will be (relatively) short
-      @contributed_project_ids ||= projects.uniq.pluck(:id)
-      authed_projects = Project.where(id: @contributed_project_ids)
-        .with_feature_available_for_user(feature, current_user)
-        .reorder(nil)
-        .select(:id)
-
-      conditions = t[:created_at].gteq(date_from.beginning_of_day)
-        .and(t[:created_at].lteq(Date.current.end_of_day))
-        .and(t[:author_id].eq(contributor.id))
-
-      date_interval = if Gitlab::Database.postgresql?
-                        "INTERVAL '#{Time.zone.now.utc_offset} seconds'"
-                      else
-                        "INTERVAL #{Time.zone.now.utc_offset} SECOND"
-                      end
-
-      Event.reorder(nil)
-        .select(t[:project_id], t[:target_type], t[:action], "date(created_at + #{date_interval}) AS date", 'count(id) as total_amount')
-        .group(t[:project_id], t[:target_type], t[:action], "date(created_at + #{date_interval})")
-        .where(conditions)
-        .having(t[:project_id].in(Arel::Nodes::SqlLiteral.new(authed_projects.to_sql)))
-    end
   end
 end
diff --git a/lib/gitlab/user_contribution_calendar.rb b/lib/gitlab/user_contribution_calendar.rb
new file mode 100644
index 00000000000..7eace88e4d7
--- /dev/null
+++ b/lib/gitlab/user_contribution_calendar.rb
@@ -0,0 +1,22 @@
+module Gitlab
+  class UserContributionCalendar
+    def initialize(user)
+      @user = user
+    end
+
+    def calculate
+      query.each_with_object({}) do |(date, contributions), hash|
+        hash[date] = contributions
+      end
+    end
+
+    private
+
+    def query
+      UserContribution
+        .where(user_id: @user.id)
+        .where('date >= ?', 1.year.ago.beginning_of_day)
+        .pluck(:date, :contributions)
+    end
+  end
+end
diff --git a/spec/features/calendar_spec.rb b/spec/features/calendar_spec.rb
index 4fc6956d111..b29d4a65124 100644
--- a/spec/features/calendar_spec.rb
+++ b/spec/features/calendar_spec.rb
@@ -61,6 +61,8 @@ feature 'Contributions Calendar', :js do
     }
 
     Event.create(note_comment_params)
+
+    UserContribution.calculate_for(Date.today)
   end
 
   def selected_day_activities
@@ -142,6 +144,7 @@ feature 'Contributions Calendar', :js do
     describe '1 issue creation calendar activity' do
       before do
         Issues::CreateService.new(contributed_project, user, issue_params).execute
+        UserContribution.calculate_for(Date.today)
       end
 
       it_behaves_like 'a day with activity', contribution_count: 1
@@ -166,6 +169,7 @@ feature 'Contributions Calendar', :js do
     describe '10 calendar activities' do
       before do
         10.times { push_code_contribution }
+        UserContribution.calculate_for(Date.today)
       end
 
       it_behaves_like 'a day with activity', contribution_count: 10
@@ -178,6 +182,8 @@ feature 'Contributions Calendar', :js do
         Timecop.freeze(Date.yesterday) do
           Issues::CreateService.new(contributed_project, user, issue_params).execute
         end
+        UserContribution.calculate_for(Date.today)
+        UserContribution.calculate_for(Date.yesterday)
       end
       include_context 'visit user page'
 
diff --git a/spec/lib/gitlab/contributions_calendar_spec.rb b/spec/lib/gitlab/contributions_calendar_spec.rb
index f1655854486..a87c881f1b4 100644
--- a/spec/lib/gitlab/contributions_calendar_spec.rb
+++ b/spec/lib/gitlab/contributions_calendar_spec.rb
@@ -53,11 +53,18 @@ describe Gitlab::ContributionsCalendar do
     )
   end
 
+  def create_contributions(day, count)
+    UserContribution.create!(
+      user: contributor,
+      contributions: count,
+      date: day
+    )
+  end
+
   describe '#activity_dates' do
     it "returns a hash of date => count" do
-      create_event(public_project, last_week)
-      create_event(public_project, last_week)
-      create_event(public_project, today)
+      create_contributions(last_week, 2)
+      create_contributions(today, 1)
 
       expect(calendar.activity_dates).to eq(last_week => 2, today => 1)
     end
diff --git a/spec/lib/gitlab/user_contribution_calendar_spec.rb b/spec/lib/gitlab/user_contribution_calendar_spec.rb
new file mode 100644
index 00000000000..a6fb61b0b05
--- /dev/null
+++ b/spec/lib/gitlab/user_contribution_calendar_spec.rb
@@ -0,0 +1,25 @@
+require 'spec_helper'
+
+describe Gitlab::UserContributionCalendar, lib: true do
+  let(:contributor) { create(:user) }
+  let(:today) { Time.now.to_date }
+  let(:last_week) { today - 7.days }
+
+  def create_contributions(day, count)
+    UserContribution.create!(
+      user: contributor,
+      contributions: count,
+      date: day
+    )
+  end
+
+  describe '.calculate' do
+    it 'returns a Hash mapping dates to contribution counts for the user' do
+      create_contributions(last_week, 2)
+      create_contributions(today, 1)
+
+      user_contribution_calendar = Gitlab::UserContributionCalendar.new(contributor)
+      expect(user_contribution_calendar.calculate).to eq(last_week => 2, today => 1)
+    end
+  end
+end
diff --git a/spec/models/user_contribution_spec.rb b/spec/models/user_contribution_spec.rb
new file mode 100644
index 00000000000..405abb8bf2a
--- /dev/null
+++ b/spec/models/user_contribution_spec.rb
@@ -0,0 +1,20 @@
+require "spec_helper"
+
+describe UserContribution, models: true do
+  describe '.calculate_for' do
+    let(:contributor) { create(:user) }
+    let(:project) { create(:project) }
+
+    it 'writes all user contributions for a given date' do
+      create(:event, :created,
+             project: project,
+             target: create(:issue, project: project, author: contributor),
+             author: contributor,
+             created_at: 1.day.ago)
+
+      UserContribution.calculate_for(1.day.ago)
+
+      expect(UserContribution.first.contributions).to eq(1)
+    end
+  end
+end
diff --git a/spec/workers/user_contribution_worker_spec.rb b/spec/workers/user_contribution_worker_spec.rb
new file mode 100644
index 00000000000..c5910481771
--- /dev/null
+++ b/spec/workers/user_contribution_worker_spec.rb
@@ -0,0 +1,13 @@
+require 'spec_helper'
+
+describe UserContributionWorker do
+  describe "#perform" do
+    it "calls the calculation of user contributions for the given date" do
+      worker = described_class.new
+      date = 1.day.ago
+
+      expect(UserContribution).to receive(:calculate_for).with(date)
+      worker.perform(date)
+    end
+  end
+end