DEPENDS – direct or recursive?

Marie Helene asked a very good question:

Does the “for run” dependency list only list direct dependencies, or depends of depends too?

The answer is yes, only direct dependencies, but all the information is there to provide a complete list of all dependencies.

The table looks like this:

freshports.org=# \d port_dependencies
         Table "public.port_dependencies"
         Column         |     Type     | Modifiers 
------------------------+--------------+-----------
 port_id                | integer      | not null
 port_id_dependent_upon | integer      | not null
 dependency_type        | character(1) | not null
Indexes:
    "port_dependencies_pkey" PRIMARY KEY, btree (port_id, port_id_dependent_upon, dependency_type)
Foreign-key constraints:
    "port_dependencies_port_id_dependent_upon_fkey" FOREIGN KEY (port_id_dependent_upon) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    "port_dependencies_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=#

Here is the list of dependencies for sysutils/bacula-server:

freshports.org=# select * from port_dependencies where port_id = (select getport('sysutils/bacula-server'));
 port_id | port_id_dependent_upon | dependency_type 
---------+------------------------+-----------------
   13990 |                    954 | B
   13990 |                  35865 | B
   13990 |                    954 | R
   13990 |                  15203 | L
   13990 |                  13986 | L
   13990 |                  35866 | L
   13990 |                  32910 | L
(7 rows)

freshports.org=# 

You’ll see that one port is listed twice, both as a Run and as a Library dependency. The following output is more useful:

freshports.org=# select *, categoryport(port_id_dependent_upon) from port_dependencies where port_id = (select getport('sysutils/bacula-server')) order by categoryport;
 port_id | port_id_dependent_upon | dependency_type |         categoryport          
---------+------------------------+-----------------+-------------------------------
   13990 |                  15203 | L               | archivers/lzo2
   13990 |                  32910 | L               | databases/postgresql93-client
   13990 |                  35866 | L               | devel/gettext-runtime
   13990 |                  35865 | B               | devel/gettext-tools
   13990 |                    954 | R               | security/openssl
   13990 |                    954 | B               | security/openssl
   13990 |                  13986 | L               | sysutils/bacula-client
(7 rows)

freshports.org=# 

Dependencies of dependencies

What we want now is a list of dependencies for each of the above mentioned dependencies. For example:

freshports.org=# select *, categoryport(port_id_dependent_upon) from port_dependencies where port_id = (select getport('archivers/lzo2'));
 port_id | port_id_dependent_upon | dependency_type | categoryport 
---------+------------------------+-----------------+--------------
(0 rows)

freshports.org=# select *, categoryport(port_id_dependent_upon) from port_dependencies where port_id = (select getport('databases/postgresql93-client'));
 port_id | port_id_dependent_upon | dependency_type | categoryport 
---------+------------------------+-----------------+--------------
(0 rows)

freshports.org=# select *, categoryport(port_id_dependent_upon) from port_dependencies where port_id = (select getport('devel/gettext-runtime'));
 port_id | port_id_dependent_upon | dependency_type |    categoryport     
---------+------------------------+-----------------+---------------------
   35866 |                  34803 | R               | print/indexinfo
   35866 |                   1849 | L               | converters/libiconv
(2 rows)

freshports.org=# 

And so on, for each port. Clearly, this calls for recursion. My first thought was to create a stored procedure and call it from within itself. I have done this for other situations in FreshPorts, but when I searched, I found this RECURSIVE Query Is Recursive post by Harold Giménez. It took me little time to create this query:

WITH RECURSIVE all_depends AS (
  SELECT  port_id, port_id_dependent_upon, dependency_type
    FROM port_dependencies
    WHERE port_id = (select getport('sysutils/bacula-server'))
  UNION
  SELECT PD.port_id, PD.port_id_dependent_upon, PD.dependency_type
    FROM port_dependencies PD
    JOIN all_depends AD
      ON (PD.port_id = AD.port_id_dependent_upon)
)
   SELECT categoryport(port_id) AS ThisPort, categoryport(port_id_dependent_upon) AS IsDependentUpon, dependency_type AS DependencyType
     FROM all_depends
 ORDER BY ThisPort, IsDependentUpon;

        thisport        |        isdependentupon        | dependencytype 
------------------------+-------------------------------+----------------
 devel/gettext-runtime  | converters/libiconv           | L
 devel/gettext-runtime  | print/indexinfo               | R
 devel/gettext-tools    | converters/libiconv           | L
 devel/gettext-tools    | devel/gettext-runtime         | L
 devel/gettext-tools    | print/indexinfo               | R
 security/openssl       | lang/perl5.20                 | B
 sysutils/bacula-server | archivers/lzo2                | L
 sysutils/bacula-server | databases/postgresql93-client | L
 sysutils/bacula-server | devel/gettext-runtime         | L
 sysutils/bacula-server | devel/gettext-tools           | B
 sysutils/bacula-server | security/openssl              | B
 sysutils/bacula-server | security/openssl              | R
 sysutils/bacula-server | sysutils/bacula-client        | L
(13 rows)

I think we could build a small tree based upon that and display it on the web page.