#!/usr/bin/perl 
#
#$Id: osipsconsole 6386 2009-12-10 11:32:06Z iulia_bublea $
#
# Copyright (C) 2008-2009 Voice Sistem S.R.L
#
# This file is part of opensips, a free SIP server.
#
# opensips is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version
#
# opensips is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
# History:
# -------
#  2008-10-10  first version (iulia_bublea)
#

#use strict;
#use warnings;
use Term::ReadLine;
use DBI;
use POSIX;
use Frontier::RPC2;
use IO::Socket;
use Socket;
#use Net::IP;
#use BerkeleyDB;
#use Config::General;
####variables that need to be changed

##### ------------------------------------------------ #####
#

####path to the directory where opensips binaries are...
my $PATH_BIN = "/usr/sbin/";
my $PATH_CTLRC = "/usr/etc/opensips/";
my $PATH_ETC = "/usr/local/etc";
my $PATH_LIBS = "/usr/local/lib";
my $PATH_SHARE = "/usr/local/share";

my $OSIPSCONSOLERC = "";
### include config files
if ( -e  $PATH_CTLRC . "osipsconsolerc" ) {
	$OSIPSCONSOLERC = $PATH_CTLRC . "osipsconsolerc";
	&include_osipsconsolerc();	
}


if ( -e "~/.osipsconsolerc" ) {
	$OSIPSCONSOLERC = "~/.osipsconsolerc";
	&include_osipsconsolerc();	
}

#$conf = new Config::General("$OSIPSCONSOLERC");
#my %config = $conf->getall;

my $OSIPSBIN = $PATH_BIN . "opensips";
my $HISTORY_FILE = "/tmp/osipsconsole_history";
my $argnum = $#ARGV + 1;
### aliases configuration  ( DB | UL )
my $ALIASES_TYPE = "DB";

my ($socket, @ACL_GROUPS, @arr, $var_name, @OSIPS, $STORE_PLAINTEXT_PW, $DUMP_CMD, $LOAD_CMD, $HA1, $HA1B, $sth, $dbh, @row, $RET, $RES,
    $DBENGINELOADED, $cmd_fifo, $CHROOT_DIR, $input, $response, $CHECK_SUB, $USRLOC_TABLE, @content, $line, @list, @cmd, @output, $command);

my $ETCDIR = " ";

my $MI_CONNECTOR = "";
my $OSIPS_FIFO = "";
my $OSIPS_UNIXSOCK = "";
my $OSIPSUNIX = "";
my $OSIPSIP = "";
my $OSIPS_PORT = "";

my $fifo_reply_file = "fifo_reply";
my $fifo_reply_path = "/tmp/" . $fifo_reply_file;
my $PID_FILE = "";
my $DBENGINE = "";
my $SIP_DOMAIN = "";
my $VERIFY_ACL = 0;

my $DBNAME = "";
my $DBHOST = "";
my $DBPORT = "";
my $DBRWUSER = "";
my $DBROUSER = "";
my $DBROPW = "";
my $DBROOTUSER = "";
my $DBRWPW = "";
my $CTLENGINE = "";

my $migrate_trusted=1;
my $migrate_address=1;	
my $EGREP = "";
my $SYSLOG = "";
my $STARTOPTIONS = "";
my $TOOLPATH = "";
my $AWK = "";
my $MD5 = "";
my $SED = "";
my $LAST_LINE = "";
my $EXPR = "";
my $WATCH_PERIOD = "";
##### ----------------------------------------------- #####
#### Defined values
my $ALL_METHODS=4294967295;
my $USERNAME_RE="[-a-zA-Z0-9&=\+\$,;\?/_\.\!~\*'\(\)]+";

my $OSIPSUSER = "";
my $OSIPSDOMAIN = "";
my $CTLENGINELOADED = 0;
my $ENABLE_ALIASES = 0;
my $ALIAS_EXISTS = 0;
######-----------------------------------------------#######
##database specific variables
#
my @STANDARD_TABLES = ('version', 'acc', 'dbaliases', 'lcr', 
			'domain', 'grp', 'uri', 'speed_dial', 
			'gw', 'pdt', 'subscriber', 'location', 
			're_grp', 'address', 'missed_calls', 
			'usr_preferences', 'aliases', 'silo', 
			'dialog', 'dispatcher', 'dialplan',
			'dr_gateways','dr_rules','dr_gw_lists',
			'dr_groups','nh_sockets','load_balancer');

my @STANDARD_MODULES = ('standard', 'acc', 'lcr', 'domain', 'group', 
	'permissions', 'registrar', 'usrloc', 'msilo', 'alias_db', 'uri_db',
	'speeddial', 'avpops', 'auth_db', 'pdt', 'dialog', 'dispatcher', 
	'dialplan','drouting','nathelper','load_balancer' );

my @EXTRA_TABLES = ('imc_members', 'imc_rooms', 'cpl', 'sip_trace', 
	'domainpolicy', 'carrierroute', 'route_tree', 'carrierfailureroute', 
	'userblacklist', 'globalblacklist');

my @EXTRA_MODULES = ('imc', 'cpl', 'siptrace', 'domainpolicy', 'carrierroute', 'userblacklist');

my @PRESENCE_TABLES = ('presentity', 'active_watchers', 'watchers', 'xcap', 'pua');

my @PRESENCE_RLS_TABLES = ('rls_presentity', 'rls_watchers');

my $HAS_EXTRA = "NO";
#my $NO_USER_INIT = "NO";
my $MYSQL = "";
my $PGSQL = "";
my $MYLIBDIR = $PATH_LIBS."/opensips/opensipsctl";
my $DBTEXTCMD = $MYLIBDIR."/dbtextdb/dbtextdb.py";

# path to the database schemas
my $DB_SCHEMA = "";
my $DATA_DIR = $PATH_SHARE."/opensips";
my $DBTEXT_PATH = $PATH_SHARE."/opensips/dbtext/opensips";
# path to the db_berkeley database
my $DB_PATH;
if ( $DB_PATH eq "" ) {
	$DB_PATH = $PATH_ETC."/opensips/db_berkeley";
}

my $PW = "";

my $ALIAS_DB_EXISTS = "";
my $ALIAS_UL_EXISTS = "";
 
my $PASS = "";

my $result = 0;

####CLI control variables
my $term = Term::ReadLine->new("OpenSIPS");
my $OUT = $term->OUT() ||\*STDOUT;
my @command_list = ('acl'=> ('show','grant','revoke'),
		    'add'=>'add',
		    'avp'=>('list','add','rm'),
		    'passwd'=>'passwd',
		    'rm'=>'rm',
		    'alias'=>('show','rm','add'),
	            'usrloc'=>('show','rm','add'),
		    'ul'=>('show','rm','add'),
		    'alias_db'=> ('list','show','add','rm'),
		    'aliasdb'=> ('list','show','add','rm'),		   		    
		    'domain'=> ('reload','show','showdb','add','rm'),	
		    'address'=> ('show','dump','reload','add','rm'),
		    'fifo'=>'fifo',		 
		    'lcr'=> ('show','reload','addgw','rmgw','addroute','rmroute'),	
		    'cr' => ('show','reload','dump','addrt','rmrt','addcarrier','rmcarrier'),
		    'dispatcher'=>('show','addgw','rmgw','reload','dump'),
		    'monitor'=>'monitor',
		    'console'=>'monitor',
		    'moni'=>'monitor',
		    'con'=>'monitor',
		    'online'=>'online',		   		     	 
		    'ping'=>'ping',
		    'ps'=>'ps',
		    'restart' => 'restart',
		    'rpid'=> ('add','rm','show'),
		    'speeddial'=> ('list','show','add','rm'),
		    'speed_dial'=> ('list','show','add','rm'),
	            'tls'=>('rootCA','userCERT'),
		    'start' => 'start',
		    'stop'  => 'stop',
		    'unixsock'=>'unixsock',
		    'udp'=>'udp',
		    'version'=>'version',
		    'xmlrpc'=>'xmlrpc',	
		    #'db'=>('exec','roexec','run','rorun','show'),
		    'dialplan'=>('show','addrule','rm','rmpid','rmrule','reload'),
		    'db'=>('migrate','copy','backup','restore','create','presence','extra','reinit'),
		    'bdb'=>('list','ls','cat','swap','append','newappend','export','import','migrate'),
		    'db_berkeley'=>('list','ls','cat','swap','append','newappend','export','import','migrate'),
		    'dr'=>('gateway', 'rules'),
		    'gateway'=>('add','rm','list'), 
		    'rules'=>('add','rm','list'),
		    'help'=>'help'					
		    );
   
 
my $attribs = $term->Attribs;


#
##### ------------------------------------------------ #####
###paths are either initialized in the script or in the  file osipsconsolerc
sub include_osipsconsolerc() {
	open (FILE,"< $OSIPSCONSOLERC") || die "Can't Open File: osipsconsolerc\n";

	while ($line=<FILE>){		
		if ( $line !~ m/^(\s*\w+)/g ) {				
			next;
		} else {
			#print $line;
			unshift(@content,$line);
		}

	}

	close(FILE);
}
while ( $#content gt -1 ){
	my $res = shift(@content);
	my @arr = split("=",$res); 	
	chomp($arr[1]);

		#initializing global vars

		if ( !-z $PID_FILE){
			if ( $arr[0] =~ /^\s*PID_FILE/ ){				
				$PID_FILE = $arr[1];	
			}
		}

		if ( $SYSLOG eq "" ) {
			if ( $arr[0] =~ /^\s*SYSLOG/ ) {
			 	$SYSLOG = $arr[1]; 
			} 			
		}

		if ( $STARTOPTIONS eq "" ) {
			if ( $arr[0] =~ /^\s*STARTOPTIONS/ ) {
			 	$STARTOPTIONS = $arr[1]; 
			}
		}

		if ( $ALIASES_TYPE eq "" ) {
			if (  $arr[0] =~ /^\s*ALIASES_TYPE/ ) {
			 	$ALIASES_TYPE = $arr[1]; 
			}
			if ( $ALIASES_TYPE =~ /^UL/ ) {
				$ENABLE_ALIASES = 1;
			} elsif ( $ALIASES_TYPE =~ /DB/ ) {
					$ENABLE_ALIASES = 2;
			}
		}

		if ( $MI_CONNECTOR eq "" ) {
			if (   $arr[0] =~ /^\s*MI_CONNECTOR$/ ) {
				$MI_CONNECTOR = $arr[1];
				@list = split(":",$arr[1]);								
			 	$CTLENGINE = $list[0]; 
				$CTLENGINELOADED = 1;
				if ($CTLENGINE =~ /^FIFO$/) {
					$OSIPS_FIFO = $list[1];
				}
				elsif ($CTLENGINE =~ /^UNIXSOCK$/){
					$OSIPS_UNIXSOCK = $list[1];
					$OSIPSUNIX = "opensipsunix";
				}			
				if ($CTLENGINE =~ /^UDP$/){			
					$OSIPSIP = $list[1];
					$OSIPS_PORT = $list[2];
				}
				if ($CTLENGINE =~ /^XMLRPC$/){
					$OSIPSIP = $list[1];
					$OSIPS_PORT = $list[2];
				}								
			}
		}


		##### ------------------------------------------------ #####
		### ACL name verification
		if ( !$VERIFY_ACL ) {
			if ( $arr[0] =~ /^\s*VERIFY_ACL/ ) {
			 	$VERIFY_ACL = $arr[1]; 
			}

		}					

		if ( $#ACL_GROUPS lt 0 ) {
			if ( $arr[0] =~ /^\s*ACL_GROUPS/ ) {				
			 	@ACL_GROUPS = split(" ",$arr[1]); 
			}
		}

		##### ----------------------------------------------- #####
		### common variables and functions for SQL engines
		
		if ( $DBENGINE eq "" ) {
			if ( $arr[0] =~ /^\s*DBENGINE/ ) {
				if ( $arr[1] =~ /^\s*MYSQL/ ) {
					$DBENGINE = "mysql";
				} elsif  ( $arr[1] =~ /^\s*PGSQL/ ) {
					$DBENGINE = "Pg";
				} else {	
				 	$DBENGINE = $arr[1]; 					
				}
			}			
		}

		if ( $DBNAME eq "" ) {
			if ( $arr[0] =~ /^\s*DBNAME/ ) {
				$DBNAME = $arr[1];
			}
		}


		if ( $DBHOST eq "" ) {
			if ( $arr[0] =~ /^\s*DBHOST/ ) {
				$DBHOST = $arr[1];	
			}
		}

		if ( $DBPORT eq "" ) {
			if ( $arr[0] =~ /^\s*DBPORT/ ) {
				$DBPORT = $arr[1];	
			}
		}

		if ( $DBRWUSER eq "" ) {
			if ( $arr[0] =~ /^\s*DBRWUSER/ ) {
				$DBRWUSER = $arr[1]
			}
		}

			# the read-only user for whom password may be stored here
		if ( $DBROUSER eq "" ) {
			if ( $arr[0] =~ /^\s*DBROUSER/ ) {
				$DBROUSER = $arr[1];
			}
		}

		if ( $DBROPW eq "" ) {
			if ( $arr[0] =~ /^\s*DBROPW/ ) {
				$DBROPW = $arr[1];
			}			
		}

		# full privileges SQL user
		if ( $DBROOTUSER eq "" ) {
			if ( $arr[0] =~ /^\s*DBROOTUSER/ ) {
				$DBROOTUSER = $arr[1];
			}			
		}

		if ( $DBRWPW eq "" ) {
			if ( $arr[0] =~ /^\s*DBRWPW/ ) {
				$DBRWPW = $arr[1];
			}			
		}
		
		if ( $SIP_DOMAIN eq "" ) {
			if ( $arr[0] =~ /^\s*SIP_DOMAIN/ ) {
			 	$SIP_DOMAIN = $arr[1]; 
			}			
		}

		if ( ! $STORE_PLAINTEXT_PW ) {
			if ( $arr[0] =~ /^\s*STORE_PLAINTEXT_PW/ ) {
			 	$STORE_PLAINTEXT_PW = $arr[1]; 
			}		
		}

		if ( $AWK eq "" ) {
			if ( $arr[0] =~ /^\s*AWK/ ) {
			 	$AWK = $arr[1]; 
			}		
		}

		if ( $EGREP eq "") {
			if ( $arr[0] =~ /^\s*GREP/ ) {
			 	$EGREP = $arr[1]; 
			}		
		}

		if ( $SED eq "" ) {
			if ( $arr[0] =~ /^\s*SED/ ) {
			 	$SED = $arr[1]; 
			}		
		}

} 	
	

if ( !-z $PID_FILE){
	$PID_FILE = "/var/run/opensips.pid";
}

if ( $SYSLOG eq "" ) {
	$SYSLOG = 0; # 0=output to console, 1=output to syslog
}

if ( $STARTOPTIONS eq "" ) {
	$STARTOPTIONS = ""; # for example -dddd
}

##### ------------------------------------------------ #####
### aliases configuration
#
if ( $ALIASES_TYPE =~ /^UL/ ) {
	$ENABLE_ALIASES = 1;
} elsif ( $ALIASES_TYPE =~ /DB/ ) {
	$ENABLE_ALIASES = 2;
}

#
##### ------------------------------------------------ #####
### CTLENGINE
#		

if ( $MI_CONNECTOR eq "" ) {				
	$CTLENGINE = "FIFO";
	$OSIPS_FIFO = "/tmp/opensips_fifo";
	$CTLENGINELOADED = 1;	
}

if ( !-z $OSIPSUNIX ) {
	$OSIPSUNIX = "opensipsunix";
}

##### ------------------------------------------------ #####
### ACL name verification
if ( ! $VERIFY_ACL ) {
	$VERIFY_ACL = 1;
}

if ( $#ACL_GROUPS lt 0 ) {
	@ACL_GROUPS=("local", "ld", "int", "voicemail", "free-pstn");
}

##### ----------------------------------------------- #####
### common variables and functions for SQL engines
if ( $DBENGINE eq "" ) {
	$DBENGINE = "mysql";
}

if ( $DBNAME eq "" ) {
	$DBNAME = "opensips";
}
	
if ( $DBHOST eq "" ) {
	$DBHOST = "localhost";
}

if ( $DBRWUSER  eq "" ) {
	$DBRWUSER = "opensips";
}

if ( $DBRWPW  eq ""  ) {
	$DBRWPW = 'opensipsrw';
}

# the read-only user for whom password may be stored here
if ( $DBROUSER  eq ""  ) {
	$DBROUSER = 'opensipsro';
}

if ( $DBROPW  eq "" ) {
	$DBROPW = 'opensipsro';
}

# full privileges SQL user
if ( $DBROOTUSER  eq ""  ) {
	$DBROOTUSER = "root";
}


### force values for variables in this section
# you better set the variables in ~/.osipsconsolerc

if ( !-z $ETCDIR ) {
	$ETCDIR=$PATH_ETC."/opensips";
}

if ( $EGREP eq "" ) {
	&locate_tool("egrep");		
	if ( !-e $TOOLPATH ){
		# now error, but we can look for alternative names if it is the case
		print "error: 'egrep' tool not found: set \$EGREP variable to correct tool path\n";		
	} else {
		$EGREP = $TOOLPATH;
	}
}
		
if ($AWK eq "") {
	&locate_tool('awk');
	if ( !-e $TOOLPATH ) {
		# now error, but we can look for alternative names if it is the case
		print "error: 'awk' tool not found: set \$AWK variable to correct tool path\n";				
	} else {
		$AWK = $TOOLPATH;
	}
}

if ( $MD5 eq "") {
	&locate_tool ('md5sum md5');
	if ( !-e $TOOLPATH ) {
		# now error, but we can look for alternative names if it is the case
		print "error: 'md5sum' or 'md5' tool not found: set MD5 variable to correct tool path\n";
	} else {			
		$MD5 = $TOOLPATH;
	}
}

if ( $EXPR eq "" ) {
	&locate_tool('expr');
	if ( !-e $TOOLPATH ) {
		# now error, but we can look for alternative names if it is the case
		print "error: 'expr' tool not found: set EXPR variable to correct tool path\n"
	} else {
		$EXPR= $TOOLPATH;
	}
}

#if ( $LAST_LINE eq " "  ) {
#	&locate_tool('tail');
#	if ( !-e $TOOLPATH ){
#		# now error, but we can look for alternative names if it is the case
#		print "error: 'TAIL' tool not found: set TAIL variable to correct tool path\n"
#	} else {
#		$LAST_LINE = `$TOOLPATH -n 1`;
#	}
#}


##### ----------------------------------------------- #####
### binaries
{
	if ( ($DBENGINE eq "mysql") &&  ( $MYSQL eq "" ) ) {
		&locate_tool('mysql');
		if ( !-e $TOOLPATH ) {
			print "Error: 'mysql' tool not found: set MYSQL variable to correct tool path";
		}
		$MYSQL = $TOOLPATH;
	}
}


##### ----------------------------------------------- #####
### binaries
if ( ($DBENGINE eq "Pg") && ($PGSQL eq "" ) ) {
	&locate_tool('psql');
	if ( !-e $TOOLPATH ) {
		print "Error: 'psql' tool not found: set PGSQL variable to correct tool path\n";
		return;
	}
	$PGSQL = $TOOLPATH;
}


#berkeley db utility program that writes out db to plain text
#small hack to autodetect the db dump command, debian prefix the version..

system("which db_dump > /dev/null");
if ( $? == 0 ) {
	$DUMP_CMD = "db_dump";					
}

system("which db4.4_dump > /dev/null");
if ( $? == 0 ) {
	$DUMP_CMD = "db4.4_dump";
}

system("which db4.5_dump > /dev/null");
if ( $? == 0 ) {
	$DUMP_CMD = "db4.5_dump";
}

system("which db4.6_dump > /dev/null");
if ( $? == 0 ) {
	$DUMP_CMD = "db4.6_dump";
}

#berkeley db utility program that imports data from plain text file
#small hack to autodetect the db load command, debian prefix the version..

system("which db_load > /dev/null");
if ( $? == 0 ) {
	$LOAD_CMD = "db_load";
}

system("which db4.4_load > /dev/null");
if ( $? == 0 ) {
	$LOAD_CMD = "db4.4_load";
}

system("which db4.5_load > /dev/null");
if ( $? == 0 ) {
	$LOAD_CMD = "db4.5_load";
}

system("which db4.6_load > /dev/null");
if ( $? == 0 ) {
	$LOAD_CMD = "db4.6_load";
}

# period in which stats are reprinted
if ( -z $WATCH_PERIOD ) {
	$WATCH_PERIOD = 2;
}

##### ----------------------------------------------- #####
#### database tables for SQL databases 

# UsrLoc Table
my $UL_TABLE = " ";
if ( !-z $UL_TABLE ) {
	$UL_TABLE="location";
}
my %ul_table = ('USER_COLUMN' => 'username',
	     'DOMAIN_COLUMN' => 'domain',
             'CALLID_COLUMN'=> 'callid'
	      );


# subscriber table
my $SUB_TABLE = " ";
if ( !-z $SUB_TABLE ) {
	$SUB_TABLE='subscriber';
}
my %sub_table = ('REALM_COLUMN' => 'domain',
		 'HA1_COLUMN' => 'ha1',
		 'HA1B_COLUMN'=> 'ha1b',
		 'PASSWORD_COLUMN' => 'password',
		 'RPID_COLUMN' => 'rpid',
		 'SUBSCRIBER_COLUMN' => 'username',
		 'PHP_LIB_COLUMN' => 'phplib_id',
		 'EMAIL_ADDRESS' => 'email_address'
		  );


# acl table
my $ACL_TABLE = " ";
if ( !-z $ACL_TABLE ) {
	$ACL_TABLE = 'grp';
}
my %acl_table = ( 'ACL_USER_COLUMN' => 'username',
		  'ACL_DOMAIN_COLUMN' => 'domain',
		  'ACL_GROUP_COLUMN' => 'grp',
		  'ACL_MODIFIED_COLUMN' => 'last_modified',
		);



