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;

#drop table if exists ProjectCompanyActivity;
#create table ProjectCompanyActivity
#	select project, company, count(distinct revision) as count 
#	from commits 
#	where date > NOW() - INTERVAL 3 MONTH and login != 'gerrit@eclipse.o' 
#	group by 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);