utils/kamctl/kamdbctl.mysql
107258eb
 #
860c33c5
 # Script for adding and dropping Kamailio MySQL tables
107258eb
 #
 # History:
7a9eeff1
 # 2006-04-07  removed gen_ha1 dependency - use md5sum;
e102ae72
 #             separated the serweb from kamailio tables;
7a9eeff1
 #             fixed the reinstall functionality (bogdan)
bf458268
 # 2006-05-16  added ability to specify MD5 from a configuration file
 #             FreeBSD does not have the md5sum function (norm)
f8f47da2
 # 2006-09-02  Added address table (juhe)
2a679bc0
 # 2006-10-27  subscriber table cleanup; some columns are created only if
 #             serweb is installed (bogdan)
48dead3c
 # 2007-02-28  DB migration added (bogdan)
0ab35848
 # 2007-05-21  Move SQL database definitions out of this script (henning)
de1a4247
 # 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
b2750804
 
033f259f
 # path to the database schemas
860c33c5
 DATA_DIR="/usr/local/share/kamailio"
0ab35848
 if [ -d "$DATA_DIR/mysql" ] ; then
 	DB_SCHEMA="$DATA_DIR/mysql"
 else
 	DB_SCHEMA="./mysql"
 fi
 
107258eb
 #################################################################
 # config vars
 #################################################################
b2750804
 
107258eb
 # full privileges MySQL user
bed32f67
 if [ -z "$DBROOTUSER" ]; then
 	DBROOTUSER="root"
107258eb
 fi
 
31380513
 # Set DBROOTPW in kamctlrc or via next line 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.
 #DBROOTPW=""
552d35b0
 
 
18e3327b
 if [ -z "$DBPORT" ] ; then
 	CMD="mysql -h $DBHOST -u$DBROOTUSER "
 	DUMP_CMD="mysqldump -h $DBHOST -u$DBROOTUSER -c -t "
 else
 	CMD="mysql -h $DBHOST -P $DBPORT -u$DBROOTUSER "
 	DUMP_CMD="mysqldump -h $DBHOST -P $DBPORT -u$DBROOTUSER -c -t "
 fi
 
107258eb
 #################################################################
 
 
 # read password
 prompt_pw()
 {
 	savetty=`stty -g`
96a2629b
 	echo -n "MySQL password for $DBROOTUSER: "
107258eb
 	stty -echo
31380513
 	read DBROOTPW
107258eb
 	stty $savetty
 	echo
31380513
 	export DBROOTPW
107258eb
 }
 
31380513
 # execute sql command with optional db name
0ab35848
 # and password parameters given
107258eb
 sql_query()
 {
0ab35848
 	if [ $# -gt 1 ] ; then
 		if [ -n "$1" ]; then
 			DB="$1" # no quoting, mysql client don't like this
 		else
 			DB=""
 		fi
 		shift
31380513
 		if [ -n "$DBROOTPW" ]; then
 			$CMD "-p$DBROOTPW" $DB -e "$@"
0ab35848
 		else
 			$CMD $DB -e "$@"
 		fi
48dead3c
 	else
31380513
 		if [ -n "$DBROOTPW" ]; then
 			$CMD "-p$DBROOTPW" "$@"
0ab35848
 		else
 			$CMD "$@"
 		fi
48dead3c
 	fi
107258eb
 }
 
7a9eeff1
 
e102ae72
 kamailio_drop()  # pars: <database name>
107258eb
 {
71d1f9b2
 	if [ $# -ne 1 ] ; then
 		merr "kamailio_drop function takes two params"
 		exit 1
 	fi
107258eb
 
71d1f9b2
 	sql_query "" "DROP DATABASE $1;"
7a9eeff1
 
71d1f9b2
 	if [ $? -ne 0 ] ; then
 		merr "Dropping database $1 failed!"
 		exit 1
 	fi
 	minfo "Database $1 deleted"
107258eb
 }
 
03dc375e
 
201542f4
 db_charset_test()
 {
31380513
 	if [ -n "$DBROOTPW" ]; then
 		CURRCHARSET=`echo "show variables like '%character_set_server%'" | $CMD "-p$DBROOTPW" | $AWK '{print $2}' | $SED -e 1d`
 		ALLCHARSETS=`echo "show character set" | $CMD "-p$DBROOTPW" | $AWK '{print $1}' | $SED -e 1d | $GREP -iv -e "utf8\|ucs2"`
604e64c7
 	else
 		CURRCHARSET=`echo "show variables like '%character_set_server%'" | $CMD | $AWK '{print $2}' | $SED -e 1d`
40d40e5a
 		ALLCHARSETS=`echo "show character set" | $CMD | $AWK '{print $1}' | $SED -e 1d | $GREP -iv -e "utf8\|ucs2"`
604e64c7
 	fi
 
03dc375e
 	while [ `echo "$ALLCHARSETS" | $GREP -icw $CURRCHARSET`  = "0" ]
201542f4
 	do
96a2629b
 		mwarn "Your current default mysql characters set cannot be used to create DB. Please choice another one from the following list:"
 		mecho "$ALLCHARSETS"
40d40e5a
 		mecho "Enter character set name: "
201542f4
 		read CURRCHARSET
03dc375e
 		if [ `echo $CURRCHARSET | $GREP -cE "\w+"` = "0" ]; then
96a2629b
 			merr "can't continue: user break"
201542f4
 			exit 1
 		fi
 	done
03dc375e
 	CHARSET=$CURRCHARSET
201542f4
 }
 
71d1f9b2
 kamailio_db_create () # pars: <database name>
107258eb
 {
71d1f9b2
 	if [ $# -ne 1 ] ; then
 		merr "kamailio_db_create function takes one param"
 		exit 1
 	fi
107258eb
 
76553200
 	if [ "$CHARSET" = "" ]; then
 		minfo "test server charset"
 		db_charset_test
 	fi
201542f4
 
71d1f9b2
 	minfo "creating database $1 ..."
107258eb
 
1fd2b4ec
 	sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;"
107258eb
 
71d1f9b2
 	if [ $? -ne 0 ] ; then
 		merr "Creating database $1 failed!"
 		exit 1
 	fi
 }
107258eb
 
71d1f9b2
 kamailio_db_grant () # pars: <database name>
 {
 	if [ $# -ne 1 ] ; then
 		merr "kamailio_db_grant function takes one param"
 		exit 1
 	fi
 
 	minfo "granting privileges to database $1 ..."
 
 	# Users: kamailio is the regular user, kamailioro only for reading
c0879add
 	sql_query "" "CREATE USER '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
66ef7ab7
 		GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST';"
 
 	if [ "${DBRWUSER}" != "${DBROUSER}" ] ; then
 		sql_query "" "CREATE USER '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';
 			GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST';"
 	fi
71d1f9b2
 
 	if [ $? -ne 0 ] ; then
 		merr "granting privileges to database $1 failed!"
 		exit 1
 	fi
 
 	if [ "$DBHOST" != "localhost" ] ; then
c0879add
 		sql_query "" "CREATE USER '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
66ef7ab7
 			GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost';"
 		if [ "${DBRWUSER}" != "${DBROUSER}" ] ; then
 			sql_query "" "CREATE USER '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';
 				GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost';"
 		fi
71d1f9b2
 		if [ $? -ne 0 ] ; then
 			merr "granting localhost privileges to database $1 failed!"
 			exit 1
 		fi
 	fi
9a1d64bd
 
 	if [ ! -z "$DBACCESSHOST" ] ; then
c0879add
 		sql_query "" "CREATE USER '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
66ef7ab7
 			GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST';"
 		if [ "${DBRWUSER}" != "${DBROUSER}" ] ; then
 			sql_query "" "CREATE USER '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';
 				GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST';"
 		fi
9a1d64bd
 		if [ $? -ne 0 ] ; then
 			merr "granting access host privileges to database $1 failed!"
 			exit 1
 		fi
 	fi
71d1f9b2
 }
 
1fd2b4ec
 kamailio_db_revoke () # pars: <database name>
71d1f9b2
 {
1fd2b4ec
 	if [ $# -ne 1 ] ; then
 		merr "kamailio_db_revoke function takes one param"
 		exit 1
 	fi
0ab35848
 
1fd2b4ec
 	minfo "revoking privileges to database $1 ..."
71d1f9b2
 
1fd2b4ec
 	# Users: kamailio is the regular user, kamailioro only for reading
66ef7ab7
 	sql_query "" "REVOKE ALL PRIVILEGES ON $1.* FROM '${DBRWUSER}'@'$DBHOST';"
 
 	if [ "${DBRWUSER}" != "${DBROUSER}" ] ; then
 		sql_query "" "REVOKE SELECT ON $1.* FROM '${DBROUSER}'@'$DBHOST';"
 	fi
71d1f9b2
 
83fee513
 	if [ $? -ne 0 ] ; then
1fd2b4ec
 		merr "revoking privileges to database $1 failed!"
83fee513
 		exit 1
 	fi
0ab35848
 
1fd2b4ec
 	if [ "$DBHOST" != "localhost" ] ; then
66ef7ab7
 		sql_query "" "REVOKE ALL PRIVILEGES ON $1.* FROM '$DBRWUSER'@'localhost';"
 		if [ "${DBRWUSER}" != "${DBROUSER}" ] ; then
 			sql_query "" "REVOKE SELECT ON $1.* FROM '$DBROUSER'@'localhost';"
 		fi
1fd2b4ec
 		if [ $? -ne 0 ] ; then
 			merr "granting localhost privileges to database $1 failed!"
 			exit 1
 		fi
 	fi
9a1d64bd
 
 	if [ ! -z "$DBACCESSHOST" ] ; then
66ef7ab7
 		sql_query "" "REVOKE ALL PRIVILEGES ON $1.* FROM '$DBRWUSER'@'$DBACCESSHOST';"
 		if [ "${DBRWUSER}" != "${DBROUSER}" ] ; then
 			sql_query "" "REVOKE SELECT ON $1.* FROM '$DBROUSER'@'$DBACCESSHOST';"
 		fi
9a1d64bd
 		if [ $? -ne 0 ] ; then
 			merr "granting access host privileges to database $1 failed!"
 			exit 1
 		fi
 	fi
1fd2b4ec
 }
48dead3c
 
1fd2b4ec
 
 kamailio_create () # pars: <database name>
 {
 	if [ $# -ne 1 ] ; then
 		merr "kamailio_create function takes one param"
83fee513
 		exit 1
8783629a
 	fi
 
782c3259
 	if [ "$DBINITASK" = "yes" ]; then
 		get_answer "ask" "Create the database '$1'? (y/n): "
 	else
 		ANSWER="y"
 	fi
 	if [ "$ANSWER" = "y" ]; then
 		kamailio_db_create $1
 	fi
5da3724f
 
782c3259
 	if [ "$DBINITASK" = "yes" ]; then
 		get_answer "ask" "Create database users with access privileges? (y/n): "
 	else
 		ANSWER="y"
 	fi
 	if [ "$ANSWER" = "y" ]; then
 		kamailio_db_grant $1
 	fi
5da3724f
 
782c3259
 	if [ "$DBINITASK" = "yes" ]; then
 		get_answer "ask" "Create the standard database tables? (y/n): "
 	else
 		ANSWER="y"
 	fi
 	if [ "$ANSWER" = "y" ]; then
 		standard_create $1
 	fi
5da3724f
 
782c3259
 	get_answer $INSTALL_PRESENCE_TABLES "Create the presence related tables? (y/n): "
1fd2b4ec
 	if [ "$ANSWER" = "y" ]; then
 		presence_create $1
 	fi
 
782c3259
 	get_answer $INSTALL_EXTRA_TABLES "Create the tables for $EXTRA_MODULES? (y/n): "
1fd2b4ec
 	if [ "$ANSWER" = "y" ]; then
 		HAS_EXTRA="yes"
 		extra_create $1
 	fi
 
782c3259
 	get_answer $INSTALL_DBUID_TABLES "Create the tables for $DBUID_MODULES? (y/n): "
1fd2b4ec
 	if [ "$ANSWER" = "y" ]; then
 		HAS_EXTRA="yes"
 		dbuid_create $1
 	fi
 } # end kamailio_create
 
 standard_create () # pars: <database name>
5da3724f
 {
1fd2b4ec
 	if [ $# -ne 1 ] ; then
 		merr "standard_create function takes one param"
 		exit 1
 	fi
5da3724f
 
1fd2b4ec
 	minfo "creating standard tables into $1 ..."
5da3724f
 
1fd2b4ec
 	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 at $TABLE!"
 			exit 1
 		fi
 	done
5da3724f
 
571fe52f
 	minfo "Core Kamailio tables successfully created."
48dead3c
 
1fd2b4ec
 	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
 }  # end standard_create
6e5f8823
 
 
1fd2b4ec
 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 ..."
 
 	for TABLE in $PRESENCE_MODULES; do
 		mdbg "Creating presence tables for $TABLE"
 		sql_query $1 < $DB_SCHEMA/$TABLE-create.sql
 		if [ $? -ne 0 ] ; then
 			merr "Creating presence tables failed at $TABLE!"
 			exit 1
 		fi
 	done
 
571fe52f
 	minfo "Presence tables successfully created."
5da3724f
 }  # end presence_create
 
 
 extra_create () # pars: <database name>
 {
1fd2b4ec
 	if [ $# -ne 1 ] ; then
 		merr "extra_create function takes one param"
 		exit 1
 	fi
5da3724f
 
1fd2b4ec
 	minfo "creating extra tables into $1 ..."
5da3724f
 
1fd2b4ec
 	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 at $TABLE!"
 			exit 1
 			fi
 		done
571fe52f
 	minfo "Extra tables successfully created."
5da3724f
 }  # end extra_create
107258eb
 
1fd2b4ec
 dbuid_create () # pars: <database name>
 {
 	if [ $# -ne 1 ] ; then
 		merr "dbuid_create function takes one param"
 		exit 1
 	fi
 
 	minfo "creating uid tables into $1 ..."
 
 	for TABLE in $DBUID_MODULES; do
 		mdbg "Creating uid table: $TABLE"
 		sql_query $1 < $DB_SCHEMA/$TABLE-create.sql
 		if [ $? -ne 0 ] ; then
 			merr "Creating uid tables failed at $TABLE!"
 			exit 1
 			fi
 		done
571fe52f
 	minfo "UID tables successfully created."
1fd2b4ec
 }  # end uid_create
 
107258eb
 
727aa357
 kamailio_add_tables () # params: <database name> <tables group name>
 {
 	if [ $# -ne 2 ] ; then
 		merr "kamailio_add_tables function takes two params"
 		exit 1
 	fi
 
 	minfo "creating group of tables [$2] into database [$1] ..."
 
 	if [ -e $DB_SCHEMA/$2-create.sql ]
 	then
 		sql_query $1 < $DB_SCHEMA/$2-create.sql
 		if [ $? -ne 0 ] ; then
 			merr "Creating group of tables [$2] failed"
 			exit 1
 		fi
 	else
 		merr "Script for creating group of tables [$2] not found"
 		exit 1
 	fi
 }  # end kamailio_add_tables
 
107258eb
 
31380513
 export DBROOTPW
 if [ "$#" -ne 0 ] && [ "$DBROOTPW" = "" ]; then
 	if [ "$DBROOTPWSKIP" = "" ]; then
76553200
 		prompt_pw
 	fi
107258eb
 fi