psp_mysql.sh

partysip 插件开发 用于实时在线管理

#!/bin/sh
#
# Script to manage MySql tables for antisip
#


#################################################################
# config vars
#################################################################
DBNAME=antisip
DBHOST=localhost
USERNAME=pspadmin
DEFAULT_PW=secret
ROUSER=pspadminro
RO_PW=secretro
SQL_USER="root"
CMD="mysql -h $DBHOST -u$SQL_USER "
DUMP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c -t "
BACKUP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c "
TABLE_TYPE="TYPE=MyISAM"
# user name column
USERCOL="username"

GENHA1='gen_ha1'

FOREVER="2020-05-28 21:32:15"

DEFAULT_ALIASES_EXPIRES=$FOREVER
DEFAULT_Q="1.0"
DEFAULT_CALLID="Default-Call-ID"
DEFAULT_CSEQ="42"
DEFAULT_LOCATION_EXPIRES=$FOREVER

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


usage() {
COMMAND=`basename $0`
cat <<EOF
usage: $COMMAND create
       $COMMAND drop   (!!entirely deletes tables)
       $COMMAND reinit (!!entirely deletes and than re-creates tables
       $COMMAND backup (dumps current database to stdout)
	   $COMMAND restore <file> (restores tables from a file)
       $COMMAND copy <new_db> (creates a new db from an existing one)
       $COMMAND reinstall (updates to a new ANTISIP database)

       if you want to manipulate database as other MySql user than
       root, want to change database name from default value "$DBNAME",
       or want to use other values for users and password, edit the
       "config vars" section of the command $COMMAND

EOF
} #usage


# read password
prompt_pw()
{
	savetty=`stty -g`
	printf "MySql password for $SQL_USER: "
	stty -echo
	read PW
	stty $savetty
	echo
}

# execute sql command
sql_query()
{
	$CMD "-p$PW" "$@"
}

# dump all rows
antisip_dump()  # pars: <database name>
{
	if [ $# -ne 1 ] ; then
		echo "antisip_dump function takes one param"
		exit 1
	fi
	$DUMP_CMD "-p$PW" $1
}


# copy a database to database_bak
antisip_backup() # par: <database name>
{
	if [ $# -ne 1 ] ; then
		echo  "antisip_backup function takes one param"
		exit 1
	fi
	BU=/tmp/mysql_bup.$$
	$BACKUP_CMD "-p$PW" $1 > $BU
	if [ "$?" -ne 0 ] ; then
		echo "antisip backup dump failed"
		exit 1
	fi
	sql_query <<EOF
	create database $1_bak;
EOF

	antisip_restore $1_bak $BU
	if [ "$?" -ne 0 ]; then
		echo "antisip backup/restore failed"
		rm $BU
		exit 1
	fi
}

antisip_restore() #pars: <database name> <filename>
{
if [ $# -ne 2 ] ; then
	echo "antisip_restore function takes two params"
	exit 1
fi
sql_query $1 < $2
}

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

sql_query << EOF
drop database $1;
EOF
} #antisip_drop

# read realm
prompt_realm()
{
	printf "Domain (realm) for the default user 'admin': "
	read SIP_DOMAIN
	echo
}

# calculate credentials for admin
credentials()
{
	HA1=`$GENHA1 admin $SIP_DOMAIN heslo`
	if [ $? -ne 0 ] ; then
	echo "HA1 calculation failed"
		exit 1
	fi
	HA1B=`$GENHA1 "admin@$SIP_DOMAIN" $SIP_DOMAIN heslo`
	if [ $? -ne 0 ] ; then
		echo "HA1B calculation failed"
		exit 1
	fi

  #PHPLIB_ID of users should be difficulty to guess for security reasons
  NOW=`date`;
  PHPLIB_ID=`$GENHA1 "$RANDOM" "$NOW" $SIP_DOMAIN`
	if [ $? -ne 0 ] ; then
    echo "PHPLIB_ID calculation failed"
		exit 1
	fi
}

antisip_create () # pars: <database name> [<no_init_user>]
{
if [ $# -eq 1 ] ; then
	if [ -z "$SIP_DOMAIN" ] ; then
		prompt_realm
	fi
	#AMD credentials
	# by default we create initial user
	INITIAL_USER="INSERT INTO subscriber
		($USERCOL, password, first_name, last_name, phone,
		email_address, datetime_created, datetime_modified, confirmation,
    flag, sendnotification, greeting, ha1, domain, ha1b, phplib_id )
		VALUES ( 'admin', 'heslo', 'Initial', 'Admin', '123',
		'root@localhost', '2002-09-04 19:37:45', '0000-00-00 00:00:00',
		'57DaSIPuCm52UNe54LF545750cfdL48OMZfroM53', 'o', '', '',
		'bidon', '$SIP_DOMAIN', '$HA1B',
    '$PHPLIB_ID' );"
elif [ $# -eq 2 ] ; then
	# if 3rd param set, don't create any initial user
	INITIAL_USER=""
else
	echo "antisip_create function takes one or two params"
	exit 1
fi

echo "creating database $1 ..."

sql_query <<EOF
create database $1;
use $1;

# Users: antisip is the regular user, antisipro only for reading
GRANT ALL PRIVILEGES ON $1.* TO $USERNAME IDENTIFIED  BY '$DEFAULT_PW';
GRANT ALL PRIVILEGES ON $1.* TO ${USERNAME}@$DBHOST IDENTIFIED BY '$DEFAULT_PW';
GRANT SELECT ON $1.* TO $ROUSER IDENTIFIED BY '$RO_PW';
GRANT SELECT ON $1.* TO ${ROUSER}@$DBHOST IDENTIFIED BY '$RO_PW';


#
# Table structure versions
#

CREATE TABLE version (
   table_name varchar(64) NOT NULL,
   table_version smallint(5) DEFAULT '0' NOT NULL
) $TABLE_TYPE;

#
# Dumping data for table 'version'
#

# valid domains for server
INSERT INTO version VALUES ( 'domain', '1');

# available users & related informations
INSERT INTO version VALUES ( 'pending', '4');
INSERT INTO version VALUES ( 'subscriber', '5');
INSERT INTO version VALUES ( 'trusted', '1');
INSERT INTO version VALUES ( 'phonebook', '1');

# locations of users
INSERT INTO version VALUES ( 'location', '6');

# MESSAGEs to be sent to offline users
INSERT INTO version VALUES ( 'messages', '1');

# presence information of users
# antisip is sending SUBSCRIBE for event=presence
# or
# antisip is receiving PUBLISH for event=presence
INSERT INTO version VALUES ( 'presence', '1');

# dialog information of users
# antisip is sending SUBSCRIBE for event=dialog
# or
# antisip is receiving PUBLISH for event=dialog
INSERT INTO version VALUES ( 'dialog', '1');

# winfo for presence published to users
# antisip is receiving SUBSCRIBE for event=winfo.presence
INSERT INTO version VALUES ( 'wipresence', '1');

# table for storing SUBSCRIBE's dialogs on server
INSERT INTO version VALUES ( 'subscriptions', '1');


#
# Table structure for table 'domain' -- domains proxy is responsible for
#
CREATE TABLE domain (
  domain varchar(128) NOT NULL default '',
  last_modified datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (domain)
) $TABLE_TYPE;




#
# Table structure for table 'pending' -- unconfirmed subscribtion
# requests
#
CREATE TABLE pending (
  phplib_id varchar(32) NOT NULL default '',
  $USERCOL varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default '',
  password varchar(25) NOT NULL default '',
  first_name varchar(25) NOT NULL default '',
  last_name varchar(45) NOT NULL default '',
  phone varchar(15) NOT NULL default '',
  email_address varchar(50) NOT NULL default '',
  datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
  datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
  confirmation varchar(64) NOT NULL default '',
  flag char(1) NOT NULL default 'o',
  sendnotification varchar(50) NOT NULL default '',
  greeting varchar(50) NOT NULL default '',
  ha1 varchar(128) NOT NULL default '',
  ha1b varchar(128) NOT NULL default '',
  allow_find char(1) NOT NULL default '0',
  timezone varchar(128) default NULL,
  rpid varchar(128) default NULL,
  domn int(10) default NULL,
  uuid varchar(64) default NULL,
  PRIMARY KEY ($USERCOL, domain),
  KEY user_2 ($USERCOL),
  UNIQUE KEY phplib_id (phplib_id)
) $TABLE_TYPE;



#
# Table structure for table 'subscriber' -- user database
#
CREATE TABLE subscriber (
  phplib_id varchar(32) NOT NULL default '',
  $USERCOL varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default '',
  password varchar(25) NOT NULL default '',
  first_name varchar(25) NOT NULL default '',
  last_name varchar(45) NOT NULL default '',
  phone varchar(15) NOT NULL default '',
  email_address varchar(50) NOT NULL default '',
  datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
  datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
  confirmation varchar(64) NOT NULL default '',