[Devel] postgres tables example
Daniel-Constantin Mierla
daniel at voice-system.ro
Fri Jul 1 13:14:42 CEST 2005
Hello,
thanks for these updates. Will be imported in the cvs very soon. Maybe a
merge with Cesc's dbtext support should be done -- I will take a look to
see how compile would be.
Regards,
Daniel
On 07/01/05 13:53, Klaus Darilion wrote:
> 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
>>>
>>>
>>
>>
>
>------------------------------------------------------------------------
>
>#!/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
>
>
>
>------------------------------------------------------------------------
>
>#!/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