Check DB For Records Mark for Deletion

From KJ4BYI D-Star Wiki

First make sure you have "cpan" up to date. If you notice some errors, try this method:

perl -MCPAN -e 'install Bundle::DBI'

perl -MCPAN -e 'install DBD::Pg'

This script will retreive a count of records from your local postgres database, and print out the number of records marked for deletion.

#!/usr/bin/perl -w
use strict;
use DBI;

# This Script to access the local postgres database
# and notify you if there are any records marked for delation.

my($dsipcnf) = "/opt/products/dstar/dstar_gw/dsipsvd/dsipsvd.conf";
my($db_host);
my($db_user);
my($db_pass);
my($db_name);

my($dbh);
my($res);
my($ref);
my($cmd);

my($debug) = 1;

if ( -e $dsipcnf)
{

 if ($debug >= 5) { print "[DEBUG   ] dsipsvd.conf config file found, reading database values.\n"; }

 open(my $dsconfig,"$dsipcnf") || die("Cannot open dsipsvd configuration file.\n");

 while (<$dsconfig>) {
    chomp;                  # no newline
    s/#.*//;                # no comments
    s/^\s+//;               # no leading white
    s/\s+$//;               # no trailing white
    next unless length;     # anything left?
    my ($var, $value) = split(/\s*=\s*/, $_, 2);

    if ($var eq "DB_IP") { $db_host = $value; }
    if ($var eq "DB_NAME") { $db_name = $value; }
    if ($var eq "DB_USER") { $db_user = $value; }
    if ($var eq "DB_PASSWORD") { $db_pass = $value; }

    if ($debug >= 5) { print "[CNF READ] $var=$value\n"; }
}

close($dsconfig);

}

if ($db_host eq "") { die("Unable to read DB_IP from dsipsvd.conf\n");       }
if ($db_name eq "") { die("Unable to read DB_USER from dsipsvd.conf\n");     }
if ($db_user eq "") { die("Unable to read DB_USER from dsipsvd.conf\n");     }
if ($db_pass eq "") { die("Unable to read DB_PASSWORD from dsipsvd.conf\n"); }

if ($debug) {

print "# End of Configuration file read\n"; 
print "#\n"; 
print "# db_host   = $db_host\n";
print "# db_name   = $db_name\n";
print "# db_user   = $db_user\n";
print "# db_pass   = $db_pass\n";
print "#\n";
print "# End of Variable Definations\n";
print "\n";

}

$dbh = DBI->connect("DBI:Pg:dbname=$db_name;host=$db_host", "$db_user", "$db_pass", {'RaiseError' => 1});


########## check the sync_gip.

$cmd = $dbh->prepare("SELECT Count(*) as cnt FROM sync_gip where del_flg = 't';");
$cmd->execute();

$ref = "";            
$res = 0;

if ($ref=$cmd->fetchrow_hashref())
{
	$res = $ref->{'cnt'};
}

print "There are $res records in the Gateway IP Table marked for deletion.\n";

$cmd->finish();

########### Check syn_rip

$cmd = $dbh->prepare("SELECT Count(*) as cnt FROM sync_rip where del_flg = 't';");
$cmd->execute();

$ref="";
$res = 0;

if ($ref=$cmd->fetchrow_hashref())
{
	$res = $ref->{'cnt'};
}

print "There are $res records in the User Callsign Routing Table marked for deletion.\n";

$cmd->finish();

########### Check sync_mng

$cmd = $dbh->prepare("SELECT Count(*) as cnt FROM sync_mng where del_flg = 't';");
$cmd->execute();

$ref="";
$res = 0;

if ($ref=$cmd->fetchrow_hashref())
{
        $res = $ref->{'cnt'};
}

print "There are $res records in the User Callsign Management Table marked for deletion.\n";

$cmd->finish();


$dbh->disconnect();



print "\n\nEnd of Report.\n";