# $Id$
#
# Script for adding and dropping Kamailio MySQL tables
#
# History:
# 2006-04-07  removed gen_ha1 dependency - use md5sum;
#             separated the serweb from openser tables;
#             fixed the reinstall functionality (bogdan)
# 2006-05-16  added ability to specify MD5 from a configuration file
#             FreeBSD does not have the md5sum function (norm)
# 2006-09-02  Added address table (juhe)
# 2006-10-27  subscriber table cleanup; some columns are created only if
#             serweb is installed (bogdan)
# 2007-02-28  DB migration added (bogdan)
# 2007-05-21  Move SQL database definitions out of this script (henning)
# 2007-05-31  Move common definitions to kamdbctl.base file (henningw)
# 2007-06-11  Use a common control tool for database tasks, like the kamctl

# path to the database schemas
DATA_DIR="/usr/local/share/kamailio"
if [ -d "$DATA_DIR/mysql" ] ; then
	DB_SCHEMA="$DATA_DIR/mysql"
else
	DB_SCHEMA="./mysql"
fi

#################################################################
# config vars
#################################################################

# full privileges MySQL user
if [ -z "$DBROOTUSER" ]; then
	DBROOTUSER="root"
fi

# Uncomment this to set the database root password if you want to run this 
# script without any user prompt. This is unsafe, but useful e.g. for 
# automatic testing.
#PW=""


CMD="mysql -h $DBHOST -u$DBROOTUSER "
DUMP_CMD="mysqldump -h $DBHOST -u$DBROOTUSER -c -t "
#################################################################


# read password
prompt_pw()
{
	savetty=`stty -g`
	echo -n "MySQL password for $DBROOTUSER: "
	stty -echo
	read PW
	stty $savetty
	echo
	export PW
}

