| #!/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"; |