commits/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 FileHandle;

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

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

my $line;
my @header;
my $count = 0;
my $sth;

$_ = shift;
my $batch = /\-\-batch/;
my $table = $batch ? "commits_batch" : "commits";

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 );
  } else {
    $count++;
    my @body = split( /\t/, $line );
    if( @body == 1 ) {
    } 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 );
    }
  }
}

$sth = $dbh->prepare("DELETE FROM $table WHERE YEAR < 1980 LIMIT 2") or writelog( "prepare error: " . $dbh->errstr );
my $affected_rows = $sth->execute() or writelog( "query error: " . $sth->errstr );

# Debug logging to make sure we're deleting offending rows as needed
my $logfh = new FileHandle(">>deletedcount.log") or writelog("Can't open deletecount.log for writing");
if(defined($logfh)) {
	$logfh->print(scalar localtime(time) . ": " . $affected_rows . " deleted");
	$logfh->close();
}

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