#!/bin/bash

# mySQL helper script by 3PO


PASSWORD=""
MY_CNF="/etc/mysql/my.cnf"
[ $(grep "^NAME=" /etc/os-release |cut -d "=" -f2) == Fedora ] && MY_CNF="/etc/my.cnf"
 

rc_start ()
{
  MYSQL_START="/etc/init.d/mysql start"
  MYSQL_RESTART="/etc/init.d/mysql restart"
  EPGD_START="/etc/init.d/epgd start"
  EPGD_STOP="/etc/init.d/epgd stop"
  EPGD_RESTART="/etc/init.d/epgd restart"
}

systemd_start ()
{
  MYSQL_START="systemctl start mysqld"
  MYSQL_RESTART="systemctl restart mysqld"
  EPGD_START="systemctl epgd start"
  EPGD_STOP="systemctl epgd stop"
  EPGD_RESTART="systemctl epgd restart"
}

upstart_start ()
{
  MYSQL_START="/sbin/start mysql"
  MYSQL_RESTART="/sbin/restart mysql"
  EPGD_START="/sbin/start epgd"
  EPGD_STOP="/sbin/stop epgd"
  EPGD_RESTART="/sbin/restart epgd"
}

chkRootPwd ()
{
[ -f ~/.ssh/mysqlpasswd ] && . ~/.ssh/mysqlpasswd 
if [ ! $PASSWORD ] ; then
  echo "root Password for MySQL Database is not set!"
  echo "Exit"
  exit
else
  . ~/.ssh/mysqlpasswd
  SQL_ROOT="mysql -u root -p$PASSWORD --verbose"
fi
}

if [ "$(ps -p 1 -o comm=)" == 'systemd' ] ; then
   systemd_start
 elif [ -d /usr/lib/upstart ] ; then
   upstart_start
elif which rc > /dev/null 2>&1 ; then
   rc_start
 fi

PID_DB="$(pidof mysqld | wc -l)"
PID_EPGD="$(pidof epgd | wc -l)"
[ "$(pidof epgd)" != "" ] && EPGD_RUN="1"
[ "$(pidof mysqld)" != "" ] && MYSQL_RUN="1"
SQL_EPG2VDR="mysql -u epg2vdr -pepg -Depg2vdr --verbose"

EPGD_EXIST="$(which epgd |wc -l)"
[ "$EPGD_EXIST" -eq 0 ] && PID_EPGD="0" && EPGD_RUN="0"



DEL_DB ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
chkRootPwd
echo "DROP DATABASE epg2vdr;" |$SQL_ROOT
}

DEL_U ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
chkRootPwd
echo "DROP USER epg2vdr" |$SQL_ROOT
} 

DROP_PICS ()
{
echo "DROP TABLE images;" |$SQL_EPG2VDR
}

DEL_ALL ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
chkRootPwd
echo "DROP DATABASE epg2vdr;" |$SQL_ROOT
echo "DROP USER epg2vdr;" |$SQL_ROOT
}

DROPVIEW ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
echo "DROP VIEW eventsview;" |$SQL_EPG2VDR
[ "$EPGD_RUN" == 1 ] && $EPGD_START
}
   
DROP_ALL ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
echo "DROP TABLE events;" |$SQL_EPG2VDR
echo "DROP TABLE fileref;" |$SQL_EPG2VDR
echo "DROP TABLE images;" |$SQL_EPG2VDR
echo "DROP TABLE imagerefs;" |$SQL_EPG2VDR
echo "DROP TABLE channelmap;" |$SQL_EPG2VDR
echo "DROP TABLE episodes;" |$SQL_EPG2VDR
echo "DROP TABLE vdrs;" |$SQL_EPG2VDR
echo "DROP TABLE components;" |$SQL_EPG2VDR
echo "DROP TABLE parameters;" |$SQL_EPG2VDR
echo "DROP TABLE analyse;" |$SQL_EPG2VDR
echo "DROP TABLE snapshot;" |$SQL_EPG2VDR
echo "DROP VIEW eventsview;" |$SQL_EPG2VDR
echo "DROP PROCEDURE reverseepg;" |$SQL_EPG2VDR
echo "DROP PROCEDURE mergeepg;" |$SQL_EPG2VDR
echo "DROP FUNKTION getupdflg;" |$SQL_EPG2VDR
echo "DROP FUNKTION getcrosslvr;" |$SQL_EPG2VDR
echo "DROP FUNKTION getlvrmin;" |$SQL_EPG2VDR
[ "$EPGD_RUN" == 1 ] && $EPGD_START
CASE="DROP_PICS"
echo -e "\nDelete all EPG Pictures from epg2vdr Database?\n"
YES_NO
}


YES_NO ()
{
LOOP=0 
while [ $LOOP -eq 0 ] 
do 
  echo -en 'Are You sure? [yes/no]: ' 
  read CHOICE 
  echo -en "\n" 
 case $CHOICE in 

   [yY][eE][sS]|[yY])  
   $CASE 
   LOOP=1 ;; 
  
   [nN][oO]|[nN])
   echo -e "\nScript aborted by User\n" 
   exit  ;; 
 
   *) echo "Please enter \"yes\" or \"no\"." 
   LOOP=0;; 
 
 esac 
