#!/usr/bin/perl 
#
#$Id: osipsconsole 4787 2008-10-10 16:18:28Z iulia_bublea $
#
# Copyright (C) 2008 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 Switch;
use DBI;
use POSIX;
use Frontier::RPC2;
use IO::Socket;
use Socket;
use String;
use Net::IP;
use BerkeleyDB;

####variables that need to be changed

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

####path to the directory where opensips binaries are...

my $PATH_BIN = "/usr/sbin/";
my $PATH_CTLRC = "/etc/opensips/";
my $PATH_ETC = "/etc/opensips/";


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


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



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

my $ETCDIR = " ";

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

my $name = "fifo_reply";
my $path = "/tmp/" . $name;
my $PID_FILE = "";
my $DBENGINE = "";
my $SIP_DOMAIN = "";
my $VERIFY_ACL = 0;
my @ACL_GROUPS;
my $DBNAME = "";
my $DBHOST = "";
my $DBRWUSER = "";
my $DBROUSER = "";
my $DBROPW = "";
my $DBROOTUSER = "";
my $DBRWPW = "";
my $CTLENGINE = "";
	
my @arr;
my $var_name;
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 @OSIPS ;
my $CTLENGINELOADED = 0;
my $STORE_PLAINTEXT_PW;
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', 'trusted', 'address', 'missed_calls', 'usr_preferences', 'aliases', 'silo', 'dialog', 'dispatcher', 'dialplan');

my @STANDARD_MODULES = ('standard', 'acc', 'lcr', 'domain', 'group', 'permissions', 'registrar', 'usrloc', 'msilo', 'alias_db', 'uri_db',
				     'speeddial', 'avpops', 'auth_db', 'pdt', 'dialog', 'dispatcher', 'dialplan' );
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 = "/usr/local/lib/opensips/opensipsctl";
my $DBTEXTCMD = $MYLIBDIR."/dbtextdb/dbtextdb.py";

# path to the database schemas
my $DB_SCHEMA = "";
my $DATA_DIR = "/usr/local/share/opensips";
my $DBTEXT_PATH = "/usr/local/share/opensips/dbtext/opensips";
# path to the db_berkeley database
my $DB_PATH;
if ( $DB_PATH eq "" ) {
	$DB_PATH = "/usr/local/etc/opensips/db_berkeley";
}
my $DUMP_CMD;
my $LOAD_CMD;
my $PW = "";

my $ALIAS_DB_EXISTS = "";
my $ALIAS_UL_EXISTS = "";
 
my $DBENGINELOADED;
my $PASS = "";
my $HA1;
my $HA1B;
my $sth;
my $dbh;
my @row;

my $RET;
my $RES;
my $result = 0;
my $cmd_fifo;

my $CHROOT_DIR;
my $input;
my $response;
my $CHECK_SUB;
my $USRLOC_TABLE;
my @content;	
my $line;	
my @list;

####CLI control variables
my $term = Term::ReadLine->new("OpenSIPS");
my $OUT = $term->OUT() ||\*STDOUT;
my @cmd;
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'),	
		    'trusted'=> ('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'),
		    'migrate'=>'migrate',
		    'copy'=>'copy',
		    'backup'=>'backup',
		    'restore'=>'restore',
		    'create'=>'create',
		    'presence'=>'presence',
		    'extra'=>'extra',
		    'drop'=>'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 @output;
my $attribs = $term->Attribs;
my $command;


#
##### ------------------------------------------------ #####
###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;
				switch ($CTLENGINE) {
					case (/^FIFO$/) {
						$OSIPS_FIFO = $list[1];
					}
					case(/^UNIXSOCK$/){
						$OSIPS_UNIXSOCK = $list[1];
						$OSIPSUNIX = "opensipsunix";
					}			
					case(/^UDP$/){			
						$OSIPSIP = $list[1];
						$OSIPS_PORT = $list[2];
					}
					case(/^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/ ) {
			 	my $str = $arr[1]; 
				$DBNAME = new String($str);
			}
		}


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

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

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

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

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

		if ( $DBRWPW eq "" ) {
			if ( $arr[0] =~ /^\s*DBRWPW/ ) {
			 	my $str = $arr[1]; 
				$DBRWPW = new String($str);
			}			
		}
		
		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_1_4';
}
	
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";
}

