Wrong link to svnweb

Brad Davis wrote in to explain that the home page for net-mgmt/nagios-snmp-plugins-extras on beta is wrong.

It is missing the hostname for the URL.

Indeed it is. And it’s wrong on the dev box too.

I looked. I found out the cause. Here is a brief version of the code which pulls back the hostname for the port in question:

SELECT ports.id, 
       ports.element_id, 
       ports.category_id       as category_id,
       ports.short_description as short_description, 
       GMT_Format(commit_log.date_added) as last_modified,
       R.svn_hostname,
       R.path_to_repo,
       element_pathname(ports.element_id) as element_pathname 
 FROM categories, 
      element, 
      ports_vulnerable RIGHT OUTER JOIN ports ON ports_vulnerable.port_id = ports.id
                       LEFT OUTER JOIN commit_log ON ports.last_commit_id = commit_log.id 
                       LEFT OUTER JOIN repo R     ON commit_log.repo_id   = R.id 
WHERE ports.id = 25088 
  AND ports.category_id = categories.id 
  AND ports.element_id  = element.id;

The problem is the ports.last_commit_id which links to the commit_log table, which in turn joins to the repo table.

The repo table looks like this:

freshports.org=# select * from repo;
 id | name  |      description       |    svn_hostname    | path_to_repo
----+-------+------------------------+--------------------+--------------
  1 | ports | The FreeBSD Ports tree | svnweb.freebsd.org | /ports
  2 | doc   | The FreeBSD doc tree   | svnweb.freebsd.org | /doc
  3 | src   | The FreeBSD src tree   | svnweb.freebsd.org | /base
(3 rows)

The issue is the value in commit_log.repo_id, which is a field populated during each commit, but only recently. This was a change introduced when the ports tree moved to SVN from CVS. Observe:

freshports.org=# select last_commit_id from ports where ports.id = 25088 ;
 last_commit_id
----------------
         423775
(1 row)

That is the id of the last commit against this port. When was that?

freshports.org=# select commit_date, repo_id from commit_log where id = 423775;
      commit_date       | repo_id
------------------------+---------
 2012-02-07 13:29:00+00 |
(1 row)

Ahh, that was early in 2012, long before the commit tree went to SVN. Thus, there is no value.

Hmmm, what to do here?

I want to think about this for a while. Any ideas?