#!/bin/bash
#######################################################
# Quick diagnostic check for vB forums, server, database 
# character set and collation settings
# v0.0.3
#######################################################
# Written by George Liu (eva2000) vbtechsupport.com
#######################################################
# my.cnf for utf8
# default-character-set = utf8
#######################################################

#######################################################
# !!!!!!! IMPORTANT !!!!!!!!!!
# always backup your database before doing any characterset coversions
# I'd make 3 sets of backups 
#######################################################
######
# set 1 separate schema and data backups in latin1 charset 
# mysqldump --default-character-set=latin1 --skip-set-charset --no-data vbdatabasename > vbdatabasename.schema.latin1.sql
# mysqldump --default-character-set=latin1 --skip-set-charset --no-create-info vbdatabasename > vbdatabasename.data.latin1.sql
#######################################################
######
# set 2 separate schema and data backups in charset of your language which you would set 
# in /etc/my.cnf
# full list of charset at http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html
# i.e. for greek default-character-set=greek
# mysqldump --default-character-set=greek --skip-set-charset --no-data vbdatabasename > vbdatabasename.schema.greek.sql
# mysqldump --default-character-set=greek --skip-set-charset --no-create-info vbdatabasename > vbdatabasename.data.greek.sql
#######################################################
######
# set 3 mysql server default
# mysqldump --no-data vbdatabasename > vbdatabasename.schema.default.sql
# mysqldump --no-create-info vbdatabasename > vbdatabasename.data.default.sql
#######################################################
# you'll end up with 4 to 6 sql backup files named
# vbdatabasename.schema.latin1.sql
# vbdatabasename.data.latin1.sql
# vbdatabasename.schema.greek.sql
# vbdatabasename.data.greek.sql
# vbdatabasename.schema.default.sql
# vbdatabasename.data.default.sql
#######################################################
# When changing your vB forum databases character set, you will
# also need to change the mysql server default character set which
# is a global mysql server change, so you may need to convert all
# other non-vB mysql databases on your server not just your vB database
#######################################################

# answer y or n 
# is MySQL version > 5.1.xx ?
MYSQLVER='y'

# your mysql server hostname set
# in vB config.php 
# $config['MasterServer']['servername'] =
MYSQLHOST='localhost'

# mysql username/password
MYSQLUSER='yourmysqlusername'
MYSQLPASS='yourmysqlpassword'

# your database name
# vB config.php
# $config['Database']['dbname'] = 
DBNAME='yourvb_databasename'

# if you set table prefix in vB config.php
# $config['Database']['tableprefix'] =
DBPREFIX=''

# vB config.php full path
# to find what you set
# in vB admincp > Language Manage -> Edit settings -> HTML Character Set
VBCONFIGPATH='/full/path/to/your/vbforum/includes/config.php'

# How to set POSTID
# find a post preferably with short text i.e. 1 or 2 lines of text
# which has incorrectly displayed characters or create a new post with
# 1 or 2 lines of mixed english and native charset of your forum text
# note the posts' postid i.e. postid = 1
POSTID='1'

#######################################################

function funct_checkcharset {

echo ""
echo "----------------------------------------------------------------------------------------------------"
echo "Check vB config.php settings:"
echo "----------------------------------------------------------------------------------------------------"

grep -E "('dbtype'|'charset')" $VBCONFIGPATH

echo ""
echo "----------------------------------------------------------------------------------------------------"
echo "Check MySQL server's default character set and collation"
echo "----------------------------------------------------------------------------------------------------"

mysqladmin -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST var | grep -E '(character|collation)'

echo ""
echo "----------------------------------------------------------------------------------------------------"
echo "Check vB database's default character set and collation"
echo "----------------------------------------------------------------------------------------------------"

echo ""

mysql -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST -t -e "use ${DBNAME}; select languagecode, charset from ${DBNAME}.${DBPREFIX}language; SHOW TABLE STATUS LIKE '${DBPREFIX}language'\G;"

echo ""

echo ""
echo "----------------------------------------------------------------------------------------------------"
echo "Check vB database's table default character set and collation"
echo "----------------------------------------------------------------------------------------------------"

if [[ "$MYSQLVER" = [yY] ]]; then

mysql -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST -t -e "select table_name, table_collation, engine from information_schema.tables where table_schema like '${DBNAME}';"

else

mysqlshow -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST -i ${DBNAME} | grep -Ev '(+---)' | awk '{ printf "%-25s%-10s%-20s%-20s %s\n", $2, $4, $32, $33, $30 }'

fi

echo ""
echo "----------------------------------------------------------------------------------------------------"
echo "Checking to see what the actual data in table returns"
echo "----------------------------------------------------------------------------------------------------"

mysql -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST -e "select * from ${DBNAME}.${DBPREFIX}post where postid = $POSTID\G;"

echo "----------------------------------------------------------------------------------------------------"

}

funct_checkcharset