dj_setup_database.in

#!/bin/bash
# @configure_input@

# This script allows one to perform DOMjudge database setup actions.
# See usage below for possible commands and options.

set -e

BINDIR="@domserver_bindir@"
ETCDIR="@domserver_etcdir@"
WEBAPPDIR="@domserver_webappdir@"
EXAMPLEPROBDIR="@domserver_exampleprobdir@"
DATABASEDUMPDIR="@domserver_databasedumpdir@"

PASSWDFILE="$ETCDIR/dbpasswords.secret"

verbose()
{
	if [ -z "$QUIET" ]; then
		echo "$@"
	fi
}

usage() {
	cat <<EOF
Usage: $0 [option]... <command> [argument]

Commands:
  status           check database installation status
  genpass          generate DB,API,Symfony,admin password files
  create-db-users  create (empty) database and users
  update-password  update DB user database to that in 'etc/dbpasswords.secret'
  install          create database, example contest and users if not existing
  bare-install     create database, setup defaults if not existing
  uninstall        remove database users and database, INCLUDING ALL DATA!
  install-examples install examples only
  install-loadtest configure for load testing. WARNING: CREATES A LOT OF EXTRA ITEMS!
  upgrade          upgrade MySQL database schema to current version
  dump [filename]  backup the current database to file (without .sql.gz suffix)
  load [filename]  load a backup from file (without .sql.gz suffix), REMOVES ALL PREVIOUS DATA!

Options:
  -u <user>  connect to MySQL with DB admin <user>
  -p <pass>  use password <pass> for DB admin user
  -q         be (mostly) quiet
  -r         read DB admin password from prompt
  -s         connect via local socket (do not specify port)

Note: you may want to store your credentials in ~/.my.cnf in order to
not have to pass any of the options above.

EOF
}

urlencode()
{
	php -r "echo rawurlencode('$1');"
}

# This is global variable to be able to return the output from
# mysql_options() below as an array, which is not possible otherwise.
declare -a _mysql_options

mysql_options()
{
	local user pass
	_mysql_options=()

	# shellcheck disable=SC2153
	if [ -n "$DBUSER" ]; then
		_mysql_options+=('-u' "$DBUSER")
	elif [ -n "$DBA_USER" ]; then
		_mysql_options+=('-u' "$DBA_USER")
	fi
	# shellcheck disable=SC2153
	if [ -n "$PASSWD" ]; then
		_mysql_options+=("-p$PASSWD")
	elif [ -n "$DBA_PASSWD" ]; then
		_mysql_options+=("-p$DBA_PASSWD")
	elif [ -n "$PROMPT_PASSWD" ]; then
		_mysql_options+=('-p')
	fi

	_mysql_options+=('-h' "$DBHOST")

	if [ -z "$USE_SOCKET" ]; then
		_mysql_options+=("-P$DBPORT")
	fi
}

# Wrapper around mysql command to allow setting options, user, etc.
mysql()
{
	mysql_options
	command mysql "${_mysql_options[@]}" --silent --skip-column-names "$@"
}

# Quick shell hack to get a key from an INI file.
# This is not perfect and ignores sections at least!
get_ini_file_key()
{
	(
	FILE="$1"
	KEY="$2"

	grep "^[[:space:]]*${KEY}[[:space:]]*=" "$FILE" 2>/dev/null \
		| sed -r "s/^[[:space:]]*${KEY}[[:space:]]*=[[:space:]]*//"
	)
}

# Wrapper around the Symfony console to allow setting the connection and passing options
symfony_console()
{
	if [ -n "$QUIET" ]; then
		ARG="-q"
	else
		ARG=""
	fi
	DATABASE_URL=

	# If we do not have an explicit user and password set, determine them from passed arguments,
	# ~/.my.cnf or the current user

	if [ -z "$DBUSER" ]; then
		if [ -n "$DBA_USER" ]; then
			if [ -n "$PROMPT_PASSWD" ]; then
				stty -echo
				printf "Enter password: "
				read -r DBA_PASSWD
				stty echo
				printf "\n"
			fi
		fi

		# If we do not have a user or password yet, try to read it from ~/.my.cnf
		if [ -f ~/.my.cnf ]; then
			[ -z "$DBA_USER"   ] && DBA_USER=$(  get_ini_file_key ~/.my.cnf 'user')
			[ -z "$DBA_PASSWD" ] && DBA_PASSWD=$(get_ini_file_key ~/.my.cnf 'password')
		fi

		if [ -z "$DBA_USER" ]; then
			DBA_USER=$(whoami)
		fi

		if [ -n "$DBA_USER" ]; then
			user=$(urlencode "${DBA_USER}")
			host=$(urlencode "${domjudge_DBHOST}")
			db=$(urlencode "${domjudge_DBNAME}")
			if [ -n "$DBA_PASSWD" ]; then
				DATABASE_URL="mysql://$user:$(urlencode "${DBA_PASSWD}")@$host:${domjudge_DBPORT}/$db"
			else
				DATABASE_URL="mysql://$user@$host:${domjudge_DBPORT}/$db"
			fi
		fi
	fi

	if [ -n "$DATABASE_URL" ]; then
		DATABASE_URL=$DATABASE_URL ${WEBAPPDIR}/bin/console -v $ARG "$@"
	else
		${WEBAPPDIR}/bin/console -v $ARG "$@"
	fi

	# Make sure any generated cache data has the right permissions if we ran as root.
	if [ `id -u` -eq 0 ]; then
		${BINDIR}/fix_permissions
	fi
}