if ( ! $STORE_PLAINTEXT_PW ) {
	$STORE_PLAINTEXT_PW = 1; 
}


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

if ( !-z $ETCDIR ) {
	$ETCDIR="/usr/local/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'
		);


# trusted table
my $TRUSTED_TABLE = " ";
if ( !-z $TRUSTED_TABLE ) {
	$TRUSTED_TABLE = 'trusted';
}
my %trusted_table = ('TRUSTED_SRC_IP_COLUMN' => 'src_ip',
		     'TRUSTED_PROTO_COLUMN' => 'proto',
		     'TRUSTED_FROM_PATTERN_COLUMN' => 'from_pattern',
		     'TRUSTED_TAG_COLUMN' => 'tag'
		    );


# 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";
}


#trusted
sub usage_trusted() {
	print " -- command 'add|dump|reload|rm|show' - manage trusted\n" .
	      "trusted show ...................... show db content\n" .
	      "trusted dump ...................... show cache content\n" .
	      "trusted reload .................... reload db table into cache\n" .
	      "trusted add <src_ip> <proto> <from_pattern> <tag>\n" .
		           "\t\t\t....................... add a new entry\n" .
		           "\t\t\t....................... (from_pattern and tag are optional arguments)\n" .
	      "trusted rm <src_ip> ............... remove all entries for the given src_ip\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";
}


#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 "usage:create <db name or db_path, optional> .....(creates a new database)\n" .
      "\tdrop <db name or db_path, optional> .......(!entirely deletes tables!)\n" .
      "\treinit <db name or db_path, optional> .....(!entirely deletes and than re-creates tables!)\n" .
      "\tbackup <file> .................................(dumps current database to file)\n" . 
      "\trestore <file> ................................(restores tables from a file)\n" .
      "\tcopy <new_db> .................................(creates a new db from an existing one)\n" .
      "\tmigrate <old_db> <new_db> .....................(migrates DB from 1.2 to 1.3, not implemented yet!)\n" .
      "\tpresence ......................................(adds the presence related tables)\n" . 
      "\textra .........................................(adds the extra tables)\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() {
#COMMAND=`basename $0`

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> [<type>] [<strip>] [<pri_prefix>] [<description>].....\n" .
 	      		    "\t\t\t\t\t\t\t..........................adds new route\n" .
 	      "dr gateway rm <gwid>................................removes route\n" . 
 	      "dr gateway list <type>..............................lists route(s)\n" .
 	      "dr gateway list <address>...........................lists route(s)\n" .
 	      "dr gateway list ....................................lists all routes\n" .
	      "dr gateway h........................................droute help\n" .
 	      "dr rules add <gwlist> [<groupid>][<prefix>][<timerec>][<priority>][<routeid>][<description>].....\n" .
		            "\t\t\t\t\t\t\t\t.............adds new rule(s)\n" .
 	      "dr rules rm <ruleid> .................................removes rules\n" .
 	      "dr rules list...............................lists rules(s)\n" .
 	      "dr rules list <gwlist>...............................lists rules(s)\n" .
 	      "dr rules list <groupid>...............................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(){

	my @fields;
	@fields = split(":",@_);
	$HA1 = $fields[0];
	return;
}


# params: user, realm, password
# output: HA1B
sub gen_ha1b() {

	my @fields;
	@fields = split("@",@_);
	$HA1B = $fields[0];
	return;
}



