Integrate Sheriff Rotation data

[andymeneely]

Now that we have the excel file for the Sherriff Rotation, let's get this data into the database and into our models. Ultimately, I would like to be able to have the following work:

> Developer.take.sheriff?(DateTime.new(2011,01,01))
> CodeReview.take.sheriffs
#returns an ActiveRecord::Relation of the people who were at one point sheriffs
> CodeReview.joins(participants: :sheriffs).group(:issue)
#returns all code reviews with the number of people who were at one point sheriffs (this might be wrong, but hopefully you get the idea)

The excel sheet is a little rough around the edges. Let's move it into a Google Spreadsheet with the following things:

• I started the GoogleDoc called Sheriff Calendar
• Single sheet. No need for multiple sheets
• The only rotations I care about are these: Chromium Perf Sheriff Rotation, Build Sheriff, Chromium GPU Pixel Wrangling, Valgrind Sheriff, Chromium Troopers Rotation (and similar). The other ones aren't relevant to our study and won't count.

Then, download our Google Spreadsheet as a csv and write a loader for that csv.

• Use the CSV parser, not "split by commas" in Ruby for parsing the CSV file. Note that the Attendees column is comma-separated, so you'll need to do "split by commas" within that field.
• Do developer search_or_add on all the emails
• Ignore any fields without the @ sign
• In the rake task, do this after code reviews and git log.

Once we get this data parsing properly, we need:

• A Sheriff model. Stores an email, start, end, and title
• The Sheriff model belongs to a Developer, Participant, and Reviewer.
• The CodeReview model should return a relation of all sheriffs, using proper joining.
• A loader that loads this csv file. For now, you can just load a csv file manually, but we can automate the downloading from GoogleDocs in the future
• Development data that has a few rows
• Verifies for our models and associations

So it's the full treatment and we'll soon have sheriff data as a first-class citizen in the product. I can help out with this but I'll give you first crack at it.

[andymeneely]

@dani5447 I've written this one up. Want to take this one?

[dani5447]

Sure!

[dani5447]

Completed the first steps of this endeavor - transferring only the calendar events we care about into a google sheet. I have some comments on that data:

• There is a random event (I highlighted it in yellow) in the Build Sheriff calendar. It's called "sheriff" but I believe it is still a Build Sheriff rotation event.
• Each calendar seems to have different times when the calendar events started to occur:
  • Build Sheriff events go back as far as 3/23/2009
  • Chrome GPU Pixel Wrangling events go back as far as 3/27/2012
  • Valgrind Sheriff events go back as far as 7/14/2010
  • Chromium Perf Sheriff Rotation events go back as far as 10/04/2011
  • Chromium Troopers Rotation events go back as far as 7/26/2011
• We will also have to take into account that for the "Attendees" column, sometimes the new sheriffs are listed by email, sometimes they're by name.

[andymeneely]

Random event - just ignore it/delete it from our sheets. Looks like a red herring.

As for the dates - yes let's just work with what we have. Maybe they added that rotation partway through.

As for the names, let's process only emails but keep the names in our data in case we ever do start disambiguating by full names. For now, just go with emails and we'll state that limitation.

[dani5447]

Alright I'm still working on the loader, but I created a Sheriff model taking care of the first two points in your last section:

• A Sheriff model. Stores an email, start, end, and title
• The Sheriff model belongs to a Developer, Participant, and Reviewer.

That leaves these points still to be done:

• The CodeReview model should return a relation of all sheriffs, using proper joining.
• A loader that loads this csv file. For now, you can just load a csv file manually, but we can automate the downloading from GoogleDocs in the future - I'm still working on this loader
• Development data that has a few rows
• Verifies for our models and associations

[dani5447]

I was doing a comparison of the events as listed on the google calendars and as they're downloaded in the excel sheets and I think gcal2excel might have done some sort of automatic conversion from email to a user name where it could find the user.

So to try to fix this, I made a Google Apps Script to get calendar events data and put it in an excel sheet. This way I can make sure that I get the emails. And this bypasses the whole issue I had earlier with the OAuth, because the script is already inside the google drive system!

It seems to work pretty well! I'll do a bit more testing and then put the script in our system and download the new data.

I'll put the script in our Google Drive and copy the script to a .txt file and put it into our files under the "data" folder. It'll be called "SheriffCalendarScraperGAppScript.txt"

In the new data I took the liberty of removing the event descriptions, and only get the attendees (not the 'owner' of the event as the gcal2excel did, because that was just a generic owner)

[dani5447]

The old excel sheet, google sheet, and instructions are still in the Google Drive. I can leave them there now for comparison purposes if you guys want to compare the data sheets. (same with the csv files in our github filesystem)

[kaylaerdmann]

Working on the sheriffs now and just saw that we've been having failing builds. I think it might have something to do with this issue: https://www.ruby-forum.com/topic/64428. It's the closest problem I've found so far and can't figure out why else it's erroring like this.