read_dbpasswords()
{
	if [ ! -r "$PASSWDFILE" ]; then
		echo "Error: password file '$PASSWDFILE' not found or not readable."
		echo "You may want to run: $0 genpass"
		return 1
	fi
	OLDIFS="$IFS"
	IFS=":"
	# Don't pipe $PASSWDFILE into this while loop as that spawns a
	# subshell and then variables are not retained in the original shell.
	while read -r role host db user passwd port dummy; do
		# Skip lines beginning with a '#'
		[ "x$role" != "x${role###}" ] && continue
		domjudge_DBHOST=$host
		domjudge_DBPORT=$port
		domjudge_DBNAME=$db
		domjudge_DBUSER=$user
		domjudge_PASSWD=$passwd
		DBHOST=$host
		DBNAME=$db
		DBPORT=$port
	done < "$PASSWDFILE"
	IFS="$OLDIFS"
	if [ -z "$domjudge_DBPORT" ]; then
		domjudge_DBPORT=3306
	fi
	if [ -z "$DBPORT" ]; then
		DBPORT=3306
	fi
	if [ -z "$domjudge_PASSWD" ]; then
		echo "Error: no login info found."
		return 1
	fi
	verbose "Database credentials read from '$PASSWDFILE'."
}

status()
{
	if [ ! -r "$PASSWDFILE" ]; then
		echo "Error: cannot read database password file '$PASSWDFILE'."
		return 1
	fi
	read_dbpasswords || return 1

	printf "Trying to connect to the server as DB admin: "
	mysql -e 'SELECT "success.";' || return 1

	printf "Trying to connect to the server with user '%s': " "$domjudge_DBUSER"
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD mysql -e 'SELECT "success.";' || return 1

	printf "Searching for database '%s': " "$DBNAME"
	mysql -e "USE \`$DBNAME\`; SELECT 'found.';" || return 1

	printf "Searching for data in database: "
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD \
		mysql -e "USE \`$DBNAME\`; SELECT name FROM team WHERE name = 'DOMjudge';" || return 1

	printf "MySQL server version: "
	mysql -e 'SELECT version();'
}

create_db_users()
{
	(
	# The MySQL character set and collation are hardcoded here, but
	# can be changed in the database and their configuration settings
	# in etc/domserver-config.php updated after installation.
	echo "CREATE DATABASE IF NOT EXISTS \`$DBNAME\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

	echo "CREATE USER IF NOT EXISTS '$domjudge_DBUSER'@'localhost' IDENTIFIED BY '$domjudge_PASSWD';"
	echo "GRANT SELECT, INSERT, UPDATE, DELETE ON \`$DBNAME\`.* TO '$domjudge_DBUSER'@'localhost';"

	echo "FLUSH PRIVILEGES;"
	) | mysql
	verbose "DOMjudge database and user(s) created."
}

remove_db_users()
{
	(
	echo "DROP DATABASE IF EXISTS \`$DBNAME\`;"
	echo "DROP USER IF EXISTS '$domjudge_DBUSER'@'localhost';"
	echo "FLUSH PRIVILEGES;"
	) | mysql -f
	verbose "DOMjudge database and user(s) removed."
}

update_password()
{
	read_dbpasswords
	(
    echo "ALTER USER '$domjudge_DBUSER'@'localhost' IDENTIFIED BY '$domjudge_PASSWD';"
	echo "FLUSH PRIVILEGES;"
	) | mysql
    verbose "ALTER USER '$domjudge_DBUSER'@'localhost' IDENTIFIED BY '$domjudge_PASSWD';"
	verbose "Database user password updated from credentials file."
}

install_examples()
{
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD symfony_console domjudge:load-example-data
	"$EXAMPLEPROBDIR"/generate-contest-yaml
	( cd "$EXAMPLEPROBDIR" && yes y | "$BINDIR"/import-contest )
}

uninstall_helper()
{
	read_dbpasswords
	remove_db_users
}

create_db_users_helper()
{
	read_dbpasswords
	create_db_users
	verbose "Created empty database and users."
}

create_database_dump () {
	sudo mysqldump $(mysql_options) --opt --skip-lock-tables domjudge | pv | gzip > "$DATABASEDUMPDIR/${1}.sql.gz"
}

### Script starts here ###

