commits/batch/create_query_tables.sql

#/*******************************************************************************
# * Copyright (c) 2012, 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.

# Moved to ../capture/create_query_tables.sql
#drop table if exists ProjectCommitActivity;
#create table ProjectCommitActivity
#	select project, yearmonth as period, count(distinct revision) as count 
#	from commits 
#	where login != 'gerrit@eclipse.o' 
#	group by project, yearmonth
#	order by yearmonth;

# Moved to ../capture/create_query_tables.sql
#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;

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

#drop table if exists CommitterProjectActivity;
#create table CommitterProjectActivity
#	select login, project, yearmonth as period, count(distinct revision) as count
#	from commits 
#	where login != 'gerrit@eclipse.o' 
#	group by project, login, yearmonth 
#	order by yearmonth;
#create index login_project on CommitterProjectActivity(login, project);