news/batch/insert.pl

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

use strict;

use DBI;

sub writelog {
  my $line = shift;
  my $logfilename = "logs/inserts.log";
  my $date = `date`;
  chomp($date);
  open LOG, ">>$logfilename";
  print LOG $date . " " . $line . "\n";
  close LOG;
}

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

my $line;
my @header;
my %rheader;
my $count = 0;
my $rcount = 0;
my $sth;

$_ = shift;
my $batch = /\-\-batch/;
my $table = $batch ? "news_batch" : "news";

my $replysql = "UPDATE $table SET REPLYDAYS = DATEDIFF(?, DATE) WHERE MESSAGEID = ? AND (REPLYDAYS IS NULL OR DATEDIFF(?, DATE) < REPLYDAYS)";
my $rsth = $dbh->prepare( $replysql ) or writelog( "prepare error: " . $dbh->errstr );


while( $line = <STDIN> ) {
  chomp($line);
  if( $line =~ /^#(.*)/ ) {
    my $header = $1;
    @header = split /\t/, $header;
    my @questions = map( "?", @header );
    my $sql = 
"INSERT INTO $table ( " 
. join( ", ", @header )
. " ) VALUES ("
. join( ", ", @questions )
. " );";
    $sth = $dbh->prepare($sql) or writelog( "prepare error: " . $dbh->errstr );
    for( my $i = 0; $i < scalar( @header ); $i++ ) {
	$rheader{$header[$i]} = $i;
    }
  } else {
    $count++;
    my @body = split( /\t/, $line );
    if( @body == 1 ) {
      $sth->execute( $body[0] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 2 ) {
      $sth->execute( $body[0], $body[1] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 3 ) {
      $sth->execute( $body[0], $body[1], $body[2] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 4 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 5 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 6 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 7 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 8 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 9 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 10 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8], $body[9] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 11 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8], $body[9], $body[10] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 12 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8], $body[9], $body[10], $body[11] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 13 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8], $body[9], $body[10], $body[11],
                     $body[12] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 14 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8], $body[9], $body[10], $body[11],
                     $body[12], $body[13] ) or writelog( "query error: " . $sth->errstr );
    } elsif( @body == 15 ) {
      $sth->execute( $body[0], $body[1], $body[2], $body[3],
                     $body[4], $body[5], $body[6], $body[7],
                     $body[8], $body[9], $body[10], $body[11],
                     $body[12], $body[13], $body[14] ) or writelog( "query error: " . $sth->errstr );
    }
    if( $body[$rheader{'REPLYTOID'}] ne "" ) {
	$rsth->execute( $body[$rheader{'DATE'}], 
			$body[$rheader{'REPLYTOID'}],
			$body[$rheader{'DATE'}] ) or writelog( "update error: " . $sth->errstr );
	$rcount++;
    }
  }
}

$sth->finish();
$dbh->disconnect();
writelog( "inserted $count rows to $table" );
writelog( "updated $rcount reply rows in $table" );
print stderr "|";