# params: user, realm, password
# output: PHPLIB_ID
sub gen_phplib_id()
{
	my $NOW=`date`;
	my $PHPLIB_ID=`echo -n "$1$2:$3:$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=$_[0];
	my $COL=$_[1];
	my $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;
	}		


}
 
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 db_load() {
	if ( $DBENGINE eq "" ) {
		print "database engine not specified, please setup one in the config script";
		$DBENGINELOADED = 0;
	} else {
	
		switch ( $DBENGINE ) {
			case (/(^mysql$)|(^MYSQL$)/) {
				print "Used database is mysql\n";
				if ( -d $DATA_DIR."/mysql" ) {
					$DB_SCHEMA=$DATA_DIR."/mysql";
				} else {
					$DB_SCHEMA="./mysql";
				}
				#Connect to the database.
				$dbh = DBI->connect("DBI:$DBENGINE:database=$DBNAME;host=$DBHOST",
				                 "$DBRWUSER", "$DBRWPW",
						 {'PrintError' => 0} ) or die "Database connection failed.";
				$DBENGINELOADED = 1;
			}

			case (/^oracle$/) {
				print "Used database is Oracle\n";
				if ( -d $DATA_DIR."/oracle" ) {
					$DB_SCHEMA=$DATA_DIR."/oracle";
				} else {
					$DB_SCHEMA="./oracle";
				}
				#Connect to the database.
				$dbh = DBI->connect("DBI:$DBENGINE:database=$DBNAME;host=$DBHOST",
				                 "$DBRWUSER", "$DBRWPW",
						 {'PrintError' => 0} ) or die "Database connection failed.";
				$DBENGINELOADED = 1;

			}

			case (/^Pg$/) {
				print "Used database is PostgreSQL\n";
				if ( -d $DATA_DIR."/postgres" ) {
					$DB_SCHEMA=$DATA_DIR."/postgres";
				} else {
					$DB_SCHEMA="./postgres";
				}
				$DBROOTUSER = "postgres";
				#Connect to the database.
				$dbh = DBI->connect("DBI:$DBENGINE:database=$DBNAME;host=$DBHOST",
				                 "$DBRWUSER", "$DBRWPW",
						 {'PrintError' => 0} ) or die "Database connection failed.";
				$DBENGINELOADED = 1;
			}

			case (/^DBTEXT$/) {
				print "Used database is DBTEXT\n";
				if ( -d $DATA_DIR."/dbtext/opensips" ) {
					$DB_SCHEMA="$DATA_DIR/dbtext/opensips";
				} else {
					$DB_SCHEMA="./dbtext/opensips";					
				}
				$ENV{DBTEXT_PATH} = $DBTEXT_PATH;
				$DBENGINELOADED = 1;
			}

			case (/^DB_BERKELEY$/) {
				print "Used database is DB_BERKELEY\n";
				if ( -d $DATA_DIR."/db_berkeley/opensips" ) {
					$DB_SCHEMA="$DATA_DIR/db_berkeley/opensips";
				} else {
					$DB_SCHEMA="./db_berkeley/opensips";
				}
				$ENV{PATH} = $DB_PATH;
				$DBENGINELOADED = 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() {

	switch ($cmd[0]) {		
		case (/^acl$/)	 				{ &db_load(); system(&opensips_acl()); }
		case (/^add$/)  				{ &db_load(); system(&subscriber()); }
		case (/^avp$/)	 				{ &db_load(); system(&avpops()); }	
		case (/(^aliasdb$)|(^alias_db$)/)		{ &db_load(); system(&alias_db()); }
		case (/^cr$/) 					{ &db_load(); system(&opensips_cr()); }
		case (/^dialplan$/)                     	{ &db_load(); system(&opensips_dialplan()); }
		case (/^db$/)	 				{ &db_load(); system(&db_ops()); }
		case (/^dispatcher$/) 				{ &db_load(); system(&opensips_dispatcher()); }
		case (/^domain$/) 				{ &db_load(); system(&domain()); }
		case (/(^fifo$)|(^unixsock$)|(^udp$)|(^xmlrpc$)/){ &mi_comm(); } 
		case (/^lcr$/) 					{ &db_load(); system(&opensips_lcr()); } 
		case (/(^moni$)|(^monitor$)|(^con$)|(^console$)/){ &mi_comm_monitor(); }
		case (/^ping$/)		 			{ system(&options_ping()); }
		case (/^ps$/)					{ system(&opensips_ps()); }
		case (/^passwd$/)		      		{ &db_load(); system(&subscriber()); }		
		case (/^online$/)				{ &opensips_online(); }
		case (/^rpid$/)		 			{ &db_load(); system(&opensips_rpid()); }
		case (/^restart$/)		   		{ system(&opensips_restart()); }		
		case (/^rm$/)			      		{ &db_load(); system(&subscriber()); }
		case (/(^speeddial$)|(^speed_dial$)/)		{ &db_load(); system(&speeddial()); }
		case (/^start$/)		    		{ system(&opensips_start()); }	
		case (/^stop$/)			      		{ system(&opensips_stop()); }
		case (/^tls$/)	 	  			{ &db_load(); system(&tls_ca()); }
		case (/^trusted$/)		 		{ &db_load(); system(&trusted()); }
		case (/(^ul$)|(^alias$)|(^usrloc$)/)	  	{ system(&opensips_usrloc()); }		
		case (/^version$/)				{ system(&opensips_version()); }
		case (/(^extra$)|(^migrate$)|(^copy$)|(^backup$)|(^restore$)|(^create$)|(^presence$)|(^drop$)|(^reinit$)|(^bdb$)|(^db_berkeley$)|(^cat$)/) {system(&opensips_db()); }
		case (/^dr$/)				      	{ &db_load(); system(&opensips_dr()); }
		case (/^help$/)				      	{ system(&opensips_help()); }
		else 						{ print " unknown command!!!\n"; }

			
	}
	return;

}




#all
##### ------------------------------------------------ #####
#
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_trusted();
        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 {
		switch ($cmd[1]) {
			case (/^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;
				}
			}
			case (/^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();
					} 
	
				}

			}
			case (/^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;
				}			
			
			}
			case (/^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;
	} 
		switch ( $cmd[1] ) {
			case /^h/ {
				&usage_rpid();
				return;
			}

			case (/^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();
					}
				 }
			}
 
			case (/^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();
					}
				 }
			} 
			case (/^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;
	}

	switch ($cmd[1]) {

		case (/^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(); 

			}
			
		}

		case (/^reload$/) {
			&mi_comm("lcr_reload");			
			
		}
	
		case (/^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');			
		}
		

		case (/^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');			
		}
		
		case (/^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');		
			
		}

		case (/^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');			
		}

		case (/^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;
	}
	switch ($cmd[0]) {

		case (/^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 ( $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\",\"$cmd[2]\", null) " );
			} 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'} ) 
							VALUES (\"$OSIPSUSER\",\"$OSIPSDOMAIN	\",\"$HA1\",\"$HA1B\",\"$cmd[2]\") " );
	
				#execute the query
				$sth->execute( );

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

		case (/^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;
				}

				system(&credentials( $cmd[1], $cmd[2] ));

				if ( $STORE_PLAINTEXT_PW == 1 ) {
					$PASS = $cmd[2];
				} else {
					$PASS = "";
				}

			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();
				}
			}
 						
		}
		case (/^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;
	}

	switch ($cmd[1]) {
		case (/^h$/) {
			&usage_usrloc();
			return;
		}
		case (/^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();
			}

		}

		case (/^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));
			
		}

		case (/^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;
	}	

	
	switch ( $cmd[1] ) {
		case (/^reload$/) {

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

			if ( $#cmd gt 1 ) {
				print "Too many parameters\n!";
				&usage_domain();
				return;
			}
			
			&require_ctlengine();
			&mi_comm('domain_dump');
		}
		
		case (/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(); 			
			}

		}

		case (/^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();
			}
		}
		
		case (/^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";

		}
	
		case (/^h$/) {
			&usage_domain();
		}

		else {

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

		}


	}
}



#
##### ------------------------------------------------ #####
### trusted 
#
sub trusted() {
	if ( $#cmd lt 1 ) {
		print "Too few parameters!\n";
		&usage_trusted();
		return;
	}	
	
	if ( $#cmd gt 5 ) {
		print "Too many parameters!\n";
		&usage_trusted();
		return;
	}	

	switch ($cmd[1]) {
		case (/^reload$/) {

			&require_ctlengine();
			&mi_comm('trusted_reload');

		}
		
		case (/^dump$/) {
		
			&require_ctlengine();
			&mi_comm('trusted_dump');

		}	

		case (/^show$/) {
		
			&require_dbengine();

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				&bdb_select($TRUSTED_TABLE);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD","SELECT * FROM $TRUSTED_TABLE");
			} else {
				$sth = $dbh->prepare ( "SELECT * FROM $TRUSTED_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(); 
			}			
					
	
		}	

		case (/^add$/) {
			
			my $PATTERN;

			&require_dbengine();

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


			if ( $#cmd gt 5 ) {
				print "Too many parameters!\n";
				&usage_trusted();
				return;
			}
	
			if ( &is_value_in_db( $TRUSTED_TABLE, $trusted_table{'TRUSTED_SRC_IP_COLUMN'}, $cmd[2] ) != 0 ) {
				print $cmd[2] . " already in $TRUSTED_TABLE table\n";
				return;
			}

			if ( $cmd[3] !~ /^any$|udp$|tcp$|tls$|sctp$|none$/ ) {
				print "unknown protocol\n";
				return;
			}
			
			if ( ! -z $cmd[4] ) {
				
				$PATTERN = $cmd[4];
						
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				my $value = join(" ",$cmd[3],$PATTERN, $cmd[5]);
				&bdb_insert($TRUSTED_TABLE,$key,$value);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD", " INSERT INTO $TRUSTED_TABLE 
						( $trusted_table{'TRUSTED_SRC_IP_COLUMN'}, $trusted_table{'TRUSTED_PROTO_COLUMN'}, 
						$trusted_table{'TRUSTED_FROM_PATTERN_COLUMN'},$trusted_table{'TRUSTED_TAG_COLUMN'} ) 
						VALUES (\'$cmd[2]\', \'$cmd[3]\', \'$PATTERN\', \'$cmd[5]\') " );
			} else {			
				$sth = $dbh->prepare( " INSERT INTO $TRUSTED_TABLE 
							( $trusted_table{'TRUSTED_SRC_IP_COLUMN'}, $trusted_table{'TRUSTED_PROTO_COLUMN'}, 
							$trusted_table{'TRUSTED_FROM_PATTERN_COLUMN'},$trusted_table{'TRUSTED_TAG_COLUMN'} ) 
							VALUES (\'$cmd[2]\', \'$cmd[3]\', \'$PATTERN\', \'$cmd[5]\') " );
	
				#execute the query
				$sth->execute( );

				warn "Introducing the new user " . $cmd[1] . " to the database failed", $sth->errstr( ), "\n" if $sth->err( );
				$sth->finish();
			
				print "execute 'trusted reload' to synchronize cache and database\n";
			}
		}

		case (/^rm$/) {

			&require_dbengine();
			if ( $#cmd lt 2 ) {
				&usage_trusted();
				return;
			}

			if ( $DBENGINE =~ /^DB_BERKELEY$/ ) {
				my $key = $cmd[2];
				&bdb_delete($TRUSTED_TABLE,$key);								
			} elsif ( $DBENGINE =~ /^DBTEXT$/ ) {
				system ("$DBTEXTCMD", "DELETE FROM $TRUSTED_TABLE 
					      WHERE $trusted_table{'TRUSTED_SRC_IP_COLUMN'} = \'$cmd[2]\'" );
			} else {				
				$sth = $dbh->prepare("DELETE FROM $TRUSTED_TABLE 
						      WHERE $trusted_table{'TRUSTED_SRC_IP_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();
			}

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

		case (/^h$/) {
		
			&usage_trusted();
			
		}

		else {

			print "trusted -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();
	
	switch ($cmd[1]) {
	
		case (/^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(); 		
			}

		}	

		case (/^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');			

		}	

		case (/^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');				

		}	

		case (/^reload$/) {
	
			&mi_comm('ds_reload');						

		}	

		case (/^dump$/) {
	
			&mi_comm('ds_list');
		}	

		case (/^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();

	switch ( $cmd[1] ) {
		
		case (/^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(); 	
			}
		}

		case (/^reload$/) {

			&mi_comm('cr_reload_routes');
		
		}

		case (/^dump$/) {

			&mi_comm('cr_dump');
		
		}

		case (/^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";

		
		}

		case (/^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";
		
		}

		case (/^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";

		
		}

		case (/^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";

		}

		case (/^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();



	switch ($cmd[1]) {  
		case (/(^h$)|(^help$)/) {
			&usage_alias_db();
			return;
		}
		case (/^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;
			}

		}

		case (/^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();
			}

		}

		case (/^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();

			}				
		}
		
		case (/^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) {
		switch ($CTLENGINE) {
			case (/^FIFO$/) {
			
				&fifo_cmd($argument);	

			}

			case (/^UNIXSOCK$/) {
	
				&unixsock_cmd($argument);

			}

			case (/^UDP$/) {
	
				&udp_cmd($argument);

			}

			case (/^XMLRPC$/) {
	
				&xmlrpc_cmd($argument);

			}

		}	
		
	} else {
		switch ( $cmd[0] ) {	
			case (/^fifo$/) {
			
				&fifo_cmd();	
				

			}

			case (/^unixsock$/) {
	
				&unixsock_cmd();

			}

			case (/^udp$/) {
	
				&udp_cmd();

			}

			case (/^xmlrpc/) {
	
				&xmlrpc_cmd();

			}

		}	

	}
	
}


sub mi_comm_monitor(){
	switch ($CTLENGINE) {
		case (/^FIFO$/) {
			
			&fifo_opensips_monitor();	

		}

		case (/^UNIXSOCK$/) {
	
			&unixsock_opensips_monitor();

		}

		case (/^UDP$/) {
	
			&udp_opensips_monitor();

		}

		case (/^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] . ":" . $name . "\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] . ":" . $name . "\n" . $arg_list;

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

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

}


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

		return;
}


sub write_read_fifo() {

	$OSIPS_FIFO = $_[0];
	$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 $path ) {

		    unlink $path;

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

	 }
	
	`chmod a+rw $path`;

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

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

	shift(@fifo_line);
		
	close (FIFO);
	
	unlink $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;

	}

	$str = new String($OSIPSIP);
	$MAXLEN = 2048;

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

	$portaddr = sockaddr_in($OSIPS_PORT, inet_aton($str));

	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;
	}
	
	switch ($cmd[1]) {
	
		case (/^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";


		}	

		case (/^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";

		}

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

					case (/^-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]\'";
							}
						}			

					}

					case (/^-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]\'";
						}

					}

					case (/^-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]\'";
						}

					}

					case (/^-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();	
			}


		}	

		case (/^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();
			}
		
		}	

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


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

					case (/^-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]\'";
							}
						}			

					}

					case (/^-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]\'";
						}

					}

					case (/^-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]\'";
						}

					}

					case (/^-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();
			}		
		}	

		case (/^(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();

	switch ($cmd[1]) {

		case (/(^exec$)|(^query$)/) {
			if ( $#cmd lt 2 ) {
				print "Missing query parameter\n";
				return;
			}			

			&db_query($cmd[2]);

		}

		case (/(^roexec$)|(^roquery$)/) {
			if ( $#cmd lt 2 ) {
				print "Missing query parameter\n";
				return;
			}
			&db_ro_query($cmd[2]);
		}
		case (/^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);
			
		}
		case (/^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);
		}
		case (/^show$/) {
			if ( $#cmd != 2 ) {
				print "Missing table parameter\n";
				return;
			}

			&db_ro_query("SELECT * FROM $cmd[2]");
			
		}
		case (/^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();
	
	switch ($cmd[1]) {

		case (/^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";				
					}	
				}
			} 

		}	

		case (/^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";				
				}	
			}	

		}	

		case (/^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();
			}
		}	

		case (/^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();
			}
		}	

		case (/^(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;
	}
	
	switch ( $cmd[1] ) {
	
		case (/^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();
				}	

			}			


		}

		case (/^reload$/) {

			&mi_comm('dp_reload');			

		}

		case (/^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];

			switch ($DIALPLAN_MATCH_OP) {
				case (/^equal$/) {
					$DIALPLAN_MATCH_OP = 0;
				}
				case (/^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');
		}


		case (/^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');			
		}

		case (/^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');			

		}

		case (/^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');			

		}

		case (/^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() {

	switch ($cmd[0]) {

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

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

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

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

		case /(^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 != 1 ) {
				&usage_db();
				return;
			}

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

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

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

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

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

		}

		case /(^restore$)/{

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

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

		case /(^create$)/{

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

		case /(^presence$)/{

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

			&presence_create($cmd[1]);
		}

		case /(^extra$)/ {

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

			&extra_create($cmd[1]);
		}

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

		case /(^reinit$)/{

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

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

			&opensips_drop($cmd[1]);

			&opensips_create($cmd[1]);

		}

		case /(^bdb$)|(^db_berkeley$)/{
			&opensips_berkeley();
		}

		else {
			print "Unknown command\n";
		}

	}
}

sub opensips_create() {

	my $db = $_[0];

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

}

sub presence_create() {

	my $db = $_[0];

	switch ($DBENGINE) {

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

	}
}

sub extra_create() {

	my $db = $_[0];

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

	}
}

sub opensips_drop() {

	my $db = $_[0];

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


# pars: <database name>

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

	my (@content, $temp, @query, $i, $ans, $TABLE, $test);
	if ( -d $DATA_DIR."/mysql" ) {
        	$DB_SCHEMA=$DATA_DIR."/mysql";
        } else {
                $DB_SCHEMA="./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 );

        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/postgres";
        } else {
                $DB_SCHEMA="./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/ ) {
			&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;" );
			if ( $? != 0 ) {
				print "Grant privileges to standard tables failed!\n";
			} else {
				print "Privileges to standard tables succeded!\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++) {
			&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);

        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/db_berkeley/opensips";
        } else {
                $DB_SCHEMA="./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);

        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/mysql";
        } else {
                $DB_SCHEMA="./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);

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

	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 );
        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/dbtext/opensips";
        } else {
                $DB_SCHEMA="./dbtext/opensips";
        }

	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);

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

	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);

        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/mysql";
        } else {
                $DB_SCHEMA="./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);
        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/postgres";
        } else {
                $DB_SCHEMA="./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 );
        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/dbtext/opensips";
        } else {
                $DB_SCHEMA="./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);
        if ( -d $DATA_DIR."/mysql" ) {
                $DB_SCHEMA=$DATA_DIR."/db_berkeley/opensips";
        } else {
                $DB_SCHEMA="./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;

	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" );


	### trusted
	&mysql_migrate_table( $dst_db . ".trusted", "id,src_ip,proto,from_pattern,tag", $src_db . ".trusted", "?id,?src_ip,?proto,?from_pattern,?tag" );

	### address
	&mysql_migrate_table( $dst_db . ".address", "id,grp,ip_addr,mask,port", $src_db . ".address", "?id,?grp,?ip_addr,?mask,?port" );

	### 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" );


	### gw_grp
	&mysql_migrate_table( $dst_db . ".gw_grp", "grp_id,grp_name", $src_db . ".gw_grp", "?grp_id,?grp_name" );

	### 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,ha1,ha1b,rpid", $src_db . ".subscriber", "?id,?username,?domain,?password,?email_address,?ha1,?ha1b,?rpid" );


	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" );
	}
}


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";				
			}	 
			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";				
			}	 
			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];

	switch ($DBENGINE) {
		case /(^mysql$)/ {
			&db_mysql_query($query);
		}
		case /(^Pg$)/ {
			&db_pgsql_query($query);
		}
		case /(^DBTEXT$)/ {
			&db_dbtext_query($query);
		}
	}

}


sub db_ro_query(){

	my $query = $_[0];	

	switch ($DBENGINE) {
		case /(^mysql$)/ {
			&db_mysql_ro_query($query);
		}
		case /(^Pg$)/ {
			&db_pgsql_ro_query($query);
		}
		case /(^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;		
	}

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

		case /(^cat$)/ {
			&opensips_cat($cmd[2],$DB_PATH);			
		}

		case /(^swap$)/ {
			&opensips_swap($cmd[2], $DB_PATH);
		}

		case /(^append$)/ {
			&opensips_append($cmd[2], $cmd[3], $DB_PATH);
		}

		case /(^newappend$)/ {
			#shift
			#opensips_newappend  $1 $2 $DB_PATH
			#exit $?
		}

		case /(^export$)/ {
			&opensips_export($cmd[2],$DB_PATH);
		}

		case /(^migrate$)/ {
			&opensips_migrate($cmd[2], $DB_PATH);
		}
	
		case /(^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, $description, @var, $i, $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;
		}	  
	
		switch ($cmd[2]) {

			case /(^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]);
						print "var[1]".$var[1];
						switch ($var[0]) {
							case /(type)/ {
								print $type . "before\n";
								$type = $var[1]; 
								print $type . "after\n";
							}
							case /(strip)/ {
								$strip = $var[1];
							}
							case /(pri_prefix)/ {
								$pri_prefix = $var[1];
							}
							case /(description)/ {
								$description = $var[1];
							}
						}	
						$i++;
					}
					print $type . "after loop\n";
				}

				#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();
	
				}				
	
			}

			case /(^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();

				}				

			}

			case /(^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]);	
					switch ( $var[0] ) {
				
						case /(^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();
							}		
						}
						case /(^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();
							}		
						}
					}
				}
			}

			case /(^h$)/ {
				&usage_dr();
			}

			else {print "Not an droute option!!\n"};

		}


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

			if ( $#cmd < 2 ) {
				print "Too few parameters!";
				&usage_dr();
				return;
			} elsif ( $#cmd > 9 ) {
				print "Too many parameters!";
				&usage_dr();
				return;
			}

			switch ($cmd[2]) {

				case /(^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]);				
							switch ($var[0]) {
								case /(^groupid$)/ {
									$groupid = $var[1]; 
								}
								case /(^prefix$)/ {
									$prefix = $var[1];
								}
								case /(^timerec$)/ {
									$timerec = $var[1];
								}
								case /(^priority$)/ {
									$priority = $var[1];
								}
								case /(^routeid$)/ {
									$routeid = $var[1];
								}
								case /(^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();

					}				

						
				}

				case /(^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();

					}		
				}

				case /(^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]);	
						switch ( $var[0] ) {
				
							case /(^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();
								}		
							}
							case /(^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();
								}		
							}
						}
					}

				}

				case /(^h$)/ {
					&usage_dr();
		     	        }
		 		
				else {print "Not an dr_rules option!!\n"};

			}


		}
}
