#!/bin/sh

#
# Script creates necessary MySQL databases, tables and users for a
# standard RTG installation.
#

if [ $# -ne 1 ]; then
   echo 1>&2 Usage: $0 mysql_root_password 
   exit 127
fi
MYSQLBIN="@MYSQL_DIR@/bin"
ROOTPASS=$1
HOST="localhost"
RTGPASS="rtgdefault"
DATABASE="rtg"
USER="snmp"

echo ""
echo "$0 setting up MySQL database for RTG."
echo "Using MySQL binaries in $MYSQLBIN."
echo ""

# Create the necessary SQL in two /tmp files
cat <<EOT >/tmp/mysql.sql
INSERT INTO user (Host, User, Password) VALUES ('$HOST','$USER',PASSWORD("$RTGPASS"));
INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, 
Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv) 
VALUES ('$HOST','$DATABASE','$USER','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
FLUSH PRIVILEGES;
EOT

cat <<EOT >/tmp/rtg.sql
#
# Table structure for table 'router'
#

CREATE TABLE router (
  rid int(11) unsigned NOT NULL auto_increment,
  name char(120) NOT NULL default '',
  pop char(10) NOT NULL default '',
  popid tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (rid)
);

#
# Table structure for table 'interface'
#

CREATE TABLE interface (
  id int(11) unsigned NOT NULL auto_increment,
  name char(255) NOT NULL default '',
  rid int(11) NOT NULL default '0',
  speed bigint(11) default NULL,
  description char(255) default NULL,
  status enum('active','inactive') default 'active',
  PRIMARY KEY  (id)
);

#
# Table structure for table 'ifInOctets'
#

CREATE TABLE ifInOctets (
  id int(11) NOT NULL default '0',
  dtime datetime NOT NULL default '0000-00-00 00:00:00',
  counter bigint(20) NOT NULL default '0',
  KEY ifInOctets_idx (dtime)
);

#
# Table structure for table 'ifOutOctets'
#

CREATE TABLE ifOutOctets (
  id int(11) NOT NULL default '0',
  dtime datetime NOT NULL default '0000-00-00 00:00:00',
  counter bigint(20) NOT NULL default '0',
  KEY ifOutOctets_idx (dtime)
);

#
# Table structure for table 'ifInUcastPkts'
#

CREATE TABLE ifInUcastPkts (
  id int(11) NOT NULL default '0',
  dtime datetime NOT NULL default '0000-00-00 00:00:00',
  counter bigint(20) NOT NULL default '0',
  KEY ifInUcastPkts_idx (dtime)
);

#
# Table structure for table 'ifOutUcastPkts'
#

CREATE TABLE ifOutUcastPkts (
  id int(11) NOT NULL default '0',
  dtime datetime NOT NULL default '0000-00-00 00:00:00',
  counter bigint(20) NOT NULL default '0',
  KEY ifOutUcastPkts_idx (dtime)
);

#
# Table structure for table 'ifInErrors'
#

CREATE TABLE ifInErrors (
  id int(11) NOT NULL default '0',
  dtime datetime NOT NULL default '0000-00-00 00:00:00',
  counter bigint(20) NOT NULL default '0',
  KEY ifInErrors_idx (dtime)
);
EOT

echo "Adding user \"$USER\" to MySQL database..."
cat /tmp/mysql.sql | $MYSQLBIN/mysql -u root -p$ROOTPASS mysql
echo "Creating RTG database \"$DATABASE\"..."
$MYSQLBIN/mysqladmin -u root -p$ROOTPASS create $DATABASE
echo "Reloading MySQL privileges..."
$MYSQLBIN/mysqladmin -u root -p$ROOTPASS flush-privileges
echo "Creating RTG tables..."
cat /tmp/rtg.sql | $MYSQLBIN/mysql -u $USER -p$RTGPASS $DATABASE
echo "Done."