#!/bin/sh

# (c) 2001-2002 Ondrej Jombik <nepto@pobox.sk>
# $Platon: topzaw.sh,v 1.5 2002/03/29 14:29:30 jombik9 Exp $

# source database constants
SRC_DB_CFGFILE='db_src.cfg'
SRC_DB_HOST='atlantis.sk';
SRC_DB_USER='';
SRC_DB_PASS='';
SRC_DB_NAME='';
SRC_DB_TABLE='users';		# must be specified

# destination database constants
DEST_DB_CFGFILE='db_dest.cfg'
DEST_DB_HOST='';
DEST_DB_USER='';
DEST_DB_PASS='';
DEST_DB_NAME='';

# destination database tables prefix
TBL_PREFIX="topzaw";

# maximum difference between number of read items and number of written items
# to count execution as successful
MAX_DIFF=1;

# minimal number of written items to count execution as successful
MIN_WRITE=2000;

# log file 
LOG_FILE="topzaw.log";

# mysql
#mysql=`/usr/bin/which mysql`;
mysql="/usr/local/bin/mysql";

#############################################################################

#
# preparing logging stuff
#

if [ ! -w $LOG_FILE ]; then
	> $LOG_FILE;
fi;

#log_str=`date +"-----[ %Y-%m-%d %H:%M:%S ]-----"`;
log_str=`date +"DATE:        %Y-%m-%d %H:%M:%S"`;

#
# creating new table name and grep string
#

tbl_grep=`date +"$TBL_PREFIX""_%Y%m"`;
tbl_new_name=`date +"$TBL_PREFIX""_%Y%m%d_%H%M%S"`;

if [ -z $tbl_new_name -o -z $tbl_grep ]; then 
	log_str="$log_str\ntopzaw.sh: date failure";
	echo -e "\n$log_str";
	echo -e "$log_str\n" >> $LOG_FILE;
	exit;
fi

#
# creating mysql command line parameters
#

if [ -r "$SRC_DB_CFGFILE" ]; then
	src_mysql_params="--defaults-file=$SRC_DB_CFGFILE";
else
	src_mysql_params="-u $SRC_DB_USER -h $SRC_DB_HOST $SRC_DB_NAME";

	if [ $SRC_DB_PASS ]; then 
		src_mysql_params="$src_mysql_params -p$SRC_DB_PASS"; 
	fi
fi


if [ -r "$DEST_DB_CFGFILE" ]; then
	dest_mysql_params="--defaults-file=$DEST_DB_CFGFILE";
else
	dest_mysql_params="-u $DEST_DB_USER -h $DEST_DB_HOST $DEST_DB_NAME";

	if [ $DEST_DB_PASS ]; then
		dest_mysql_params="$dest_mysql_params -p$DEST_DB_PASS";
	fi
fi

#
# preparing list of tables for deletion
#

if [ $tbl_grep ]; then 
	tbl_remove_list=`echo "show tables" \
					| $mysql $dest_mysql_params \
					| grep "$tbl_grep"`;
fi

#
# creating new table
#

echo "create table $tbl_new_name (
  id int(10) unsigned NOT NULL auto_increment,
  username varchar(20) NOT NULL default '',
  level tinyint(3) default 0,
  total_login datetime default NULL,
  PRIMARY KEY (username),
  UNIQUE KEY id (id)
) TYPE=MyISAM;" \
	| $mysql $dest_mysql_params;

# aborting if it fails
if [ $? -ne 0 ]; then 
	log_str="$log_str\ntopzaw.sh: create table failure";
	echo -e "\n$log_str";
	echo -e "$log_str\n" >> $LOG_FILE;
	exit; 
fi

#
# copying & counting data
#

tbl_new_insert_cmd='insert into '"$tbl_new_name";
tbl_new_insert_cmd="$tbl_new_insert_cmd"' values ("$1", "$2", "$3", "$4");';

echo "select id, username, level, total_login 
from $SRC_DB_TABLE
order by total_login desc;"  \
	| $mysql $src_mysql_params \
	| grep -v ^id \
	| perl -pe \
		's/^(\d+)[\t ]+(\w+)[\t ]+(\d+)[\t ]+(.+)$/'"$tbl_new_insert_cmd"'/g;' \
	| $mysql $dest_mysql_params 
	
tbl_new_insert_exitcode=$?;

tbl_new_count_items=`echo "select count(*) from $tbl_new_name;" \
	| $mysql $dest_mysql_params \
	| grep -v ^count `

tbl_new_count_exitcode=$?;
if [ -z $tbl_new_count_items ]; then tbl_new_count_items=0; fi;


tbl_old_count_items=`echo "select count(*) from $SRC_DB_TABLE;" \
	| $mysql $src_mysql_params \
	| grep -v ^count `

tbl_old_count_exitcode=$?;
if [ -z $tbl_old_count_items ]; then tbl_old_count_items=0; fi;

#
# preparing statistics
#

stat_str='';
stat_str="$stat_str\nTBL_PREFIX:  $TBL_PREFIX";
stat_str="$stat_str\nMAX_DIFF:    $MAX_DIFF";
stat_str="$stat_str\nLOG_FILE:    $LOG_FILE";
stat_str="$stat_str\nnew table:   $tbl_new_name";
stat_str="$stat_str\ngrep:        $tbl_grep";
stat_str="$stat_str\nitems:       $tbl_old_count_items loaded,";
stat_str="$stat_str $tbl_new_count_items stored";
stat_str="$stat_str\nexitcodes:   new insert $tbl_new_insert_exitcode,";
stat_str="$stat_str old count $tbl_old_count_exitcode,";
stat_str="$stat_str new count $tbl_new_count_exitcode";
stat_str="$stat_str\nremove list:";
	
for tbl_remove_item in $tbl_remove_list; do
	stat_str="$stat_str $tbl_remove_item";
done;

stat_str="$stat_str\n";

#
# if everything went ok, remove unneccessary table(s)
#

if [ $tbl_new_count_items -ge $(($tbl_old_count_items - $MAX_DIFF)) \
	-a $tbl_new_count_items -le $(($tbl_old_count_items + $MAX_DIFF)) \
	-a $tbl_new_count_items -ge $MIN_WRITE \
	-a $tbl_new_insert_exitcode -eq 0 \
	-a $tbl_new_count_exitcode -eq 0 \
	-a $tbl_old_count_exitcode -eq 0 ]; then

	for tbl_remove_item in $tbl_remove_list; do
		echo "drop table $tbl_remove_item;" \
			| $mysql $dest_mysql_params;
	done;
	
	log_str="$log_str\ntopzaw.sh: all ok";
else
	echo "drop table $tbl_new_name;" \
		| $mysql $dest_mysql_params;

	stat_str="$stat_str\nTable $tbl_new_name was dropped, remove list kept.\n";

	log_str="$log_str""$stat_str\n";
	log_str="$log_str""topzaw.sh: database connection error or insert failure";
	echo -e "\n$log_str";
fi;

#
# write info into logfile
#

echo -e "--\n$log_str\n" >> $LOG_FILE;