perdition-mysql 2.1-2build1 / usr / sbin / perditiondb_mysql_makedb

#!/bin/bash
######################################################################
# makedb                                                 December 2000
# Horms                                             horms@verge.net.au
#
# Frederic Delchambre                                     October 1999
# N.T.S. / Freegates                                dedel@freegates.be
#                                             http://www.freegates.be/
#                                                   http://www.nts.be/
#
# Sample programme to create a perdition database in a MySQL RDMS
#
# perdition
# Mail retrieval proxy server MySQL support
# Copyright (C) 1999-2005  Horms, Frederic Delchambre
# 
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the
# License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software Foundation,
# Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA.
#
######################################################################

DEFAULT_DBSERVER=localhost
DEFAULT_DBNAME=dbPerdition
DEFAULT_DBUSER=perdition
DEFAULT_DBHOST=localhost
DEFAULT_DBTABLE=tblPerdition

quit () {
  stty echo
}

trap quit HUP
trap quit TERM
trap quit STOP


echo
echo "MySQL should already be running on a host. The"
echo "default host is $DEFAULT_DBSERVER. To use this host"
echo "hit return. Otherwise enter a host to connect to."
echo -n "Enter host name [$DEFAULT_DBSERVER]: " >&2
read dbserver
if [ -z "$dbserver" ]; then
  dbserver="$DEFAULT_DBSERVER"
elif [ "$dbserver" != "localhost" ]; then
  DEFAULT_DBHOST="$(hostname)"
fi

echo
echo "The MySQL root user password should already be set."
echo "It is needed to add a MySQL perdition user that will"
echo "own the perdition database."
echo -n "Enter the password for the MySQL root user: " >&2
stty -echo
read rootpw
stty echo
echo

# Dummy command to check that we can loggin
mysqladmin --user=root --password="$rootpw" --host="$dbserver" ping
if [ $? != 0 ]; then
  echo "Error cannot connect to MySQL server using given password. Bailing" >&2
  exit 1
fi

echo
echo "Perdition information will be sotored in a separeate"
echo "database within the MySQL RDBMS. The default name for"
echo "this database is $DEFAULT_DBNAME. If you want to use this"
echo "hit return, otherwise enter the name for the database."
echo -n "Enter database name [$DEFAULT_DBNAME]: "
read dbname
if [ -z "$dbname" ]; then
  dbname="$DEFAULT_DBNAME"
fi

echo
echo "Perdition information will be stored in table in the"
echo "$dbname database. The default name for this table is"
echo "$DEFAULT_DBTABLE. If you want to use this hit return,"
echo "otherwise enter the name for the table."
echo -n "Enter table name [$DEFAULT_DBTABLE]: "
read dbtable
if [ -z "$dbtable" ]; then
  dbtable="$DEFAULT_DBTABLE"
fi

echo
echo "A MySQL user, other than root should be used to access"
echo "the $dbname database. The default name for this user"
echo "is $DEFAULT_DBUSER. If you want to use this name, hit return,"
echo "otherwise enter the name for the user."
echo -n "Enter database user [$DEFAULT_DBUSER]: "
read dbuser
if [ -z "$dbuser" ]; then
  dbuser="$DEFAULT_DBUSER"
fi

while [ 1 ]; do
  echo -n "Enter a password for the $dbuser user: " >&2
  stty -echo
  read dbpw
  stty echo
  echo
  if [ -z "$dbpw" ]; then
    echo "Password is empty, please try again." >& 2
    continue
  fi
  echo -n "Enter password again to verify: " >&2
  stty -echo
  read dbpw2
  stty echo
  echo
  if [ "$dbpw" != "$dbpw2" ]; then
    echo "Passwords do not match, please try again." >& 2
    continue
  fi
  break
done

echo
echo "It is desirable to restrict access to the $dbname database"
echo "by $dbuser to only be accepted from certain hosts. The default"
echo "is $DEFAULT_DBHOST. If you want to use this, hit return."
echo "otherwise enter host that $dbuser may connect from."
echo "The host may be a hostname or IP address and may contain the"
echo "SQL wildcard characters '%' and '_'. '%' matches zero or more"
echo "charcacters. '_' matches exactly one character."
echo -n "Enter hostname  [$DEFAULT_DBHOST]: "
read dbhost
if [ -z "$dbhost" ]; then
  dbhost="$DEFAULT_DBHOST"
fi

echo
echo "Database server:          $dbserver"
echo "Database name:            $dbname"
echo "Database table:           $dbtable"
echo "Database user:            $dbuser"
echo "Connections allowed from: $dbhost"
echo -n "Proceed (May destroy existing data in database) [y/n]? " >&2
read answer
if [ "$answer" != "y" -a "$answer" != "Y" ]; then
  exit 0
fi


echo
echo -n "Droping database $dbname... "
echo "drop database if exists $dbname;" | \
  mysql --user=root --password="$rootpw" --host="$dbserver" mysql 
if [ $? != 0 ]; then
  echo
  echo "Error. Bailing" >&2
  exit 1
fi
echo " Done"


echo -n "Creating database $dbname..."
mysqladmin --user=root --password="$rootpw" --host="$dbserver" create $dbname\
  > /dev/null
if [ $? != 0 ]; then
  echo
  echo "Error, Bailing" >&2
  exit 1
fi
echo " Done"


echo -n "Granting access to $dbname for user $dbuser..."
#Create user perdition in database
mysql --user=root --password="$rootpw" --host="$dbserver" mysql <<__EOF__
  GRANT ALL PRIVILEGES ON $dbname.* TO $dbuser@$dbhost
           IDENTIFIED BY '$dbpw' WITH GRANT OPTION;
__EOF__
if [ $? != 0 ]; then
  echo "Error granting access to $dbname for $dbuser. Bailing"
  exit 1
fi
echo " Done"


echo -n "Creating table $dbtable in database $dbname..."
#Seed table with data as user perdition
mysql --user="$dbuser" --password="$dbpw" "$dbname" --host="$dbserver" << _EOF_
drop table if exists $dbtable;
create table $dbtable (
  user varchar(128) not null primary key, 
  servername varchar(255) not null, 
  port varchar(8) default null
);
create index idx${dbtable}_user on $dbtable (user);
_EOF_
if [ $? != 0 ]; then
  echo "Error creating $dbtable in $dbname. Bailing"
  exit 1
fi
echo " Done"

echo
echo
echo "You may now add entries to $dbtable in $dbname."
echo "To connect to $dbname use:"
echo
echo "mysql --user=\"$dbuser\" --password=\"****\" --host=\"$dbserver\" \"$dbname\""
echo
echo "To insert rows into $dbtable use the following once"
echo "logged into $dbname"
echo
echo "insert into $dbtable values (\"user\", \"servername\", \"port\");"
echo "where: "
echo "  user:       name of user. Up to 128 characters. May not be NULL."
echo "  servername: name of server for user. Up to 255 characters. May not be NULL."
echo "  port:       port to connect to on server. May be NULL."