module/mysql/scheme.sh - tod - Gitiles

 #!/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"
	#!/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"