Dan Langille

Jul 252016
 

FreshPorts uses a cache for every port page (e.g. https://www.freshports.org/sysutils/bacula-server/). When an update to a port occurs, we must clear the cache for that port. This sounds simple, and it is. The various ways in which updates occur complicates the situation.

This post is all about fixing one edge case: the addition or removal of a port dependency (e.g. RUN_DEPENDS, LIB_DEPENDS).

When port A depends on port B, this fact is listed on both pages, but adding/removing a dependency is not properly handled (see this bug). Tonight, I have coded a fix. I’d like it to be less complex, but it has been fixed.

The table

This is the table in question:

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=# 

Using the terminology from the first sentence, port A and port B are represented by port_id and port_id_dependent_upon respectively. Any new row or any deleted row must invalidate the cache for both port A and port B.

Keeping track of cache to clear

This is the table we use to clear the ports cache:

freshports.org=# \d cache_clearing_ports
                                     Table "public.cache_clearing_ports"
   Column   |            Type             |                             Modifiers                             
------------+-----------------------------+-------------------------------------------------------------------
 id         | integer                     | not null default nextval('cache_clearing_ports_id_seq'::regclass)
 port_id    | integer                     | not null
 category   | text                        | not null
 port       | text                        | not null
 date_added | timestamp without time zone | not null default now()
Indexes:
    "cache_clearing_ports_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "cache_clearing_ports_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=# 

We add rows to this table to indicate that a given port must be cleared from the cache.

On INSERT

On an insert to the port_dependencies table, let’s do this:

CREATE OR REPLACE FUNCTION port_dependencies_insert_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_cache_clearing_ports_id   int8;
      l_port      text;
      l_category  text;
   BEGIN
        --
        -- This function handles the addition of a new dependency.
        -- yes, we need to clear the cache for both port_id and port_id_dependent_upon
        -- from the cache_clearing_ports.  I figure there is a shorter way to this but
        -- I cannot think it through right now.
        --

        IF TG_OP = 'INSERT' THEN
         -- handle port A (port_id)
         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = NEW.port_id;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.port_id;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (NEW.port_id, l_category, l_port);
          END IF;

         -- handle port B (port_id_dependent_upon)
         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = NEW.port_id_dependent_upon;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.port_id_dependent_upon;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (NEW.port_id_dependent_upon, l_category, l_port);
          END IF;

          NOTIFY port_updated;
      END IF;

      -- when a port changes, add an entry to the cache clearing table
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER IF EXISTS port_dependencies_insert_clear_cache ON port_dependencies;
CREATE TRIGGER port_dependencies_insert_clear_cache
    AFTER INSERT on port_dependencies
    FOR EACH ROW
    EXECUTE PROCEDURE port_dependencies_insert_clear_cache();

That function is rather long, and I know it can be simplified, but that’s not on tonight’s agenda. If you are so inclined, I am happy to hear your suggestions. Specifically, I think I can do an INSERT INTO … WHERE NOT EXISTS and do the SELECT & INSERT in one step, without the need for IF FOUND.

On DELETE

Here is the code for the DELETE.

CREATE OR REPLACE FUNCTION port_dependencies_delete_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_cache_clearing_ports_id   int8;
      l_port      text;
      l_category  text;
   BEGIN
        --
        -- This function handles the deletion of a existing dependency.
        -- yes, we need to clear the cache for both port_id and port_id_dependent_upon
        -- from the cache_clearing_ports.  I figure there is a shorter way to this but
        -- I cannot think it through right now.
        --

        IF TG_OP = 'DELETE' THEN
         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = OLD.port_id;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = OLD.port_id;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (OLD.port_id, l_category, l_port);
          END IF;

         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = OLD.port_id_dependent_upon;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = OLD.port_id_dependent_upon;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (OLD.port_id_dependent_upon, l_category, l_port);
          END IF;

          NOTIFY port_updated;
      END IF;

      -- when a port changes, add an entry to the cache clearing table
      RETURN OLD;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER IF EXISTS port_dependencies_delete_clear_cache ON port_dependencies;
CREATE TRIGGER port_dependencies_delete_clear_cache
    AFTER DELETE on port_dependencies
    FOR EACH ROW
    EXECUTE PROCEDURE port_dependencies_delete_clear_cache();

It is nearly identical to the first function. Again, those of you who want to suggest improvements there, feel free.

I will run this in dev for a while and see how it goes. Initial testing has been promising.

NOTE: there are no updates to this table, by design. Instead when we see a new commit to a port, we do a DELETE from port_dependencies WHERE port_id = :a.

Jul 182016
 

Recently, I’ve been adding more and more file content into the database, mostly because it’s useful. For example, we add the contents of the distinfo, because that information is useful. Next, it was pkg-plist, and before I was done that, someone suggested added in the Makefile.

Neither pkg-plist nor Makefile will be perfect:

  1. Not all ports have a pkg-plist
  2. Some ports use Makefile.inc, and we are not capturing that

Why store these files?

Because we can, and then we can search on it.

Ever wondered how many ports do not have a pkg-plist file?

freshports.org=# select count(*) from ports where pkg_plist is not null and status = 'A';;
 count 
-------
 17632
(1 row)

freshports.org=# select count(*) from ports where pkg_plist is null and status = 'A';;
 count 
-------
 11733
(1 row)

freshports.org=#

How many ports use OSVERSION?

freshports.org=# select count(*) from ports where makefile ilike '%OSVERSION%';
 count 
-------
   658
(1 row)

Yes, you can do this with find and grep, but these queries are much faster than that.

We can create all kinds of queries with this data, and allow you to save the output in plain text form.

Loading this up on dev

I loaded this up on dev first. Then I looked at how many Makefiles I had.

freshports.org=# select count(*) from ports where Makefile is not null and status = 'A';;
 count 
-------
 29349
(1 row)

freshports.org=# select count(*) from ports where Makefile is null and status = 'A';;
 count 
-------
    10
(1 row)

Ten ports without Makefiles? Who are they?

freshports.org=# select id, element_pathname(element_id) from ports where Makefile is null and status = 'A';;
  id   |                    element_pathname                     
-------+---------------------------------------------------------
 39093 | /ports/branches/2014Q1/print/typetools
 39450 | /ports/branches/2014Q2/lang/twelf
 39268 | /ports/branches/2014Q1/graphics/ipe
 38946 | /ports/branches/2014Q1/textproc/hs-lhs2tex
 39958 | /ports/branches/2014Q4/multimedia/gmp-api
 40059 | /ports/branches/2014Q4/multimedia/openh264
 40223 | /ports/branches/2016Q1/sysutils/p5-Schedule-Cron-Events
 39208 | /ports/branches/2014Q1/textproc/dblatex
 40070 | /ports/branches/2015Q1/textproc/p5-HTML-FormatExternal
 39110 | /ports/branches/2014Q1/www/chromium
(10 rows)

Ahh, that explains these errors I saw while processing some ports. I won’t worry about they because they are all old branches.

Jul 18 02:48:20 jester FreshPorts[69764]: error executing make command for multimedia/gmp-api: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2014Q4 multimedia/gmp-api 2>/tmp/FreshPorts.multimedia.gmp-api.make-error.2016.7.18.2.48.20.69764  produced this error:  Error message is: cd: /usr/local/repos/PORTS-2014Q4/multimedia/gmp-api: No such file or directory make: cannot open /usr/local/repos/PORTS-2014Q4/multimedia/gmp-api/Makefile. Make results are :  make: stopped in /  (/usr/local/FreshPorts/scripts) 
Jul 18 02:50:33 jester FreshPorts[69764]: error executing make command for multimedia/openh264: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2014Q4 multimedia/openh264 2>/tmp/FreshPorts.multimedia.openh264.make-error.2016.7.18.2.50.33.69764  produced this error:  Error message is: cd: /usr/local/repos/PORTS-2014Q4/multimedia/openh264: No such file or directory make: cannot open /usr/local/repos/PORTS-2014Q4/multimedia/openh264/Makefile. Make results are :  make: stopped in /  (/usr/local/FreshPorts/scripts) 
Jul 18 03:12:40 jester FreshPorts[69764]: error executing make command for print/typetools: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2014Q1 print/typetools 2>/tmp/FreshPorts.print.typetools.make-error.2016.7.18.3.12.40.69764  produced this error:  Error message is: make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.tex.mk" line 92: malformed TEX_DEFAULT: tetex  (/usr/local/FreshPorts/scripts) 
Jul 18 03:35:37 jester FreshPorts[69764]: error executing make command for sysutils/p5-Schedule-Cron-Events: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2016Q1 sysutils/p5-Schedule-Cron-Events 2>/tmp/FreshPorts.sysutils.p5-Schedule-Cron-Events.make-error.2016.7.18.3.35.37.69764  produced this error:  Error message is: cd: /usr/local/repos/PORTS-2016Q1/sysutils/p5-Schedule-Cron-Events: No such file or directory make: cannot open /usr/local/repos/PORTS-2016Q1/sysutils/p5-Schedule-Cron-Events/Makefile. Make results are :  make: stopped in /  (/usr/local/FreshPorts/scripts) 
Jul 18 03:38:48 jester FreshPorts[69764]: error executing make command for textproc/dblatex: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2014Q1 textproc/dblatex 2>/tmp/FreshPorts.textproc.dblatex.make-error.2016.7.18.3.38.48.69764  produced this error:  Error message is: make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.tex.mk" line 92: malformed TEX_DEFAULT: tetex  (/usr/local/FreshPorts/scripts) 
Jul 18 03:39:50 jester FreshPorts[69764]: error executing make command for textproc/hs-lhs2tex: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2014Q1 textproc/hs-lhs2tex 2>/tmp/FreshPorts.textproc.hs-lhs2tex.make-error.2016.7.18.3.39.50.69764  produced this error:  Error message is: make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.tex.mk" line 92: malformed TEX_DEFAULT: tetex  (/usr/local/FreshPorts/scripts) 
Jul 18 03:41:25 jester FreshPorts[69764]: error executing make command for textproc/p5-HTML-FormatExternal: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2015Q1 textproc/p5-HTML-FormatExternal 2>/tmp/FreshPorts.textproc.p5-HTML-FormatExternal.make-error.2016.7.18.3.41.25.69764  produced this error:  Error message is: cd: /usr/local/repos/PORTS-2015Q1/textproc/p5-HTML-FormatExternal: No such file or directory make: cannot open /usr/local/repos/PORTS-2015Q1/textproc/p5-HTML-FormatExternal/Makefile. Make results are :  make: stopped in /  (/usr/local/FreshPorts/scripts) 
Jul 18 03:48:07 jester FreshPorts[69764]: error executing make command for www/chromium: This command (FreshPorts code 1):  /usr/local/bin/sudo /usr/sbin/chroot -u dan /usr/local/FreshPorts/ports-jail /make-port.sh /usr/local/repos/PORTS-2014Q1 www/chromium 2>/tmp/FreshPorts.www.chromium.make-error.2016.7.18.3.48.7.69764  produced this error:  Error message is: make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.port.mk" line 1516: Cannot open /usr/local/repos/PORTS-2014Q1/Mk/Uses/tar.mk make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.port.mk" line 5281: warning: duplicate script for target "-depends" ignored make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.port.mk" line 5278: warning: using previous script for "-depends" defined here make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.port.mk" line 5281: warning: duplicate script for target "-depends" ignored make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.port.mk" line 5278: warning: using previous script for "-depends" defined here make: "/usr/local/repos/PORTS-2014Q1/Mk/bsd.port.mk" lin

What next?

Right now, the data is loading up… Makefile, pkg-plist, distinfo, etc. The script should be done at about 0800 UTC.

Tomorrow morning, I will restart commit processing, and clear the cache so you can see all the pretty new stuff.

In the coming weeks, the search page will be amended to allow searching on the new fields.

Jul 152016
 

About two weeks ago, I wrote a function for creating a port. This will take the port from head and copy it to a branch. This is vital because of the reasons discussed here: creating a new port and copying a port from head to a branch.

This function worked and was useful, but it was missed one vital part: population of the ports_categories table. This table tells us what categories the port is in. Now that I think of it, I may have to populate other tables, such as:

  1. ports_vulnerable
  2. ports_moved
  3. ports_updating_xref
  4. commit_log_ports_vuxml

Those can wait for another day.

Existing bugs fixed

While debugging this issue, I noticed that CopyPortFromHeadToBranch() was documented as returning the port id, but was returning the element id. The difference is that all ports are elements, but not all elements are ports. The code which invokes CopyPortFromHeadToBranch() actually creates the port, while the function just copies stuff around in the element table.

The diff is as follow:

-CREATE OR REPLACE FUNCTION CopyPortFromHeadToBranch(text, text, text) returns int4 AS $$
+CREATE OR REPLACE FUNCTION CopyPortFilesFromHeadToBranch(text, text, text) returns int4 AS $$
 DECLARE
   a_category_name ALIAS for $1;
   a_port_name     ALIAS for $2;
   a_branch_name   ALIAS for $3;
  
-  l_r             element_id_pathname%rowtype;
-  l_port_id       int4;
- 
+  l_r                      element_id_pathname%rowtype;
+  l_element_id_of_new_port int4;
+
   BEGIN
- 
+
+  -- this query will add the new port... the key is the element_add() function call
+
   FOR l_r IN
   WITH RECURSIVE all_descendents AS ( 
   SELECT id, name, parent_id, directory_file_flag, status
@@ -3243,20 +3248,22 @@
     FROM element E
     JOIN all_descendents AD
       ON (E.parent_id = AD.id)
-)
-    SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/' || a_branch_name || '/'), directory_file_flag),
-                       replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/')
+  )
+    SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/' || a_branch_name || '/'), directory_file_flag)
       FROM all_descendents
      WHERE status = 'A'
   LOOP
   END LOOP;
