psp_mysql.sh

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

  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,
  UNIQUE KEY phplib_id (phplib_id),
  PRIMARY KEY ($USERCOL, domain),
  KEY user_2 ($USERCOL)
) $TABLE_TYPE;



#
# Table structure for table trusted
CREATE TABLE trusted (
  src_ip varchar(39) NOT NULL,
  proto varchar(4) NOT NULL,
  from_pattern varchar(64) NOT NULL,
  PRIMARY KEY (src_ip, proto, from_pattern)
) $TABLE_TYPE;



#
# Table structure for table 'phonebook' -- user's phonebook
#
CREATE TABLE phonebook (
  id int(10) unsigned NOT NULL auto_increment,
  $USERCOL varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default '',
  fname varchar(32) NOT NULL default '',
  lname varchar(32) NOT NULL default '',
  sip_uri varchar(128) NOT NULL default '',
  PRIMARY KEY  (id)
) $TABLE_TYPE;



#
# Table structure for table 'location' -- that is persistent UsrLoc
#
CREATE TABLE location (
  $USERCOL varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default '',
  contact varchar(255) NOT NULL default '',
  received varchar(255) default NULL,
  expires datetime NOT NULL default '$DEFAULT_LOCATION_EXPIRES',
  q float(10,2) NOT NULL default '$DEFAULT_Q',
  callid varchar(255) NOT NULL default '$DEFAULT_CALLID',
  cseq int(11) NOT NULL default '$DEFAULT_CSEQ',
  last_modified timestamp(14) NOT NULL,
  replicate int(10) unsigned NOT NULL default '0',
  state tinyint(1) unsigned NOT NULL default '0',
  flags int(11) NOT NULL default '0',
  user_agent varchar(50) NOT NULL default '',
  PRIMARY KEY($USERCOL, domain, contact)
) $TABLE_TYPE;



#
# "instant" message queue
#
CREATE TABLE message(
    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    src_addr VARCHAR(255) NOT NULL DEFAULT "",
    dst_addr VARCHAR(255) NOT NULL DEFAULT "",
    r_uri VARCHAR(255) NOT NULL DEFAULT "",
    username VARCHAR(64) NOT NULL DEFAULT "",
    domain VARCHAR(128) NOT NULL DEFAULT "",
    inc_time INTEGER NOT NULL DEFAULT 0,
    exp_time INTEGER NOT NULL DEFAULT 0,
    ctype VARCHAR(32) NOT NULL DEFAULT "text/plain",
    body BLOB NOT NULL DEFAULT ""
) $TABLE_TYPE;



#
# presence event package informations
#
CREATE TABLE presence(
    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT "",
    domain VARCHAR(128) NOT NULL DEFAULT "",
    sip_etag VARCHAR(64) NOT NULL DEFAULT "",
    inc_time INTEGER NOT NULL DEFAULT 0,
    exp_time INTEGER NOT NULL DEFAULT 0,
    ctype VARCHAR(32) NOT NULL DEFAULT "application/cpid+xml",
    body BLOB NOT NULL DEFAULT ""
) $TABLE_TYPE;



#
# dialog event package informations
#
CREATE TABLE dialog(
    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT "",
    domain VARCHAR(128) NOT NULL DEFAULT "",
    sip_etag VARCHAR(64) NOT NULL DEFAULT "",
    inc_time INTEGER NOT NULL DEFAULT 0,
    exp_time INTEGER NOT NULL DEFAULT 0,
    ctype VARCHAR(32) NOT NULL DEFAULT "application/dialog+xml",
    body BLOB NOT NULL DEFAULT ""
) $TABLE_TYPE;


#
# dialog event package informations
#
CREATE TABLE wipresence(
    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT "",
    domain VARCHAR(128) NOT NULL DEFAULT "",
    sip_etag VARCHAR(64) NOT NULL DEFAULT "",
    inc_time INTEGER NOT NULL DEFAULT 0,
    exp_time INTEGER NOT NULL DEFAULT 0,
    ctype VARCHAR(32) NOT NULL DEFAULT "application/watcher+xml",
    body BLOB NOT NULL DEFAULT ""
) $TABLE_TYPE;

