#!/bin/bash
# $Id: my_patch_tables.bash 2 2023-09-15 09:19:17Z bertrand $
# Ce script permet de faire des modifications en masses sur des tables 
# MySQL/MariaDB après une mise à jour majeur de Zabbix.
database_name=$1
target_file=$2
table_charset="utf8mb4"
table_collation="utf8mb4_bin"
mysql_tool=$(which mysql)
mysql_cmdline="$mysql_tool -s"
if [[ -n $mysql_tool && -n $database_name ]]; then
	table_list=$(echo SHOW TABLES | $mysql_cmdline $database_name)
	if [[ -z $target_file ]]; then
		target_file="${database_name}_tables.sql"
	fi
	if [[ -n $table_list ]]; then
		echo -n "" > $target_file
		for table in $table_list ; do
			echo -n "Generating SQL for table $table... "
			echo "ALTER TABLE \`$table\` CONVERT TO CHARACTER SET $table_charset COLLATE $table_collation;" >> $target_file
			echo "Done"
		done
	else
		echo "empty table list"
	fi
else
	if [[ -z $mysql_tool ]]; then
		echo "$mysql_tool not found"
	fi
	if [[ -z $database_name ]]; then
		echo "usage: $0 database_name"
	fi
fi