Creating the new repo base directories

This article was originally written in September 2012, but never published. I’m publishing it today, much later than expected.

I started looking at how to create these new directories as mentioned yesterday.

This starts the process:

$ psql freshports.org
psql (9.0.8, server 9.0.9)
Type "help" for help.

freshports.org=# select Element_Add('/base/head', 'D');
 element_add 
-------------
      453742
(1 row)

freshports.org=# select Element_Add('/doc/head', 'D');
 element_add 
-------------
      453743
(1 row)

freshports.org=# select Element_Add('/ports/head', 'D');
 element_add 
-------------
           0
(1 row)

freshports.org=# 

Looking at the last add, it returned zero, which means it already exists. Looking into that, I see a rogue commit which got through while I was still amending my dev box. Deleting the commit and the directories under /ports/head fixes that:

freshports.org=# begin;
BEGIN
freshports.org=# delete from commit_log where message_id = '201209061429.q86ETxwZ034822@svn.freebsd.org';
DELETE 1
freshports.org=# select pathname_id('/ports/head/ftp');
 pathname_id 
-------------
      453738
(1 row)

freshports.org=# delete from element where id = 453738;
DELETE 1
freshports.org=# commit;
COMMIT
freshports.org=# 

Oh wait, there is a problem. The /ports directory already exists. Rather than move everything under /ports/* to /ports/head, it’s easier to create a temporary location, move one element, then rename. You’ll see….

freshports.org=# select Element_Add('/portsTMP/head', 'D');
 element_add 
-------------
      453745
(1 row)

freshports.org=# 

Let’s delete what we created in error:

freshports.org=# select pathname_id('/ports/head');
 pathname_id 
-------------
      453737
(1 row)

freshports.org=# begin; delete from element where id = (select pathname_id('/ports/head'));
BEGIN
DELETE 1
freshports.org=# select pathname_id('/ports/head');
 pathname_id 
-------------
            
(1 row)

freshports.org=# commit;
COMMIT
freshports.org=# 

Now, let’s move those elements around:

freshports.org=# begin;
BEGIN
freshports.org=# update element set name = 'ports' where id = (select pathname_id('/portsTMP'));
ERROR:  duplicate key value violates unique constraint "element_pathname_pathname"
DETAIL:  Key (pathname)=(/ports) already exists.
CONTEXT:  SQL statement "UPDATE element_pathname SET pathname = element_pathname(new.id) WHERE element_id = NEW.id"
PL/pgSQL function "element_pathname_update" line 3 at SQL statement
freshports.org=# rollback;
ROLLBACK

What? Pathnames (e.g. /ports) are both calculated and stored. They are stored for doing certain queries, and they are calculated for historical reasons.

Thus, the following two queries should be identical:

freshports.org=# select element_pathname(453742);
 element_pathname 
------------------
 /base/head
(1 row)

freshports.org=# select * from element_pathname where element_id = 453742;
 element_id |  pathname  
------------+------------
     453742 | /base/head
(1 row)

The pathname is kept in sync by these triggers:

CREATE OR REPLACE FUNCTION element_pathname_insert() RETURNS TRIGGER AS $$
   BEGIN
                INSERT INTO element_pathname (element_id, pathname)
                        VALUES (NEW.id, element_pathname(NEW.id));
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER element_pathname_insert ON element;
CREATE TRIGGER element_pathname_insert
    AFTER INSERT on element
    FOR EACH ROW
    EXECUTE PROCEDURE element_pathname_insert();

CREATE OR REPLACE FUNCTION element_pathname_update() RETURNS TRIGGER AS $$
   BEGIN
                if (NEW.name != old.name) THEN
                        UPDATE element_pathname SET pathname = element_pathname(new.id) WHERE element_id = NEW.id;
                END IF;
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER element_pathname_update ON element;
CREATE TRIGGER element_pathname_update
    AFTER UPDATE on element
    FOR EACH ROW
    EXECUTE PROCEDURE element_pathname_update();

There are two situations under which an element’s pathname can change. The above code considers only one.

• The name changes
• The parent_id changes

The fix is in the IF statement within the update trigger:

REATE OR REPLACE FUNCTION element_pathname_update() RETURNS TRIGGER AS $$
   BEGIN
                if (NEW.name != old.name) THEN
                        UPDATE element_pathname SET pathname = element_pathname(new.id) WHERE element_id = NEW.id;
                END IF;
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

Now, let’s try that update again:

freshports.org=# begin;
BEGIN
freshports.org=# update element set name = 'ports' where id = (select pathname_id('/portsTMP'));
UPDATE 1
freshports.org=# commit;
COMMIT

Much better.