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