# Parse command-line options:
while getopts ':u:p:qrs' OPT ; do
	case "$OPT" in
		u)
			DBA_USER=$OPTARG
			;;
		p)
			DBA_PASSWD=$OPTARG
			;;
		q)
			QUIET=1
			;;
		r)
			PROMPT_PASSWD=1
			;;
		s)
			USE_SOCKET=1
			;;
		:)
			echo "Error: option '$OPTARG' requires an argument."
			usage
			exit 1
			;;
		?)
			echo "Error: unknown option '$OPTARG'."
			usage
			exit 1
			;;
		*)
			echo "Error: unknown error reading option '$OPT', value '$OPTARG'."
			usage
			exit 1
			;;
	esac
done
shift $((OPTIND-1))

case "$1" in

status)
	if status ; then
		echo "Database check successful: database and users present and accessible."
		exit 0
	else
		echo "Database status check failed."
		exit 1
	fi
	;;

genpass)
	$ETCDIR/gen_all_secrets
	;;

uninstall)
	uninstall_helper
	;;

install-examples)
	read_dbpasswords
	install_examples
	;;

install-loadtest)
	read_dbpasswords
	create_db_users
	export DB_FIRST_INSTALL=1
	symfony_console doctrine:migrations:migrate -n
	unset DB_FIRST_INSTALL
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD symfony_console domjudge:load-default-data
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD symfony_console domjudge:load-gatling-data
	;;

create-db-users)
	create_db_users_helper
	;;

update-password)
	update_password
	;;

bare-install|install)
	read_dbpasswords
	create_db_users
	export DB_FIRST_INSTALL=1
	symfony_console doctrine:migrations:migrate -n
	unset DB_FIRST_INSTALL
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD symfony_console domjudge:load-default-data
	if [ "$1" = "install" ]; then
		install_examples
		verbose "SQL structure and default/example data installed."
	else
		verbose "SQL structure and defaults installed (no sample data)."
	fi
	;;

upgrade)
	# check for legacy dbpasswords.secret content
	if grep -Eq ^team: $PASSWDFILE >/dev/null 2>&1 ; then
		echo "Warning: please remove all non-jury users from $PASSWDFILE"
		echo "You may also remove those users from MySQL."
	fi
	read_dbpasswords

	# Check if we need to upgrade the Doctrine migrations table
	if ! echo "SHOW CREATE TABLE \`doctrine_migration_versions\`" | mysql "$DBNAME" >/dev/null 2>&1; then
		symfony_console doctrine:migrations:sync-metadata-storage -n
		# shellcheck disable=SC2016
		echo 'INSERT INTO `doctrine_migration_versions`
			(version, executed_at, execution_time)
			SELECT concat("DoctrineMigrations\\\\Version", version), executed_at, 1
			FROM migration_versions;' | mysql "$DBNAME"
		echo "DROP TABLE \`migration_versions\`" | mysql "$DBNAME"
	fi

	symfony_console doctrine:migrations:migrate -n
	DBUSER=$domjudge_DBUSER PASSWD=$domjudge_PASSWD symfony_console domjudge:load-default-data
	verbose "DOMjudge database upgrade completed."
	;;

dump)
	read_dbpasswords
	DUMPNAME="$2"
	if [ -z "$DUMPNAME" ]; then
		usage
		exit 1
	fi

	if [ -f "${DATABASEDUMPDIR}/${DUMPNAME}.sql.gz" ]; then
		while true; do
			read -p "Overwrite existing database dump (y/N)? " yn
			case $yn in
				[Yy]* ) break ;;
				''|[Nn]* ) exit 0;;
			esac
		done
	fi
	create_database_dump $DUMPNAME
	exit 0
	;;

load)
	DUMPNAME="$2"
	FILE=""
	if [ -z "$DUMPNAME" ]; then
		databases=$(find "$DATABASEDUMPDIR" -name "*.sql.gz" -type f -print0)
		if [ -z "$databases" ]; then
			echo "No files with .sql.gz suffix found in '$DATABASEDUMPDIR'"
			exit 1
		fi
		ind=1
		for i in "$databases"; do
			echo "$ind) $i"
			: $((ind+=1))
		done
		while true; do
			read -p "Which database should be loaded? " db
			ind=1
			for i in "$databases"; do
				if [ "$ind" = "$db" ]; then
					FILE="$i"
					break
				fi
				: $((ind+=1))
			done
			if [ -n "$FILE" ]; then
				break
			fi
		done
	else
		FILE="$DATABASEDUMPDIR/${DUMPNAME}.sql.gz"
	fi

	if [ ! -f "${FILE}" ]; then
		echo "Error: file ${FILE} not found."
		exit 1
	fi

	uninstall_helper
	create_db_users_helper
	pv "${FILE}" | gunzip | mysql domjudge
	;;

*)
	echo "Error: Unknown subcommand '$1'"
	usage
	exit 1
	;;

esac