#!/usr/bin/perl
# Copyright (c) 2009 AdCycle.com All rights reserved.
# http://www.adcycle.com - download the lastest version of adcycle

# Log Compress v0.1b - Sept. 9,2001 (log_compress.cgi - compresses your daily_log)


#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# WARNING!! BACKUP YOUR DATABASES BEFORE USING THIS TOOL <<<
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

use strict;
use AdMaster;
my $master=AdMaster->new("query");
my $minute=$master->{env}->get_min;
$master->{db}->connect($master,"adcycle");


if($minute > 4 && $minute <55){

	# use mysql exclusively (try to stay away from system commands) a bit more work
	my $rows_before=$master->{db}->single_result("SELECT COUNT(*) FROM daily_log");
	
	print "1. Flushing daily_log\n";
	$master->{db}->{adcycle}->do("FLUSH TABLE daily_log");

	print "2. Creating TEMP_daily_log\n";
	my $sth=$master->{db}->{adcycle}->prepare("
	CREATE TABLE TEMP_daily_log(
	GID                   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
	CID                   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
	MID                   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
	IMPR                  INT UNSIGNED NOT NULL DEFAULT 0,
	CLICK                 MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	ACTION                SMALLINT UNSIGNED NOT NULL DEFAULT 0,
	LOG_HOUR              TINYINT UNSIGNED NOT NULL DEFAULT 0,
	LOG_DATE              DATE NOT NULL DEFAULT '1900-01-01'
	)");
	$sth->execute;
	$master->{db}->{adcycle}->do("CREATE UNIQUE INDEX idxPRI on TEMP_daily_log(GID,CID,MID,LOG_DATE,LOG_HOUR)");

	print "3. Selecting data for new table.... this may take a while\n";
	$master->{db}->{adcycle}->do("
	INSERT INTO TEMP_daily_log(GID,CID,MID,IMPR,CLICK,ACTION,LOG_DATE)
	SELECT GID,CID,MID,SUM(IMPR),SUM(CLICK),SUM(ACTION),LOG_DATE
	FROM daily_log
	GROUP BY GID,CID,MID,LOG_DATE
	");

	print "4. Deleting contents of old daily_log\n";
	$master->{db}->{adcycle}->do("DELETE FROM daily_log");

	print "5. Inserting compressed data into daily_log... this may take a while\n";
	$master->{db}->{adcycle}->do("
	INSERT INTO daily_log(GID,CID,MID,IMPR,CLICK,ACTION,LOG_DATE)
	SELECT GID,CID,MID,IMPR,CLICK,ACTION,LOG_DATE
	FROM TEMP_daily_log
	");

	print "6. Dropping TEMP_daily_log table\n";
	$master->{db}->{adcycle}->do("DROP TABLE TEMP_daily_log");

	print "7. Final Flushing of daily_log\n";
	$master->{db}->{adcycle}->do("FLUSH TABLE daily_log");

	my $rows_after=$master->{db}->single_result("SELECT COUNT(*) FROM daily_log");
	my $delta=$rows_before-$rows_after;

	print "\nDone.  $delta out of $rows_before rows were axed. :)\n\n";

}else{
	print qq~It is probably not a good idea to run this program near the top of the hour. Try it again at :05 after.~;
}
$master->{db}->{adcycle}->disconnect;

# Copyright (c) 2009 AdCycle.com All rights reserved.
# http://www.adcycle.com - download the lastest version of adcycle