#
# $Id: openserctl.sqlbase 2429 2007-07-10 11:00:20Z henningw $
#
# sc: openser control; tool for maintaining openser
#
#===================================================================

##### ----------------------------------------------- #####
### common variables and functions for SQL engines

if [ -z "$DBNAME" ] ; then
	DBNAME=openser
fi
if [ -z "$DBHOST" ] ; then
	DBHOST=localhost
fi
if [ -z "$DBRWUSER" ] ; then
	DBRWUSER=openser
fi

# the read-only user for whom password may be stored here
if [ -z "$DBROUSER" ] ; then
	DBROUSER=openserro
fi
if [ -z "$DBROPW" ] ; then
	DBROPW=openserro
fi

# full privileges SQL user
if [ -z "$DBROOTUSER" ]; then
	DBROOTUSER="root"
fi

##### ----------------------------------------------- #####
#### SQL names

# UsrLoc Table
if [ -z "$UL_TABLE" ] ; then
	UL_TABLE=location
fi
USER_COLUMN=username
DOMAIN_COLUMN=domain
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
PHP_LIB_COLUMN=phplib_id
if [ -z "$STORE_PLAINTEXT_PW" ] ; then
	STORE_PLAINTEXT_PW=1
fi

# 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

# 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
DO_DOMAIN_COLUMN=domain

# lcr tables
if [ -z "$LCR_TABLE" ] ; then
	LCR_TABLE=lcr
fi
LCR_PREFIX_COLUMN=prefix
LCR_FROMURI_COLUMN=from_uri
LCR_GRPID_COLUMN=grp_id
LCR_PRIO_COLUMN=priority
if [ -z "$GW_TABLE" ] ; then
	GW_TABLE=gw
fi
LCR_GW_GWNAME_COLUMN=gw_name
LCR_GW_GRPID_COLUMN=grp_id
LCR_GW_IP_COLUMN=ip_addr
LCR_GW_PORT_COLUMN=port
LCR_GW_URIS_COLUMN=uri_scheme
LCR_GW_PROTO_COLUMN=transport
LCR_GW_STRIP_COLUMN=strip
LCR_GW_PREFIX_COLUMN=prefix
if [ -z "$GW_GRP_TABLE" ] ; then
	GW_GRP_TABLE=gw_grp
fi
LCR_GRP_ID_COLUMN=grp_id
LCR_GRP_NAME_COLUMN=grp_name

# 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=last_modified

##### ----------------------------------------------- #####
### 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"
fi


#
##### ------------------------------------------------ #####
### usage functions
#
usage_acl() {
	echo
	mecho " -- command 'acl' - manage access control lists (acl)"
	echo
cat <<EOF
 acl show [<username>] .............. show user membership
 acl grant <username> <group> ....... grant user membership (*)
 acl revoke <username> [<group>] .... grant user membership(s) (*)
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_acl"


usage_alias_db() {
	echo
	mecho " -- command 'alias_db' - manage database aliases"
	echo
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
    - <alias> must be an AoR (username@domain)"
    - <sip-id> must be an AoR (username@domain)"
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_alias_db"

usage_avp() {
	echo
	mecho " -- command 'avp' - manage AVPs"
	echo
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
    - -T - table name
    - -u - SIP id or unique id
    - -a - AVP name
    - -v - AVP value
    - -t - AVP name and type (0 (str:str), 1 (str:int),
                              2 (int:str), 3 (int:int))
    - <sip-id> must be an AoR (username@domain)
    - <uuid> must be a string but not AoR
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_avp"

usage_db_ops() {
	echo
	mecho " -- command 'db' - database operations"
	echo
cat <<EOF
 db exec <query> ..................... execute SQL query
 db roexec <roquery> ................. execute read-only SQL query
 db run <id> ......................... execute SQL query from \$id variable
 db rorun <id> ....................... execute read-only SQL query from 
                                       \$id variable
 db show <table> ..................... display table content
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_db_ops"

usage_lcr() {
	echo
	mecho " -- command 'lcr' - manage least cost routes (lcr)"
	echo
cat <<EOF
           * lcr *
   * IP addresses must be entered in dotted quad format e.g. 1.2.3.4 *
   * <uri_scheme> and <transport> must be entered in integer or text,*
   * e.g. transport '2' is identical to transport 'tcp'.             *
   *   scheme: 1=sip, 2=sips;   transport: 1=udp, 2=tcp, 3=tls       *
   * Examples:  lcr addgw_grp usa 1                                  *
   *            lcr addgw level3 1.2.3.4 5080 sip tcp 1              *
   *            lcr addroute +1 '' 1 1                               *
 lcr show ....................................................................
           ............. show routes, gateways and groups
 lcr reload ..................................................................
           ............. reload lcr gateways
 lcr addgw_grp <grp_name> ....................................................
           .............. add gateway group, autocreate grp_id
 lcr addgw_grp <grp_name> <grp_id> ...........................................
           ............... add gateway group with grp_id
 lcr rmgw_grp  <grp_id> ......................................................
           ............... delete the gw_grp
 lcr addgw <gw_name> <ip> <port> <scheme> <transport> <grp_id> ...............
           ............... add a gateway
 lcr addgw <gw_name> <ip> <port> <scheme> <transport> <grp_id> <prefix> ......
           ............... add a gateway with prefix
 lcr addgw <gw_name> <ip> <port> <scheme> <transport> <grp_id> <prefix> <strip>
           ............... add a gateway with prefix and strip
 lcr rmgw  <gw_name> .........................................................
           ............... delete a gateway
 lcr addroute <prefix> <from> <grp_id> <prio> ................................
           .............. add a route ( use '' to match anything in <from> )
 lcr rmroute  <prefix> <from> <grp_id> <prio> ................................
           .............. delete a route
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_lcr"


usage_rpid() {
	echo
	mecho " -- command 'rpid' - manage Remote-Party-ID (RPID)"
	echo
cat <<EOF
 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
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_rpid"

usage_speeddial() {
	echo
	mecho " -- command 'speeddial' - manage speed dials (short numbers)"
	echo
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
    - <speeddial-id>, <sd-id> must be an AoR (username@domain)
    - <sip-id> must be an AoR (username@domain)
    - <new-uri> must be a SIP AoR (sip:username@domain)
    - <desc> a description for speeddial
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_speeddial"

usage_subscriber() {
	echo
	mecho " -- command 'add|mail|passwd|rm' - manage subscribers"
	echo
cat <<EOF
 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
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_subscriber"

usage_domain() {
	echo
	mecho " -- command 'domain' - manage domains"
	echo
cat <<EOF
  domain show ........................ show list of served domains
  domain showdb .......................show list of domains from database
  domain add <domainname> ............ add a new served domain
  domain rm <domainname> ............. remove a served domain
  domain reload .......................reload domain list from database
EOF
}
USAGE_FUNCTIONS="$USAGE_FUNCTIONS usage_domain"

#params: none
# output: DBRWPW
prompt_pw() {
	if [ -z "$DBRWPW" ] ; then
		savetty=`stty -g`
		if [ -z "$1" ] ; then
			printf "Password: " > /dev/stderr
		else
			printf "$1: " > /dev/stderr
		fi
		stty -echo
    	read DBRWPW
		stty $savetty
    	echo
	fi
}