source: trunk/scripts/samhain-scripts/samhain.table.rotate.pl@ 3

#!/usr/bin/perl
#-------------------------------------------------------------------------#
# Author: Simon Bailey, <simon.bailey@uibk.ac.at>                         #
#                                                                         #
# table rotation script for shifting data from the main table to keep     #
# yule/samhain/beltane happy and fast.                                    #
# the main reason for this is that we had 62568 log entries after         #
# less than a week of real work with yule/samhain/beltane                 #
# this script takes all data which is older than a day and acknowledged   #
# and inserts it into a backup table, also in the main database.          #
#-------------------------------------------------------------------------#

use DBI;

# Configuration Variables
$SAMHAIN_DB='samhain';
$SAMHAIN_SRV='localhost';
$SAMHAIN_PRT='3306';
$SAMHAIN_USR='samhain';
$SAMHAIN_PWD='*******';

# GMT Offset -- change this to your GMT Offset.
# CET = +1
$GMT_OFFSET = 1;

# get the current date in GMT time, then convert it to yesterday
($sec,$min,$hr,$day,$mon,$year,$wday,$ytag,$isdst) = gmtime(time);

# year is returned as real year - 1900...
$realYear = ($year-100+2000);
$realMonth = ($mon+1); # month is a zero base array
$realHour = ($hr+$GMT_OFFSET);
$yesterday = ($day-1);

$dbh = DBI->connect("DBI:mysql:database=$SAMHAIN_DB;host=$SAMHAIN_SRV;port=$SAMHAIN_PRT",$SAMHAIN_USR,$SAMHAIN_PWD);

$sth = $dbh->prepare("INSERT INTO log_backup SELECT * FROM log WHERE ((log_ref IS NULL AND entry_status!=\'NEW\') OR (log_ref IS NOT NULL)) AND log_time < \'$realYear-$realMonth-$yesterday $realHour:00:00\';");
$sth->execute;

# print "insert done";

$sth = $dbh->prepare("DELETE FROM log WHERE ((log_ref IS NULL AND entry_status!=\'NEW\') OR (log_ref IS NOT NULL)) AND log_time < \'$realYear-$realMonth-$yesterday $realHour:00:00\';");
$sth->execute;

# print "delete done";