commits/batch/create_query_tables.sql

#/*******************************************************************************
# * Copyright (c) 2012 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, yearmonth as period, count(distinct revision) as count 
	from commits 
	where login != 'gerrit@eclipse.o' 
	group by project, yearmonth
	order by yearmonth;

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;

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);