commits/batch/create-commits-index-table.pl

#!/usr/bin/perl
# Copyright (c) 2006-2009 Eclipse Foundation, made available under EPL v1.0
# Contributors Bjorn Freeman-Benson, Karl Matthias
#
# usage:
# create-commits-index-table.pl
#

use strict;

use DBI;

require 'dbpassconfig'; our $db_password;
chomp( $db_password );

my $dsn = 'DBI:mysql:dashboard';
my $db_user_name = 'dashboard';
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

sub get_date {
	my($period) = @_;

	# Calculate N months ago in nasty perl date code
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time - (2629743 * $period));
	$year += 1900;
	$mon += 1;
	return sprintf("%04d%02d%02d", $year, $mon, $mday);
}

print "Dropping old table..\n";
my $sth = $dbh->prepare(qq{
DROP TABLE IF EXISTS commits_index;
});
$sth->execute();
$sth->finish();

print "Creating new table..\n";
my $date_code3 = get_date(3);
my $date_code6 = get_date(6);
my $date_code9 = get_date(9);
$sth = $dbh->prepare(qq{
CREATE TABLE commits_index (
	SELECT PROJECT, LOGIN, 3 AS PERIOD, SUM(CHANGE_SIZE) AS COUNT FROM commits 
	WHERE YEARMONTHDAY >= $date_code3
	GROUP BY PROJECT, LOGIN
);
});
$sth->execute();
$sth->finish();

print "Inserting 6 months...\n";
$sth = $dbh->prepare(qq{
INSERT INTO commits_index (
	SELECT PROJECT, LOGIN, 6 AS PERIOD, SUM(CHANGE_SIZE) AS COUNT FROM commits 
	WHERE YEARMONTHDAY >= $date_code6
	GROUP BY PROJECT, LOGIN
);
});
$sth->execute();
$sth->finish();

print "Inserting 9 months...\n";
$sth = $dbh->prepare(qq{
INSERT INTO commits_index (
	SELECT PROJECT, LOGIN, 9 AS PERIOD, SUM(CHANGE_SIZE) AS COUNT FROM commits 
	WHERE YEARMONTHDAY >= $date_code9
	GROUP BY PROJECT, LOGIN
);
});
$sth->execute();
$sth->finish();

print "Inserting inf months...\n";
$sth = $dbh->prepare(qq{
INSERT INTO commits_index (
	SELECT PROJECT, LOGIN, 999999 AS PERIOD, SUM(CHANGE_SIZE) AS COUNT FROM commits 
	GROUP BY PROJECT, LOGIN
);
});
$sth->execute();
$sth->finish();

print "Creating indices..\n";
$sth = $dbh->prepare(qq{
CREATE INDEX commits_index_index ON commits_index (PROJECT);
});
$sth->execute();
$sth->finish();

$dbh->disconnect();
print "Complete.\n";