#!/bin/csh -f
# mysqldb
# ------------------------------------------------------------------------------
# Shell script to start, stop, and manage MySQL DB server.
# ------------------------------------------------------------------------------
# Usage:  See Usage section below or run with -h or --help option to see usage.
# Assumptions:
# Effects:
#       - Performs operations on the MySQL DB server.
# Notes:
# Implementation Notes:
# Portability Issues:
# Revision History:
#   $Log$
# ------------------------------------------------------------------------------

if ("$1" == "-h" || "$1" == "--help") then
   echo "Usage:"
   echo "  $0:t               Start MySQL client"
   echo "  $0:t start         Start MySQL server"
   echo "  $0:t stop          Stop MySQL server"
   echo "  $0:t ping          Ping the server to see if it is alive"
   echo "  $0:t status        Detailed status of the server"
   echo "  $0:t conn          Show connection counts"
   echo "  $0:t show          Show names of DBs"
   echo "  $0:t show db_name  Show tables of specified DB"
   echo "  $0:t users db_name Show defined users of specified DB"
   echo "  $0:t desc obj_name db_name"
   echo "                        Describe specified object of specified DB,"
   echo "                        defaulting to information_schema DB."
   echo "  $0:t schema db_name"
   echo "                        Show schema for specified DB."
   echo "  $0:t admin         Show help for MySQL Admin client"
   echo "  $0:t admin params  Start MySQL Admin client with specified params"
   echo "  $0:t exec db_name statement "
   echo "                        Execute specified SQL statement against "
   echo "                        specified DB"
   echo "  $0:t dump db_name  Dump DDL and INSERTs of specified DB to stdout."
   exit 1
endif

if ($#argv == 0) then 
#   title SQL
   mysql -u root -p${MYSQL_PW}
#   settitle

else if ($#argv == 1) then
   if ("$1" == "start") then
      # Use the standard /etc/rc.d script so there's no difference between
      # a manual start and one that occurs at reboot.
      # sudo mysqld_safe --console
      /etc/init.d/mysqld start
   else if ("$1" == "stop") then
      # Use the standard /etc/rc.d script so there's no difference between
      # a manual stop and one that occurs at reboot.
      # mysqladmin -u root -p${MYSQL_PW} shutdown
      /etc/init.d/mysqld stop
   else if ("$1" == "ping") then
      mysqladmin -u root -p${MYSQL_PW} ping
   else if ("$1" == "status") then
      mysqladmin -u root -p${MYSQL_PW} version status processlist extended-status variables | more
   else if ("$1" == "show")   then
      mysqlshow -u root -p${MYSQL_PW}
   else if ("$1" == "admin")  then
      mysqladmin -u root -p${MYSQL_PW} | more
   else if ("$1" == "conn") then
      shift 
      mysql -u root -p${MYSQL_PW} -t -vvv -e "show status like '%onnect%'" $*:q | more
   else
      # Pass 1 other param to mysql so that we can tolerate single 
      # mysql options like --max_allowed_packet=100M
      mysql -u root -p${MYSQL_PW} $*
   endif

else if ($#argv >= 2) then
   if ("$1" == "show") then
      shift; mysqlshow -u root -p${MYSQL_PW} $*:q
   else if ("$1" == "users")  then
      shift; mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from user" $*:q | more
   else if ("$1" == "admin")  then
      shift; mysqladmin -u root -p${MYSQL_PW} $*:q
   else if ("$1" == "exec")   then
      mysql -u root -p${MYSQL_PW} -t -vvv -e "$3 $4 $5 $6 $7 $8 $9" $2:q | more
   else if ("$1" == "dump")   then
      shift; mysqldump -u root -p${MYSQL_PW} --order-by-primary --complete-insert --skip-extended-insert --routines --triggers --databases $*:q | more
      # --order-by-primary     = Sorted is easier to diff w/other dumps
      # --complete-insert      = Include column names in INSERTS
      # --skip-extended-insert = Only one database row per INSERT
      # --routines             = Stored procedures/functions also
      # --triggers             = Triggers also (on by default anyhow)
      # --databases $*:q       = Generate CREATE DATABASE, DROP DATABASE, USE
      #                          statements for each specified database.
   else if ("$1" == "desc") then
      set DB_NAME = $3:q
      if ("$DB_NAME" == "") set DB_NAME = "information_schema"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "describe $DB_NAME.$2" | more
   else if ("$1" == "schema") then
      echo ""
      echo "*************************"
      echo "Schemas"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.schemata where '$2' like schema_name" | more

      echo ""
      echo "*************************"
      echo "Schema_Privileges"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.schema_privileges where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "mysql.db"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.db" | more

      echo ""
      echo "*************************"
      echo "Tables"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.tables where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "Table_Constraints"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.table_constraints where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "Key_Column_Usage"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.key_column_usage where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "Table_Privileges"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.table_privileges where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "mysql.Tables_Priv"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.tables_priv" | more

      echo ""
      echo "*************************"
      echo "Columns"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.columns where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "Column_Privileges"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.column_privileges where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "mysql.Columns_Priv"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.columns_priv" | more

      echo ""
      echo "*************************"
      echo "Views"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.views where '$2' like table_schema" | more

      echo ""
      echo "*************************"
      echo "Routines"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.routines where '$2' like routine_schema" | more

      echo ""
      echo "*************************"
      echo "mysql.Func"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.func" | more

      echo ""
      echo "*************************"
      echo "mysql.Proc"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.proc" | more

      echo ""
      echo "*************************"
      echo "mysql.Procs_Priv"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.procs_priv" | more

      echo ""
      echo "*************************"
      echo "Triggers"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.triggers where '$2' like trigger_schema" | more

      echo ""
      echo "*************************"
      echo "mysql.Host"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.host" | more

      echo ""
      echo "*************************"
      echo "mysql.User"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.user" | more

      echo ""
      echo "*************************"
      echo "User_Privileges"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.user_privileges" | more

      echo ""
      echo "*************************"
      echo "Statistics"
      echo "*************************"
      mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.statistics where '$2' like table_schema" | more
   else
      # Pass 2+ other params to mysql so that we can tolerate multiple
      # mysql options like --max_allowed_packet=100M
      mysql -u root -p${MYSQL_PW} $*
   endif
endif

exit 0