Sep 062012
 

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:

CREATE OR REPLACE FUNCTION element_pathname_update() RETURNS TRIGGER AS $$
   BEGIN
                if (NEW.name != old.name OR new.parent_id != old.parent_id) 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.

With those changes, done, let’s modify the www layer. This change is simple:

define('FRESHPORTS_PORTS_TREE_PREFIX', '/ports/head/');

It was at this point, that I realized my previous move was in error. Take, for example, this port:

freshports.org=# select E.*, element_pathname(id) from element E where name = 'beadm' ORDER BY element_pathname(id);
   id   | name  | parent_id | directory_file_flag | status |         element_pathname         
--------+-------+-----------+---------------------+--------+----------------------------------
 427802 | beadm |       218 | D                   | A      | /ports/head/ports/sysutils/beadm
(1 row)

That path is wrong. It should be: /ports/head/sysutils/beadm to reflect the svn path: . This SQL moves everything up one path, and deletes the now unused directory:

freshports.org=# begin;
BEGIN
freshports.org=# update element set parent_id = (select pathname_id('/ports/head')) where parent_id = (select pathname_id('/ports/head/ports'));
UPDATE 80
freshports.org=# delete from element where id  = (select pathname_id('/ports/head/ports')) ;
DELETE 1
freshports.org=# commit;
COMMIT
freshports.org=# 

However, this led me to an interesting situation… the element_pathname table…. is it still accurate with this type of move? Let’s check:

freshports.org=# select E.*, element_pathname(id) from element E where id = 427802 or parent_id = 427802;
   id   |   name    | parent_id | directory_file_flag | status |           element_pathname           
--------+-----------+-----------+---------------------+--------+--------------------------------------
 427802 | beadm     |       218 | D                   | A      | /ports/head/sysutils/beadm
 427803 | Makefile  |    427802 | F                   | A      | /ports/head/sysutils/beadm/Makefile
 427804 | distinfo  |    427802 | F                   | A      | /ports/head/sysutils/beadm/distinfo
 427805 | pkg-descr |    427802 | F                   | A      | /ports/head/sysutils/beadm/pkg-descr
(4 rows)

freshports.org=# select * from element_pathname where element_id in (select E.id from element E where id = 427802 or parent_id = 427802);
 element_id |            pathname             
------------+---------------------------------
     427802 | /ports/sysutils/beadm
     427803 | /ports/sysutils/beadm/Makefile
     427804 | /ports/sysutils/beadm/distinfo
     427805 | /ports/sysutils/beadm/pkg-descr
(4 rows)

freshports.org=# select * from element_pathname where pathname like '/ports/head/%';
 element_id |         pathname          
------------+---------------------------
     453747 | /ports/head/A
     172891 | /ports/head/CHANGES
     228686 | /ports/head/COPYRIGHT
     441602 | /ports/head/CVSROOT
     249957 | /ports/head/GIDs
      77267 | /ports/head/INDEX
     120467 | /ports/head/INDEX-5
     229283 | /ports/head/KNOBS
      83075 | /ports/head/LEGAL
     117606 | /ports/head/MOVED
     110905 | /ports/head/Makefile
      77070 | /ports/head/Mk
     105961 | /ports/head/README
     453746 | /ports/head/TEST
     453748 | /ports/head/TEST/B
     453749 | /ports/head/TEST/C
     105962 | /ports/head/Templates
      77184 | /ports/head/Tools
     249958 | /ports/head/UIDs
     176987 | /ports/head/UPDATING
     171607 | /ports/head/accessibility
     159346 | /ports/head/arabic
        350 | /ports/head/archivers
        410 | /ports/head/astro
        386 | /ports/head/audio
       2710 | /ports/head/benchmarks
        869 | /ports/head/biology
        830 | /ports/head/cad
       1660 | /ports/head/chinese
       2191 | /ports/head/comms
        423 | /ports/head/converters
        582 | /ports/head/databases
        802 | /ports/head/deskutils
         84 | /ports/head/devel
     148762 | /ports/head/dns
          2 | /ports/head/editors
        245 | /ports/head/emulators
     118514 | /ports/head/finance
      16545 | /ports/head/french
        140 | /ports/head/ftp
         18 | /ports/head/games
       3747 | /ports/head/german
         29 | /ports/head/graphics
      11329 | /ports/head/hebrew
     118517 | /ports/head/hungarian
         39 | /ports/head/irc
        129 | /ports/head/japanese
        815 | /ports/head/java
       1109 | /ports/head/korean
        171 | /ports/head/lang
        201 | /ports/head/mail
        176 | /ports/head/math
       6412 | /ports/head/mbone
         42 | /ports/head/misc
     118520 | /ports/head/multimedia
         50 | /ports/head/net
     229588 | /ports/head/net-im
     173566 | /ports/head/net-mgmt
     236506 | /ports/head/net-p2p
        179 | /ports/head/news
       2143 | /ports/head/palm
      58316 | /ports/head/picobsd
     148764 | /ports/head/polish
     265343 | /ports/head/ports-mgmt
     118523 | /ports/head/portuguese
        360 | /ports/head/print
        577 | /ports/head/russian
      56065 | /ports/head/science
         34 | /ports/head/security
        465 | /ports/head/shells
        218 | /ports/head/sysutils
        188 | /ports/head/textproc
      57265 | /ports/head/ukrainian
         94 | /ports/head/vietnamese
          9 | /ports/head/www
        231 | /ports/head/x11
        428 | /ports/head/x11-clocks
     278044 | /ports/head/x11-drivers
        516 | /ports/head/x11-fm
       1229 | /ports/head/x11-fonts
       3321 | /ports/head/x11-servers
     171611 | /ports/head/x11-themes
         55 | /ports/head/x11-toolkits
        147 | /ports/head/x11-wm
(84 rows)

No. They are wrong. The function, element_pathname, is correct, but the stored values are correct only for the elements which have been moved. For any of their descendents, it is wrong. The element_pathname_update() trigger needs to act recursively for any children.

My fix for that is:

CREATE OR REPLACE FUNCTION ElementPathnameUpdateChildren (int4) returns
int4 AS $$
  DECLARE
    p_ParentID  ALIAS FOR $1;

    l_element_pathname  RECORD;
    l_record_count      int4;
    l_temp_count        int4;

  BEGIN
    l_record_count := 0;
    FOR l_element_pathname IN SELECT * FROM element WHERE parent_id = p_ParentID LOOP
      l_record_count := l_record_count + 1;
      UPDATE element_pathname
         SET pathname   = element_pathname(l_element_pathname.id)
       WHERE element_id = l_element_pathname.id;

      IF l_element_pathname.directory_file_flag = 'D' THEN
        SELECT ElementPathnameUpdateChildren(l_element_pathname.id)
          INTO l_temp_count;
        l_record_count := l_record_count + l_temp_count;
      END IF;
    END LOOP;

    RETURN l_record_count;
  END;
$$  LANGUAGE 'plpgsql';

Which is invoked from within the trigger:

CREATE OR REPLACE FUNCTION element_pathname_update() RETURNS TRIGGER AS $$
   BEGIN
                if (NEW.name != old.name OR new.parent_id != old.parent_id) THEN
                        UPDATE element_pathname SET pathname = element_pathname(new.id) WHERE element_id = NEW.id;
                        PERFORM ElementPathnameUpdateChildren(new.id);
                END IF;
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';
Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive