Код:
# $Id: opensipsdbctl.mysql 8401 2011-09-27 14:31:15Z bogdan_iancu $
#
# Script for adding and dropping OpenSIPS MySQL tables
#
# History:
# 2006-04-07 removed gen_ha1 dependency - use md5sum;
# separated the serweb from opensips 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 opensipsdbctl.base file (henningw)
# 2007-06-11 Use a common control tool for database tasks, like the opensipsctl
# path to the database schemas
DATA_DIR="/usr/local/share/opensips"
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
}
opensips_drop() # pars: <database name>
{
if [ $# -ne 1 ] ; then
merr "opensips_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 "utf8" | $GREP -iv "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 "utf8" | $GREP -iv "ucs2"`
fi
tries=0;
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"
let tries=tries+1;
mecho "Enter character set name: "
read CURRCHARSET
if [ $tries -ge 3 ]
then
merr "Check your current charset"
merr "Exiting..."
exit 1;
fi
done
CHARSET=$CURRCHARSET
}
opensips_create () # pars: <database name>
{
if [ $# -ne 1 ] ; then
merr "opensips_create function takes one param"
exit 1
fi
minfo "test server charset"
db_charset_test
minfo "creating database $1 ..."
# Users: opensips is the regular user, opensipsro only for reading
sql_query "" "create database $1 character set $CHARSET;"
if [ "$DBROOTUSER" != "$DBRWUSER" ] ; then
sql_query "" "
GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER' IDENTIFIED BY '$DBRWPW';
GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
"
fi
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 OpenSIPS 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
} # opensips_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
echo "param1=$1 param2=$2 param3=$3 param4=$4"
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,duration,setuptime,created" \
${src_db}.acc \
"?id,?method,?from_tag,?to_tag,?callid,?sip_code,?sip_reason,?time,?duration,?setuptime,?created"
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,received,path,expires,q,callid,cseq,last_modified,flags,cflags,user_agent,socket,methods" \
${src_db}.aliases \
"?id,?username,?domain,?contact,?received,?path,?expires,?q,?callid,?cseq,?last_modified,?flags,?cflags,?user_agent,?socket,?methods"
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}.address \
"id,grp,ip,mask,port,proto,pattern,context_info" \
${src_db}.address \
"?id,?grp,?ip,?mask,?port,?proto,?pattern,?context_info"
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}.pdt \
"id,sdomain,prefix,domain" \
${src_db}.pdt \
"?id,?sdomain,?prefix,?domain"
migrate_table ${dst_db}.subscriber \
"id,username,domain,password,email_address,ha1,ha1b,rpid" \
${src_db}.subscriber \
"?id,?username,?domain,?password,?email_address,?ha1,?ha1b,?rpid"
migrate_table ${dst_db}.load_balancer \
"id,group_id,dst_uri,resources,probe_mode,description" \
${src_db}.load_balancer \
"?id,?group_id,?dst_uri,?resources,?probe_mode,?description" \
migrate_table ${dst_db}.dispatcher \
"id,setid,destination,flags,weight,attrs,description" \
${src_db}.dispatcher \
"?id,?setid,?destination,?flags,?weight,?attrs,?description"
migrate_table ${dst_db}.nh_sockets \
"id,rtpproxy_sock,set_id" \
${src_db}.nh_sockets \
"?id,?rtpproxy_sock,?set_id"
migrate_table ${dst_db}.dialplan \
"id,dpid,pr,match_op,match_exp,match_len,subst_exp,repl_exp,attrs" \
${src_db}.dialplan \
"?id,?dpid,?pr,?match_op,?match_exp,?match_len,?subst_exp,?repl_exp,?attrs"
migrate_table ${dst_db}.dr_gateways \
"gwid,type,address,strip,pri_prefix,attrs,probe_mode,description" \
${src_db}.dr_gateways \
"?gwid,?type,?address,?strip,?pri_prefix,?attrs,?probe_mode,?description"
migrate_table ${dst_db}.dr_rules \
"ruleid,groupid,prefix,timerec,priority,routeid,gwlist,attrs,description" \
$src_db.dr_rules \
"?ruleid,?groupid,?prefix,?timerec,?priority,?routeid,?gwlist,?attrs,?description"
migrate_table ${dst_db}.dr_groups \
"id,username,domain,groupid,description" \
${src_db}.dr_groups \
"?id,?username,?domain,?groupid,?description"
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}.sip_trace \
"id,time_stamp,callid,traced_user,msg,method,status,fromip,toip,fromtag,direction" \
${src_db}.sip_trace \
"?id,?time_stamp,?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}.imc_members \
"?id,?username,?domain,?room,?flag"
migrate_table ${dst_db}.carrierroute \
"id,carrier,domain,scan_prefix,flags,mask,prob,strip,rewrite_host,rewrite_prefix,rewrite_suffix,description" \
${src_db}.carrierroute \
"?id,?carrier,?domain,?scan_prefix,?flags,?mask,?prob,?strip,?rewrite_host,?rewrite_prefix,?rewrite_suffix,?description"
migrate_table ${dst_db}.carrierfailureroute \
"id,carrier,domain,scan_prefix,host_name,reply_code,flags,mask,next_domain,description" \
${src_db}.carrierfailureroute \
"?id,?carrier,?domain,?scan_prefix,?host_name,?reply_code,?flags,?mask,?next_domain,?description"
migrate_table ${dst_db}.route_tree \
"id,carrier" \
${src_db}.route_tree \
"?id,?carrier"
migrate_table ${dst_db}.domainpolicy \
"id,rule,type,att,val,description" \
${src_db}.domainpolicy \
"?id,?rule,?type,?att,?val,?description"
migrate_table ${dst_db}.userblacklist \
"id,username,domain,prefix,whitelist" \
${src_db}.userblacklist \
"?id,?username,?domain,?prefix,?whitelist"
migrate_table ${dst_db}.globalblacklist \
"id,prefix,whitelist,description" \
${src_db}.globalblacklist \
"?id,?prefix,?whitelist,?description"
fi
} #end migrate_db()
export PW
if [ "$#" -ne 0 ] && [ "$PW" = "" ]; then
prompt_pw
fi
Вход
Регистрация
FAQ
Поиск
