module/mysql/scheme.sh

#!/bin/bash
#
# mysql templates
#
# Copyright 2019 Luigi Santivetti <luigi.santivetti@gmail.com>

# Permission is hereby granted, free of charge, to any person obtaining a
# copy of this software and associated documentation files (the "Software"),
# to deal in the Software without restriction, including without limitation
# the rights to use, copy, modify, merge, publish, distribute, sublicense,
# and/or sell copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice (including the next
# paragraph) shall be included in all copies or substantial portions of the
# Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
# ITS SUPPLIERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
# IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
# CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

declare -r mycnf_t="\
# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# 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; version 2 of the License.
#
# 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
#
# https://dev.mysql.com/doc/refman/${_MYSQL_DKRC_VERSION_:0:3}/en/server-system-variables.html

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]

# Always enable SSL
require_secure_transport = ON

# Certificates (use self signed)
ssl-cert=${_MYSQL_SSL_SERVER_CERT_F_}
ssl-key=${_MYSQL_SSL_SERVER_KEY_F_}
ssl-ca=${_MYSQL_SSL_SERVER_CA_F_}

# Logs
general_log = ${_MYSQL_GENERAL_LOG_}
log_output = ${_MYSQL_LOG_OUTPUT_}
slow_query_log = ${_MYSQL_SLOW_QUERY_LOG_}
long_query_time = 2
log_error_verbosity = 3
general_log_file = ${_MYSQL_CON_GENERAL_LOG_F_}
slow_query_log_file = ${_MYSQL_CON_SLOW_QUERY_LOG_F_}
log_error = ${_MYSQL_CON_ERROR_LOG_F_}

port=${_MYSQL_DB_PORT_}
explicit_defaults_for_timestamp = TRUE
#connect_timeout = 1000000
#net_write_timeout = 1000000
#wait_timeout = 1000000
#max_allowed_packet = 1024M
#interactive_timeout = 1000000
#net_buffer_length = 200M
#net_read_timeout = 1000000
#bind-address = *
#innodb_data_file_path=

#[mysqld_safe]"

declare -r initdb_t="\
CREATE DATABASE IF NOT EXISTS \`${_MYSQL_DB_NAME_}\`;

use ${_MYSQL_DB_NAME_};

