kamailio/modules/iptrtpproxy/examples/clean_rtpproxy_attrs

#!/usr/bin/env perl

# $Id$

# The script cleans up orphaned RTP proxy attributes from rtpproxy_attrs.
# The orphaned attribute is the attribute having none of its sessions.
# in iptrtpproxy session list.

use warnings;
#use strict;
use DBI;

my $version = "0.1";
my $defConfigFile = "/etc/iptrtpproxy.cfg";
my $configFile = $defConfigFile;
my $verbose = 0;

my $defmysqlhost = 'localhost';
my $mysqlhost = $defmysqlhost;
my $defmysqldb = 'ser';
my $mysqldb = $defmysqldb;
my $defmysqluser = 'ser';
my $mysqluser = $defmysqluser;
my $defmysqlpassword = 'heslo';
my $mysqlpassword = $defmysqlpassword;
my $defmysqlsock = "";
my $mysqlsock = $defmysqlsock;

use Config::IniFiles;

sub dbg {
	if ($verbose) {
		print STDERR "DBG: $_[0]\n";
	}
}

sub myexit ($$)
{
	my ($msg, $exitcode ) = @_;
	print "# ERROR: $msg\n";
	DBI->disconnect_all();
	exit $exitcode;
}


sub do_exec($) 
{
	my $cmd = $_[0];
	dbg "Exec: $cmd";
	my @res = `$cmd`;
	dbg "Exit code: $?";	
	@res;
}


# parse config file to $cfg
sub parseConfigFile($)
{
	my $fileName = $_[0];
	dbg "Parsing config file '$fileName'\n" if $verbose;
	my %switchboards = ();
	goto ret unless -e $configFile;
	my $cfg = Config::IniFiles->new( -file => $configFile, -default => "<default>" );

	for my $section ($cfg->Sections) {
		if ($section =~ /^[a-zA-Z_]+$/) {
			print STDERR "Parsing section '$section'\n" if $verbose;
			for my $param ('addr-a', 'addr-b', 'port-a', 'port-b') {
				my $val = $cfg->val($section, $param);
				if ($val) {
					$switchboards{$section}{$param} = $val;
				}
			}
		}

	}
ret:
	return %switchboards;
}

my $scriptName = $0;
$scriptName =~ s!^.*/!!;

sub printUsage {
	print "$scriptName, Version: $version\n";
	print "usage: $scriptName [-f <iptrtpproxy_config>] [-v] [-h] [-H <db_host>] [-S <db_sock>] [-D <db_name>] [-u <db_user>] [-p <db_password>]\n";
	print "  iptrtpproxy_config .. iptrtpproxy_helper config, default: $defConfigFile\n";
	print "  db_host .. database MYSQL  host, default: $defmysqlhost\n";
	print "  db_sock .. MYSQL socket, default: $defmysqlsock\n";
	print "  db_name .. database name, default: $defmysqldb\n";
	print "  db_user .. database user, default: $defmysqluser\n";
	$_ = substr $defmysqlpassword, 1, length($defmysqlpassword)-2;
	s/./\*/g;
	my $psw = substr($defmysqlpassword, 0, 1) . $_ . substr($defmysqlpassword, length($defmysqlpassword)-1, 1);
	print "  db_password .. database password, default: $psw\n";
	print "  -v .. verbose\n";
}

my $command = '';
my $arg;

