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

Last change on this file since 22 was 1, checked in by katerina, 19 years ago

Initial import

File size: 1.9 KB
Line 
1#!/usr/bin/perl
2#-------------------------------------------------------------------------#
3# Author: Simon Bailey, <simon.bailey@uibk.ac.at> #
4# #
5# table rotation script for shifting data from the main table to keep #
6# yule/samhain/beltane happy and fast. #
7# the main reason for this is that we had 62568 log entries after #
8# less than a week of real work with yule/samhain/beltane #
9# this script takes all data which is older than a day and acknowledged #
10# and inserts it into a backup table, also in the main database. #
11#-------------------------------------------------------------------------#
12
13use DBI;
14
15# Configuration Variables
16$SAMHAIN_DB='samhain';
17$SAMHAIN_SRV='localhost';
18$SAMHAIN_PRT='3306';
19$SAMHAIN_USR='samhain';
20$SAMHAIN_PWD='*******';
21
22# GMT Offset -- change this to your GMT Offset.
23# CET = +1
24$GMT_OFFSET = 1;
25
26# get the current date in GMT time, then convert it to yesterday
27($sec,$min,$hr,$day,$mon,$year,$wday,$ytag,$isdst) = gmtime(time);
28
29# year is returned as real year - 1900...
30$realYear = ($year-100+2000);
31$realMonth = ($mon+1); # month is a zero base array
32$realHour = ($hr+$GMT_OFFSET);
33$yesterday = ($day-1);
34
35$dbh = DBI->connect("DBI:mysql:database=$SAMHAIN_DB;host=$SAMHAIN_SRV;port=$SAMHAIN_PRT",$SAMHAIN_USR,$SAMHAIN_PWD);
36
37$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\';");
38$sth->execute;
39
40# print "insert done";
41
42$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\';");
43$sth->execute;
44
45# print "delete done";
Note: See TracBrowser for help on using the repository browser.