# aliases table
my $ALS_TABLE = " ";
if ( !-z $ALS_TABLE ) {
	$ALS_TABLE = 'aliases';
}
my %als_table = ('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
my $DOMAIN_TABLE = " ";
if ( !-z $DOMAIN_TABLE ) {
	$DOMAIN_TABLE = 'domain';
}
my %domain_table = ('DO_DOMAIN_COLUMN' => 'domain',
		    'DO_LAST_MODIFIED_COLUMN' => 'last_modified',
		    );

# lcr tables
my $LCR_TABLE = " ";
if ( !-z $LCR_TABLE ) { 
	$LCR_TABLE = 'lcr';
}

my %lcr_table = ('LCR_PREFIX_COLUMN' => 'prefix',
		 'LCR_FROMURI_COLUMN' => 'from_uri',
		 'LCR_GRPID_COLUMN' => 'grp_id',
		 'LCR_PRIO_COLUMN' => 'priority'
		);

# gw table
my $GW_TABLE = " ";
if ( !-z $GW_TABLE ) {
	$GW_TABLE = 'gw';
}
my %gw_table = ('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_TAG_COLUMN' => 'tag',
		'LCR_GW_FLAGS_COLUMN' => 'flags'
		);

# route_tree table
my $ROUTE_TREE_TABLE = " ";
if ( !-z $ROUTE_TREE_TABLE ) {
	$ROUTE_TREE_TABLE = 'route_tree';
}
my %route_tree_table = ('CARRIERROUTE_ROUTE_TREE_PREFIX_COLUMN' =>'id',
			'CARRIERROUTE_ROUTE_TREE_CARRIER_COLUMN' => 'carrier'
			);


# carrierroute table
my $CARRIERROUTE_TABLE = " ";
if ( !-z $CARRIERROUTE_TABLE ) {
	$CARRIERROUTE_TABLE = 'carrierroute';
}

my %carrierroute_table = ('CARRIERROUTE_CARRIERROUTE_PREFIX_COLUMN' => 'id',
 			  'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN' => 'carrier',
			  'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN' => 'scan_prefix',
			  'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN' => 'domain',
			  'CARRIERROUTE_CARRIERROUTE_PROB_COLUMN' => 'prob',
			  'CARRIERROUTE_CARRIERROUTE_STRIP_COLUMN' => 'strip',
			  'CARRIERROUTE_CARRIERROUTE_REWRITE_HOST_COLUMN' => 'rewrite_host',
			  'CARRIERROUTE_CARRIERROUTE_REWRITE_PREFIX_COLUMN' => 'rewrite_prefix',
			  'CARRIERROUTE_CARRIERROUTE_REWRITE_SUFFIX_COLUMN' => 'rewrite_suffix',
			  'CARRIERROUTE_CARRIERROUTE_COMMENT_COLUMN' => 'description',
			  'CARRIERROUTE_CARRIERROUTE_FLAGS_COLUMN' => 'flags',
			  'CARRIERROUTE_CARRIERROUTE_MASK_COLUMN' => 'mask'
			);

# URI table
my $URI_TABLE = " ";
if ( !-z $URI_TABLE ) {
	$URI_TABLE = 'uri'
}
my %uri_table = ('URIUSER_COLUMN' => 'uri_user',
		 'MODIFIED_COLUMN' => 'last_modified'
		);

# dbaliases table
my $DA_TABLE = " ";
if ( !-z $DA_TABLE ) {
	$DA_TABLE = 'dbaliases';
}
my %da_table = ('DA_USER_COLUMN' => 'username',
		'DA_DOMAIN_COLUMN' => 'domain',
		'DA_ALIAS_USER_COLUMN' => 'alias_username',
		'DA_ALIAS_DOMAIN_COLUMN' => 'alias_domain'
		);

# speeddial table
my $SD_TABLE = " ";
if ( !-z $SD_TABLE ) {
	$SD_TABLE = 'speed_dial'
}
my %sd_table = ('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
my $AVP_TABLE = " ";
if ( !-z $AVP_TABLE ) {
	$AVP_TABLE = 'usr_preferences';
}
my %avp_table = ('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'
		);


# address table
my $ADDRESS_TABLE = " ";
if ( !-z $ADDRESS_TABLE ) {
	$ADDRESS_TABLE = 'address';
}
my %address_table = ('ADDRESS_ID_COLUMN' => 'ip',
		     'ADDRESS_GRP_COLUMN' => 'grp',
		     'ADDRESS_IP_COLUMN' => 'ip',
		     'ADDRESS_MASK_COLUMN' => 'mask',
		     'ADDRESS_PORT_COLUMN' => 'port',
		     'ADDRESS_PROTO_COLUMN' => 'proto',
		     'ADDRESS_PATTERN_COLUMN' => 'pattern',
		     'ADDRESS_CONTEXT_INFO_COLUMN' => 'context_info'
		    );


# dispatcher tables  
my $DISPATCHER_TABLE = " ";
if ( !-z $DISPATCHER_TABLE ){
	$DISPATCHER_TABLE = 'dispatcher';
}
my %dispatcher_table = ('DISPATCHER_ID_COLUMN' => 'id',
			'DISPATCHER_SETID_COLUMN' => 'setid',
			'DISPATCHER_DESTINATION_COLUMN' => 'destination',
			'DISPATCHER_FLAGS_COLUMN' => 'flags',
			'DISPATCHER_DESCRIPTION_COLUMN' => 'description'
			);



# dialplan tables
my $DIALPLAN_TABLE = " ";
if ( !-z $DIALPLAN_TABLE ) {
	$DIALPLAN_TABLE = 'dialplan';
}
my %dialplan_table = ('DIALPLAN_ID_COLUMN' => 'id',
		'DIALPLAN_DPID_COLUMN' => 'dpid',
		'DIALPLAN_PR_COLUMN' => 'pr',
		'DIALPLAN_MATCH_OP_COLUMN' => 'match_op',
		'DIALPLAN_MATCH_EXP_COLUMN' => 'match_exp',
		'DIALPLAN_MATCH_LEN_COLUMN' => 'match_len',
		'DIALPLAN_SUBST_EXP_COLUMN' => 'subst_exp',
		'DIALPLAN_REPL_EXP_COLUMN' => 'repl_exp',
		'DIALPLAN_ATTRS_COLUMN' => 'attrs'
		);

#drouting tables
my $DR_GW_TABLE = " ";
if ( !-z $DR_GW_TABLE ) {
	$DR_GW_TABLE = 'dr_gateways';
}
my %dr_gw_table = ('DR_GW_GWID_COLUMN' => 'gwid',
		   'DR_GW_ADDRESS_COLUMN' => 'address',	
		   'DR_GW_TYPE_COLUMN' => 'type',
		   'DR_GW_STRIP_COLUMN' => 'strip',
		   'DR_GW_PRI_PREFIX_COLUMN' => 'pri_prefix',
		   'DR_GW_DESCRIPTION_COLUMN' => 'description'
		);	


# dr_rules table
my $DR_RULES_TABLE = " ";
if ( !-z $DR_RULES_TABLE ) {
	$DR_RULES_TABLE = 'dr_rules';
}
my %dr_rules_table = (  'DR_RULES_RULEID_COLUMN' => 'ruleid',
			'DR_RULES_GROUPID_COLUMN' => 'groupid',
			'DR_RULES_PREFIX_COLUMN' => 'prefix', 
			'DR_RULES_TIMEREC_COLUMN' => 'timerec',
			'DR_RULES_PRIORITY_COLUMN' => 'priority',
			'DR_RULES_ROUTEID_COLUMN' => 'routeid',
			'DR_RULES_GWLIST_COLUMN' => 'gwlist',
			'DR_RULES_DESCRIPTION_COLUMN' => 'description'
			);

##### ----------------------------------------------- #####
### path to useful tools

sub locate_tool() {
        while ( 1 ){
               if ( -x "/usr/bin/which" ) {
                        $TOOLPATH = `which @_`;
			chomp($TOOLPATH);
                        #if ( $TOOLPATH ) {
                        #       return $TOOLPATH;
                        #}
			return;
                }

                # look in common locations
                if ( -x "/usr/bin/".@_ ){
                        $TOOLPATH = "/usr/bin/".@_;
                        return;
                }

                if ( -x "/bin/".$_[0] ) {
                        $TOOLPATH = "/bin/".$_[0];
                        return;
                }
                if ( -x "/usr/local/bin/".$_[0] ) {
                        $TOOLPATH = "/usr/local/bin/$_[0]";
                        return;
                }
	      	last;
	}
	return;
}



#
##### ------------------------------------------------ #####
### usage functions
#


#online
sub usage_online() {
	print " -- command 'online' - dump online users from memory\n" .
	      "online ............................. display online users\n";
}


#monitor
sub usage_opensips_monitor() {
	print " -- command 'monitor' - show internal status\n" .
	      "monitor ............................ show server's internal status\n";
}


#ping
sub usage_ping() {
	print " -- command 'ping' - ping a SIP URI (OPTIONS)\n" .
	      "ping <uri> ......................... ping <uri> with SIP OPTIONS\n";
}


#usrloc
sub usage_usrloc() {
	print " -- command 'ul|alias' - manage user location or aliases\n" .
	      "ul show [<username>]................ show in-RAM online users\n" .
	      "ul show --brief..................... show in-RAM online users in short format\n" .
	      "ul rm <username> [<contact URI>].... delete user's usrloc entries\n" .
 	      "ul add <username> <uri> ............ introduce a permanent usrloc entry\n" .
	      "ul add <username> <uri> <expires> .. introduce a temporary usrloc entry\n" ;
}


#base - start|stop|restart
sub usage_base() {
	print " -- command 'start|stop|restart'\n" .
	      "restart ............................ restart OpenSIPS\n" .
	      "start .............................. start OpenSIPS\n" .
              "stop ............................... stop OpenSIPS\n";
}


#tls
sub usage_tls() {
	print " -- command 'tls'\n" .
 	      "tls rootCA [<etcdir>] .......... creates new rootCA\n" .
              "tls userCERT <user> [<etcdir>] ... creates user certificate\n" .
              "\t\t\t\tdefault <etcdir> is $ETCDIR/tls\n";
}


#acl
sub usage_acl() {
	print " -- command 'acl' - manage access control lists (acl)\n" .
	      "acl show [<username>] .............. show user membership\n" .
              "acl grant <username> <group> ....... grant user membership (*)\n" .
              "acl revoke <username> [<group>] .... revoke user membership(s) (*)\n";
}


#lcr
sub usage_lcr() {
	print " -- command 'lcr' - manage least cost routes (lcr)\n" .
	      "* IP addresses must be entered in dotted quad format e.g. 1.2.3.4 *\n" .
	      "* <uri_scheme> and <transport> must be entered in integer or text,*\n" .
              "* e.g. transport '2' is identical to transport 'tcp'.             *\n" .
   	      "*   scheme: 1=sip, 2=sips;   transport: 1=udp, 2=tcp, 3=tls       *\n" .
   	      "* Examples:  lcr addgw level3 1.2.3.4 5080 sip tcp 1              *\n" .
   	      "*            lcr addroute +1 '' 1 1                               *\n" .
	      "lcr show .....................................................................\n" .
           		"............. show routes, gateways and groups\n" .
	      "lcr reload ...................................................................\n" .
	           "............. reload lcr gateways\n" .
  	      "lcr addgw <gw_name> <ip> <port> <scheme> <transport> <grp_id> <flags> <tag> <strip>\n" .
              "............... add a gateway with flags, tag and strip ............\n" .
              "................(flags, tag, and strip are optional arguments) .....\n" .
              "lcr rmgw  <gw_name> ..........................................................\n" .
              "............... delete a gateway\n" .
              "lcr addroute <prefix> <from> <grp_id> <prio> .................................\n" .
              ".............. add a route ( use '' to match anything in <from> )\n" .
              "lcr rmroute  <prefix> <from> <grp_id> <prio> .................................\n" .
              ".............. delete a route\n";
}


#cr
sub usage_cr() {
	print " -- command 'cr' - manage carrierroute tables\n" . 
              "cr show ....................................................... show tables\n" .
              "cr reload ..................................................... reload tables\n" .
  	      "cr dump ....................................................... show in memory tables\n" .
 	      "cr addrt <routing_tree_id> <routing_tree> ..................... add a tree\n" .
 	      "cr rmrt  <routing_tree> ....................................... rm a tree\n" .
 	      "cr addcarrier <carrier> <scan_prefix> <domain> <rewrite_host> ................\n" .
                             "\t\t[<prob>] [<strip>] [<rewrite_prefix>] [<rewrite_suffix>] ...............\n" .
                             "\t\t[<flags>] [<mask>] [<comment>] .........................add a carrier\n" . 
               		     "\t\t(prob, strip, rewrite_prefix, rewrite_suffix,...................\n" .
                	     "\t\tflags, mask and comment are optional arguments) ...............\n" .
              "cr rmcarrier  <carrier> <scan_prefix> <domain> ................ rm a carrier\n";
}


#rpid
sub usage_rpid() {
	print " -- command 'rpid' - manage Remote-Party-ID (RPID)\n" .
	      "rpid add <username> <rpid> ......... add rpid for a user (*)\n" .
              "rpid rm <username> ................. set rpid to NULL for a user (*)\n" . 
              "rpid show <username> ............... show rpid of a user\n";
}


#subscriber - add|passwd|rm
sub usage_subscriber() {
	print " -- command 'add|passwd|rm' - manage subscribers\n" .
	      "add <username> <password> .......... add a new subscriber (*)\n" .
	      "passwd <username> <passwd> ......... change user's password (*)\n" .
	      "rm <username> ...................... delete a user (*)\n";
}


#address
sub usage_address() {
	print " -- command 'add|dump|reload|rm|show' - manage address\n" .
	      "address show ...................... show db content\n" .
	      "address dump ...................... show cache content\n" .
	      "address reload .................... reload db table into cache\n" .
	      "address add <grp> <ip> <mask> <port> <proto> [<context_info>] [<pattern>]\n" .
		           "\t\t\t....................... add a new entry\n" .
		           "\t\t\t....................... (from_pattern and tag are optional arguments)\n" .
	      "address rm <grp> <ip> <mask> <port>............... remove all entries for the given grp ip mask and port\n";
}


#dispatcher
sub usage_dispatcher() {
	print " -- command 'dispatcher' - manage dispatcher\n" .
   	      "* Examples:  dispatcher addgw 1 sip:1.2.3.1:5050 1 'outbound gateway'\n" . 
              "*            dispatcher addgw 2 sip:1.2.3.4:5050 3 ''\n" .
   	      "*            dispatcher rmgw 4\n" .
 	      "dispatcher show ..................... show dispatcher gateways\n" .
 	      "dispatcher reload ................... reload dispatcher gateways\n" .
              "dispatcher dump ..................... show in memory dispatcher gateways\n" . 
              "dispatcher addgw <setid> <destination> <flags> <description>\n" .
                                "\t\t\t.......................... add gateway\n" . 
              "dispatcher rmgw <id> ................ delete gateway\n";
}


# dbtext don't support db_ops
sub usage_db_ops() {
	print " -- command 'db' - database operations\n" .
              "db exec <query> ..................... execute SQL query\n" .
              "db roexec <roquery> ................. execute read-only SQL query\n" .
              "db run <id> ......................... execute SQL query from \$id variable\n" .
              "db rorun <id> ....................... execute read-only SQL query from\n" . 
                                                     "\t\t\t\t\$id variable\n" .
              "db show <table> ..................... display table content\n";

}


# speeddial 
sub usage_speeddial() {
	print " -- command 'speeddial' - manage speed dials (short numbers)\n" .
	      "speeddial show <speeddial-id> ....... show speeddial details\n" .
	      "speeddial list <sip-id> ............. list speeddial for uri\n" .
	      "speeddial add <sip-id> <sd-id> <new-uri> [<desc>] ... \n" .
		 "\t\t........................... add a speedial (*)\n" .
              "speeddial rm <sip-id> <sd-id> ....... remove a speeddial (*)\n" .
	      "speeddial help ...................... help message\n" .
	               "\t\t- <speeddial-id>, <sd-id> must be an AoR (username\@domain)\n" .
	               "\t\t- <sip-id> must be an AoR (username\@domain)\n" .
	               "\t\t- <new-uri> must be a SIP AoR (sip:username\@domain)\n" .
	   	       "\t\t- <desc> a description for speeddial\n";
}


# avp 
sub usage_avp() {
	print " -- command 'avp' - manage AVPs\n" .
		 "avp list [-T table] [-u <sip-id|uuid>]\n" .
		    "\t[-a attribute] [-v value] [-t type] ... list AVPs\n" .
		 "avp add [-T table] <sip-id|uuid>\n" .
		     "\t<attribute> <type> <value> ............ add AVP (*)\n" .
		 "avp rm [-T table]  [-u <sip-id|uuid>]\n" .
		     "\t[-a attribute] [-v value] [-t type] ... remove AVP (*)\n" .
		 "avp help .................................. help message\n" .
		    "\t- -T - table name\n" .
		    "\t- -u - SIP id or unique id\n" .
		    "\t- -a - AVP name\n" .
		    "\t- -v - AVP value\n" .
		    "\t- -t - AVP name and type (0 (str:str), 1 (str:int),\n" .
				              "\t\t\t\t2 (int:str), 3 (int:int))\n" .
		    "\t\t- <sip-id> must be an AoR (username\@domain)\n" .
		    "\t\t- <uuid> must be a string but not AoR\n";

}


# alias_db 
sub usage_alias_db() {
	print " -- command 'alias_db' - manage database aliases\n" .
		"alias_db show <alias> .............. show alias details\n" .
		"alias_db list <sip-id> ............. list aliases for uri\n" .
		"alias_db add <alias> <sip-id> ...... add an alias (*)\n" .
		"alias_db rm <alias> ................ remove an alias (*)\n" .
		"alias_db help ...................... help message\n" .
		    "\t\t- <alias> must be an AoR (username\@domain)\n" .
		    "\t\t- <sip-id> must be an AoR (username\@domain)\n";
}

sub usage_avp() {
        print " -- command 'avp' - manage AVPs\n" .
                 "avp list [-T table] [-u <sip-id|uuid>]\n" .
                    "\t[-a attribute] [-v value] [-t type] ... list AVPs\n" .
                 "avp add [-T table] <sip-id|uuid>\n" .
                     "\t<attribute> <type> <value> ............ add AVP (*)\n" .
                 "avp rm [-T table]  [-u <sip-id|uuid>]\n" .
                     "\t[-a attribute] [-v value] [-t type] ... remove AVP (*)\n" .
                 "avp help .................................. help message\n" .
                    "\t- -T - table name\n" .
                    "\t- -u - SIP id or unique id\n" .
                    "\t- -a - AVP name\n" .
                    "\t- -v - AVP value\n" .
                    "\t- -t - AVP name and type (0 (str:str), 1 (str:int),\n" .
                                              "\t\t\t\t2 (int:str), 3 (int:int))\n" .
                    "\t\t- <sip-id> must be an AoR (username\@domain)\n" .
                    "\t\t- <uuid> must be a string but not AoR\n";

}



#domain
sub usage_domain() {
	print " -- command 'domain' - manage local domains\n" .
 	      "domain reload ....................... reload domains from disk\n" .
              "domain show ......................... show current domains in memory\n" .
              "domain showdb ....................... show domains in the database\n" .
              "domain add <domain> ................. add the domain to the database\n" .
              "domain rm <domain> .................. delete the domain from the database\n";

}



# fifo
sub usage_fifo() {
	print " -- command 'fifo'\n" . 
 	      "fifo ............................... send raw FIFO command\n";
}


sub usage_dialplan() {
	print " -- command 'dialplan' - manage dialplans\n" . 
	      "dialplan show <dpid> .............. show dialplan tables\n" .
	      "dialplan reload ................... reload dialplan tables\n" .
	      "dialplan addrule <dpid> <prio> <match_op> <match_exp>\n" .
 	      "\t\t\t<match_len> <subst_exp> <repl_exp> <attrs>\n" .
	      "\t\t\t\t\t.................... add a rule\n" .
              "dialplan rm ....................... removes the entire dialplan table\n" .
	      "dialplan rmdpid <dpid> ............ removes all the gived dpid entries\n" .
 	      "dialplan rmrule <dpid> <prio> ..... removes all the gived dpid/prio entries\n";
}

sub usage_unixsock() {
	print " -- command 'unixsock'\n" .
   	      "unixsock ........................... send raw unixsock command\n";
}


# common functions
sub usage_db() {
my $COMMAND=`basename $0`;

	print " -- command 'db' - manage OpenSIPS databases\n".
	      "db create <db name or db_path, optional> ....(creates a new database)\n".
	      "db presence .................................(adds the presence related tables)\n".
	      "db extra ....................................(adds the extra tables)\n".
	      "db migrate <old_db> <new_db> ................(migrates DB from 1.5 to 1.6)\n".
	      "db drop <db name or db_path, optional> ......(!entirely deletes tables!)\n".
	      "db reinit <db name or db_path, optional> ....(!entirely deletes and than re-creates tables!)\n".
	      "db backup <file> ............................(dumps current database to file)\n".
	      "db restore <file> ...........................(restores tables from a file)\n".
	      "db copy <new_db> ............................(creates a new db from an existing one)\n\n".
      "\tif you want to manipulate database as other database user than\n" .
      "\troot, want to change database name from default value \"$DBNAME\",\n" .
      "\tor want to use other values for users and password, edit the\n" .
      "\t\"config vars\" section of the command $COMMAND.\n";
} #usage


sub berkeley_usage() {

print "Script for maintaining OpenSIPS Berkeley DB tables\n".
       "bdb | db_berkeley list      (lists the underlying db files in DB_PATH)\n".
       "bdb | db_berkeley cat       <db>  (db_dump the underlying db file to STDOUT)\n".
       "bdb | db_berkeley swap      <db>  (installs db.new by db -> db.old; db.new -> db)\n".
       "bdb | db_berkeley append    <db> <datafile> (appends data to an existing db;output DB_PATH/db.new)\n".
       "bdb | db_berkeley newappend <db> <datafile> (appends data to a new instance of db; output DB_PATH/db.new)\n".
       "bdb | db_berkeley export <dump_dir> (exports table data to plain-txt files in dump_dir)\n".
       "bdb | db_berkeley import <dump_dir> (imports plain-txt table data and creates new db tables in db_path)\n";
} #usage


# droute
sub usage_dr() {
	print " -- command 'droute'\n" . 
 	      "dr gateway add <address=address_param> [<type=type_param>] [<strip=strip_param>] \n". 
			" [<pri_prefix=pri_prefix_param>] [<description=description_param>].....\n" .
 	      		    "\t\t\t\t\t\t\t..........................adds new route\n" .
 	      "dr gateway rm <gwid=gwid_param>................................removes route\n" . 
 	      "dr gateway list <type=type_param>..............................lists route(s)\n" .
 	      "dr gateway list <address=address_param>...........................lists route(s)\n" .
 	      "dr gateway list ....................................lists all routes\n" .
	      "dr gateway h........................................droute help\n" .
 	      "dr rules add <gwlist=gwlist_param> [<groupid=groupid_param>][<prefix=prefix_param>] \n".
		"[<timerec=timerec_param>][<priority=priority_param>][<routeid=routeid_param>][<description=description_param>].....\n" .
		            "\t\t\t\t\t\t\t\t.............adds new rule(s)\n" .
 	      "dr rules rm <ruleid=ruleid_param> .................................removes rules\n" .
 	      "dr rules list...............................lists rules(s)\n" .
 	      "dr rules list <gwlist=gwlist_param>...............................lists rules(s)\n" .
 	      "dr rules list <groupid=groupid_param>...............................lists rules(s)\n" .
	      "dr rules h..................................dr rules help\n" .
	      "dr h........................................dr help\n";
}

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

sub get_my_host() {
	
	if ( $SIP_DOMAIN eq "") {
		$SIP_DOMAIN = `uname -n`;		
		return $SIP_DOMAIN;
	} else {
		return $SIP_DOMAIN;
	}
}

# calculate name and domain of current user
sub set_user() {

	@OSIPS = split ("@",$_[0]);
	$OSIPSUSER = $OSIPS[0];
	$OSIPSDOMAIN = $OSIPS[1];
	#print "user:".$OSIPSUSER." domain:".$OSIPSDOMAIN."\n";

	if ( ! $OSIPSDOMAIN ) {
		$OSIPSDOMAIN = $SIP_DOMAIN;
		return;
	}

	if ( ! $OSIPSDOMAIN ) {
		print "domain unknown: use usernames with domain or set default domain in SIP_DOMAIN\n";	
		return;
	}
	return;
}


# check the parameter if it is a valid address of record (user@domain)
sub check_aor() {

	if ( $_[0] !~ /^$USERNAME_RE\@.*\..*/ ) {
		print "error: invalid AoR: " . $_[0] . "> /dev/stderr";
		$result = 1;
	} else {
		 $result = 0;
	}

}


# check the parameter if it is a valid address of record (user@domain)
sub is_aor() {

	if ( $_[0] !~ /^$USERNAME_RE\@.*\..*/ ) {
		$result = 1;
	} else {
		 $result = 0;
	}
}


# check the parameter if it is a valid SIP address of record (sip:user@domain)
sub check_sipaor() {

	if ( $_[0] !~ /sips?:$USERNAME_RE\@.*\..*/ ) {
		print "error: invalid SIP AoR: ". $_[0] . " > /dev/stderr";
		$result = 1;
	} else {
		$result = 0;
	}

}


# check the parameter if it is a valid SIP URI
# quite simplified now -- it captures just very basic
# errors
sub check_uri() {

	if ( $_[0] !~ /sips?:($USERNAME_RE\@)?.*\..*/) { 
		print "error: invalid SIP URI: " . $_[0] . " > /dev/stderr";
		$result = 1;
	} else {
		$result = 0;
	}

}


#sub print_status() {

#	if ( $_[0] !~ /^[1-6][0-9][0-9]/ ) { 
#		print $_[0];
#	} else {
#		print "200 OK\n";
#	}

#}


# params: user, realm, password
# # output: HA1
sub gen_ha1(){
	$HA1=`echo -n \"$_[0]:$_[1]:$_[2]\" | $MD5 | $AWK '{ print \$1 }'`;
	if ( $? != 0 ) {
		print "HA1 calculation failed!";
		return;
	}
	return $HA1;
}

# params: user, realm, password
# output: HA1B
sub gen_ha1b() {
	$HA1B=`echo -n \"$_[0]@$_[1]:$_[1]:$_[2]\" | $MD5 | $AWK '{ print \$1 }'`;
	if ( $? != 0 ) {
		print "HA1B calculation failed!";
		return;
	}
	return $HA1B;
}


# params: user, realm, password
# output: PHPLIB_ID
sub gen_phplib_id()
{
	my $NOW=`date`;
	my $PHPLIB_ID=`echo -n \"$_[0]$_[1]:$_[2]:$NOW\" | $MD5 | $AWK '{ print \$1 }'`;
}



# params: user, password
# output: HA1, HA1B
sub credentials()
{
	system(&set_user(@_));
	system(&gen_ha1 ( $OSIPSUSER, $OSIPSDOMAIN, $cmd[2] ));
	system(&gen_ha1b ( $OSIPSUSER, $OSIPSDOMAIN, $cmd[2] ));
	return;
}



# params: user
# output: false if exists, true otherwise
sub is_user() {

	system(&set_user($_[0]));
	print $OSIPSUSER ." " .$OSIPSDOMAIN."\n";

	if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
		my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
		my $res = &bdb_select_where($SUB_TABLE,$key);
		$result = $res;
	} elsif ( $DBENGINE =~ /^DBTEXT$/) {
		my $res = `\"$DBTEXTCMD\" \"SELECT COUNT(*) FROM $SUB_TABLE WHERE $sub_table{'SUBSCRIBER_COLUMN'} = \"$OSIPSUSER\" AND $sub_table{'REALM_COLUMN'} = \"$OSIPSDOMAIN\"\" 2>&1`;
		$result = $res;
	} else {
		#prepare the query
		$sth = $dbh->prepare( "SELECT count(*) 
				       FROM $SUB_TABLE 
				       WHERE $sub_table{'SUBSCRIBER_COLUMN'} = \'$OSIPSUSER\' 
				       AND $sub_table{'REALM_COLUMN'} = \'$OSIPSDOMAIN\' " );
		
		#execute the query
		$sth->execute( );
		warn "Entry could not be retrieved from table", $sth->errstr( ), "\n" if $sth->err( );

		## Retrieve the results of a row of data and print
		print "\tQuery results:\n================================================\n";

		while ( @row = $sth->fetchrow_array( ) )  {
			 print "@row\n";
			 $result = "@row";

		}
	
		$sth->finish();	
	}
	
	#print $result;
	return $result;
}


#
##### ------------------------------------------------ #####
### helper functions (require db and/or ctl)
#

#sub lower() {
#	lc ($_[0]);
#	return;
#}


# params: table, column, value
# output: false if exists, true otherwise
sub is_value_in_db() {
	my ($TABLE, $COL, $VALUE);
	$TABLE=$_[0];
	$COL=$_[1];
	$VALUE=$_[2];
	if ( $DBENGINE =~ /^DB_BERKELEY$/) {
		&bdb_select_where($TABLE,$VALUE);
	} elsif ( $DBENGINE =~ /^DBTEXT$/) {
		my $res = system("$DBTEXTCMD","SELECT count(*) FROM $TABLE WHERE $COL=\'$VALUE\'");
		$result =$res;
	} else {
		#prepare query
		$sth = $dbh->prepare( "SELECT count(*) FROM $TABLE WHERE $COL=\'$VALUE\'" );
		#execute the query
		$sth->execute( );
		warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );	
		while ( @row = $sth->fetchrow_array( ) )  {
			    $result = "@row";
	 		     print "@row\n";
		}	

		$sth->finish();
	}

        if ($result == 0) {
		$response = 0;
	} else {
		$response = 1;
	}		

#print $response;
}
 
sub prompt_pw() {

    print "Password for $DBROOTUSER (If no password is needed just hit Enter, else introduce password): ";
    my $PASS = <STDIN>;
    chomp($PASS);	
    if ( $PASS =~ "" ) {
         $PW = "";
    } else {
       $PW = $PASS;
    }	
   return $PW;

}


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


sub validate_dbdata() {

 if( -d $DATA_DIR."/$_[0]" ) {

     $DB_SCHEMA = $DATA_DIR."/$_[0]";

     return $DB_SCHEMA;	

 } else {

     $DB_SCHEMA = "./$_[0]";	

     print "Warn fallback to local workdir ./$_[0]\n";

     return $DB_SCHEMA;

 }

}


sub db_load() {
	if ( $DBENGINE eq "" ) {
		print "database engine not specified, please setup one in the config script";
		$DBENGINELOADED = 0;
		return 0;
	} else {
	
			if ($DBENGINE =~ /(^mysql$)|(^MYSQL$)/) {
				print "Used database is mysql\n";
				&validate_dbdata("mysql");
				#Connect to the database.
				$dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST",
						"$DBRWUSER", "$DBRWPW", {'PrintError' => 0} );
				if ($dbh==NULL) {
					print "Failed to connect the configured Database ".
						"(database=$DBNAME;host=$DBHOST;user=$DBRWUSER)";
					$DBENGINELOADED = 0;
					return 0;
				}
				$DBENGINELOADED = 1;
			}

			elsif ($DBENGINE =~ /^oracle$/) {
				print "Used database is Oracle\n";
				&validate_dbdata("oracle");
				#Connect to the database.
				$dbh = DBI->connect("DBI:Oracle:database=$DBNAME;host=$DBHOST",
						"$DBRWUSER", "$DBRWPW", {'PrintError' => 0} );
				if ($dbh==NULL) {
					print "Failed to connect the configured Database ".
						"(database=$DBNAME;host=$DBHOST;user=$DBRWUSER)";
					$DBENGINELOADED = 0;
					return 0;
				}
				$DBENGINELOADED = 1;
			}

			elsif ($DBENGINE =~ /^Pg$/) {
				print "Used database is PostgreSQL\n";
				&validate_dbdata("postgres");
				$DBROOTUSER = "postgres";
				#Connect to the database.
				$dbh = DBI->connect("DBI:Pg:database=$DBNAME;host=$DBHOST;".
					"port=$DBPORT","$DBRWUSER","$DBRWPW", {'PrintError' => 0});
				if ($dbh==NULL) {
					print "Failed to connect the configured Database ".
						"(database=$DBNAME;host=$DBHOST;user=$DBRWUSER)";
					$DBENGINELOADED = 0;
					return 0;
				}
				$DBENGINELOADED = 1;
			}

			elsif ($DBENGINE =~ /^DBTEXT$/) {
				print "Used database is DBTEXT\n";
				&validate_dbdata("dbtext/opensips");
				$ENV{DBTEXT_PATH} = $DBTEXT_PATH;
				$DBENGINELOADED = 1;
			}

			elsif ($DBENGINE =~ /^DB_BERKELEY$/) {
				print "Used database is DB_BERKELEY\n";
				&validate_dbdata("db_berkeley/opensips");
				$ENV{PATH} = $DB_PATH;
				$DBENGINELOADED = 1;
			}
	}

	return 1;
}

#
##### ------------------------------------------------ #####
### CTLENGINE
#

if ( $CTLENGINELOADED eq 1 ) {
	print "Control engine " . $CTLENGINE . " loaded\n";
} else {
	print "no control engine found - tried " . $CTLENGINE . "\n";
}


#
##### ------------------------------------------------ #####
### common functions
#


sub require_dbengine() {
	if ( $DBENGINELOADED eq 0 ){
		print "This command requires a database engine - none was loaded\n";
	}
	return;
}

sub require_ctlengine() {
	if ( $CTLENGINELOADED eq 0 ) {
		print "This command requires a control engine - none was loaded\n";
	}
	return;	
}




sub not_command(){
	print "Not an opensips command!\n";
	return;
}


if ( $argnum  == 0 ) {	
	&interactively();
} elsif ($argnum gt 0) {
	&non_interactively();
}

sub interactively(){
	my @history_list;
	open(HIST,"<$HISTORY_FILE");
	@history_list=<HIST>;
	while ( $#history_list gt 0 ){	
		$result = shift(@history_list);
		chomp($result);	
		$term->addhistory($result);

	}
	close(HIST);
	ET: while (1) {

		$attribs->{completion_entry_function} = $attribs->{list_completion_function};
		$attribs->{completion_word} = [@command_list];
		$command = $term->readline('OpenSIPS$:');



			if ( $command eq  "") {
				next ET;			
			} 

	  	        @cmd = split(" ",$command);
			my $found=0;

		
			foreach my $i (@command_list){

				if ($cmd[0] eq $i) {
					$found=1;
				}

			}
				
			if ($found == 1 ) {

				system(&cmd());

			} elsif ( ($found == 0) & ($cmd[0] =~ /^quit/) ) {
					#$dbh->disconnect();
					open(HIST,"+>/tmp/osipsconsole_history");
					print HIST @history_list; 
					close(HIST);
					print "Thank you for flying Opensips!!\n";
					exit;
		     	} elsif ( $found == 0 ) {
				 	system(&not_command());
			}		
			


			$term->addhistory($cmd[0]);
			unshift(@history_list,"$command\n");

	}
}


sub non_interactively(){
	@cmd = @ARGV;		
	&cmd();	
}

sub cmd() {

	if ($cmd[0] =~ /^acl$/) {
		if (&db_load()) { system(&opensips_acl()); }
	} elsif ($cmd[0] =~ /^add$/) {
		if (&db_load()) {system(&subscriber()); }
	} elsif ($cmd[0] =~ /^avp$/) {
		if (&db_load()) {system(&avpops()); }
	} elsif ($cmd[0] =~ /(^aliasdb$)|(^alias_db$)/) {
		if (&db_load()) {system(&alias_db()); }
	} elsif ($cmd[0] =~ /^cr$/) {
		if (&db_load()) {system(&opensips_cr()); }
	} elsif ($cmd[0] =~ /^dialplan$/) {
		if (&db_load()) {system(&opensips_dialplan()); }
	} elsif ($cmd[0] =~ /^db$/) {
		system(&opensips_db());
	} elsif ($cmd[0] =~ /^dispatcher$/) {
		if (&db_load()) {system(&opensips_dispatcher()); }
	} elsif ($cmd[0] =~ /^domain$/) {
		if (&db_load()) {system(&domain()); }
	}elsif ($cmd[0] =~ /(^fifo$)|(^unixsock$)|(^udp$)|(^xmlrpc$)/) {
		&mi_comm();
	} elsif ($cmd[0] =~ /^lcr$/) {
		if (&db_load()) {system(&opensips_lcr()); }
	} elsif ($cmd[0] =~ /(^moni$)|(^monitor$)|(^con$)|(^console$)/) {
		&mi_comm_monitor();
	} elsif ($cmd[0] =~ /^ping$/) {
		system(&options_ping());
	} elsif ($cmd[0] =~ /^ps$/) {
		system(&opensips_ps());
	} elsif ($cmd[0] =~ /^passwd$/) {
		if (&db_load()) {system(&subscriber()); }
	} elsif ($cmd[0] =~ /^online$/) {
		&opensips_online();
	} elsif ($cmd[0] =~ /^rpid$/) {
		if (&db_load()) {system(&opensips_rpid()); }
	} elsif ($cmd[0] =~ /^restart$/) {
		system(&opensips_restart());
	} elsif ($cmd[0] =~ /^rm$/) {
		if (&db_load()) {system(&subscriber()); }
	} elsif ($cmd[0] =~ /(^speeddial$)|(^speed_dial$)/) {
		if (&db_load()) {system(&speeddial()); }
	} elsif ($cmd[0] =~ /^start$/) {
		system(&opensips_start());
	} elsif ($cmd[0] =~ /^stop$/) {
		system(&opensips_stop());
	} elsif ($cmd[0] =~ /^tls$/) {
		system(&tls_ca());
	} elsif ($cmd[0] =~ /^address$/) {
		if (&db_load()) {system(&address()); }
	} elsif ($cmd[0] =~ /(^ul$)|(^alias$)|(^usrloc$)/) {
		system(&opensips_usrloc());
	} elsif ($cmd[0] =~ /^version$/) {
		system(&opensips_version());
	} elsif ($cmd[0] =~ /^dr$/) {
		if (&db_load()) {system(&opensips_dr()); }
	} elsif ($cmd[0] =~ /^help$/) {
		system(&opensips_help());
	} else {
		print " unknown command!!!\n";
	}

	return;

}




#all
##### ------------------------------------------------- ##### 
#help
#
sub opensips_help() {

	&usage_online();
	print "\n\n";
	&usage_opensips_monitor();
	print "\n\n";
	&usage_ping();
	print "\n\n";
	&usage_usrloc();
	print "\n\n";
	&usage_base();
	print "\n\n";
	&usage_tls();
	print "\n\n";
	&usage_acl();
	print "\n\n";
	&usage_lcr();
	print "\n\n";
	&usage_cr();
	print "\n\n";
	&usage_rpid();
	print "\n\n";
	&usage_subscriber();
	print "\n\n";
	&usage_address();
	print "\n\n";
	&usage_dispatcher(); 
	print "\n\n";
	&usage_db_ops();
	print "\n\n";
	&usage_speeddial();
	print "\n\n";
	&usage_avp();
	print "\n\n";
	&usage_alias_db();
	print "\n\n";
	&usage_domain();
	print "\n\n";
	&usage_fifo();
	print "\n\n";
	&usage_dialplan;
	print "\n\n";
	&usage_unixsock();
	print "\n\n";
	&usage_db();
	print "\n\n";
	&berkeley_usage();
	print "\n\n";
	&usage_dr();
	print "\n\n";
}
#
##### ------------------------------------------------ #####
### opensips_start
#