while ($#ARGV >= 0) {
	$arg = shift(@ARGV);
	if ($arg eq '-f') {
		if ($#ARGV < 0) {
			print STDERR "ERROR: config file name required\n";
			&printUsage();
			exit(1);
		} else {
			$configFile = shift(@ARGV);
		}
	} elsif ($arg eq '-D') {
		if ($#ARGV < 0) {
			print STDERR "ERROR: database name required\n";
			&printUsage();
			exit(1);
		} else {
			$mysqldb = shift(@ARGV);
		}
	} elsif ($arg eq '-u') {
		if ($#ARGV < 0) {
			print STDERR "ERROR: user name required\n";
			&printUsage();
			exit(1);
		} else {
			$mysqluser = shift(@ARGV);
		}
	} elsif ($arg eq '-p') {
		if ($#ARGV < 0) {
			print STDERR "ERROR: password required\n";
			&printUsage();
			exit(1);
		} else {
			$mysqlpassword = shift(@ARGV);
		}
	} elsif ($arg eq '-H') {
		if ($#ARGV < 0) {
			print STDERR "ERROR: hostname required\n";
			&printUsage();
			exit(1);
		} else {
			$mysqlhost = shift(@ARGV);
		}
	} elsif ($arg eq '-S') {
		if ($#ARGV < 0) {
			print STDERR "ERROR: socket required\n";
			&printUsage();
			exit(1);
		} else {
			$mysqlsock = shift(@ARGV);
		}
	} elsif ($arg eq '-v') {
		$verbose++;
	} elsif ($arg eq '-h') {
		&printUsage();
		exit(0);
	} else {
		print STDERR "ERROR: unknown argument '$arg'\n";
		&printUsage();
		exit(1);
	}	
}

dbg "Verbose: $verbose";

dbg "Connecting: mysql://$mysqluser\@$mysqlhost/$mysqldb";
myexit("Cannot connect database", 2) unless my $dbh = DBI->connect("dbi:mysql:database=$mysqldb".
	($mysqlhost?";host=$mysqlhost":'').
	($mysqlsock?";mysql_socket=$mysqlsock":'')
	, $mysqluser, $mysqlpassword);

dbg "Reading rtpproxy_attrs";
my %attrs = ();
# copy rtpproxy_attrs to memory, we need snapshot of table
$sth = $dbh->prepare("SELECT * FROM rtpproxy_attrs");
$sth->execute();
my $i = 0;
while (my $href = $sth->fetchrow_hashref()) {

	if ($href->{'name'} eq 'dlg_sess_ids') {
		$attrs{$href->{'id'}} = $href->{'value'};
		$i++;
	}
}
$sth->finish();
dbg "rtpproxy_attrs records: $i";

my %cfgSwitchboards = &parseConfigFile($configFile);

my %switchboards = ();
for my $id (keys %cfgSwitchboards) {
	dbg "Switchboard: $id";
	$cmd = "iptrtpproxy list --addr-a $cfgSwitchboards{$id}{'addr-a'} --port-a $cfgSwitchboards{$id}{'port-a'} --addr-b $cfgSwitchboards{$id}{'addr-b'} --port-b $cfgSwitchboards{$id}{'port-b'} --force-switchboard-audit --format tab";
	my @lines = &do_exec($cmd);
	my @l = split(/\t/, $lines[0]);
	my $sess_id = -1;
	my $tick = -1;
	for my $i (0..$#l) {
		if ($l[$i] eq 'sess-id') {
			$sess_id = $i;
		} elsif ($l[$i] eq 'created/tick') {
			$tick = $i;
		}
	}
	dbg "Lines: $#lines, Columns: sess-id: $sess_id, tick: $tick";
	for my $i (1..$#lines) {
		my @l = split(/\t/, $lines[$i]);
		$switchboards{$id}{$l[$sess_id]} = $l[$tick];
	}
}

dbg "Clean rtpproxy_attrs";
# get iptrtpproxy switchboards
my $delCnt = 0;
$sth = $dbh->prepare("DELETE FROM rtpproxy_attrs WHERE id=?");
loop: for my $id (keys %attrs) {
	# format name:session_id/stamp (, session_id/stamp)*] 
	my $found = 0;
	my ($name, $s) = split( /:/, $attrs{$id});
	if (!defined $s) {
		goto del;
	}
	my @ss = split( /,/, $s);
	for $i (0..$#ss) {
		my ($sess_id, $tick) = split ( /\//, $ss[$i]);
		if ($sess_id eq "" || !defined $tick || $tick eq "") {
			next;
		}
		if ($switchboards{$name}{$sess_id} && $switchboards{$name}{$sess_id} eq $tick) {
			dbg "Leave: $id=$attrs{$id}";
			next loop;
		}
	}
del:
	dbg "Delete: $id=$attrs{$id}";
	$sth->execute($id);
	$delCnt++;
}
dbg "Deleted: $delCnt records";

exit(0);