- 
+
   IF FOUND THEN
-    SELECT pathname_id('/ports/branches/' || a_branch_name || '/' || a_category_name || '/' || a_port_name || '/')
-      INTO l_port_id;
+    -- based on the element_id of the pathname for newly created port, grab the port id
+     SELECT pathname_id('/ports/branches/' || a_branch_name || '/' || a_category_name || '/' || a_port_name || '/')
+      INTO l_element_id_of_new_port;
+
   END IF;
- 
-  return l_port_id;
+
+  return l_element_id_of_new_port;
+
   END;
 $$  LANGUAGE 'plpgsql';
 
@@ -3278,12 +3285,27 @@
  
   BEGIN
  
-  SELECT CopyPortFromHeadToBranch(a_category_name, a_port_name, a_branch_name)
+  SELECT CopyPortFilesFromHeadToBranch(a_category_name, a_port_name, a_branch_name)
     INTO l_element_id_of_new_port;
  
   IF FOUND THEN
     SELECT createport(l_element_id_of_new_port, getcategory(a_category_name))
       INTO l_port_id;
+
+    SELECT PA.id
+      FROM ports_active PA
+     WHERE PA.element_id = l_element_id_of_new_port
+      INTO l_port_id;
+
+    -- we need to set up the port_categories table first...
+
+    RAISE WARNING 'about to call GetPort and I only want stuff from head, so branch agnostic is OK.';
+
+    INSERT INTO ports_categories(port_id, category_id)
+     SELECT l_port_id, PC.category_id
+       FROM ports_categories PC
+      WHERE PC.port_id = GetPort(a_category_name, a_port_name);
+
   END IF;
   
   RETURN l_port_id;

