Enumerating the ports

Tonight I started working on the Deleted port that wasn’t deleted problem tonight. The first step is to create a script that lists all the entries in the ports table. I took an existing script, altered it to meet my needs, and came up with this:

#!/usr/bin/perl -w
#
# $Id: set-broken.pl,v 1.1.2.2 2004/11/27 13:54:09 dan Exp $
#
# Copyright (c) 1999-2005 DVL Software
#

use strict;
use lib "../";
use port;
use DBI;
use database;
use utilities;

my $dbh;

my $port;
my @PORTS;
my $sql;
my $sth;
my $row;

FreshPorts::Utilities::InitSyslog();

$dbh = FreshPorts::Database::GetDBHandle();

#
# get a list of ports to update
#

$sql = "
SELECT P.id     AS port_id,
       E.id     AS element_id,
       E.name   AS port,
       C.name   AS category,
       E.status AS status
  FROM ports P, element E, categories C
 WHERE P.element_id  = E.id
   AND P.category_id = C.id
ORDER by category, port
";

print "sql = $sql\n";

$sth = $dbh->prepare($sql);
$sth->execute ||
                FreshPorts::Utilities::ReportError('warning', "Could not execute SQL $sql ... maybe invalid?", 1);

while ($row=$sth->fetchrow_hashref) {
#       print "now reading @row\n";
        my $port = $row;
        push @PORTS, $row;
}

foreach $port (@PORTS) {
        my $result;

        print 'P.id:' . $port->{port_id} . ' E.id:' . $port->{element_id} . ' ' .
            $port->{category} . '/' . $port->{port} . ': ' . $port->{status} . "\n";
}

$sth->finish();

$dbh->commit();
$dbh->disconnect();

The output is pretty long, but I’ll show you a bit of it:

$ perl look-for-deleted-ports.pl | less
sql =
SELECT P.id AS port_id,
E.id AS element_id,
E.name AS port,
C.name AS category,
E.status AS status
FROM ports P, element E, categories C
WHERE P.element_id = E.id
AND P.category_id = C.id
ORDER by category, port

P.id:11598 E.id:171704 accessibility/at-spi: A
P.id:11601 E.id:171706 accessibility/atk: A
P.id:11600 E.id:171708 accessibility/dasher: A
P.id:11597 E.id:171710 accessibility/gail: A
P.id:11596 E.id:171712 accessibility/gnomemag: A
P.id:11599 E.id:171714 accessibility/gnomespeech: A
P.id:11602 E.id:171718 accessibility/gnopernicus: A

And so on, and so on…. There’s a few rows there:

$ perl look-for-deleted-ports.pl  | wc -l
   16444

Why did I so this? This is the first step in getting a list of Element IDs. I will compare the status of the Element ID for each port, with the results of a PGSQL function I’m going to write. This function will take an Element ID and tell me whether or not everything under that point in the tree has been removed. If everything has been removed, then the port has been deleted.

All of this might sound confusing. Element ID. Port ID, etc. Each item in the CVS repositorty is represented by an entry in the Element table. Each entry in the Ports table refers to something in the Element table. So the port sysutils/bacula-server has Port ID = 13982 and Element ID = 204995. All the files for that port will reside in the Element table somewhere that Element ID.

The next post on this news blog will most likely be the PGSQL function.