Eve-online/Eat Market Data.pl

#!/usr/bin/perl -w

# CREATE TABLE IF NOT EXISTS `marketdata` (
#   `price` int(11) NOT NULL default '0',
#   `volRemaining` int(11) NOT NULL default '0',
#   `typeID` int(11) NOT NULL default '0',
#   `range` int(11) NOT NULL default '0',
#   `orderID` int(11) NOT NULL default '0',
#   `volEntered` int(11) NOT NULL default '0',
#   `minVolume` int(11) NOT NULL default '0',
#   `bid` varchar(8) default NULL,
#   `issued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
#   `duration` int(11) NOT NULL default '0',
#   `stationID` int(11) NOT NULL default '0',
#   `regionID` int(11) NOT NULL default '0',
#   `solarSystemID` int(11) NOT NULL default '0',
#   `jumps` int(11) NOT NULL default '0'
# ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

use strict;
use DBI qw(:sql_types);

my $database = "eve";
my $hostname = "localhost";
my $port = "3306";
my $user= "david";
my $password = "7hfmcroHtik";
my $data_file_path = "/home/david/eve_market_data";

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password)
 or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('INSERT INTO marketdata (price,volRemaining,typeID,range,orderID,volEntered,minVolume,bid,issued,duration,stationID,regionID,solarSystemID,jumps) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
 or die "Couldn't prepare statement: " . $dbh->errstr;

# Metropolis-Mexallon-2008.10.28 042933.txt
opendir (DIR, $data_file_path) or die "$!";
my @files = grep {/.*.txt/}  readdir DIR;
close DIR;

foreach my $file (@files) {
	print "found file $data_file_path/$file , preocessing\n";
	open(FH,"$data_file_path/$file") or die "$!";
	while (<FH>){
		#my ( $price,$volRemaining,$typeID,$range,$orderID,$volEntered,$minVolume,$bid,$issued,$duration,$stationID,$regionID,$solarSystemID,$jumps ) = split /,/;
		my @col = split /,/;
		next if $col[0] eq "price";
		eval {
			$sth->bind_param( 1, $col[0], SQL_INTEGER );
			$sth->bind_param( 2, $col[1], SQL_INTEGER );
			$sth->bind_param( 3, $col[2], SQL_INTEGER );
			$sth->bind_param( 4, $col[3], SQL_INTEGER );
			$sth->bind_param( 5, $col[4], SQL_INTEGER );
			$sth->bind_param( 6, $col[5], SQL_INTEGER );
			$sth->bind_param( 7, $col[6], SQL_INTEGER );
			$sth->bind_param( 8, $col[7], SQL_VARCHAR );
			$sth->bind_param( 9, $col[8], SQL_VARCHAR );
			$sth->bind_param( 10, $col[9], SQL_VARCHAR );
			$sth->bind_param( 11, $col[10], SQL_INTEGER );
			$sth->bind_param( 12, $col[11], SQL_INTEGER );
			$sth->bind_param( 13, $col[12], SQL_INTEGER );
			$sth->bind_param( 14, $col[13], SQL_INTEGER );
			$sth->execute();
		};

		if ( $@ ) {
			warn "Database error: $DBI::errstr\n";
		}
	}
	close(FH);
}
$sth->finish();
$dbh->disconnect();