When is a port not a port?

When is a port not a port? When it was deleted before the ports_categories table was introduced.

I noticed that some long-deleted ports were not displaying correctly. For example: http://www.freshports.org/sysutils/cdrecord/

It was displaying as a non-port, but it clearly was a port, although deleted.

I tracked the problem down to this stored procedure:

CREATE OR REPLACE FUNCTION Port_ID(text, text) returns integer AS $$
    SELECT P.id
      FROM element E, ports P, categories C, ports_categories PC
    WHERE E.name         = $2
      AND E.id           = P.element_id
      AND C.name         = $1
      AND PC.port_id     = P.id
      AND PC.category_id = C.id
      AND P.category_id  = C.id;
$$ LANGUAGE SQL STABLE;

This will not return anything if there is no entry in the ports_categories table. Let’s try another way to do this:

CREATE OR REPLACE FUNCTION Port_ID(text, text) returns integer AS $$
    SELECT P.id
      FROM element E, ports P, categories C
    WHERE E.name         = $2
      AND E.id           = P.element_id
      AND C.name         = $1
      AND P.category_id  = C.id;
$$ LANGUAGE SQL STABLE;

At first, I thought this fix may affect virtual categories. However, I discovered that a port cannot be displayed by its virtual categories in FreshPorts. For example, look at deskutils/kdepim3. You will see:

Also Listed In: mail news palm kde ipv6

Thus, within the freshports.org website, deskutils/kdepim3 will work, but mail/kdepim3 will give a 404. Similarly with news/kdepim3, palm/kdepim3, kde/kdepim3, and ipv6/kdepim3. But that is an entirely different problem. We are here only to solve the issues of long-deleted ports.

For now, both beta and dev have the fix. Thus, http://www.freshports.org/sysutils/cdrecord/ does not display properly, but http://beta.freshports.org/sysutils/cdrecord/ and http://dev.freshports.org/sysutils/cdrecord/ do. I will migrate the fix to production a few days after publishing this post.