dbconfig.pl

sub setDBConfigure(){
$DBHost				 = "192.168.1.1";
$DBName				 = "httpd";
$DBEngine			 = "mysql";
$DBUser				 = "httpdlog";
$DBPass				 = "password";
}
1;

logfromsql.pl

#!/usr/bin/perl
###############################################################################
### AWstats Traffic log on MySQL to stdout
### 
### LogFormat:
### LogFormat = "%virtualname %host %logname %time4 %methodurl %query %code %bytesd %refererquot %uaquot"
###############################################################################
use DBI;
use strict;
use warnings;
 
use vars qw/$DBEngine $DBName $DBHost $DBUser $DBPass/;
use constant FileTableCheck		 => "tablecheck.skip";
require 'dbconfig.pl';

&setDBConfigure();

###############################################################################
### Code Starts
###
###############################################################################
# DB接続について定義 
my $dbi = DBI->connect('DBI:' . $DBEngine . ':database=' . $DBName . ';host=' . $DBHost, $DBUser, $DBPass, {
		 'AutoCommit' => 1
		,'PrintError' => 1
	});

&exportLines($dbi,$ARGV[0]);
# 接続解除する 
$dbi->disconnect();
exit;


###############################################################################
### DB Export Subroutines
###
###############################################################################
sub getSqlApache($$){
	my ($dbi,$key) = @_;
	my $sqlSelectApache = <<'EOS_SQL';
SELECT 
	virtual_host
	,remote_host
	,REPLACE(remote_user,' ','_')
	,time_stamp
	,CONCAT('"',request_method,' ',REPLACE(request_uri,' ','%20'),' ',request_protocol,'"')
	,IFNULL(REPLACE(request_args,' ','%20'),'-')
	,status
	,bytes_sent
	,CONCAT('"',IFNULL(REPLACE(referer,'"','%22'),'-'),'"')
	,CONCAT('"',IFNULL(REPLACE(agent,'"','\''),'-'),'"')
FROM access_log
WHERE time_stamp BETWEEN ? AND ? AND
---virtual_hosts
ORDER BY time_stamp
LIMIT 1000
EOS_SQL
	#TODO performance up 
	my $sqlHostnameCasesStr = '';
	my @sqlHostnameCases = ();
	push(@sqlHostnameCases,'virtual_host = \'' . $_ . '\'')
		foreach (split(/,/,&getHostnames($dbi,$key)));
	$sqlHostnameCasesStr = join(' OR ',@sqlHostnameCases);
	return '' if ($sqlHostnameCasesStr eq '');
	$sqlSelectApache =~ s/\-+virtual_hosts/($sqlHostnameCasesStr)/;
	return $sqlSelectApache;
}




sub exportLines($$){
	my ($dbi,$key) = @_;
	my $nextstatus = time();
	my $counts = 0;
	my $sqlSelectApache = &getSqlApache($dbi,$key);
	
	return if ($sqlSelectApache eq '');
	
	my $dbBaseSelection = $dbi->prepare($sqlSelectApache);
	my $laststatus = &getCurrentCursor($dbi,$key);
	$laststatus = 0 if ($laststatus eq '');
	do {
		$dbi->begin_work;
		$laststatus++;
		$dbBaseSelection->bind_param(1, $laststatus);
		$dbBaseSelection->bind_param(2, $nextstatus);
		$dbBaseSelection->execute;
		
		$counts = $dbBaseSelection->rows;
		# 最後の1件を残して行を出力する 
		my $itemcounts = 0;
		my $lastline = '';
		while(my @item = $dbBaseSelection->fetchrow_array){
			print join(' ',@item) . "\n";
			if((++$itemcounts) >= $counts){
				$laststatus = $item[3];
				$lastline = join(' ',@item);
			}
		}
		#print STDERR "$itemcounts \n";
		#print STDERR "$laststatus \n";
		#print STDERR "$lastline \n";
		# 最後の1件を処理する 
		if($counts > 0){
			# 同一時間帯のレコードをすべて吐き出す 
			$dbBaseSelection->bind_param(1, $laststatus);
			$dbBaseSelection->bind_param(2, $laststatus);
			$dbBaseSelection->execute;
			my $nextRecord = 0;
			for(my $i=0;$i<($dbBaseSelection->rows);$i++){
				# 記録されたレコードの場合 
				if($nextRecord){
					print join(' ',$dbBaseSelection->fetchrow_array) . "\n";
				} elsif(join(' ',$dbBaseSelection->fetchrow_array) eq $lastline) {
			#	print STDERR "found \n";
					$nextRecord = 1;
				}
			}
		}
		# 現在のカーソルを保存 
		&setCurrentCursor($dbi,$key,$laststatus);
		$dbi->commit;
		&flushFileHandle(*STDOUT);
		sleep(1);
		#$counts = 0;
	} while($counts == 1000);
	$dbBaseSelection->finish();
	# 最大件数分ループする 
	return;
}




###############################################################################
### DB Subroutines
###
###############################################################################

#ない場合テーブルを作成する
sub makeTable($){
	my ($dbi) = @_;
	my $sqlCreateTable = <<'EOS_SQL';
CREATE TABLE `awstats_report` (
  `ident` VARCHAR(128) NOT NULL,
  `virtualhosts` VARCHAR(256),
  `laststatus` INTEGER UNSIGNED,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME,
  `filemodified` DATETIME,
  PRIMARY KEY (`ident`)
)
ENGINE = MyISAM;
EOS_SQL
	my $fileCheckTableStatus = FileTableCheck;
	if(-e $fileCheckTableStatus){
		return;
	}
	$dbi->do($sqlCreateTable);
	if(open(my $fhCheck,$fileCheckTableStatus)){
		print $fhCheck ' ';
		close($fhCheck);
	}
}

sub getHostnames($$){
	return &selectCursorItem(shift,shift,'virtualhosts',0);
}

sub getCurrentCursor($$){
	return &selectCursorItem(shift,shift,'laststatus',1);
}

sub setCurrentCursor($$$){
	my ($dbi,$key,$value) = @_;
	my $dbSelectCursor = $dbi->prepare('UPDATE `awstats_report` SET `laststatus` = ?,`modified` = CURRENT_TIMESTAMP() WHERE `ident` = ? LIMIT 1;');
	$dbSelectCursor->bind_param(1, $value);
	$dbSelectCursor->bind_param(2, $key);
	$dbSelectCursor->execute;
	$dbSelectCursor->finish();
	return;
}


sub selectCursorItem($$$$){
	my ($dbi,$key,$col,$isUpdate) = @_;
	my $ret = '';
	my $sqlDbSelect = 'SELECT `' . $col . '` FROM `awstats_report` WHERE `ident` = ?';
	$sqlDbSelect .= ' FOR UPDATE' if ($isUpdate);
	$sqlDbSelect .= ';';
	my $dbSelectCursor = $dbi->prepare($sqlDbSelect);
	$dbSelectCursor->bind_param(1, $key);
	$dbSelectCursor->execute;
	$ret = ($dbSelectCursor->fetchrow_array)[0] if ($dbSelectCursor->rows > 0);
	$dbSelectCursor->finish();
	return $ret;
}

###############################################################################
### Utility Subroutines
###
###############################################################################

sub flushFileHandle($){
	my $old_fh = select(shift);
	my $old_dolcol = $|;
	$| = 1;
	print "";
	$| = $old_dolcol;
	select($old_fh);
}