I will outline the problems fixed above:

  • line 2 – Rename the function to reflect what it does
  • line 11 – we are returning the element id of the new port
  • line 28 – remove this extraneous parameter from the SELECT
  • line 65 – grab the port id based on the element id and use that to copy values from the ports_categories from the old code to the new port
  • line 74 – I had tried to do this INSERT within the CopyPortFilesFromHeadToBranch() function, but that can’t be done because the port is not created there, it is created on line 62. Once I realized that issue, which was perpetuated by the incorrectly named variable on line 39, it all became clear.

The working code

Here is the code which http://dev.freshports.org/ is using now.

--
-- this function copies a port files from /ports/head/CATEGORY/PORT to /ports/branches/BRANCH
-- It returns the id of the new element for that port.
-- This function DOES not create the port itself (i.e. no entries added to port table).
--
CREATE OR REPLACE FUNCTION CopyPortFilesFromHeadToBranch(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_r                      element_id_pathname%rowtype;
  l_element_id_of_new_port int4;

  BEGIN

  -- this query will add the new port... the key is the element_add() function call

  FOR l_r IN
  WITH RECURSIVE all_descendents AS ( 
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/' || a_category_name || '/' || a_port_name || '/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
  )
    SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/' || a_branch_name || '/'), directory_file_flag)
      FROM all_descendents
     WHERE status = 'A'
  LOOP
  END LOOP;

  IF FOUND THEN
    -- based on the element_id of the pathname for newly created port, grab the port id
     SELECT pathname_id('/ports/branches/' || a_branch_name || '/' || a_category_name || '/' || a_port_name || '/')
      INTO l_element_id_of_new_port;

  END IF;

  return l_element_id_of_new_port;

  END;
$$  LANGUAGE 'plpgsql';


--
-- this function create a port on a branch using head as a starting point.
-- i.e. from /ports/head/CATEGORY/PORT to /ports/branches/BRANCH/CATEGORY/PORT
-- is created.
-- It returns the id of the new port.
--
CREATE OR REPLACE FUNCTION CreatePort(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_element_id_of_new_port int4;
  l_port_id                int4;

  BEGIN

  SELECT CopyPortFilesFromHeadToBranch(a_category_name, a_port_name, a_branch_name)
    INTO l_element_id_of_new_port;

  IF FOUND THEN
    SELECT createport(l_element_id_of_new_port, getcategory(a_category_name))
      INTO l_port_id;

    SELECT PA.id
      FROM ports_active PA
     WHERE PA.element_id = l_element_id_of_new_port
      INTO l_port_id;

    -- we need to set up the port_categories table first...

    RAISE WARNING 'about to call GetPort and I only want stuff from head, so branch agnostic is OK.';

    INSERT INTO ports_categories(port_id, category_id)
     SELECT l_port_id, PC.category_id
       FROM ports_categories PC
      WHERE PC.port_id = GetPort(a_category_name, a_port_name);

  END IF;

  RETURN l_port_id;

  END;
$$  LANGUAGE 'plpgsql';
Jul 032016
 

As you know, I’ve been working on branches this long holiday weekend. Today, I managed to get the display of a port on a branch to correctly.

Along the way, I discovered a downside to storing raw data in the database. Take for example this output:

freshports.org=# select name, depends_run from ports_active where depends_run is not null limit 1;
     name      |                          depends_run                          
---------------+---------------------------------------------------------------
 p5-Net-NBName | /usr/local/bin/perl5.20.2:/usr/local/PORTS-head/lang/perl5.20
(1 row)

This is the output of make -V RUN_DEPENDS. You will notice that this repo is not in /usr/ports. In itself, that is OK, except when you change the directory, like I did earlier this month, when I moved everything into /usr/local/repos. After time, this field throughout the database will contain a mixture of the two root directories. This won’t do, because the website needs to strip off the root directory to get at the category/port combination at the end of the file.

Yes, I could just try taking the last of the string, starting at the second to last slash, but that’s not what I want to do. I want to store the raw data, because that’s what make -V gave me. Thus, I will update the data to make sure it is up to date with current directories.

The solution I used was:

update ports set depends_run   = replace(depends_run,   ':/usr/local/', ':/usr/local/repos/'), 
                 depends_lib   = replace(depends_lib,   ':/usr/local/', ':/usr/local/repos/'), 
                 depends_build = replace(depends_build, ':/usr/local/', ':/usr/local/repos/');

update ports set depends_run   = replace(depends_run,   ':/usr/local/repos/repos/', ':/usr/local/repos/'), 
                 depends_lib   = replace(depends_lib,   ':/usr/local/repos/repos/', ':/usr/local/repos/'), 
                 depends_build = replace(depends_build, ':/usr/local/repos/repos/', ':/usr/local/repos/');       

That second update was to reverse any updates to already-correct values.

This change was on my development server. I won’t need to do this update on the production server because that repo has not changed locations.

Jul 022016
 

In my two previous posts I talked about creating a new port and copying a port from head to a branch.

The goal of this post is the creation of a new function: CreatePortOnBranch($category_name, $port_name, $CommitBranch)

The failed start

I started out with this stored procedure:

CREATE OR REPLACE FUNCTION CreatePort(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_element_id_of_new_port int4;
  l_port_id                int4;

  BEGIN

WITH RECURSIVE all_descendents AS (
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/sysutils/bacula-client-static/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
   SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/'), directory_file_flag), 
                      replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/')
      FROM all_descendents
     WHERE status = 'A';

  SELECT createport(l_element_id_of_new_port, getcategory(a_category_name))
    INTO l_port_id;
 
  RETURN l_port_id;

  END;
$$  LANGUAGE 'plpgsql';

Running it gave this message:

# select CreatePort('sysutils', 'bacula-server', '2016Q2');
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function createport(text,text,text) line 12 at SQL statement

I tried PERFORM, but failed to find a suitable syntax. I found out why. I asked on IRC:

I am trying to use PERFORM in order to discard the result set in a plpgsql function.. I think I want PERFORM, but I’m failing. https://gist.github.com/dlangille/3f464ac2537028f0602551f1bf4db2ba

The replies I got (NOTE: the entire channel log is not shown here):

[Jul 02 12:33]  <RhodiumToad>	perform and with have ... problems
[Jul 02 12:33]  <RhodiumToad>	PERFORM works by replacing the word PERFORM with SELECT and then parsing the query
[Jul 02 12:34]  <RhodiumToad>	so PERFORM WITH ...  never works
[Jul 02 12:36]  <dvl>	RhodiumToad: Sounds like I should create a function with just that WITH RECURSION in it, and call that instead.
[Jul 02 12:36]  <RhodiumToad>	that would probably be simpler
[Jul 02 12:36]  <Myon>	enricostn: that's not a locales problem, that's a version mismatch problem, 9.3 vs 9.5
[Jul 02 12:36]  <johto>	you could also do  PERFORM 1 FROM (<query>) ss;  because there are no wCTEs
[Jul 02 12:37]  <RhodiumToad>	yeah, but that's really ugly
[Jul 02 12:37]  <enricostn>	Myon: oh... would you please so kind to post there and shade some light? Would be really appreciated.
[Jul 02 12:38]  <dvl>	I also think dividing this up into distinct functions will have code benefits later.

The two new functions and a new datatype

Based on the above discussion, I created these functions:

--
-- this function copies a port from /ports/head/CATEGORY/PORT to /ports/branches/BRANCH
-- It returns the id of the new port.
--
CREATE OR REPLACE FUNCTION CopyPortFromHeadToBranch(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_r             element_id_pathname%rowtype;
  l_port_id       int4;

  BEGIN

  FOR l_r IN
  WITH RECURSIVE all_descendents AS ( 
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/' || a_category_name || '/' || a_port_name || '/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
    SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/' || a_branch_name || '/'), directory_file_flag),
                       replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/')
      FROM all_descendents
     WHERE status = 'A'
  LOOP
  END LOOP;

  IF FOUND THEN
    SELECT pathname_id('/ports/branches/' || a_branch_name || '/' || a_category_name || '/' || a_port_name || '/')
      INTO l_port_id;
  END IF;

  return l_port_id;
  END;
$$  LANGUAGE 'plpgsql';

With this helper function created, the original function becomes:

--
-- this function create a port on a branch using head as a starting point.
-- i.e. from /ports/head/CATEGORY/PORT to /ports/branches/BRANCH/CATEGORY/PORT
-- is created.
-- It returns the id of the new port.
--
CREATE OR REPLACE FUNCTION CreatePort(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_element_id_of_new_port int4;
  l_port_id                int4;

  BEGIN

  SELECT CopyPortFromHeadToBranch(a_category_name, a_port_name, a_branch_name)
    INTO l_element_id_of_new_port;

  IF FOUND THEN
    SELECT createport(l_element_id_of_new_port, getcategory(a_category_name))
      INTO l_port_id;
  END IF;
 
  RETURN l_port_id;

  END;
$$  LANGUAGE 'plpgsql';

Here is the first test of that function:

select CreatePort('sysutils', 'bacula-docs', '2016Q2');

 createport 
------------
      41762
(1 row)

Let’s see the full pathname of that port:

# select element_pathname(element_id) from ports where id = 41762;
              element_pathname               
---------------------------------------------
 /ports/branches/2016Q2/sysutils/bacula-docs
(1 row)

Success.

Jul 022016
 

In my previous post, I wrote about the need to copy a port from head to a branch in some circumstances, specifically when a master-port is updated and the slave ports do not already exist in the FreshPorts database on that branch. This is a side-effect of FreshPorts only taking action for commits, and not repo-copies.

Get new pathnames

Expanding upon the recursive query from that post, I think I have a starting point for a copy-port function. Let me illustrate with this query. Assume we need to duplicate sysutils/bacula-client from head to branch 2016Q2.

WITH RECURSIVE all_descendents AS (
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/sysutils/bacula-client/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
   SELECT directory_file_flag, element_pathname(id) AS pathname, replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/')
      FROM all_descendents
     WHERE status = 'A'
 ORDER BY pathname;

 directory_file_flag |                  pathname                   |                        replace                         
---------------------+---------------------------------------------+--------------------------------------------------------
 D                   | /ports/head/sysutils/bacula-client          | /ports/branches/2016Q2/sysutils/bacula-client
 F                   | /ports/head/sysutils/bacula-client/Makefile | /ports/branches/2016Q2/sysutils/bacula-client/Makefile
(2 rows)

The first column indicates file or directory. The second column is the name of the existing element in head. The third column is the name of the element to be created in the branch.

Add new pathnames to the database

Next, how do use that information to create the new elements?

We use the existing function: element_add($in_element_pathname, $element_directory_file_flag)

Let’s incorporate that into the query above and get:

WITH RECURSIVE all_descendents AS (
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/sysutils/bacula-client/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
   SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/'), directory_file_flag), replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/'), directory_file_flag
      FROM all_descendents
     WHERE status = 'A';

 element_add |                        replace                         | directory_file_flag 
-------------+--------------------------------------------------------+---------------------
      739280 | /ports/branches/2016Q2/sysutils/bacula-client          | D
      739281 | /ports/branches/2016Q2/sysutils/bacula-client/Makefile | F

-- Let's confirm the new elements:

WITH RECURSIVE all_descendents AS (
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/branches/2016Q2/sysutils/bacula-client/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
   SELECT id, name, parent_id, directory_file_flag, status, element_pathname(id) AS pathname
      FROM all_descendents
     WHERE status = 'A'
 ORDER BY pathname;

   id   |     name      | parent_id | directory_file_flag | status |                        pathname                        
--------+---------------+-----------+---------------------+--------+--------------------------------------------------------
 739280 | bacula-client |    732848 | D                   | A      | /ports/branches/2016Q2/sysutils/bacula-client
 739281 | Makefile      |    739280 | F                   | A      | /ports/branches/2016Q2/sysutils/bacula-client/Makefile
(2 rows)

There they are!

Create the port

This creates the directory structure (i.e. pathnames) but it does not create the port. We can do that next with createport($port_element_id, $port_category_id). Let’s try this:

select createport(pathname_id(‘/ports/branches/2016Q2/sysutils/bacula-client’), getcategory(‘sysutils’));

Before we run that, let’s break down the individual components, because I found an error and I want to demonstrate it.

select pathname_id('/ports/branches/2016Q2/sysutils/bacula-client');
 pathname_id 
-------------
      739280
(1 row)

You will see 739280 mentioned above in the output from creating the elements on the branch.

Next we grab the category details:

select getcategory('sysutils');
 getcategory 
-------------
            
(1 row)

Eh? Woah, that’s not good.

Fix the category code

Let’s look at that code:

CREATE OR REPLACE FUNCTION GetCategory (text ) RETURNS int4 AS $$
   DECLARE
      category_name ALIAS for $1;
      pathname             text;   
      category_element_id  int4;
      category_id          int4;
   BEGIN
      pathname            := 'ports/' || category_name;
      category_element_id := Pathname_ID(pathname);
      if NOT category_element_id IS NULL THEN
         select id
           into category_id
           from categories
          where element_id = category_element_id;
      END IF;
      return category_id;
   END;
$$ LANGUAGE 'plpgsql';

Ahh, look at line 8. That pathname needs to be prefixed with ‘ports/head/’. This change is related to Full pathnames, which was work carried out a few years ago. This function was used only by some one-off maintenance scripts.

After making that change, the results are as expected:

# select getcategory('sysutils');
 getcategory 
-------------
          20
(1 row)

Back to creating the port

Let’s create that port:

# select createport(pathname_id('/ports/branches/2016Q2/sysutils/bacula-client'), getcategory('sysutils'));
 createport 
------------
      41761
(1 row)

 select element_pathname(element_id) from ports where id = 41761;
               element_pathname                
-----------------------------------------------
 /ports/branches/2016Q2/sysutils/bacula-client
(1 row)

We have a port on the branch. It needs to be refreshed from the Makefile (via make -V), but that is for a later stage.

I think we are well on our way to copying a port upon demand.

Jun 292016
 

The tracking of branches has turned out to be more involved than I thought. Many functions are already branch-aware and return / do the right thing. Tonight, I tried my recent idea of using head to get the slave ports for a given master port on a branch. So far, that works. The hard part now is dealing with a slave port on a branch which had not yet had any commits on that branch.

First, FreshPorts takes action only upon recent of a commit. If a file has been created by a repo copy, then FreshPorts knows nothing about it.

That’s bad news for branches, which are similar to a repo-copy in concept.

Compare these two URLs:

  1. https://svnweb.freebsd.org/ports/head/sysutils/bacula-server/Makefile
  2. https://svnweb.freebsd.org/ports/branches/2016Q2/sysutils/bacula-server/Makefile

You will see revision 412349 on both pages.

Ideally, FreshPorts would do it’s own internal equivalent of a repocopy when it sees the 2016Q2 branch created, but in the current database design, that would duplicate the content of head for each branch, four times a year. That’s not a good approach.

The current system of adding new elements (each file in a commit is an element, e.g. /ports/branches/2016Q2/sysutils/bacula-server/Makefile) is based upon processing a commit. Each commit has an associated revision number. Thus, when adding a new element, we add a reference to that revision name. This won’t work for the situation we have under consideration.

I think the new approach needs a function such as: $port->CreatePortOnBranch($category_name, $port_name, $CommitBranch); which will take all the elements until a given point in head and duplicate them on a branch. This seems to be the easiest solution so far. I think I can easier create a store procedure which pulls back all children under a given point in the tree.

Using the recursion query I created a few days ago as a starting point, it only took me a few minute to create this query:

WITH RECURSIVE all_descendents AS (
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/sysutils/bacula-server/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
   SELECT id, name, parent_id, directory_file_flag, status, element_pathname(id) AS pathname
      FROM all_descendents
     WHERE status = 'A'
 ORDER BY pathname;

   id   |              name               | parent_id | directory_file_flag | status |                                 pathname                                 
--------+---------------------------------+-----------+---------------------+--------+--------------------------------------------------------------------------
 205194 | bacula-server                   |       218 | D                   | A      | /ports/head/sysutils/bacula-server
 205195 | Makefile                        |    205194 | F                   | A      | /ports/head/sysutils/bacula-server/Makefile
 475011 | Makefile.common                 |    205194 | F                   | A      | /ports/head/sysutils/bacula-server/Makefile.common
 212250 | distinfo                        |    205194 | F                   | A      | /ports/head/sysutils/bacula-server/distinfo
 212251 | files                           |    205194 | D                   | A      | /ports/head/sysutils/bacula-server/files
 240895 | bacula-dir.in                   |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/bacula-dir.in
 240896 | bacula-fd.in                    |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/bacula-fd.in
 240897 | bacula-sd.in                    |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/bacula-sd.in
 212252 | chio-bacula                     |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/chio-bacula
 682189 | patch-src_console_Makefile.in   |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/patch-src_console_Makefile.in
 682190 | patch-src_dird_Makefile.in      |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/patch-src_dird_Makefile.in
 682191 | patch-src_filed_Makefile.in     |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/patch-src_filed_Makefile.in
 682192 | patch-src_qt-console_bat.pro.in |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/patch-src_qt-console_bat.pro.in
 682193 | patch-src_stored_Makefile.in    |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/patch-src_stored_Makefile.in
 682194 | patch-src_tools_Makefile.in     |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/patch-src_tools_Makefile.in
 352748 | pkg-deinstall.client.in         |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/pkg-deinstall.client.in
 352749 | pkg-deinstall.server.in         |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/pkg-deinstall.server.in
 352750 | pkg-install.client.in           |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/pkg-install.client.in
 352751 | pkg-install.server.in           |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/pkg-install.server.in
 262399 | pkg-message.client.in           |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/pkg-message.client.in
 262400 | pkg-message.server.in           |    212251 | F                   | A      | /ports/head/sysutils/bacula-server/files/pkg-message.server.in
 205198 | pkg-plist                       |    205194 | F                   | A      | /ports/head/sysutils/bacula-server/pkg-plist
 205199 | pkg-plist.client                |    205194 | F                   | A      | /ports/head/sysutils/bacula-server/pkg-plist.client
(23 rows)

Comparing this to a find in the ports tree:

$ find . | sort 
.
./Makefile
./Makefile.common
./distinfo
./files
./files/bacula-barcodes
./files/bacula-dir.in
./files/bacula-fd.in
./files/bacula-sd.in
./files/chio-bacula
./files/patch-src_console_Makefile.in
./files/patch-src_dird_Makefile.in
./files/patch-src_filed_Makefile.in
./files/patch-src_qt-console_bat.pro.in
./files/patch-src_stored_Makefile.in
./files/patch-src_tools_Makefile.in
./files/pkg-deinstall.client.in
./files/pkg-deinstall.server.in
./files/pkg-install.client.in
./files/pkg-install.server.in
./files/pkg-message.client.in
./files/pkg-message.server.in
./pkg-descr
./pkg-plist
./pkg-plist.client

That’s close enough.

I think the pseudo code for creating the port on the branch will be:

ListOfFiles = GetFilesFor('/ports/head/sysutils/bacula-server');
for file in ${Files}
do
  NewFile = replace('^/ports/head/', '/ports/branches/2016Q2/', ${file});
  AddToElementTable(${NewFile});
done

NewPort(''/ports/branches/2016Q2/sysutils/bacula-server');

Again, I’ll think on it. I’m too tired to even review what I wrote above…

Jun 282016
 

I used to think branches were simple when it came to FreshPorts, but I was wrong. Consider slave ports for example.

If we get a commit for databases/mariadb100-server, we have to update the slave ports, but only the slave ports on the branch, not the slave ports on head.

That sounds easy enough, but keep in mind that FreshPorts only knows about ports which have had a commit. If the first commit is to the master port, then there is no slave port on the branch for FreshPorts.

But wait, you’ll think, the branch is just a branch and it’s a copy of the repo from at the time the branch was … branched, the slave port is always there.

Well, yes, in the repo, but FreshPorts is not a copy of the repo, it is a reflection of commits and that data is extracted via make -V (for the most part).

When that commit for databases/mariadb100-server, there might not be any ports on the branch which have it as a master port. However, there might be commits on other branches, which means the existing SQL might return something like this:

SELECT PA.id          AS slave_port_id,
       PA.name        AS slave_port_name,
       PA.category_id AS slave_category_id,
       PA.category    AS slave_category_name,
       element_pathname(EP.element_id)
  FROM ports_active PA, element_pathname EP
 WHERE PA.master_port = 'databases/mariadb100-server'
   AND PA.element_id  = EP.element_id;

 slave_port_id |  slave_port_name  | slave_category_id | slave_category_name |                  element_pathname                  
---------------+-------------------+-------------------+---------------------+----------------------------------------------------
         35630 | mariadb100-client |                32 | databases           | /ports/head/databases/mariadb100-client
         40269 | mariadb100-client |                32 | databases           | /ports/branches/2015Q3/databases/mariadb100-client
         41023 | mariadb100-client |                32 | databases           | /ports/branches/2015Q4/databases/mariadb100-client
(3 rows)

As you can see, we have three different slave ports, none of which are on our branch (which is this particular case, is 2016Q2.

I think we need to have a query which takes the path into consideration, so we get only the slave ports on our branch. For example:

SELECT PA.id          AS slave_port_id,
       PA.name        AS slave_port_name,
       PA.category_id AS slave_category_id,
       PA.category    AS slave_category_name,
       element_pathname(EP.element_id)
  FROM ports_active PA, element_pathname EP
 WHERE PA.master_port = 'databases/mariadb100-server'
   AND EP.pathname    ilike '/ports/branches/2016Q2/%'
   AND PA.element_id  = EP.element_id;

 slave_port_id | slave_port_name | slave_category_id | slave_category_name | element_pathname 
---------------+-----------------+-------------------+---------------------+------------------
(0 rows)

This is correct. However, it is not ideal for our purposes. We want a list of ports, whether or not they are already on our branch.

Union to the rescue.

SELECT PA.id          AS slave_port_id,
       PA.name        AS slave_port_name,
       PA.category_id AS slave_category_id,
       PA.category    AS slave_category_name,
       element_pathname(EP.element_id)
  FROM ports_active PA, element_pathname EP
 WHERE PA.master_port = 'databases/mariadb100-server'
   AND EP.pathname    ilike '/ports/branches/2016Q2/%'
   AND PA.element_id  = EP.element_id

UNION

SELECT PA.id          AS slave_port_id,
       PA.name        AS slave_port_name,
       PA.category_id AS slave_category_id,
       PA.category    AS slave_category_name,
       element_pathname(EP.element_id)
  FROM ports_active PA, element_pathname EP
 WHERE PA.master_port = 'databases/mariadb100-server'
   AND EP.pathname    ilike '/ports/head/%'
   AND PA.element_id  = EP.element_id

ORDER BY slave_category_name, slave_port_name;

 slave_port_id |  slave_port_name  | slave_category_id | slave_category_name |            element_pathname             
---------------+-------------------+-------------------+---------------------+-----------------------------------------
         35630 | mariadb100-client |                32 | databases           | /ports/head/databases/mariadb100-client
(1 row)

Now we have the list we need, and it will be clear which ports do not exist on the branch and we must first create the port instead of fetching it. This point becomes clearer when you try the same query on databases/postgresql94-server

SELECT PA.id          AS slave_port_id,
       PA.name        AS slave_port_name,
       PA.category_id AS slave_category_id,
       PA.category    AS slave_category_name,
       element_pathname(EP.element_id)
  FROM ports_active PA, element_pathname EP
 WHERE PA.master_port = 'databases/postgresql94-server'
   AND EP.pathname    ilike '/ports/branches/%'
   AND PA.element_id  = EP.element_id
UNION

SELECT PA.id          AS slave_port_id,
       PA.name        AS slave_port_name,
       PA.category_id AS slave_category_id,
       PA.category    AS slave_category_name,
       element_pathname(EP.element_id)
  FROM ports_active PA, element_pathname EP
 WHERE PA.master_port = 'databases/postgresql94-server'
   AND EP.pathname    ilike '/ports/head/%'
   AND PA.element_id  = EP.element_id
ORDER BY slave_category_name, slave_port_name, element_pathname;

 slave_port_id |    slave_port_name    | slave_category_id | slave_category_name |                   element_pathname                   
---------------+-----------------------+-------------------+---------------------+------------------------------------------------------
         40432 | postgresql94-client   |                32 | databases           | /ports/branches/2015Q2/databases/postgresql94-client
         39921 | postgresql94-client   |                32 | databases           | /ports/branches/2016Q1/databases/postgresql94-client
         34655 | postgresql94-client   |                32 | databases           | /ports/head/databases/postgresql94-client
         34657 | postgresql94-contrib  |                32 | databases           | /ports/head/databases/postgresql94-contrib
         34678 | postgresql94-docs     |                32 | databases           | /ports/head/databases/postgresql94-docs
         34677 | postgresql94-plperl   |                32 | databases           | /ports/head/databases/postgresql94-plperl
         34675 | postgresql94-plpython |                32 | databases           | /ports/head/databases/postgresql94-plpython
         34676 | postgresql94-pltcl    |                32 | databases           | /ports/head/databases/postgresql94-pltcl
(8 rows)

If you look only at slave_port_name and slave_category_name, you can see we have duplicates. The ideal situation takes values from head first, then removes any values found in the branch we are interested in, and for this query, we are interested in just one branch.

I’m positive we can do this with just one query.

I thought about this issue overnight. I think the solution is as follows.

  1. Get the list of master ports from head
  2. Look for those same port names on the branch
  3. If the port exists, confirm it still has the same master port (i.e. the one from step #1)
  4. If not the same same master port, ignore this ‘slave’ port
  5. If port exists, refresh it
  6. If port does not exist on branch, create it, and refresh it

NOTE:

  • Unless otherwise mentioned, we are dealing with the branch upon with the commit occurred
  • When refreshing, we refer to the commit we are processing now

I’ll have to think about this some more.

Jun 272016
 

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.

Jun 182016
 

This afternoon, Bert JW Regeer wished that FreshPorts “would show information from the quarterly branches too…”.

I knew I had done some work on this and that there was a way to display branch information. I check the source code and found the ?branch= parameter, but there was nothing in the database for this branch.

I discovered several missing items. I apologize that this list will not refer to something you can check. It is mostly for my own notes when i come back to this later.

To try getting one commit into the try, I did this:

cd /usr/FreshPorts/ports-jail/usr/local</li>
sudo mkdir PORTS-2016Q2
sudo chown dan PORTS-2016Q2
cd PORTS-2016Q2
svn co svn://svn.freebsd.org/ports/branches/2016Q2 .

That took some time…

I grabbed a raw email from https://docs.freebsd.org/mail/current/svn-ports-branches.html and used it for testing. It seems that branches.pm needs to contain a hash of all branches we are using. Once I added 2016Q2 to that file, the commit was processed and loaded up.

That allows http://www.freshports.org/?branch=2016Q2 to display something.

Short term

In the short term, this needs to be done:

  1. svn co of each branch of the ports tree (now underway)
  2. Get copy of all emails sent to the svn-ports-branches email list (i.e. https://docs.freebsd.org/mail/archive/svn-ports-branches.html
  3. Remove requirement for each quarterly branch to be listed in branches.pm
  4. Automate the process of checking out each quarterly branch