[Devel] postgres tables example
Klaus Darilion
klaus.mailinglists at pernau.at
Fri Jul 1 12:53:20 CEST 2005
Hi!
Attached are the new versions with preliminary postgresql support.
I modified the scripts from 0.9.4. Are there any changes to 0.10?
There is a new environment variable called DBMS, which must be "mysql"
or "postgresql". For postgresql you must have a ~/.pgpass file.
mysqldb.sh:
===========
I modified the table strcuture also for mysql. I hope this does not
break thinks.
I've tested with postresql - "create" and "drop" works fine. The other
commands are not tested
sc:
===========
There are more problem, as many commands parse the output of mysql.
Thus, several commands wont work and needs more testing.
regards,
klaus
Klaus Darilion wrote:
> Update:
> I just updated serctl and mysqldb.sh to also support postgres. Basically
> it works although there are for sure several bugs. I will post them
> tomorrow (before going on holiday).
>
> klaus
>
> Klaus Darilion wrote:
>
>> Hi Ingo!
>>
>> Have you started writing a pgsqldb.sh?
>>
>> regards,
>> klaus
>>
>> Daniel-Constantin Mierla wrote:
>>
>>> Hello,
>>>
>>> could you write a pgsqldb.sh similar to mysqldb.sh? You can
>>> eventually extract the common parts in another file and have only the
>>> differences in separate files. We can grant you cvs write access if
>>> you want to do it and maintain the scripts -- just make an account on
>>> sourceforge.net and let us know your id.
>>>
>>> cheers,
>>> Daniel
>>>
>>> On 06/28/05 01:29, Ingo Wolfsberger wrote:
>>>
>>>> Hi
>>>>
>>>> I have attached "sample" tables for the postgres module.
>>>> The most important tables are in it.
>>>>
>>>> bye,
>>>> Ingo
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>> _______________________________________________
>>>> Devel mailing list
>>>> Devel at openser.org
>>>> http://openser.org/cgi-bin/mailman/listinfo/devel
>>>>
>>>>
>>>
>>> _______________________________________________
>>> Devel mailing list
>>> Devel at openser.org
>>> http://openser.org/cgi-bin/mailman/listinfo/devel
>>>
>>>
>>
>>
>> _______________________________________________
>> Devel mailing list
>> Devel at openser.org
>> http://openser.org/cgi-bin/mailman/listinfo/devel
>>
>>
>
>
-------------- next part --------------
#!/bin/sh
#
# $Id: mysqldb.sh,v 1.1.1.1.2.1 2005/06/14 11:10:20 miconda Exp $
#
# Script for adding and dropping ser MySql tables
#
# TO-DO: update_structures command for migriting to new
# table definitons
# USAGE: call the command without any parameters for info
#
# 2003-01-21 changed SILO table definition, by dcm
#
# History:
# 2003-03-12 added replication mark and state columns to location (nils)
# 2003-03-05: Changed user to username, user is reserved word (janakj)
# 2003-01-26 statistics table introduced (jiri)
# 2003-01-25: Optimized keys of some core tables (janakj)
# 2003-01-25: USER_ID changed to user everywhere (janakj)
# 2003-01-24: Changed realm column of subscriber and pending
# tables to domain (janakj)
# 2003-04-14 reinstall introduced (jiri)
# 2004-07-05 new definition of table silo (dcm)
#
# 2005-06-30 postgresql support, klaus darilion, known issues
# - int unsigned replaced by bigint
# - postgresql creates some implicit indexes, thus some of the
# indexes are doubled
# - msilo: blob replaced by text, is this fine?
# - datetime types not sure
# include resource files, if any
if [ -f /etc/ser/.opensermysqlrc ]; then
. /etc/ser/.opensermysqlrc
fi
if [ -f /usr/local/etc/openser/.opensermysqlrc ]; then
. /usr/local/etc/openser/.opensermysqlrc
fi
if [ -f ~/.opensermysqlrc ]; then
. ~/.opensermysqlrc
fi
#################################################################
# config vars
#################################################################
# name of the database to be used by SER
if [ -z "$DBNAME" ]; then
DBNAME="openser"
fi
# address of SQL server
if [ -z "$DBHOST" ]; then
DBHOST="localhost"
fi
# user with full privileges over DBNAME database
if [ -z "$USERNAME" ]; then
USERNAME="openser"
fi
# password user with full privileges over DBNAME database
if [ -z "$DEFAULT_PW" ]; then
DEFAULT_PW="openserrw"
fi
# read-only user
if [ -z "$ROUSER" ]; then
ROUSER="openserro"
fi
# password for read-only user
if [ -z "$RO_PW" ]; then
RO_PW="openserro"
fi
# Database system; "mysql" or "postgresql"
if [ -z "$DBMS" ]; then
DBMS="mysql"
#DBMS="postgresql"
fi
# full privileges MySQL user
if [ -z "$SQL_USER" ]; then
if [ $DBMS == "mysql" ]; then
SQL_USER="root"
elif [ $DBMS == "postgresql" ]; then
SQL_USER="postgres"
if [ ! -r ~/.pgpass ]; then
echo "~./pgpass does not exist, please create this file and support proper credentials for user postgres."
echo "Note: you need at least postgresql>= 7.3"
exit 1
fi
else
echo "wrong database system"
exit 1
fi
fi
if [ "$DBMS" == "mysql" ]; then
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 "
elif [ "$DBMS" == "postgresql" ]; then
CMD="psql -h $DBHOST -d template1 -U $SQL_USER "
DUMP_CMD="pg_dump -h $DBHOST -u$SQL_USER -c -t "
BACKUP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c "
fi
# type of mysql tables
if [ -z "$TABLE_TYPE" ]; then
if [ "$DBMS" == "mysql" ]; then
TABLE_TYPE="TYPE=MyISAM"
elif [ "$DBMS" == "postgresql" ]; then
TABLE_TYPE=""
fi
fi
# user name column
if [ -z "$USERCOL" ]; then
USERCOL="username"
fi
# path to gen_ha1 tool
if [ -z "$GENHA1" ]; then
GENHA1='/usr/local/sbin/gen_ha1'
fi
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 SER 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()
{
if [ "$DBMS" == "mysql" ]; then
savetty=`stty -g`
printf "MySql password for $SQL_USER: "
stty -echo
read PW
stty $savetty
echo
fi
}
# execute sql command
sql_query()
{
if [ "$DBMS" == "mysql" ]; then
$CMD "-p$PW" "$@"
elif [ "$DBMS" == "postgresql" ]; then
$CMD "$@"
fi
}
# dump all rows
ser_dump() # pars: <database name>
{
if [ $# -ne 1 ] ; then
echo "ser_dump function takes one param"
exit 1
fi
$DUMP_CMD "-p$PW" $1
}
# copy a database to database_bak
ser_backup() # par: <database name>
{
if [ $# -ne 1 ] ; then
echo "ser_backup function takes one param"
exit 1
fi
BU=/tmp/mysql_bup.$$
$BACKUP_CMD "-p$PW" $1 > $BU
if [ "$?" -ne 0 ] ; then
echo "ser backup dump failed"
exit 1
fi
sql_query <<EOF
create database $1_bak;
EOF
ser_restore $1_bak $BU
if [ "$?" -ne 0 ]; then
echo "ser backup/restore failed"
rm $BU
exit 1
fi
}
ser_restore() #pars: <database name> <filename>
{
if [ $# -ne 2 ] ; then
echo "ser_restore function takes two params"
exit 1
fi
sql_query $1 < $2
}
ser_drop() # pars: <database name>
{
if [ $# -ne 1 ] ; then
echo "ser_drop function takes two params"
exit 1
fi
if [ $DBMS == "mysql" ]; then
DROP_USER=""
elif [ $DBMS == "postgresql" ]; then
DROP_USER="DROP USER \"$USERNAME\";
DROP USER \"$ROUSER\";"
fi
sql_query << EOF
drop database $1;
$DROP_USER
EOF
} #ser_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 $DEFAULT_PW`
if [ $? -ne 0 ] ; then
echo "HA1 calculation failed"
exit 1
fi
HA1B=`$GENHA1 "admin@$SIP_DOMAIN" $SIP_DOMAIN $DEFAULT_PW`
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
}
ser_create () # pars: <database name> [<no_init_user>]
{
if [ $# -eq 1 ] ; then
if [ -z "$SIP_DOMAIN" ] ; then
prompt_realm
fi
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', '$DEFAULT_PW', 'Initial', 'Admin', '123',
'root at localhost', '2002-09-04 19:37:45', '2000-01-01 00:00:01',
'57DaSIPuCm52UNe54LF545750cfdL48OMZfroM53', 'o', '', '',
'$HA1', '$SIP_DOMAIN', '$HA1B',
'$PHPLIB_ID' );
INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
VALUES ('admin', '$SIP_DOMAIN', 'is_admin', '1');
INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
VALUES ('admin', '$SIP_DOMAIN', 'change_privileges', '1');"
elif [ $# -eq 2 ] ; then
# if 3rd param set, don't create any initial user
INITIAL_USER=""
else
echo "ser_create function takes one or two params"
exit 1
fi
echo "creating database $1 ..."
# Users: $USERNAME is the regular user, $ROUSER only for reading
if [ "$DBMS" == "mysql" ]; then
USE_CMD='use'
GRANT_CMD="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';"
TIMESTAMP="timestamp(14) NOT NULL"
DATETIME="datetime NOT NULL default '0000-00-00 00:00:00'"
DATETIMEALIAS="datetime NOT NULL default '$DEFAULT_ALIASES_EXPIRES'"
DATETIMELOCATION="datetime NOT NULL default '$DEFAULT_LOCATION_EXPIRES'"
FLOAT="float(10,2)";
AUTO_INCREMENT="int NOT NULL auto_increment PRIMARY KEY";
elif [ "$DBMS" == "postgresql" ]; then
USE_CMD='\connect'
GRANT_CMD="CREATE USER $USERNAME WITH PASSWORD '$DEFAULT_PW';
CREATE USER $ROUSER WITH PASSWORD '$RO_PW';
GRANT ALL PRIVILEGES ON TABLE version, acc, active_sessions, aliases, dbaliases, grp,
location, missed_calls, pending, phonebook, subscriber, silo, domain,
uri, server_monitoring, usr_preferences, trusted, server_monitoring_agg,
admin_privileges, speed_dial TO $USERNAME;
GRANT SELECT ON TABLE version, acc, active_sessions, aliases, dbaliases, grp,
location, missed_calls, pending, phonebook, subscriber, silo, domain,
uri, server_monitoring, usr_preferences, trusted, server_monitoring_agg,
admin_privileges, speed_dial TO $ROUSER;"
TIMESTAMP="timestamp NOT NULL DEFAULT NOW()"
DATETIME="TIMESTAMP WITHOUT TIME ZONE NOT NULL default '2000-01-01 00:00:01'"
DATETIMEALIAS="TIMESTAMP WITHOUT TIME ZONE NOT NULL default '$DEFAULT_ALIASES_EXPIRES'"
DATETIMELOCATION="TIMESTAMP WITHOUT TIME ZONE NOT NULL default '$DEFAULT_LOCATION_EXPIRES'"
FLOAT="NUMERIC(10,2)";
AUTO_INCREMENT="SERIAL PRIMARY KEY";
fi
#cat <<EOF
sql_query <<EOF
CREATE DATABASE $1;
$USE_CMD $1;
/*
* Table structure versions
*/
CREATE TABLE version (
table_name varchar(64) NOT NULL PRIMARY KEY,
table_version smallint DEFAULT '0' NOT NULL
) $TABLE_TYPE;
/*
* Dumping data for table 'version'
*/
INSERT INTO version VALUES ( 'subscriber', '5');
INSERT INTO version VALUES ( 'reserved', '1');
INSERT INTO version VALUES ( 'phonebook', '1');
INSERT INTO version VALUES ( 'pending', '4');
INSERT INTO version VALUES ( 'missed_calls', '2');
INSERT INTO version VALUES ( 'location', '6');
INSERT INTO version VALUES ( 'grp', '2');
INSERT INTO version VALUES ( 'event', '1');
INSERT INTO version VALUES ( 'aliases', '6');
INSERT INTO version VALUES ( 'active_sessions', '1');
INSERT INTO version VALUES ( 'acc', '2');
INSERT INTO version VALUES ( 'config', '1');
INSERT INTO version VALUES ( 'silo', '3');
INSERT INTO version VALUES ( 'realm', '1');
INSERT INTO version VALUES ( 'domain', '1');
INSERT INTO version VALUES ( 'uri', '1');
INSERT INTO version VALUES ( 'server_monitoring', '1');
INSERT INTO version VALUES ( 'server_monitoring_agg', '1');
INSERT INTO version VALUES ( 'trusted', '1');
INSERT INTO version VALUES ( 'usr_preferences', '1');
INSERT INTO version VALUES ( 'preferences_types', '1');
INSERT INTO version VALUES ( 'admin_privileges', '1');
INSERT INTO version VALUES ( 'calls_forwarding', '1');
INSERT INTO version VALUES ( 'speed_dial', '1');
INSERT INTO version VALUES ( 'dbaliases', '1');
/*
* Table structure for table 'acc' -- accounted calls
*/
CREATE TABLE acc (
sip_from varchar(128) NOT NULL default '',
sip_to varchar(128) NOT NULL default '',
sip_status varchar(128) NOT NULL default '',
sip_method varchar(16) NOT NULL default '',
i_uri varchar(128) NOT NULL default '',
o_uri varchar(128) NOT NULL default '',
from_uri varchar(128) NOT NULL default '',
to_uri varchar(128) NOT NULL default '',
sip_callid varchar(128) NOT NULL default '',
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
fromtag varchar(128) NOT NULL default '',
totag varchar(128) NOT NULL default '',
time $DATETIME,
timestamp $TIMESTAMP,
src varchar(128) default NULL,
dst varchar(128) default NULL
) $TABLE_TYPE;
CREATE INDEX acc_user_indx ON acc ($USERCOL, domain);
CREATE INDEX sip_callid_indx ON acc (sip_callid);
/*
* Table structure for table 'active_sessions' -- web stuff
*/
CREATE TABLE active_sessions (
sid varchar(32) NOT NULL default '',
name varchar(32) NOT NULL default '',
val text,
changed varchar(14) NOT NULL default '',
PRIMARY KEY (name,sid)
) $TABLE_TYPE;
CREATE INDEX changed_indx ON active_sessions (changed);
/*
* Table structure for table 'aliases' -- location-like table
* (aliases_contact index makes lookup of missed calls much faster)
*/
CREATE TABLE aliases (
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
contact varchar(255) NOT NULL default '',
received varchar(255) default NULL,
expires $DATETIMEALIAS,
q $FLOAT NOT NULL default '$DEFAULT_Q',
callid varchar(255) NOT NULL default '$DEFAULT_CALLID',
cseq int NOT NULL default '$DEFAULT_CSEQ',
last_modified $TIMESTAMP,
flags int NOT NULL default '0',
user_agent varchar(50) NOT NULL default '',
socket varchar(128) default NULL,
PRIMARY KEY($USERCOL, domain, contact)
) $TABLE_TYPE;
CREATE INDEX aliases_contact_indx ON aliases (contact);
/*
* DB aliases
*/
CREATE TABLE dbaliases (
alias_username varchar(64) NOT NULL default '',
alias_domain varchar(128) NOT NULL default '',
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
UNIQUE (alias_username,alias_domain)
) $TABLE_TYPE;
CREATE INDEX alias_user_indx ON dbaliases ($USERCOL, domain);
CREATE INDEX alias_key_indx ON dbaliases (alias_username,alias_domain);
/*
* Table structure for table 'grp' -- group membership
* table; used primarily for ACLs
*/
CREATE TABLE grp (
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
grp varchar(50) NOT NULL default '',
last_modified $DATETIME,
PRIMARY KEY($USERCOL, domain, grp)
) $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 $DATETIMELOCATION,
q $FLOAT NOT NULL default '$DEFAULT_Q',
callid varchar(255) NOT NULL default '$DEFAULT_CALLID',
cseq int NOT NULL default '$DEFAULT_CSEQ',
last_modified $TIMESTAMP,
flags int NOT NULL default '0',
user_agent varchar(50) NOT NULL default '',
socket varchar(128) default NULL,
PRIMARY KEY($USERCOL, domain, contact)
) $TABLE_TYPE;
/*
* Table structure for table 'missed_calls' -- acc-like table
* for keeping track of missed calls
*/
CREATE TABLE missed_calls (
sip_from varchar(128) NOT NULL default '',
sip_to varchar(128) NOT NULL default '',
sip_status varchar(128) NOT NULL default '',
sip_method varchar(16) NOT NULL default '',
i_uri varchar(128) NOT NULL default '',
o_uri varchar(128) NOT NULL default '',
from_uri varchar(128) NOT NULL default '',
to_uri varchar(128) NOT NULL default '',
sip_callid varchar(128) NOT NULL default '',
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
fromtag varchar(128) NOT NULL default '',
totag varchar(128) NOT NULL default '',
time $DATETIME,
timestamp $TIMESTAMP,
src varchar(128) default NULL,
dst varchar(128) default NULL
) $TABLE_TYPE;
CREATE INDEX mc_user_indx ON missed_calls ($USERCOL, domain);
/*
* 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,
datetime_modified $DATETIME,
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 default NULL,
uuid varchar(64) default NULL,
PRIMARY KEY ($USERCOL, domain),
UNIQUE (phplib_id)
) $TABLE_TYPE;
CREATE INDEX user_2_pend_indx ON pending ($USERCOL);
CREATE INDEX phplib_id_pend_indx ON pending (phplib_id);
/*
* Table structure for table 'phonebook' -- user's phonebook
*/
CREATE TABLE phonebook (
id $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 ''
) $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,
datetime_modified $DATETIME,
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 default NULL,
uuid varchar(64) default NULL,
nickname varchar(64) NOT NULL default '',
UNIQUE (phplib_id),
PRIMARY KEY ($USERCOL, domain)
) $TABLE_TYPE;
CREATE INDEX phplib_id_subs_indx ON subscriber (phplib_id);
CREATE INDEX user_2_subs_indx ON subscriber ($USERCOL);
/*
* "instant" message silo
*/
CREATE TABLE silo(
mid $AUTO_INCREMENT,
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 TEXT NOT NULL DEFAULT ''
) $TABLE_TYPE;
/*
* Table structure for table 'domain' -- domains proxy is responsible for
*/
CREATE TABLE domain (
domain varchar(128) NOT NULL default '',
last_modified $DATETIME,
PRIMARY KEY (domain)
) $TABLE_TYPE;
/*
* Table structure for table 'uri' -- uri user parts users are allowed to use
*/
CREATE TABLE uri (
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
uri_user varchar(50) NOT NULL default '',
last_modified $DATETIME,
PRIMARY KEY ($USERCOL, domain, uri_user)
) $TABLE_TYPE;
/*
* Table structure for table 'server_monitoring'
*/
CREATE TABLE server_monitoring (
time $DATETIME,
id bigint NOT NULL default '0',
param varchar(32) NOT NULL default '',
value int NOT NULL default '0',
increment int NOT NULL default '0',
PRIMARY KEY (id,param)
) $TABLE_TYPE;
/*
* Table structure for table 'usr_preferences'
*/
CREATE TABLE usr_preferences (
uuid varchar(64) NOT NULL default '',
$USERCOL varchar(100) NOT NULL default '0',
domain varchar(128) NOT NULL default '',
attribute varchar(32) NOT NULL default '',
type int NOT NULL default '0',
value varchar(128) NOT NULL default '',
modified $TIMESTAMP,
PRIMARY KEY (attribute,$USERCOL,domain)
) $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 'server_monitoring_agg'
*/
CREATE TABLE server_monitoring_agg (
param varchar(32) NOT NULL default '',
s_value int NOT NULL default '0',
s_increment int NOT NULL default '0',
last_aggregated_increment int NOT NULL default '0',
av real NOT NULL default '0',
mv int NOT NULL default '0',
ad real NOT NULL default '0',
lv int NOT NULL default '0',
min_val int NOT NULL default '0',
max_val int NOT NULL default '0',
min_inc int NOT NULL default '0',
max_inc int NOT NULL default '0',
lastupdate $DATETIME,
PRIMARY KEY (param)
) $TABLE_TYPE;
/*
* Table structure for table 'admin_privileges' -- multidomain serweb ACL control
*/
CREATE TABLE admin_privileges (
$USERCOL varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
priv_name varchar(64) NOT NULL default '',
priv_value varchar(64) NOT NULL default '',
PRIMARY KEY ($USERCOL,priv_name,priv_value,domain)
) $TABLE_TYPE;
/*
* Table structure for table 'speed_dial'
*/
CREATE TABLE speed_dial (
username varchar(64) NOT NULL default '',
domain varchar(128) NOT NULL default '',
sd_username varchar(64) NOT NULL default '',
sd_domain varchar(128) NOT NULL default '',
new_uri varchar(192) NOT NULL default '',
description varchar(64) NOT NULL default '',
PRIMARY KEY (username,domain,sd_domain,sd_username)
) $TABLE_TYPE;
/* add an admin user "admin" with password==$DEFAULT_PW,
* so that one can try it out on quick start
*/
$INITIAL_USER
/*
* GRANT permissions
*/
$GRANT_CMD
EOF
} # ser_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"
ser_backup $DBNAME
if [ "$?" -ne 0 ] ; then
echo "reinstall: ser_backup failed"
exit 1
fi
#2 dump original database and change names in it
echo "dumping table content ($DBNAME)"
tmp_file=/tmp/ser_mysql.$$
ser_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)"
ser_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"
ser_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"
# Recreate perms column here so that subsequent
# restore succeeds
sql_query $DBNAME << EOF
ALTER TABLE subscriber ADD perms VARCHAR(32) AFTER ha1b;
ALTER TABLE pending ADD perms VARCHAR(32) AFTER ha1b;
EOF
ser_restore $DBNAME ${tmp_file}.2
if [ "$?" -ne 0 ] ; then
echo "reinstall: restoring table failed"
rm $tmp_file*
exit 1
fi
sql_query $DBNAME << EOF
# Move perms from subscriber to admin_privileges
INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value) SELECT $USERCOL, domain, 'is_admin', '1' FROM subscriber WHERE perms='admin';
# Drop perms column here
ALTER TABLE subscriber DROP perms;
ALTER TABLE pending DROP perms;
EOF
#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/ser_mysql.$$
ser_dump $DBNAME > $tmp_file
ret=$?
if [ "$ret" -ne 0 ]; then
rm $tmp_file
exit $ret
fi
ser_create $1 no_init_user
ret=$?
if [ "$ret" -ne 0 ]; then
rm $tmp_file
exit $ret
fi
ser_restore $1 $tmp_file
ret=$?
rm $tmp_file
exit $ret
;;
backup)
# backup current database
ser_dump $DBNAME
exit $?
;;
restore)
# restore database from a backup
shift
if [ $# -ne 1 ]; then
usage
exit 1
fi
ser_restore $DBNAME $1
exit $?
;;
create)
# create new database structures
shift
if [ $# -eq 1 ] ; then
DBNAME="$1"
fi
ser_create $DBNAME
exit $?
;;
drop)
# delete ser database
ser_drop $DBNAME
exit $?
;;
reinit)
# delete database and create a new one
ser_drop $DBNAME
ret=$?
if [ "$ret" -ne 0 ]; then
exit $ret
fi
ser_create $DBNAME
exit $?
;;
*)
usage
exit 1;
;;
esac
-------------- next part --------------
#!/bin/sh
#
# $Id: sc,v 1.1.1.1 2005/06/13 16:47:52 bogdan_iancu Exp $
#
# sc: ser control; tool for maintaining ser's databases
#
# History:
# --------
# 2003-02-23 Thomas's start|stop commands checked in
# 2003-09-08 multidomain features: usernames can now include domain
# names too (jiri)
# 2003-09-15 multidomain support completed (jiri)
# 2003-10-30 more debugging output, less copy and paste in
# the previous item
# 2005-06-30 Added postgresql support, klaus darilion, known issues:
# - supplying second paramter for sql_ro_query wont work
# - functions which parse output will fail
#
# To-DO:
# -----
# - generalization for other than mysql databases
# - front-end to updating administrative mysql password would
# be a convenient thing to have
#===================================================================
### include resource files, if any
if [ -f /etc/ser/.openserctlrc ]; then
. /etc/ser/.openserctlrc
fi
if [ -f /usr/local/etc/openser/.openserctlrc ]; then
. /usr/local/etc/openser/.openserctlrc
fi
if [ -f ~/.openserctlrc ]; then
. ~/.openserctlrc
fi
### version fo this script
VERSION='1.1 - $Revision: 1.1.1.1 $'
##### ------------------------------------------------ #####
### configuration for starting/stopping ser
if [ -z "$PID_FILE" ] ; then
PID_FILE=/var/run/openser.pid
fi
if [ -z "$SYSLOG" ] ; then
SYSLOG=1 # 0=output to console, 1=output to syslog
fi
if [ -z "$STARTOPTIONS" ] ; then
STARTOPTIONS= # for example -dddd
fi
if [ -z "$DIR" ] ; then
DIR=`dirname $0`
fi
if [ -z "$SERBIN" ] ; then
SERBIN=$DIR/openser
fi
##### ----------------------------------------------- #####
### ser's FIFO server
if [ -z "$SER_FIFO" ]; then
SER_FIFO=/tmp/openser_fifo
fi
# period in which stats are reprinted
if [ -z "$WATCH_PERIOD" ] ; then
WATCH_PERIOD=2
fi
##### ----------------------------------------------- #####
### SQL config
# Database system; "mysql" or "postgresql"
if [ -z "$DBMS" ]; then
#DBMS="mysql"
DBMS="postgresql"
fi
if [ $DBMS == "postgresql" ]; then
if [ ! -r ~/.pgpass ]; then
echo "~./pgpass does not exist, please create this file and support proper credentials for user postgres."
echo "Note: you need at least postgresql>= 7.3"
exit 1
fi
fi
if [ -z "$SQL_DB" ] ; then
SQL_DB=openser
fi
if [ -z "$SQL_HOST" ] ; then
SQL_HOST=localhost
fi
if [ -z "$SQL_USER" ] ; then
SQL_USER=openser
fi
# the read-only user for whom password may be stored here
if [ -z "$RO_USER" ] ; then
RO_USER=openserro
fi
if [ -z "$RO_PW" ] ; then
RO_PW=openserro
fi
##### ----------------------------------------------- #####
### binaries
if [ -z "$GENHA1" ] ; then
GENHA1='gen_ha1'
fi
if [ -z "$MYSQL" ] ; then
MYSQL='mysql'
fi
if [ -z "$PSQL" ] ; then
PSQL='psql'
fi
if [ -z "$SER" ] ; then
SER='openser'
fi
if [ -z "$LAST_LINE" ] ; then
LAST_LINE='tail -1'
fi
##### ----------------------------------------------- #####
### path to useful tools
if [ -z "$EGREP" ] ; then
EGREP="egrep"
fi
if [ -z "$AWK" ] ; then
AWK="awk"
fi
##### ----------------------------------------------- #####
# ACL name verification
if [ -z "$VERIFY_ACL" ] ; then
VERIFY_ACL=1
fi
if [ -z "$ACL_GROUPS" ] ; then
ACL_GROUPS="local ld int voicemail free-pstn prepaid"
fi
# fifo dbg
if [ -z "$FIFO_DBG" ] ; then
FIFO_DBG=0
fi
##### ----------------------------------------------- #####
#### SQL names
# UsrLoc Table
if [ -z "$UL_TABLE" ] ; then
UL_TABLE=location
fi
USER_COLUMN=username
CALLID_COLUMN=callid
# subscriber table
if [ -z "$SUB_TABLE" ] ; then
SUB_TABLE=subscriber
fi
REALM_COLUMN=domain
HA1_COLUMN=HA1
HA1B_COLUMN=HA1B
PASSWORD_COLUMN=password
RPID_COLUMN=rpid
SUBSCRIBER_COLUMN='username'
EMAIL_COLUMN=email_address
SUB_CREATED_COLUMN=datetime_created
SUB_MODIFIED_COLUMN=datetime_modified
PHP_LIB_COLUMN=phplib_id
# acl table
if [ -z "$ACL_TABLE" ] ; then
ACL_TABLE=grp
fi
ACL_USER_COLUMN=username
ACL_DOMAIN_COLUMN=domain
ACL_GROUP_COLUMN=grp
ACL_MODIFIED_COLUMN=last_modified
ACL_DOMAIN_COLUMN=domain
# aliases table
if [ -z "$ALS_TABLE" ] ; then
ALS_TABLE=aliases
fi
A_USER_COLUMN=username
A_CONTACT_COLUMN=contact
A_EXPIRES_COLUMN=expires
A_Q_COLUMN=q
A_CALLID_COLUMN=callid
A_CSEQ_COLUMN=cseq
A_LAST_MODIFIED_COLUMN=last_modified
# domain table
if [ -z "$DOMAIN_TABLE" ] ; then
DOMAIN_TABLE=domain
fi
# URI table
if [ -z "$URI_TABLE" ] ; then
URI_TABLE=uri
fi
URIUSER_COLUMN=uri_user
MODIFIED_COLUMN=last_modified
# dbaliases table
if [ -z "$DA_TABLE" ] ; then
DA_TABLE=dbaliases
fi
DA_USER_COLUMN=username
DA_DOMAIN_COLUMN=domain
DA_ALIAS_USER_COLUMN=alias_username
DA_ALIAS_DOMAIN_COLUMN=alias_domain
# speeddial table
if [ -z "$SD_TABLE" ] ; then
SD_TABLE=speed_dial
fi
SD_USER_COLUMN=username
SD_DOMAIN_COLUMN=domain
SD_SD_USER_COLUMN=sd_username
SD_SD_DOMAIN_COLUMN=sd_domain
SD_NEW_URI_COLUMN=new_uri
SD_DESC_COLUMN=description
# avp table
if [ -z "$AVP_TABLE" ] ; then
AVP_TABLE=usr_preferences
fi
AVP_UUID_COLUMN=uuid
AVP_USER_COLUMN=username
AVP_DOMAIN_COLUMN=domain
AVP_ATTRIBUTE_COLUMN=attribute
AVP_VALUE_COLUMN=value
AVP_TYPE_COLUMN=type
AVP_MODIFIED_COLUMN=modified
#===================================================================
usage_alias_db() {
if [ "$1" = "alone" ] ; then
CMD=`basename $0`
echo "$0 $VERSION"
echo
echo "'alias_db' command line options:"
echo
fi
cat <<EOF
alias_db show <alias> .............. show alias details
alias_db list <sip-id> ............. list aliases for uri
alias_db add <alias> <sip-id> ...... add an alias (*)
alias_db rm <alias> ................ remove an alias (*)
alias_db help ...................... help message
EOF
if [ "$1" = "alone" ] ; then
echo
echo " - <alias> must be an AoR (username at domain)"
echo " - <sip-id> must be an AoR (username at domain)"
echo
fi
}
usage_speeddial() {
if [ "$1" = "alone" ] ; then
CMD=`basename $0`
echo "$0 $VERSION"
echo
echo "'speeddial' command line options:"
echo
fi
cat <<EOF
speeddial show <speeddial-id> ....... show speeddial details
speeddial list <sip-id> ............. list speeddial for uri
speeddial add <sip-id> <sd-id> <new-uri> [<desc>] ... add a speedial (*)
speeddial rm <sip-id> <sd-id> ....... remove a speeddial (*)
speeddial help ...................... help message
EOF
if [ "$1" = "alone" ] ; then
echo
echo " - <speeddial-id>, <sd-id> must be an AoR (username at domain)"
echo " - <sip-id> must be an AoR (username at domain)"
echo " - <new-uri> must be a SIP AoR (sip:username at domain)"
echo " - <desc> a description for speeddial"
echo
fi
}
usage_avp() {
if [ "$1" = "alone" ] ; then
CMD=`basename $0`
echo "$0 $VERSION"
echo
echo "'avp' command line options:"
echo
fi
cat <<EOF
avp list [-T table] [-u <sip-id|uuid>]
[-a attribute] [-v value] [-t type] ... list AVPs
avp add [-T table] <sip-id|uuid>
<attribute> <type> <value> ............ add AVP (*)
avp rm [-T table] [-u <sip-id|uuid>]
[-a attribute] [-v value] [-t type] ... remove AVP (*)
avp help .................................. help message
EOF
if [ "$1" = "alone" ] ; then
echo
echo " - -T - table name"
echo " - -u - SIP id or unique id"
echo " - -a - AVP name"
echo " - -v - AVP value"
echo " - -t - AVP name and type (0 (str:str), 1 (str:int),"
echo " 2 (int:str), 3 (int:int))"
echo
echo " - <sip-id> must be an AoR (username at domain)"
echo " - <uuid> must be a string but not AoR"
echo
fi
}
usage() {
CMD=`basename $0`
if [ "0$VERIFY_ACL" -eq 1 ] ; then
EXTRA_TEXT="ACL privileges are: $ACL_GROUPS"
fi
cat <<EOF
$0 $VERSION
parameter usage:
* subscribers *
add <username> <password> <email> .. add a new subscriber (*)
passwd <username> <passwd> ......... change user's password (*)
rm <username> ...................... delete a user (*)
mail <username> .................... send an email to a user
alias show [<alias>] ............... show aliases
alias rm <alias> ................... remove an alias
alias add <alias> <uri> ............ add an aliases
rpid add <username> <rpid> ......... add rpid for a user (*)
rpid rm <username> ................. set rpid to NULL for a user (*)
rpid show <username> ............... show rpid of a user
EOF
echo
usage_alias_db
echo
usage_speeddial
echo
usage_avp
echo
cat <<EOF
* access control lists *
acl show [<username>] .............. show user membership
acl grant <username> <group> ....... grant user membership (*)
acl revoke <username> [<group>] .... grant user membership(s) (*)
* usrloc *
ul show [<username>]................ show in-RAM online users
ul rm <username> ................... delete user's UsrLoc entries
ul add <username> <uri> ............ introduce a permanent UrLoc entry
showdb [<username>] ................ show online users flushed in DB
* pa *
pa pres <p_uri> <pstate>............ set pstate for a presentity
pa loc <p_uri> <loc>................ set location for a presentity
* domains *
domain show ........................ show list of served domains
domain add <domainname> ............ add a new served domain
domain rm <domainname> ............. remove a served domain
* control and diagnostics *
moni ... show internal status start .... start ser
ps ..... show runnig processes stop ..... stop ser
fifo ... send raw FIFO commands restart .. restart ser
ping <uri> .. ping a URI (OPTIONS)
cisco_restart <uri> .. restart a Cisco phone (NOTIFY)
Commands labeled with (*) will prompt for a MySQL password.
If the variable PW is set, the password will not be prompted.
$EXTRA_TEXT
EOF
}
# determine host name, typically for use in printing UAC
# messages; we use today a simplistic but portable uname -n way --
# no domain name is displayed ; fifo_uac expands !! to host
# address only for optional header fields; uname output without
# domain is sufficient for informational header fields such as
# From
#
get_my_host() {
uname -n
}
# calculate name and domain of current user
set_user() {
SERUSER=`echo $1|awk -F @ '{print $1}'`
SERDOMAIN=`echo $1|awk -F @ '{print $2}'`
if [ -z "$SERDOMAIN" ] ; then
SERDOMAIN="$SIP_DOMAIN"
fi
if [ -z "$SERDOMAIN" ] ; then
echo "domain unknown: use usernames with domain or set default domain in SIP_DOMAIN"
exit 1
fi
}
# check the parameter if it is a valid address of record (user at domain)
check_aor() {
echo "$1" | $EGREP "^[a-zA-Z0-9_\.]+ at .*\..*" >/dev/null
if [ $? -ne 0 ] ; then
echo "error: invalid AoR: $1" > /dev/stderr
exit 1
fi
}
# check the parameter if it is a valid address of record (user at domain)
is_aor() {
echo "$1" | $EGREP "^[a-zA-Z0-9_\.]+ at .*\..*" >/dev/null
if [ $? -ne 0 ] ; then
false
else
true
fi
}
# check the parameter if it is a valid SIP address of record (sip:user at domain)
check_sipaor() {
echo "$1" | $EGREP "^sip(s)?:[a-zA-Z0-9_\.]+ at .*\..*" >/dev/null
if [ $? -ne 0 ] ; then
echo "error: invalid SIP AoR: $1" > /dev/stderr
exit 1
fi
}
# check the parameter if it is a valid SIP URI
# quite simplified now -- it captures just very basic
# errors
check_uri() {
echo "$1" | $EGREP "^sip(s)?:([a-zA-Z0-9_]+@)?.*\..*" > /dev/null
if [ $? -ne 0 ] ; then
echo "error: invalid SIP URI: $1" > /dev/stderr
exit 1
fi
}
# check for alias duplicates
check_alias() {
RES=`fifo_cmd ul_show_contact "$ALS_TABLE" "$1"`
RET="$?"
if [ $RET -ne 0 ] ; then
echo "error: SER/FIFO not accessible: $RET" \
> /dev/stderr
exit 1
fi
echo "$RES" | grep "^404" > /dev/null
if [ $? -eq 0 ] ; then
# alias does not exist yet, go ahead!
return 0
fi
echo "$RES" | grep "^400" > /dev/null
if [ $? -eq 0 ] ; then
echo "error: 400; check if you use aliases in SER" \
> /dev/stderr
exit 1
fi
echo "$RES" | grep "^200" > /dev/null
if [ $? -eq 0 ] ; then
echo "error: overlap with an existing alias" \
> /dev/stderr
exit 1
fi
# other errors
echo "error: $RES" > /dev/stderr
exit 1
}
#params: none
# output: PW
prompt_pw() {
if [ $DBMS == "mysql" ]; then
if [ -z "$PW" ] ; then
savetty=`stty -g`
printf "MySql password: " > /dev/stderr
stty -echo
read PW
stty $savetty
echo
fi
fi
}
print_status() {
echo $1 | grep "^[1-6][0-9][0-9]" > /dev/null
if [ "$?" -eq 0 ] ; then
echo $1
else
echo "200 OK"
fi
}
# process output from FIFO server; if everything is ok
# skip the first "ok" line and proceed to returned
# parameters
filter_fl()
{
# tail +2
awk 'BEGIN {line=0;IGNORECASE=1;}
{line++}
line==1 && /^200 ok/ { next }
{ print }'
}
fifo_cmd()
{
if [ "0${FIFO_DBG}" -eq 1 ] ; then
echo "entering fifo_cmd $*"
fi
if [ "$#" -lt 1 ]; then
echo "ERROR: fifo_cmd must take at least command name as parameter"
exit 1
fi
name=ser_receiver_$$
path=/tmp/$name
if [ ! -w $SER_FIFO ]; then
echo "Error opening ser's FIFO $SER_FIFO"
echo "Make sure you have line fifo=$SER_FIFO in your config"
exit 2
fi
mkfifo $path
if [ $? -ne 0 ] ; then
echo "error opening read fifo $path"
exit 3
fi
chmod a+w $path
# construct the command now
CMD=":$1:$name\n";
shift
while [ -n "$1" ] ; do
CMD="${CMD}${1}\n"
shift
done
CMD="${CMD}\n"
trap "rm -f $path; kill 0" 2
# start reader now so that it is ready for replies
# immediately after a request was sent out
cat < $path | filter_fl &
# issue FIFO request (printf taken to deal with \n)
printf "$CMD" > $SER_FIFO
# wait for the reader to complete
wait
rm $path
if [ "0${FIFO_DBG}" -eq 1 ] ; then
printf "FIFO command was:\n$CMD"
fi
}
# $1 = name $2=path $3=attempt
print_stats() {
echo "[cycle #: $3; if constant make sure server lives and fifo is on]"
cat < $2 | filter_fl &
cat > $SER_FIFO <<EOF
:version:$1
EOF
wait
cat < $2 | filter_fl &
cat > $SER_FIFO << EOF
:uptime:$1
EOF
wait
echo
echo Transaction Statistics
cat < $2 | filter_fl &
cat > $SER_FIFO <<EOF
:t_stats:$1
EOF
wait
echo
echo Stateless Server Statistics
cat < $2 | filter_fl &
cat > $SER_FIFO <<EOF
:sl_stats:$1
EOF
wait
echo
echo UsrLoc Stats
cat < $2 | filter_fl &
cat > $SER_FIFO <<EOF
:ul_stats:$1
EOF
wait
}
# input: sql query, optional mysql command-line params
sql_query() {
if [ $DBMS == "mysql" ]; then
# if password not yet queried, query it now
if [ -z "$PW" ] ; then
savetty=`stty -g`
printf "MySql password for user '$SQL_USER@$SQL_HOST': " > /dev/stderr
stty -echo
read PW >&2
stty $savetty
echo >&2
fi
$MYSQL $2 -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$1 ;" $SQL_DB
elif [ $DBMS == "postgresql" ]; then
echo "$PSQL -h $SQL_HOST -U $SQL_USER -c \"$1;\" $SQL_DB"
$PSQL -h $SQL_HOST -U $SQL_USER -c "$1;" $SQL_DB
else
echo "Wrong database type!"
exit 1
fi
}
# input: sql query, optional mysql command-line params
sql_ro_query() {
if [ $DBMS == "mysql" ]; then
$MYSQL $2 -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
-e "$1 ;" $SQL_DB
elif [ $DBMS == "postgresql" ]; then
$PSQL -h $SQL_HOST -U $RO_USER -c "$1;" $SQL_DB
else
echo "Wrong database type!"
exit 1
fi
}
usrloc() {
if [ "$#" -lt 2 ] ; then
echo "usrloc: too few parameters"
exit 1
fi
if [ "$1" = "alias" ] ; then
USRLOC_TABLE="$ALS_TABLE"
CHECK_SUB=1
elif [ "$1" = "ul" ] ; then
USRLOC_TABLE="$UL_TABLE"
CHECK_SUB=0
else
echo "usrloc: unknown table name"
exit 1
fi
shift
case $1 in
show)
if [ $# -eq 2 ] ; then
set_user $2
fifo_cmd ul_show_contact $USRLOC_TABLE "$SERUSER@$SERDOMAIN"
elif [ $# -eq 1 ] ; then
printf "Dumping all contacts may take long: are you sure you want to proceed? [Y|N] " > /dev/stderr
read answer
if [ "$answer" = "y" -o "$answer" = "Y" ] ; then
fifo_cmd ul_dump
fi
else
echo "wrong number of params for usrloc show"
usage
exit 1
fi
exit $?
;;
add)
if [ $# -ne 3 ] ; then
usage
exit 1
fi
shift
check_uri "$2"
set_user $1
if [ "$?" -ne "0" ] ; then
echo "$2 is not a valid URI"
exit 1
fi
if [ "$CHECK_SUB" -ne 0 ] ; then
is_user
if [ $? -eq 0 ] ; then
echo overlap of alias with an existing subscriber name
exit 1;
fi
fi
check_alias "$SERUSER@$SERDOMAIN"
# 128 means FL_PERSISTENT is on
fifo_cmd ul_add "$USRLOC_TABLE" "$SERUSER@$SERDOMAIN" "$2" "0" "1.00" "0" "128"
exit $?
;;
rm)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
shift
set_user $1
fifo_cmd ul_rm $USRLOC_TABLE "$SERUSER@$SERDOMAIN"
;;
*)
usage
exit 1
;;
esac
}
presence_agent() {
if [ "$#" -lt 4 ] ; then
echo "pa: too few parameters"
# usage
# exit 1
fi
shift
case $1 in
loc)
shift
# check_uri "$1"
if [ "$?" -ne "0" ] ; then
echo "$1 is not a valid URI"
exit 1
fi
fifo_cmd pa_location "registrar" "$1" "$2"
exit $?
;;
pres)
shift
# check_uri "$1"
if [ "$?" -ne "0" ] ; then
echo "$1 is not a valid URI"
exit 1
fi
fifo_cmd pa_presence "registrar" "$1" "$2"
exit $?
;;
*)
usage
exit 1
;;
esac
}
rpid() {
if [ "$#" -lt 2 ] ; then
echo "rpid: too few parameters"
exit 1
fi
shift;
case $1 in
show)
if [ $# -eq 2 ] ; then
set_user $2
is_user
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1;
fi
CLAUSE=" WHERE $SUBSCRIBER_COLUMN='$SERUSER' AND $REALM_COLUMN='$SERDOMAIN' "
elif [ $# -ne 1 ] ; then
usage
exit 1
fi
QUERY="select $SUBSCRIBER_COLUMN, $RPID_COLUMN FROM $SUB_TABLE $CLAUSE ; "
sql_ro_query "$QUERY"
;;
add|rm)
MODE=$1;
if [ "$MODE" == "add" ] ; then
ARG_NUM=3;
else
ARG_NUM=2;
fi
if [ $# -lt $ARG_NUM ] ; then
usage
exit 1
fi
prompt_pw
set_user $2
is_user
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1
fi
shift 2
if [ "$MODE" = "add" ] ; then
RPID_VAL="'$1'";
else
RPID_VAL=NULL;
fi
QUERY="UPDATE $SUB_TABLE \
SET $RPID_COLUMN=$RPID_VAL \
WHERE $SUBSCRIBER_COLUMN='$SERUSER' AND $REALM_COLUMN='$SERDOMAIN';"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "SQL Error"
exit 1
fi
$0 rpid show $SERUSER@$SERDOMAIN
;;
*)
usage
exit 1
;;
esac
}
domain() {
case $1 in
show)
# QUERY="select * FROM $DOMAIN_TABLE ; "
# sql_ro_query "$QUERY"
fifo_cmd domain_dump
;;
add)
shift
if [ $# -ne 1 ] ; then
echo missing domain to be added
exit 1
fi
prompt_pw
QUERY="insert into $DOMAIN_TABLE (domain) VALUES ('$1');"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "SQL Error"
exit 1
fi
fifo_cmd domain_reload
;;
rm)
shift
if [ $# -ne 1 ] ; then
echo missing domain to be removed
exit 1
fi
prompt_pw
QUERY="delete from $DOMAIN_TABLE where domain='$1';"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "SQL Error"
exit 1
fi
fifo_cmd domain_reload
;;
*)
usage
exit 1
esac
}
acl() {
case $1 in
show)
if [ $# -eq 2 ] ; then
set_user $2
is_user
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1;
fi
CLAUSE=" WHERE $ACL_USER_COLUMN='$SERUSER' AND $ACL_DOMAIN_COLUMN='$SERDOMAIN' "
elif [ $# -ne 1 ] ; then
usage
exit 1
fi
QUERY="select * FROM $ACL_TABLE $CLAUSE ; "
sql_ro_query "$QUERY"
;;
grant)
if [ $# -lt 3 ] ; then
usage
exit 1
fi
prompt_pw
set_user $2
is_user
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1
fi
shift 2
while [ $# -gt 0 ] ; do
if [ $VERIFY_ACL -eq 1 ] ; then
found=0
for i in $ACL_GROUPS ; do
if [ "$1" = "$i" ] ; then
found=1
break
fi
done
if [ $found -eq 0 ] ; then
echo "Invalid privilege: $1 ignored"
shift
continue
fi
fi
QUERY="insert into $ACL_TABLE \
($ACL_USER_COLUMN,$ACL_GROUP_COLUMN,$ACL_MODIFIED_COLUMN, $ACL_DOMAIN_COLUMN ) \
values ('$SERUSER','$1', now(), '$SERDOMAIN' );"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "SQL Error"
exit 1
fi
shift
done
$0 acl show $SERUSER@$SERDOMAIN
;;
revoke)
if [ $# -eq 3 ] ; then
CLAUSE=" and $ACL_GROUP_COLUMN='$3' "
elif [ $# -ne 2 ] ; then
usage
exit 1
fi
set_user $2
QUERY="delete from $ACL_TABLE where \
$ACL_TABLE.$ACL_USER_COLUMN='$SERUSER' AND $ACL_DOMAIN_COLUMN='$SERDOMAIN' $CLAUSE"
sql_query "$QUERY"
$0 acl show $2
;;
*)
usage
exit 1
;;
esac
}
# params: user
# output: false if exists, true otherwise
is_user() {
QUERY="select count(*) from $SUB_TABLE \
where $SUBSCRIBER_COLUMN='$SERUSER' and $REALM_COLUMN='$SERDOMAIN';"
CNT=`sql_ro_query "$QUERY" | grep -v ERROR | $LAST_LINE`
if [ "0$CNT" -eq 0 ] ; then
false
else
true
fi
}
# params: user
# output: false if exists, true otherwise
is_alias_db()
{
check_aor "$1"
if [ "$?" -ne "0" ] ; then
echo "is_alias_db: <$1> is not a valid AoR (user at domain)"
exit 1
fi
set_user $1
QUERY="select count(*) from $DA_TABLE \
where $DA_ALIAS_USER_COLUMN='$SERUSER' and $DA_ALIAS_DOMAIN_COLUMN='$SERDOMAIN';"
CNT=`sql_ro_query "$QUERY" | grep -v ERROR | $LAST_LINE`
if [ "0$CNT" -eq 0 ] ; then
false
else
true
fi
}
# params: user, password
# output: HA1, HA1B
credentials()
{
set_user $1
HA1=`$GENHA1 $SERUSER $SERDOMAIN $2`
if [ $? -ne 0 ] ; then
echo "HA1 calculation failed"
exit 1
fi
HA1B=`$GENHA1 "$SERUSER@$SERDOMAIN" $SERDOMAIN $2`
if [ $? -ne 0 ] ; then
echo "HA1B calculation failed"
exit 1
fi
}
# db-based aliases
dbaliases() {
if [ "$#" -lt 2 ] ; then
echo "alias_db: error - too few parameters"
echo
usage
exit 1
fi
if [ "$1" = "alias_db" ] ; then
shift
else
echo "alias_db: error - unknown command $1"
echo
usage
exit 1
fi
case $1 in
list)
if [ $# -eq 2 ] ; then
# print aliases for user
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "alias_db: <$2> is not a valid AoR (user at domain)"
exit 1
fi
set_user $2
CLAUSE="WHERE $DA_USER_COLUMN='$SERUSER' AND $DA_DOMAIN_COLUMN='$SERDOMAIN'"
echo "Dumping aliases for user=<$2>"
echo
QUERY="SELECT $DA_ALIAS_USER_COLUMN, $DA_ALIAS_DOMAIN_COLUMN FROM $DA_TABLE $CLAUSE;"
sql_ro_query "$QUERY" | $AWK 'BEGIN {line=0;}
/^\+/ { next }
{ if(line==0) print "ALIASES";
else print line ")\t" $1 "@" $2;
line++; }'
elif [ $# -eq 1 ] ; then
printf "Dumping all aliases may take long: do you want to proceed? [Y|N] "
read answer
if [ "$answer" = "y" -o "$answer" = "Y" ] ; then
echo "Dumping all aliases..."
echo
else
exit 1
fi
QUERY="SELECT $DA_ALIAS_USER_COLUMN, $DA_ALIAS_DOMAIN_COLUMN, $DA_USER_COLUMN, $DA_DOMAIN_COLUMN FROM $DA_TABLE;"
sql_ro_query "$QUERY" | $AWK 'BEGIN {line=0;}
/^\+/ { next }
{ line++;
if(line==1) print "SIP-ID \tALIAS\n";
else print $3 "@" $4 "\t" $1 "@" $2 }'
else
echo "alias_db: wrong number of params for command [list]"
usage
exit 1
fi
exit $?
;;
show)
if [ $# -ne 2 ] ; then
echo "alias_db: wrong number of params for command [show]"
usage
exit 1
fi
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "alias_db: $2 is not a valid AoR (user at domain)"
exit 1
fi
set_user $2
CLAUSE="WHERE $DA_ALIAS_USER_COLUMN='$SERUSER' AND $DA_ALIAS_DOMAIN_COLUMN='$SERDOMAIN'"
QUERY="SELECT $DA_USER_COLUMN, $DA_DOMAIN_COLUMN FROM $DA_TABLE $CLAUSE ; "
TMP_UUID=`sql_ro_query "$QUERY" | $AWK 'BEGIN {line=0;} /^\+/ { next }
{ line++; if(line==2) print $1 "@" $2;}'`
if [ "$TMP_UUID" = "" ] ; then
echo "non-existent alias <$2>"
exit 1
fi
echo "Details for alias <$2>"
echo
echo "SIP-ID: $TMP_UUID"
echo
exit $?
;;
add)
if [ $# -ne 3 ] ; then
usage
exit 1
fi
shift
check_aor "$1"
if [ "$?" -ne "0" ] ; then
echo "alias_db: $1 is not a valid AoR (user at domain)"
exit 1
fi
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "alias_db: $2 is not a valid AoR (user at domain)"
exit 1
fi
set_user $1
TMP_SERUSER=$SERUSER
TMP_SERDOMAIN=$SERDOMAIN
set_user $2
QUERY="INSERT INTO $DA_TABLE \
($DA_USER_COLUMN,$DA_DOMAIN_COLUMN,$DA_ALIAS_USER_COLUMN,$DA_ALIAS_DOMAIN_COLUMN) \
VALUES ('$SERUSER','$SERDOMAIN','$TMP_SERUSER','$TMP_SERDOMAIN' );"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "alias_db: SQL Error"
exit 1
fi
exit $?
;;
rm)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
shift
check_aor "$1"
if [ "$?" -ne "0" ] ; then
echo "alias_db: $1 is not a valid URI"
exit 1
fi
set_user $1
CLAUSE="WHERE $DA_ALIAS_USER_COLUMN='$SERUSER' AND $DA_ALIAS_DOMAIN_COLUMN='$SERDOMAIN'"
QUERY="DELETE FROM $DA_TABLE $CLAUSE;"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "alias_db: SQL Error"
exit 1
fi
exit $?
;;
help)
usage_alias_db "alone"
;;
*)
usage
exit 1
;;
esac
} # end db-aliases
# speed-dial
speed_dial() {
if [ "$#" -lt 2 ] ; then
echo "speeddial: error - too few parameters"
echo
usage
exit 1
fi
if [ "$1" = "speeddial" ] ; then
shift
else
echo "speeddial: error - unknown command $1"
echo
usage
exit 1
fi
case $1 in
list)
if [ $# -eq 2 ] ; then
# print speed-dials for user
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "speeddial: <$2> is not a valid AoR (user at domain)"
exit 1
fi
set_user $2
CLAUSE="WHERE $SD_USER_COLUMN='$SERUSER' AND $SD_DOMAIN_COLUMN='$SERDOMAIN'"
echo "Dumping speed-dials for user=<$2>"
echo
QUERY="SELECT $SD_SD_USER_COLUMN, $SD_SD_DOMAIN_COLUMN, $SD_NEW_URI_COLUMN, $SD_DESC_COLUMN FROM $SD_TABLE $CLAUSE;"
sql_ro_query "$QUERY" | $AWK 'BEGIN {line=0;}
/^\+/ { next }
{ if(line==0) print "## SpeedDial \tNew-URI \tDescription\n";
else {
ORS_BAK=ORS;
ORS="";
print line ") " $1 "@" $2 "\t" $3 "\t\"" $4;
for (i=5;i<=NF;++i) print FS $i;
ORS=ORS_BAK;
print "\"";
}
line++;
}'
elif [ $# -eq 1 ] ; then
printf "Dumping all speed-dials may take long: do you want to proceed? [Y|N] "
read answer
if [ "$answer" = "y" -o "$answer" = "Y" ] ; then
echo "Dumping all speed-dials..."
echo
else
exit 1
fi
QUERY="SELECT $SD_SD_USER_COLUMN, $SD_SD_DOMAIN_COLUMN, $SD_USER_COLUMN, $SD_DOMAIN_COLUMN, $SD_NEW_URI_COLUMN, $SD_DESC_COLUMN FROM $SD_TABLE;"
sql_ro_query "$QUERY" | $AWK 'BEGIN {line=0;}
/^\+/ { next }
{ line++;
if(line==1) print "SIP-ID \tSpeedDial \tNew-URI \tDescritpion\n";
else {
ORS_BAK=ORS;
ORS="";
print $3 "@" $4 "\t" $1 "@" $2 "\t" $5 "\t\"" $6;
for (i=7;i<=NF;++i) print FS $i;
ORS=ORS_BAK;
print "\"";
}
}'
else
echo "speeddial: wrong number of params for command [list]"
usage
exit 1
fi
exit $?
;;
show)
if [ $# -ne 2 ] ; then
echo "speeddial: wrong number of params for command [show]"
usage
exit 1
fi
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "speeddial: $2 is not a valid AoR (user at domain)"
exit 1
fi
set_user $2
CLAUSE="WHERE $SD_SD_USER_COLUMN='$SERUSER' AND $SD_SD_DOMAIN_COLUMN='$SERDOMAIN'"
QUERY="SELECT $SD_USER_COLUMN, $SD_DOMAIN_COLUMN, $SD_NEW_URI_COLUMN, $SD_DESC_COLUMN FROM $SD_TABLE $CLAUSE ; "
echo "Details for speeddial <$2>"
sql_ro_query "$QUERY" | $AWK 'BEGIN {line=0;} /^\+/ { next }
{
if(line==0) print "## SIP-ID \tNew-URI \tDescritpion\n";
else {
ORS_BAK=ORS;
ORS="";
print line ") " $1 "@" $2 "\t" $3 "\t\"" $4;
for (i=5;i<=NF;++i) print FS $i;
ORS=ORS_BAK;
print "\"";
}
line++;
}'
exit $?
;;
add)
if [ $# -ne 4 ] ; then
if [ $# -ne 5 ] ; then
usage
exit 1
fi
fi
shift
check_aor "$1"
if [ "$?" -ne "0" ] ; then
echo "speeddial: $1 is not a valid AoR (user at domain)"
exit 1
fi
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "speeddial: $2 is not a valid AoR (user at domain)"
exit 1
fi
check_sipaor "$3"
if [ "$?" -ne "0" ] ; then
echo "speeddial: $2 is not a valid SIP AoR (sip:user at domain)"
exit 1
fi
set_user $1
TMP_SERUSER=$SERUSER
TMP_SERDOMAIN=$SERDOMAIN
set_user $2
QUERY="INSERT INTO $SD_TABLE \
($SD_USER_COLUMN,$SD_DOMAIN_COLUMN,$SD_SD_USER_COLUMN,$SD_SD_DOMAIN_COLUMN,$SD_NEW_URI_COLUMN,$SD_DESC_COLUMN) \
VALUES ('$TMP_SERUSER','$TMP_SERDOMAIN','$SERUSER','$SERDOMAIN','$3','$4');"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "speeddial: SQL Error"
exit 1
fi
exit $?
;;
rm)
if [ $# -ne 3 ] ; then
echo "speeddial rm: invalid number of parameters"
usage
exit 1
fi
shift
check_aor "$1"
if [ "$?" -ne "0" ] ; then
echo "speeddial: $1 is not a valid AoR (user at domain)"
exit 1
fi
check_aor "$2"
if [ "$?" -ne "0" ] ; then
echo "speeddial: $2 is not a valid AoR (user at domain)"
exit 1
fi
set_user $1
TMP_SERUSER=$SERUSER
TMP_SERDOMAIN=$SERDOMAIN
set_user $2
CLAUSE="WHERE $SD_USER_COLUMN='$TMP_SERUSER' AND $SD_DOMAIN_COLUMN='$TMP_SERDOMAIN' \
AND $SD_SD_USER_COLUMN='$SERUSER' AND $SD_SD_DOMAIN_COLUMN='$SERDOMAIN'"
QUERY="DELETE FROM $SD_TABLE $CLAUSE;"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "speeddial: SQL Error"
exit 1
fi
;;
help)
usage_speeddial "alone"
;;
*)
echo "speeddial: unknown command"
usage
exit 1
;;
esac
} # end speed_dial()
# AVP operations
# avp list [-T table] [-u <sip-id|uuid>]
# [-a attribute] [-v value] [-t type] ... list AVPs
# avp add [-T table] <sip-id|uuid>
# <attribute> <type> <value> ............ add AVP (*)
# avp rm [-T table] [-u <sip-id|uuid>]
# [-a attribute] [-v value] [-t type] ... remove AVP (*)
avpops() {
if [ "$#" -lt 2 ] ; then
echo "avpops: error - too few parameters"
echo
usage
exit 1
fi
if [ "$1" = "avp" ] ; then
shift
else
echo "avpops: error - unknown command $1"
echo
usage
exit 1
fi
case $1 in
list)
shift
CLAUSE=""
while [ "$#" != "0" ]
do
TMP_ARG=$1
shift
case $TMP_ARG in
-T)
if [ -z "$1" ] ; then
echo "avpops: table name parameter missing"
exit 1
fi
AVP_TABLE=$1
;;
-u)
if [ -z "$1" ] ; then
echo "avpops: user id or uuid parameter missing"
exit 1
fi
is_aor "$1"
if [ "$?" -eq "0" ] ; then
set_user $1
if [ "$CLAUSE" = "" ] ; then
CLAUSE=" WHERE $AVP_USER_COLUMN='$SERUSER' AND $AVP_DOMAIN_COLUMN='$SERDOMAIN'"
else
CLAUSE="$CLAUSE AND $AVP_USER_COLUMN='$SERUSER' AND $AVP_DOMAIN_COLUMN='$SERDOMAIN'"
fi
else
if [ "$CLAUSE" = "" ] ; then
CLAUSE=" WHERE $AVP_UUID_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_UUID_COLUMN='$1'"
fi
fi
;;
-a)
if [ -z "$1" ] ; then
echo "avpops: AVP attribute name parameter missing"
exit 1
fi
if [ "$CLAUSE" = "" ] ; then
CLAUSE=" WHERE $AVP_ATTRIBUTE_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_ATTRIBUTE_COLUMN='$1'"
fi
;;
-v)
if [ -z "$1" ] ; then
echo "avpops: AVP value parameter missing"
exit 1
fi
if [ "$CLAUSE" = "" ] ; then
CLAUSE=" WHERE $AVP_VALUE_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_VALUE_COLUMN='$1'"
fi
;;
-t)
if [ -z "$1" ] ; then
echo "avpops: AVP type parameter missing"
exit 1
fi
if [ "$CLAUSE" = "" ] ; then
CLAUSE=" WHERE $AVP_TYPE_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_TYPE_COLUMN='$1'"
fi
;;
*)
echo "avpops: unknown parameter $1"
exit 1
;;
esac
shift
done
QUERY="SELECT $AVP_UUID_COLUMN, $AVP_USER_COLUMN, $AVP_DOMAIN_COLUMN, $AVP_ATTRIBUTE_COLUMN, $AVP_TYPE_COLUMN, $AVP_VALUE_COLUMN FROM $AVP_TABLE $CLAUSE;"
# echo "Query: $QUERY"
echo "Dumping AVPs"
echo
sql_ro_query "$QUERY" # | $AWK 'BEGIN {line=0;}
# /^\+/ { next }
# { if(line==0) print "## UUID \tUserID \tAttribute \tType \tValue\n";
# else {
# ORS_BAK=ORS;
# ORS="";
# print line ") " $1 $2 "@" $3 "\t" $4 "\t\"" $5;
# for (i=6;i<=NF;++i) print FS $i;
# ORS=ORS_BAK;
# print "\"";
# }
# line++;
# }'
exit $?
;;
add)
shift
if [ $# -ne 4 ] ; then
if [ $# -ne 6 ] ; then
echo "avpops add: bad number of parameters"
echo
usage
exit 1
fi
fi
if [ $# -eq 6 ] ; then
if [ "$1" = "-T" ] ; then
AVP_TABLE=$2
shift
shift
else
echo "avpops add: unknown parameter '$1'"
echo
usage
exit 1
fi
fi
is_aor "$1"
if [ "$?" -eq "0" ] ; then
set_user $1
else
AVP_UUID=$1
fi
QUERY="INSERT INTO $AVP_TABLE \
($AVP_UUID_COLUMN,$AVP_USER_COLUMN,$AVP_DOMAIN_COLUMN,$AVP_ATTRIBUTE_COLUMN,$AVP_TYPE_COLUMN,$AVP_VALUE_COLUMN,$AVP_MODIFIED_COLUMN) \
VALUES ('$AVP_UUID','$SERUSER','$SERDOMAIN','$2',$3,'$4',NOW());"
# echo "Query: $QUERY"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "avpops: SQL Error"
exit 1
else
echo
echo "avpops: AVP added"
fi
exit $?
;;
rm)
shift
CLAUSE=""
while [ "$#" != "0" ]
do
TMP_ARG=$1
shift
case $TMP_ARG in
-T)
if [ -z "$1" ] ; then
echo "avpops: table name parameter missing"
exit 1
fi
AVP_TABLE=$1
;;
-u)
if [ -z "$1" ] ; then
echo "avpops: user id or uuid parameter missing"
exit 1
fi
is_aor "$1"
if [ "$?" -eq "0" ] ; then
set_user $1
if [ "$CLAUSE" = "" ] ; then
CLAUSE="WHERE $AVP_USER_COLUMN='$SERUSER' AND $AVP_DOMAIN_COLUMN='$SERDOMAIN'"
else
CLAUSE="$CLAUSE AND $AVP_USER_COLUMN='$SERUSER' AND $AVP_DOMAIN_COLUMN='$SERDOMAIN'"
fi
else
if [ "$CLAUSE" = "" ] ; then
CLAUSE="WHERE $AVP_UUID_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_UUID_COLUMN='$1'"
fi
fi
;;
-a)
if [ -z "$1" ] ; then
echo "avpops: AVP attribute name parameter missing"
exit 1
fi
if [ "$CLAUSE" = "" ] ; then
CLAUSE="WHERE $AVP_ATTRIBUTE_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_ATTRIBUTE_COLUMN='$1'"
fi
;;
-v)
if [ -z "$1" ] ; then
echo "avpops: AVP value parameter missing"
exit 1
fi
if [ "$CLAUSE" = "" ] ; then
CLAUSE="WHERE $AVP_VALUE_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_VALUE_COLUMN='$1'"
fi
;;
-t)
if [ -z "$1" ] ; then
echo "avpops: AVP type parameter missing"
exit 1
fi
if [ "$CLAUSE" = "" ] ; then
CLAUSE="WHERE $AVP_TYPE_COLUMN='$1'"
else
CLAUSE="$CLAUSE AND $AVP_TYPE_COLUMN='$1'"
fi
;;
*)
echo "avpops: unknown parameter $1"
exit 1
;;
esac
shift
done
QUERY="DELETE FROM $AVP_TABLE $CLAUSE;"
# echo "Query: $QUERY"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "avpops: SQL Error"
exit 1
else
echo
echo "avpops: AVP(s) deleted"
fi
exit $?
;;
help)
usage_avp "alone"
;;
*)
echo "avpops: unknown command"
usage
exit 1
;;
esac
} # end avpops()
#================================================================
# if the script calls itself ...
export PW
case $1 in
start)
echo
printf "Starting SER : "
if [ -r $PID_FILE ] ; then
echo "PID file exists! ($PID_FILE) already running?"
exit 1
else
if [ ! -x "$SERBIN" ] ; then
echo "SER binaries not found at $SERBIN; reconfigure SERBIN in $0"
exit 1
fi
if [ $SYSLOG = 1 ] ; then
$SERBIN -P $PID_FILE $STARTOPTIONS 1>/dev/null 2>/dev/null
else
$SERBIN -P $PID_FILE -E $STARTOPTIONS
fi
sleep 1
if [ ! -s $PID_FILE ] ; then
echo "PID file $PID_FILE does not exist -- SER start failed"
exit 1
fi
echo "started pid(`cat $PID_FILE`)"
fi
exit 0
;;
stop)
printf "Stopping SER : "
if [ -r $PID_FILE ] ; then
kill `cat $PID_FILE`
echo "stopped"
else
echo No PID file found! SER probably not running
exit 1
fi
exit 0
;;
restart)
$0 stop
if [ "$?" -ne 0 ] ; then
exit 1
fi
sleep 2
$0 start
exit 0
;;
passwd)
if [ $# -ne 3 ] ; then
usage
exit 1
fi
shift
credentials $1 $2
prompt_pw
is_user $1
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1
fi
QUERY="update $SUB_TABLE \
set $HA1_COLUMN='$HA1', $HA1B_COLUMN='$HA1B', $PASSWORD_COLUMN='$2' \
, $SUB_MODIFIED_COLUMN=now() \
WHERE $SUBSCRIBER_COLUMN='$SERUSER' and $REALM_COLUMN='$SERDOMAIN';"
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "password change failed"
else
echo "password change succeeded"
fi
;;
add)
if [ $# -ne 4 ] ; then
usage
exit 1
fi
shift
credentials $1 $2
prompt_pw
is_user $1
if [ $? -eq 0 ] ; then
echo user already exists
exit 1
fi
# check_alias "$SERUSER@$SERDOMAIN"
is_alias_db $1
if [ $? -eq 0 ] ; then
echo username already exists as alias
exit 1
fi
set_user $1
QUERY="insert into $SUB_TABLE \
($SUBSCRIBER_COLUMN,$REALM_COLUMN,$HA1_COLUMN,\
$HA1B_COLUMN,$PASSWORD_COLUMN,$EMAIL_COLUMN,$SUB_CREATED_COLUMN, \
$PHP_LIB_COLUMN) \
values ('$SERUSER','$SERDOMAIN','$HA1','$HA1B','$2','$3',now(),'$HA1');";
sql_query "$QUERY"
if [ $? -ne 0 ] ; then
echo "introducing the new user to the database failed"
else
echo "new user added"
fi
;;
monitor|console|moni|con)
name=ser_receiver_$$
path=/tmp/$name
if [ ! -w $SER_FIFO ]; then
echo "Error opening ser's FIFO $SER_FIFO"
echo "Make sure you have line fifo=$SER_FIFO in your config"
exit 1
fi
mkfifo $path
if [ $? -ne 0 ] ; then
echo "error opening read fifo $path"
exit 1
fi
chmod a+w $path
trap "rm $path; clear; echo sc monitor ^C-ed; exit 1" 2
attempt=0
if [ "$2" == "" ]; then
loops=-1;
else
loops=$2;
fi
clear
while [ $loops -ne $attempt ] ; do
attempt=$(($attempt + 1))
#clear
tput cup 0 0
print_stats $name $path $attempt
if [ $loops -ne $attempt ] ; then
sleep $WATCH_PERIOD
fi
done
rm $path
exit 0
;;
mail)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
shift
set_user $1
QUERY="select $SUB_TABLE.$EMAIL_COLUMN from $SUB_TABLE where \
$SUB_TABLE.$SUBSCRIBER_COLUMN='$SERUSER' and $SUB_TABLE.$REALM_COLUMN='$SERDOMAIN'"
EA=`sql_ro_query "$QUERY" "-B" | grep -v ERROR | $LAST_LINE`
if [ $? -ne 0 ] ; then
echo "MySql query failed"
exit 1
fi
echo "Write email to $1: $EA now ..."
mail -s "Message from $SERDOMAIN SIP admin" $EA
if [ $? -eq 0 ] ; then
echo message sent
else
echo sending message failed
fi
;;
alias|ul)
usrloc "$@"
;;
alias_db)
dbaliases "$@"
;;
speeddial)
speed_dial "$@"
;;
avp)
avpops "$@"
;;
pa)
presence_agent "$@"
;;
rpid)
rpid "$@"
;;
online)
fifo_cmd ul_dump |grep aor| awk '{print $3}' | sort | sort -mu
exit $?
;;
showdb|userdb)
shift
if [ $# -eq 1 ] ; then
set_user $1
is_user
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1;
fi
QUERY1="select $SUB_TABLE.$EMAIL_COLUMN from $SUB_TABLE \
where $SUB_TABLE.$SUBSCRIBER_COLUMN='$SERUSER' and \
$SUB_TABLE.$REALM_COLUMN='$SERDOMAIN' "
QUERY2="select $UL_TABLE.* from $UL_TABLE where \
$UL_TABLE.$USER_COLUMN='$SERUSER' and
$UL_TABLE.$REALM_COLUMN='$SERDOMAIN' order by expires desc"
sql_ro_query "$QUERY1"
sql_ro_query "$QUERY2"
else
QUERY3="select $UL_TABLE.$USER_COLUMN, $SUB_TABLE.$EMAIL_COLUMN, \
$UL_TABLE.$CALLID_COLUMN from $SUB_TABLE, $UL_TABLE where \
$SUB_TABLE.$SUBSCRIBER_COLUMN=$UL_TABLE.$USER_COLUMN \
order by $UL_TABLE.$USER_COLUMN"
sql_ro_query "$QUERY3"
fi
echo "Note: Due to usage of cache, server's list " \
"may differ from DB list."
;;
rm)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
shift
prompt_pw
set_user $1
is_user
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1
fi
# begin with remove all user's privileges
$0 acl revoke $1 > /dev/null 2>&1
QUERY="delete from $DA_TABLE where $DA_USER_COLUMN='$SERUSER' and $DA_DOMAIN_COLUMN='$SERDOMAIN'"
sql_query "$QUERY"
# destroy the user now
QUERY="delete from $SUB_TABLE where $SUB_TABLE.$SUBSCRIBER_COLUMN='$SERUSER' and $SUB_TABLE.$REALM_COLUMN='$SERDOMAIN'"
sql_query "$QUERY"
# and also all his contacts
$0 ul rm $1 > /dev/null 2>&1
;;
ps)
fifo_cmd ps
;;
acl)
shift
acl "$@"
;;
domain)
shift
domain "$@"
;;
fifo)
shift
fifo_cmd "$@"
;;
ping)
# error handling is hacked -- filter_fl should not
# consume positive status -- that should be done by
# calling app
if [ "$#" -ne 2 ] ; then
usage
exit 1
fi
myhost=`get_my_host`
RET=`fifo_cmd t_uac_dlg OPTIONS "$2" "." \
"From: sip:daemon@$myhost" \
"To: <$2>" "Contact: <sip:daemon@!!>" "." "." \
| head -1 `
print_status $RET
;;
cisco_restart)
if [ "$#" -ne 2 ] ; then
usage
exit 1
fi
myhost=`get_my_host`
RET=`fifo_cmd t_uac_dlg NOTIFY "$2" "." \
"From: sip:daemon@$myhost" \
"To: <$2>" "Event: check-sync" \
"Contact: <sip:daemon@!!>" "." "." |
head -1 `
print_status $RET
;;
version)
echo "$0 $VERSION"
;;
*)
usage
exit 1
;;
esac
More information about the Devel
mailing list