Sebastian> However, in nested set model we can't calculate LEVEL
Sebastian> of recursion (nesting depth) directly. By "directly" I
Sebastian> mean based on information available in current row.
Sebastian> Instead we must resort to some scripting language
Sebastian> (either inside or outside DBMS). It turns out not to
Sebastian> be too difficult:
Actually, you can do this in sql:
create table acs_object_types (
object_type varchar(100) primary key,
supertype varchar(100) references acs_object_types,
l_node integer not null,
r_node integer not null ...
);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('acs_object', null, 1, 20);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('relationship', 'acs_object', 2, 3);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('party', 'acs_object', 4, 11);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('person', 'party', 5, 8);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('user', 'person', 6, 7);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('group', 'party', 9, 10);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('membership_rel', 'acs_object', 12, 13);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('composition_rel', 'acs_object', 14, 15);
insert into acs_object_types (object_type,supertype,l_node,r_node) values ('journal_entry', 'acs_object', 16, 17);
insert into acs_object_types (object_type,supertype,l_node,r_node)
values ('site_node', 'acs_object', 18, 19);
select obj1.object_type,
obj1.supertype,
obj1.l_node,
obj1.r_node,
count(obj1.supertype) + 1 as level
from acs_object_types obj1, acs_object_types obj2
where ((obj1.l_node > obj2.l_node and obj1.r_node < obj2.r_node) or
(obj1.supertype is null))
group by obj1.object_type,
obj1.supertype,
obj1.l_node,
obj1.r_node
order by obj1.l_node;
object_type | supertype | l_node | r_node | level
-----------------+------------+--------+--------+-------
acs_object | | 1 | 20 | 1
relationship | acs_object | 2 | 3 | 2
party | acs_object | 4 | 11 | 2
person | party | 5 | 8 | 3
user | person | 6 | 7 | 4
group | party | 9 | 10 | 3
membership_rel | acs_object | 12 | 13 | 2
composition_rel | acs_object | 14 | 15 | 2
journal_entry | acs_object | 16 | 17 | 2
site_node | acs_object | 18 | 19 | 2
(10 rows)