CREATE TABLE IF NOT EXISTS \`${_MYSQL_DB_TABLE_USERS_}\` (
\`id\` int(10) unsigned NOT NULL AUTO_INCREMENT,
\`${_MYSQL_DB_FIELD_USERNAME_}\` varchar(20) NOT NULL,
\`group\` int(10) unsigned NOT NULL default 1,
\`status\` int(5) unsigned NOT NULL default 0,
\`email\` varchar(50) NOT NULL,
\`${_MYSQL_DB_FIELD_PASSWORD_}\` varchar(64) NOT NULL,
\`created\` datetime NOT NULL default current_timestamp,
\`updated\` timestamp NOT NULL default current_timestamp on update current_timestamp,
PRIMARY KEY (\`id\`),
UNIQUE KEY useremail (\`email\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS \`${_MYSQL_DB_TABLE_SESSIONS_}\` (
\`id\` varchar(128) NOT NULL,
\`ip_address\` varchar(45) NOT NULL,
\`timestamp\` int(10) unsigned DEFAULT 0 NOT NULL,
\`data\` blob NOT NULL,
PRIMARY KEY \`ci_sessions_id\` (\`id\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"

if [ "${mod_mode}" = "${release}" ]; then
	declare -rg dockerfile_debug_tools_t=""
else
	declare -rg dockerfile_debug_tools_t="\
# debug only
RUN apt-get -y install nmap
RUN apt-get -y install net-tools"
fi

declare -r dockerfile_t="\
ARG VERSION=${_MYSQL_DKRC_VERSION_}
FROM mysql:\$VERSION

RUN apt-get update
${dockerfile_debug_tools_t}

# Add SQL configuration file into autoloaded default location
COPY initdb.sql /docker-entrypoint-initdb.d/

# MYSQL seems quite strict about files mode
RUN mkdir -p ${_MYSQL_CON_LOG_D_} && \\
    chown mysql:mysql /var/log/mysql && \\
    touch ${_MYSQL_CON_GENERAL_LOG_F_} && \\
    chown mysql:mysql ${_MYSQL_CON_GENERAL_LOG_F_} && \\
    touch ${_MYSQL_CON_ERROR_LOG_F_} && \\
    chown mysql:mysql ${_MYSQL_CON_ERROR_LOG_F_} && \\
    touch ${_MYSQL_CON_SLOW_QUERY_LOG_F_} && \\
    chown mysql:mysql ${_MYSQL_CON_SLOW_QUERY_LOG_F_}"

declare -r mysql_cli_bang_t="\
#!/bin/bash

if (return 0 2>/dev/null); then
	echo \"You must run this script\" >&2
	return 1
fi

declare -r running_services=\\
\"sudo -E docker-compose ps --services --filter \\\"status=running\\\"\"

function source_passwd_file
{
	sudo whoami >/dev/null # cache password

	if [ ! -f \"\${PASSWD_F}\" ]; then
		echo \"error: PASSWD file not found\" >&2
		exit 1
	fi

	if ! source \"\${PASSWD_F}\"; then
		echo \"error: PASSWD file not sourced\" >&2
		exit 1
	fi
}

set -eu

case \"\$1\" in
	--help       ) cat <<EOF; exit 0
`printf \"\\033[1m%s\\033[0m\\n\" \"NAME\"`

  \${BASH_SOURCE[0]//.\//} - Mysql CLI for ${host_name}

`printf \"\\033[1m%s\\033[0m\\n\" \"USAGE\"`

  \$ \${BASH_SOURCE[0]} [ OPTION ] [ ARGS ... ]

`printf \"\\033[1m%s\\033[0m\\n\" \"ENVIRONMENT\"`

  PASSWD_F        path to file containing runtime credentials

`printf \"\\033[1m%s\\033[0m\\n\" \"OPTION\"`

  --set-status    string <email> integer <status>. Set the status
                  field for a user given its email
  --export        export mysql database to utf-8 text file
  --import        import a mysql database from an utf-8 text file
  --help          show this help

`printf \"\\033[1m%s\\033[0m\\n\" \"END\"`
EOF
		;;
	--export     )
		source_passwd_file
		declare -r now=\"\$(date +'%d%m%Y%H%M%S')\"
		declare -r backup_name=\"${_MYSQL_DKRC_CONTAINER_}_${_MYSQL_DB_NAME_}-\${now}.sql\"
		declare -r backup_path=\"${_MYSQL_BACKUP_PATH_}\"
		declare -r backup_file=\"\${backup_path}/\${backup_name}\"

		pushd \"${instance_d}\" >/dev/null || exit 1
		if eval \"\${running_services}\" | grep -q -- \"${_MYSQL_DKRC_SERVICE_}\"; then
			if ! (sudo -E docker exec ${_MYSQL_DKRC_CONTAINER_} /usr/bin/mysqldump \\
					-u root --password=\${_PASSWD_MYSQL_DB_PASSWORD_ROOT_} \\
					--databases ${_MYSQL_DB_NAME_} --result-file=\${backup_file}); then
				echo \"error: cannot export database\"
				popd >/dev/null
				exit 1
			fi
			sudo chmod 0400 ${_MYSQL_MYSQL_D_}/\${backup_name}
			suod mv ${_MYSQL_MYSQL_D_}/\${backup_name} \$(pwd)
			echo \"success: \$(pwd)/\${backup_name}\"
		else
			echo \"error: ${_MYSQL_DKRC_SERVICE_} not running\"
			popd >/dev/null
			exit 1
		fi ;;
	--import     )
		source_passwd_file
		declare -r input_sql_bkp=\"\$(realpath \"\$2\")\"
		if [ ! -f \"\${input_sql_bkp}\" ]; then
			echo \"error: \${input_sql_bkp:-undefined}: file not found\"
			exit 1
		fi

		pushd \"${instance_d}\" >/dev/null || exit 1
		if eval \"\${running_services}\" | grep -q -- \"${_MYSQL_DKRC_SERVICE_}\"; then
			if ! (sudo cat \"\${input_sql_bkp}\" | sudo -E docker exec -i ${_MYSQL_DKRC_CONTAINER_} \\
					/usr/bin/mysql -u root --password=\${_PASSWD_MYSQL_DB_PASSWORD_ROOT_} ${_MYSQL_DB_NAME_}); then
				echo \"error: cannot import database\"
				popd >/dev/null
				exit 1
			fi
			echo \"success: \${input_sql_bkp}\"
		else
			echo \"error: ${_MYSQL_DKRC_SERVICE_} not running\"
			popd >/dev/null
			exit 1
		fi ;;
	--set-status )
		source_passwd_file
		declare -r email=\"\$2\"
		declare -r status=\"\$3\"
		declare -r query=\"UPDATE users SET status = \${status} WHERE email = '\${email}'\"

		pushd \"${instance_d}\" >/dev/null || exit 1
		if eval \"\${running_services}\" | grep -q -- \"${_MYSQL_DKRC_SERVICE_}\"; then
			if ! (sudo -E docker exec -i ${_MYSQL_DKRC_CONTAINER_} \\
					/usr/bin/mysql -u root --password=\${_PASSWD_MYSQL_DB_PASSWORD_ROOT_} \\
					-D ${_MYSQL_DB_NAME_} -e \"\${query}\"); then
				echo \"error: cannot update status\"
				popd >/dev/null
				exit 1
			fi
			echo \"success: status updated\"
		else
			echo \"error: ${_MYSQL_DKRC_SERVICE_} not running\"
			popd >/dev/null
			exit 1
		fi ;;
esac"