Slave ports can have their current vuln count incorrectly displayed

[dlangille]

Current vuln status is shown by a skull at the top of the page.

Which skull to show (active or greyed out) is decided by an entry for this port in the ports_vulnerable table, which has three column: port_id, current, past

The stored procedure PortsVulnerabilityCountAdjust(port_id) sets these values. There seems to be a problem with this query:

            SELECT count(distinct vuxml_id)
              INTO l_VulnCurrent
              FROM commit_log_ports_vuxml CLPV, commit_log_ports CLP, ports P
             WHERE CLP.commit_log_id = CLPV.commit_log_id
               AND CLPV.port_id      = CLP.port_id
               AND P.id              = CLP.port_id
               AND P.version         = CLP.port_version
               AND P.revision        = CLP.port_revision
               AND CLP.port_epoch    = P.portepoch
               AND P.id              = p_PortID;

I'll have to look into that to see why it goes wrong, but I suspect its because there is no commit which affects the current version of the port. This is a special case for slave ports, because their version can be derived from the master and there may not be a commit against the slave port for a given PORTVERSION value.

I am not sure how best to solve this.

[dlangille]

Perhaps: when the master port is updated, rerun all the slave ports. Or.. when evaluating the slave ports for vulnerability, look also at ports.version and ports.revision.

[dlangille]

I will test a solution: when a port is marked as vuln (i.e. the Skull at the top of the port page), do the same for all slave ports.

[dlangille]

This is what I wrote without referring to the db schema. It will contain errors:

on update of ports table
when OLD.is_vulnerable != NEW.is_vulnerable
UPDATE ports
   SET is_vulnerable = NEW.is_vulnerable
 WHERE ports.id IN (SELECT MS.slave_port_id
                      FROM master_slave MS
                     where MS.master_port_id = ports.id)

[dlangille]

This is closer:

on update of ports table
when OLD.is_vulnerable != NEW.is_vulnerable
UPDATE ports
   SET is_vulnerable = NEW.is_vulnerable
 WHERE ports.id IN (SELECT getport(P.master_port)
                      FROM ports P
                     where P.master_port = convert port id to master_port(ports.id))

[dlangille]

This is the key clause. It bring back the port ids of the slave ports for a given master port:

SELECT P.id
  FROM ports P
 WHERE P.master_port = (SELECT category || '/' || name AS cat_port
                          FROM ports_active 
                         WHERE id = (select getport('textproc/uim')));

 id   
-------
 27878
 17287
 17286
 17288
 17291
 20243
 17289
 29261
 17285
 17290
 27292
(11 rows)

Explain analyze details are here; quite fast: http://explain.depesz.com/s/X4do

But looking at that SQL now, it seems to have too many clauses. I might be able to simplify it. I need sleep.

Next step, develop the trigger.

[dlangille]

On the plane, I managed to create some triggers based on this.

https://gist.github.com/dlangille/b453b03132fcfaa3b125

[dlangille]

http://www.freshports.org/mail/mutt-lite/ will only list commits against mail/mutt-lite. Any commits against mail/mutt will be at http://www.freshports.org/mail/mutt/

But the version etc at http://www.freshports.org/mail/mutt-lite/ will not match what's shown at http://www.freshports.org/mail/mutt/

[dlangille]

I wonder if I meant 'will now', instead of 'will not'.