#!/usr/local/bin/bash

# httpd.conf:
# LogFormat "%t %v %h %{JSESSIONID}C %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %>s %D %b \"%{Referer}i\" \"%{User-Agent}i\"" timelog
# mysql table:
# CREATE TABLE IF NOT EXISTS tablename ( datetime datetime NOT NULL, hostname varchar(32) NOT NULL, url varchar(200) NOT NULL, avgsize float NOT NULL, avgtime float NOT NULL, hits int(11) NOT NULL, PRIMARY KEY (datetime,hostname,url) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

hostname=$(hostname -s)
MYSQLHOST="_HOST_"
MYSQLUSER="_USER_"
MYSQLPASS="_PASSWORD_"
MYSQLAUTH="-h${MYSQLHOST} -u${MYSQLUSER} -p${MYSQLPASS}"
MYSQLDB="_DATABASE_"
MYSQLTABLE="_TABLE_"

generateawkfile () {
unset num SEARCH PRINTF
for url in $(cat ${FILESLIST})
do
 num=$((${num:-0}+1))
 timedate=$(date -j -f "%d/%b/%Y:%T" "${1}" +"%F %H:00:00")
 SEARCH="${SEARCH}
 /${url}/    { hits_${num}++; size_${num}+=\$13; time_${num}+=\$12 }"
  PRINTF="${PRINTF}
  if (hits_${num} > 1) printf(\"\\\"${timedate}\\\";\\\"${hostname}\\\";\\\"${url}\\\";\\\"\"size_${num}/hits_${num}\"\\\";\\\"\" time_${num}/hits_${num}/1000000\"\\\";\\\"\"hits_${num}\"\\\"\\n\");"
done
echo "${SEARCH}"
echo "END {"
echo "${PRINTF}"
echo "}"
}

if [ "${#}" -lt "2" ]
then
 echo "${0} syntax:"
 echo "${0} [debug] <urllist> <logfile> [logfile]"
 exit 1
fi

if [ ${1} = "debug" ]
then
 DEBUG=y
 MYSQLARGS="--verbose"
 shift
else
 MYSQLARGS="--silent"
fi

if [ -r ${1} ]
then
 FILESLIST="${1}"
else
 echo "unable to read ${1}, exiting"
 exit 1
fi
shift

while [ ${#} -ge 1 ]
do
 unset _CAT TIMESTART
 if ! [ -r ${1} ]
 then
  echo "unable to read ${1} skipping"
 else
  if [ "${DEBUG:-n}" = "y" ];then echo "processing ${1}";TIMESTART="$(date +%s)";fi
  case $(echo ${1}|rev|cut -d '.' -f 1|rev)
  in
   bz)
    _CAT=/usr/bin/bzcat
   ;;
   gz)
    _CAT=/usr/bin/gzcat
   ;;
   *)
    _CAT=/bin/cat
   ;;
  esac
  
  for timeinfile in $(${_CAT} ${1}|awk '{ print $1 }'|grep '^\['|cut -d ':' -f 1,2|sort -n|uniq)
  do
    unset TMPAWKFILE timedate
    TMPAWKFILE=$(mktemp -t /tmp)
    generateawkfile $(echo ${timeinfile}|cut -d '[' -f 2-) > ${TMPAWKFILE}
    ${_CAT} ${1}|grep "^\\${timeinfile}" |sed -Ee s%"/{1,}"%"/"%g|awk -f ${TMPAWKFILE}
    rm ${TMPAWKFILE}
   done > /tmp/${MYSQLTABLE}.sql
  if [ "${DEBUG:-n}" = "y" ];then echo "running mysqlimport on /tmp/${MYSQLTABLE}.sql";fi
  mysqlimport ${MYSQLARGS} --local --replace --fields-terminated-by=';' --fields-enclosed-by='"' --columns=datetime,hostname,url,avgsize,avgtime,hits ${MYSQLAUTH} ${MYSQLDB} /tmp/${MYSQLTABLE}.sql
  rm /tmp/${MYSQLTABLE}.sql
  if [ "${DEBUG:-n}" = "y" ];then echo "done processing ${1} in $(($(date +%s)-${TIMESTART})) seconds";fi
 fi  
 shift
done