#!/bin/bash
###
# think got it at today (95/01/02) about use `date` command to get date for deal timestamp with pgsql,
# sigh, i faint !! Do too much scripts.
###
# fixed 2005/12/31
# purpose: in order to keep only 7 days data in pgsql for maillist
# delete from table where timstamp < '2005-12-31%'
# Jan=1 31 days
# Feb=2 28 days (but it is 29 days in 2008 year,not take care about it yet)
# Mar=3 31 days
# Apr=4 30 days
# May=5 31 days
# Jun=6 30 days
# Jul=7 31 days
# Agu=8 31 days
# Sep=9 30 days
# Oct=10 31 days
# Nov=11 30 days
# Dec=12 31 days
dbname='maillist'
dbuser='maillist'
dbhost='localhost'
nowday=`date +%d`
nowmon=`date +%m`
nowyear=`date +%Y`
echo "nowday=$nowday"
echo "nowmon=$nowmon"
echo "nowyear=$nowyear"
echo "nowdate=$nowyear-$nowmon-$nowday"
nokeepday=`expr $nowday – 6`
echo "first nokeepday=$nokeepday"
if [ $nokeepday == 0 ]; then
if [ $nowmon == 05 ] || [ $nowmon == 07 ] || [ $nowmon == 10 ] || [ $nowmon == 12 ]; then
nokeepday="30"
nowmon=`expr $nowmon – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
elif [ $nowmon == 03 ]; then
nokeepday="28"
nowmon=`expr $nowmon – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
elif [ $nowmon == 01 ]; then
nowyear=`expr $nowyear – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-12-31
echo "nokeepdate=$nowyear-12-31"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
else
nokeepday=31
nowmon=`expr $nowmon – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
fi
elif [ $nokeepday -lt 0 ]; then
if [ $nowmon == 05 ] || [ $nowmon == 07 ] || [ $nowmon == 10 ] || [ $nowmon == 12 ]; then
nokeepday=`echo $nokeepday | cut -d'-' -f2`
nokeepday=`expr 30 – $nokeepday`
nowmon=`expr $nowmon – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
elif [ $nowmon == 03 ]; then
nokeepday=`echo $nokeepday | cut -d'-' -f2`
nokeepday=`expr 28 – $nokeepday`
nowmon=`expr $nowmon – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
elif [ $nowmon == 01 ]; then
nokeepday=`echo $nokeepday | cut -d'-' -f2`
nokeepday=`expr 31 – $nokeepday`
nowyear=`expr $nowyear – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-12-$nokeepday
echo "nokeepdate=$nowyear-12-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
else
nokeepday=`echo $nokeepday | cut -d'-' -f2`
nokeepday=`expr 31 – $nokeepday`
nowmon=`expr $nowmon – 1`
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
fi
else
echo "result nokeepday=$nokeepday"
nokeepdate=$nowyear-$nowmon-$nokeepday
echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
# deal alemail table
echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
# deal maillog table
echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
fi
留言