# execute sql command with optional db name 
# and password parameters given
sql_query()
{
	if [ $# -gt 1 ] ; then
		if [ -n "$1" ]; then
			DB="$1" # no quoting, mysql client don't like this
		else
			DB=""
		fi
		shift
		if [ -n "$PW" ]; then
			$CMD "-p$PW" $DB -e "$@"
		else
			$CMD $DB -e "$@"
		fi
	else
		if [ -n "$PW" ]; then
			$CMD "-p$PW" "$@"
		else
			$CMD "$@"
		fi
	fi
}


openser_drop()  # pars: <database name>
{
if [ $# -ne 1 ] ; then
	merr "openser_drop function takes two params"
	exit 1
fi

sql_query "" "drop database $1;"

if [ $? -ne 0 ] ; then
	merr "Dropping database $1 failed!"
	exit 1
fi
minfo "Database $1 deleted"
}


db_charset_test()
{
	if [ -n "$PW" ]; then
		CURRCHARSET=`echo "show variables like '%character_set_server%'" | $CMD "-p$PW" | $AWK '{print $2}' | $SED -e 1d`
		ALLCHARSETS=`echo "show character set" | $CMD "-p$PW" | $AWK '{print $1}' | $SED -e 1d | $GREP -iv -e "utf8\|ucs2"`
	else
		CURRCHARSET=`echo "show variables like '%character_set_server%'" | $CMD | $AWK '{print $2}' | $SED -e 1d`
		ALLCHARSETS=`echo "show character set" | $CMD | $AWK '{print $1}' | $SED -e 1d | $GREP -iv -e "utf8\|ucs2"`
	fi

	while [ `echo "$ALLCHARSETS" | $GREP -icw $CURRCHARSET`  = "0" ]
	do
		mwarn "Your current default mysql characters set cannot be used to create DB. Please choice another one from the following list:"
		mecho "$ALLCHARSETS"
		mecho "Enter character set name: "
		read CURRCHARSET
		if [ `echo $CURRCHARSET | $GREP -cE "\w+"` = "0" ]; then
			merr "can't continue: user break"
			exit 1
		fi
	done
	CHARSET=$CURRCHARSET
}


openser_create () # pars: <database name>
{
if [ $# -ne 1 ] ; then
	merr "openser_create function takes one param"
	exit 1
fi

minfo "test server charset"

db_charset_test

minfo "creating database $1 ..."

# Users: openser is the regular user, openserro only for reading
sql_query "" "create database $1 character set $CHARSET;
	GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER' IDENTIFIED  BY '$DBRWPW';
	GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
	GRANT SELECT ON $1.* TO '$DBROUSER' IDENTIFIED BY '$DBROPW';
	GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';"


if [ $? -ne 0 ] ; then
	merr "Creating core database and grant privileges failed!"
	exit 1
fi

for TABLE in $STANDARD_MODULES; do
	mdbg "Creating core table: $TABLE"
	sql_query $1 < $DB_SCHEMA/$TABLE-create.sql
	if [ $? -ne 0 ] ; then
		merr "Creating core tables failed!"
		exit 1
	fi
done

minfo "Core Kamailio tables succesfully created."

if [ -e $DB_SCHEMA/extensions-create.sql ]
then
	minfo "Creating custom extensions tables"
	sql_query $1 < $DB_SCHEMA/extensions-create.sql
	if [ $? -ne 0 ] ; then
		merr "Creating custom extensions tables failed!"
		exit 1
	fi
fi

get_answer $INSTALL_PRESENCE_TABLES "Install presence related tables? (y/n): "
if [ "$ANSWER" = "y" ]; then
	presence_create $1
fi

get_answer $INSTALL_EXTRA_TABLES "Install tables for $EXTRA_MODULES? (y/n): "
if [ "$ANSWER" = "y" ]; then
	HAS_EXTRA="yes"
	extra_create $1
fi
} # openser_create


presence_create () # pars: <database name>
{
if [ $# -ne 1 ] ; then
	merr "presence_create function takes one param"
	exit 1
fi

minfo "creating presence tables into $1 ..."

sql_query $1 < $DB_SCHEMA/presence-create.sql

if [ $? -ne 0 ] ; then
	merr "Failed to create presence tables!"
	exit 1
fi

sql_query $1 < $DB_SCHEMA/rls-create.sql

if [ $? -ne 0 ] ; then
	merr "Failed to create rls-presence tables!"
	exit 1
fi

minfo "Presence tables succesfully created."
}  # end presence_create


extra_create () # pars: <database name>
{
if [ $# -ne 1 ] ; then
	merr "extra_create function takes one param"
	exit 1
fi

minfo "creating extra tables into $1 ..."

for TABLE in $EXTRA_MODULES; do
	mdbg "Creating extra table: $TABLE"
	sql_query $1 < $DB_SCHEMA/$TABLE-create.sql
	if [ $? -ne 0 ] ; then
		merr "Creating extra tables failed!"
		exit 1
		fi
	done
minfo "Extra tables succesfully created."
}  # end extra_create


migrate_table () # 4 paremeters (dst_table, dst_cols, src_table, src_cols)
{
if [ $# -ne 4 ] ; then
	merr "migrate_table function takes 4 params $@"
	exit 1
fi

src_cols=`echo $4 | sed s/?/$3./g `

X=`sql_query "" "INSERT into $1 ($2) SELECT $src_cols from $3;" 2>&1`

if [ $? -ne 0 ] ; then
	echo $X | $GREP "ERROR 1146" > /dev/null
	if [ $? -eq 0 ] ; then 
		echo " -- Migrating $3 to $1.....SKIPPED (no source)"
		return 0
	fi
	echo "ERROR: failed to migrate $3 to $1!!!"
	echo -n "Skip it and continue (y/n)? "
	read INPUT
	if [ "$INPUT" = "y" ] || [ "$INPUT" = "Y" ]
	then
		return 0
	fi

	exit 1;
fi

minfo " -- Migrating $3 to $1.....OK"

}

migrate_db () # 2 parameters (src_db, dst_db)
{
if [ $# -ne 2 ] ; then
	merr "migrate_db function takes 2 params"
	exit 1
fi

dst_db=$2
src_db=$1

migrate_table ${dst_db}.acc \
	"id,method,from_tag,to_tag,callid,sip_code,sip_reason,time" \
	${src_db}.acc \
	"?id,?method,?from_tag,?to_tag,?callid,?sip_code,?sip_reason,?time"

migrate_table ${dst_db}.missed_calls \
	"id,method,from_tag,to_tag,callid,sip_code,sip_reason,time" \
	${src_db}.missed_calls \
	"?id,?method,?from_tag,?to_tag,?callid,?sip_code,?sip_reason,?time"

migrate_table ${dst_db}.aliases \
	"id,username,domain,contact,expires,q,callid,cseq,last_modified,\
		flags,cflags,user_agent" \
	${src_db}.aliases \
	"?id,?username,?domain,?contact,?expires,?q,?callid,?cseq,?last_modified,\
		?flags,?cflags,?user_agent"

migrate_table ${dst_db}.dbaliases \
	"id,alias_username,alias_domain,username,domain" \
	${src_db}.dbaliases \
	"?id,?alias_username,?alias_domain,?username,?domain"

migrate_table ${dst_db}.grp \
	"id,username,domain,grp,last_modified" \
	${src_db}.grp \
	"?id,?username,?domain,?grp,?last_modified"

migrate_table ${dst_db}.re_grp \
	"id,reg_exp,group_id" \
	${src_db}.re_grp \
	"?id,?reg_exp,?group_id"

migrate_table ${dst_db}.silo \
	"id,src_addr,dst_addr,username,domain,inc_time,exp_time,snd_time,\
		ctype,body" \
	${src_db}.silo \
	"?id,?src_addr,?dst_addr,?username,?domain,?inc_time,?exp_time,?snd_time,\
		?ctype,?body"

migrate_table ${dst_db}.domain \
	"id,domain,last_modified" \
	${src_db}.domain \
	"?id,?domain,?last_modified"

migrate_table ${dst_db}.uri \
	"id,username,domain,uri_user,last_modified" \
	${src_db}.uri \
	"?id,?username,?domain,?uri_user,?last_modified"

migrate_table ${dst_db}.usr_preferences \
	"id,uuid,username,domain,attribute,type,value,last_modified" \
	${src_db}.usr_preferences \
	"?id,?uuid,?username,?domain,?attribute,?type,?value,?last_modified"

migrate_table ${dst_db}.trusted \
	"id,src_ip,proto,from_pattern,tag" \
	${src_db}.trusted \
	"?id,?src_ip,?proto,?from_pattern,?tag"

migrate_table ${dst_db}.address \
	"id,grp,ip_addr,mask,port" \
	${src_db}.address \
	"?id,?grp,?ip_addr,?mask,?port"

migrate_table ${dst_db}.speed_dial \
	"id,username,domain,sd_username,sd_domain,new_uri,\
		fname,lname,description" \
	${src_db}.speed_dial \
	"?id,?username,?domain,?sd_username,?sd_domain,?new_uri,\
		?fname,?lname,?description"

migrate_table ${dst_db}.gw \
	"id,gw_name,grp_id,ip_addr,port,uri_scheme,transport,strip,prefix" \
	${src_db}.gw \
	"?id,?gw_name,?grp_id,?ip_addr,?port,?uri_scheme,?transport,?strip,?prefix"

migrate_table ${dst_db}.gw_grp \
	"grp_id,grp_name" \
	${src_db}.gw_grp \
	"?grp_id,?grp_name"

migrate_table ${dst_db}.lcr \
	"id,prefix,from_uri,grp_id,priority" \
	${src_db}.lcr \
	"?id,?prefix,?from_uri,?grp_id,?priority"

migrate_table ${dst_db}.pdt \
	"id,sdomain,prefix,domain" \
	${src_db}.pdt \
	"?id,?sdomain,?prefix,?domain"

# migrate subscribers with no serweb support
migrate_table ${dst_db}.subscriber \
	"id,username,domain,password,ha1,ha1b,rpid" \
	${src_db}.subscriber \
	"?id,?username,?domain,?password,?ha1,?ha1b,?rpid"

if [ "$HAS_EXTRA" = "yes" ] ; then
	migrate_table ${dst_db}.cpl \
		"id,username,domain,cpl_xml,cpl_bin" \
		${src_db}.cpl \
		"?id,?username,?domain,?cpl_xml,?cpl_bin"

	migrate_table ${dst_db}.siptrace \
		"id,date,callid,traced_user,msg,method,status,fromip,toip,\
		fromtag,direction" \
		${src_db}.siptrace \
		"?id,?date,?callid,?traced_user,?msg,?method,?status,?fromip,?toip,\
		?fromtag,?direction"

	migrate_table ${dst_db}.imc_rooms \
		"id,name,domain,flag" \
		${src_db}.imc_rooms \
		"?id,?name,?domain,?flag"

	migrate_table ${dst_db}.imc_members \
		"id,username,domain,room,flag" \
		${src_db}.im_members \
		"?id,?username,?domain,?room,?flag"
fi


}  #end migrate_db()


export PW
if [ "$#" -ne 0 ] && [ "$PW" = "" ]; then
	prompt_pw
fi