createmmdb

#!/bin/bash

usage(){
    echo "This script will configure a database, users and login profiles"
    echo "Usage: -c (create database) -u (create user) -h (help)"
}

_error_check(){
#verify success of operation
if [ "$?" != "0" ]; then
echo -e "\033[1;103;95mERROR:Please confirm inputs and try again.\033[0m"
mysql_config_editor remove --login-path=tempsetting
exit 1
fi
}

OPTIND=1
while getopts "hcu" opt ; do
    case "${opt}" in
        h) usage ;;
        c) runtype="create";;
        u) runtype="user";;
        *)
    esac
done

if [ "${*}" = "" ] ; then
    usage
fi

#create database option
if [ "$runtype" = "create" ] ; then
#get input for database creation
echo "This script will create the database used by mm on your localhost."
echo "Please enter root password for mysql:"
mysql_config_editor set --login-path=tempsetting --host=localhost --user=root --password
echo "Please enter name of DB to be created"
read -r DB_NAME

#create database
echo "CREATE DATABASE "$DB_NAME"" | mysql --login-path=tempsetting
_error_check
echo "CREATE TABLE object (objectIdentifierValueID bigint NOT NULL AUTO_INCREMENT,objectIdentifierValue varchar(1000) NOT NULL UNIQUE,objectDB_Insertion datetime NOT NULL DEFAULT NOW(),object_LastTouched datetime NOT NULL,PRIMARY KEY (objectIdentifierValueID))" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE TABLE event (eventIdentifierValue bigint NOT NULL AUTO_INCREMENT,objectIdentifierValue varchar(1000) NOT NULL,eventType varchar(100) NOT NULL,eventDateTime datetime NOT NULL DEFAULT NOW(),eventDetail varchar(30) NOT NULL,eventOutcome varchar(30),eventDetailOPT varchar(1000),eventDetailCOMPNAME varchar(50) NOT NULL,linkingAgentIdentifierValue varchar(30) NOT NULL,PRIMARY KEY (eventIdentifierValue),FOREIGN KEY (objectIdentifierValue) REFERENCES object(objectIdentifierValue))" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE TABLE fixity (fixityIdentifierValue bigint NOT NULL AUTO_INCREMENT,eventIdentifierValue bigint NOT NULL, objectIdentifierValue varchar(1000),eventDateTime datetime NOT NULL DEFAULT NOW(),eventDetail varchar(30) NOT NULL,messageDigestAlgorithm varchar (20) NOT NULL,messageDigestSOURCE varchar (1000),messageDigestPATH varchar (8000) NOT NULL,messageDigestFILENAME varchar (8000) NOT NULL,messageDigestHASH varchar (32) NOT NULL,PRIMARY KEY (fixityIdentifierValue),FOREIGN KEY (eventIdentifierValue) REFERENCES event(eventIdentifierValue), FOREIGN KEY (objectIdentifierValue) REFERENCES object(objectIdentifierValue))" | mysql --login-path=tempsetting "$DB_NAME"
echo "CREATE INDEX checksums ON fixity (messageDigestHASH)" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE TABLE ltoSchema (ltoSchemaValueID bigint NOT NULL AUTO_INCREMENT, ltoID varchar(10) NOT NULL,fileName varchar(200),filePath varchar(400),fileSize varchar(100),modifyTime varchar(40),FULLTEXT (filePath),PRIMARY KEY (ltoSchemaValueID))" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE INDEX ltoID_Index ON ltoSchema (ltoID)" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE UNIQUE INDEX lto_column_index ON ltoSchema(ltoID,fileName,filePath,fileSize,modifyTime)" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE TABLE objectCharacteristics (objectCharacteristicValueID bigint NOT NULL AUTO_INCREMENT, objectIdentifierValue varchar(1000) NOT NULL UNIQUE,mediaInfo MEDIUMTEXT,captureLog TEXT,videoFingerprint MEDIUMTEXT,videoFingerprintSorted MEDIUMTEXT,PRIMARY KEY (objectCharacteristicValueID),FOREIGN KEY (objectIdentifierValue) REFERENCES object(objectIdentifierValue))" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE TABLE fingerprints (hashNumber bigint NOT NULL AUTO_INCREMENT, objectIdentifierValue varchar(1000) NOT NULL,startframe varchar(100),endframe varchar(100),hash1 varchar(255),hash2 varchar(255),hash3 varchar(255),hash4 varchar(255),hash5 varchar(255), PRIMARY KEY (hashNumber), FOREIGN KEY (objectIdentifierValue) REFERENCES object(objectIdentifierValue))" | mysql --login-path=tempsetting "$DB_NAME"

echo "CREATE INDEX hashindex ON fingerprints (hash1(244))" | mysql --login-path=tempsetting "$DB_NAME"
_error_check
#remove root config
mysql_config_editor remove --login-path=tempsetting

#Option to continue on to user creation
echo -e "\033[1;103;95mDatabase has been created\033[0m"
echo -e "\033[1;103;95mDo You wish to create a user? Y or N?\033[0m"
read user_input
continue=$(echo "$user_input" | tr '[:upper:]' '[:lower:]')
if [ "$continue" = "y" ] ; then
    runtype="user"
fi
fi

#create user option
if [ "$runtype" = "user" ] ; then
#get input for user creation
echo "This will create a user on the mm database"
if [ -z "$DB_NAME" ] ; then
echo "Please enter the name of target database"
read -r DB_NAME
fi
echo "Please enter the name of user to be created"
read -r USER_NAME
echo "Please enter the password for the new user"
read -r USER_PASSWORD
echo "Please enter the location of user to be created"
read -r USER_HOST
echo "Please enter mysql root password"
mysql_config_editor set --login-path=tempsetting --host=localhost --user=root --password

#create user
echo "CREATE USER \""$USER_NAME"\"@\""$USER_HOST"\" IDENTIFIED BY \""$USER_PASSWORD"\"" | mysql --login-path=tempsetting
_error_check
echo "GRANT ALL PRIVILEGES ON "$DB_NAME".* TO \""$USER_NAME"\"@\""$USER_HOST"\"" | mysql --login-path=tempsetting
_error_check
echo "FLUSH PRIVILEGES" | mysql --login-path=tempsetting

#show commands to create sql login path
if [ "$USER_HOST" = "localhost" ] ; then
    db_host="localhost"
else
    db_host=$(ifconfig |grep inet | tail -n1 | cut -d ' ' -f2)
fi
echo -e "\033[1;103;95mTo finalize, run the following command on your user machine. NOTE You may wish to confirm your host IP address first!\033[0m"
echo -e "\033[1;103;95mmysql_config_editor set --login-path="$USER_NAME"_config --host="$db_host" --user="$USER_NAME" --password\033[0m"
echo -e "\033[1;103;95mFollowed by the user password: "$USER_PASSWORD"\033[0m"

echo ""
echo -e "\033[1;103;95mThen, use the following settings in mmconfig\033[0m"
echo -e "\033[1;103;95mDatabase Profile is: "$USER_NAME"_config\033[0m"
echo -e "\033[1;103;95mDatabase Name is: "$DB_NAME"\033[0m"
#remove root config
mysql_config_editor remove --login-path=tempsetting
fi