#
# dialog for SUBSCRIBE to presence server.
#
#CREATE TABLE subscriptions(
#    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
#    l_username VARCHAR(64) NOT NULL DEFAULT "",
#    l_domain VARCHAR(128) NOT NULL DEFAULT "",
#    l_tag VARCHAR(64) NOT NULL DEFAULT "",
#    l_cseq VARCHAR(64) NOT NULL DEFAULT "",
#    r_username VARCHAR(64) NOT NULL DEFAULT "",
#    r_domain VARCHAR(128) NOT NULL DEFAULT "",
#    r_contact VARCHAR(1024) NOT NULL DEFAULT "",
#    r_cseq VARCHAR(64) NOT NULL DEFAULT "",
#    events VARCHAR(64) NOT NULL DEFAULT "",
#    routes VARCHAR(2048) NOT NULL DEFAULT "",
#    callid VARCHAR(64) NOT NULL DEFAULT "",
#    inc_time INTEGER NOT NULL DEFAULT 0,
#    exp_time INTEGER NOT NULL DEFAULT 0,
#) $TABLE_TYPE;

#
# dialog for SUBSCRIBE to presence server.
#
CREATE TABLE subscriptions(
    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    l_username VARCHAR(64) NOT NULL DEFAULT "",
    l_domain VARCHAR(128) NOT NULL DEFAULT "",
    l_tag VARCHAR(64) NOT NULL DEFAULT "",
    l_cseq INTEGER NOT NULL DEFAULT 1,
    r_username VARCHAR(64) NOT NULL DEFAULT "",
    r_domain VARCHAR(128) NOT NULL DEFAULT "",
    r_cseq INTEGER NOT NULL DEFAULT 1,
    event VARCHAR(64) NOT NULL DEFAULT "",
    exp_time DATETIME default NULL,
    notify_template BLOB NOT NULL DEFAULT ""
) $TABLE_TYPE;



# add an admin user "admin" with password==heslo,
# so that one can try it out on quick start

$INITIAL_USER



EOF

} # antisip_create


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

case $1 in
	reinstall)

		#1 create a backup database (named *_bak)
		echo "creating backup database"
		antisip_backup $DBNAME
		if [ "$?" -ne 0 ] ; then
			echo "reinstall: antisip_backup failed"
			exit 1
		fi
		#2 dump original database and change names in it
		echo "dumping table content ($DBNAME)"
		tmp_file=/tmp/antisip_mysql.$$
		antisip_dump $DBNAME  > $tmp_file
		if [ "$?" -ne 0 ] ; then
			echo "reinstall: dumping original db failed"
			exit 1
		fi
		sed "s/[uU][sS][eE][rR]_[iI][dD]/user/g" $tmp_file |
			sed "s/[uU][sS][eE][rR]\($\|[^a-zA-Z]\)/$USERCOL\1/g" |
			sed "s/[rR][eE][aA][lL][mM]/domain/g"> ${tmp_file}.2
		#3 drop original database
		echo "dropping table ($DBNAME)"
		antisip_drop $DBNAME
		if [ "$?" -ne 0 ] ; then
			echo "reinstall: dropping table failed"
			rm $tmp_file*
			exit 1
		fi
		#4 change names in table definition and restore
		echo "creating new structures"
		antisip_create $DBNAME no_init_user
		if [ "$?" -ne 0 ] ; then
			echo "reinstall: creating new table failed"
			rm $tmp_file*
			exit 1
		fi
		#5 restoring table content
		echo "restoring table content"

		antisip_restore $DBNAME ${tmp_file}.2
		if [ "$?" -ne 0 ] ; then
			echo "reinstall: restoring table failed"
			rm $tmp_file*
			exit 1
		fi


#XX
#		rm $tmp_file*
		exit 0
		;;
	copy)
		# copy database to some other name
		shift
		if [ $# -ne 1 ]; then
			usage
			exit 1
		fi
		tmp_file=/tmp/antisip_mysql.$$
		antisip_dump $DBNAME  > $tmp_file
		ret=$?
		if [ "$ret" -ne 0 ]; then
			rm $tmp_file
			exit $ret
		fi
		antisip_create $1 no_init_user
		ret=$?
		if [ "$ret" -ne 0 ]; then
			rm $tmp_file
			exit $ret
		fi
		antisip_restore $1 $tmp_file
		ret=$?
		rm $tmp_file
		exit $ret
		;;
	backup)
		# backup current database
		antisip_dump $DBNAME
		exit $?
		;;
	restore)
		# restore database from a backup
		shift
		if [ $# -ne 1 ]; then
			usage
			exit 1
		fi
		antisip_restore $DBNAME $1
		exit $?
		;;
	create)
		# create new database structures
		shift
		if [ $# -eq 1 ] ; then
			DBNAME="$1"
		fi
		antisip_create $DBNAME
		exit $?
		;;
	drop)
		# delete ser database
		antisip_drop $DBNAME
		exit $?
		;;
	reinit)
		# delete database and create a new one
		antisip_drop $DBNAME
		ret=$?
		if [ "$ret" -ne 0 ]; then
			exit $ret
		fi
		antisip_create $DBNAME
		exit $?
		;;
	*)
		usage
		exit 1;
		;;
esac