sub opensips_start(){
	if ( ($#cmd+1) eq 1 ){
		print "\nStarting opensips........\n";
		if ( -r $PID_FILE ) {
				`ps -ef | egrep opensips`;
				`ls -l $PID_FILE`;
				print "\nPID file exists ( " . $PID_FILE . " )! OpenSIPS already running?\n";
				return;
			}

		if ( ! -x $OSIPSBIN ) {
			print "\nOpenSIPS binaries not found at " . $OSIPSBIN . "\n";
			print "\nset OSIPSBIN to the path of opensips in " . $0 . " or ~/.osipsconsolerc\n";		        
			return;
		}

		if ( $SYSLOG == 1 ) {
			`$OSIPSBIN -P $PID_FILE $STARTOPTIONS 1>/dev/null 2>/dev/null`;
			return;0
		} else {
			`$OSIPSBIN -f $PATH_ETC/opensips.cfg -P $PID_FILE -E $STARTOPTIONS`;	
			return;			
		}

		sleep 3;

		if ( -z $PID_FILE ) {
			print "\nPID file " . $PID_FILE . " does not exist -- OpenSIPS start failed\n";
			return;
		}

		print "\nstarted (pid: " . `cat $PID_FILE` . ")\n";

	} elsif ($cmd[1] =~ /h/){		
		&usage_base();		
	}else {
		print "No parameters required!!! Syntax is not correct\n";
	}
	return;

}

#
##### ------------------------------------------------ #####
### opensips_stop
#

sub opensips_stop(){
	if ( ($#cmd+1) eq 1 ){
		print "\nStopping OpenSIPS : \n";
		if ( -r $PID_FILE ) {
			my $sys = `cat $PID_FILE`;
			`kill $sys`;			
		        print "stopped\n";
			return;
		}
		else {               
	
		       print "\nNo PID file found ( " . $PID_FILE . " )! OpenSIPS probably not running\n";

		       print "check with 'ps -ef | " . $EGREP . " opensips'\n";               
		       
		       return;	
		}
	}elsif ($cmd[1] =~ /h/){		
		&usage_base();		
	}else {
		print "No parameters required!!! Syntax is not correct\n";
	}
	return;

}

#
##### ------------------------------------------------ #####
### opensips_restart
#

sub opensips_restart(){
	&opensips_stop();
	sleep 2;
	&opensips_start();			
}




#
##### ------------------------------------------------ #####
### oppensips_acl
#


sub opensips_acl() {

	if (!$#cmd gt 0) {
		print "Too few parameters\n";
		&usage_acl();
		return;
	} else {
		if ($cmd[1] =~ /^show$/) {
			
			if ( $#cmd eq 1 ) {

				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					&bdb_select($ACL_TABLE); 
				} elsif ( $DBENGINE =~ /^DBTEXT$/) {
					system("$DBTEXTCMD","SELECT * FROM $ACL_TABLE ");
				} else {												
					$sth = $dbh->prepare ("SELECT * FROM $ACL_TABLE ");	

					##execute the query
					$sth->execute( );

			 		## Retrieve the results of a row of data and print
		 	                print "\tQuery results:\n================================================\n";
					while (@row = $sth->fetchrow_array( ) )  {
		     		             	print "@row\n";
			    	        }		 

					warn "Error retireving data from the database! ", $sth->errstr( ), "\n" if $sth->err( );

					$sth->finish(); 
				}
 				return;
			}					
				
			if ( $#cmd == 2 ) {			
			
				if (  &is_user($cmd[2]) == 0 ) {
						print "Non-existent user " . $cmd[2] . "\n";						
						return;

  				 } else {

					&set_user($cmd[2]);

					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						print "For DB BERKELEY, this operation needs 2 params: username\@domain and group\n";
							return;
						} elsif ( $DBENGINE =~ /^DBTEXT$/) {
							system("$DBTEXTCMD","SELECT * 
									     FROM $ACL_TABLE
		                                                             WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\'  
		                                                             AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' ");
						} else {
						
							$sth = $dbh->prepare ("SELECT * 
									       FROM $ACL_TABLE
		                                                               WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\'  
		                                                               AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' ");	
				
							 ##execute the query
							 $sth->execute( );

				 			 ## Retrieve the results of a row of data and print
							 print "\tQuery results:\n================================================\n";
							 while (@row = $sth->fetchrow_array( ) )  {
			     		                 	print "@row\n";
				    	                 }		 
							 warn "Error retireving data from the database! ", $sth->errstr( ), "\n" if $sth->err( );
							 $sth->finish(); }
						}
			} elsif ( $#cmd == 3 ){
				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN,$cmd[3]);
					&bdb_select_where($ACL_TABLE,$key); 
				} else {
					print "Too many parameters for $DBENGINE query\n";
					return;
				}
			} else {
				&usage_acl();
				return;
			}
		}
		elsif ($cmd[1] =~ /^grant$/) {
			if ( $#cmd lt 3 ) {
				&usage_acl();
				return;
			}

			my $acl_inserted = 0;

			if ( $#cmd == 3 ) {
					
				if ( &is_user($cmd[2]) == 0 ) {  
					print "Non-existent user " . $cmd[2] . " Still proceeding? [Y|N]:";
					if ( ( $input = <STDIN>) =~ /[y|Y]/  ) {
						print "Proceeding with non-local user\n";
					} else {
						return;
					}
				}
				&set_user($cmd[2]);			

				if ( $VERIFY_ACL == 1 ) {
					my $found = 0;
					foreach my $i (@ACL_GROUPS) {
						if ( $cmd[3] =~ /(^$i$)/ ) {
							print $cmd[3]."...".$i."\n";
							$found = 1;					
						}
					 }
					if ( $found == 0 ) {
						print "Invalid privilege: acl " . $cmd[3] . " ignored\n";
						return;
					 }
			        }


				my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
				my $date = join("-",$year+1900,$mon+1,$mday+1);
				my $time = join(":",$hour,$min,$sec);
				my $last_modified =  join(" ",$date,$time);
				my $unix_time = time();

				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN,$cmd[3]);
					my $value = join(" ",$last_modified);
					&bdb_insert($ACL_TABLE,$key,$value); 
				} elsif ( $DBENGINE =~ /^DBTEXT$/) {

					system("$DBTEXTCMD","INSERT INTO $ACL_TABLE ($acl_table{'ACL_USER_COLUMN'},$acl_table{'ACL_GROUP_COLUMN'},$acl_table{'ACL_MODIFIED_COLUMN'},$acl_table{'ACL_DOMAIN_COLUMN'} ) VALUES (\"$OSIPSUSER\",\"$cmd[3]\",$unix_time, \"$OSIPSDOMAIN\" ) ");
				} else {
					$sth = $dbh->prepare ("INSERT INTO $ACL_TABLE ($acl_table{'ACL_USER_COLUMN'},$acl_table{'ACL_GROUP_COLUMN'},
							       $acl_table{'ACL_MODIFIED_COLUMN'},$acl_table{'ACL_DOMAIN_COLUMN'} ) 
							       VALUES (\'$OSIPSUSER\',\'$cmd[3]\',\'$last_modified\', \'$OSIPSDOMAIN\' ) " );	
		
					 ##execute the query
					 $sth->execute( );
		 
					 print "Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
					 $sth->finish();
		
						 
					if ( &is_user($cmd[2]) == 0 ) { 
						print "acl - SQL Error\n";
						return;
					}
					$acl_inserted = 1;
				}
				
			}

			if ( $acl_inserted == 1 ) {

				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN,$cmd[3]);
					&bdb_select_where($ACL_TABLE,$key); 
				} elsif ( $DBENGINE =~ /^DBTEXT$/) {
					system("$DBTEXTCMD","SELECT * FROM $ACL_TABLE 
					       WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\' 
					       AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' ");
				} else {
					$sth = $dbh->prepare ("SELECT * FROM $ACL_TABLE 
							       WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\' 
							       AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' ");	
			
					 ##execute the query
					 $sth->execute( );

		 			 ## Retrieve the results of a row of data and print						 
	 	                         print "\tQuery results:\n================================================\n";
					 while (@row = $sth->fetchrow_array( ) )  {
       			                 	 print "@row\n";
		    	                 }		 
					 warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
					 $sth->finish();
				} 
	
			}

			}
			elsif ($cmd[1] =~ /^revoke$/) {

				 &set_user($cmd[2]);

				 if ( $#cmd == 2 ) {

					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						print "Too few parameters for this operation!!!\n";
					} elsif ( $DBENGINE =~ /^DBTEXT$/) {						
						system("$DBTEXTCMD","DELETE FROM $ACL_TABLE 
								       WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\' 
								       AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
					} else {
						$sth = $dbh->prepare ("DELETE FROM $ACL_TABLE 
								       WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\' 
								       AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");	
				
						 ##execute the query
						 $sth->execute( );

			 			 ## Retrieve the results of a row of data and print
						 warn "Could not delete entry! ", $sth->errstr( ), "\n" if $sth->err( );
						 $sth->finish();
					}
 
				} elsif ( $#cmd == 3 ) {


					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN,$cmd[3]);
						&bdb_delete($ACL_TABLE,$key); 
					} elsif ( $DBENGINE =~ /^DBTEXT$/) {
						system("$DBTEXTCMD","DELETE FROM $ACL_TABLE 
									WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\' 
									AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' 
									AND $acl_table{'ACL_GROUP_COLUMN'}=\'$cmd[3]\'");
					} else {
						 $sth = $dbh->prepare ("DELETE FROM $ACL_TABLE 
									WHERE $acl_table{'ACL_USER_COLUMN'}=\'$OSIPSUSER\' 
									AND $acl_table{'ACL_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' 
									AND $acl_table{'ACL_GROUP_COLUMN'}=\'$cmd[3]\'");	
				
						 ##execute the query
						 $sth->execute( );

			 			 ## Retrieve the results of a row of data and print
						 warn "Could not delete entry! ", $sth->errstr( ), "\n" if $sth->err( );
						 $sth->finish(); 
					}
				} else {
					print "acl - wrong number of parameters\n";
					&usage_acl();
					return;
				}			
			
			}
			elsif ($cmd[1] =~ /^h$/) {
				&usage_acl();
			}

			else {

				print "acl -unknown command $cmd[1]\n";

			}
	
	}
	return;
}


#
##### ------------------------------------------------ #####
### opensips_rpid
#
sub opensips_rpid() {

	if ( $#cmd lt 1 ) {
		print "rpid - too few parameters\n";	
		&usage_rpid();
		return;
	} 
			if ($cmd[1] =~ /^h/) {
				&usage_rpid();
				return;
			}

			elsif ($cmd[1] =~ /^add$/) {				 
				 if ( $#cmd lt 3 ) {
					print "Too few arguments!";
				 } else {
					
					&set_user($cmd[2]);
					&is_user($cmd[2]);

					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
						&bdb_update($SUB_TABLE,$key); 
					} elsif ( $DBENGINE =~ /^DBTEXT$/) {
						system("$DBTEXTCMD","UPDATE $SUB_TABLE 
								      SET $sub_table{'RPID_COLUMN'} = $cmd[3] 
								      WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\"$OSIPSUSER\" 
								      AND  $sub_table{'REALM_COLUMN'}= \"$OSIPSDOMAIN\" ");
					} else {
					 	$sth = $dbh->prepare("UPDATE $SUB_TABLE 
								      SET $sub_table{'RPID_COLUMN'} = $cmd[3] 
								      WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\'$OSIPSUSER\' 
								      AND  $sub_table{'REALM_COLUMN'}= \'$OSIPSDOMAIN\' " );	

						 ##execute the query
						 $sth->execute( );
						 
						 warn "Entry was not updated in database!", $sth->errstr( ), "\n" if $sth->err( );
						 $sth->finish();
					}
				 }
			}
 
			elsif ($cmd[1] =~ /^rm$/) {
				  if ( $#cmd lt 2 ) {
					print "Too few arguments!";
				 } else {
					
					&set_user($cmd[2]);
					&is_user($cmd[2]);


					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
						&bdb_update($SUB_TABLE,$key); 
					} elsif ( $DBENGINE =~ /^DBTEXT$/) {
						system("$DBTEXTCMD","UPDATE $SUB_TABLE 
								      SET rpid = null  
	   							      WHERE $sub_table{'SUBSCRIBER_COLUMN'} = \'$OSIPSUSER\' 
		                                                      AND $sub_table{'REALM_COLUMN'} = \'$OSIPSDOMAIN\'");
					} else {
					 	$sth = $dbh->prepare("UPDATE $SUB_TABLE 
								      SET rpid = null  
	   							      WHERE $sub_table{'SUBSCRIBER_COLUMN'} = \'$OSIPSUSER\' 
		                                                      AND $sub_table{'REALM_COLUMN'} = \'$OSIPSDOMAIN\' " );	

						 ##execute the query
						 $sth->execute( );

						 warn "Entry was not updated in database!", $sth->errstr( ), "\n" if $sth->err( );
						 $sth->finish();
					}
				 }
			} 
			elsif ($cmd[1] =~ /^show$/) {
				 if ($#cmd lt 2){
					print "Too few parameters!\n";
					return;
				 } else{
					if ( &is_user($cmd[2]) == 0 ) {
						print "rpid - invalid user " . $cmd[2] . "\n";
						return;				 
					}
				 }

					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
						&bdb_select_where($SUB_TABLE,$key); 
					} elsif ( $DBENGINE =~ /^DBTEXT$/)  {
						system("$DBTEXTCMD","select $sub_table{'SUBSCRIBER_COLUMN'}, $sub_table{'RPID_COLUMN'} FROM $SUB_TABLE WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\'$OSIPSUSER\' AND $sub_table{'REALM_COLUMN'}=\'$OSIPSDOMAIN\'");
					} else {
					 	$sth = $dbh->prepare("select $sub_table{'SUBSCRIBER_COLUMN'}, $sub_table{'RPID_COLUMN'} FROM $SUB_TABLE WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\'$OSIPSUSER\' AND $sub_table{'REALM_COLUMN'}=\'$OSIPSDOMAIN\'" );	

						 ##execute the query
						 $sth->execute( );

						 warn "Entry was not updated in database!", $sth->errstr( ), "\n" if $sth->err( );
						 $sth->finish();
					}
			} 

			else {

				print "rpid -unknown command $cmd[1]\n";

			}
}


#
##### ------------------------------------------------ #####
### opensips_lcr
#
sub opensips_lcr(){
	if ( $#cmd lt 1 ) {
		print "Too few parameters!\n";
		&usage_lcr();
		return;
	}

		if ($cmd[1] =~ /^show$/) {
			print "lcr routes\n";
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($LCR_TABLE); 
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","SELECT * 
							FROM $LCR_TABLE 
							ORDER BY $lcr_table{'LCR_PREFIX_COLUMN'} ");
			} else {
				$sth = $dbh->prepare ( "SELECT * 
							FROM $LCR_TABLE 
							ORDER BY $lcr_table{'LCR_PREFIX_COLUMN'} ");	
				
				 ##execute the query
				 $sth->execute( );
	 			 
				 ## Retrieve the results of a row of data and print
				 print "\tQuery results:\n================================================\n";

				 while (@row = $sth->fetchrow_array( ) )  {
	     		                 	print "@row\n";
	    	                 }		 
				 warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
				 $sth->finish(); 
			}
	
			 #retrieve lcr gateways	
			 print "lcr gateways\n";

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($GW_TABLE); 
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","SELECT $gw_table{'LCR_GW_GWNAME_COLUMN'}, $gw_table{'LCR_GW_IP_COLUMN'},$gw_table{'LCR_GW_PORT_COLUMN'}, 							 $gw_table{'LCR_GW_URIS_COLUMN'}, $gw_table{'LCR_GW_PROTO_COLUMN'},$gw_table{'LCR_GW_GRPID_COLUMN'}, 							 $gw_table{'LCR_GW_STRIP_COLUMN'}, $gw_table{'LCR_GW_TAG_COLUMN'},$gw_table{'LCR_GW_FLAGS_COLUMN'} 
							 FROM $GW_TABLE 
							 ORDER BY $gw_table{'LCR_GW_GRPID_COLUMN'}");
			} else {			 
				 $sth = $dbh->prepare ( "SELECT $gw_table{'LCR_GW_GWNAME_COLUMN'}, $gw_table{'LCR_GW_IP_COLUMN'},$gw_table{'LCR_GW_PORT_COLUMN'}, 							 $gw_table{'LCR_GW_URIS_COLUMN'}, $gw_table{'LCR_GW_PROTO_COLUMN'},$gw_table{'LCR_GW_GRPID_COLUMN'}, 							 $gw_table{'LCR_GW_STRIP_COLUMN'}, $gw_table{'LCR_GW_TAG_COLUMN'},$gw_table{'LCR_GW_FLAGS_COLUMN'} 
							 FROM $GW_TABLE 
							 ORDER BY $gw_table{'LCR_GW_GRPID_COLUMN'}");	
				
				 ##execute the query
				 $sth->execute( );

	 			 ## Retrieve the results of a row of data and print						 
		                 print "\tQuery results:\n================================================\n";

				 while (@row = $sth->fetchrow_array( ) )  {
	     		                 	print "@row\n";
	    	                 }		 
				 warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
				 $sth->finish(); 

			}
			
		}

		elsif ($cmd[1] =~ /^reload$/) {
			&mi_comm("lcr_reload");			
			
		}
	
		elsif ($cmd[1] =~ /^addroute$/) {
			if ( $#cmd lt 5 ) {
				print "lcr - too few parameters\n";
				&usage_lcr();
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				my $value = join(" ", $cmd[3],$cmd[4],$cmd[5]); 
				&bdb_insert($LCR_TABLE,$key,$value); 
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","INSERT INTO $LCR_TABLE ($lcr_table{'LCR_PREFIX_COLUMN'}, $lcr_table{'LCR_FROMURI_COLUMN'}, $lcr_table{'LCR_GRPID_COLUMN'}, $lcr_table{'LCR_PRIO_COLUMN'}) VALUES (" . $cmd[2] . "," . $cmd[3] . "," . $cmd[4] . "," . $cmd[5] . ")");
			} else {
				$sth = $dbh->prepare (
				"INSERT INTO $LCR_TABLE 
				 ($lcr_table{'LCR_PREFIX_COLUMN'}, $lcr_table{'LCR_FROMURI_COLUMN'}, $lcr_table{'LCR_GRPID_COLUMN'}, $lcr_table{'LCR_PRIO_COLUMN'}) 				 VALUES (" . $cmd[2] . "," . $cmd[3] . "," . $cmd[4] . "," . $cmd[5] . ")" );	
	 
				 ##execute the query
				 $sth->execute( );

		 		 print "Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );

				 $sth->finish();
			}
			
			&mi_comm('lcr_reload');			
		}
		

		elsif ($cmd[1] =~ /^rmroute$/){
			if ( $#cmd lt 5 ) {
				print "too few parameters\n";
				&usage_lcr();
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_delete($LCR_TABLE,$cmd[2]); 
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","DELETE FROM $LCR_TABLE 
						       WHERE $lcr_table{'LCR_PREFIX_COLUMN'}=$cmd[2] 
						       AND $lcr_table{'LCR_FROMURI_COLUMN'}=$cmd[3] 
						       AND $lcr_table{'LCR_GRPID_COLUMN'}=$cmd[4] 
		                                       AND $lcr_table{'LCR_PRIO_COLUMN'}=$cmd[5]");
			} else {
				$sth = $dbh->prepare ("DELETE FROM $LCR_TABLE 
						       WHERE $lcr_table{'LCR_PREFIX_COLUMN'}=$cmd[2] 
						       AND $lcr_table{'LCR_FROMURI_COLUMN'}=$cmd[3] 
						       AND $lcr_table{'LCR_GRPID_COLUMN'}=$cmd[4] 
		                                       AND $lcr_table{'LCR_PRIO_COLUMN'}=$cmd[5]");	
				
				##execute the query
				$sth->execute( );

			 	## Retrieve the results of a row of data and print
				warn "Could not delete entry!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish(); 
			}

			&mi_comm('lcr_reload');			
		}
		
		elsif ($cmd[1] =~ /^addgw$/) {

			my ($GW_FLAGS, $GW_TAG, $GW_STRIP, $STRIP, $GW_NAME, $GW_IP, $GW_PORT, $GW_URI_SCHEME, $GW_GRP_ID, $GW_TRANSPORT);

			if ( $#cmd < 7 ) {
				print "Too few parameters!\n";
				&usage_lcr();
				return;
			}
			
			if ( $#cmd gt 7 ) {
				$GW_FLAGS = $cmd[8];
				if ( $#cmd gt 8 ) {
					$GW_TAG = $cmd[9];
				} else {
					$GW_TAG = "";
					$GW_STRIP = 0;
				}
				if ( $#cmd gt 9 ) {
					$STRIP = $cmd[10];
				} else {
					$STRIP = 0;
				}
				if ( $#cmd gt 10 ) {
					print "Too many parameters!\n";
					&usage_lcr();
					return;
				}
			} else {
				$GW_FLAGS = 0;
				$GW_TAG = "";
				$STRIP = 0;
			}
			
			$GW_NAME = $cmd[2];
			$GW_IP = $cmd[3];
			$GW_PORT = $cmd[4];
			$GW_URI_SCHEME = $cmd[5];
			if   ( $GW_URI_SCHEME =~ /^sip$/ ) {
				$GW_URI_SCHEME = 1;
			} elsif ( $GW_URI_SCHEME =~ /^sips$/ ) {
				$GW_URI_SCHEME = 2;
			}
			$GW_TRANSPORT = $cmd[6];
			if   ( $GW_TRANSPORT =~ /^udp$/ ) {
				$GW_TRANSPORT = 1;
			} elsif ( $GW_TRANSPORT =~ /^tcp$/ ) {
				$GW_TRANSPORT = 2;
			} elsif ( $GW_TRANSPORT =~ /^tls$/ ) {
				$GW_TRANSPORT = 3;
			}
			$GW_GRP_ID=$cmd[7];

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $GW_NAME;
				my $value = join(" ",$GW_GRP_ID,$GW_IP,$GW_PORT,$GW_URI_SCHEME,$GW_TRANSPORT,$STRIP,$GW_TAG, $GW_FLAGS);
				&bdb_insert($GW_TABLE,$key,$value); 
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","INSERT INTO $GW_TABLE ( $gw_table{'LCR_GW_GWNAME_COLUMN'}, $gw_table{'LCR_GW_GRPID_COLUMN'},$gw_table{'LCR_GW_IP_COLUMN'},$gw_table{'LCR_GW_PORT_COLUMN'},$gw_table{'LCR_GW_URIS_COLUMN'},$gw_table{'LCR_GW_PROTO_COLUMN'},$gw_table{'LCR_GW_STRIP_COLUMN'},$gw_table{'LCR_GW_TAG_COLUMN'},$gw_table{'LCR_GW_FLAGS_COLUMN'} ) VALUES (\'$GW_NAME\', $GW_GRP_ID,\'$GW_IP\', $GW_PORT, $GW_URI_SCHEME,	$GW_TRANSPORT, $STRIP, \'$GW_TAG\', $GW_FLAGS)");
			} else {
				$sth = $dbh->prepare ("INSERT INTO $GW_TABLE ( $gw_table{'LCR_GW_GWNAME_COLUMN'}, $gw_table{'LCR_GW_GRPID_COLUMN'},$gw_table{'LCR_GW_IP_COLUMN'},$gw_table{'LCR_GW_PORT_COLUMN'},$gw_table{'LCR_GW_URIS_COLUMN'},$gw_table{'LCR_GW_PROTO_COLUMN'},$gw_table{'LCR_GW_STRIP_COLUMN'},$gw_table{'LCR_GW_TAG_COLUMN'},$gw_table{'LCR_GW_FLAGS_COLUMN'} ) VALUES (\'$GW_NAME\', $GW_GRP_ID,\'$GW_IP\', $GW_PORT, $GW_URI_SCHEME,	$GW_TRANSPORT, $STRIP, \'$GW_TAG\', $GW_FLAGS)" );	
 
				 ##execute the query
				 $sth->execute( );

		 		 print "Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );

				 $sth->finish();
			}
			
			&mi_comm('lcr_reload');		
			
		}

		elsif ($cmd[1] =~ /^rmgw$/) {
			if ( $#cmd lt 2 ) {
				print "missing gateway to be removed\n";
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_delete($GW_TABLE,$cmd[2]); 
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","DELETE FROM $GW_TABLE 
						       WHERE $gw_table{'LCR_GW_GWNAME_COLUMN'}=$cmd[2]");
			} else {

				$sth = $dbh->prepare ("DELETE FROM $GW_TABLE 
						       WHERE $gw_table{'LCR_GW_GWNAME_COLUMN'}=$cmd[2]");	
				
				##execute the query
				$sth->execute( );
			 	## Retrieve the results of a row of data and print
			
				warn "Could not delete entry!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			} 
			
			&mi_comm('lcr_reload');			
		}

		elsif ($cmd[1] =~ /^h$/) {
			print "Too few parameters\n";
			&usage_lcr;		
		}

		else {

			print "lcr -unknown command $cmd[1]\n";

		}

}

#
##### ------------------------------------------------ #####
### subscriber - add|passwd|rm
#
sub subscriber() {
	if ( $#cmd lt 1 ) {
		print "too few parameters\n";
		&usage_subscriber();
		return;
	}
		if ($cmd[0] =~ /^add$/) {
			if  ($#cmd ne 2 ) {
				&usage_subscriber();
				return;
			}
			&credentials($cmd[1],$cmd[2]);
			if ( &is_user($cmd[1]) != 0 ) {
				print "user " . $cmd[1] . " already exists\n";
				return;
			}

			&set_user($cmd[1]);
			&check_alias($OSIPSUSER,$OSIPSDOMAIN);
			if ( $ALIAS_EXISTS == 1 ) {
				print "user " . $cmd[1] . " already exists as alias\n";
				return;
			} else {
				print "Alias $cmd[1] does not exist!\n";
			}		
		

                        if ( $STORE_PLAINTEXT_PW eq "1" ) {
	                        $PASS = $cmd[2];
				$HA1 = "";
				$HA1B = "";
                        } else {
        	                $PASS = "";
				&credentials($cmd[1],$cmd[2]);
	
                        }
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				my $value = join(" ", $cmd[2], $HA1, $HA1B);
				&bdb_insert($SUB_TABLE, $key,$value);				
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD","INSERT INTO $SUB_TABLE 
					       ($sub_table{'SUBSCRIBER_COLUMN'},$sub_table{'REALM_COLUMN'},$sub_table{'HA1_COLUMN'},
						$sub_table{'HA1B_COLUMN'},$sub_table{'PASSWORD_COLUMN'},$sub_table{'EMAIL_ADDRESS'} ) 
						VALUES (\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$HA1\',\'$HA1B\',\'$PASS\',\'\') " );
			} else {
				#prepare the query
				$sth = $dbh->prepare( "INSERT INTO $SUB_TABLE 
						       ($sub_table{'SUBSCRIBER_COLUMN'},$sub_table{'REALM_COLUMN'},$sub_table{'HA1_COLUMN'},
							$sub_table{'HA1B_COLUMN'},$sub_table{'PASSWORD_COLUMN'},$sub_table{'EMAIL_ADDRESS'} ) 
							VALUES (\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$HA1\',\'$HA1B\',\'$PASS\',\'\') " );

	
				#execute the query
				$sth->execute( );

				warn "Introducing the new user " . $cmd[1] . " to subscriber table failed ", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
		}

		elsif ($cmd[0] =~ /^passwd$/) {
			if ( $#cmd lt 2 ) {
				&usage_subscriber();
				return;
			} elsif ($#cmd eq 2){

				if ( &is_user( $cmd[1]) == 0 ) {
					print "non-existent user: " . $cmd[1] . "\n";
					return;
				}

				if ( $STORE_PLAINTEXT_PW eq "1" ) {
					$PASS = $cmd[2];
	                                $HA1 = " ";
        	                        $HA1B = " ";
				} else {
					$PASS = "";
					&credentials($cmd[1],$cmd[2]);
				}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				&bdb_update($SUB_TABLE, $key);				
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
					system("$DBTEXTCMD","UPDATE $SUB_TABLE 
							       SET $sub_table{'HA1_COLUMN'}=\"$HA1\",$sub_table{'HA1B_COLUMN'}=\"$HA1B\",
							       $sub_table{'PASSWORD_COLUMN'}=\"$PASS\"
							       WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\"$OSIPSUSER\" 
		                                               AND $sub_table{'REALM_COLUMN'}=\"$OSIPSDOMAIN\"");

				} else {

					$sth = $dbh->prepare( "UPDATE $SUB_TABLE 
							       SET $sub_table{'HA1_COLUMN'}=\'$HA1\',$sub_table{'HA1B_COLUMN'}=\'$HA1B\',
							       $sub_table{'PASSWORD_COLUMN'}=\'$PASS\'
							       WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\'$OSIPSUSER\' 
		                                               AND $sub_table{'REALM_COLUMN'}=\'$OSIPSDOMAIN\'" );
	
					#execute the query
					$sth->execute( );

					warn "Changing the password for user: " . $cmd[1] . " in database failed", $sth->errstr( ), "\n" if $sth->err( );
			
					$sth->finish();
				}
			}
 						
		}
		elsif ($cmd[0] =~ /^rm$/) {

			if ( $#cmd ne 1 ) {
				&usage_subscriber();
				return;
			}

			&require_ctlengine();
			

			if ( &is_user ( $cmd[1] ) == 0) {
				print "non-existent user $cmd[1]\n";
				return;
			}

			# begin with remove all user's privileges
			# ####################################################acl revoke $1  > /dev/null 2>&1

			# destroy db-aliases
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_delete($DA_TABLE, "$OSIPSUSER","$OSIPSDOMAIN");				
				#&bdb_select($DA_TABLE);	
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD", "DELETE FROM $DA_TABLE 
					      WHERE $da_table{'DA_USER_COLUMN'}=\'$OSIPSUSER\' 
			                      AND $da_table{'DA_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
			} else {		
				$sth = $dbh->prepare("DELETE FROM $DA_TABLE 
						      WHERE $da_table{'DA_USER_COLUMN'}=\'$OSIPSUSER\' 
					              AND $da_table{'DA_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'" );
				#execute the query
				$sth->execute( );			
				warn "Destroying db-aliases for: " . $cmd[1] . " in database failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			# destroy the user now
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_delete($SUB_TABLE, "$OSIPSUSER","$OSIPSDOMAIN");				
				#&bdb_select($SUB_TABLE);	
			} elsif ( $DBENGINE =~ /^DBTEXT$/) {
				system("$DBTEXTCMD", "DELETE FROM $SUB_TABLE 
					      WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\'$OSIPSUSER\'
 					      AND $sub_table{'REALM_COLUMN'}=\'$OSIPSDOMAIN\'");
			} else {
				$sth = $dbh->prepare("DELETE FROM $SUB_TABLE 
						      WHERE $sub_table{'SUBSCRIBER_COLUMN'}=\'$OSIPSUSER\'
	 					      AND $sub_table{'REALM_COLUMN'}=\'$OSIPSDOMAIN\'" );
				#execute the query
				$sth->execute( );			

				warn "Destroying user " . $cmd[1] . " in subscriber table failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			# and also all his contacts			
			&mi_comm(join(" ","ul_rm",$USRLOC_TABLE, $OSIPSUSER."\@".$OSIPSDOMAIN));

		}

		else {

			print "unknown command $cmd[0]\n";

		}
	
}



#
##### ------------------------------------------------ #####
### opensips_usrloc 
#

sub opensips_usrloc() {
	if ( $#cmd lt 1 ) {
		print "usrloc - too few parameters\n";
		&usage_usrloc();
		return;
	}
	&require_ctlengine();
	if  ( $cmd[0] eq "alias" ) {
		$USRLOC_TABLE=$ALS_TABLE;
		if ( -z $USRLOC_TABLE ) {
			$USRLOC_TABLE = 'aliases';
		}
		$CHECK_SUB = 1;
	} elsif  ( $cmd[0] eq "ul" ) {
		$USRLOC_TABLE = $UL_TABLE;
		if ( -z $USRLOC_TABLE ) {
			$USRLOC_TABLE = 'location';
		} 
		$CHECK_SUB = 0;
	} elsif ( $cmd[0] eq 'usrloc' ) {
		$USRLOC_TABLE= $UL_TABLE;
		if ( -z $USRLOC_TABLE ) {
			$USRLOC_TABLE = 'location';
		}
		$CHECK_SUB = 0;
	} else {
		print "usrloc - unknown subcommand " . $cmd[0];
		&usage_usrloc();
		return;
	}

		if ($cmd[1] =~ /^h$/) {
			&usage_usrloc();
			return;
		}
		elsif ($cmd[1] =~ /^show$/) {
			if (  $#cmd == 2){
				if ($cmd[2] eq "--brief"){
					&mi_comm("ul_dump", "brief");					
				} else {
					&set_user($cmd[2]);
					&mi_comm(join(" ","ul_show_contact",$USRLOC_TABLE,$OSIPSUSER."\@".$OSIPSDOMAIN));
				}
			} elsif ( $#cmd == 1){
				&mi_comm("ul_dump");							
			} else {
				print "wrong number of params";
				&usage_usrloc();
			}

		}

		elsif ($cmd[1] =~ /^add$/) {
			my ($UL_EXPIRES, $UL_FLAGS, $BR_FLAGS);
			if ( $#cmd == 2 ) {
				# expires 0 means persistent contact
				$UL_EXPIRES = 0;
				$UL_FLAGS = 0;
				$BR_FLAGS = 0
			}elsif ( $#cmd == 3 ) {
				$UL_EXPIRES = $cmd[3];
				$UL_FLAGS = 0;
				$BR_FLAGS = 0;
			}else {
				&usage_usrloc();
				return;
			}			
							
			if ( &check_uri($cmd[3]) != 0 ) {
				print "$cmd[3] is not a valid URI\n";
				return;
			}

			&set_user($cmd[2]);
			if ( $CHECK_SUB != 0 ) {
				if ( &is_user( $cmd[2] ) ne 0 ) {
					print "overlap of alias with an existing subscriber name\n";
					return;
				}
			}

			&check_alias($OSIPSUSER, $OSIPSDOMAIN);
			if ( $ALIAS_EXISTS == 1 ) {
				if ( $CHECK_SUB != 0 ) {
					print "alias already defined\n";
				} else {
					print "AOR is an alias\n";
				}
				return;
			}

			&mi_comm( join("ul_add" ,$USRLOC_TABLE, $OSIPSUSER . "\@" . $OSIPSDOMAIN, $cmd[2],$UL_EXPIRES, "1.00", "0", $UL_FLAGS, $BR_FLAGS,$ALL_METHODS));
			
		}

		elsif ($cmd[1] =~ /^rm$/) {
			if ( $#cmd == 2 ) {
				&set_user($cmd[2]);
				&mi_comm(join(" ","ul_rm",$USRLOC_TABLE, $OSIPSUSER."\@".$OSIPSDOMAIN));

			 }elsif ( $#cmd == 3 ) {
				&set_user( $cmd[2] );		
				if ( &check_uri($cmd[3]) != 0 ) {
					print $cmd[3] . "is not a valid SIP URI (sip:[user\@]domain)";
					return;
				}

				&mi_comm(join(" ","ul_rm_contact", $USRLOC_TABLE, $OSIPSUSER . "\@" . $OSIPSDOMAIN,$cmd[3]));				

			} else {
				print "wrong number of params\n";
				&usage_usrloc();
				return;
			}
			
		}

		else {

			print "usrloc|ul|alias -unknown command $cmd[1]\n";

		}
	
}


#
##### ------------------------------------------------ #####
### options_ping 
#
sub options_ping() {
	if ( $#cmd lt 1 ) {
		print "Too few parameters";
		&usage_ping();
		return;
	}

	my $myhost = &get_my_host();
	&require_ctlengine();
	my $temp = &mi_comm(join(" ","t_uac_dlg", "OPTIONS", "$cmd[1]", ".", ".","\"From:sip:daemon\@$myhost\r\nTo:<$cmd[1]>\r\nContact:sip:daemon\@$myhost\""));		
	print $temp;	
	return;	
}



#
##### ------------------------------------------------ #####
### domain 
#
sub domain() {
	if ( $#cmd lt 1 ) {
		print "Too few parameters!\n";
		&usage_domain();
		return;
	} elsif ( $#cmd gt 2 ) {
		print "Too many parameters!\n";
		&usage_domain();
		return;
	}	

	
		if ($cmd[1] =~ /^reload$/) {

			if ( $#cmd gt 1 ) {
				print "Too many parameters\n!";
				&usage_domain();
				return;
			}
						
			&require_ctlengine();
			&mi_comm('domain_reload');
		}
		
		elsif ($cmd[1] =~ /^show$/) {

			if ( $#cmd gt 1 ) {
				print "Too many parameters\n!";
				&usage_domain();
				return;
			}
			
			&require_ctlengine();
			&mi_comm('domain_dump');
		}
		
		elsif ($cmd[1] =~ /showdb$/) {
			if ( $#cmd gt 1 ) {
				print "Too many parameters\n!";
				&usage_domain();
				return;
			}

			&require_dbengine();
			
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($DOMAIN_TABLE);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","select * FROM $DOMAIN_TABLE");
			} else {
				$sth = $dbh->prepare ( "select * FROM $DOMAIN_TABLE");
				
				##execute the query
				$sth->execute( );

				## Retrieve the results of a row of data and print						 
				print "\tQuery results:\n================================================\n";

				while (@row = $sth->fetchrow_array( ) )  {
					print "@row\n";
	    	                 }		 
				warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish(); 			
			}

		}

		elsif ($cmd[1] =~ /^add$/) {
	
			if ( $#cmd lt 2 ) {
				print "Too few parameters\n!";
				&usage_domain();
				return;
			} elsif ( $#cmd gt 2 ) {
				print "Too many parameters\n";
				&usage_domain();
				return;
			}

			if ( &is_value_in_db($DOMAIN_TABLE, $domain_table{'DO_DOMAIN_COLUMN'},$cmd[2]) == 1 ) {
				print $cmd[2] . " is already in $DOMAIN_TABLE table\n";
				return;
			}	

			my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
			my $date = join("-",$year+1900,$mon+1,$mday+1);
			my $time = join(":",$hour,$min,$sec);
			my $last_modified =  join(" ",$date,$time);
			my $unix_time = time();

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				my $value = $last_modified;
				&bdb_insert($DOMAIN_TABLE,$key,$value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","INSERT INTO $DOMAIN_TABLE 
					       ($domain_table{'DO_DOMAIN_COLUMN'}, $domain_table{'DO_LAST_MODIFIED_COLUMN'}) 
					       VALUES (\'$cmd[2]\',$unix_time) ");
			} else {
				$sth = $dbh->prepare( "INSERT INTO $DOMAIN_TABLE 
						       ($domain_table{'DO_DOMAIN_COLUMN'}, $domain_table{'DO_LAST_MODIFIED_COLUMN'}) 
						       VALUES (\'$cmd[2]\',\'$last_modified\') " );
	
				#execute the query
				$sth->execute( );

				warn "Introducing the new user " . $cmd[1] . " to the database failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
		}
		
		elsif ($cmd[1] =~ /^rm$/) {	
			&require_dbengine();
			if ( $#cmd lt 2 ) {
				print "Too few parameters!\n";
				&usage_domain();
				return;
			} elsif ( $#cmd gt 2 ) {
				print "Too ma	ny parameters!\n";
				&usage_domain();
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				&bdb_delete($DOMAIN_TABLE,$cmd[2]);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","DELETE FROM $DOMAIN_TABLE 
   					      WHERE domain=\'$cmd[2]\'" );
			} else {
				$sth = $dbh->prepare("DELETE FROM $DOMAIN_TABLE 
	   					      WHERE domain=\'$cmd[2]\'" );
				#execute the query
				$sth->execute( );			
				warn "Deleting domain " . $cmd[2] . " in table $DOMAIN_TABLE failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
			
			print "execute 'domain reload' to synchronize cache and database\n";

		}
	
		elsif ($cmd[1] =~ /^h$/) {
			&usage_domain();
		}

		else {

			print "domain -unknown command $cmd[1]\n";

		}


}



#
##### ------------------------------------------------ #####
### address 
#
sub address() {

	my ($GRP, $IP, $MASK, $PORT, $PROTO, $PATTERN, $CONTEXT_INFO, $columns, $query);

	if ( $#cmd lt 1 ) {
		print "Too few parameters!\n";
		&usage_address();
		return;
	}	
	
	if ( $#cmd gt 8 ) {
		print "Too many parameters!\n";
		&usage_address();
		return;
	}	

		if ($cmd[1] =~ /^reload$/) {

			&require_ctlengine();
			&mi_comm('address_reload');

		}
		
		elsif ($cmd[1] =~ /^dump$/) {
		
			&require_ctlengine();
			&mi_comm('address_dump');

		}	

		elsif ($cmd[1] =~ /^show$/) {
		
			&require_dbengine();

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($ADDRESS_TABLE);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","SELECT * FROM $ADDRESS_TABLE");
			} else {
				$sth = $dbh->prepare ( "SELECT * FROM $ADDRESS_TABLE");	
				
				##execute the query
				$sth->execute( );

				## Retrieve the results of a row of data and print						 
				print "\tQuery results:\n================================================\n";

				while (@row = $sth->fetchrow_array( ) )  {
					print "@row\n";
	    	                 }		 
				warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish(); 
			}			
					
	
		}	

		elsif ($cmd[1] =~ /^add$/) {
			

			&require_dbengine();

			if ( $#cmd lt 6 ) {
				print "Too few parameters!\n";
				&usage_address();
				return;
			}

			$GRP = $cmd[2];
 			$columns = " $address_table{'ADDRESS_GRP_COLUMN'}, ";
			$query = "$GRP, ";

			$IP = $cmd[3];
			$MASK = $cmd[4];

			$columns .= "$address_table{'ADDRESS_IP_COLUMN'}, $address_table{'ADDRESS_MASK_COLUMN'}, "; 
                        $query .= "\'$IP\', \'$MASK\', ";

			if ( $cmd[6] !~ /^any$|^ANY$|^UDP$|^udp$|^tcp$|^TCP$|^tls$|^TLS$|^sctp$|^SCTP$|^NONE$|^none$/ ) {
				print "unknown protocol\n";
				return;
			}

			if ( (&is_value_in_db( $ADDRESS_TABLE, $address_table{'ADDRESS_IP_COLUMN'}, $IP ) != 0) ) {
				print "Record having $IP is already in $ADDRESS_TABLE table\n";
				return;
			}

	
			$PORT = $cmd[5];
			$PROTO = $cmd[6];

                        $columns .= "$address_table{'ADDRESS_PORT_COLUMN'}, $address_table{'ADDRESS_PROTO_COLUMN'} "; 
                        $query .= "$PORT, \'$PROTO\' ";
			my $value = join(" ", $MASK,$PORT,$PROTO);	

			if ( $cmd[6] ) {
				$CONTEXT_INFO = $cmd[7];
				$columns .= ", " . "$address_table{'ADDRESS_CONTEXT_INFO_COLUMN'}";
				$query .= ", " . "\'$CONTEXT_INFO\'";
				$value = join(" ",$CONTEXT_INFO);
				if ( $cmd[7] ) {
					$PATTERN = $cmd[8];
	                                $columns .= ", " . "$address_table{'ADDRESS_PATTERN_COLUMN'}";
        	                        $query .= ", " . "\'$PATTERN\'";
					$value = join(" ",$PATTERN);
				}						
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ", $GRP, $IP);
				&bdb_insert($ADDRESS_TABLE, $key, $value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD", " INSERT INTO $ADDRESS_TABLE ( $columns ) VALUES ( $query ) " ) ;
			} else {	
				$sth = $dbh->prepare( " INSERT INTO $ADDRESS_TABLE ($columns ) VALUES ( $query )" );
				#execute the query
				$sth->execute( );
				warn "Introducing the address rule $cmd[2] $IP $MASK $PORT $PROTO to the database failed \n", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			
				print "\nExecute 'address reload' to synchronize cache and database\n";
			}
		}

		elsif ($cmd[1] =~ /^rm$/) {
			&require_dbengine();
			if ( $#cmd ne 5 ) {
				&usage_address();
				return;
			}
			$GRP = $cmd[2];
			$IP = $cmd[3];
			$MASK = $cmd[4];
			$PORT = $cmd[5];

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ", $cmd[2], $IP);
				&bdb_delete($ADDRESS_TABLE,$key);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD", "DELETE FROM $ADDRESS_TABLE 
					      WHERE $address_table{'ADDRESS_GRP_COLUMN'} = $GRP AND 
	                             	            $address_table{'ADDRESS_IP_COLUMN'} = \'$IP\' AND 
						    $address_table{'ADDRESS_MASK_COLUMN'} = \'$MASK\' AND 
						    $address_table{'ADDRESS_PORT_COLUMN'} = $PORT" );
			} else {			
				$sth = $dbh->prepare("DELETE FROM $ADDRESS_TABLE 
						      WHERE $address_table{'ADDRESS_GRP_COLUMN'} = $GRP AND 
                                                            $address_table{'ADDRESS_IP_COLUMN'} = \'$IP\' AND 
                                                            $address_table{'ADDRESS_MASK_COLUMN'} = \'$MASK\' AND 
                                                            $address_table{'ADDRESS_PORT_COLUMN'} = $PORT" );
				#execute the query
				$sth->execute( );			
				warn "Deleting address rule: $GRP $IP $MASK $PORT  in table $DOMAIN_TABLE failed \n", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			print "\nExecute 'address reload' to synchronize cache and database\n";
	
		}

		elsif ($cmd[1] =~ /^h$/) {
		
			&usage_address();
			
		}

		else {

			print "address -unknown command $cmd[1]\n";

		}

}


#
##### ------------------------------------------------ #####
### DISPATCHER management
#
sub opensips_dispatcher() {
	if ( $#cmd lt 1 ) {
		print "Too few parameters!\n";
		&usage_dispatcher();
		return;
	} elsif ( $#cmd gt 5 ) {
		print "Too many parameters!\n";
		&usage_dispatcher();
		return;
	}	
	&require_dbengine();
	&require_ctlengine();
	
		if ($cmd[1] =~ /^show$/) {
	
			print "dispatcher gateways\n";
			
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($DISPATCHER_TABLE);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD", "SELECT * 
						FROM $DISPATCHER_TABLE 
						ORDER BY $dispatcher_table{'DISPATCHER_SETID_COLUMN'}" );
			} else {

				$sth = $dbh->prepare ( "SELECT * 
							FROM $DISPATCHER_TABLE 
							ORDER BY $dispatcher_table{'DISPATCHER_SETID_COLUMN'}");	
				
				##execute the query
				$sth->execute( );

				## Retrieve the results of a row of data and print						 
				print "\tQuery results:\n================================================\n";

				while (@row = $sth->fetchrow_array( ) )  {
					print "@row\n";
	    	                 }		 
				warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish(); 		
			}

		}	

		elsif ($cmd[1] =~ /^addgw$/) {

			my ( $DISPATCHER_DESCRIPTION, $DISPATCHER_SETID, $DISPATCHER_DESTINATION, $DISPATCHER_FLAGS);
	
			if ( $#cmd lt 5 ) {
				print "Too few parameters!\n";
				&usage_dispatcher();
				return;
			}

			if ( $#cmd gt 5 ) {
				print "Too many parameters!\n";
				&usage_dispatcher();
				return;
			}

			if ( $#cmd gt 3 ) {
				$DISPATCHER_DESCRIPTION = $cmd[5];
			} else {
				$DISPATCHER_DESCRIPTION = "";
			}

			$DISPATCHER_SETID = $cmd[2];
			$DISPATCHER_DESTINATION = $cmd[3];
			$DISPATCHER_FLAGS = $cmd[4];

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$DISPATCHER_SETID,$DISPATCHER_FLAGS);
				my $value = join(" ",$DISPATCHER_DESTINATION,$DISPATCHER_DESCRIPTION);
				&bdb_insert($DISPATCHER_TABLE,$key,$value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD"," INSERT INTO $DISPATCHER_TABLE ( $dispatcher_table{'DISPATCHER_SETID_COLUMN'}, 
						$dispatcher_table{'DISPATCHER_DESTINATION_COLUMN'}, $dispatcher_table{'DISPATCHER_FLAGS_COLUMN'},
 						$dispatcher_table{'DISPATCHER_DESCRIPTION_COLUMN'} ) 
						VALUES ($DISPATCHER_SETID,\'$DISPATCHER_DESTINATION\',$DISPATCHER_FLAGS,\'$DISPATCHER_DESCRIPTION\') ");
			} else {

				$sth = $dbh->prepare( " INSERT INTO $DISPATCHER_TABLE ( $dispatcher_table{'DISPATCHER_SETID_COLUMN'}, 
							$dispatcher_table{'DISPATCHER_DESTINATION_COLUMN'}, $dispatcher_table{'DISPATCHER_FLAGS_COLUMN'},
							$dispatcher_table{'DISPATCHER_DESCRIPTION_COLUMN'} ) 
							VALUES ($DISPATCHER_SETID,\'$DISPATCHER_DESTINATION\',$DISPATCHER_FLAGS,\'$DISPATCHER_DESCRIPTION\') " );

				#execute the query
				$sth->execute( );

				warn "Introducing the new user " . $cmd[1] . " to the database failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			&mi_comm('ds_reload');			

		}	

		elsif ($cmd[1] =~ /^rmgw$/) {
	
			if ( $#cmd lt 2 ) {
				print "missing gateway id to be removed\n";
				return;
			} elsif ( ( $#cmd lt 3 ) && ( $DBENGINE =~ /^DB_BERKELEY$/ ) ) {
				print "missing flag to be removed\n";
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$cmd[2],$cmd[3]);				
				&bdb_delete($DISPATCHER_TABLE,$key);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","DELETE FROM $DISPATCHER_TABLE 
					      WHERE $dispatcher_table{'DISPATCHER_SETID_COLUMN'}=\'$cmd[2]\'" );
			} else {
				$sth = $dbh->prepare("DELETE FROM $DISPATCHER_TABLE 
						      WHERE $dispatcher_table{'DISPATCHER_SETID_COLUMN'}=\'$cmd[2]\'" );
				#execute the query
				$sth->execute( );			
				warn "Deleting domain " . $cmd[2] . " in table $DOMAIN_TABLE failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			&mi_comm('ds_reload');				

		}	

		elsif ($cmd[1] =~ /^reload$/) {
	
			&mi_comm('ds_reload');						

		}	

		elsif ($cmd[1] =~ /^dump$/) {
	
			&mi_comm('ds_list');
		}	

		elsif ($cmd[1] =~ /^h$/) {
	
			&usage_dispatcher();

		}	

		else {

			print "dispatcher - unknown command $cmd[1]\n";

		}

}


#
##### ------------------------------------------------ #####
### CARRIERROUTE management
#
sub opensips_cr() {
	if ( $#cmd lt 1 ) {
		print "Too few parameters!\n";
		&usage_cr();
		return;
	} 

	&require_dbengine();
	&require_ctlengine();

		if ($cmd[1] =~ /^show$/) {

			print "cr routing tree\n";

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($ROUTE_TREE_TABLE);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","SELECT * 
						      FROM $ROUTE_TREE_TABLE 
						      ORDER BY $route_tree_table{'CARRIERROUTE_ROUTE_TREE_PREFIX_COLUMN'}" );
			} else {

				$sth = $dbh->prepare ( "SELECT * 
							FROM $ROUTE_TREE_TABLE 
							ORDER BY $route_tree_table{'CARRIERROUTE_ROUTE_TREE_PREFIX_COLUMN'}");	
				
				##execute the query
				$sth->execute( );

				## Retrieve the results of a row of data and print						 
				print "\tQuery results:\n================================================\n";

				while (@row = $sth->fetchrow_array( ) )  {
					print "@row\n";
	    	                 }		 
				warn "Could not retrieve data!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish(); 	
			}

			print "cr routes\n";
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($CARRIERROUTE_TABLE);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD"," SELECT * 
						 FROM $CARRIERROUTE_TABLE ORDER BY $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN'},
						 $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN'}, 
						 $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN'},
						 $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_PROB_COLUMN'} ");
			} else {
				$sth = $dbh->prepare ( " SELECT * 
							 FROM $CARRIERROUTE_TABLE ORDER BY $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN'},
							 $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN'}, 
							 $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN'},
							 $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_PROB_COLUMN'} ");	
				
				##execute the query
				$sth->execute( );

				## Retrieve the results of a row of data and print						 
				print "\tQuery results:\n================================================\n";

				while (@row = $sth->fetchrow_array( ) )  {
					print "@row\n";
	    	                 }		 
				warn "Could not retrieve data! Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish(); 	
			}
		}

		elsif ($cmd[1] =~ /^reload$/) {

			&mi_comm('cr_reload_routes');
		
		}

		elsif ($cmd[1] =~ /^dump$/) {

			&mi_comm('cr_dump');
		
		}

		elsif ($cmd[1] =~ /^addrt$/) {

			if ( $#cmd lt 3 ) {
				print "cr - missing route_tree\n";
				&usage_cr();
				return;
			}
	
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				my $value = $cmd[3];
				&bdb_insert($ROUTE_TREE_TABLE,$key,$value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD"," INSERT INTO $ROUTE_TREE_TABLE 
						( $route_tree_table{'CARRIERROUTE_ROUTE_TREE_PREFIX_COLUMN'}, 
						$route_tree_table{'CARRIERROUTE_ROUTE_TREE_CARRIER_COLUMN'}) 
						VALUES ($cmd[2], \'$cmd[3]\') ");
			} else {

				$sth = $dbh->prepare( " INSERT INTO $ROUTE_TREE_TABLE 
							( $route_tree_table{'CARRIERROUTE_ROUTE_TREE_PREFIX_COLUMN'}, 
							$route_tree_table{'CARRIERROUTE_ROUTE_TREE_CARRIER_COLUMN'}) 
							VALUES ($cmd[2], \'$cmd[3]\') " );
	
				#execute the query
				$sth->execute( );

				warn "Introducing the new route tree prefix and route tree carrier to the database failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			print "execute 'cr reload' to synchronize cache and database\n";

		
		}

		elsif ($cmd[1] =~ /^rmrt$/) {

			if ( $#cmd lt 2 ) {
				print "cr - missing route_tree to be removed\n";
				&usage_cr();
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				my $value = $cmd[1];
				&bdb_delete($ROUTE_TREE_TABLE,$key,$value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","DELETE FROM $ROUTE_TREE_TABLE 
					      WHERE $route_tree_table{'CARRIERROUTE_ROUTE_TREE_CARRIER_COLUMN'}=\'$cmd[2]\'" );
			} else {
				$sth = $dbh->prepare("DELETE FROM $ROUTE_TREE_TABLE 
						      WHERE $route_tree_table{'CARRIERROUTE_ROUTE_TREE_CARRIER_COLUMN'}=\'$cmd[2]\'" );
				#execute the query
				$sth->execute( );			
				warn "Deleting route tree " . $cmd[2] . " in table $ROUTE_TREE_TABLE failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
			print "execute 'cr reload' to synchronize cache and database\n";
		
		}

		elsif ($cmd[1] =~ /^addcarrier$/) {

			my ($CARRIER, $SCAN_PREFIX, $DOMAIN, $REWRITE_HOST, $PROB, $STRIP, $REWRITE_PREFIX, $REWRITE_SUFFIX, $COMMENT, $FLAGS, $MASK);

			if ( $#cmd lt 5 ) {
				print "cr - too few parameters\n";
				&usage_cr();
				return;
			}
		

			if ( $#cmd gt 5 ) {
				$PROB = $cmd[6];
				if ( $#cmd gt 6 ) {
					$STRIP = $cmd[7];
					if ( $#cmd gt 7 ) {
						$REWRITE_PREFIX = $cmd[8];
						if ( $#cmd gt 8 ) {
							$REWRITE_SUFFIX = $cmd[9];
							if ( $#cmd gt 9 ) {
								$COMMENT = $cmd[10];
								if ( $#cmd gt 10 ) {
									$FLAGS = $cmd[11];
									if ( $#cmd gt 11 ) {
										$MASK = $cmd[12];
									} else {
										$MASK = 0;
									}
								} else {
									$FLAGS = 0;
									$MASK = 0;
								}
							} else {
								$COMMENT = 'NULL';
								$FLAGS = 0;
								$MASK = 0;
							}
						} else {
							$REWRITE_SUFFIX = 'NULL';
							$COMMENT = 'NULL';
							$FLAGS = 0;
							$MASK = 0;
						}
					} else {
						$REWRITE_PREFIX = 'NULL';
						$REWRITE_SUFFIX = 'NULL';
						$COMMENT = 'NULL';
						$FLAGS = 0;
						$MASK = 0;
					}
				} else {
					$STRIP = 0;
					$REWRITE_PREFIX = 'NULL';
					$REWRITE_SUFFIX = 'NULL';
					$COMMENT = 'NULL';
					$FLAGS = 0;
					$MASK = 0;

				}
			} else {		
				$PROB = 0;
				$STRIP = 0;
				$REWRITE_PREFIX = 'NULL';
				$REWRITE_SUFFIX = 'NULL';
				$COMMENT = 'NULL';
				$FLAGS = 0;
				$MASK = 0;

			}		


			$CARRIER = $cmd[2];
			$SCAN_PREFIX = $cmd[3];
			$DOMAIN = $cmd[4];
			$REWRITE_HOST = $cmd[5];
			
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $CARRIER;
				my $value = join(" ",$DOMAIN,$SCAN_PREFIX,$FLAGS,$MASK,$PROB,$STRIP,$REWRITE_HOST,$REWRITE_PREFIX,$REWRITE_SUFFIX,$COMMENT);
				&bdb_insert($CARRIERROUTE_TABLE,$key,$value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD"," INSERT INTO $CARRIERROUTE_TABLE ( $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_PROB_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_STRIP_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_REWRITE_HOST_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_REWRITE_PREFIX_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_REWRITE_SUFFIX_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_COMMENT_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_FLAGS_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_MASK_COLUMN'}) VALUES ($CARRIER, \'$SCAN_PREFIX\', \'$DOMAIN\', $PROB, $STRIP, \'$REWRITE_HOST\', \'$REWRITE_PREFIX\', \'$REWRITE_SUFFIX\', \'$COMMENT\', $FLAGS, $MASK)");
			} else {
				$sth = $dbh->prepare( " INSERT INTO $CARRIERROUTE_TABLE ( $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_PROB_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_STRIP_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_REWRITE_HOST_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_REWRITE_PREFIX_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_REWRITE_SUFFIX_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_COMMENT_COLUMN'},$carrierroute_table{'CARRIERROUTE_CARRIERROUTE_FLAGS_COLUMN'}, $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_MASK_COLUMN'}) VALUES ($CARRIER, \'$SCAN_PREFIX\', \'$DOMAIN\', $PROB, $STRIP, \'$REWRITE_HOST\', \'$REWRITE_PREFIX\', \'$REWRITE_SUFFIX\', \'$COMMENT\', $FLAGS, $MASK)" );
	
				#execute the query
				$sth->execute( );

				warn "Introducing the new route tree prefix and route tree carrier to the database failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
			print "execute 'cr reload' to synchronize cache and database\n";

		
		}

		elsif ($cmd[1] =~ /^rmcarrier$/) {

			if ( $#cmd lt 4 ) {
				print "cr - too few parameters\n";
				&usage_cr();
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				&bdb_delete($CARRIERROUTE_TABLE,$key);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD", "DELETE FROM $CARRIERROUTE_TABLE 
					      WHERE $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN'}=\'$cmd[2]\' 
					      AND $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN'}=\'$cmd[3]\' 
					      AND $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN'}=\'$cmd[4]\' ");
			} else {
				$sth = $dbh->prepare("DELETE FROM $CARRIERROUTE_TABLE 
						      WHERE $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_CARRIER_COLUMN'}=\'$cmd[2]\' 
						      AND $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_SCAN_PREFIX_COLUMN'}=\'$cmd[3]\' 
						      AND $carrierroute_table{'CARRIERROUTE_CARRIERROUTE_DOMAIN_COLUMN'}=\'$cmd[4]\' " );
				#execute the query
				$sth->execute( );			
				warn "Deleting carrier " . $cmd[2] . " in table $CARRIERROUTE_TABLE failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			print "execute 'cr reload' to synchronize cache and database\n";

		}

		elsif ($cmd[1] =~ /^h$/) {
	
			&usage_cr();			

		}

		else {

			print "cr - unknown command $cmd[1]\n";

		}

}

#
##### ------------------------------------------------ #####
### alias management
#




#check alias
sub check_alias(){
	$OSIPSUSER = $_[0];
	$OSIPSDOMAIN = $_[1];
	$ALIAS_EXISTS = 0;
	&mi_comm( join(" ","ul_show_contact", $ALS_TABLE, $OSIPSUSER."\@".$OSIPSDOMAIN) );
	
	if ( $ENABLE_ALIASES == 1 ) {
		&check_ul_alias($OSIPSUSER,$OSIPSDOMAIN);
		if ( $ALIAS_UL_EXISTS == 0 ) {
			$ALIAS_EXISTS = 0;
		} else {
			$ALIAS_EXISTS = 1;
		}
	}
	if  ( $ENABLE_ALIASES == 2 ) {
		&check_db_alias();
		if ( $ALIAS_DB_EXISTS == 0 ) {
			$ALIAS_EXISTS = 0;
		} else {
			$ALIAS_EXISTS = 1;
		}
	}

}


sub check_db_alias() {
	&require_dbengine();

	$ALIAS_DB_EXISTS = 0;

	if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
		$result = &bdb_select_where($DA_TABLE,"$OSIPSUSER","$OSIPSDOMAIN");
	} elsif ( $DBENGINE =~ /^DBTEXT$/) {
		my $res = system("$DBTEXTCMD","SELECT count(*) 
			       FROM $DA_TABLE 
			       WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
                               AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
		$result = $res;
	} else {
	 	$sth = $dbh->prepare( "SELECT count(*) 
				       FROM $DA_TABLE 
				       WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
		                       AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'" );
	
		#execute the query
		$sth->execute( );

		warn "Retrieving user " . $cmd[1] . " from $DA_TABLE failed", $sth->errstr( ), "\n" if $sth->err( );
			

		#Retrieve the results of a row of data and print
		print "\tQuery results:\n================================================\n";
		while ( @row = $sth->fetchrow_array( ) )  {
	 		     print "@row\n";
			     $result = "@row";	
		}
		$sth->finish();
	}

	#print $result;
	if ( $result == 0 ) {
		$ALIAS_DB_EXISTS = 0;
	} else {
		$ALIAS_DB_EXISTS = 1;
	}

}



#xxxxxxxxxxxxxxxxxxx
sub check_ul_alias() {
	&require_ctlengine();
	$ALIAS_UL_EXISTS = 0;
	my $temp = &mi_comm(join(" ","ul_show_contact", $ALS_TABLE, $_[0]."\@".$_[1]));

	if  ( $temp !~ /^404/ ) {
		if ( $temp =~ /^400/ ) {
			print "400; check if you use aliases in OpenSIPS\n";
			return;
		}

		if ( $temp =~ /^200/ ) {
			$ALIAS_UL_EXISTS = 1;
		}
		return;
	}
}



#
##### ------------------------------------------------ #####
### db_alias 
#
sub alias_db() {
	
	if ( $#cmd lt 1 ) {
		print "Too few parameters!";
		&usage_alias_db();
		return;
	}

	&require_dbengine();



		if ($cmd[1] =~ /(^h$)|(^help$)/) {
			&usage_alias_db();
			return;
		}
		elsif ($cmd[1] =~ /^list$/) {
			if ( $#cmd eq 2 ) {
				# print aliases for user				
				if ( &check_aor ($cmd[2]) ne 0 ) {
					print "alias_db - <" .$cmd[2]."> is not a valid AoR (user\@domain)\n";
					return;
				}
				
				&set_user($cmd[2]);
				
				print "Dumping aliases for user=<".$cmd[2].">\n";
			
				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
					&bdb_select_where($DA_TABLE,$key);
				} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
					system("$DBTEXTCMD","SELECT CONCAT($da_table{'DA_ALIAS_USER_COLUMN'},\'@\',$da_table{'DA_ALIAS_DOMAIN_COLUMN'})
							     ALIAS FROM $DA_TABLE 
							     WHERE $da_table{'DA_USER_COLUMN'}=\'$OSIPSUSER\' 
 							     AND $da_table{'DA_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'")
				} elsif ($DBENGINE =~ /^Pg$/ ) {
					$sth = $dbh->prepare( "SELECT ($da_table{'DA_ALIAS_USER_COLUMN'} || \'@\' || $da_table{'DA_ALIAS_DOMAIN_COLUMN'}) ALIAS 
	 						       FROM $DA_TABLE 
							       WHERE $da_table{'DA_USER_COLUMN'}=\'$OSIPSUSER\' 
	 						       AND $da_table{'DA_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'" );
					#execute the query
					$sth->execute( );
					warn "Concatenting user with domain failed", $sth->errstr( ), "\n" if $sth->err( );

					#Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";
					while ( @row = $sth->fetchrow_array( ) )  {
				 		     print "@row\n";
					}	
					$sth->finish();
				} else {
					$sth = $dbh->prepare( "SELECT CONCAT($da_table{'DA_ALIAS_USER_COLUMN'},\'@\',$da_table{'DA_ALIAS_DOMAIN_COLUMN'}) ALIAS 
	 						       FROM $DA_TABLE 
							       WHERE $da_table{'DA_USER_COLUMN'}=\'$OSIPSUSER\' 
	 						       AND $da_table{'DA_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'" );
	
					#execute the query
					$sth->execute( );
					warn "Concatenting user with domain failed", $sth->errstr( ), "\n" if $sth->err( );

					#Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";
					while ( @row = $sth->fetchrow_array( ) )  {
				 		     print "@row\n";
					}	
					$sth->finish();
				}
			
			} elsif ( $#cmd eq 1 ) {
				print "Dumping all aliases may take long: do you want to proceed? [Y|N]:";
				if ( ( my $input = <STDIN>) =~ /[y|Y]/ ) { 
							print "Dumping all aliases...\n";
						    } else {
							return;
				} 
				
				#preparing query
				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					&bdb_select($DA_TABLE);
				} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
					system("$DBTEXTCMD", "SELECT $da_table{'DA_ALIAS_USER_COLUMN'}, $da_table{'DA_ALIAS_DOMAIN_COLUMN'},
						       $da_table{'DA_USER_COLUMN'}, $da_table{'DA_DOMAIN_COLUMN'} 
						       FROM $DA_TABLE" );
				} else {
	 				$sth = $dbh->prepare( "SELECT $da_table{'DA_ALIAS_USER_COLUMN'}, $da_table{'DA_ALIAS_DOMAIN_COLUMN'},
							       $da_table{'DA_USER_COLUMN'}, $da_table{'DA_DOMAIN_COLUMN'} 
							       FROM $DA_TABLE" );
	
					#execute the query
					$sth->execute( );
					warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );
	
					#Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";
					while ( @row = $sth->fetchrow_array( ) )  {
				 		     print "@row\n";
					}	
					$sth->finish();
				}
		
			} else {
				print "alias_db - wrong number of params for command [list]\n";
				&usage_alias_db();
				return;
			}

		}

		elsif ($cmd[1] =~ /^show$/) {
			if ( $#cmd ne 2 ) {
				print "alias_db - wrong number of params for command [show]\n";
				&usage_alias_db();
				return;
			}			
			
			if ( &check_aor ($cmd[2]) ne 0 ) {
				print "alias_db -". $cmd[2]." is not a valid AoR (user\@domain)\n";
				return;
			}
			
			&set_user($cmd[2]);

			#prepare query	
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				&bdb_select_where($DA_TABLE,$key);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
				system("$DBTEXTCMD", " SELECT CONCAT($da_table{'DA_USER_COLUMN'},\'@\',$da_table{'DA_DOMAIN_COLUMN'} ) AS SIP_ID 
						       FROM $DA_TABLE
   						       WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
 						       AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' ");
			} elsif ( $DBENGINE =~ /^Pg$/ ) {
				$sth = $dbh->prepare( " SELECT ($da_table{'DA_USER_COLUMN'} || \'@\' || $da_table{'DA_DOMAIN_COLUMN'} ) AS SIP_ID 
							FROM $DA_TABLE 				
							WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
							AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' " );
	
				#execute the query
				$sth->execute( );
				warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );			

				#Retrieve the results of a row of data and print
				print "\tQuery results:\n================================================\n";
				while ( @row = $sth->fetchrow_array( ) )  {
					     print "@row\n";
				}	
			} else {
				$sth = $dbh->prepare( " SELECT CONCAT($da_table{'DA_USER_COLUMN'},\'@\',$da_table{'DA_DOMAIN_COLUMN'} ) AS SIP_ID 
							FROM $DA_TABLE 				
							WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
							AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\' " );
	
				#execute the query
				$sth->execute( );
				warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );			

				#Retrieve the results of a row of data and print
				print "\tQuery results:\n================================================\n";
				while ( @row = $sth->fetchrow_array( ) )  {
					     print "@row\n";
				}	
				$sth->finish();
			}

		}

		elsif ($cmd[1] =~ /^add$/) {
			if ( $#cmd ne 3 ) {
				&usage_alias_db();
				return;
			}
			
			if ( &check_aor($cmd[2]) != 0 ) {
				print "alias_db - " . $cmd[1] ." is not a valid AoR (user\@domain)\n";
				return;
			}

			
			if ( &check_aor($cmd[3]) != 0 ) {
				print "alias_db - " . $cmd[2] ." is not a valid AoR (user\@domain)\n";
				return;
			}
			
			&set_user($cmd[2]);
			my $TMP_OSIPSUSER = $OSIPSUSER;
			my $TMP_OSIPSDOMAIN = $OSIPSDOMAIN;
			&set_user($cmd[3]);
			
			if ( &is_value_in_db( $DA_TABLE, $da_table{'DA_ALIAS_USER_COLUMN'}, $TMP_OSIPSUSER ) != 0 ) {
				print "$TMP_OSIPSUSER alias already in $DA_TABLE table\n";
				return;
			}


			#prepare query
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
				my $value = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				&bdb_insert($DA_TABLE,$key,$value);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
				system("$DBTEXTCMD"," INSERT INTO $DA_TABLE 
						($da_table{'DA_USER_COLUMN'},$da_table{'DA_DOMAIN_COLUMN'},$da_table{'DA_ALIAS_USER_COLUMN'},
						$da_table{'DA_ALIAS_DOMAIN_COLUMN'}) 
						VALUES (\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$TMP_OSIPSUSER\',\'$TMP_OSIPSDOMAIN\' )");
			} else {
				$sth = $dbh->prepare( " INSERT INTO $DA_TABLE 
							($da_table{'DA_USER_COLUMN'},$da_table{'DA_DOMAIN_COLUMN'},$da_table{'DA_ALIAS_USER_COLUMN'},
							$da_table{'DA_ALIAS_DOMAIN_COLUMN'}) 
							VALUES (\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$TMP_OSIPSUSER\',\'$TMP_OSIPSDOMAIN\' )" );
	
				#execute the query
				$sth->execute();
				warn "Entry could not be inserted into table", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();

			}				
		}
		
		elsif ($cmd[1] =~ /^rm$/) {
			if ( $#cmd != 2 ) {
				print "alias_db - wrong numbers of parameters\n";
				&usage_alias_db();
				return;
			}
			
						
			if ( &check_aor($cmd[2]) != 0 ) {
				print "alias_db - $cmd[1] is not a valid URI\n";
				return;
			}

			&set_user ( $cmd[2] );

			#prepare query
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				&bdb_delete($DA_TABLE,$key);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
				system("$DBTEXTCMD", " DELETE FROM $DA_TABLE 
						WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
						AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'" );
			} else {
				$sth = $dbh->prepare( " DELETE FROM $DA_TABLE 
							WHERE $da_table{'DA_ALIAS_USER_COLUMN'}=\'$OSIPSUSER\' 
							AND $da_table{'DA_ALIAS_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'" );
	
				#execute the query
				$sth->execute( );
				warn "Deleting entry failed!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
		
		}	

		else {

			print "alias_db -unknown command $cmd[1]\n";

		}		
		
} # end db-aliases



#####------------------------------------------------#####
### mi_comm
#

sub mi_comm(){
	my $argument = $_[0]; 
	if ( ($#_ + 1) gt 0) {
			if ($CTLENGINE =~ /^FIFO$/) {
			
				&fifo_cmd($argument);	

			}

			elsif ($CTLENGINE =~ /^UNIXSOCK$/) {
	
				&unixsock_cmd($argument);

			}

			elsif ($CTLENGINE =~ /^UDP$/) {
	
				&udp_cmd($argument);

			}

			elsif ($CTLENGINE =~ /^XMLRPC$/) {
	
				&xmlrpc_cmd($argument);

			}
		
	} else {
			if ($cmd[0] =~ /^fifo$/) {
			
				&fifo_cmd();	
				

			}

			elsif ($cmd[0] =~ /^unixsock$/) {
	
				&unixsock_cmd();

			}

			elsif ($cmd[0] =~ /^udp$/) {
	
				&udp_cmd();

			}

			elsif ($cmd[0] =~ /^xmlrpc/) {
	
				&xmlrpc_cmd();

			}
		
	}
	
}


sub mi_comm_monitor(){
		if ( $CTLENGINE =~ /^FIFO$/) {
			
			&fifo_opensips_monitor();	

		}

		elsif ( $CTLENGINE =~ /^UNIXSOCK$/) {
	
			&unixsock_opensips_monitor();

		}

		elsif ( $CTLENGINE =~ /^UDP$/) {
	
			&udp_opensips_monitor();

		}

		elsif ( $CTLENGINE =~ /^XMLRPC$/) {
			my $content = "";
			&xmlrpc_opensips_monitor();

		}
}

##### ----------------------------------------------- #####
### FIFO specific variables and functions
#
sub fifo_cmd() {

	my @fifo_cmd;
	
	if ($cmd[0] =~ /^fifo$/ ) {

		if ( $#cmd == 0 ) {
			print "Too few parameters! fifo must take at least the command name as parameter\n";
			&usage_fifo();
			return;
		}
 	 	if ($cmd[1] !~ /arg|debug|kill|list_blacklist|ps|pwd|uptime|version|which|get_statistics|reset_statistics|t_uac_dlg|t_uac_cancel|t_hash|t_reply|ul_rm|ul_rm_contact|ul_dump|ul_flush|ul_add|ul_show_contact/) {
			print "Fifo command is not valid. Should be in list: [arg|debug|kill|list_blacklist|ps|pwd|uptime|version|which|get_statistics|reset_statistics]\n";
			return;
		}		
		@fifo_cmd = @cmd;
		shift(@fifo_cmd);	
		
	}
	
	if ($_[0]) {	

		@fifo_cmd = split(" ",$_[0]);		
	}	


		
	
	my $arg_list = "";
	
	if ( $#fifo_cmd == 0){

		$cmd_fifo = ":" . $fifo_cmd[0] . ":" . $fifo_reply_file . "\n";
	
	} elsif ( $#fifo_cmd  gt 0 ){

		for (my $i = 1; $i < $#fifo_cmd +1; $i++) {

			$arg_list = join("",$arg_list,$fifo_cmd[$i],"\n");


		}

		$cmd_fifo = ":" . $fifo_cmd[0] . ":" . $fifo_reply_file . "\n" . $arg_list;

	}
	$cmd_fifo = join("",$cmd_fifo,"\n");

	&write_read_fifo($OSIPS_FIFO,$fifo_reply_path,$cmd_fifo);

}


#monitor
sub fifo_opensips_monitor() {
	
		
		$cmd_fifo = ":version:$fifo_reply_file\n\n";
		print $cmd_fifo."\n";
		&write_read_fifo($OSIPS_FIFO,$fifo_reply_path,$cmd_fifo);
	
		$cmd_fifo = ":uptime:$fifo_reply_file\n\n";
		print $cmd_fifo."\n";
		&write_read_fifo($OSIPS_FIFO,$fifo_reply_path,$cmd_fifo);
		
		print "Transaction Statistics: \n";
		&write_read_fifo($OSIPS_FIFO,$fifo_reply_path,":get_statistics:$fifo_reply_file\nUAS_transactions\nUAC_transactions\ninuse_transactions\n\n");
		
		print "Stateless Server Statistics: \n";
		&write_read_fifo($OSIPS_FIFO,$fifo_reply_path,":get_statistics:$fifo_reply_file\nsent_replies\nsent_err_replies\nreceived_ACKs\n\n");
		
		print "UsrLoc Stats: \n";
		&write_read_fifo($OSIPS_FIFO,$fifo_reply_path,":get_statistics:$fifo_reply_file\nusrloc:\n\n");

		return;
}


sub write_read_fifo() {

	$OSIPS_FIFO = $_[0];
	$fifo_reply_path = $_[1];
	$cmd_fifo = $_[2];
	
	if (!-e $OSIPS_FIFO){

		print "File $OSIPS_FIFO does not exist!";

		return;

	}

	if ( !-w $OSIPS_FIFO ) {

		print "Error opening OpenSIPS's FIFO " . $OSIPS_FIFO . "\n" . 
		      "Make sure you have the line 'modparam(\"mi_fifo\", \"fifo_name\", \" " . $OSIPS_FIFO . "\")' in your config\n" .
		      "and also have loaded the mi_fifo module.\n";

		return;	

	}

	 unless ( -p $fifo_reply_path ) {

		    unlink $fifo_reply_path;

		    mkfifo ($fifo_reply_path,666) or die "mkfifo $fifo_reply_path failed: $!"; 

	 }
	
	`chmod a+rw $fifo_reply_path`;

	open(ANS,">$OSIPS_FIFO") or die "Could not open $OSIPS_FIFO for writing: $!\n";

	print ANS $cmd_fifo;
	
	close (ANS);
	
	open(FIFO, "< $fifo_reply_path") or die "Couldn't open $fifo_reply_path for reading: $!\n";
	
	my @fifo_line = <FIFO>;

	shift(@fifo_line);
		
	close (FIFO);
	
	unlink $fifo_reply_path;
		
	if ( ( $cmd[0] =~ /^fifo$/ ) && ($fifo_line[0] =~ /^\d+/) ) {
		shift(@fifo_line);	
		print "@fifo_line";	
	} elsif ( $cmd[0] =~ /^fifo$/ ) {
		print "@fifo_line";
	} else {
		print "@fifo_line";
	}
		

}


##### ----------------------------------------------- #####
### UNIXSOCK specific variables and functions
#
sub unixsock_cmd()
{	
my ($CMD,@unix_cmd,@var);
my $arg_list="";
	if ($cmd[0] eq "unixsock") {
		if ( $#cmd lt 1 ) {
			print "unixsock must take at least the command name as parameter\n";
			return;
		}
		shift(@cmd);
		@unix_cmd = @cmd;
	}

	if ($_[0]) {	

		@unix_cmd = split(" ",$_[0]);		

	}	


	# construct the command now

	if ( $#unix_cmd == 0){

		$CMD = ":" . $unix_cmd[0] . ":\n";
	
	} elsif ( $#unix_cmd  gt 0 ){

		for (my $i = 1; $i < $#unix_cmd +1; $i++) {

			$arg_list = join("",$arg_list,$unix_cmd[$i],":\n");


		}

		$CMD = ":" . $unix_cmd[0] . ":\n" . $arg_list;

	}
	@var =  `printf "$CMD" | $OSIPSUNIX $OSIPS_UNIXSOCK`;
	if ( $var[0] =~ /^200 OK$/) {
		shift(@var);
	}

	print "@var";
	

	print "\nUNIXSOCK command was:\n$CMD";
}


sub unixsock_opensips_monitor() {
			#clear
			#tput cup 0 0

	# print_stats $attempt
	#print "[cycle #: $attempt; if constant make sure server lives]\n";
	&unixsock_cmd("version");
	&unixsock_cmd("uptime");

	print "\nTransaction Statistics\n";
	&unixsock_cmd(join(" ","get_statistics","UAS_transactions"));
	&unixsock_cmd(join(" ","get_statistics","UAC_transactions"));
	&unixsock_cmd(join(" ","get_statistics","inuse_transactions"));

	print "\nStateless Server Statistics\n";
	&unixsock_cmd(join(" ","get_statistics","sent_replies"));
	&unixsock_cmd(join(" ","get_statistics","sent_err_replies"));
	&unixsock_cmd(join(" ","get_statistics","received_ACKs"));

	print "\nUsrLoc Stats\n";
	&unixsock_cmd(join(" ","get_statistics","registered_users"));
	&unixsock_cmd(join(" ","get_statistics","location-users"));
	&unixsock_cmd(join(" ","get_statistics","location-contacts"));
	&unixsock_cmd(join(" ","get_statistics","location-expires"));


	return;

}

##### ----------------------------------------------- #####
### UDP specific variables and functions
#
sub udp_cmd()
{	
my ($CMD, @udp_cmd, @var, $portaddr, $msg, @recv_msg, $str, $MAXLEN);
my $arg_list="";
	if ($cmd[0] eq "udp") {
		if ( $#cmd lt 1 ) {
			print "upd_cmd must take at least the command name as parameter\n";
			return;
		}
		shift(@cmd);
		@udp_cmd = @cmd;
	}

	if ($_[0]) {	

		@udp_cmd = split(" ",$_[0]);	

	}	


	# construct the command now
	#$CMD = ":" . $unix_cmd[0] . ":\n";

	if ( $#udp_cmd == 0){

		$CMD = ":" . $udp_cmd[0] . ":\n";
	
	} elsif ( $#udp_cmd  gt 0 ){

		for (my $i = 1; $i < $#udp_cmd +1; $i++) {

			$arg_list = join("",$arg_list,$udp_cmd[$i],":\n");


		}

		$CMD = ":" . $udp_cmd[0] . ":\n" . $arg_list;

	}

	$MAXLEN = 2048;

	socket(SOCKET, PF_INET, SOCK_DGRAM, getprotobyname("udp")) 
	    or die "socket: $!";

	$portaddr = sockaddr_in($OSIPS_PORT, inet_aton("$OSIPSIP"));

	send(SOCKET, $CMD, 0, $portaddr) == length($CMD)
		or die "cannot send msg";

	$portaddr = recv(SOCKET, $msg, $MAXLEN, 0);
	
	@recv_msg = split("\n",$msg);
		
	if ($recv_msg[0] =~ /^\d+/)  {
		shift(@recv_msg);	
	} else {
		"@recv_msg";
	}

	for (my $i=0; $i<=$#recv_msg; $i++) {	
		print $recv_msg[$i]."\n";
	}
	
	close (SOCKET);

	print "\nUDP command was:\n$CMD";
}


sub udp_opensips_monitor() {

	&udp_cmd('version');
	&udp_cmd('uptime');

	print "\nTransaction Statistics\n";
	&udp_cmd(join(" ","get_statistics","UAS_transactions"));
	&udp_cmd(join(" ","get_statistics","UAC_transactions"));
	&udp_cmd(join(" ","get_statistics","inuse_transactions"));

	print "\nStateless Server Statistics\n";
	&udp_cmd(join(" ","get_statistics","sent_replies"));
	&udp_cmd(join(" ","get_statistics","sent_err_replies"));
	&udp_cmd(join(" ","get_statistics","received_ACKs"));

	print "\nUsrLoc Stats\n";
	&udp_cmd(join(" ","get_statistics","registered_users"));
	&udp_cmd(join(" ","get_statistics","location-users"));
	&udp_cmd(join(" ","get_statistics","location-contacts"));
	&udp_cmd(join(" ","get_statistics","location-expires"));

	return;


}


sub xml_do_call() {

	my $host = $_[0];
	my $port = $_[1];
	my $query = $_[2];
	my ($xml_query, $socket, $answer, $content);
	#create xmlrpc query 
	$xml_query = "POST /RPC2 HTTP/1.0\nUser_Agent: openser-cp\nHost: ".$host."\nContent-Type: text/xml\nContent-Length: ".length($query)."\n\n".$query."\n";

	$socket = new IO::Socket::INET ( PeerAddr => $host,
					 PeerPort => $port, 
					 Proto => 'tcp'); 

	die "Could not create socket: $!\n" unless $socket; 

	#print $xml_query to created socket; 
	print $socket $xml_query;
	
	# read the answer
	while ($answer = <$socket>) {	
	 $content .= $answer;
	}

	close($socket);

	return $content;

}


sub write2xmlrpc() {

	my $remote_host = $_[0];
	my $remote_port = $_[1];
	my $xml_cmd = $_[2];
	my @args = $_[3];
	my ($coder, $string, $server_response, $res, $xml_response);
	#create xmlrpc client
	$coder = Frontier::RPC2->new( 'encoding' => 'ISO-8859-1' );
	
	#encode command to xmlrpc format
	$string = $coder->encode_call($xml_cmd,@args);
	
	#obtain xmlrpc response
	$server_response = &xml_do_call($remote_host,$remote_port,$string);

	#derefferencing the xmlrpc response 
 	$xml_response = ${$coder->string($server_response)};

	#parsing the xmlrpc response
	if ( $xml_response =~ />Too few or too many arguments</ ) {
		$res = "Too few or too many arguments\n";
	} elsif ($xml_response =~ />Requested command (\D+) is not available!</) {
		$res = "Requested command is not available!\n";
	}else {
		$res = substr($xml_response,index($xml_response,"<param><value><string>")+22,index($xml_response,"</string></value></param>")-43-index($xml_response,"<param><value><string>")+21);		
	}
	#print $res;
	return $res;

}
	

sub xmlrpc_cmd() {

	my (@xmlrpc_cmd,$xml_command);

	if ($cmd[0] eq "xmlrpc") {
		if ( $#cmd lt 1 ) {
			print "upd_cmd must take at least the command name as parameter\n";
			return;
		}
		shift(@cmd);
		@xmlrpc_cmd = @cmd;
	}

	if ($_[0]) {	

		@xmlrpc_cmd = split(" ",$_[0]);		

	}
	
	$xml_command = shift(@xmlrpc_cmd);
	#print $xml_command." @xmlrpc_cmd\n";

	#result of xmlrpc query
	my $buf = &write2xmlrpc($OSIPSIP,$OSIPS_PORT,$xml_command,@xmlrpc_cmd);
	print $buf;
}	


sub xmlrpc_opensips_monitor() {
		
	# print_stats $attempt
	&xmlrpc_cmd('version');
	&xmlrpc_cmd('uptime');

	print "\nTransaction Statistics\n";
	&xmlrpc_cmd(join(" ","get_statistics","UAS_transactions"));
	&xmlrpc_cmd(join(" ","get_statistics","UAC_transactions"));
	&xmlrpc_cmd(join(" ","get_statistics","inuse_transactions"));

	print "\nStateless Server Statistics\n";
	&xmlrpc_cmd(join(" ","get_statistics","sent_replies"));
	&xmlrpc_cmd(join(" ","get_statistics","sent_err_replies"));
	&xmlrpc_cmd(join(" ","get_statistics","received_ACKs"));

	print "\nUsrLoc Stats\n";
	&xmlrpc_cmd(join(" ","get_statistics","registered_users"));
	&xmlrpc_cmd(join(" ","get_statistics","location-users"));
	&xmlrpc_cmd(join(" ","get_statistics","location-contacts"));
	&xmlrpc_cmd(join(" ","get_statistics","location-expires"));

	return;

}



#
##### ------------------------------------------------ #####
### tls_ca 
#
sub tls_ca() {
	my ( $CA_BASE, $CA_CONF, $CA_PATH );
	if ($#cmd lt 1) {
		print "Too few parameters!\n";
		&usage_tls();
		return;
	}
	
		if ($cmd[1] =~ /^rootCA$/) {
		

			if ( $#cmd == 1 ) {
				
				$CA_BASE = $ETCDIR . "/tls";		

			} else {

				$CA_BASE=`(cd $cmd[2];pwd)`;

			}			
			if ( ! -d $CA_BASE ) {
				print "Config directory ($CA_BASE) does not exist\n";
				return;
			}
		
			$CA_CONF='ca.conf';
			$CA_PATH=$CA_BASE."/rootCA";
	
			if ( ! -f $CA_BASE."/".$CA_CONF ) {
				print "root CA config file ($CA_BASE/$CA_CONF) does not exist\n";
				return;
			}

			if ( -d $CA_PATH ) {
				print "root CA directory ($CA_PATH) exists! Remove it (y/n)?";
				if ( (my $line = <STDIN>) !~ /y|Y/ ) {
					return;
				}
			}

			print "Creating directory $CA_PATH and its sub-tree\n";
			system("mkdir -p $CA_PATH");
			if ( $? != 0 ) {
				print "Failed to create root directory $CA_PATH\n";
				return;
			}
			`rm -fr $CA_PATH/*`;
			`mkdir $CA_PATH/private`;
			`mkdir $CA_PATH/certs`;
			`touch $CA_PATH/index.txt`;
			`echo 01 >$CA_PATH/serial`;

			print "Creating CA self-signed certificate\n";
			system(" cd $CA_PATH; openssl req -config $CA_BASE/$CA_CONF -x509 -newkey rsa:2048 -days 365 -out ./cacert.pem -outform PEM ");
			if ( $? != 0 ) {
				print "Failed to create self-signed certificate\n";
				return;
			}

			print "Protecting CA private key\n";
			system("chmod 600 $CA_PATH/private/cakey.pem");

			print "DONE\n";
			print "Private key can be found in $CA_PATH/private/cakey.pem\n";
			print "Certificate can be found in $CA_PATH/cacert.pem\n";


		}	

		elsif ($cmd[1] =~ /^userCERT$/) {

			if ( $#cmd lt 2 ) {
				print "Missing user name parameter\n";
				return;
			}

			if ( $#cmd lt 3 ) {
				# use default
				$CA_BASE = $ETCDIR."/tls";
			} else {
				$CA_BASE = `(cd $cmd[3];pwd)`;
			}

			if ( ! -d $CA_BASE ) {
				print "Config directory ($CA_BASE) does not exist\n";
				return;
			}

			my $USER_DIR = $CA_BASE."/".$cmd[2];
			my $USER_CFG = $CA_BASE."/".$cmd[2].".conf";
			my $USER = $cmd[2];
			my $REQ_CFG=$CA_BASE . "/request.conf";

			if ( ! -f $USER_CFG ) {
				print "User config file $USER_CFG not found\n";
				return;
			}

			if ( ! -f $REQ_CFG ) {
				print "Request config file $REQ_CFG not found\n";
				return;
			}

			print "Using config file $USER_CFG\n";

			if ( -d $USER_DIR ) {
				print "User CERT directory ($USER_DIR) exists! Remove it (y/n)?";
				if ( ( my $line = <STDIN>) =~ /y|Y/ ) {
					return;
				}
			}

			print "Creating directory $USER_DIR\n";
			system(mkdir -p $USER_DIR);
			if ( $? ne 0 ) {
				print "Failed to create user directory $USER_DIR\n";
				return;
			}
			`rm -fr $USER_DIR/*`;

			print "Creating user certificate request\n";
			`openssl req  -config $USER_CFG -out $USER_DIR/$USER-cert_req.pem -keyout $USER_DIR/$USER-privkey.pem -new -nodes`;
			if ( $? ne 0 ) {
				print "Failed to generate certificate request\n";
				return;
			}

			print "Signing certificate request\n";
			system(" cd $CA_BASE ; openssl ca -config $REQ_CFG -in $USER_DIR/$USER-cert_req.pem -out $USER_DIR/$USER-cert.pem ");
			if ( $? ne 0 ) {
				print  "Failed to generate certificate request\n";
				return;
			}

			print "Generating CA list\n";
			`cat $CA_BASE/rootCA/cacert.pem >> $USER_DIR/$USER-calist.pem`;

			print "DONE\n";
			print "Private key is locate at $USER_DIR/$USER-privkey.pem\n";
			print "Certificate is locate at $USER_DIR/$USER-cert.pem\n";
			print "CA-List is locate at $USER_DIR/$USER-calist.pem\n";

		}

		elsif ($cmd[1] =~ /^h$/) {

			&usage_tls();
		
		}

		else {

			print "tls -unknown command $cmd[1]\n";

		}
}



#
##### ------------------------------------------------ #####
### AVP management
#
# 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 (*)

sub avpops() {
	&require_dbengine();
	if ( $#cmd lt 1 ) {
		print "avp - too few parameters\n";
		&usage_avp();
		return;
	}	

	my $CLAUSE="";
	my $i;
	
		if ($cmd[1] =~ /^list$/) {
			$CLAUSE = "";
			if ($#cmd lt 3) {
				print "avp - too few parameters\n";
				&usage_avp();
				return;
			}	
			#shift(@cmd);					
			
			$i=2;
			while ( $i lt $#cmd  ){
					if ($cmd[$i] =~ /^-T$/) {												
						if ($cmd[1] =~ /(\s+)|-[Tuavt]/ ) {
							print "table name missing\n";							
							return;
						} else {
							$AVP_TABLE = $cmd[$i+1];						
						}					
					}	

					elsif ($cmd[$i] =~ /^-u$/) {
			
						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp list - user id or uuid parameter missing or wrong (user\@domain)\n ";
							return;
						}
						
						if ( &is_aor($cmd[$i+1]) == 0 ) {
							&set_user($cmd[$i+1]);
							if ( $CLAUSE eq "" ) {
								$CLAUSE = " WHERE $avp_table{'AVP_USER_COLUMN'}=\'$OSIPSUSER\' AND $avp_table{'AVP_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'";
							} else {
								$CLAUSE = "$CLAUSE AND $avp_table{'AVP_USER_COLUMN'}=\'$OSIPSUSER\' AND $avp_table{'AVP_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'";
							}
						} else {
							if ( $CLAUSE eq "" ) {
								$CLAUSE = " WHERE $avp_table{'AVP_UUID_COLUMN'} = \'$cmd[$i+1]\'";
							} else {
								$CLAUSE = "$CLAUSE AND $avp_table{'AVP_UUID_COLUMN'}=\'$cmd[$i+1]\'";
							}
						}			

					}

					elsif ($cmd[$i] =~ /^-a$/) {

						if ( $cmd[1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp list - attribute name parameter missing\n";
							return;
						}
						if ( $CLAUSE eq "" ) {
							$CLAUSE = " WHERE $avp_table{'AVP_ATTRIBUTE_COLUMN'}=\'$cmd[$i+1]\'";
						} else {
							$CLAUSE = "$CLAUSE AND $avp_table{'AVP_ATTRIBUTE_COLUMN'}=\'$cmd[$i+1]\'";
						}

					}

					elsif ($cmd[$i] =~ /^-v$/) {

						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp list - value parameter missing\n";
							return;
						}
						if  ( $CLAUSE eq "" ) {
							$CLAUSE = " WHERE $avp_table{'AVP_VALUE_COLUMN'}=\'$cmd[$i+1]\'";
						} else {
							$CLAUSE = "$CLAUSE AND $avp_table{'AVP_VALUE_COLUMN'}=\'$cmd[$i+1]\'";
						}

					}

					elsif ($cmd[$i] =~ /^-t$/) {

						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp list - type parameter missing\n";
							return;
						}
						if ( $CLAUSE eq "" ) {
							$CLAUSE = " WHERE $avp_table{'AVP_TYPE_COLUMN'}=\'$cmd[$i+1]\'";
						} else {
							$CLAUSE = "$CLAUSE AND $avp_table{'AVP_TYPE_COLUMN'}=\'$cmd[$i+1]\'";
						}				

					}
					
					else {
	
						print "avp list - unknown parameter $cmd[$i]\n";
					}
				$i+=2;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);	
				&bdb_select_where($CARRIERROUTE_TABLE,$key);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD", " SELECT $avp_table{'AVP_UUID_COLUMN'},$avp_table{'AVP_USER_COLUMN'},$avp_table{'AVP_DOMAIN_COLUMN'}, $avp_table{'AVP_ATTRIBUTE_COLUMN'},$avp_table{'AVP_TYPE_COLUMN'},$avp_table{'AVP_VALUE_COLUMN'} FROM $AVP_TABLE $CLAUSE " );
			} else {				
				$sth = $dbh->prepare( "SELECT $avp_table{'AVP_UUID_COLUMN'},$avp_table{'AVP_USER_COLUMN'},$avp_table{'AVP_DOMAIN_COLUMN'},
					       $avp_table{'AVP_ATTRIBUTE_COLUMN'},$avp_table{'AVP_TYPE_COLUMN'},$avp_table{'AVP_VALUE_COLUMN'} 
 					       FROM $AVP_TABLE $CLAUSE" );
	
				#execute the query
				$sth->execute( );
				warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );	
				while ( @row = $sth->fetchrow_array( ) )  {
					print "@row\n";
					$result = $#row+1;				    	
				}	
		
				if ( $result == 0 ) {
					print "No entry found having the corresponding attributes!\n";
				}
		
			
				$sth->finish();	
			}


		}	

		elsif ($cmd[1] =~ /^add$/) {
		
			my $AVP_UUID="";

			if ( $#cmd != 7 ) {
				if ( $#cmd != 9 ) {
					print "avp add - bad number of parameters\n";
					return;
				}
			}
			if ( $#cmd eq 9 ) {
				if ( $cmd[2] =~ /-T/ ) {
					$AVP_TABLE=$cmd[3];
				} else {
					print "avp add - unknown parameter $cmd[2]\n";
					return;
				}
			}
			
			if ( &is_aor($cmd[4]) eq 0 ) {
				&set_user($cmd[4]);
			} else {
				$AVP_UUID = $cmd[4];
			}

			my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
			my $date = join("-",$year+1900,$mon+1,$mday+1);
			my $time = join(":",$hour,$min,$sec);
			my $last_modified =  join(" ",$date,$time);
			my $unix_time = time();

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				my $value = join(" ",$AVP_UUID,$cmd[5],$cmd[6],$cmd[7],$last_modified);
				&bdb_insert($CARRIERROUTE_TABLE);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD", "INSERT INTO $AVP_TABLE ($avp_table{'AVP_UUID_COLUMN'},$avp_table{'AVP_USER_COLUMN'},
					       $avp_table{'AVP_DOMAIN_COLUMN'},$avp_table{'AVP_ATTRIBUTE_COLUMN'}, $avp_table{'AVP_TYPE_COLUMN'},
 					       $avp_table{'AVP_VALUE_COLUMN'},$avp_table{'AVP_MODIFIED_COLUMN'}) 
					 VALUES (\'$AVP_UUID\',\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$cmd[5]\',$cmd[6],\'$cmd[7]\',$unix_time)" );
			} else {
				$sth = $dbh->prepare ("INSERT INTO $AVP_TABLE ($avp_table{'AVP_UUID_COLUMN'},$avp_table{'AVP_USER_COLUMN'},
					       $avp_table{'AVP_DOMAIN_COLUMN'},$avp_table{'AVP_ATTRIBUTE_COLUMN'}, $avp_table{'AVP_TYPE_COLUMN'},
 					       $avp_table{'AVP_VALUE_COLUMN'},$avp_table{'AVP_MODIFIED_COLUMN'}) 
					 VALUES (\'$AVP_UUID\',\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$cmd[5]\',$cmd[6],\'$cmd[7]\',\'$last_modified\')" );	

				 ##execute the query
				 $sth->execute( );


		 		 warn "Data was not inserted in database!", $sth->errstr( ), "\n" if $sth->err( );

				 $sth->finish();
			}
		
		}	

		elsif ($cmd[1] =~ /^rm$/) {
		
			$CLAUSE = "";
			if ($#cmd lt 3) {
				print "avp - too few parameters\n";
				&usage_avp();
				return;
			}	


			$i=2;
			while ( $i lt $#cmd ){
					if ($cmd[$i] =~  /^-T$/ ) {						
						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ) {
							print "avp rm - table name parameter missing\n";							
							return;
						} else {
							$AVP_TABLE = $cmd[$i+1];
						}					
					}	

					elsif ($cmd[$i] =~ /^-u$/) {
			
						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp rm - user id uuid or parameter missing\n";
							return;
						}
						
						if ( &is_aor($cmd[$i+1]) == 0 ) {
							&set_user($cmd[$i+1]);
							if ( $CLAUSE eq "" ) {
								$CLAUSE = " WHERE $avp_table{'AVP_USER_COLUMN'}=\'$OSIPSUSER\' AND $avp_table{'AVP_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'";
							} else {
								$CLAUSE = "$CLAUSE AND $avp_table{'AVP_USER_COLUMN'}=\'$OSIPSUSER\' AND $avp_table{'AVP_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'";
							}
						} else {
							if ( $CLAUSE eq "" ) {
								$CLAUSE = " WHERE $avp_table{'AVP_UUID_COLUMN'} = \'$cmd[$i+1]\'";
							} else {
								$CLAUSE = "$CLAUSE AND $avp_table{'AVP_UUID_COLUMN'}=\'$cmd[$i+1]\'";
							}
						}			

					}

					elsif ($cmd[$i] =~ /^-a$/) {

						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp rm - attribute name parameter missing\n";
							return;
						}
						if ( $CLAUSE eq "" ) {
							$CLAUSE = " WHERE $avp_table{'AVP_ATTRIBUTE_COLUMN'}=\'$cmd[$i+1]\'";
						} else {
							$CLAUSE = "$CLAUSE AND $avp_table{'AVP_ATTRIBUTE_COLUMN'}=\'$cmd[$i+1]\'";
						}

					}

					elsif ($cmd[$i] =~ /^-v$/) {

						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp rm - value parameter missing\n";
							return;
						}
						if  ( $CLAUSE eq "" ) {
							$CLAUSE = " WHERE $avp_table{'AVP_VALUE_COLUMN'}=\'$cmd[$i+1]\'";
						} else {
							$CLAUSE = "$CLAUSE AND $avp_table{'AVP_VALUE_COLUMN'}=\'$cmd[$i+1]\'";
						}

					}

					elsif ($cmd[$i] =~ /^-t$/) {

						if ( $cmd[$i+1] =~ /(\s+)|-[Tuavt]/ ){
							print "avp rm - type parameter missing\n";
							return;
						}
						if ( $CLAUSE eq "" ) {
							$CLAUSE = " WHERE $avp_table{'AVP_TYPE_COLUMN'}=\'$cmd[$i+1]\'";
						} else {
							$CLAUSE = "$CLAUSE AND $avp_table{'AVP_TYPE_COLUMN'}=\'$cmd[$i+1]\'";
						}				

					}
					
					else {
	
						print "avp list - unknown parameter $cmd[2]\n";
					}

				$i+=2;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				&bdb_delete($CARRIERROUTE_TABLE,$key);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD", "DELETE FROM $AVP_TABLE $CLAUSE ");
			} else {
				#prepare query
				$sth = $dbh->prepare( "DELETE FROM $AVP_TABLE $CLAUSE " );
	
				#execute the query
				$sth->execute( );
				warn "Deleting data from table failed", $sth->errstr( ), "\n" if $sth->err( );	
		       	        $sth->finish();
			}		
		}	

		elsif ($cmd[1] =~ /^(help$)|(h$)/) {

			&usage_avp();
	
		}

		else {

			print "avp -unknown command $cmd[1]\n";

		}
}


#
##### ------------------------------------------------ #####
### DB operations
#
sub db_ops() {

	if ($#cmd lt 1) {
		print "Too few parameters\n";
		&usage_db_ops();
		return;
	}

	for(my $i=3; $i<=$#cmd; $i++){
		$cmd[2] = join(" ",$cmd[2],$cmd[$i]);
	}

	&require_dbengine();

		if ($cmd[1] =~ /(^exec$)|(^query$)/) {
			if ( $#cmd lt 2 ) {
				print "Missing query parameter\n";
				return;
			}			

			&db_query($cmd[2]);

		}

		elsif ($cmd[1] =~ /(^roexec$)|(^roquery$)/) {
			if ( $#cmd lt 2 ) {
				print "Missing query parameter\n";
				return;
			}
			&db_ro_query($cmd[2]);
		}
		elsif ($cmd[1] =~ /^run$/) {
			
			my $QUERY;

			if ( $#cmd lt 2 ) {
				print "Missing query parameter\n";
				return;
			}

			$QUERY = system("eval \$$cmd[2]");
			#if ( $? != 0 ) { 
			#	print "Missing query value\n";
			#	return;
			#}
			&db_query($QUERY);
			
		}
		elsif ($cmd[1] =~ /^rorun$/) {
	
			my $QUERY;
	
			if ( $#cmd lt 2 ) {
				print "Missing query parameter\n";
				return;
			}
			system ("eval $QUERY = \$$cmd[2]");
			#if ( $QUERY != 0 ) {
			#	print "Missing query value\n";
			#	return;
			#}
			&db_ro_query($QUERY);
		}
		elsif ($cmd[1] =~ /^show$/) {
			if ( $#cmd != 2 ) {
				print "Missing table parameter\n";
				return;
			}

			&db_ro_query("SELECT * FROM $cmd[2]");
			
		}
		elsif ($cmd[1] =~ /^h$/) {
			&usage_db_ops();
		}
		else {
			print "db -unknown command $cmd[1]\n";
		}
}



#
##### ------------------------------------------------ #####
### speeddial 
#
sub speeddial() {

	my $TMP_OSIPSUSER;
	my $TMP_OSIPSDOMAIN;	

	if ($#cmd lt 1) {
		print "Too few parameters!\n";
		&usage_speeddial();
		return;
	}
	
	&require_dbengine();

		if ($cmd[1] =~ /^list$/) {
	
			if ($#cmd == 2) {
				# print speed-dials for user
				if ( &check_aor($cmd[2]) != 0 ) {
					print "speeddial - <$cmd[2]> is not a valid AoR (user\@domain)\n";
					return;
				}
				
				&set_user($cmd[2]);
				
				print "Dumping speed-dials for user=<$cmd[2]>\n";
	
				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					my $key = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
					&bdb_select_where($SD_TABLE,$key);
				} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
					system("$DBTEXTCMD","SELECT CONCAT($sd_table{'SD_SD_USER_COLUMN'},'\@',$sd_table{'SD_SD_DOMAIN_COLUMN'}) AS Short_number, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI,$sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE WHERE $sd_table{'SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'"); 
				} elsif ( $DBENGINE =~ /^Pg$/  ) {			
					$sth = $dbh->prepare("SELECT ($sd_table{'SD_SD_USER_COLUMN'}||'\@'||$sd_table{'SD_SD_DOMAIN_COLUMN'}) AS Short_number, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI,$sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE WHERE $sd_table{'SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
					#execute the query
					$sth->execute( );
					warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );
					## Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";

					while ( @row = $sth->fetchrow_array( ) )  {
						 print "@row\n";				
					}	
	
				} else {			
					$sth = $dbh->prepare("SELECT CONCAT($sd_table{'SD_SD_USER_COLUMN'},'\@',$sd_table{'SD_SD_DOMAIN_COLUMN'}) AS Short_number, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI,$sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE WHERE $sd_table{'SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
					#execute the query
					$sth->execute( );
					warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );
					## Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";

					while ( @row = $sth->fetchrow_array( ) )  {
						 print "@row\n";				
					}	
	
					$sth->finish();	
				}

			} elsif ($#cmd == 1) {
				print "Dumping all speed-dials may take long: do you want to proceed? [Y|N] ";
				if ( (my $line = <STDIN>) =~ /(^y$)|(^Y$)/ ) {
					print "Dumping all speed-dials...\n";
				} else {
					return;
				}

				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					&bdb_select($SD_TABLE);
				} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
					system("$DBTEXTCMD","SELECT CONCAT($sd_table{'SD_SD_USER_COLUMN'},'\@',$sd_table{'SD_SD_DOMAIN_COLUMN'}) AS Short_number, CONCAT($sd_table{'SD_USER_COLUMN'},'\@',$sd_table{'SD_DOMAIN_COLUMN'}) AS Owner, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI,$sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE");
				} elsif ( $DBENGINE =~ /^Pg$/ ) {
					$sth = $dbh->prepare("SELECT ($sd_table{'SD_SD_USER_COLUMN'}||'\@'||$sd_table{'SD_SD_DOMAIN_COLUMN'}) AS Short_number, ($sd_table{'SD_USER_COLUMN'}||'\@'||$sd_table{'SD_DOMAIN_COLUMN'}) AS Owner, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI,$sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE");
					#execute the query
					$sth->execute( );
					warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );
					## Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";

					while ( @row = $sth->fetchrow_array( ) )  {
						 print "@row\n";				
					}
				} else {
					$sth = $dbh->prepare("SELECT CONCAT($sd_table{'SD_SD_USER_COLUMN'},'\@',$sd_table{'SD_SD_DOMAIN_COLUMN'}) AS Short_number, CONCAT($sd_table{'SD_USER_COLUMN'},'\@',$sd_table{'SD_DOMAIN_COLUMN'}) AS Owner, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI,$sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE");
					#execute the query
					$sth->execute( );
					warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );
					## Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";

					while ( @row = $sth->fetchrow_array( ) )  {
						 print "@row\n";				
					}	
				}
			} 

		}	

		elsif ($cmd[1] =~ /^show$/) {

			if ( $#cmd lt 2 ) {
				print "speeddial - wrong number of params for command [show]\n";
				&usage_speeddial();
				return;
			}
			
			if (&check_aor($cmd[2]) != 0 ) {
				print "speeddial - $cmd[2] is not a valid AoR (user\@domain)\n";
				return;
			}
			
			&set_user($cmd[2]);
		
			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key =  join("",$OSIPSUSER,$OSIPSDOMAIN);
				&bdb_select_where($SD_TABLE,$key);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system("$DBTEXTCMD","SELECT CONCAT($sd_table{'SD_USER_COLUMN'},'\@',$sd_table{'SD_DOMAIN_COLUMN'}) AS Owner, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI, $sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE WHERE $sd_table{'SD_SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
			} elsif ( $DBENGINE =~ /^Pg$/ ) {
				$sth = $dbh->prepare("SELECT ($sd_table{'SD_USER_COLUMN'}||'\@'||$sd_table{'SD_DOMAIN_COLUMN'}) AS Owner, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI, $sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE WHERE $sd_table{'SD_SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
				$sth->execute( );
				warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );
				## Retrieve the results of a row of data and print
				print "\tQuery results:\n================================================\n";

				while ( @row = $sth->fetchrow_array( ) )  {
						 print "@row\n";				
				}
			} else {
				$sth = $dbh->prepare("SELECT CONCAT($sd_table{'SD_USER_COLUMN'},'\@',$sd_table{'SD_DOMAIN_COLUMN'}) AS Owner, $sd_table{'SD_NEW_URI_COLUMN'} AS New_URI, $sd_table{'SD_DESC_COLUMN'} FROM $SD_TABLE WHERE $sd_table{'SD_SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
				$sth->execute( );
				warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );
				## Retrieve the results of a row of data and print
				print "\tQuery results:\n================================================\n";

				while ( @row = $sth->fetchrow_array( ) )  {
						 print "@row\n";				
				}	
			}	

		}	

		elsif ($cmd[1] =~ /^add$/) {

			if ( $#cmd != 4 ) {
				if ( $#cmd != 5 ) {
					print "speeddial - wrong number of parameters\n";
					&usage_speeddial();
					return;
				}
			}
			
			if ( &check_aor($cmd[2]) != 0 ) {
				print "speeddial - $cmd[2] is not a valid AoR (user\@domain)\n";
				return;
			}

			if ( &check_aor($cmd[3]) != 0 ) {
				print "speeddial - $cmd[2] is not a valid AoR (user\@domain)\n";
				return;
			}
			
			if ( &check_sipaor($cmd[4]) != 0 ) {
				print "speeddial - $cmd[4] is not a valid SIP AoR (sip:user\@domain)\n";
				return;
			}
			
			&set_user($cmd[2]);
			$TMP_OSIPSUSER = $OSIPSUSER;
			$TMP_OSIPSDOMAIN = $OSIPSDOMAIN;
			&set_user($cmd[3]);

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key =  join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
				my $value = join(" ",$OSIPSUSER,$OSIPSDOMAIN,$cmd[4],$cmd[5]);
				&bdb_insert($SD_TABLE,$key,$value);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system("$DBTEXTCMD","INSERT INTO $SD_TABLE ($sd_table{'SD_USER_COLUMN'},$sd_table{'SD_DOMAIN_COLUMN'},$sd_table{'SD_SD_USER_COLUMN'},$sd_table{'SD_SD_DOMAIN_COLUMN'},$sd_table{'SD_NEW_URI_COLUMN'},$sd_table{'SD_DESC_COLUMN'}) VALUES (\'$TMP_OSIPSUSER\',\'$TMP_OSIPSDOMAIN\',\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$cmd[4]\',\'$cmd[5]\')");
			} else {
				$sth = $dbh->prepare("INSERT INTO $SD_TABLE ($sd_table{'SD_USER_COLUMN'},$sd_table{'SD_DOMAIN_COLUMN'},$sd_table{'SD_SD_USER_COLUMN'},$sd_table{'SD_SD_DOMAIN_COLUMN'},$sd_table{'SD_NEW_URI_COLUMN'},$sd_table{'SD_DESC_COLUMN'}) VALUES (\'$TMP_OSIPSUSER\',\'$TMP_OSIPSDOMAIN\',\'$OSIPSUSER\',\'$OSIPSDOMAIN\',\'$cmd[4]\',\'$cmd[5]\')");

				#execute the query
				$sth->execute();
				warn "Entry could not be inserted into table", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
		}	

		elsif ($cmd[1] =~ /^rm$/) {
		

			if ($DBENGINE =~ /^DB_BERKELEY$/) {
				if  ( $#cmd != 2 ) {
					print "Wrong number of parameters!\n";
					return;
				}
			} else {
				if ( $#cmd != 3 ) {
					print "speeddial rm - invalid number of parameters\n";
					&usage_speeddial();
					return;
				}
			}
			
			if ( &check_aor($cmd[2]) != 0 ) {
				print "speeddial - $cmd[2] not a valid AoR (user\@domain)\n";
				return;
			}
			
			if ( &check_aor($cmd[2]) != 0 ) {
				print "speeddial - $cmd[3] is not a valid AoR (user\@domain)\n";
				return;
			}

			&set_user($cmd[2]);
			$TMP_OSIPSUSER = $OSIPSUSER;
			$TMP_OSIPSDOMAIN = $OSIPSDOMAIN;
			&set_user($cmd[3]);

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key =  join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
				&bdb_delete($SD_TABLE,$key);
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system("$DBTEXTCMD","DELETE FROM $SD_TABLE WHERE $sd_table{'SD_USER_COLUMN'}=\'$TMP_OSIPSUSER\' AND $sd_table{'SD_DOMAIN_COLUMN'}=\'$TMP_OSIPSDOMAIN\' AND $sd_table{'SD_SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
			} else {
				$sth = $dbh->prepare("DELETE FROM $SD_TABLE WHERE $sd_table{'SD_USER_COLUMN'}=\'$TMP_OSIPSUSER\' AND $sd_table{'SD_DOMAIN_COLUMN'}=\'$TMP_OSIPSDOMAIN\' AND $sd_table{'SD_SD_USER_COLUMN'}=\'$OSIPSUSER\' AND $sd_table{'SD_SD_DOMAIN_COLUMN'}=\'$OSIPSDOMAIN\'");
	
				#execute the query
				$sth->execute( );
				warn "Deleting entry failed!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}
		}	

		elsif ($cmd[1] =~ /^(help$)|(h$)/) {

			&usage_speeddial();			

		}	

		else {
			print "speeddial -unknown command $cmd[1]\n";
		}

}


#
##### ------------------------------------------------ #####
### online 
#
sub opensips_online() {
	my $var;
	&require_ctlengine();
	$var = system(&mi_comm("ul_dump"));	
	print $var;
	if ($var =~ /aor/i) {
		`echo $var | awk '{print $2}' | sort | sort -mu`;
	}	
	
}


#
##### ------------------------------------------------ #####
### dialplan 
#
sub opensips_dialplan() {
	

	if ( $#cmd lt 1 ) {
		print "Too few parameters\n";
		&usage_dialplan();
		return;
	}
	
		if ($cmd[1] =~ /^show$/) {
			
			if ( $#cmd == 2 ) {
				print "dialplan $cmd[2] tables\n";

				if ( $DBENGINE =~ /^DBTEXT$/ ) {
					system("$DBTEXTCMD"," SELECT * 
						 FROM $DIALPLAN_TABLE 
 						 WHERE $dialplan_table{'DIALPLAN_DPID_COLUMN'} = $cmd[2] 
						 ORDER BY $dialplan_table{'DIALPLAN_PR_COLUMN'} ");
				} else {
					$sth = $dbh->prepare( " SELECT * 
							 FROM $DIALPLAN_TABLE
	 						 WHERE $dialplan_table{'DIALPLAN_DPID_COLUMN'} = $cmd[2] 
							 ORDER BY $dialplan_table{'DIALPLAN_PR_COLUMN'} " );
					#execute query
					$sth->execute( );

					warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );

					## Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";
					while ( @row = $sth->fetchrow_array( ) )  {
							 print "@row\n";				
					}	
					$sth->finish( );
				}

			} else {
				print "dialplan tables\n";
				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					&bdb_select($DIALPLAN_TABLE);
				} elsif ( $DBENGINE =~ /^DBTEXT$/  ) {
					system("$DBTEXTCMD", " SELECT * 
							FROM $DIALPLAN_TABLE 
							ORDER BY $dialplan_table{'DIALPLAN_DPID_COLUMN'}, 
							$dialplan_table{'DIALPLAN_PR_COLUMN'} " );
				} else {				
					$sth = $dbh->prepare( " SELECT * 
								FROM $DIALPLAN_TABLE
								ORDER BY $dialplan_table{'DIALPLAN_DPID_COLUMN'}, 
								$dialplan_table{'DIALPLAN_PR_COLUMN'} " );

					#execute query
					$sth->execute( );

					warn "Entry could not be retrieved from table\n", $sth->errstr( ), "\n" if $sth->err( );

					## Retrieve the results of a row of data and print
					print "\tQuery results:\n================================================\n";
					while ( @row = $sth->fetchrow_array( ) )  {
							 print "@row\n";				
					}
					$sth->finish();
				}	

			}			


		}

		elsif ($cmd[1] =~ /^reload$/) {

			&mi_comm('dp_reload');			

		}

		elsif ($cmd[1] =~ /^addrule$/) {

			if ( $#cmd lt 9 ) {
				print "Too few parameters\n";
				&usage_dialplan();
				return;
			}

			my $DIALPLAN_DPID = $cmd[2];
			my $DIALPLAN_PR = $cmd[3];
			my $DIALPLAN_MATCH_OP = $cmd[4];

				if ($DIALPLAN_MATCH_OP =~ /^equal$/) {
					$DIALPLAN_MATCH_OP = 0;
				}
				elsif ($DIALPLAN_MATCH_OP =~ /^regexp$/) {
					$DIALPLAN_MATCH_OP = 1;
				}
				else {
					print "dialplan - unexpected $DIALPLAN_MATCH_OP for operating matching. Use 'equal' or 'regexp'!\n";
					return;
				}

			my $DIALPLAN_MATCH_EXP = $cmd[5];
			my $DIALPLAN_MATCH_LEN = $cmd[6];
			my $DIALPLAN_SUBST_EXP = $cmd[7];
			my $DIALPLAN_REPL_EXP = $cmd[8];
			my $DIALPLAN_ATTRS = $cmd[9];

			if ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD"," INSERT INTO $DIALPLAN_TABLE 
					     ( $dialplan_table{'DIALPLAN_DPID_COLUMN'}, $dialplan_table{'DIALPLAN_PR_COLUMN'}, 
					     $dialplan_table{'DIALPLAN_MATCH_OP_COLUMN'}, $dialplan_table{'DIALPLAN_MATCH_EXP_COLUMN'}, 
				 	     $dialplan_table{'DIALPLAN_MATCH_LEN_COLUMN'},	$dialplan_table{'DIALPLAN_SUBST_EXP_COLUMN'}, 
		                  	     $dialplan_table{'DIALPLAN_REPL_EXP_COLUMN'}, $dialplan_table{'DIALPLAN_ATTRS_COLUMN'} ) 
					     VALUES ( $DIALPLAN_DPID, $DIALPLAN_PR, $DIALPLAN_MATCH_OP, 
					     \'$DIALPLAN_MATCH_EXP\', $DIALPLAN_MATCH_LEN, \'$DIALPLAN_SUBST_EXP\', 
					     \'$DIALPLAN_REPL_EXP\', \'$DIALPLAN_ATTRS\') " );

			} else {

				$sth=$dbh->prepare( " INSERT INTO $DIALPLAN_TABLE 
						     ( $dialplan_table{'DIALPLAN_DPID_COLUMN'}, $dialplan_table{'DIALPLAN_PR_COLUMN'}, 
						     $dialplan_table{'DIALPLAN_MATCH_OP_COLUMN'}, $dialplan_table{'DIALPLAN_MATCH_EXP_COLUMN'}, 
					 	     $dialplan_table{'DIALPLAN_MATCH_LEN_COLUMN'},	$dialplan_table{'DIALPLAN_SUBST_EXP_COLUMN'}, 
				          	     $dialplan_table{'DIALPLAN_REPL_EXP_COLUMN'}, $dialplan_table{'DIALPLAN_ATTRS_COLUMN'} ) 
						     VALUES ( $DIALPLAN_DPID, $DIALPLAN_PR, $DIALPLAN_MATCH_OP, 
						     \'$DIALPLAN_MATCH_EXP\', $DIALPLAN_MATCH_LEN, \'$DIALPLAN_SUBST_EXP\', 
						     \'$DIALPLAN_REPL_EXP\', \'$DIALPLAN_ATTRS\') " );
			

				#execute the query
				$sth->execute();
				warn "Entry could not be inserted into table", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			&mi_comm('dp_reload');
		}


		elsif ($cmd[1] =~ /^rm$/) {
			print "rm";
			if ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD"," DELETE FROM $DIALPLAN_TABLE ");
			} else {
				$sth = $dbh->prepare(" DELETE FROM $DIALPLAN_TABLE ");

				#execute the query
				$sth->execute( );
				warn "Deleting entry failed!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			&mi_comm('dp_reload');			
		}

		elsif ($cmd[1] =~ /^rmpid$/) {

			if ( $#cmd lt 2 ) {
				print "Too few parameters\n";
				&usage_dialplan();
				return;
			}

			if ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD"," DELETE FROM $DIALPLAN_TABLE WHERE $dialplan_table{'DIALPLAN_DPID_COLUMN'}=$cmd[2]" );
			} else {
				$sth = $dbh->prepare( " DELETE FROM $DIALPLAN_TABLE WHERE $dialplan_table{'DIALPLAN_DPID_COLUMN'}=$cmd[2]" );

				#execute the query
				$sth->execute();
				warn "Deleting entry failed!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}


			&mi_comm('dp_reload');			

		}

		elsif ($cmd[1] =~ /^rmrule$/) {

			if ( $#cmd lt 3 ) {
				print "Too few parameters\n";
				&usage_dialplan();
				return;
			}

			my $DIALPLAN_DPID = $cmd[2];
			my $DIALPLAN_PR = $cmd[3];

			if ( $DBENGINE =~ /^DBTEXT$/  ) {
				system("$DBTEXTCMD","DELETE FROM $DIALPLAN_TABLE 
						WHERE $dialplan_table{'DIALPLAN_DPID_COLUMN'}=$DIALPLAN_DPID 
						AND $dialplan_table{'DIALPLAN_PR_COLUMN'}=$DIALPLAN_PR " );
			} else {
				$sth = $dbh->prepare ( "DELETE FROM $DIALPLAN_TABLE 
							WHERE $dialplan_table{'DIALPLAN_DPID_COLUMN'}=$DIALPLAN_DPID 
							AND $dialplan_table{'DIALPLAN_PR_COLUMN'}=$DIALPLAN_PR " );
				#execute query
				$sth->execute();
				warn "Deleting entry failed!", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			}

			&mi_comm('dp_reload');			

		}

		elsif ($cmd[1] =~ /^h$/) {
			
			&usage_dialplan();
		
		}

		else {
			print "dialplan -unknown command $cmd[1]\n";
		}
}


#
##### ------------------------------------------------ #####
### ps shortcut 
#
sub opensips_ps(){
	&require_ctlengine();
	&mi_comm("ps");	
	return;
}


#
##### ------------------------------------------------ #####
### version shortcut 
#
sub opensips_version() {
	&require_ctlengine();
	&mi_comm("version");
	return;
}


#
##### ------------------------------------------------ #####
### database operations  
#
sub opensips_db() {

		if ($cmd[1] =~ /^migrate$/){
			
			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				if ( $DBENGINE !~ /^mysql$/ ) {
					print $DBENGINE ." doesn't support the migrate operation\n";
					return;
				}

				if ( $#cmd != 3 ) {
					print "Migrate requires 2 parameters: old and new database\n";
					&usage_db();
					return;
				}

				# create new database
				print "Creating new database $cmd[3]....\n";
				&mysql_opensips_create($cmd[3]);
				#if ( $? != 0 ) {
				#	print "Migrate: creating new database failed\n";
				#	return;
				#}

				# migrate data
				print "Migrating data from $cmd[2] to $cmd[3]....\n";
				&mysql_migrate_db($cmd[2],$cmd[3]);
				#if ( $? == 0 ) {
				print "Migration successfully completed.\n";
				#}	
			}
		}

		elsif ($cmd[1] =~ /^copy$/) {

			my ( $tmp_file);
			# copy database to some other name
			if ( ( $DBENGINE =~ /^DB_BERKELEY$/ ) || ( $DBENGINE =~ /^DBTEXT$/ ) ) {
				print "$DBENGINE don't support this operation\n";
				return;
			}
			
			if ( $#cmd != 2 ) {
				&usage_db();
				return;
			}

			$tmp_file=`mktemp /tmp/osipsconsole_db.XXXXXXXXXX`;

			&opensips_dump($DBNAME,$tmp_file);
			if ( $? != 0 ) {
				`rm $tmp_file`;
				return;
			}

			&opensips_create($cmd[2]);
			if ( $? != 0 ) {
				`rm $tmp_file`;
				return;
			}

			&opensips_restore($cmd[2],$tmp_file);
			`rm -f $tmp_file`;
			return;
		}

		elsif ($cmd[1] =~ /^backup$/) {
			if ( ($DBENGINE =~ /^DB_BERKELEY$/) || ( $DBENGINE =~ /^DBTEXT$/ ) ) {
				print "$DBENGINE doesn't support the backup operation\n";
				return;
			}
			# backup current database
			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				&usage_db();
				return;
			}
			&opensips_dump($DBNAME, $cmd[2]);

		}

		elsif ($cmd[1] =~ /^restore$/) {

			if ( ($DBENGINE =~ /^DB_BERKELEY$/) || ($DBENGINE =~ /^dbtext$/) ) {
				print "$DBENGINE doesn't support the restore operation\n";
				return;
			}

			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				&usage_db();
				return;
			}
			&opensips_restore($cmd[1],);
		}

		elsif ($cmd[1] =~ /^create$/) {

			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				&usage_db();
				return;
			}
			&opensips_create($cmd[2]);
		}

		elsif ($cmd[1] =~ /^presence$/) {

			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				&usage_db();
				return;
			}

			&presence_create($cmd[2]);
		}

		elsif ($cmd[1] =~ /^extra$/) {

			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				&usage_db();
				return;
			}

			&extra_create($cmd[2]);
		}

		elsif ($cmd[1] =~ /^drop$/) {
	
			if ($cmd[2] =~ /^h$/) {
				&usage_db();
				return;
			} elsif ( $#cmd != 2 ) {
				&usage_db();
				return;
			}
			&opensips_drop($cmd[2]);
		}

		elsif ($cmd[1] =~ /^reinit$/) {

			# delete database and create a new one
			# create new database structures
			if ( $#cmd != 2 ) {
				&usage_db();
				return;
			}

			if ( $cmd[2] =~ /^h$/ ) {
				&usage_db();
				return;
			}

			&opensips_drop($cmd[2]);

			&opensips_create($cmd[2]);

		}

		elsif ($cmd[1] =~ /(^bdb$)|(^db_berkeley$)/){
			&opensips_berkeley();
		}

		else {
			print "Unknown command\n";
		}
}

sub opensips_create() {

	my $db = $_[0];

		if ($DBENGINE =~ /^mysql$/) {	
			$PW = &prompt_pw();
			&mysql_opensips_create($db);
		}
		elsif ($DBENGINE =~ /^oracle$/) {
			#&oracle_opensips_create($db);
		}
		elsif ($DBENGINE =~ /^Pg$/) {
			&pgsql_opensips_create($db);
		}
		elsif ($DBENGINE =~ /^DBTEXT$/) {
			&dbtext_opensips_create($db);
		}
		elsif ($DBENGINE =~  /^DB_BERKELEY$/) {
			&bdb_opensips_create($db);
		}
}

sub presence_create() {

	my $db = $_[0];

		if ($DBENGINE =~ /^mysql$/) {
			$PW=&prompt_pw();
			&mysql_presence_create($db);
		}
		elsif ($DBENGINE =~  /^oracle$/) {
			#&oracle_presence_create($db);
		}
		elsif ($DBENGINE =~ /^Pg$/) {
			&pgsql_presence_create($db);
		}
		elsif ($DBENGINE =~ /^DBTEXT$/) {
			&dbtext_presence_create($db);
		}
		elsif ($DBENGINE =~ /^DB_BERKELEY$/) {
			&bdb_presence_create($db);
		}
}

sub extra_create() {

	my $db = $_[0];

		if ($DBENGINE =~ /^mysql$/) {
			$PW = &prompt_pw();
			&mysql_extra_create($cmd[1]);
		}
		elsif ($DBENGINE =~ /^oracle$/) {
			#&oracle_extra_create($db);
		}
		elsif ($DBENGINE =~ /^Pg$/) {
			&pgsql_extra_create($db);
		}
		elsif ($DBENGINE =~ /^DBTEXT$/) {
			&dbtext_extra_create($db);
		}
		elsif ($DBENGINE =~ /^DB_BERKELEY$/) {
			&bdb_extra_create($db);
		}
}

sub opensips_drop() {

	my $db = $_[0];

		if ($DBENGINE =~ /^mysql$/) {
			$PW = &prompt_pw();
			&mysql_opensips_drop($db);
		}
		elsif ($DBENGINE =~ /^oracle$/) {
			#&oracle_opensips_drop($db);
		}
		elsif ($DBENGINE =~ /^Pg$/) {
			&pgsql_opensips_drop($db);
		}
		elsif ($DBENGINE =~ /^DBTEXT$/) {
			&dbtext_opensips_drop($db);
		}
		elsif ($DBENGINE =~ /^DB_BERKELEY$/) {
			&bdb_opensips_drop($db);
		}
}

# pars: <database name>

#
##### ------------------------------------------------ #####
### database create functions 
#
sub mysql_opensips_create() {

	my (@content, $temp, @query, $i, $ans, $TABLE, $test);

	&validate_dbdata("mysql");

	if ( $#_ lt 0 ) { 
		print "opensips_create function takes one parameter\n";
		return;
	}

	my $db = $_[0];

	print "Creating database $db ...\n";

	# Users: opensips is the regular user, opensipsro only for reading
	&mysql_query("create database $db;" . 
	"GRANT ALL PRIVILEGES ON $db.\* TO \'$DBRWUSER\' IDENTIFIED  BY \'$DBRWPW\'; " .
	"GRANT ALL PRIVILEGES ON $db.* TO \'${DBRWUSER}\'\@\'$DBHOST\' IDENTIFIED BY \'$DBRWPW\';" .
	"GRANT SELECT ON $db.* TO \'$DBROUSER\' IDENTIFIED BY \'$DBROPW\';" .
	"GRANT SELECT ON " . $db. ".\* TO \'" . ${DBROUSER} . "\'\@\'" . $DBHOST . "\' IDENTIFIED BY \'".$DBROPW."\';");
	if ($? == 0 )	{
		print "Database $db....created\n";
	} else {
		print "Creating database $db failed....Exiting!\n";
		return;
	}

	
	foreach $TABLE (@STANDARD_MODULES) {
		print "Creating core table: $TABLE...\n";
		open(TABLE, "< $DB_SCHEMA/$TABLE-create.sql");
		@content = <TABLE>;
		$temp = "@content";
		@query = split(";",$temp);
		for ( $i=0; $i<$#query; $i++) {
			&mysql_query($db, $query[$i]);
		}
	}

	print "Core OpenSIPS tables succesfully created.\n";

	if ( -e $DB_SCHEMA."/extensions-create.sql" ) {
		print "Creating custom extensions tables\n";

		open(TABLE, "< $DB_SCHEMA/extensions-create.sql");
		@content = <TABLE>;
		$temp = "@content";
		@query = split(";",$temp);
		for ( $i=0; $i<$#query; $i++) {
			&mysql_query($db, $query[$i]);
		}
	}

	print "Install presence related tables? (y/n): ";
	if ( ( $ans = <STDIN> ) =~ /[(^y)(^Y)]$/ ) {
		&mysql_presence_create($db);
	} else {
		return;
	}

	print "Install tables for @EXTRA_MODULES? (y/n): ";
	if ( ( $ans = <STDIN> ) =~ /[(^y)(^Y)]$/ ) {
		$HAS_EXTRA="yes";
		&mysql_extra_create($db);
	}  else {
		return;
	}
	
	return;
	
} 


### pgsql database create functions 
sub pgsql_opensips_create() {	# pars: <database name>
        
	my ( $db, $TABLE, @query, $temp, @content, $i, $ans );

	&validate_dbdata("postgres");

	if ( $#_ != 0 ) {
		print "opensips_create function takes one param\n";
		return;
	}
	
	$db = $_[0];


	print "Creating database $db...\n";

	&pgsql_query("template1", "create database \"$db\";");
	if ( $? != 0 ) {
		print "Creating database failed!";
		return;
	}

	#&pgsql_query ($db, "CREATE FUNCTION \"concat\" (text,text) RETURNS text AS \"SELECT \$1 || \$2;\" LANGUAGE \'sql\';
	 #       CREATE FUNCTION \"rand\" () RETURNS double precision AS \'SELECT random();\' LANGUAGE \'sql\';");

	# emulate mysql proprietary functions used by the lcr module in postgresql

	#if ( $? != 0 ) {
	#	print "Creating pgsql emulation functions failed!";
	#	return;
	#}

	foreach $TABLE (@STANDARD_MODULES) {
		print "Creating core table: $TABLE...\n";
		open(TABLE, "< $DB_SCHEMA/$TABLE-create.sql");
		@content = <TABLE>;
		$temp = "@content";
		@query = split(";",$temp);
		for ( $i=0; $i<$#query; $i++) {
			&pgsql_query($db, $query[$i]);
		}
		if ( $? == 0 )	{
			print "Table $TABLE was created!\n";
		} else {
			print "Table $TABLE could not be created!\n";
		}
	}

	&pgsql_query ($db, "CREATE USER $DBRWUSER WITH PASSWORD '$DBRWPW';");
	if ( $? == 0 )	{
		print "Creating user $DBRWUSER in database succeded!\n";
	} else {
		print "Creating user $DBRWUSER in database failed!\n";
	}
	
	&pgsql_query ($db,"CREATE USER $DBROUSER WITH PASSWORD '$DBROPW';");
	if ( $? == 0 )	{
		print "Creating user $DBROUSER in database succeded!\n";
	} else {
		print "Creating user $DBROUSER in database failed!\n";
	}

	foreach $TABLE ( @STANDARD_TABLES ) {
		#print $TABLE;
		&pgsql_query ( $db, "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;");
		&pgsql_query ($db, "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;");
                if ( $TABLE !~ /version/ ) {
                        if ( ($TABLE !~ /dr_gateways/) && ($TABLE !~ /nh_sockets/) && ($TABLE !~ /dr_rules/) ) {
                                &pgsql_query ( $db, "GRANT ALL PRIVILEGES ON TABLE " . $TABLE . "_id_seq TO $DBRWUSER;");
                                if ( $? != 0 ) {
                                        print "Grant privileges to standard tables failed!\n";
                                } else {
                                        print "Privileges to standard tables succeded!\n";
                                }
                                &pgsql_query ($db, "GRANT SELECT ON TABLE " . $TABLE . "_id_seq TO $DBROUSER;" );
                        } elsif ( $TABLE =~ /dr_gateways/ ) {
                                &pgsql_query ( $db, "GRANT ALL PRIVILEGES ON TABLE " . $TABLE . "_gwid_seq TO $DBRWUSER;");
                                if ( $? != 0 ) {
                                        print "Grant privileges to standard tables failed!\n";
                                } else {
                                        print "Privileges to standard tables succeded!\n";
                                }
                                &pgsql_query ($db, "GRANT SELECT ON TABLE " . $TABLE . "_gwid_seq TO $DBROUSER;" );
                        } elsif ( $TABLE =~ /dr_rules/ ) {
                                &pgsql_query ( $db, "GRANT ALL PRIVILEGES ON TABLE " . $TABLE . "_ruleid_seq TO $DBRWUSER;");
                                if ( $? != 0 ) {
                                        print "Grant privileges to standard tables failed!\n";
                                } else {
                                        print "Privileges to standard tables succeded!\n";
                                }
                                &pgsql_query ($db, "GRANT SELECT ON TABLE " . $TABLE . "_ruleid_seq TO $DBROUSER;" );
                        }

		}
	}


	if ( -e $DB_SCHEMA."/extensions-create.sql" ) {
		print "Creating custom extensions tables\n";

		open(TABLE, "< $DB_SCHEMA/extensions-create.sql");
		@content = <TABLE>;
		$temp = "@content";
		@query = split(";",$temp);
		for ( $i=0; $i<$#query; $i++) {
			&pgsql_query($db, $query[$i]);
		}
		if ($? == 0 )	{
			print "Custom extension tables were created!\n";
		} else {
			print "Custom extension tables could not be created!\n";
		}
	}

	print "Core OpenSIPS tables succesfully created.\n";

	print "Install presence related tables? (y/n): ";
	if ( ( $ans = <STDIN> ) =~ /[(^y)(^Y)]$/ ) {
		&pgsql_presence_create($db);
	} else {
		return;
	}

	print "Install tables for @EXTRA_MODULES? (y/n): ";
	if ( ( $ans = <STDIN> ) =~ /[(^y)(^Y)]$/ ) {
		$HAS_EXTRA="yes";
		&pgsql_extra_create($db);
	}  else {
		return;
	}
	
	return;
}


sub dbtext_opensips_create () { # pars: <database name>
	my ( $DB_PATH, $TABLE, $ANSWER );

        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/dbtext/opensips";
        } else {
                $DB_SCHEMA="./dbtest/opensips";
        }

	if ( $#_ != 0 ) {
		print "opensips_create function takes one param (DB_PATH)\n";
		return;
	}

	$DB_PATH = $_[0];

	print "creating DBTEXT tables at: $DB_PATH ...\n";

	`mkdir -p -m 777 $DB_PATH`;

	foreach $TABLE ( @STANDARD_TABLES ) {
	    print "Creating core table: $TABLE\n";
	    `cp $DB_SCHEMA/$TABLE $DB_PATH/$TABLE`;
	    if ( $? != 0 ) {
		print "Creating core tables failed!\n";
		return;
	    }
	}

	`chmod -R a+w $DB_PATH`;

	print "Install presence related tables? (y/n): ";
	if ( ($ANSWER = <STDIN> ) =~ /y|Y/ ) {
		&dbtext_presence_create($DB_PATH);
	}

	print "Install tables for @EXTRA_MODULES? (y/n): ";
	if ( ($ANSWER = <STDIN> ) =~ /y|Y/ ) {
		&dbtext_extra_create($DB_PATH);
	}
} 

sub bdb_opensips_create() { # pars: <DB_PATH>

	my ($TABLE, $ans);

	&validate_dbdata("db_berkeley/opensips");

	if ( $#_ != 0 ) {
		print "opensips_create param [DB_PATH]\n";
		return;
	}
	
	$DB_PATH = $_[0];
	if ( ! -d $_[0] ) {
		print "creating Berkeley DB database at: [$_[0]]\n";
		`mkdir -p -m 777 $DB_PATH`;
	}
	
	foreach $TABLE (@STANDARD_TABLES) {
	    print "Creating standard table: $TABLE\n";
	    system("$LOAD_CMD -T -t hash -f $DB_SCHEMA/$TABLE -h $_[0] $TABLE");
	    if ( $? != 0 ) {
		print "Creating standard tables failed!\n";
		return;
	    }
	}

	print "Install presence related tables? (y/n): ";
	if ( ($ans = <STDIN>) =~ /(^y$)|(^Y$)/ ) { 
		&presence_create($DB_PATH);
	}

	print "Install presence related tables? (y/n): ";
	if ( ($ans = <STDIN>) =~ /(^y$)|(^Y$)/ ) { 
		&extra_create($DB_PATH);
	}

} # opensips_create




#
##### ------------------------------------------------ #####
### mysql presence create functions  
#
sub mysql_presence_create(){ # pars: <database name>

	my (@content, $temp, @query, $i, $TABLE);

	&validate_dbdata("mysql");

	if ( $#_ lt 0 ) {
		print "presence_create function takes one parameter\n";
		return;
	}
	
	my $db = $_[0];
	print "Creating presence tables into $db ...\n";

	open(TABLE, "< $DB_SCHEMA/presence-create.sql");
	@content = <TABLE>;	
	$temp = "@content";
	@query = split(";",$temp);		
	for ( $i=0; $i<$#query; $i++) {
		&mysql_query($db, $query[$i]);
	}

	open(TABLE, "< $DB_SCHEMA/rls-create.sql");
	@content = <TABLE>;
	$temp = "@content";
	@query = split(";",$temp);
	for ( $i=0; $i<$#query; $i++) {
		&mysql_query($db, $query[$i]);
	}

}  


### pgsql presence create functions  
#
sub pgsql_presence_create(){ # pars: <database name>

	my (@content, $temp, @query, $i, $TABLE);

	&validate_dbdata("mysql");

	if ( $#_ != 0 ) {
		print "presence_create function takes one parameter\n";
		return;
	}
	
	my $db = $_[0];
	print "Creating presence tables into $db ...\n";

	open(TABLE, "< $DB_SCHEMA/presence-create.sql");
	@content = <TABLE>;	
	$temp = "@content";
	@query = split(";",$temp);		
	for ( $i=0; $i<$#query; $i++) {
		&pgsql_query($db, $query[$i]);
		if ($? == 0 )	{
			print "Presence tables were created!\n";
		} else {
			print "Presence tables could not be created!\n";
		}
	}

	open(TABLE, "< $DB_SCHEMA/rls-create.sql");
	@content = <TABLE>;
	$temp = "@content";
	@query = split(";",$temp);
	for ( $i=0; $i<$#query; $i++) {
		&pgsql_query($db, $query[$i]);
		if ($? == 0 )	{
			print "Presence_rls tables were created!\n";
		} else {
			print "Presence_rls tables could not be created!\n";
		}
	}



	foreach $TABLE (@PRESENCE_TABLES) {
		&pgsql_query ($db, "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;");
		&pgsql_query ($db, "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;");
		&pgsql_query ($db, "GRANT ALL PRIVILEGES ON TABLE " . $TABLE . "_id_seq TO $DBRWUSER;");
	    	&pgsql_query ($db, "GRANT SELECT ON TABLE " . $TABLE . "_id_seq TO $DBROUSER;");
		if ($? == 0 )	{
			print "Granting privileges to presence tables succeded!\n";
		} else {
			print "Granting privileges to presence tables failed!\n";
		}
	}

	print "Presence tables succesfully created.\n";
} 

sub dbtext_presence_create () { # pars: <database name>
	my ( $DB_PATH, $TABLE );

	&validate_dbdata("mysql");

	if ( $#_ != 0 ) {
		print "presence_create function takes one param (DB_PATH)";
		return;
	}

	$DB_PATH = $_[0];

	print "creating DBTEXT presence tables at: $DB_PATH ...\n";

	`mkdir -p $DB_PATH`;

	foreach $TABLE (@PRESENCE_TABLES) {
	 	print "Creating presence table: $TABLE\n";
		`cp $DB_SCHEMA/$TABLE $DB_PATH/$TABLE`;

		if ( $? != 0 ) {
			print "Creating presence tables failed!\n";
			return;
		}
	}

	foreach $TABLE (@PRESENCE_RLS_TABLES) {
	 	print "Creating presence table: $TABLE\n";
		`cp $DB_SCHEMA/$TABLE $DB_PATH/$TABLE`;

		if ( $? != 0 ) {
			print "Creating presence_rls tables failed!\n";
			return;
		}
	}

}  # end presence_create


sub bdb_presence_create() {# pars: <DB_PATH>
	my ($TABLE);

	&validate_dbdata("mysql");

	if ( $#_ != 0 ) {
		print "presence_create param [DB_PATH]\n";
		return;
	}
	
	$DB_PATH=$_[0];
	if ( ! -d $_[0] ) {
		# Assert: the directory should already exist
		print "BerkeleyDB directory does not exist at: [$_[0]]\n";
		print;
	}

	if ( ! -f $_[0]."/version" ) {
		# Assert: directory should already contain table 'version'
		print "BerkeleyDB directory does not have VERSION table at: [$_[0]]\n";
		return;
	}
	
	foreach $TABLE (@PRESENCE_TABLES) {
	    print "Creating presence table: $TABLE\n";
	    system("$LOAD_CMD -T -t hash -f $DB_SCHEMA/$TABLE -h $_[0] $TABLE");
	    if ( $? != 0 ) {
		print "Creating presence tables failed!\n";
		return;
	    }
	}
	
	foreach $TABLE (@PRESENCE_RLS_TABLES) {
	    print "Creating presence table: $TABLE\n";
	    system("$LOAD_CMD -T -t hash -f $DB_SCHEMA/$TABLE -h $_[0] $TABLE");
	    if ( $? != 0 ) {
		print "Creating presence tables failed!\n";
		return;
	    }
	}

}  # end presence_create


#
##### ------------------------------------------------ #####
### mysql extra functions  
#
sub mysql_extra_create() { # pars: <database name>
	my (@content, $temp, @query, $i, $TABLE);

	&validate_dbdata("mysql");

	if ( $#_ lt 0 ) {
		print "extra_create function takes one param\n";
		return;
	}
	my $db = $_[0];
	print "Creating extra tables into $db...\n";

	foreach $TABLE (@EXTRA_MODULES) {
		print "Creating extra table: $TABLE....\n";
		open(TABLE, "< $DB_SCHEMA/$TABLE-create.sql");
		@content = <TABLE>;
		$temp = "@content";
		@query = split(";",$temp);
		for ( $i=0; $i<$#query; $i++) {
			&mysql_query($db, $query[$i]);
		}
	}

} 



### pgsql extra functions  
sub pgsql_extra_create() { # pars: <database name>
	my (@content, $temp, @query, $i, $TABLE);

	&validate_dbdata("postgres");

	if ( $#_ != 0 ) {
		print "extra_create function takes one param\n";
		return;
	}
	
	my $db = $_[0];
	print "Creating extra tables into $db...\n";

	foreach $TABLE (@EXTRA_MODULES) {
		print "Creating extra table: $TABLE\n";
		open(TABLE, "< $DB_SCHEMA/$TABLE-create.sql");
		@content = <TABLE>;
		$temp = "@content";
		@query = split(";",$temp);
		for ( $i=0; $i<$#query; $i++) {
			&pgsql_query($db, $query[$i]);
		}
	}
	print "Extra tables succesfully created.\n";


	foreach $TABLE ( @EXTRA_TABLES ) {

		&pgsql_query ($db, "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;" );
		if ($? == 0 )	{
			print "Granting privileges on $TABLE to $DBRWUSER succeded!\n";
		} else {
			print "Granting priviliges on $TABLE to $DBRWUSER failed!\n";
		}

		&pgsql_query ($db, "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;" );
		if ($? == 0 )	{
			print "Granting privileges on $TABLE to $DBROUSER succeded!\n";
		} else {
			print "Granting priviliges on $TABLE to $DBROUSER failed!\n";
		}

		if ( $TABLE !~ /route_tree/ ) {
			&pgsql_query ($db, "GRANT ALL PRIVILEGES ON TABLE " . $TABLE . "_id_seq TO $DBRWUSER;");
			if ($? == 0 )	{
				print "Granting privileges on $TABLE to $DBRWUSER succeded!\n";
			} else {
				print "Granting priviliges on $TABLE to $DBRWUSER failed!\n";
			}	

		 	&pgsql_query ($db, "GRANT SELECT ON TABLE " . $TABLE . "_id_seq TO $DBROUSER;" );
			if ($? == 0 )	{
				print "Granting privileges on $TABLE to $DBROUSER succeded!\n";
			} else {
				print "Granting priviliges on $TABLE to $DBROUSER failed!\n";
			}	
		}
	}


} 


sub dbtext_extra_create() { # pars: <database name>
       
	my ( $DB_PATH, $TABLE );

	&validate_dbdata("dbtext/opensips");

	if ( $#_ != 0 ) {
		print "extra_create function takes one param\n";
		return;
	}

	print "creating DBTEXT extra tables at: $DB_PATH ...\n";

	foreach $TABLE (@EXTRA_TABLES) {
	    print "Creating extra table: $TABLE\n";
	    `cp $DB_SCHEMA/$TABLE $DB_PATH/$TABLE`;
	    
		if ( $? !=  0 ) {
			print "Creating extra tables failed!\n";
			return;
	        }
	}

}  # end extra_create



sub bdb_extra_create() { # pars: <DB_PATH>
        
	my ($TABLE);

	&validate_dbdata("db_berkeley/opensips");

	if ( $#_ != 0 ) {
		print "extra_create function takes one param (DB_PATH)\n";
		return;
	}
	
	$DB_PATH = $_[0];
	if ( ! -d $_[0] ) {
		# Assert: the directory should already exist
		print "BerkeleyDB directory does not exist at: [$_[0]]";
		return;
	}

	if ( ! -f $_[0]."/version" ) {
		# Assert: directory should already contain table 'version'
		print "BerkeleyDB directory does not have VERSION table at: [$_[0]]\n";
		return;
	}
	
	foreach $TABLE (@EXTRA_TABLES) {
	    print "Creating extra table: $TABLE\n";
	    system("$LOAD_CMD -T -t hash -f $DB_SCHEMA/$TABLE -h $_[0] $TABLE");
	    if ( $? != 0 ) {
		print "Creating extra tables failed!\n";
		return;
	    }
	}
	
}  # end extra_create


#
##### ------------------------------------------------ #####
### mysql drop functions  
#
sub mysql_opensips_drop() {# pars: <database name>
	if ( $#_ != 0 ) {
		print "opensips_drop function takes two parameters!\n";
		return;
	}

	my $db = $_[0];	

	&mysql_query($db, "drop database $db");
	if ($? == 0 )	{
		print "Database was successfuly dropped!\n";
	} else {
		print "Database could not be dropped!\n";
	}

	return;
} 

### pgsql drop functions  
sub pgsql_opensips_drop() {# pars: <database name>

	if ( $#_ != 0 ) {
		print "opensips_drop function takes two params\n";
		return;
	}

	&pgsql_query("template1", "drop database \"$_[0]\";");
	
} 


sub dbtext_opensips_drop() { # pars: <database name>

	my $DB_PATH;
	
	if ( $#_ != 0 ) {
		print "opensips_drop function takes one param\n";
		return;
	}

	$DB_PATH = $_[0];

	print "DBTEXT ... erasing all files at: $DB_PATH\n";
	`rm -rf $DB_PATH`;
}


sub bdb_opensips_drop() { # pars:  <DB_PATH>

	my $TABLE;#my $DATA_DIR = $PATH_SHARE."/opensips";

	if ( $#_ != 0 ) {
		print "opensips_drop function takes one param\n";
		return;
	}
	
	if ( ! -d $_[0] ) {
		print "Directory does not exist:  $_[0]\n";
	}
	
	print "Dropping Berkeley DB database at: $_[0] ...\n";
	
	# core
	if ( -f $_[0]."/version" ) {
		foreach $TABLE ( @STANDARD_TABLES ) {
		    print "Dropping core table: $TABLE\n";
		    `rm -f $_[0]/$TABLE`;
		}
	}
	
	# presence
	if ( -f $_[0]."/presentity" ) {
		foreach $TABLE (@PRESENCE_TABLES) {
		    print "Dropping presence table: $TABLE\n";
		    `rm -f $_[0]/$TABLE`;
		}
	}
	
	if ( -f $_[0]."/presentity" ) {
		foreach $TABLE (@PRESENCE_RLS_TABLES) {
		    print "Dropping presence table: $TABLE\n";
		    `rm -f $_[0]/$TABLE`;
		}
	}

	# extra tables
	if ( -f $_[0]."/cpl" ) {
		foreach $TABLE (@EXTRA_TABLES) {
		    print "Dropping extra table: $TABLE\n";
		    `rm -f $_[0]/$TABLE`;
		}
	}

	# delete db files and directory
	`rm -rf $_[0]/__db.001`;
	`rm -rf $_[0]/__db.002`;
	`rm -rf $_[0]/__db.003`;
	`rm -rf $_[0]/__db.004`;
	`rmdir $_[0]`;
} ## end drop



###migrate functions ---only for mysql
sub mysql_migrate_db() { # 2 parameters (src_db, dst_db)

	if ( $#_ != 1 ) {
		print "migrate_db function takes 2 params\n";
		return;
	}

	my $src_db=$_[0];
	my $dst_db=$_[1];

	### acc
	&mysql_migrate_table($dst_db.".acc", "id,method,from_tag,to_tag,callid,sip_code,sip_reason,time", $src_db . ".acc", "?id,?method,?from_tag,?to_tag,?callid,?sip_code,?sip_reason,?time" );

	### missed_calls
	&mysql_migrate_table($dst_db . ".missed_calls", "id,method,from_tag,to_tag,callid,sip_code,sip_reason,time", $src_db . ".missed_calls", "?id,?method,?from_tag,?to_tag,?callid,?sip_code,?sip_reason,?time" );

	### aliases
	&mysql_migrate_table($dst_db . ".aliases", "id,username,domain,contact,expires,q,callid,cseq,last_modified,flags,cflags,user_agent", $src_db . ".aliases", "?id,?username,?domain,?contact,?expires,?q,?callid,?cseq,?last_modified,?flags,?cflags,?user_agent" );


	### dbaliases
	&mysql_migrate_table( $dst_db . ".dbaliases", "id,alias_username,alias_domain,username,domain", $src_db . ".dbaliases" ,"?id,?alias_username,?alias_domain,?username,?domain" );

	### grp
	&mysql_migrate_table( $dst_db . ".grp", "id,username,domain,grp,last_modified", $src_db . ".grp", "?id,?username,?domain,?grp,?last_modified" );

	### re_grp
	&mysql_migrate_table( $dst_db . ".re_grp", "id,reg_exp,group_id", $src_db . ".re_grp", "?id,?reg_exp,?group_id" );

	### silo
	&mysql_migrate_table( $dst_db . ".silo", "id,src_addr,dst_addr,username,domain,inc_time,exp_time,snd_time,ctype,body", $src_db . ".silo", "?id,?src_addr,?dst_addr,?username,?domain,?inc_time,?exp_time,?snd_time,?ctype,?body" );

	### domain
	&mysql_migrate_table( $dst_db . ".domain", "id,domain,last_modified", $src_db . ".domain", "?id,?domain,?last_modified" );

	### uri
	&mysql_migrate_table( $dst_db . ".uri", "id,username,domain,uri_user,last_modified", $src_db . ".uri", "?id,?username,?domain,?uri_user,?last_modified" );

	### usr_preferences
	&mysql_migrate_table($dst_db . ".usr_preferences", "id,uuid,username,domain,attribute,type,value,last_modified", $src_db . ".usr_preferences", "?id,?uuid,?username,?domain,?attribute,?type,?value,?last_modified" );

	print "In version 1.6, tables \'trusted\' and \'address\' have been joined under the name \'address\'. \n ".
	      "You have the option to migrate just one of them or both. \n".
	      "Do you want to migrate \'trusted\' table? (y|n)";
	if ( ( my $ans = <STDIN> ) =~ /[yY]$/ ) {
		### address
		&mysql_migrate_table( $dst_db . ".address", "id,ip,proto,pattern,context_info", $src_db . ".trusted", "?id,?src_ip,?proto,?from_pattern,?tag" );
	} else {
		print "Migration of table \'trusted\' to table \'address\' was skipped!"
	}

	print "Do you want to migrate \'address\' table? (y|n)";
	if ( ( my $ans = <STDIN> ) =~ /[yY]$/ ) {
		&mysql_migrate_table( $dst_db . ".address", "id,grp,ip,mask,port", $src_db . ".address", "?id,?grp,?ip_addr,?mask,?port" );
	} else {
		print "Migration of table \'address\' to table \'address\' was skipped!"
	}
	### speed_dial
	&mysql_migrate_table( $dst_db . ".speed_dial", "id,username,domain,sd_username,sd_domain,new_uri,fname,lname,description", $src_db . ".speed_dial", "?id,?username,?domain,?sd_username,?sd_domain,?new_uri,?fname,?lname,?description" );

	### gw
	&mysql_migrate_table( $dst_db . ".gw", "id,gw_name,grp_id,ip_addr,port,uri_scheme,transport,strip,tag,flags", $src_db . ".gw", "?id,?gw_name,?grp_id,?ip_addr,?port,?uri_scheme,?transport,?strip,?tag,?flags" );

	### lcr
	&mysql_migrate_table( $dst_db . ".lcr", "id,prefix,from_uri,grp_id,priority", $src_db . ".lcr", "?id,?prefix,?from_uri,?grp_id,?priority" );
	

	### pdt
	&mysql_migrate_table( $dst_db. ".pdt", "id,sdomain,prefix,domain", $src_db . ".pdt", "?id,?sdomain,?prefix,?domain" );

	### subscriber
	&mysql_migrate_table( $dst_db . ".subscriber", "id,username,domain,password,email_address,ha1,ha1b,rpid", $src_db . ".subscriber", "?id,?username,?domain,?password,?email_address,?ha1,?ha1b,?rpid" );

	### load_balancer
	&mysql_migrate_table( $dst_db . ".load_balancer", "id,group_id,dst_uri,resources,description", $src_db . ".load_balancer", "?id,?group_id,?dst_uri,?resources,?description" );

	### dialog
	&mysql_migrate_table( $dst_db . ".dialog", "id,hash_entry,hash_id,callid,from_uri,from_tag,to_uri,to_tag,caller_cseq,callee_cseq,caller_route_set,callee_route_set,caller_contact,callee_contact,caller_sock,callee_sock,state,start_time,timeout", $src_db . ".dialog", "?id,?hash_entry,?hash_id,?callid,?from_uri,?from_tag,?to_uri,?to_tag,?caller_cseq,?callee_cseq,?caller_route_set,?callee_route_set,?caller_contact,?callee_contact,?caller_sock,?callee_sock,?state,?start_time,?timeout" );

	### dispatcher
	&mysql_migrate_table( $dst_db . ".dispatcher", "id,setid,destination,flags,description", $src_db . ".dispatcher", "?id,?setid,?destination,?flags,?description" );

	### nathelper
	&mysql_migrate_table( $dst_db . ".nh_sockets", "id,rtpproxy_sock,set_id", $src_db . ".nh_sockets", "?id,?rtpproxy_sock,?set_id" );

	### dialplan
	&mysql_migrate_table( $dst_db . ".dialplan", "id,dpid,pr,match_op,match_exp,match_len,subst_exp,repl_exp,attrs", $src_db . ".dialplan", "?id,?dpid,?pr,?match_op,?match_exp,?match_len,?subst_exp,?repl_exp,?attrs" );

	### dr_gateways
	&mysql_migrate_table( $dst_db . ".dr_gateways", "gwid,type,address,strip,pri_prefix,attrs,description", $src_db . ".dr_gateways", "?gwid,?type,?address,?strip,?pri_prefix,?attrs,?description" );

	### dr_rules
	&mysql_migrate_table( $dst_db . ".dr_rules", "ruleid,groupid,prefix,timerec,priority,routeid,gwlist,description", $src_db . ".dr_rules", "?ruleid,?groupid,?prefix,?timerec,?priority,?routeid,?gwlist,?description" );

	### dr_gw_lists
	&mysql_migrate_table( $dst_db . ".dr_gw_lists", "id,gwlist,description", $src_db . ".dr_gw_lists", "?id,?gwlist,?description" );

	### dr_groups
	&mysql_migrate_table( $dst_db . ".dr_groups", "id,username,domain,groupid,description", $src_db . ".dr_groups", "?id,?username,?domain,?groupid,?description" );



	if ( $HAS_EXTRA eq "yes" ) {
		### cpl
		&mysql_migrate_table( $dst_db . ".cpl", "id,username,domain,cpl_xml,cpl_bin", $src_db . ".cpl", "?id,?username,?domain,?cpl_xml,?cpl_bin" );

		### siptrace
		&mysql_migrate_table( $dst_db . ".sip_trace", "id,time_stamp,callid,traced_user,msg,method,status,fromip,toip,fromtag,direction", $src_db . ".sip_trace", "?id,?time_stamp,?callid,?traced_user,?msg,?method,?status,?fromip,?toip, ?fromtag,?direction" );

		### imc_rooms
		&mysql_migrate_table(  $dst_db . ".imc_rooms", "id,name,domain,flag", $src_db . ".imc_rooms", "?id,?name,?domain,?flag" );

		### imc_members
		&mysql_migrate_table(  $dst_db. ".imc_members", "id,username,domain,room,flag", $src_db. ".imc_members", "?id,?username,?domain,?room,?flag" );

		### carrierroute
		&mysql_migrate_table( $dst_db . ".carrierroute", "id,carrier,domain,scan_prefix,flags,mask,prob,strip,rewrite_host,rewrite_prefix,rewrite_suffix,description", $src_db . ".carrierroute", "?id,?carrier,?domain,?scan_prefix,?flags,?mask,?prob,?strip,?rewrite_host,?rewrite_prefix,?rewrite_suffix,?description" );

		### carrierfailureroute
		&mysql_migrate_table( $dst_db . ".carrierfailureroute", "id,carrier,domain,scan_prefix,host_name,reply_code,flags,mask,next_domain,description", $src_db . ".carrierfailureroute", "?id,?carrier,?domain,?scan_prefix,?host_name,?reply_code,?flags,?mask,?next_domain,?description" );

		### route_tree
		&mysql_migrate_table( $dst_db . ".route_tree", "id,carrier", $src_db . ".route_tree", "?id,?carrier" );

		### domainpolicy
		&mysql_migrate_table(  $dst_db . ".domainpolicy", "id,rule,type,att,val,description", $src_db . ".domainpolicy", "?id,?rule,?type,?att,?val,?description" );

		### userblacklist
		&mysql_migrate_table(  $dst_db . ".userblacklist", "id,username,domain,prefix,whitelist", $src_db . ".userblacklist", "?id,?username,?domain,?prefix,?whitelist" );

		### globalblacklist
		&mysql_migrate_table(  $dst_db . ".globalblacklist", "id,prefix,whitelist,description", $src_db . ".globalblacklist", "?id,?prefix,?whitelist,?description" );

	}
}


sub mysql_migrate_table(){ # 4 paremeters (dst_table, dst_cols, src_table, src_cols)

	my $X;
	
	if ( $#_ != 3 ) {
		print "migrate_table function takes 4 parameters\n";
		return;
	}

	my $dst_table = $_[0];
	my $dst_cols = $_[1];
	my $src_table = $_[2];
	my $src_cols = $_[3];

	$src_cols=`echo $src_cols | sed s/?/$src_table./g `;

	if ( $PW eq "" ) {
		$X = system("mysql -h $DBHOST -u$DBROOTUSER -e \"INSERT INTO $dst_table ($dst_cols) SELECT $src_cols FROM $src_table\"");
		if ( $? != 0 ) {
			system("echo $X | $EGREP \"ERROR 1146\" > /dev/null");
			if ( $? != 0 ) {
				print " -- Migrating $_[2] to $_[0].....SKIPPED (no source)\n";
				return;
			}
			print "ERROR: failed to migrate $src_table to $dst_table!!!";
			print "Skip it and continue (y/n)? ";
			if ( ( my $ans = <STDIN> ) =~ /[yY]$/ ) {
				return;
			}
		}
	} else {
		$X = system("mysql -h $DBHOST -u$DBROOTUSER -p$PW -e \"INSERT INTO $dst_table ($dst_cols) SELECT $src_cols FROM $src_table\"");
		if ( $? != 0 ) {
			system("echo $X | $EGREP \"ERROR 1146\" > /dev/null");
			if ( $? != 0 ) {
				print " -- Migrating $_[2] to $_[0].....SKIPPED (no source)\n";
				return;
			}
			print "ERROR: failed to migrate $src_table to $dst_table!!!";
			print "Skip it and continue (y/n)? ";
			if ( ( my $ans = <STDIN> ) =~ /[yY]$/ ) {
				return;
			}
		}
	}

	print " -- Migrating " . $src_table . " to " . $dst_table . ".....OK\n";

}  ##migrate functions


#
##### ------------------------------------------------ #####
### dump database 
#
sub opensips_dump() {

	if ( $#_ != 1 ) {
		print "opensips_dump function takes two param\n";
		return;
	}
	$PW = &prompt_pw();
	
	if ( $DBENGINE =~ /^mysql$/ ) {
		if ( $PW eq "" ) {
			system("mysqldump -h $DBHOST -u$DBROOTUSER -c -t $_[0] > $_[1]");
			if ( $? != 0 ) {
				print "db dump failed\n";
				return;
			} else {
				print "db dump successful!\n";
				return;
			}
		} else {
                        system("mysqldump -h $DBHOST -u$DBROOTUSER -p$PW -c -t $_[0] > $_[1]");
                        if ( $? != 0 ) {
                                print "db dump failed\n";
				return;
			} else {
				print "db dump successful!\n";
				return;
			}
		}
	} elsif ( $DBENGINE =~ /^Pg$/ ) {
		system("pg_dump -h $DBHOST -U $DBROOTUSER -c $_[0] > $_[1]");
		if ( $? != 0 ) {
			print "db dump failed\n";
			return;
		}
	} elsif ( $DBENGINE =~ /^oracle$/ ) {
		#&....
		return;
	} else {
		print "Unknown database engine !!!!";
		return;
	}

	print "db dump successful\n";
	return;
}


#
##### ------------------------------------------------ #####
### restore database 
#
sub opensips_restore() { #pars: <database name> <filename>

	if ( $#_ != 1 ) {
		print "opensips_restore function takes two params\n";
		return;
	}

	if ( $DBENGINE =~ /^oracle$/ ) {
		#oracle_restore $1 $2
	} elsif ( $DBENGINE =~ /^mysql$/ ) {
		&mysql_query("$_[0] < $_[1]");
		return;
		#sql_query $1 < $2
	} elsif ( $DBENGINE =~ /^Pg$/ ) {
		&pgsql_query("$_[0] < $_[1]");
		return;
	}

	print "Database was restorered successfully\n";
}


#
##### ------------------------------------------------ #####
### database query functions 
#
sub mysql_query() {

	my $MYSQL_CMD="mysql -h $DBHOST -u$DBROOTUSER ";
	if ( $#_ > 0 ) {
		if ( $PW eq "") {
			system("$MYSQL_CMD -D $_[0] -e \"$_[1]\"");
			if ( $? != 0 ) {		
				return 1;
			} else {
				return 0;
			}
		} else {
			system("$MYSQL_CMD -p$PW -D $_[0] -e \"$_[1]\"");
			if ( $? != 0 ) {		
				return 1;
			} else {
				return 0;
			}
		}
	} elsif ( $#_ == 0 ) {
		if ( $PW eq "") {
			system ("$MYSQL_CMD -e \"$_[0]\"");
			if ( $? != 0 ) {		
				return 1;
			} else {
				return 0;
			}
		} else {
			system ("$MYSQL_CMD -p$PW -e \"$_[0]\"");
			if ( $? != 0 ) {		
				return 1;
			} else {
				return 0;
			}
		}
	}
}


sub pgsql_query() {
	my $PGSQL_CMD="psql -h $DBHOST -U $DBROOTUSER";	
	if ( $#_ gt 0 ) {
		system("$PGSQL_CMD -d $_[0] -c \"$_[1]\"");
		if ( $? != 0 ) {
			return 1;
		} else {
			return 0;
		}
	} else {
		system("$PGSQL_CMD \"$_[0]\"");
		if ( $? != 0 ) {
			return 1;
		} else {
			return 0;
		}
	}
}


#
##### ------------------------------------------------ #####
### db_ops query functions 
#
sub db_query() {

	my $query = $_[0];

		if ($DBENGINE =~  /^mysql$/) {
			&db_mysql_query($query);
		}
		elsif ($DBENGINE =~ /^Pg$/) {
			&db_pgsql_query($query);
		}
		elsif ($DBENGINE =~ /^DBTEXT$/) {
			&db_dbtext_query($query);
		}
}


sub db_ro_query(){

	my $query = $_[0];	

		if ($DBENGINE =~ /^mysql$/) {
			&db_mysql_ro_query($query);
		}
		elsif ($DBENGINE =~ /^Pg$/) {
			&db_pgsql_ro_query($query);
		}
		elsif ($DBENGINE =~ /^DBTEXT$/) {
			&db_dbtext_query($query);
		}
}


# input: sql query, optional mysql command-line params
sub db_mysql_query() {
	
	my $query = $_[0];
	
	# if password not yet queried, query it now
	#&prompt_pw(); "MySQL password for user '$DBRWUSER@$DBHOST'"
	system("$MYSQL -h $DBHOST -u$DBRWUSER \"-p$DBRWPW\" -D $DBNAME -e \"$query\";");
	if ( $? != 0 ) {		
		return 1;
	} else {
		return 0;
	}
}


# input: sql query, optional mysql command-line params
sub db_mysql_ro_query() {

	my $query = $_[0];

	system("$MYSQL -h $DBHOST -u$DBROUSER \"-p$DBROPW\" -D $DBNAME -e \"$query\" ;");
	if ( $? != 0 ) {		
		return 1;
	} else {
		return 0;
	}
}


# input: sql query, optional pgsql command-line params
sub db_pgsql_query() {

	my ( $query, $PGPASSWORD ); 
	$query = $_[0];

	print "pgsql_query: $PGSQL -A -q -t -P fieldsep=\'	\' -h $DBHOST -U $DBRWUSER $DBNAME -c \'$query\'\n";
	system("$PGSQL -A -q -t -P fieldsep=\"	\" -h $DBHOST -U $DBRWUSER \"-W$DBRWPW\" $DBNAME \ -c \"$query\"");
}


# input: sql query, optional pgsql command-line params
sub db_pgsql_ro_query() {

	my $query = $_[0];
	print "pgsql_ro_query: $PGSQL -h $DBHOST -U $DBROUSER -W $DBNAME -c '$query'\n";
	system("$PGSQL -h $DBHOST -U $DBROUSER \"-W$DBROPW\" $DBNAME -c \"$query;\"");
}


sub db_dbtext_query() {
	my $query = $_[0];
	
	#print $DBTEXTCMD." ".$query;
	system("$DBTEXTCMD","$query");
}

sub opensips_berkeley()  { # parms: <op> <arg1> <arg2>

	if ($#cmd lt 2 ) {
		&berkeley_usage();
		return;	
	} elsif ($cmd[1] =~ /^h$/) {
		&berkeley_usage();
		return;		
	}

		if ($cmd[1] =~  /(^list$)|(^ls$)/) {
			system("ls -al $DB_PATH");
		}

		elsif ($cmd[1] =~ /^cat$/) {
			&opensips_cat($cmd[2],$DB_PATH);			
		}

		elsif ($cmd[1] =~ /^swap$/) {
			&opensips_swap($cmd[2], $DB_PATH);
		}

		elsif ($cmd[1] =~ /^append$/) {
			&opensips_append($cmd[2], $cmd[3], $DB_PATH);
		}

		elsif ($cmd[1] =~ /^newappend$/) {
			#shift
			#opensips_newappend  $1 $2 $DB_PATH
			#exit $?
		}

		elsif ($cmd[1] =~ /^export$/) {
			&opensips_export($cmd[2],$DB_PATH);
		}

		elsif ($cmd[1] =~ /^migrate$/) {
			&opensips_migrate($cmd[2], $DB_PATH);
		}
	
		elsif ($cmd[1] =~ /^import$/) {
			#shift
			#opensips_import  $1 $DB_PATH
			#exit $?
		}

		else {
			&berkeley_usage();
		}
}

##
# MIGRATE (schema)
# Examine each plain-txt file in DUMP_DIR
#  (Assumes that opensips_export was already invoked)
#
# Migrate converts data from schema-old to schema-new in place.
#
# After this step is complete the IMPORT should be executed.
sub opensips_migrate() { # parms: <DUMP_DIR> [DB_PATH]

	print "db_berkeley migrate not implemented\n";
	return;
}


# cat all rows to STDOUT
sub opensips_cat() { # pars: <database name> <DB_PATH>

	if ( $#_ != 1 ) {
		print  "opensips_cat params <db> [DB_PATH]\n";
		return;
	}
	
	system("$DUMP_CMD -p -h $_[1] $_[0]");
}


##
# EXPORT existing data to plain-txt files in DUMP_DIR
# eg.  DB_PATH/version ---> DUMP_DIR/version.txt
#
# Export is used as part of a DB migration process to another 
# major version of berkeley db.
sub opensips_export() { # parms: <DUMP_DIR> [DB_PATH]

	my ($DUMP_DIR, $PATH, $TABLE);
	if ( $#_ lt 1 ) {
		print  "opensips_dump parms: <DUMP_DIR> [DB_PATH]\n";
		return;
	}
	
	$DUMP_DIR = $_[0];
	$PATH = $_[1];

	# Assert: the DB_PATH directory should already exist
	if ( ! -d $PATH ) {
		print "BerkeleyDB directory does not exist at: [$PATH]\n";
		return;
	}
	
	# Assert: DB_PATH directory should already contain table 'version'
	if ( ! -f $PATH."/version" ) {
		print "BerkeleyDB directory does not have VERSION table at: [$PATH]\n";
		return;
	}
	
	# Create dir at <DUMP_DIR> to store the exported data
	if ( ! -d  $DUMP_DIR) {
		print "creating DUMP_DIR at: [$DUMP_DIR]\n";
		`mkdir -p $DUMP_DIR`;
	} else {
		print "Cleaning out DUMP_DIR to get ready for new data\n";
		`rm -rf $DUMP_DIR/*`;
	}


	foreach $TABLE (@STANDARD_TABLES) {
	    if ( -f $PATH."/".$TABLE ) {
		    print "Exporting standard table: $TABLE\n";
		    system("$DUMP_CMD -p -h $PATH $TABLE  | perl -pe 's/^\w.*// ; s/^\s(.*)/$DUMP_DIR/' > $DUMP_DIR/$TABLE.txt");
		    
		    # Check return code to make sure the export worked ok
		    if ( $? != 0 ) {
			print "Export of standard table failed [$TABLE]\n";
			# there was a problem, but it is not something
			# we can handle here; We can deal with this at import
			# time.
		    }
	    } else {
	    	    print "Table not found: [$TABLE]\n";
	    }
	}
	
	# Dump the PRESENCE tables to plain-text files in DUMP_DIR
	foreach $TABLE (@PRESENCE_TABLES) {
	    if ( -f $PATH."/".$TABLE ) {
		    print "Exporting presence table: $TABLE\n";
		    system("$DUMP_CMD -p -h $PATH $TABLE  | perl -pe 's/^\w.*// ; s/^\s(.*)/$DUMP_DIR/' > $DUMP_DIR/$TABLE.txt");
		    if ( $? != 0 ) {
			print "Export of presence table failed [$TABLE]\n";
		    }
	    } else {
	    	    print "Table not found: [$TABLE]\n";
	    }
	}
	
	# Dump the EXTRA tables to plain-text files in DUMP_DIR
	foreach $TABLE (@EXTRA_TABLES) {
	    if ( -f $PATH."/".$TABLE ) {
		    print "Exporting extra table: $TABLE\n";
		    system("$DUMP_CMD -p -h $PATH $TABLE  | perl -pe 's/^\w.*// ; s/^\s(.*)/$DUMP_DIR/' > $DUMP_DIR/$TABLE.txt");
		    if ( $? != 0 ) {
			print "Export of extra table failed [$TABLE]\n";
		    }
	    } else {
	    	    print "Table not found: [$TABLE]\n";
	   }
	}
	
	print "All tables are now exported to DUMP_DIR: [$DUMP_DIR]\n";
	return;

}


sub opensips_swap() { # parms: <db> [DB_PATH]
	
	my ( $DB, $DBNEW, $DBOLD );

	if ( $#_ lt 1 ) {
		print "opensips_swap parms: <db> [DB_PATH]\n";
		return;
	}
	
	$DB = $_[1]."/".$_[0];
	$DBNEW = $DB.".new";
	$DBOLD = $DB.".old";
	`cp $DB $DBOLD`;
	`mv $DBNEW $DB`;
}


#####
# append process is:
# 1. copy DB_PATH/db to DB_PATH/db.new
# 2. appends contents of newdata to DB_PATH/db.new
#
sub opensips_append() { # parms: <db> <newdata> [DB_PATH]

	my ( $DB, $DBNEW );

	if ( $#_ lt 2  ) {
		print  "opensips_append parms: <db> <newdata> [DB_PATH]\n";
		return;
	}
	
	$DB = $_[2]."/".$_[0];
	$DBNEW = $DB.".new";
	if ( -e $DBNEW ) {
		`rm $DBNEW`;
	}
	
	`cp $DB $DBNEW`;
# echo "$LOAD_CMD -T -t hash -f $2 -h $3 $1.new"
	system("$LOAD_CMD -T -t hash -f $_[1] -h $_[2] $_[0].new");
	
# echo "$LOAD_CMD -r fileid -h $3 $1.new"
  	system("$LOAD_CMD -r fileid -h $_[2] $_[0].new");
}


sub opensips_newappend() { # parms: <db> <newdata> [DB_PATH]

	my ( $DB, $DBNEW, $TMPENV, $OLDPWD);

	if ( $#_ lt 2  ) {
		print  "opensips_append parms: <db> <newdata> [DB_PATH]\n";
		return;
	}
	
	$DB = $_[2]."/".$_[0];
	$DBNEW = $DB.".new";
	if ( -e $DBNEW ) {
		`rm $DBNEW`;
	}
	$TMPENV = "/tmp/sc-$$";
	&bdb_opensips_create($TMPENV);
	`cd $OLDPWD`;
	system("$LOAD_CMD -T -t hash -f $_[1] -h $TMPENV $_[0]");
	`mv $TMPENV/$_[0] $DBNEW`;
	`rm -rf $TMPENV`;
}


##
# IMPORT existing plain-txt files from DUMP_DIR to DB_PATH
# eg.  DUMP_DIR/version.txt  --> DB_PATH/version
#
# import is used as part of DB migrate to another major version of berkeley db.
# this will over-write anything in DB_PATH
sub opensips_import() { # parms: <DUMP_DIR> [DB_PATH]

	my ($TABLE);

	if ( $#_ lt 1  ) {
		print  "opensips_dump parms: <DUMP_DIR> [DB_PATH]\n";
		return;
	}
	
	# Assert: DUMP_DIR (source dir) already exists
	if ( ! -d $_[0] ) {
		print "Berkeley DUMP_DIR directory does not exist: [$_[0]]\n";
		return;
	}
	
	# Assert: DUMP_DIR directory should already contain table 'version.txt'
	if ( ! -e $_[0]."/version.txt" ) {
		print "DUMP_DIR directory does not have VERSION.txt data at: [$_[0]]\n";
		return;
	}
	
	# Assert: destination dir exists [DB_PATH]
	if ( ! -d $_[1] ) {
		print "Berkeley DB_PATH directory is being created: [$_[1]]";
		`mkdir -p $_[1]`;
	} else {
		# Wipe out the destination dir to make room for new data
		print "Berkeley DB_PATH directory is being purged at: [$_[1]]\n";
		`rm -rf $_[1]./*`;
	}
	
	# Creates STANDARD tables from plain-text files in DUMP_DIR
	foreach $TABLE (@STANDARD_TABLES) {
	    if ( -f $_[0]."/".$TABLE."txt" ) {
		    print "Importing standard table: $TABLE\n";
		    system("$LOAD_CMD -T -t hash -f $_[0]/$TABLE.txt -h $_[1] $TABLE");
		    
		    # Check return code to make sure the export worked ok
		    if ( $? != 0 ) {
			print "Import of standard table failed [$TABLE.txt]\n";
			print "Create this missing table with bdb_recover.\n";
		    }
	    } else {
	    	    print "Import data not found for table: [$TABLE.txt]\n"; 
		    print "Create this missing table with bdb_recover.\n";
	    }
	}
	

	# Creates PRESENCE tables from plain-text files in DUMP_DIR
	foreach $TABLE (@PRESENCE_TABLES) {
	    if ( -f $_[0]."/".$TABLE.".txt" ) {
		    print "Importing presence table: $TABLE\n";
		    system("$LOAD_CMD -T -t hash -f $_[0]/$TABLE.txt -h $_[1] $TABLE");
		    
		    # Check return code to make sure the export worked ok
		    if ( $? != 0 ) {
			print "Import of presence table failed [$TABLE.txt]\n";
			print "Create this missing table with bdb_recover.\n";
		    }
	    } else {
		    print "Import data not found for table: [$TABLE.txt]\n";
	    }
	}


	foreach $TABLE (@PRESENCE_RLS_TABLES) {
	    if ( -f $_[0]."/".$TABLE.".txt" ) {
		    print "Importing presence table: $TABLE\n";
		    system("$LOAD_CMD -T -t hash -f $_[0]/$TABLE.txt -h $_[1] $TABLE");
		    
		    # Check return code to make sure the export worked ok
		    if ( $? != 0 ) {
			print "Import of presence table failed [$TABLE.txt]\n";
			print "Create this missing table with bdb_recover.\n";
		    }
	    } else {
		    print "Import data not found for table: [$TABLE.txt]\n";
	    }
	}


	# Creates EXTRA tables from plain-text files in DUMP_DIR
	foreach $TABLE (@EXTRA_TABLES) {
	    if ( -s $_[0]."/".$TABLE.".txt" ) {
		    print "Importing extra table: $TABLE\n";
		    system("$LOAD_CMD -T -t hash -f $_[0]/$TABLE.txt -h $_[1] $TABLE");
		    
		    # Check return code to make sure the export worked ok
		    if ( $? != 0 ) {
			print "Import of extra table failed [$TABLE.txt]\n";
			print "Create this missing table with bdb_recover.\n";
		    }
	    } else {
		    print "Import data not found for table: [$TABLE.txt]\n";
	    }
	}
	
	print "All tables are now imported to DB_PATH: [$_[1]]\n";
	return;

}


#
##### ------------------------------------------------ #####
### Berkeley DB control functions 
#

sub bdb_select() {
	
	my ($TABLE, $db, $cursor);
	my ($key, $value) = ("", "") ;

	$TABLE = $_[0];
	
	unlink $DB_PATH;

	$db = new BerkeleyDB::Hash
              -Filename   => "$DB_PATH/$TABLE"
              #-Flags      => DB_CREATE
        or die "Cannot open file $DB_PATH: $! $BerkeleyDB::Error\n" ;
	
	# print the contents of the file 
	$cursor = $db->db_cursor() ;
	while ($cursor->c_get($key, $value, DB_NEXT) == 0)
		{ print "$key -> $value\n" }

	undef $cursor ;
	undef $db ;

}

sub bdb_select_where() {
	
	my ( $TABLE, $db, $key, $value, $result );
	
	$TABLE = $_[0];
	$key = $_[1];	

	unlink $DB_PATH;

	$db = new BerkeleyDB::Hash
              -Filename   => "$DB_PATH/$TABLE"
              #-Flags      => DB_CREATE
        or die "Cannot open file $DB_PATH: $! $BerkeleyDB::Error\n" ;

	# Check for existence of a key   
	if ( $db->db_get($key, $value) == 0 ) {
		print "$key -> $value\n\n"; 
		$result = 1;
	} else {
		$result = 0;
		print "Value does not exist in the database\n";
	}

	undef $db ;
	return $result;

}

sub bdb_insert() {

	my ($TABLE, $db, $key, $value);
	
	$TABLE = $_[0];
	$key = $_[1];
	$value =$_[2];
	
	unlink $DB_PATH;

	$db = new BerkeleyDB::Hash
              -Filename   => "$DB_PATH/$TABLE"
              #-Flags      => DB_CREATE
        or die "Cannot open file $DB_PATH: $! $BerkeleyDB::Error\n" ;



	# Add a few key/value pairs to the file
	$db->db_put($key, $value,DB_NOOVERWRITE) ;

	undef $db;

}

sub bdb_delete() {

	my ($TABLE, $db, $key);
	
	$TABLE = $_[0];
	$key = $_[1];

	unlink $DB_PATH;

	$db = new BerkeleyDB::Hash
              -Filename   => "$DB_PATH/$TABLE"
              #-Flags      => DB_CREATE
        or die "Cannot open file $DB_PATH: $! $BerkeleyDB::Error\n" ;

	# Delete a key/value pair
	$db->db_del($key) ;

	undef $db ;

}


sub bdb_update() {
	
	my ( $TABLE, $db, $key, $value, $status, $cursor, @val_array );
	
	$TABLE = $_[0];
	shift(@_);
	$key = "@_";	
	unlink $DB_PATH;

	$db = new BerkeleyDB::Hash
              -Filename   => "$DB_PATH/$TABLE"
              #-Flags      => DB_CREATE
        or die "Cannot open file $DB_PATH: $! $BerkeleyDB::Error\n" ;

	# print the contents of the file 
	$cursor = $db->db_cursor() ;
	if ( defined $key ) {
		$status = $cursor->c_get($key, $value, DB_SET);
		if ( $status == 0 ) {
			@val_array = split(" ",$value);
			if ( $cmd[0] =~ /^rpid$/) {
				if ( $cmd[1] =~ /^add$/ ) {
					if ( $#val_array == 4 ) {
						$val_array[$#val_array] = $cmd[3];						
					} elsif ( ( $#val_array == 3 ) && ( $val_array[$#val_array-1] =~ /^s*\d+$/) ) {
						$val_array[$#val_array] = $cmd[3];						
					} elsif ( ( $#val_array == 3 ) && ( $val_array[1] =~ /^s*\.@\..\.s*$/) && ($val_array[2] =~ /^s*\d+$/) && ($val_array[3] =~ /^s*\d+$/) ) {
						$val_array[$#val_array+1] = $cmd[3];						
					} else {
						$val_array[$#val_array+1] = $cmd[3];						
					}

				} elsif ( $cmd[1] =~ /^rm$/ ) {
					pop(@val_array);
				}
			} elsif ( $cmd[0] =~ /^passwd$/ ) {
				$val_array[0] = $PASS;	
			}	
		}

		$status = $cursor->c_put($key, "@val_array", DB_CURRENT);
	}

	undef $cursor ;
	undef $db ;
}


sub opensips_dr() {

	my ( @var, $address, $gwid, $type, $strip, $pri_prefix, $description, $i );

	my ( $groupid, $prefix, $timerec, $priority, $routeid, $gwlist, $ruleid );

	if ( $cmd[1] =~ /(^gateway$)/ ) {
		if ( $#cmd < 2 ) {
			print "Too few parameters!";
			&usage_dr();
			return;
		} if ( $#cmd > 7 ) {
			print "Too many parameters!";
			&usage_dr();
			return;
		}	  
	
			if ($cmd[2] =~ /^add$/) {
				$type = 0;
				$strip = 0; 
				$pri_prefix = 'NULL';
				$description = ' ';
				$i = 0;

				@var = split ("=",$cmd[3]);
				$address = $var[1];
		
				if ( ($#cmd > 3) & ($#cmd <= 7) ) { 
					$i += 4;
					while ( $i < $#cmd+1 ) {
						@var = split ("=",$cmd[$i]);
							if ($var[0] =~ /type/) {
								$type = $var[1]; 
							}
							elsif ($cmd[2] =~ /strip/) {
								$strip = $var[1];
							}
							elsif ($cmd[2] =~ /pri_prefix/) {
								$pri_prefix = $var[1];
							}
							elsif ($cmd[2] =~ /description/) {
								$description = $var[1];
							}
						$i++;
					}
				}

				#prepare query
				#if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				#	my $key = join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
				#	my $value = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
				#	&bdb_insert($DA_TABLE,$key,$value);
				#} els
				if ( $DBENGINE =~ /^DBTEXT$/ )	{
					system("$DBTEXTCMD"," INSERT INTO $DR_GW_TABLE 
						($dr_gw_table{'DR_GW_ADDRESS_COLUMN'},$dr_gw_table{'DR_GW_TYPE_COLUMN'},
						$dr_gw_table{'DR_GW_STRIP_COLUMN'},$dr_gw_table{'DR_GW_PRI_PREFIX_COLUMN'},
						$dr_gw_table{'DR_GW_DESCRIPTION_COLUMN'}) 
						VALUES (\'$address\',$type,$strip,\'$pri_prefix\',\'$description\' )");
				} else {
					$sth = $dbh->prepare( "INSERT INTO $DR_GW_TABLE 
					($dr_gw_table{'DR_GW_ADDRESS_COLUMN'},$dr_gw_table{'DR_GW_TYPE_COLUMN'},
					$dr_gw_table{'DR_GW_STRIP_COLUMN'},$dr_gw_table{'DR_GW_PRI_PREFIX_COLUMN'},
					$dr_gw_table{'DR_GW_DESCRIPTION_COLUMN'}) 
					VALUES (\'$address\',$type,$strip,\'$pri_prefix\',\'$description\' )");
					#execute the query
					$sth->execute();
					warn "Entry could not be inserted into table", $sth->errstr( ), "\n" if $sth->err( );
					$sth->finish();
	
				}				
	
			}

			elsif ($cmd[2] =~ /^rm$/) {

				@var = split("=",$cmd[3]);
				$gwid = $var[1];

				if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					my $key = $gwid;
					&bdb_delete($DR_GW_TABLE,$key);
				} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
					system("$DBTEXTCMD"," DELETE FROM $DR_GW_TABLE WHERE $dr_gw_table{'DR_GW_GWID_COLUMN'}=$gwid ");
				} else {
					$sth = $dbh->prepare(" DELETE FROM $DR_GW_TABLE WHERE $dr_gw_table{'DR_GW_GWID_COLUMN'}=$gwid ");
	
					#execute the query
					$sth->execute();
					warn "Entry could not be deleted from table", $sth->errstr( ), "\n" if $sth->err( );
					$sth->finish();

				}				

			}

			elsif ($cmd[2] =~ /^list$/) {
		
				if ( $#cmd == 2 ) {
					if ( $DBENGINE =~ /^DBTEXT$/ )	{
						system("$DBTEXTCMD"," SELECT * FROM $DR_GW_TABLE ");
					} else {
						$sth = $dbh->prepare( " SELECT * FROM $DR_GW_TABLE " );
	
		
						#execute the query
						$sth->execute( );
						warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );
	
						#Retrieve the results of a row of data and print
						print "\tQuery results:\n================================================\n";
						while ( @row = $sth->fetchrow_array( ) )  {
							print "gwid=$row[0] type=$row[1] address=$row[2] strip=$row[3] pri_prefix=$row[4] description=$row[5]\n";
						}	
						$sth->finish();
					}	
					return;	
				} elsif ( $#cmd == 3 ) {
					@var = split("=",$cmd[3]);	
				
					if ($var[0] =~ /^type$/) {
						$type = $var[1];
 
						#prepare query
				
						#if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						#	my $key = ;
						#	&bdb_select_where($DR_GW_TABLE,$key);
						#} els
						if ( $DBENGINE =~ /^DBTEXT$/ )	{
							system("$DBTEXTCMD"," SELECT * FROM $DR_GW_TABLE WHERE $dr_gw_table{'DR_GW_TYPE_COLUMN'}=$var[1] ");
						} else {
							$sth = $dbh->prepare( " SELECT * FROM $DR_GW_TABLE WHERE $dr_gw_table{'DR_GW_TYPE_COLUMN'}=$var[1]");	
							#execute the query
							$sth->execute( );
							warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );
		
							#Retrieve the results of a row of data and print
							print "\tQuery results:\n================================================\n";
							while ( @row = $sth->fetchrow_array( ) )  {
								print "gwid=$row[0] type=$row[1] address=$row[2] strip=$row[3] ".
								      " pri_prefix=$row[4] description=$row[5]\n";
							}	
							$sth->finish();
						}		
					}
					elsif ($var[0] =~ /^address$/) {

						#prepare query
						#if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						#	my $key = join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
						#	&bdb_select_where($DA_TABLE,$key,$value);
						#} els
						if ( $DBENGINE =~ /^DBTEXT$/ )	{
						    system("$DBTEXTCMD"," SELECT * FROM $DR_GW_TABLE WHERE  $dr_gw_table{'DR_GW_ADDRESS_COLUMN'}=\'$var[1]\' ");		
						} else {
						    $sth = $dbh->prepare( " SELECT * FROM $DR_GW_TABLE WHERE $dr_gw_table{'DR_GW_ADDRESS_COLUMN'}=\'$var[1]\' " );
	
	 					    #execute the query
						    $sth->execute( );
						    warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );
		
						    #Retrieve the results of a row of data and print
						    print "\tQuery results:\n================================================\n";
						    while ( @row = $sth->fetchrow_array( ) )  {
						    	print "gwid=$row[0] type=$row[1] address=$row[2] strip=$row[3] ".
							      " pri_prefix=$row[4] description=$row[5]\n";
							}	
						$sth->finish();
						}		
					}
				}
			}

			elsif ($cmd[2] =~ /^h$/) {
				&usage_dr();
			}

			else {print "Not an droute option!!\n"};

		} elsif ( $cmd[1] =~ /(^rules$)/ ) {

			if ( $#cmd lt 2 ) {
				print "Too few parameters!";
				&usage_dr();
				return;
			} elsif ( $#cmd gt 9 ) {
				print "Too many parameters!";
				&usage_dr();
				return;
			}

				if ($cmd[2] =~ /^add$/) {
					$groupid = ' ';
				    	$prefix = ' ';
					$timerec = ' ';
					$priority = 0;
					$routeid = 0; 
					$gwlist = ' ';
					$description = ' ';
					$i = 0;

					@var = split ("=",$cmd[3]);
					$gwlist = $var[1];
			
					if ( ($#cmd > 3) & ($#cmd <= 9) ) { 
						$i += 4;		
						while ( $i < $#cmd+1 ) {
							@var = split ("=",$cmd[$i]);				
							if ($var[0] =~ /^groupid$/) {
								$groupid = $var[1]; 
							}
							elsif ($var[0] =~ /^prefix$/) {
								$prefix = $var[1];
							}
							elsif ($var[0] =~ /^timerec$/) {
								$timerec = $var[1];
							}
							elsif ($var[0] =~ /^priority$/) {
								$priority = $var[1];
							}
							elsif ($var[0] =~ /^routeid$/) {
								$routeid = $var[1];
							}
							elsif ($var[0] =~ /^description$/) {
								$description = $var[1];
							}
							$i++;
						}	
					}

					#prepare query
					#if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
					#	my $key = join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
					#	my $value = join(" ",$OSIPSUSER,$OSIPSDOMAIN);
					#	&bdb_insert($DA_TABLE,$key,$value);
					#} els
					if ( $DBENGINE =~ /^DBTEXT$/ )	{
						system("$DBTEXTCMD"," INSERT INTO $DR_RULES_TABLE 
								($dr_rules_table{'DR_RULES_GWLIST_COLUMN'},$dr_rules_table{'DR_RULES_GROUPID_COLUMN'},
								$dr_rules_table{'DR_RULES_PREFIX_COLUMN'},$dr_rules_table{'DR_RULES_TIMEREC_COLUMN'},
								$dr_rules_table{'DR_RULES_PRIORITY_COLUMN'},$dr_rules_table{'DR_RULES_ROUTEID_COLUMN'},
								$dr_rules_table{'DR_RULES_DESCRIPTION_COLUMN'}) 
								VALUES (\'$gwlist\',\'$groupid\',\'$prefix\',\'$timerec\',$priority,$routeid,\'$description\' )");
					} else {
						$sth = $dbh->prepare( " INSERT INTO $DR_RULES_TABLE 
								($dr_rules_table{'DR_RULES_GWLIST_COLUMN'},$dr_rules_table{'DR_RULES_GROUPID_COLUMN'},
								$dr_rules_table{'DR_RULES_PREFIX_COLUMN'},$dr_rules_table{'DR_RULES_TIMEREC_COLUMN'},
								$dr_rules_table{'DR_RULES_PRIORITY_COLUMN'},$dr_rules_table{'DR_RULES_ROUTEID_COLUMN'},
								$dr_rules_table{'DR_RULES_DESCRIPTION_COLUMN'}) 
								VALUES (\'$gwlist\',\'$groupid\',\'$prefix\',\'$timerec\',$priority,$routeid,\'$description\' )" );
						#execute the query
						$sth->execute();
						warn "Entry could not be inserted into table", $sth->errstr( ), "\n" if $sth->err( );
						$sth->finish();

					}				

						
				}

				elsif ($cmd[2] =~ /^rm$/) {

					@var = split("=",$cmd[3]);
					$ruleid = $var[1];

					if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
						my $key = $ruleid;
						&bdb_delete($DR_RULES_TABLE,$key);
					} elsif ( $DBENGINE =~ /^DBTEXT$/ )	{
						system("$DBTEXTCMD"," DELETE FROM $DR_RULES_TABLE WHERE $dr_rules_table{'DR_RULES_RULEID_COLUMN'}=\'$ruleid\' ");
					} else {
						$sth = $dbh->prepare(" DELETE FROM $DR_RULES_TABLE WHERE $dr_rules_table{'DR_RULES_RULEID_COLUMN'}=\'$ruleid\' ");
	
						#execute the query
						$sth->execute();
						warn "Entry could not be deleted from table", $sth->errstr( ), "\n" if $sth->err( );
						$sth->finish();

					}		
				}

				elsif ($cmd[2] =~ /^list$/) {
					if ( $#cmd == 2 ) {
						if ( $DBENGINE =~ /^DBTEXT$/ )	{
							system("$DBTEXTCMD"," SELECT * FROM $DR_RULES_TABLE ");
						} else {
							$sth = $dbh->prepare( " SELECT * FROM $DR_RULES_TABLE " );
	
		
							#execute the query
							$sth->execute( );
							warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );

							#Retrieve the results of a row of data and print
							print "\tQuery results:\n================================================\n";
							while ( @row = $sth->fetchrow_array( ) )  {
								print "ruleid=$row[0] groupid=$row[1] prefix=$row[2] timerec=$row[3] priority=$row[4] routeid=$row[5] gwlist=$row[6] description=$row[7]\n";
						}	
							$sth->finish();
						}	
						return;	
					} elsif ( $#cmd == 3 ) {
						@var = split("=",$cmd[3]);	
				
						if ($var[0] =~ /^gwlist$/) {
								$gwlist = $var[1];
		 
								#prepare query
				
								#if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
								#	my $key = ;
								#	&bdb_select_where($DR_GW_TABLE,$key);
								#} els
								if ( $DBENGINE =~ /^DBTEXT$/ )	{
									system("$DBTEXTCMD"," SELECT * FROM $DR_RULES_TABLE WHERE $dr_rules_table{'DR_RULES_GWLIST_COLUMN'}=\'$var[1]\' ");
								} else {

									$sth = $dbh->prepare( " SELECT * FROM $DR_RULES_TABLE WHERE 
												$dr_rules_table{'DR_RULES_GWLIST_COLUMN'}=\'$var[1]\'");	
		
									#execute the query
									$sth->execute( );
									warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );
		
									#Retrieve the results of a row of data and print
									print "\tQuery results:\n================================================\n";
									while ( @row = $sth->fetchrow_array( ) )  {
										print "ruleid=$row[0] groupid=$row[1] prefix=$row[2] ".
										      "timerec=$row[3] priority=$row[4] routeid=$row[5] ".
										      "gwlist=$row[6] description=$row[7]\n";
									}	
									$sth->finish();
								}		
							}
							elsif ($var[0] =~ /^groupid$/) {
								$groupid = $var[1];
								#prepare query
								#if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
								#	my $key = join(" ",$TMP_OSIPSUSER,$TMP_OSIPSDOMAIN);
								#	&bdb_select_where($DA_TABLE,$key,$value);
								#} els
								if ( $DBENGINE =~ /^DBTEXT$/ )	{
									system("$DBTEXTCMD"," SELECT * FROM $DR_RULES_TABLE WHERE 
									$dr_rules_table{'DR_RULES_GROUPID_COLUMN'}=\'$var[1]\' ");
								} else {

									$sth = $dbh->prepare( " SELECT * FROM $DR_RULES_TABLE WHERE 
												$dr_rules_table{'DR_RULES_GROUPID_COLUMN'}=\'$var[1]\'");	
		
									#execute the query
									$sth->execute( );
									warn "Retrieving data from table failed", $sth->errstr( ), "\n" if $sth->err( );
		
									#Retrieve the results of a row of data and print
									print "\tQuery results:\n================================================\n";
									while ( @row = $sth->fetchrow_array( ) )  {
										print "ruleid=$row[0] groupid=$row[1] prefix=$row[2] ".
										      "timerec=$row[3] priority=$row[4] routeid=$row[5] ".
										      "gwlist=$row[6] description=$row[7]\n";
									}	
									$sth->finish();
								}		
							}
					}

				}

				elsif ($cmd[2] =~ /^h$/) {
					&usage_dr();
		     	        }
		 		
				else {print "Not an dr_rules option!!\n"};

		}
}
