#!/bin/bash

DB_HOST=localhost
# COnservation des journaux en nombre de jours
RETENTION=365
SQL_DB=squid_log
TABNAME="access_log deny_log"
SQLCMD="mysql ${SQL_DB} --batch -u squid -psecret";
MONTH=$(date +%m)
YEAR=$(date +%Y)

# We rotate on the first day of a new month
if [ "$MONTH" == "1" ]; then
    MONTH=12
else
    MONTH=$(($MONTH-1))
fi

# Pad with 0
MONTH=$(printf "%02d" $MONTH)

DATE=$MONTH"_"$YEAR

for T in ${TABNAME}; do
    # create table 0
    echo "CREATE TABLE IF NOT EXISTS ${T}_0 LIKE ${T};" | $SQLCMD;

    # Rotate table
    echo "FLUSH TABLES ${T}; RENAME TABLE ${T} TO ${T}_$DATE; RENAME TABLE ${T}_0 TO ${T}" | ${SQLCMD}  >/dev/null 2>&1

    # Drop _0 table if we rotate more than two times a month
    if echo "DESCRIBE ${T}_0;" | ${SQLCMD} >/dev/null 2>&1; then
        echo "DROP TABLE ${T}_0;" | $SQLCMD
    fi

    #compress 2
    cd /var/lib/mysql/${SQL_DB}/
    echo "FLUSH TABLE ${T}_${DATE};" | $SQLCMD
    myisampack -s "${T}_${DATE}.MYI"
    myisamchk -s -rq --sort-index --analyze "${T}_${DATE}.MYI"
    echo "FLUSH TABLE ${T}_${DATE}" | $SQLCMD
done

# Now check existing table to drop olds ones
for T in $(echo "show tables" | $SQLCMD | grep -v -P "^Tables_in_"$SQL_DB | grep -v -P "^(access|deny)_log$"); do
    TMONTH=$(echo $T | perl -pe 'm/^(access|deny)_log_(\d+)_(\d+)/; print $2;exit')
    TYEAR=$(echo $T | perl -pe 'm/^(access|deny)_log_(\d+)_(\d+)/; print $3;exit')
    # Drop table if older than configured retention
    if [ "$(($(date -d "01/$MONTH/$YEAR" +%s)-$(date -d "01/$TMONTH/$TYEAR" +%s)))" -gt "$((24*3600*$RETENTION))" ]; then
        echo "DROP TABLE $T;" | $SQLCMD
    fi
done