mail != japanese

Yes, that’s right. You heard me. mail != japanese.

Stefan Walter just wrote me to point out that http://www.freshports.org/mail/squirrelmail/ was incorrectly displaying the content from japanese/squirrelmail. At first, I was thinking physical categories versus virtual categories. I was wrong. It was a good old SQL error.

I recently introduced Better URL Parsing. I suggest that it has some room to improve. :) I’ll explain the error in detail.

One of the functions used by the URL parsing code is this function:

function freshports_GetPortID($db, $category, $port) {
    $sql = "select Port_ID('$category', '$port')";

    $result = pg_exec($db, $sql);
    if (!$result) {
        echo "error " . pg_errormessage();
        exit;
    }

    $myrow = pg_fetch_array($result, 0);

    return $myrow['port_id'];
}

What does this function return?

freshports.org=# select Port_ID('mail', 'squirrelmail');
 port_id
---------
   11056
(1 row)

freshports.org=#

OK, we have a Port ID. What port is that?

freshports.org=# select id, name, category from ports_active where id = 11056;
  id   |     name     | category
-------+--------------+----------
 11056 | squirrelmail | japanese
(1 row)

freshports.org=#

Oh. OK, the error must be in the Port_ID stored procedure. Let’s look at that now.

The code for that stored procedure is:

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

Let’s have a look at what the above returns:

freshports.org=# SELECT P.id
freshports.org-#       FROM element EP, ports P, categories C, ports_categories PC
freshports.org-#     WHERE EP.name        = 'squirrelmail'
freshports.org-#       AND EP.id          = P.element_id
freshports.org-#       AND C.name         = 'mail'
freshports.org-#       AND PC.port_id     = P.id
freshports.org-#       AND PC.category_id = C.id;
  id
-------
 11056
  6834
(2 rows)

freshports.org=#

There’s the problem! Two results from the single query. There’s a fault in my SQL for sure. It should on return one row. That second row is indeed the one I want:

freshports.org=# select id, name, category from ports_active where id = 6834;
  id  |     name     | category
------+--------------+----------
 6834 | squirrelmail | mail
(1 row)

freshports.org=#

So what’s wrong? I’m sure it’s just a condition I’m missing. I started out by checking the data from the element table:

freshports.org=# SELECT P.id, EP.*
freshports.org-#       FROM element EP, ports P, categories C, ports_categories PC
freshports.org-#     WHERE EP.name        = 'squirrelmail'
freshports.org-#       AND EP.id          = P.element_id
freshports.org-#       AND C.name         = 'mail'
freshports.org-#       AND PC.port_id     = P.id
freshports.org-#       AND PC.category_id = C.id;
  id   |   id   |     name     | parent_id | directory_file_flag | status
-------+--------+--------------+-----------+---------------------+--------
 11056 | 161116 | squirrelmail |       129 | D                   | A
  6834 |  75342 | squirrelmail |       201 | D                   | A
(2 rows)

Then the light bulb went off as I was looking at the SQL: the ports table. There’s a cateory ID in there. It must match too! The amended, and now working, query is:

freshports.org=# SELECT P.id
freshports.org-#       FROM element EP, ports P, categories C, ports_categories PC
freshports.org-#     WHERE EP.name        = 'squirrelmail'
freshports.org-#       AND EP.id          = P.element_id
freshports.org-#       AND C.name         = 'mail'
freshports.org-#       AND PC.port_id     = P.id
freshports.org-#       AND PC.category_id = C.id
freshports.org-#       AND P.category_id  = C.id;
  id
------
 6834
(1 row)

freshports.org=#

I have put the new conditional in bold.

With that fix, everything is now working correctly.

Additional Checking

I did toy around with adding code such as this to the freshports_GetPortID() function, but it fails to achieve the desired effects:

if (pg_numrows($result) > 1) {
    syslog(LOG_NOTICE, "'$sql' has returned more than one row");
}

Why? Because the stored procedure returns an integer, not a result set. In order for me to check for multiple rows, I’d either have to change the stored procedure to return a result set, or check that condition in the stored procedure. I’ve decided to not check at all. Given the new SQL, there should only ever be one port in a given category with the given name.

Now all I had to do is wait for someone to break that. :)