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';
