Getting a link from a package name

From IRC:

[Oct 05 22:41] <@dvl> zi: we have conflict matches: https://dev.freshports.org/security/openssl/
[Oct 06 09:08] <@zi> dvl: cute. possible to add in a clickable link to take you to that matching port? :)
[Oct 06 10:50] <@dvl> zi: That matching port, it’s just a package name, sometimes there is a matching port, sometimes there is not… But yeah, let’s see.

So let’s see what happens.

I started off with this:

freshports.dev=# select id, packagename(id), element_pathname(element_id) from ports where package_name = 'libressl';
  id   | packagename |             element_pathname             
-------+-------------+------------------------------------------
 48916 | libressl    | /ports/branches/2018Q2/security/libressl
 40767 | libressl    | /ports/branches/2017Q1/security/libressl
 43361 | libressl    | /ports/branches/2017Q2/security/libressl
 34863 | libressl    | /ports/head/security/libressl
(4 rows)

What about just head?

Let’s join to the element_pathname table and use LIKE to get what we need.

freshports.dev=# SELECT P.id, packagename(P.id), element_pathname(P.element_id)
freshports.dev-#   FROM ports P JOIN element_pathname EP ON P.element_id = EP.element_id
freshports.dev-#  WHERE package_name = 'libressl'
freshports.dev-#    AND EP.pathname LIKE '/ports/head/%';
  id   | packagename |       element_pathname        
-------+-------------+-------------------------------
 34863 | libressl    | /ports/head/security/libressl
(1 row)

That’s better. But we need category and port name too.

freshports.dev=# SELECT P.id AS port_id, C.name AS category, packagename(P.id) as port, element_pathname(P.element_id)
  FROM ports P JOIN element_pathname EP ON P.element_id  = EP.element_id
               JOIN categories       C  ON P.category_id = C.id
 WHERE package_name = 'libressl'
   AND EP.pathname LIKE '/ports/head/%';
 port_id | category |   port   |       element_pathname        
---------+----------+----------+-------------------------------
   34863 | security | libressl | /ports/head/security/libressl
(1 row)

That’s it. Let’s create a datatype for that:

  DROP TYPE IF EXISTS GetPortFromPackageName_record CASCADE;
CREATE TYPE GetPortFromPackageName_record AS (
        port_id                 integer,
        category                text,
        port                    text,
        element_pathname        text
);

And next, the function to get it all:

CREATE OR REPLACE FUNCTION GetPortFromPackageName(text) RETURNS SETOF GetPortFromPackageName_RECORD AS $$
DECLARE
        a_PackageName   ALIAS for $1;

        r               GetPortFromPackageName_RECORD%rowtype;

BEGIN
        FOR r IN
                SELECT P.id AS port_id, C.name AS category, packagename(P.id) as port, element_pathname(P.element_id)
                  FROM ports P JOIN element_pathname EP ON P.element_id  = EP.element_id
                               JOIN categories       C  ON P.category_id = C.id
                 WHERE package_name = a_PackageName
                   AND EP.pathname LIKE '/ports/head/%'

        LOOP
                RETURN NEXT r;
        END LOOP;
END $$ LANGUAGE plpgsql;

Now we can get that information via:

freshports.dev=# select * from GetPortFromPackageName('libressl');
 port_id | category |   port   |       element_pathname        
---------+----------+----------+-------------------------------
   34863 | security | libressl | /ports/head/security/libressl
(1 row)

freshports.dev=#

Next step: alter the website to use this data to create the hyperlink.

The existing website code was:

SELECT DISTINCT PackageName(PCM.port_id) as package_name
  FROM ports_conflicts_matches PCM JOIN ports_conflicts PC ON PCM.ports_conflicts_id = PC.id
 WHERE PC.port_id = ' . $this ->{'id'};

The first attempt to use the new function was:

freshports.dev=# SELECT DISTINCT PackageName(PCM.port_id) as package_name, GetPortFromPackageName(PackageName(PCM.port_id))
freshports.dev-#   FROM ports_conflicts_matches PCM JOIN ports_conflicts PC ON PCM.ports_conflicts_id = PC.id
freshports.dev-#  WHERE PC.port_id = 34863;
  package_name  |                       getportfrompackagename                        
----------------+---------------------------------------------------------------------
 libressl-devel | (37855,security,libressl-devel,/ports/head/security/libressl-devel)
 openssl        | (954,security,openssl,/ports/head/security/openssl)
 openssl-devel  | (38174,security,openssl-devel,/ports/head/security/openssl-devel)
 openssl111     | (49920,security,openssl111,/ports/head/security/openssl111)
(4 rows)

freshports.dev=# 

That’s not quite what I needed. I got on IRC and asked. xocolatl provided.

SELECT DISTINCT PackageName(PCM.port_id) as package_name, f.*
  FROM ports_conflicts_matches PCM 
  JOIN ports_conflicts PC ON PCM.ports_conflicts_id = PC.id
  JOIN GetPortFromPackageName(PackageName(PCM.port_id)) AS f ON true
 WHERE PC.port_id = 34863;

That query is now on the dev website.

This is the corresponding change to the display part of the website:

@@ -663,6 +663,12 @@ class port_display {
                        }
 
                        $HTML .= "\n";
+
+                       $HTML .= "Conflicts Matches:\n
";
+                       foreach($port->conflicts_matches as $match) {
+                               $HTML .= "
conflicts with " . freshports_link_to_port($match['category'], $match['port']) . '
';
+                       }
+                       $HTML .= '
';
                }

You can see the results at https://dev.freshports.org/security/openssl/

Soon, this will go into production. Please review any other ports which you know have CONFLICTS.