Where is en-aspell?

Tonight, I was contacted by jrm on IRC, who told me that FreshPorts was missing something on a search. Yep. It was not in there.

I looked on dev and I saw the same thing.

Finding the cause

I started by enabling debug on dev. I found this SQL:

  SELECT count(*)
  FROM ports P LEFT OUTER JOIN ports_vulnerable    PV  ON PV.port_id       = P.id
               LEFT OUTER JOIN commit_log          CL  ON P.last_commit_id = CL.id
               LEFT OUTER JOIN repo                R   ON CL.repo_id       = R.id
               LEFT OUTER JOIN commit_log_branches CLB ON CL.id            = CLB.commit_log_id
                          JOIN system_branch       SB  ON SB.branch_name   = 'head'
                                                      AND SB.id            = CLB.branch_id,
       categories C, element E

    WHERE P.category_id  = C.id
      AND P.element_id   = E.id  AND 
     E.name ILIKE '%en-aspell%' and E.status = 'A' 

I started by removing one LEFT OUTER JOIN at a time until I found the cause. It was SB.id = CLB.branch_id.

OK, but why?

Let’s look at some of the SQL first.

One line 3, we start with P.last_commit_id, which is the last commit for this port.

From there, we join to the commit_log table to find the repo_id for that commit on line 4.

Line 5 gives us the commit_log_branch table entry for that commit, and we know now which branch this commit touched.

Lines 6 and 7 will restrict the search to head.

I started examining the values from these JOINs. I discovered that some commits did not have entries in the commit_log_branches table. These were commits which occurred before that table was created.

The problem arose when a port whose last commit occurred before the creation of that table. These ports would never be included because, according to the database, there was no such commit on head for them.

The solution

The solution is to add entries to the commit_log_branches table. The following SQL accomplished that.

insert into commit_log_branches
SELECT distinct P.last_commit_id, (select SB.id  FROM system_branch SB where branch_name = 'head')
  FROM ports P LEFT OUTER JOIN commit_log CL ON P.last_commit_id = CL.id
  WHERE NOT EXISTS (SELECT * FROM commit_log_branches CLB WHERE CL.id            = CLB.commit_log_id)
 and P.last_commit_id is not null;

While there, I did this for good measure:

create unique index commit_log_branches_pk on commit_log_branches(commit_log_id, branch_id);

The table now looks like this:

freshports.org=# \d commit_log_branches
 Table "public.commit_log_branches"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 commit_log_id | integer | not null
 branch_id     | integer | not null
Indexes:
    "commit_log_branches_pk" UNIQUE, btree (commit_log_id, branch_id)
Foreign-key constraints:
    "commit_log_branch_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES system_branch(id) ON UPDATE CASCADE ON DELETE CASCADE
    "commit_log_branch_commit_log_id_fkey" FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=#