commits/capture/create_query_tables.sql

#/*******************************************************************************
# * Copyright (c) 2014 Eclipse Foundation and others.
# * All rights reserved. This program and the accompanying materials
# * are made available under the terms of the Eclipse Public License v1.0
# * which accompanies this distribution, and is available at
# * http://www.eclipse.org/legal/epl-v10.html
# *
# * Contributors:
# *    Wayne Beaton (Eclipse Foundation) - Initial implementation
# *******************************************************************************/

# Create some tables that aggregate the commit data.

drop table if exists ProjectCommitActivity;
create table ProjectCommitActivity 
	select project, concat(year(date), right(concat('00', month(date)),2)) as period, count(ref) as count 
	from GitRepo as r join GitCommit as c on r.path=c.path 
	group by project, period;

# Activity associated with a company. Note that we label those
# commits that cannot be connected to a committer as being affiliated
# with "[Committer]". Commit belonging to committers who are not 
# connected to a company are affilated with "[Unaffilated]".
# Only the last three months of activity are considered.
drop table if exists ProjectCompanyActivity;
create table ProjectCompanyActivity
	select 
		gr.project as project, 
		if(isnull(c.id), '[Contributor]', if(isnull(ca.orgName), '[Unaffiliated]', ca.orgName)) as company, 
		count(distinct gc.ref) as count 
	from GitRepo as gr 
		join GitCommit as gc on gr.path=gc.path 
		join GitCommitAuthor as gca on gc.ref=gca.ref 
		left join Committer as c on gca.email=c.email 
		left join CommitterAffiliation as ca on c.id=ca.id 
	where gc.date > now() - interval 3 month 
	group by gr.project, company;

# Not so much "committer" activity anymore; more like "author" activity.
drop table if exists ProjectCommitterActivity;
create table ProjectCommitterActivity 
	select project, name as login, '' as company, count(distinct c.ref) as count 
	from GitRepo as r join GitCommit as c on r.path=c.path 
		join GitCommitAuthor as a on c.path=a.path and c.ref=a.ref 
	where date > NOW() - INTERVAL 3 MONTH group by project, login;

#drop table if exists ProjectActivity;
#create table ProjectActivity
#	select yearmonth, count(distinct project) 
#	from commits 
#	where login != 'gerrit@eclipse.o' 
#	group by yearmonth 
#	order by yearmonth;

# Not quite ready for this one yet. We need the committer mapping.
#drop table if exists CommitterProjectActivity;
#create table CommitterProjectActivity
#	select
#		name as login, project,
#		concat(year(date), right(concat('00', month(date)),2)) as period,
#		count(distinct c.ref) as count 
#	from GitRepo as r join GitCommit as c on r.path=c.path 
#		join GitCommitAuthor as a on c.path=a.path and c.ref=a.ref
#	group by login, project, period;
#create index login_project on CommitterProjectActivity(login, project);