done 
}


case $1 in

   -new-db|new-db)
   [ "$PID_DB" -lt 1 ] && $MYSQL_START 
   chkRootPwd
   echo "CREATE DATABASE epg2vdr charset utf8;" |$SQL_ROOT
   echo "DROP FUNCTION IF EXISTS epglv;" |$SQL_ROOT
   echo "DROP FUNCTION IF EXISTS epglvr;" |$SQL_ROOT
   echo "CREATE FUNCTION epglv RETURNS INT SONAME 'mysqlepglv.so';" |$SQL_ROOT
   echo "CREATE FUNCTION epglvr RETURNS INT SONAME 'mysqlepglv.so';" |$SQL_ROOT
   exit
   ;;

   -new-u|new-u)
   [ "$PID_DB" -lt 1 ] && $MYSQL_START 
   chkRootPwd
   echo "CREATE USER 'epg2vdr'@'%' IDENTIFIED BY 'epg';" |$SQL_ROOT
   echo "GRANT ALL PRIVILEGES ON epg2vdr.* TO 'epg2vdr'@'%' IDENTIFIED BY 'epg';" |$SQL_ROOT
   echo "GRANT ALL PRIVILEGES ON epg2vdr.* TO 'epg2vdr'@'localhost' IDENTIFIED BY 'epg';" |$SQL_ROOT
   echo "FLUSH PRIVILEGES;" |$SQL_ROOT
   exit
   ;;

   -del-db|del-db)
   echo -e "\nDelete the complete epg2vdr Database?\n"
   CASE="DEL_DB"
   YES_NO
   ;;

   -del-u|del-u)
   echo -e "\nDelete the User epg2vdr?\n"
   CASE="DEL_U"
   YES_NO
   ;; 

   -del-all|del-all)
   echo -e "\nDelete the complete epg2vdr Database and User epg2vdr?\n"
   CASE="DEL_ALL"
   YES_NO
   ;;

   -show|show)
   [ "$PID_DB" -lt 1 ] && $MYSQL_START
   chkRootPwd
   echo "SELECT User,Host,Db FROM mysql.db;" |$SQL_ROOT
   echo "SHOW GRANTS FOR epg2vdr;" |$SQL_ROOT
   echo "SHOW DATABASES;" |$SQL_ROOT
   echo "SHOW TABLES;" |$SQL_EPG2VDR
   exit
   ;;

   -fix-cnf|fix-cnf)
   if [ ! -f $MY_CNF ] ; then 
     echo "$MY_CNF does not exist"
     echo "Exit"
     exit
   else
     echo "Flush LOGS" |$SQL_ROOT
     echo "RESET MASTER" |$SQL_ROOT
     sed -i $MY_CNF -e "s/^bind-address/# bind-address/"
     sed -i $MY_CNF -e "s/^log-bin/# log-bin/"
     [ "$MYSQL_RUN" == "1" ] && $MYSQL_RESTART
     [ "$EPGD_RUN" == "1" ] && $EPGD_RESTART 
   fi
   exit
   ;;
   
   -dropview|dropview)
   echo -e "\nDrop all Views form database?\n"
   CASE="DROPVIEW"
   YES_NO
   ;;
   
   -drop-all|drop-all)
   echo -e "\nClean the complete epg2vdr Database?\n"
   CASE="DROP_ALL"
   YES_NO
   ;;

   -check-events|check-events)
   echo "SELECT count(1),source FROM events group by source;" |$SQL_EPG2VDR
   exit
   ;;

   -check-pic|check-pic)
   echo "SELECT count(1) FROM images WHERE image IS NOT NULL;" |$SQL_EPG2VDR
   exit
   ;;
   
   -del-logs|del-logs)
   echo -e "\nDelete the binary Logfiles?\n"
   CASE="DEL_LOGS"
   YES_NO
   ;;
   
   -drop-pics|drop-pics)
   echo -e "\nDelete all EPG Pictures from epg2vdr Database?\n"
   CASE="DROP_PICS"
   YES_NO
   ;;

   *)
   echo ""
   echo "usage: [-new-db] [-new-u] [-del-db] [-del-u] [ -del-all] [-show] [-fix-cnf] [-dropview] [-drop-all] [-drop-pics] [-check-events] [-check-pic]"
   echo ""
   echo "	-new-db         Create new Database for epg2vdr" 
   echo "	-new-u          Create new User for epg2vdr" 
   echo "	-del-db         Delete epg2vdr Database"
   echo "	-del-u          Delete User epg2vdr"
   echo "	-del-all        Delete epg2vdr Database and User epg2vdr"
   echo "	-show           Shows GRANTS for epg2vdr and list Databases and Users"
   echo "	-fix-cnf        Disable \"bind-address\" in $MY_CNF"
   echo "	-dropview       Drop Table \"eventsview\" in Database"
   echo "	-drop-all       Drop all Tables in epg2vdr Database"
   echo "	-drop-pics      Delete all Pictures from epg2vdr Database"
   echo "	-check-events   Shows quantity of Events in Database imported per source"
   echo "	-check-pic      Shows quantity of EPG Pictures stored in Database"
   echo ""
   exit
   ;;

esac









