Re: Trees: maintaining pathnames

On 17 Nov 2002 at 11:39, Dan Langille wrote:

> My existing tree implementation reflects the files contained on disk.
> The full pathname to a particlar file is obtained from the path to the
> parent directory. I am now considering putting this information into
> a field in the table.
>
> Attached you will find the pg_dump from my test database (2.4k) if you
> want to test with this setup and in case what I have pasted below
> contains an error.
>
> Here is the table and the test data:
>
> create table tree(id int not null, parent_id int, name text not null,
> pathname text not null, primary key (id));
>
> insert into tree (id, name, pathname) values (1, 'usr', '/usr');
> insert into tree (id, name, parent_id, pathname) values (2, 'ports',
> 1, '/usr/ports'); insert into tree values (3, 2, 'security', 'test');
>
> select * from tree;
>
> test=# select * from tree;
> id | parent_id | name | pathname
> ----+-----------+----------+---------------------
> 1 | | usr | /usr
> 2 | 1 | ports | /usr/ports
> 3 | 2 | security | /usr/ports/security
> (3 rows)
>
>
> The goal is to ensure that pathname always contains the correct value.

I am now trying another method, which involves the use of a cache
table. In short, we store the pathname in another table.

create table tree_pathnames (
id int4 not null,
pathname text not null,
primary key(id),
foreign key (id) references tree(id)
on delete cascade on update cascade
);

I populated this table with the following:

insert into tree_pathnames select id, pathname from tree;

My next task was to create a function which would cascade a change to
tree.name throughout tree_pathname. Here is what I came up with:

create or replace function tree_pathname_set_children(int4, text)
returns int as
'DECLARE

node ALIAS for $1;
path ALIAS for $2;
children record;

BEGIN
FOR children IN SELECT ep.id, ep.pathname, e.name
FROM element_pathnames ep, element e
WHERE ep.id = e.id
AND e.parent_id = node LOOP
-- children.pathname = path || ''/'' || children.name;
RAISE NOTICE ''in tree_pathname_set_children %/%'', path,
children.name ;
UPDATE element_pathnames set pathname = path || ''/'' ||
children.name where id = children.id;
perform tree_pathname_set_children(children.id, path || ''/''
|| children.name);
END LOOP;

return 0;
END;'

language 'plpgsql';

This function is invoked from within the trigger on tree:

create or replace function tree_pathnames() returns opaque as '
DECLARE
parent_pathname text;
my_pathname text;
BEGIN
if old.name <> new.name then
select pathname
into parent_pathname
from tree_pathnames
where id = new.parent_id;
if found then
my_pathname = parent_pathname || \'/\' ||new.name;
else
my_pathname = \'/\' || new.name;
end if;

new.pathname = my_pathname;
update tree_pathnames set pathname = my_pathname where id =
new.id;
perform tree_pathname_set_children(new.id,my_pathname);
end if;

RETURN new;
END;'

language 'plpgsql';

drop trigger tree_pathnames on element;
create trigger tree_pathnames before update on element for each row
execute procedure tree_pathnames();

I have done only preliminary testing on this, but it seems to work
fine for my application.

Comments please.
--
Dan Langille : http://www.langille.org/