#!/bin/sh

#---------------------------------------------------------------------
# Program parameters.

# DBNAME specifies a  database name.  This is  not a filename  but the
# MySQL-level name of a database. The database will be created.  If it
# already exists it will be deleted first.

# DBUSER specifies a  database user.  This is not a Linux user account
# name  but the MySQL-level name of a database user. The user does not
# need to exist already.

# DBPASS specifies the MySQL-level password for the specified database
# user.

# DBROOTPASS specifies the  MySQL-level password for the MySQL admini-
# strator (i.e., root) user.

# DBTABLE specifies a database table name.  This should be a short and
# unique lower-case word.

DBNAME=odms
DBUSER=odms
DBPASS='redacted'
DBROOTPASS='redacted'
DBTABLE=games_table

#---------------------------------------------------------------------
# Start MySQL.

mysql -uroot -p$DBROOTPASS << END

#---------------------------------------------------------------------
# Create and select database.

drop   database if exists $DBNAME;
create database $DBNAME;
use $DBNAME;

#---------------------------------------------------------------------
# Create table.

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS $DBTABLE;
CREATE TABLE $DBTABLE (
  uuid char(50) COLLATE utf8_unicode_ci NOT NULL,
  od_version char(50) COLLATE utf8_unicode_ci NOT NULL,
  creator char(20) COLLATE utf8_unicode_ci NOT NULL,
  ip_address char(16) COLLATE utf8_unicode_ci NOT NULL,
  port int(11) NOT NULL,
  label char(50) COLLATE utf8_unicode_ci NOT NULL,
  descr text COLLATE utf8_unicode_ci NOT NULL,
  status tinyint(1) NOT NULL DEFAULT '0',
  last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY uuid (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#---------------------------------------------------------------------
# Set up database user.

grant  usage on *.* to '$DBUSER'@'localhost';
drop   user '$DBUSER'@'localhost';
create user '$DBUSER'@'localhost' identified by '$DBPASS';
grant all on $DBNAME.* TO '$DBUSER'@'localhost';

#---------------------------------------------------------------------
# End of script.

# The following label is required.

END