#!/usr/bin/perl
# Copyright (c) 2006 Eclipse Foundation, made available under EPL v1.0
# Contributors Ward Cunningham, Bjorn Freeman-Benson
#
# usage:
# create-db.pl
#

use strict;

use DBI;

my $db_password = `cat DBPASSWORD.txt`;
chomp( $db_password );

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

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

print "Creating new table..\n";
$sth = $dbh->prepare(qq{
CREATE TABLE news (
	MESSAGEID varchar(64) primary key,
	DATE datetime not null,
	YEAR int,
	YEARMONTH int,
	YEARMONTHDAY int,
	TOPPROJECT varchar(16),
	PROJECT varchar(32),
	EMAIL varchar(64),
	NEWSGROUP varchar(32),
	REPLYTOID varchar(64),
	REPLYDAYS int,
	INDEX (YEAR),
	INDEX (YEARMONTH),
	INDEX (YEARMONTHDAY),
	INDEX (TOPPROJECT),
	INDEX (PROJECT),
	INDEX (NEWSGROUP),
	INDEX (REPLYDAYS)
);
});
$sth->execute();
$sth->finish();

print "Tables are..\n";
$sth = $dbh->prepare(qq{
SHOW TABLES;
});
$sth->execute();
while (my ($name) = 
    $sth->fetchrow_array())  # keep fetching until 
                             # there's nothing left
{
     print "$name\n";
}
$sth->finish();

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