#!/bin/bash

username="playlist"
password="playlist"
mysqlcmd_db_charset="mysql --user=${username} --password=${password} chartest"
mysqlcmd="mysql --user=${username} --password=${password}"

testdata="AR%ÄR%ZZ%XÉ%XÉ F%XE, J%XÉ; P%XÉ; S"
OIFS=$IFS
IFS='%'
testarr=(${testdata})
IFS=$OIFS

# Get all CHARACTER SET
while read line
do
        csetarray+=("$(awk '{ print $1; }' <<<${line})")
done < <(${mysqlcmd} -Bse "SHOW CHARACTER SET;")

# Try all character sets
for ((i=0; i<${#csetarray[@]}; i++))
do
	# Get all valid COLLATION for this character set
	while read line
	do
		loccollation=$(awk '{ print $1; }' <<<${line})
		echo "DROP DATABASE IF EXISTS chartest;" | ${mysqlcmd}
		# CREATE database with these parameters
		echo "CREATE DATABASE chartest CHARACTER SET ${csetarray[$i]} COLLATE ${loccollation};" | ${mysqlcmd}
		# Was this correct? (Does the database exist?)
		if [ -z $(${mysqlcmd} -Bse "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'chartest'") ]; then
			continue
		fi
		# Give rights for this DATABASE
		echo "GRANT ALL PRIVILEGES ON chartest.* TO 'playlist'@'localhost';" | ${mysqlcmd}
		# CREATE table with these parameters
		# id INT NOT NULL AUTO_INCREMENT,  name TEXT
		${mysqlcmd_db_charset} -Bse "CREATE TABLE chartest (name text) CHARACTER SET ${csetarray[$i]} COLLATE ${loccollation};"
		# INSERT testdata
		OIFS=$IFS
		IFS='%'
		for data in ${testdata}
		do
			IFS=$OIFS
			${mysqlcmd_db_charset} -Bse "INSERT INTO chartest (name) VALUES ('${data}');"
		done
		IFS=$OIFS

		# Testtabel created, test with bash comparison
		error=0
		count=0
		while read dbline
		do
			echo -n "${dbline} "
			if [ ! "${dbline}" == "${testarr[${count}]}" ]; then
				echo
				echo "Character error ${csetarray[$i]} / ${loccollation}: ${dbline} != ${testarr[${count}]}"
				error=1
				break
			fi
			let "count+=1"
		done < <(${mysqlcmd_db_charset} -Bse "SELECT UPPER(name) FROM chartest;")
		element=""
		if [ ${error} -eq 1 ]; then
			continue
		fi
		while read dbline
    do
      echo -n "${dbline} "
      if [ !  "${element}" \< "${dbline}" ]; then
        echo
        echo "Sorting error ${csetarray[$i]} / ${loccollation}: ${element} < ${dbline}"
        error=1
        break
      fi
      element=${dbline}
      let "count+=1"
    done < <(${mysqlcmd_db_charset} -Bse "SELECT UPPER(name) FROM chartest ORDER BY name COLLATE ${loccollation};")

		if [ ${error} -eq 0 ]; then
			echo "No error ${csetarray[$i]} / ${loccollation}"
		fi
	done < <(${mysqlcmd} -Bse "SHOW COLLATION LIKE '${csetarray[$i]}%';")
done

exit 0